一、MYSQL主從同步概述 1、什麼是MySQL主從同步？實現數據自動同步的服務結構主伺服器（master）: 接受客戶端訪問連接從伺服器（slave)：自動同步主伺服器數據 2、主從同步原理 Maste：啟用binlog 日誌 Slave：Slave_IO: 複製master主機binlog ...

一、MYSQL主從同步概述

1、什麼是MySQL主從同步？

實現數據自動同步的服務結構

主伺服器（master）: 接受客戶端訪問連接
從伺服器（slave)：自動同步主伺服器數據

2、主從同步原理

Maste：啟用binlog 日誌
Slave：Slave_IO: 複製master主機binlog 日誌文件的SQL命令到本機的relay-log(中繼日誌) 文件里。
Slave_SQL: 執行本機 relay-log(中繼日誌) 文件里的SQL語句，實現與 Master 數據一致。

Master(主伺服器)：

1》開啟binlog日誌，記錄所有除查詢以外的SQL命令

Slave(從伺服器):

1》從伺服器上的I/O thread(讀寫線程) 負責讀取主伺服器binlog日誌中的SQL命令，並將其寫入到 Relay log(中繼日誌中)
2》從伺服器中的SQL thread(SQL 線程)讀取中繼日誌中的SQL命令，並將其寫入到Slave的資料庫中

3、主從同步結構模式

基本應用
單向複製：一主 <—— 一從
擴展應用
一主多從：從 <—— 主 ——> 從
鏈式複製：主 <—— 從 <—— 從
互為主從：主 <——> 主

4、MySQL主從同步配置

拓撲結構

5、實施

步驟一：根據host50克隆虛擬機出host51,配置IP地址為192.168.4.51，主機名為host51，刪除51，52上面多餘的資料庫，只保留預設的四個庫

[root@host51 ~]# mysql -uroot -p123qqq...A
mysql> drop database bbsdb;
[root@host52 ~]# mysql -uroot -p123qqq...A
mysql> drop database db4;

步驟二：host51配置主伺服器

# 修改主配置文件，開啟binlog日誌
[root@host51 ~]# vim /etc/my.cnf
[mysqld]
#binlog_format="mixed"	# 加上註釋
server_id=50				#指定id號，預設與IP地址的主機位相同
log_bin=master51			#指定binlog日誌名，日誌文件在/var/lib/mysql下

步驟三：重啟資料庫服務，讓配置生效

[root@host51 ~]# systemctl restart mysqld
# 用戶授權，給replication slave複製數據的許可權，授權用戶為: repluser
[root@host51 ~]# mysql -uroot -p123qqq...A
mysql> grant replication slave on *.* to repluser@"%" identified by "123qqq...A";
mysql> show master status;

步驟四：host52配置從伺服器

# 修改主配置文件，指定server_id號
[root@host52 ~]# vim /etc/my.cnf
 [mysqld]
server_id=52          #指定id號，預設與IP地址的主機位相同
[root@host52 ~]# systemctl restart mysqld
# 指定主伺服器信息
mysql> change master to master_host="192.168.4.51", master_user="repluser", master_password="123qqq...A", master_log_file="master51.000001", master_log_pos=441;

步驟五：啟動從伺服器

mysql> start slave;		
mysql> show slave status\G;		#確認IO線程、SQL線程都是 Yes 狀態

如果IO線程為NO，錯誤提示如下

這是因，auto.cnf 存放的是主伺服器的uuid號，因為是克隆出的虛擬機，uuid號都一樣，會進行衝突，可以任意修改其中一個或多個字元，重啟資料庫服務即可

[root@host52 ~]# vim /var/lib/mysql/auto.cnf
[auto]
server-uuid=d45877cc-f4ab-11ea-b2a1-000c291f7055
[root@host52 ~]# systemctl restart mysqld

步驟六：host51：測試主從同步

[root@host51 ~]# mysql -uroot -p'123qqq...A'
mysql> create database db1;
mysql> create table db1.user (name char(10));
mysql> insert into db1.user values("tom");

步驟七：在host52上查看

[root@host52 ~]# mysql -uroot -p'123qqq...A'
mysql> show databases;
mysql> select * from db1.user;

