mysql的主從復制

MySQL之間數據復制的基礎是二進制日志文件（binary log file）。一臺MySQL數據庫一旦啟用二進制日志后，其作為master，它的數據庫中所有操作都會以“事件”的方式記錄在二進制日志中，其他數據庫 作為slave通過一個I/O線程與主服務器保持通信，并監控master的二進制日志文件的變化，如果發現master二進制日志文件發生變化，則會把 變化復制到自己的中繼日志中，然后slave的一個SQL線程會把相關的“事件”執行到自己的數據庫中，以此實現從數據庫和主數據庫的一致性，也就實現了 主從復制。

實驗環境：

Linux系統版本：Redhat 6.5版本 
系統防火墻關閉：selinux=disabled 
server2：172.25.68.2（Master） 
server3：172.25.68.3（Slave） 

1.master數據庫的搭建

[root@server2 ~]# tar xf mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar

[root@server2 ~]# yum install mysql-community-client-5.7.17-1.el6.x86_64.rpm mysql-community-common-5.7.17-1.el6.x86_64.rpm  mysql-community-libs-5.7.17-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm mysql-community-server-5.7.17-1.el6.x86_64.rpm -y
[root@server2 ~]# /etc/init.d/mysqld start    ##開啟數據庫

[root@server2 ~]# grep password /var/log/mysqld.log      ##查找數據庫的原始密碼

[root@server2 ~]# mysql_secure_installation     ##初始化數據庫,密碼設定為WESTOS.com123
[root@server2 ~]# vim /etc/my.cnf    ##server-id為服務器的默認id，master和slave不能相同,log-bin表示二進制日志

 [root@server2 ~]# /etc/init.d/mysqld restart

[root@server2 ~]# mysql -p

可以看出，master的id為154

mysql> grant replication slave on *.* to repl@'172.25.68.%' identified by 'WESTOS.com123';      ##授權slave以repl身份從172.25.68.0/24網段登陸，對任何數據庫的任何表進行操作，密碼‘WESTOS.com123‘

授權后，id為447

2.slave數據庫的搭建

[root@server3 ~]# tar xf mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar
[root@server3 ~]# yum install mysql-community-client-5.7.17-1.el6.x86_64.rpm mysql-community-common-5.7.17-1.el6.x86_64.rpm  mysql-community-libs-5.7.17-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm mysql-community-server-5.7.17-1.el6.x86_64.rpm -y
[root@server3 ~]# /etc/init.d/mysqld start    ##開啟數據庫
[root@server3 ~]# grep password /var/log/mysqld.log      ##查找數據庫的原始密碼
[root@server3 ~]# mysql_secure_installation     ##初始化數據庫,密碼設定為WESTOS.com123
[root@server3 ~]# vim /etc/my.cnf   

[root@server3 ~]# /etc/init.d/mysqld restart
[root@server3 ~]# mysql -p
mysql> change master to master_host='172.25.68.2',master_user='repl',master_password='WESTOS.com123',master_log_file='mysql-bin.000001',master_log_pos=447; 
mysql> start slave;
mysql> show slave status\G;      ##開啟slave，并查看slave狀態

IO線程和sql線程已開啟

創建westos庫，在westos庫中創建表linux并寫入數據

查看linux表中信息

在slave中登陸mysql，查看自動同步過來的linux表中的信息

3.GTID主從復制

1.配置master

[root@server2 ~]# vim /etc/my.cnf     ##添加的內容表示開啟gtid模塊

 [root@server2 ~]# /etc/init.d/mysqld restart
 [root@server2 ~]# mysql -p

2.配置slave

[root@server3 ~]# vim /etc/my.cnf

[root@server3 ~]# /etc/init.d/mysqld restart
[root@server3 ~]# mysql -p

測試：

在master的westos庫中的linux表中寫入數據

在slave中查看同步到的數據

4.半同步復制

異步復制（Asynchronous replication）

MySQL默認的復制即是異步的，主庫在執行完客戶端提交的事務后會立即將結果返給給客戶端，并不關心從庫是否已經接收并處理，這樣就會有一個問題，主如果crash掉了，此時主上已經提交的事務可能并沒有傳到從上，如果此時，強行將從提升為主，可能導致新主上的數據不完整。

全同步復制（Fully synchronous replication）

