MySQL主從分離實現

前言

  大型網站為了減輕服務器處理海量的並發訪問，所產生的性能問題，採用了很多解決方案，其中最主流的解決方案就是讀寫分離，即將讀操作和寫操作分別導流到不同的服務器集群執行，到了數據業務層，數據訪問層，如果還是傳統的單靠一台服務器去扛，面對如此多的數據庫連接操作，必然會崩潰，如果造成數據的丟失，是不堪設想的。本文將利用mysql的主從配置，實現讀寫分離。

主從庫理論知識

主從同步如何實現？
  同步工作主要又三步，第一步就是主服務器（master）將對數據的操作記錄到二進制日誌文件（Binary log）中，也就是說，在每個事務更新數據完成之前，mster在日誌里記錄這些改變，mysql將事務串行地寫入二進制文件中，在事件寫入二進制日誌完成後，master通知存儲引擎提交事務，提交好事務後，就會進入第二步，需要補充一點的是，我們對數據的一次操作就稱為一次二進制日誌事件，也就是Binary log event。在第二步呢，Slave將master的Binary log拷貝到它的中繼日誌（Relay log）中，也就是Slave會首先開啟一個工作線程（I/O thread）,I/O線程在master上打開一個普通的連接，做Binary log的拷貝，從master的二進制日誌文件中讀取事件，如果已經跟上了master，就會睡眠並等待master產生新的事件，I/O線程然後將這些事件寫入中繼日誌中；第三步就是Slave重做中繼日誌事件，SQL線程從中繼日誌中讀取事件，並重放其中的事件，更新Slave中的數據，使其與Master中的數據一致。

主從庫配置

  我在自己VMware虛擬機上裝了兩個centOs系統，使用命令：# cat /etc/redhat-release 可以看到我自己的CentOs版本為7.6.1810的

CentOS7.6安裝mysql5.7.31

1. 解壓到/usr/local目錄
# tar -zxvf mysql-5.7.31.tar.gz -C /usr/local

2. mysql-5.7.31-linux-glibc2.5-i686文件夾重命名為mysql
# cd /usr/local
# mv mysql-5.7.18-linux-glibc2.5-i686/ mysql

3. 新建mysql用戶組和mysql用戶
# groupadd mysql
# useradd -r -g mysql mysql

4. 新建數據目錄
# cd /usr/local/mysql
# mkdir data

5. 更改所有者以及授權755
# cd /usr/local
# chown -R mysql:mysql mysql/
# chmod -R 755 mysql/

6. 初始化mysqld
# cd /usr/local/mysql
# ./bin/mysqld –user=mysql –basedir=/usr/local/mysql –datadir=/usr/local/mysql/data –initialize

如果執行初始化時出現報錯：./bin/mysqld: error while loading shared libraries: libnuma.so.1: cannot open shared object file: No such file or directory，就是少東西，centos使用yum裝yum -y install numactl，裝了後在執行初始化命令就可以了。

如果出現錯誤：initialize specified but the data directory has files in it. Aborting.大意是提示data目錄有文件，把mysql安裝目錄\data文件夾里的內容清空（建議讀者備份到其他地方）。再次執行”mysqld –initialize”，沒有報錯了！

記錄下root的初始密碼: Wg;iqpl_P5os

7. 將mysqld添加成服務，並啟動它
# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
建立mysql默認的配置文件/etc/my.cnf，並添加圖片中內容
# vim /etc/my.cnf

按esc，輸入 :wq，保存並退出my.cnf，啟動mysqld
# service mysqld start
查看mysql是否啟動成功
# ps -ef|grep mysql
出現如下信息表示啟動成功

當然也可以查看mysqld的狀態
# service mysqld status

8. 登錄mysql並修改root密碼
# cd /usr/local/mysql
# ./bin/mysql -uroot –p
輸入初始密碼，步驟6中有生成，出現如下信息表示登錄成功

修改root密碼
mysql> SET PASSWORD = PASSWORD(‘123456’);
mysql> FLUSH PRIVILEGES;
初次登錄沒有修改root的密碼，操作數據庫會出現如下錯誤提示，那麼需要修改root用戶的密碼
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