記錄主伺服器的信息

[root@host52 ~]# cat /var/lib/mysql/master.info

查看中繼日誌信息

# host52-relay-bin.000001 是中繼日誌文件：記錄從主伺服器拷貝過來的sql命令
# host52-relay-bin.index  是中繼日誌索引文件
[root@host52 ~]# ls /var/lib/mysql/host52*
# 查看中繼日誌文件內容
[root@host52 ~]# cd /var/lib/mysql
[root@host52 mysql]# cat relay-log.info 
7
./host52-relay-bin.000004		#本機正在使用的中繼日誌文件
319							#中繼日誌記錄主伺服器sql命令的偏移量
master51.000001				#中繼日誌從哪個文件中拷貝sql命令(主伺服器)
441                         #此為主伺服器最近的binlog日誌的偏移量
# 查看中繼日誌索引文件，有幾個中繼日誌文件，就記錄幾條
[root@host52 ~]# cd /var/lib/mysql
[root@host52 mysql]# cat host52-relay-bin.index

6、配置MySQL一主多從：解決從伺服器宕機之後，無法備份數據問題

拓撲結構

步驟一：準備資料庫host53，可以直接用之前的host53，刪除創建資料庫，保留預設的四個庫

[root@host53 ~]# mysql -uroot -p123qqq...A
mysql> show databases;
mysql> drop database db4;

步驟二：沒有配置之前，要確保從與主伺服器數據一致

host51：安裝innobackupex相關軟體包

[root@host52 ~]# scp libev-4.15-1.el6.rf.x86_64.rpm  percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm 192.168.4.51:/root/
[root@host51 ~]# rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm 
[root@host51 ~]# yum -y install percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm
[root@host51 ~]# innobackupex --user root --password '123qqq...A' --slave-info  /allbak1  --no-timestamp
[root@host51 ~]# scp -r /allbak1/ 192.168.4.53:/opt/

host53使用innobackupex恢複數據

[root@host53 ~]# systemctl stop mysqld
[root@host53 ~]# rm -rf /var/lib/mysql/*
[root@host53 ~]# innobackupex --apply-log  /root/allbak1/		#準備恢複數據
[root@host53 ~]# innobackupex --copy-back  /root/allbak1/			#恢複數據
[root@host53 ~]# chown -R mysql:mysql /var/lib/mysql
[root@host53 ~]# systemctl start mysqld
[root@host53 ~]# mysql -uroot -p'123qqq...A' -e "show databases"

# xtrabackup_binlog_info 文件記錄的是binlog日誌文件名和偏移量
# 此偏移量和主伺服器的偏移量一致，從伺服器同步數據時從這個偏移量開始同步
# 可以查看host51的binlog日誌狀態，偏移量相同
[root@host53 ~]# cat /opt/alldb/xtrabackup_binlog_info 
master51.000001	1020

步驟三：配置從伺服器

# 修改主配置文件，指定server_id號
[root@host53 ~]# vim /etc/my.cnf
[mysqld]
server_id=53			#指定id號，預設與IP地址的主機位相同
[root@host53 ~]# vim /var/lib/mysql/auto.cnf
[auto]
server-uuid=d45877cc-ffab-11ea-b2a1-000c291f7055
[root@host53 ~]# systemctl restart mysqld
[root@host53 ~]# mysql -uroot -p123qqq...A

步驟四：指定主伺服器信息

mysql> change master to master_host="192.168.4.51", master_user="repluser", master_password="123qqq...A", master_log_file="master51.000001", master_log_pos=1020;
mysql> start slave;				#啟動slave
mysql> show slave status;		#查看從伺服器狀態信息
host51：插入數據
[root@host51 ~]# mysql -uroot -p'123qqq...A'
mysql> insert into db1.user values("tomA"),("tomB"),("tomC");

步驟五：host53測試主從同步

[root@host53 ~]# mysql -uroot -p'123qqq...A'
mysql> select * from db1.user;

二、MYSQL主從從結構

配置MYSQL主從從結構

1、主從從結構

主從從結構優勢：
1》【host55】是【host54】的從伺服器，【host54】是【host53】的從伺服器；
2》當【host53】宕機以後，用戶訪問的是從伺服器【host54】的資料庫；
3》當【host54】宕機以後，用戶訪問的就是從伺服器【host55】的資料庫；

