1.1 MySQL主從復(fù)制原理介紹

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

創(chuàng)新互聯(lián)建站為您提適合企業(yè)的網(wǎng)站設(shè)計?讓您的網(wǎng)站在搜索引擎具有高度排名，讓您的網(wǎng)站具備超強的網(wǎng)絡(luò)競爭力！結(jié)合企業(yè)自身，進行網(wǎng)站設(shè)計及把握，最后結(jié)合企業(yè)文化和具體宗旨等，才能創(chuàng)作出一份性化解決方案。從網(wǎng)站策劃到網(wǎng)站設(shè)計、網(wǎng)站建設(shè)， 我們的網(wǎng)頁設(shè)計師為您提供的解決方案。

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

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

[mysqld]

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

下面針對MySQL主從復(fù)制原理的重點進行小結(jié)。

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

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

◆   實現(xiàn)主從復(fù)制的必要條件是主庫要開啟記錄binlog功能。

◆   作為復(fù)制的所有MySQL節(jié)點的server-id都不能相同。

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

忘了數(shù)據(jù)庫密碼

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

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

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

進入數(shù)據(jù)庫后設(shè)置密碼

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

刷新權(quán)限

flush privileges;

1.2MySQL主從復(fù)制實踐

環(huán)境:多實例

10.0.0.52 3306

10.0.0.52 3307

3306---->3307復(fù)制---->3309

   ---->3008復(fù)制

3306主---->3307從

架構(gòu)實踐：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

重啟服務(wù)

/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 主庫創(chuàng)建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 從主庫導(dǎo)出數(shù)據(jù)

按照我們見過的內(nèi)容，直接取今天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

拿到位置點是關(guān)鍵 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 從庫導(dǎo)入全備的數(shù)據(jù)

[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

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;

打開復(fù)制開關(guān)slave：

mysql> start slave;

Query OK, 0 rows affected (0.03 sec)

mysql> show slave status\G

顯示如下結(jié)果證明主從復(fù)制實踐成功

[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線程狀態(tài)，I/O線程負責(zé)從從庫去主庫讀取binlog日志，并寫入從庫的中繼日志，狀態(tài)為Yes表示I/O線程工作正常。
◆   Slave_SQL_Running: Yes，這個是SQL線程狀態(tài)，SQL線程負責(zé)讀取中繼日志（relay-log）中的數(shù)據(jù)并轉(zhuǎn)化為SQL語句應(yīng)用到從數(shù)據(jù)庫中，狀態(tài)為Yes表示I/O線程工作正常。
◆   Seconds_Behind_Master: 0，這個是在復(fù)制的過程中，從庫比主庫延遲的秒數(shù)，這個參數(shù)很重要，但企業(yè)里更準確地判斷主從延遲的方法為：在主庫寫時間戳，然后從庫讀取時間戳和當前數(shù)據(jù)庫時間的進行比較，從而認定是否延遲。

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主從復(fù)制問題匯總

  故障1：主庫show master status；沒返回狀態(tài)結(jié)果。

mysql> show master status;

Empty set (0.00 sec)

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

[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    |

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

故障二：出現(xiàn)錯誤信息“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'”

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

CHANGE MASTER TO 

MASTER_HOST='172.16.1.52',

MASTER_PORT=3306,

MASTER_USER='rep',

MASTER_PASSWORD='oldboy123',

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

MASTER_LOG_POS=120;

故障三：服務(wù)無法啟動。

故障語句如下：

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

MySQL is running...

[root@db02 ~]# ps -ef |grep mysql   發(fā)現(xiàn)沒有服務(wù)端口號

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

解決：原因是啟動腳本里對mysql.sock是否存在做了判斷，如果存在mysql.sock，就認為服務(wù)運行是個小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   出現(xiàn)問題看日志

1.4 生產(chǎn)環(huán)境下輕松部署MySQL主從復(fù)制

1.4.1 快速配置MySQL主從復(fù)制

步驟如下：

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

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

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

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

5.      在從庫執(zhí)行change master to.....語句，無需binlog文件及對應(yīng)位置點。

6.      從庫開啟同步開關(guān)，start slave。

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

1.4.2 無需熬夜，輕松部署MySQL主從復(fù)制

實戰(zhàn)過程如下：

1）在主庫上通過定時任務(wù)執(zhí)行如下命令，備份導(dǎo)出主庫數(shù)據(jù)：

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

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

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

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

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

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<

 CHANGEMASTER TO 

MASTER_HOST='172.16.1.52',

MASTER_PORT=3306,

MASTER_USER='rep',

MASTER_PASSWORD='oldboy123',

EOF

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

start slave；  #<=====開啟主從復(fù)制開關(guān)

show slave status\G    #<===查看主從復(fù)制狀態(tài)

1.5 MySQL主從復(fù)制線程狀態(tài)說明及用途

1.5.1 MySQL主從復(fù)制I/O線程狀態(tài)說明

1）登錄主數(shù)據(jù)庫查看MySQL線程的同步狀態(tài)

命令如下：

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)

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

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

                         主庫I/O線程工作狀態(tài)

2）登錄從庫數(shù)據(jù)庫查看MySQL線程工作狀態(tài)

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

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

 下表列出了從服務(wù)器的I/O線程的state列的最常見的狀態(tài)，該狀態(tài)也出現(xiàn)在Slave_IO_State列，由SHOW SLAVE STATUS顯示

                             從庫IO線程工作狀態(tài)

                             從庫SQL線程狀態(tài)

  有關(guān)MySQL主從復(fù)制參與線程的狀態(tài)更多信息，請參考MySQL官方手冊。

1.5.2查看MySQL線程同步狀態(tài)的用途

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

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