MySQL主從復制介紹

1.1 MySQL主從復制原理介紹

 MySQL的主從復制是一個異步的復制過程（雖然一般情況下感覺是實時的），數據將從一個MySQL數據庫（我們稱之為Master）復制到另一個MySQL數據庫（我們稱之為Slave），在Master與Slave之間實現整個主從復制的過程是由三個線程參與完成的，其中有兩個線程（SQL線程和IO線程）在Slave端，另外一個線程（I/O線程）在Master端。

  要實現MySQL的主從復制，首先必須打開Master端的binlog記錄功能，否則就無法實現。因為整個復制過程實際上就是Slave從Master端獲取binlog日志，然后在Slave上以相同順序執行獲取的binlog日志中所激勵的各種SQL操作。

  要打開MySQL的binlog記錄功能，可通過在MySQL的配置文件my.cnf的mysql模塊（[mysql]標識后的參數部分）增加“log-bin”參數選項來實現，具體信息如下：

[mysqld]

log-bin = /data/3306/mysql-bin

下面針對MySQL主從復制原理的重點進行小結。

◆   主從復制是異步的邏輯的SQL語句級的復制。

◆   復制時，主庫有一個I/O線程，從庫有兩個線程，I/O和SQL線程。

◆   實現主從復制的必要條件是主庫要開啟記錄binlog功能。

◆   作為復制的所有MySQL節點的server-id都不能相同。

◆   binlog文件只記錄對數據有更改的SQL語句（來自主數據庫內容的變更），不記錄任何查詢（select，show）語句。

忘了數據庫密碼

mysqld_safe --defaults-file=/data/3306/my.cnf--skip-grant-table --user=mysql & 放后臺運行

然后不用輸入密碼進行登錄

mysql -uroot -p -S /data/3306/mysql.sock

進入數據庫后設置密碼

update mysql.user setpassword=password('oldboy123') where user='root' and host='localhost';

刷新權限

flush privileges;

1.2 MySQL主從復制實踐

環境:多實例

10.0.0.52 3306

10.0.0.52 3307

3306---->3307復制---->3309

   ---->3008復制

3306主---->3307從

架構實踐：3306----->3307

1.2.1 開啟主庫binlog,配置server-id※※※※※※

[root@db02 ~]# egrep -i"server-id|log-bin" /data/3306/my.cnf

log-bin = /data/3306/mysql-bin

server-id = 6

重啟服務

/data/3306/mysql restart

從庫

[root@db02 ~]# egrep -i"server-id|log-bin" /data/3307/my.cnf

#log-bin = /data/3307/mysql-bin

server-id = 7

1.2.2 主庫創建rep用戶

grant replication slave on *.* to 'rep'@'172.16.1.%'identified by 'oldboy123';

mysql> grant replication slave on *.* to'rep'@'172.16.1.%' identified by 'oldboy123';

Query OK, 0 rows affected (0.04 sec)

mysql> select user,host from mysql.user;

+------+------------+

| user | host      |

+------+------------+

| root | 127.0.0.1 |

| rep  |172.16.1.% |

1.2.3 從主庫導出數據

按照我們見過的內容，直接取今天00點的備份就可以

1.先鎖表flush table with read lock;

mysql> flush table with read lock;

Query OK, 0 rows affected (0.00 sec)

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000001 |      120 |              |                  |                   |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

2.主庫全備

全備三個重要命令

mysqldump

cp/tar

xtrabackup

拿到位置點是關鍵 sed-n '22p' all_2017-06-28.sql

[root@db02 ~]#mysqldump -B --master-data=2 --single-transaction -S /data/3306/mysql.sock  -A|gzip>/data/backup/all_$(date+%F).sql.gz

[root@db02 ~]# ls -l /data/backup/

總用量 228

-rw-r--r-- 1 root root  178468 6月  28 11:11 all_2017-06-28.sql.gz

3.主庫解鎖

mysql> unlock table;

Query OK, 0 rows affected (0.00 sec)

1.2.4 從庫導入全備的數據

[root@db02 scripts]# cd/data/backup/

[root@db02 backup]# gzip-d all_2017-06-28.sql.gz

[root@db02 backup]#mysql -S /data/3307/mysql.sock <all_2017-06-28.sql

1.2.5 找位置點，然后change master to從庫