2、主從從結構實驗環境準備，根據host50克隆虛擬機host54和host55主機，配置IP如下，並還原資料庫預設的四個庫

主伺服器     主機名：host53      IP地址: 192.168.4.53
從伺服器     主機名：host54      IP地址: 192.168.4.54
從伺服器     主機名：host55      IP地址: 192.168.4.55

3、將host53恢覆成獨立的資料庫伺服器

[root@host53 ~]# cd /var/lib/mysql
[root@host53 mysql]# rm -rf master.info 			#刪除連接主伺服器的信息文件
[root@host53 mysql]# rm -rf host53-relay-bin.0*		#刪除所有的中繼日誌文件，存放從主伺服器拷貝過來的sql命令
[root@host53 mysql]# rm -rf host53-relay-bin.index 	#刪除中繼日誌的索引文件，按順序記錄所有的中繼日誌文件名
[root@host53 mysql]# rm -rf relay-log.info 			#刪除中繼日誌文件，記錄中繼日誌信息
[root@host53 mysql]# systemctl restart mysqld

4、測試

[root@host53 ~]# mysql -uroot -p'123qqq...A'
mysql> show slave status;		#查看從伺服器狀態信息，為Empty，已經還原
mysql> drop database db1;		#刪除db1庫
mysql> exit

5、host53配置主伺服器

修改主配置文件，開啟binlog日誌
[root@host53 ~]# vim /etc/my.cnf
[mysqld]
server_id=53
log_bin=master53
[root@host53 ~]# systemctl restart mysqld
[root@host53 ~]# mysql -uroot -p123qqq...A

這一次的授權不需要操作，因為之前53主機在做從庫的時候已經同步用戶了

mysql> grant replication slave on *.* to repluser@"%" identified by "123qqq...A"; 
mysql> select user,host from mysql.user;
mysql> show grants for repluser@"%";
mysql> show master status;

6、host54配置從伺服器

修改配置文件、用戶授權、指定主庫信息、啟動slave進程
修改主配置文件，指定server_id號
log_slave_updates 必須開啟級聯複製功能，因為【host54】同步數據是從【host53】的binlog日誌中獲取的，【host54】並沒有直接執行sql命令，所以在【host54】的binlog日誌中並沒有sql命令，那麼【host55】也就無法同步【host54】中的數據；而開啟級聯複製功能，則允許【host55】同步【host54】從【host53】同步過來的數據

[root@host54 ~]# vim /etc/my.cnf
[mysqld]
#binlog_format="mixed"
server_id=54
log_bin=master54
log_slave_updates		#允許級聯複製
[root@host54 ~]# vim /var/lib/mysql/auto.cnf	# auto.cnf 存放的是主伺服器的uuid號 因為是克隆出的虛擬機，uuid號都一樣，會衝突可以任意修改其中一個或多個字元，重啟資料庫服務即可
[auto]
server-uuid=d45877cc-f4ab-12ea-b2a1-000c291f7055
[root@host54 ~]# systemctl restart mysqld

7、連接資料庫，指定主伺服器信息

[root@host54 ~]# mysql -uroot -p123qqq...A
mysql> change master to master_host="192.168.4.53", master_user="repluser", master_password="123qqq...A",master_log_file="master53.000001",master_log_pos=154;
mysql> start slave;
mysql> show slave status\G;

8、host54：從伺服器【host55】添加授權用戶

mysql> grant replication slave on *.* to jim@"%" identified by "123qqq...A";
mysql> show master status;

9、host55配置從伺服器

[root@host55 ~]# vim /etc/my.cnf
[mysqld]
#binlog_format="mixed"
server_id=55
[root@host55 ~]# vim /var/lib/mysql/auto.cnf	
[auto]
server-uuid=d45877cc-f4ab-12ea-b2a1-000c291f7055
[root@host55 ~]# systemctl restart mysqld

10、指定主伺服器信息

[root@host55 ~]# mysql -uroot -p123qqq...A
mysql> change master to master_host="192.168.4.54",master_user="jim", master_password="123qqq...A",master_log_file="master54.000001",master_log_pos=1554;
mysql> start slave;
mysql> show slave status\G;