9. 設置mysql遠程可訪問（前提是防火牆必須關閉，chkconfig iptables off:設置自動啟動為關閉，service iptables stop:關閉防火牆）
先登錄到mysql
mysql> use mysql
mysql> update user set host = ‘192.168.0.148’ where user = ‘root’;
mysql> FLUSH PRIVILEGES;
192.168.0.148即是可遠程訪問本地mysql的遠程ip，若想任意ip都能訪問本地mysql，那麼只需要將192.168.0.148換成%即可
mysql> update user set host = ‘%’ where user = ‘root’;

MySQL主從庫配置

  本文將ip為192.168.0.188的主機作為master，ip為192.168.0.152的主機作為slave，我用的mysql版本為：5.7.31。當然我們也可以在本機下載兩套mysql軟件，設置不同端口後進行啟動，也是可以配置出主從同步來。
1. master上配置bin-log日誌
修改mysql的配置文件：# vim /etc/my.cnf，加上如下內容，修改配置後需要重啟mysql服務器

log-bin= master-bin  #[必須]啟用二進制日誌
log-bin-index=master-bin.index
server-id         = 4          #[必須]服務器唯一ID，默認是1，最好取ip的後3位
expire-logs-days  = 7           #只保留7天的二進制日誌，以防磁盤被日誌佔滿

測試log_bin是否成功開啟
mysql> show variables like ‘%log_bin%’;
出現下圖，log_bin為ON則表示開啟成功，OFF表示開啟失敗

配置好後，查看主服務器狀態：show master status

2. slave上配置relay-log
配置從服務器:vim /etc/my.cnf，點擊 i 進入編輯模式，添加如下配置：

relay-log-index=slave-relay-bin.index
relay-log=slave-bin
server-id=152 #只要和master的server-id不一樣就可以了

3. 將master和slave聯繫起來，slave需要知道master的地址

  • master的數據庫中建立備份賬號
    repl為用戶名，192.168.0.152代表從數據庫的主機地址，如下表示遠程地址為密碼為123的，用戶名是repl就可以連接到master主機，並為其授予 replication slave 權限。123為master的連接密碼。
    mysql> create user repl; # 創建用戶名為repl的用戶
    mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’192.168.0.152’ IDENTIFIED BY ‘123’;
    mysql> FLUSH PRIVILEGES;
    mysql> use mysql;
    mysql> select user,authentication_string,host from user;
    可看到我們剛創建的備份賬號：

重啟MySQL服務並設置讀取鎖定:登錄mysql]，mysql> flush tables with read lock;讀取鎖定的意思是只能讀取，不能更新，以便獲得一個一致性的快照
查看主服務器上當前的二進制日誌名和偏移量值：

4. 我們已經在主庫配置好了允許從庫訪問主庫的用戶名和密碼，下面我們對slave進行操作：
mysql> change master to master_host=’192.168.0.188′,master_port=3306,master_user=’repl’,master_password=’123′,master_log_file=’master-bin.000001′,master_log_pos=154;
其中的master_log_file，就是主庫中file，在主庫中使用 show master status；就可以看到了。

啟動slave: mysql> start slave;
查看slave從機的狀態(\G表示豎著展示): mysql> show slave status \G

5. 關閉掉主數據庫的讀取鎖定
mysql> unlock tables;
6. 測試
  才開始的時候，我的主庫和從庫中的數據庫是相同的，使用show databases查看，如下:


我現在主庫執行創建庫語句，執行：create database o2o; 我們去從庫查詢，會發現該數據庫會自動同步到從庫，如下:

  通過測試驗證，我們的mysql主從同步就配置好了，這裡需要注意的是主庫的mysql版本不能高於從庫的mysql版本，因為MySQL總的語法是高版本兼容底版本的，如果從庫的版本要比主庫的版本低，可能主庫執行的sql語句，從庫執行就會報錯，導致主從同步失敗；從庫只能讀數據，不能在從庫寫數據，主庫既可以讀數據，也可以寫數據。

Tags:

VirMach 便宜 VPS

QNews

QNews