[root@db02 backup]# sed-n '22p' all_2017-06-28.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=720;

在從庫3307添加：

CHANGE MASTER TO 

MASTER_HOST='172.16.1.52',

MASTER_PORT=3306,

MASTER_USER='rep',

MASTER_PASSWORD='oldboy123',

MASTER_LOG_FILE='mysql-bin.000001',

MASTER_LOG_POS=720;

打開復制開關slave：

mysql> start slave;

Query OK, 0 rows affected (0.03 sec)

mysql> show slave status\G

顯示如下結果證明主從復制實踐成功

[root@db02 backup]# mysql -S /data/3307/mysql.sock -e "show slavestatus\G"|egrep "_Running|Behind_Master"|head -3

            Slave_IO_Running:Yes

            Slave_SQL_Running:Yes

            Seconds_Behind_Master: 0

#######################################################################################

◆   Slave_IO_Running: Yes，這個是I/O線程狀態，I/O線程負責從從庫去主庫讀取binlog日志，并寫入從庫的中繼日志，狀態為Yes表示I/O線程工作正常。
◆   Slave_SQL_Running: Yes，這個是SQL線程狀態，SQL線程負責讀取中繼日志（relay-log）中的數據并轉化為SQL語句應用到從數據庫中，狀態為Yes表示I/O線程工作正常。
◆   Seconds_Behind_Master: 0，這個是在復制的過程中，從庫比主庫延遲的秒數，這個參數很重要，但企業里更準確地判斷主從延遲的方法為：在主庫寫時間戳，然后從庫讀取時間戳和當前數據庫時間的進行比較，從而認定是否延遲。

1.2.6 登錄3306查看管理的主機

mysql> showslave hosts;

+-----------+------+------+-----------+--------------------------------------+

| Server_id | Host | Port | Master_id |Slave_UUID                           |

+-----------+------+------+-----------+--------------------------------------+

|         7|      | 3307 |         6 |295750c8-54c1-11e7-80dd-000c29fc02ee |

|         8|      | 3308 |         6 | 328e8c80-54c1-11e7-80dd-000c29fc02ee|

+-----------+------+------+-----------+--------------------------------------+

2 rows in set (0.00 sec)

1.3 MySQL主從復制問題匯總

  故障1：主庫show master status；沒返回狀態結果。

mysql> show master status;

Empty set (0.00 sec)

  解答：上述問題原因是binlog功能開關沒開或沒生效。binlog功能開啟正確的配置結果如下：

[root@db02 ~]# grep "log-bin"/data/3306/my.cnf

log-bin = /data/3306/mysql-bin

[root@db02 ~]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock  -e "showvariables like 'log_bin';"

Warning: Using a password on the command lineinterface can be insecure.

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_bin      | ON    |

+---------------+-------+

故障二：出現錯誤信息“Last_IO_Error:Got fatal error 1236 from master when reading datafrom binary log：'Could notfind first log file name in binary log index file'”

解答：上面故障的原因是執行CHANGE MASTER命令時某一個參數的值多了個空格，因而產生錯誤，如下：

CHANGE MASTER TO 

MASTER_HOST='172.16.1.52',

MASTER_PORT=3306,

MASTER_USER='rep',

MASTER_PASSWORD='oldboy123',

MASTER_LOG_FILE=' mysql-bin.000001 ', #<==內容的兩端不能有空格。

MASTER_LOG_POS=120;

故障三：服務無法啟動。

故障語句如下：

[root@db02 ~]# /data/3306/mysql start

MySQL is running...

[root@db02 ~]# ps -ef |grep mysql   發現沒有服務端口號

root      1271   1234  0 08:36 pts/0    00:00:00 grep mysql

解決：原因是啟動腳本里對mysql.sock是否存在做了判斷，如果存在mysql.sock，就認為服務運行是個小bug，讀者可以自行更改啟動腳本解決。

[root@db02 ~]# rm -f /data/3306/mysql.sock  /data/3306/*.pid

[root@db02 ~]# /data/3306/mysql start

Starting MySQL...

[root@db02 ~]# mysql -uroot -poldboy123 -S/data/3306/mysql.sock 登錄

[root@db02 ~]# cat /data/3306/oldboy_3306.err   出現問題看日志

1.4 生產環境下輕松部署MySQL主從復制