11、測試主從從同步，在host53上建庫,建表，插入記錄

[root@host53 ~]# mysql -uroot -p'123qqq...A'
mysql> create database bbsdb;
mysql> create table bbsdb.user(name char(11));
mysql> insert into bbsdb.user values("bob");
mysql> select * from bbsdb.user;

12、在host54上查看驗證數據

[root@host54 ~]# mysql -uroot -p'123qqq...A'
mysql> select * from bbsdb.user;

13、在host55上查看驗證數據

[root@host55 ~]# mysql -uroot -p'123qqq...A'
mysql> select * from bbsdb.user;

三、複製模式

複製模式介紹

非同步複製 (預設的複製模式)：Asynchronous replication
主伺服器執行完一次事務後，立即將結果返給客戶端，不關心從伺服器是否已經同步數據。

案例：【host50】(主伺服器) <—— 【host51】(從伺服器)

用戶在【host50】(主伺服器)上執行插入，更新，刪除等SQL命令時，【host50】(主伺服器)直接將結果返回給用戶，不關心【host51】(從伺服器)是否同步數據成功
優點：響應速度快，用戶體驗很好；
缺點：主伺服器宕機後，有可能會存在從伺服器數據丟失的情況；

半同步複製：Semisynchronous replication
主伺服器在執行完一次事務後，等待至少一臺從伺服器同步數據完成，才將結果返回給客戶端。

案例：【host50】(主伺服器) <—— 【host51】(從伺服器)

用戶在【host50】(主伺服器)上執行插入，更新，刪除等SQL命令時，【host50】(主伺服器)不會立刻將結果返回給用戶，而是等待至少一個從伺服器將數據同步寫入到本機的資料庫後，才將結果返回給用戶

優點：主伺服器宕機後，至少有一臺從伺服器擁有和主伺服器相同的數據，數據安全度高；
缺點：響應速度下降，用戶體驗度下降；

配置半同步複製 (主從伺服器都要配置)

主伺服器     主機名：host53      IP地址: 192.168.4.53
從伺服器     主機名：host54      IP地址: 192.168.4.54
從伺服器     主機名：host55      IP地址: 192.168.4.55

命令行載入模塊

host53主伺服器命令行載入半同步複製的master模塊
mysql> show databases;
mysql> desc information_schema.PLUGINS;		#查看預設庫information_schema 下PLUGINS表(模塊表)的表結構
載入 master 模塊，rpl_semi_sync_master模塊類型，semisync_master.so模塊名稱
mysql> install plugin rpl_semi_sync_master SONAME "semisync_master.so";
mysql> select PLUGIN_NAME,PLUGIN_STATUS from information_schema.PLUGINS where PLUGIN_NAME  like "%semi%";

host55安裝slave模塊

mysql>  install plugin rpl_semi_sync_slave SONAME "semisync_slave.so";  
host54因為是即做主又做從，所以要安裝 matser和slave模塊
mysql> install plugin rpl_semi_sync_master SONAME "semisync_master.so";
mysql> install plugin rpl_semi_sync_slave SONAME "semisync_slave.so";
mysql> select PLUGIN_NAME,PLUGIN_STATUS from information_schema.PLUGINS where PLUGIN_NAME  like "%semi%";

啟用模塊

host53：模糊查詢半同步複製模塊是否開啟
mysql> show variables like "%semi%";
mysql> set global rpl_semi_sync_master_enabled=1;		#全局設置，開啟半同步複製模塊
mysql> show variables like "%semi%";					#on開啟

host54啟動半同步複製的master和slave模塊

mysql> set global rpl_semi_sync_master_enabled=1;
mysql> set global rpl_semi_sync_slave_enabled=1;
mysql> show variables like "%semi%";

host55：啟動半同步複製的slave模塊

mysql> set global rpl_semi_sync_slave_enabled=1;
mysql> show variables like "%semi%";

永久配置

主伺服器永久安裝和啟用半同步複製的master模塊