指當主庫執行完一個事務，所有的從庫都執行了該事務才返回給客戶端。因為需要等待所有從庫執行完該事務才能返回，所以全同步復制的性能必然會收到嚴重的影響。

半同步復制（Semisynchronous replication）

介于異步復制和全同步復制之間，主庫在執行完客戶端提交的事務后不是立刻返回給客戶端，而是等待至少一個從庫接收到并寫到relay log中才返回給客戶端。相對于異步復制，半同步復制提高了數據的安全性，同時它也造成了一定程度的延遲，這個延遲最少是一個TCP/IP往返的時間。所以，半同步復制最好在低延時的網絡中使用。

1.master配置

[root@server2 ~]# mysql -p     ##安裝半同步模塊

以上設定為臨時的，永久設定如下：

[root@server2 ~]# vim /etc/my.cnf

[root@server2 ~]# /etc/init.d/mysqld restart

2.slave配置

[root@server3 ~]# mysql -p     ##安裝半同步模塊并啟動

重新連接主服務器（半同步才會生效）

3.server2(slave)配置：配置同server2相同

Master上查看是否啟用了半同步：

主要看Rpl_semi_sync_master_clients是否為1，Rpl_semi_sync_master_status是否為ON。若符合則表示班同步復制機制已經正常工作，如果Rpl_semi_sync_master_status為OFF，說明出現了網絡延遲或Slave IO線程延遲。

延時測試：

關掉server2的IO線程

在server1寫入數據

在server2和3查看是否同步

未同步過來，因為設置的延時為10秒，當server1等待10秒后，若還沒有反應則會轉成異步模式

開啟server2的io線程

server3又自動同步過來

當server2的IO線程關閉后，server2和server3的數據都不會與server1同步

只關閉server3的IO線程后，只有server3與server1不同步

5.并行復制

思考：

一般主從復制，有三個線程參與，都是單線程：Binlog Dump（主） ----->IO Thread （從） -----> SQL Thread（從）。復制出現延遲一般出在兩個地方

1）SQL線程忙不過來（可能需要應用數據量較大，可能和從庫本身的一些操作有鎖和資源的沖突；主庫可以并發寫，SQL線程不可以；主要原因）

2）網絡抖動導致IO線程復制延遲（次要原因）。

解決辦法：

　    MySQL從5.6開始有了SQL Thread多個的概念，可以并發還原數據，即并行復制技術。

　　MySQL 5.6中，設置參數slave_parallel_workers = 4(>1)，即可有4個SQL Thread（coordinator線程）來進行并行復制，其狀態為：Waiting for an evant from Coordinator。

但是其并行只是基于Schema的，也就是基于庫的。如果數據庫實例中存在多個Schema，這樣設置對于Slave復制的速度可以有比較大的提升。通常情況下單庫多表是更常見的一種情形，

那基于庫的并發就沒有卵用。其核心思想是：不同schema下的表并發提交時的數據不會相互影響，即slave節點可以用對relay log中不同的schema各分配一個類似SQL功能的線程，

來重放relay log中主庫已經提交的事務，保持數據與主庫一致。

　　在MySQL 5.7中，引入了基于組提交的并行復制（Enhanced Multi-threaded Slaves），設置參數slave_parallel_workers>0并且 global.slave_parallel_type＝‘LOGICAL_CLOCK’，

即可支持一個schema下，slave_parallel_workers個的worker線程并發執行relay log中主庫提交的事務。其核心思想：一個組提交的事務都是可以并行回放（配合binary log group commit）；

slave機器的relay log中 last_committed相同的事務（sequence_num不同）可以并發執行。

[root@server2 ~]# vim /etc/my.cnf

[root@server2 ~]# /etc/init.d/mysqld restart
[root@server2 ~]# mysql -p

其狀態：Waiting for an evant from Coordinator已經存在，說明并行復制已經配置成功！

 

6.斷電后數據庫的恢復

mysql> show binlog events\G可以查看到binlog和pos點

方案一：

mysqldump -p test > test.sql      ## 將庫的內容導入到test.sql文件中

scp test.sql server3:       ##傳到server3

mysql -p < test.sql    ##在server3上恢復test庫

ps:這種方法會DROP之前的內容重新建，可能會導致信息丟失

方案二：

mysqlbinlog 命令，可以使用start time 和stop time進行限定，或--start-position

--help查看用法