一、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/

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

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

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

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/550754.html

標籤：其他

上一篇：Redis - 資料型別映射底層結構

下一篇：返回列表

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上操作