下文給大家?guī)碛嘘P(guān)實戰(zhàn)演示MySQL 5.7.17實現(xiàn)主從復制內(nèi)容，相信大家一定看過類似的文章。我們給大家?guī)淼挠泻尾煌兀恳黄饋砜纯凑牟糠职?，相信看完實?zhàn)演示MySQL 5.7.17實現(xiàn)主從復制你一定會有所收獲。
創(chuàng)新互聯(lián)成立于2013年，我們提供高端重慶網(wǎng)站建設公司、成都網(wǎng)站制作、成都網(wǎng)站設計、網(wǎng)站定制、營銷型網(wǎng)站建設、微信平臺小程序開發(fā)、微信公眾號開發(fā)、seo優(yōu)化服務，提供專業(yè)營銷思路、內(nèi)容策劃、視覺設計、程序開發(fā)來完成項目落地，為成都小攪拌車企業(yè)提供源源不斷的流量和訂單咨詢。
主從復制的原理：
分為同步復制和異步復制，實際復制架構(gòu)中大部分為異步復制。 復制的基本過程如下：
1).Slave上面的IO進程連接上Master，并請求從指定日志文件的指定位置（或者從最開始的日志）之后的日志內(nèi)容；
2).Master接收到來自Slave的IO進程的請求后，通過負責復制的IO進程根據(jù)請求信息讀取制定日志指定位置之后的日志信息，返回給Slave 的IO進程。返回信息中除了日志所包含的信息之外，還包括本次返回的信息已經(jīng)到Master端的bin-log文件的名稱以及bin-log的位置；
3).Slave的IO進程接收到信息后，將接收到的日志內(nèi)容依次添加到Slave端的relay-log文件的最末端，并將讀取到的Master端的 bin-log的文件名和位置記錄到master-info文件中，以便在下一次讀取的時候能夠清楚的告訴Master“我需要從某個bin-log的哪個位置開始往后的日志內(nèi)容，請發(fā)給我”；
4).Slave的Sql進程檢測到relay-log中新增加了內(nèi)容后，會馬上解析relay-log的內(nèi)容成為在Master端真實執(zhí)行時候的那些可執(zhí)行的內(nèi)容，并在自身執(zhí)行。
一、環(huán)境準備
操作系統(tǒng)版本：centos 7.2
云服務器架構(gòu)：
Master(主) ip：192.168.2.70 主機名稱：node01 server_id:1
Slave(從) ip：192.168.2.71 主機名稱：node02 server_id:2
Slave(從) ip：192.168.2.5 主機名稱：node03 server_id:3
其他準備：
每臺云服務器配置以下hosts
# vim /etc/hosts
192.168.2.70 node01
192.168.2.71 node02
192.168.2.5 node03
系統(tǒng)時間同步:
#yum install chrony
配置時間同步源：# vi /etc/chrony.conf
# Please consider joining the pool (http://www.pool.ntp.org/join.html).
server 0.rhel.pool.ntp.org iburst
server 1.rhel.pool.ntp.org iburst
# systemctl start chronyd.service #啟動
# chronyc sources -v #同步時間源
關(guān)閉selinux
關(guān)閉firewalld防火墻
準備測試數(shù)據(jù)庫apps.sql
解決依賴關(guān)系:yum -y install gcc gcc-c++ ncurses ncurses-devel cmake bison
先移除:rpm -e --nodeps mariadb-libs-5.5.44-2.el7.centos.x86_64
新建目錄/mydata/data(建議使用lvm2鏡像，實現(xiàn)物理備份)，存放數(shù)據(jù)庫數(shù)據(jù)
mysql 安裝部署請參照http://daisywei.blog.51cto.com/7837970/1896614 (這里不再闡述)
二、SSH密鑰登錄方式設置
在node01節(jié)點192.168.2.70操作：
[root@node01 ~]# ssh-keygen -t rsa [root@node01 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.2.71 [root@node01 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.2.5
在node02節(jié)點192.168.2.71操作：
[root@node02 ~]# ssh-keygen -t rsa [root@node02 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.2.70 [root@node02 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.2.5
在node03節(jié)點192.168.2.5操作：
[root@node03 ~]# ssh-keygen -t rsa [root@node03 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.2.70 [root@node03 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.2.71
三、搭建主從復制架構(gòu)
1、node01、node02、node03安裝mysql,并初始化數(shù)據(jù)庫(省略)
2、默認安裝mysql有隨機生成密碼，node01、node02、node3節(jié)點上數(shù)據(jù)庫設置管理員密碼(測試密碼：111111)，同時創(chuàng)建復制使用的賬號repl(密碼111111)
#授權(quán)192.168.2.%網(wǎng)段主機root用戶訪問任意庫，本地登錄密碼
重慶分公司
重慶分公司
mysql> SET PASSWORD = PASSWORD('111111');
mysql> UPDATE user SET host='192.168.2.%' where user = 'root';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhsot' IDENTIFIED BY '111111';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.2.%' IDENTIFIED BY '111111';
mysql> grant replication slave on *.* to 'repl'@'%' identified by '111111';
mysql> FLUSH PRIVILEGES;3、node01(192.168.2.70)master配置mysql文件my.cnf(修改前建議備份該文件)修改如下內(nèi)容,修改配置后重啟數(shù)據(jù)庫：
[mysqld] datadir = /mydata/data #數(shù)據(jù)存放目錄 socket = /tmp/mysql.sock #socket innodb_file_per_table = ON #開啟獨立的表空間 skip_name_resolve = ON #禁用DNS解析 log_bin = mysql-bin #開啟日志(日志存儲位置盡量不要同數(shù)據(jù)存儲同一磁盤同一目錄，這里測試方便不重新指向) relay_log = relay-bin #開啟中繼日志 (日志存儲位置盡量不要同數(shù)據(jù)存儲同一磁盤同一目錄，這里測試方便不重新指向) binlog-format = row #日志格式 log-slave-updates = true # 配置從云服務器的更新寫入二進制日志 sever_id = 1 #server_id一定要唯一； [root@node01 ~]# systemctl restart mysqld.service #重啟服務
注意：日志格式，binlog的格式也有三種：STATEMENT，ROW，MIXED。
mysql> show variables like 'log_%'; #查看日志是否開啟
binlog-do-db 和 replicate-ignore-db 必須相同
log_bin、relay_log，二進制日志和中繼日志盡量不要跟數(shù)據(jù)存儲放在同一磁盤同一目錄，防止硬盤損壞時日志也丟失
4、node01(192.168.2.70)master導入測試數(shù)據(jù)庫apps.sql
mysql> CREATE DATABASE apps; Query OK, 1 row affected (0.00 sec) mysql> use apps; Database changed mysql> source /home/soft/apps.sql
5、node02(192.168.2.71)slave配置mysql文件my.cnf修改如下內(nèi)容,修改配置后重啟數(shù)據(jù)庫：
[mysqld] datadir = /mydata/data socket = /tmp/mysql.sock #socket innodb_file_per_table = ON skip_name_resolve = ON log_bin = mysql-bin relay_log = relay-bin binlog-format = row log-slave-updates = true sever_id = 2 #relay_log_purge=0 #禁止自動刪除中繼日志(slave配置文件多了下面這兩條），如果是MHA開啟此項 [root@node02 ~]# systemctl restart mysqld.service #重啟服務 [root@node02 ~]#mysql -uroot -p111111 -e "set global read_only=1" #從庫只讀，不建議寫在配置文件中
6、node03(192.168.2.5)slave配置mysql文件my.cnf修改如下內(nèi)容,修改配置后重啟數(shù)據(jù)庫：
[mysqld] datadir = /mydata/data socket = /tmp/mysql.sock #socket innodb_file_per_table = ON skip_name_resolve = ON log_bin = mysql-bin relay_log = relay-bin binlog-format = row log-slave-updates = true sever_id = 3 #relay_log_purge=0 #禁止自動刪除中繼日志(slave配置文件多了下面這兩條），如果是MHA開啟此項 [root@node03 ~]# systemctl restart mysqld.service #重啟服務 [root@node03 ~]#mysql -uroot -p111111 -e "set global read_only=1" #從庫只讀，不建議寫在配置文件中
7、在node01(192.168.2.70)Master上備份一份完整的數(shù)據(jù)：
[root@node01 /]# mysqldump -uroot -p111111 -h292.168.2.70 --master-data=2 --single-transaction -R --triggers -A > /home/soft/all.sql;
說明：
--master-data=2代表備份時刻記錄master的Binlog位置和Position
--single-transaction意思是獲取一致性快照
-R意思是備份存儲過程和函數(shù)
--triggres的意思是備份觸發(fā)器
-A代表備份所有的庫
查看更多信息mysqldump --help
8、在node01(192.168.2.70)Master上創(chuàng)建復制用戶repl（密碼111111），并授權(quán)訪問所有主機:
mysql> grant replication slave on *.* to 'repl'@'%' identified by '111111'; mysql> flush privileges; #刷新緩存
9、查看node01(192.168.2.70)Master主庫備份時的binlog名稱和位置，MASTER_LOG_FILE和MASTER_LOG_POS：
[root@node01 soft]# head -n 30 /home/soft/all.sql | grep 'CHANGE MASTER TO' -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;
10、把node01 Master主庫備份all.sql復制到從庫node01(192.168.2.71)和node3(192.168.2.5)/home/soft/目錄下，并且導入到從庫中
[root@node01 /]# scp /home/soft/all.sql root@192.168.2.71:/home/soft/ [root@node01 /]# scp /home/soft/all.sql root@192.168.2.5:/home/soft/
11、從庫node02，node3導入all.sql，并設置復制參數(shù)
[root@node02 /]# mysql -uroot -p111111 -h292.168.2.71 < /home/soft/all.sql [root@node03 /]# mysql -uroot -p111111 -h292.168.2.5 < /home/soft/all.sql
mysql> CREATE DATABASE apps; mysql> use apps; Database changed mysql> source /home/soft/all.sql
node02和node03，連接mysql，執(zhí)行以下命令：
[root@node02 /]# mysql -uroot -p111111 -h292.168.2.71
mysql> stop slave; #暫停從庫 Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> CHANGE MASTER TO MASTER_HOST='192.168.2.70',MASTER_USER='repl', MASTER_PASSWORD='111111',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=154; Query OK, 0 rows affected, 2 warnings (0.04 sec)
說明：MASTER_HOST #主庫 ，MASTER_USER和MASTER_PASSWORD #復制賬號密碼 ，CHANGE MASTER TO #還原同步文件和日志
mysql> start slave; #啟動復制 Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G #查看復制狀態(tài) *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.2.70 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 154 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 154 Relay_Log_Space: 521 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 4c9775f6-ef61-11e6-9973-5297c04d0733 Master_Info_File: /mydata/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)
======================================================
[root@node03 /]# mysql -uroot -p111111 -h292.168.2.5
mysql> stop slave; #暫停從庫 Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> CHANGE MASTER TO MASTER_HOST='192.168.2.70',MASTER_USER='repl', MASTER_PASSWORD='111111',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=154; Query OK, 0 rows affected, 2 warnings (0.04 sec) 說明：MASTER_HOST #主庫 ，MASTER_USER和MASTER_PASSWORD #復制賬號密碼 ，CHANGE MASTER TO #還原同步文件和日志 mysql> start slave; #啟動復制 Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G #查看復制狀態(tài) *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.2.70 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 154 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 154 Relay_Log_Space: 521 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 4c9775f6-ef61-11e6-9973-5297c04d0733 Master_Info_File: /mydata/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.01 sec)
12、驗證復制是否正常！
############主庫node01 Master插入一行數(shù)據(jù)，并查看Position號
mysql> INSERT INTO apps (app_name,url,country) VALUES ('BAIDU','http://www.baidu.com','CN');
Query OK, 1 row affected (0.00 sec)
mysql> show master status \G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 449
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)#######從庫node02 slave查看是否已經(jīng)同步存在，Position號是否與主庫master一致
mysql> SELECT * from apps; +----+------------+-------------------------+---------+ | id | app_name | url | country | +----+------------+-------------------------+---------+ | 1 | QQ APP | http://im.qq.com/ | CN | | 2 | 微博 APP | http://weibo.com/ | CN | | 3 | 淘寶 APP | https://www.taobao.com/ | CN | | 4 | BAIDU | http://www.baidu.com | CN | +----+------------+-------------------------+---------+ mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.2.70 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 449 #與主庫Position一致 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 615 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 449 Relay_Log_Space: 816 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 4c9775f6-ef61-11e6-9973-5297c04d0733 Master_Info_File: /mydata/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)
########從庫node03 slave查看是否已經(jīng)同步存在，Position號是否與主庫master一致
mysql> SELECT * from apps; +----+------------+-------------------------+---------+ | id | app_name | url | country | +----+------------+-------------------------+---------+ | 1 | QQ APP | http://im.qq.com/ | CN | | 2 | 微博 APP | http://weibo.com/ | CN | | 3 | 淘寶 APP | https://www.taobao.com/ | CN | | 4 | BAIDU | http://www.baidu.com | CN | +----+------------+-------------------------+---------+ mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.2.70 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 449 #與主庫Position保持一致 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 615 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 449 Relay_Log_Space: 816 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 4c9775f6-ef61-11e6-9973-5297c04d0733 Master_Info_File: /mydata/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)
四、主從故障切換
1、確保所有主從數(shù)據(jù)庫都開啟二進制日志
mysql> show variables like 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ 1 row in set (0.01 sec)
2、確保切換時數(shù)據(jù)時從庫都是最新先把主庫node01設為只讀：set global read_only=1;并且刷新一下主庫log-bin日志
mysql> show variables like 'read_only'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | ON | +---------------+-------+ 1 row in set (0.00 sec) mysql> flush logs; Query OK, 0 rows affected (0.02 sec) mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000002 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
注意：這里的file已經(jīng)是mysql-bin.000002不是mysql-bin.000001了，Position號是154，不是上面的449了?。。。?！
3、確認從庫node02，node03的file和pos是否與主庫一致。
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.2.70 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 #注意這里與主庫是否一致 Read_Master_Log_Pos: 154 #注意這里與主庫是否一致 Relay_Log_File: relay-bin.000004 Relay_Log_Pos: 367 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes
4、以上確認主庫node01沒有數(shù)據(jù)更新后，提升node02(192.168.2.71)為主庫，node01變?yōu)閺膸?/p>
1)node02暫停從庫(注意這里也可以暫停node03從庫SLAVE，讓它依然同步node01)
mysql> STOP SLAVE; Query OK, 0 rows affected (0.01 sec)
2)更改node02為主庫master
mysql> RESET MASTER; Query OK, 0 rows affected (0.01 sec) mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
3)關(guān)閉新主庫node02的只讀屬性，并創(chuàng)建一個test01數(shù)據(jù)庫(為后面新鏈接的從庫node01和node03是否正常同步)
mysql> SET GLOBAL read_only=0; Query OK, 0 rows affected (0.00 sec) mysql> CREATE DATABASE test01; Query OK, 1 row affected (0.00 sec)
這時新主庫的Position為以下:
mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 319 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
4)設置node01(原主庫)身份為slave,并且更改鏈接新主庫node02信息(注:MASTER_LOG_POS這時應該是老的154，不是創(chuàng)建新test01庫后的319)，并啟動SLAVE
mysql> RESET SLAVE; Query OK, 0 rows affected (0.00 sec) mysql> CHANGE MASTER TO MASTER_HOST='192.168.2.71',MASTER_USER='repl', MASTER_PASSWORD='111111',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=154; Query OK, 0 rows affected, 2 warnings (0.02 sec) mysql> START SLAVE; mysql> show slave status\G; #查看從庫狀態(tài) *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.2.71 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 319 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 485 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes ………… mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | apps | | mysql | | performance_schema | | sys | | test01 | #test01數(shù)據(jù)也已經(jīng)過來了 +--------------------+ 6 rows in set (0.00 sec)
注意:這時查看node03從庫，Master_Host依然是node01(192.168.2.70)，但測試驗證的數(shù)據(jù)庫test01也依然同步過來了，如果需要更改鏈接新主庫node02，詳見下面操作??！
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.2.70 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 319 #與新主庫node02的Position號也保持一致 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes ………… mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | apps | | mysql | | performance_schema | | sys | | test01 | #新創(chuàng)建的test01庫也同步過來 +--------------------+ 6 rows in set (0.00 sec)
**********注意:修改node03從庫SLAVE身份，鏈接新主庫node02的信息(注:MASTER_LOG_POS也依然老的154)，修改并重新啟動SLAVE*********
mysql> CHANGE MASTER TO MASTER_HOST='192.168.2.71',MASTER_USER='repl', MASTER_PASSWORD='111111',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=154; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> START SLAVE; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.2.71 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 319 #與新主庫node02的Position號也保持一致 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes ………… mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | apps | | mysql | | performance_schema | | sys | | test01 | #新創(chuàng)建的test01庫也同步過來 +--------------------+ 6 rows in set (0.00 sec)
5、再次驗證數(shù)據(jù)，在node02(192.168.2.71)新主庫的apps庫apps表中，再插入一條新記錄，查看是否同步至其他從庫
node02主庫上執(zhí)行以下命令:
mysql> USE apps;
Database changed
mysql> INSERT INTO apps (app_name,url,country) VALUES ('XINLANG','http://www.sina.com.cn','CN');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM apps;
+----+------------+-------------------------+---------+
| id | app_name | url | country |
+----+------------+-------------------------+---------+
| 1 | QQ APP | http://im.qq.com/ | CN |
| 2 | 微博 APP | http://weibo.com/ | CN |
| 3 | 淘寶 APP | https://www.taobao.com/ | CN |
| 4 | BAIDU | http://www.baidu.com | CN |
| 5 | XINLANG | http://www.sina.com.cn | CN |
+----+------------+-------------------------+---------+
5 rows in set (0.00 sec)查看從庫node01、node03節(jié)點，apps表是否也成功插入數(shù)據(jù)，得到如下結(jié)果:
mysql> SELECT * FROM apps; +----+------------+-------------------------+---------+ | id | app_name | url | country | +----+------------+-------------------------+---------+ | 1 | QQ APP | http://im.qq.com/ | CN | | 2 | 微博 APP | http://weibo.com/ | CN | | 3 | 淘寶 APP | https://www.taobao.com/ | CN | | 4 | BAIDU | http://www.baidu.com | CN | | 5 | XINLANG | http://www.sina.com.cn | CN | +----+------------+-------------------------+---------+ 5 rows in set (0.00 sec)
對于上文關(guān)于實戰(zhàn)演示MySQL 5.7.17實現(xiàn)主從復制，大家覺得是自己想要的嗎？如果想要了解更多相關(guān)，可以繼續(xù)關(guān)注我們的行業(yè)資訊板塊。