[root@host53 ~]# vim /etc/my.cnf
[mysqld]
plugin-load=rpl_semi_sync_master=semisync_master.so    		#載入master模塊
rpl_semi_sync_master_enabled=1                        		#啟用master模塊
[root@host53 ~]# systemctl restart mysqld
[root@host54 ~]# mysql -uroot -p'123qqq...A'
mysql> show variables like "%semi%";

主從伺服器永久安裝和啟用半同步複製的master和slave模塊,在host54上操作

[root@host54 ~]# vim /etc/my.cnf
[mysqld]
plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"              							#同時載入master和slave模塊
rpl_semi_sync_master_enabled=1				#啟用master模塊
rpl_semi_sync_slave_enabled=1				#啟用slave模塊
[root@host54 ~]# systemctl restart mysqld
[root@host54 ~]# mysql -uroot -p'123qqq...A'
mysql> show variables like "%semi%";

從伺服器永久安裝和啟用半同步複製的slave模塊,在host55上操作

[root@host55 ~]# vim /etc/my.cnf
[mysqld]
plugin-load=rpl_semi_sync_slave=semisync_slave.so    		#載入master模塊
rpl_semi_sync_slave_enabled=1                          #啟用master模塊
[root@host55 ~]# systemctl restart mysqld
[root@host55 ~]# mysql -uroot -p'123qqq...A'
mysql> show variables like '%semi%';

作者：ChAn 出處：http://www.cnblogs.com/sre-chan/

-------------------------------------------

個性簽名：今天做了別人不想做的事，明天你就做得到別人做不到的事，嘗試你都不敢，你拿什麼贏！

如果覺得這篇文章對你有小小的幫助的話，記得在右下角點個“推薦”哦，博主在此感謝！

MYSQL---主從同步概述與配置

一、MYSQL主從同步概述

1、什麼是MySQL主從同步？

2、主從同步原理

Master(主伺服器)：

Slave(從伺服器):

3、主從同步結構模式

4、MySQL主從同步配置

5、實施

步驟一：根據host50克隆虛擬機出host51,配置IP地址為192.168.4.51，主機名為host51，刪除51，52上面多餘的資料庫，只保留預設的四個庫

步驟二：host51配置主伺服器

步驟三：重啟資料庫服務，讓配置生效

步驟四：host52配置從伺服器

步驟五：啟動從伺服器

如果IO線程為NO，錯誤提示如下

步驟六：host51：測試主從同步

步驟七：在host52上查看

相關文件

記錄主伺服器的信息

查看中繼日誌信息

6、配置MySQL一主多從：解決從伺服器宕機之後，無法備份數據問題

步驟一：準備資料庫host53，可以直接用之前的host53，刪除創建資料庫，保留預設的四個庫

步驟二：沒有配置之前，要確保從與主伺服器數據一致

host51：安裝innobackupex相關軟體包

host53使用innobackupex恢複數據

步驟三：配置從伺服器

步驟四：指定主伺服器信息

步驟五：host53測試主從同步

配置MYSQL主從從結構

1、主從從結構

2、主從從結構實驗環境準備，根據host50克隆虛擬機host54和host55主機，配置IP如下，並還原資料庫預設的四個庫

3、將host53恢覆成獨立的資料庫伺服器

4、測試

5、host53配置主伺服器

這一次的授權不需要操作，因為之前53主機在做從庫的時候已經同步用戶了

6、host54配置從伺服器

7、連接資料庫，指定主伺服器信息

8、host54：從伺服器【host55】添加授權用戶

9、host55配置從伺服器

10、指定主伺服器信息

11、測試主從從同步，在host53上建庫,建表，插入記錄

12、在host54上查看驗證數據

13、在host55上查看驗證數據

三、複製模式

複製模式介紹

案例：【host50】(主伺服器) <—— 【host51】(從伺服器)

案例：【host50】(主伺服器) <—— 【host51】(從伺服器)

命令行載入模塊

host55安裝slave模塊

啟用模塊

host54啟動半同步複製的master和slave模塊

host55：啟動半同步複製的slave模塊

永久配置

主伺服器永久安裝和啟用半同步複製的master模塊

主從伺服器永久安裝和啟用半同步複製的master和slave模塊,在host54上操作

從伺服器永久安裝和啟用半同步複製的slave模塊,在host55上操作