1.4.1 快速配置MySQL主從復制

步驟如下：

1.      安裝好要配置從庫的數據庫，配置好log-bin和server-id參數。

2.      無需配置主庫my.cnf文件，主庫的log-bin和server-id參數默認就是配好的

3.      登錄主庫，增加從庫連接主庫同步的賬戶，例如rep，并授權replicationslave同步的權限。

4.      使用曾經在半夜通過mysqldump帶-x和--master-data=1的命令及茶樹定時備份的全備數據備份文件，把它恢復到從庫。

5.      在從庫執行change master to.....語句，無需binlog文件及對應位置點。

6.      從庫開啟同步開關，start slave。

7.      從庫show slave status\G, 檢查同步狀態，并在主庫進行更新測試。

1.4.2 無需熬夜，輕松部署MySQL主從復制

實戰過程如下：

1）在主庫上通過定時任務執行如下命令，備份導出主庫數據：

mysqldump -uroot -poldboy123 -S/data/3306/mysql.sock -A --events -B -x --master-data=1|grep >/opt/$(date +%F).sql.gz

--master-data=1參數會在備份數據里增加如下語句;

-- position to start replication or point-in-timerecovery from

change master tomaster_log_file='mysql-bin.000005',master_log_pos=107;

2) 找機會在需要做復制的從庫上導入全備做從庫，命令如下：

gzip -d 2017-07-08.sql.gz

mysql -uroot -poldboy123 -S /data/3308/mysql.sock<2017-07-08.sql

mysql -uroot -poldboy123 -S /data/3308/mysql.sock<<EOF

 CHANGEMASTER TO 

MASTER_HOST='172.16.1.52',

MASTER_PORT=3306,

MASTER_USER='rep',

MASTER_PASSWORD='oldboy123',

EOF

  這里的change master后面無需指定binlog文件名及具體位置，因為這部分已經在還原數據時提前應用到數據庫里了（備份時--master-data=1的功勞）

start slave；  #<=====開啟主從復制開關

show slave status\G    #<===查看主從復制狀態

1.5 MySQL主從復制線程狀態說明及用途

1.5.1 MySQL主從復制I/O線程狀態說明

1）登錄主數據庫查看MySQL線程的同步狀態

命令如下：

mysql> show processlist\G

*************************** 1. row***************************

     Id: 7

   User: rep

   Host:10.0.0.52:27306

     db:NULL

Command: Binlog Dump

   Time: 538

  State:init

  State:Master has sent all binlog to slave;waiting for binlog to beupdated

Info: NuLL

1 row in set (0.00 sec)

提示：上述狀態的意思是線程已經從binlog日志讀取所有更新，并已經發送到了從數據庫服務器。線程目前為空閑狀態，等待由主服務器上二進制日子中的新事件更新。

  下表列出了主服務器的binlog Dump線程中State列的最常見狀態。如果你沒有在主服務器上看見人和我binlog Dump線程，這說明復制沒有運行，二進制binlog日志由各種事件組成，事件通常會為更新添加新加信息。

                         主庫I/O線程工作狀態

2）登錄從庫數據庫查看MySQL線程工作狀態

從庫有兩個線程，即I/O和SQL線程。從庫I/O線程的狀態如下：

mysql> show processlist\G

*************************** 1. row***************************

     Id: 1

   User:system user

   Host:

     db:NULL

Command: Connect

   Time: 36

  State:Waiting for master to send event

   Info:NULL

 下表列出了從服務器的I/O線程的state列的最常見的狀態，該狀態也出現在Slave_IO_State列，由SHOW SLAVE STATUS顯示

                             從庫IO線程工作狀態

                             從庫SQL線程狀態

  有關MySQL主從復制參與線程的狀態更多信息，請參考MySQL官方手冊。

1.5.2查看MySQL線程同步狀態的用途

  通過MySQL線程同步狀態可以看到同步是否正常進行，故障的位置是什么，另外還可查看數據庫同步是否完成，可用于主庫宕機切換數據庫或者人工數據庫主從切換遷移等。

  例如：主庫宕機，要選擇最快的從庫將其提升為主庫，就需要查看主從庫的線程狀態，如果主從復制在正常情況下進行角色切換，也需要查看主從庫的線程狀態，根據復制狀態確定更新是否完成。