基于mysqldump搭建gtid主從

276

在實現mysql主從架構的過程中，可以使用基于mysqldump方式來構建主從。mysqldump在備份的過程中已經產生了GTID的相關信息，即這些GTID可以跳過，對于未跳過的GTID則有IO線程復制到從服務器，由SQL線程進行執行。本文主要演示mysqldump在GTID模式下搭建mysql主從。

有關知識點參考：
配置MySQL GTID 主從復制
基于mysqldump快速搭建從庫
使用mysqldump導出數據庫

一、GTID添加從庫的方法

1.如果master所有的binlog還在，安裝slave后，直接change?master?到master  原理是直接獲取master所有的gtid并執行  優點是簡單  缺點是如果binlog太多，數據完全同步需要的時間較長，并且需要master一開始就啟用了GTID  總結：適用于master也是新建不久的情況    2.通過master或者其它slave的mysqldump備份搭建新的slave.  原理：備份時獲取master的數據和這些數據對應的GTID，在Slave端跳過備份包含的GTID  優點是可以避免第一種方法中的不足  缺點操作相對復雜  總結：適用于擁有較大數據集的情況    3、percona?xtrabackup  基于xtrabackup備份文件xtrabackup_binlog_info包含了GTID信息  做從庫恢復后，需要手工設置：set@@GLOBAL.GTID_PURGED='c8d960f1-83ca-11e5-a8eb-000c29ea831c:1-745497';恢復后，執行change?master?to  缺點操作相對復雜  總結：適用于擁有較大數據集的情況

二、演示從庫搭建

1、演示環境  mysql>?system?cat?/etc/redhat-release  CentOS?release?6.7?(Final)mysql>?show?variables?like?'version';  +---------------+------------+|?Variable_name?|?Value??????|  +---------------+------------+|?version???????|?5.7.12-log?|  +---------------+------------+主服務器：192.168.1.245:3306??server_id?:?245  從服務器：192.168.1.247:3306??server_id?:?247    --在主庫端創建復制用戶  mysql>?GRANT?REPLICATION?SLAVE,?REPLICATION?CLIENT?ON?*.*?TO?'repl'@'%'?IDENTIFIED?BY?'123456';?    2、直接使用change?master(針對本文第一部分，第1小點情形)    此處省略基于gtid配置的參數描述，具體可以參考：配置MySQL?GTID?主從復制  在從服務器端直接change?master，如下：SLAVE>?show?variables?like?'server_id';  +---------------+-------+|?Variable_name?|?Value?|  +---------------+-------+|?server_id?????|?247???|  +---------------+-------+Slave>?CHANGE?MASTER?TO??  ????->?MASTER_HOST='192.168.1.245',????????->?MASTER_USER='repl',????????->?MASTER_PASSWORD='123456',????????->?MASTER_PORT=3306,????????->?MASTER_AUTO_POSITION?=?1;Query?OK,?0?rows?affected,?2?warnings?(0.12?sec)    Slave>?start?slave;  Query?OK,?0?rows?affected?(0.01?sec)    Slave>?start?slave;  Query?OK,?0?rows?affected?(0.01?sec)    Slave>?show?slave?status?G***************************?1.?row?***************************???????????????Slave_IO_State:?Waiting?for?master?to?send?event??????????????????Master_Host:?192.168.1.245??????????????????Master_User:?repl??????????????????Master_Port:?3306????????????????Connect_Retry:?60??????????????Master_Log_File:?node3-binlog.000001??????????Read_Master_Log_Pos:?457???????????????Relay_Log_File:?node5-relay-bin.000002????????????????Relay_Log_Pos:?676????????Relay_Master_Log_File:?node3-binlog.000001?????????????Slave_IO_Running:?Yes????????????Slave_SQL_Running:?Yes??????????????...............--主服務器端操作如下  Master>?create?database?tempdb;  Query?OK,?1?row?affected?(0.02?sec)    Master>?use?tempdb  Database?changed  Master>?create?table?t1(id?int,ename?varchar(20));  Query?OK,?0?rows?affected?(0.09?sec)    Master>?insert?into?t1?values(1,'leshami');  Query?OK,?1?row?affected?(0.08?sec)    --從服務器端驗證Slave>?select?*?from?tempdb.t1;  +------+---------+|?id???|?ename???|  +------+---------+|????1?|?leshami?|  +------+---------+1?row?in?set?(0.01?sec)    3、基于mysqldump搭建gtid從庫?  --準備環境，從庫端執行  Slave>?stop?slave;??????????--停止重庫  Query?OK,?0?rows?affected?(0.01?sec)    Slave>?reset?slave?all;?????--重置主從配置信息  Query?OK,?0?rows?affected?(0.02?sec)???    --準備環境，主庫端執行??  Master>?source?sakila-db/sakila-schema.sql??--導入mysql自帶的sakila數據庫  Master>?source?sakila-db/sakila-data.sql????--填充數據???    --使用mysqldump導出數據庫??  #?mysqldump?--all-databases?--single-transaction?--triggers?--routines?--events?  >?--host=localhost?--port=3306?--user=root?--password=MyP@ssw0rd?>/tmp/alldb.sql????????    --導出的文件中已經包含了GTID_PURGED的信息  #?grep?GTID_PURGED?/tmp/alldb.sql???  SET?@@GLOBAL.GTID_PURGED='78336cdc-8cfb-11e6-ba9f-000c29328504:1-38';--將備份文件copy到從服務器  #?scp?/tmp/alldb.sql?192.168.1.247:/tmp--?執行reset?master，重置從服務器上的binlog  Slave>?reset?master;  Query?OK,?0?rows?affected?(0.03?sec)    Slave>?source?/tmp/alldb.sqlSlave>?show?databases;????--此時tempdb已產生  +--------------------+|?Database???????????|  +--------------------+|?information_schema?|  |?mysql??????????????|  |?performance_schema?|  |?sakila?????????????|  |?sys????????????????||?tempdb?????????????|  +--------------------+--執行change?master  Slave>?CHANGE?MASTER?TO??  ????->?MASTER_HOST='192.168.1.245',????????->?MASTER_USER='repl',????????->?MASTER_PASSWORD='123456',????????->?MASTER_PORT=3306,????????->?MASTER_AUTO_POSITION?=?1;Query?OK,?0?rows?affected,?2?warnings?(0.06?sec)    Slave>?start?slave;  Query?OK,?0?rows?affected?(0.00?sec)    Slave>?show?slave?status?G***************************?1.?row?***************************???????????????Slave_IO_State:?Waiting?for?master?to?send?event??????????????????Master_Host:?192.168.1.245??????????????????Master_User:?repl??????????????????Master_Port:?3306????????????????Connect_Retry:?60??????????????Master_Log_File:?node3-binlog.000001??????????Read_Master_Log_Pos:?25637???????????????Relay_Log_File:?node5-relay-bin.000002????????????????Relay_Log_Pos:?423????????Relay_Master_Log_File:?node3-binlog.000001?????????????Slave_IO_Running:?Yes????????????Slave_SQL_Running:?Yes--主庫端執行一些事務  Master>?alter?table?tempdb.t1?modify?ename?varchar(50);  Query?OK,?0?rows?affected?(0.05?sec)  Records:?0??Duplicates:?0??Warnings:?0    Master>?insert?into?tempdb.t1?values(2,'http://blog.csdn.net/leshami');  Query?OK,?1?row?affected?(0.02?sec)    --從庫端驗證結果Slave>?desc?tempdb.t1;  +-------+-------------+------+-----+---------+-------+|?Field?|?Type????????|?Null?|?Key?|?Default?|?Extra?|  +-------+-------------+------+-----+---------+-------+|?id????|?int(11)?????|?YES??|?????|?NULL????|???????||?ename?|?varchar(50)?|?YES??|?????|?NULL????|???????|  +-------+-------------+------+-----+---------+-------+2?rows?in?set?(0.00?sec)Slave>?select?*?from?tempdb.t1;  +------+------------------------------+|?id???|?ename????????????????????????|  +------+------------------------------+|????1?|?leshami??????????????????????||????2?|?http://www.php.cn/?|  +------+------------------------------+

在實現mysql主從架構的過程中，可以使用基于mysqldump方式來構建主從。mysqldump在備份的過程中已經產生了GTID的相關信息，即這些GTID可以跳過，對于未跳過的GTID則有IO線程復制到從服務器，由SQL線程進行執行。本文主要演示mysqldump在GTID模式下搭建mysql主從。

有關知識點參考：
配置MySQL GTID 主從復制
基于mysqldump快速搭建從庫
使用mysqldump導出數據庫

一、GTID添加從庫的方法

1.如果master所有的binlog還在，安裝slave后，直接change?master?到master  原理是直接獲取master所有的gtid并執行  優點是簡單  缺點是如果binlog太多，數據完全同步需要的時間較長，并且需要master一開始就啟用了GTID  總結：適用于master也是新建不久的情況    2.通過master或者其它slave的mysqldump備份搭建新的slave.  原理：備份時獲取master的數據和這些數據對應的GTID，在Slave端跳過備份包含的GTID  優點是可以避免第一種方法中的不足  缺點操作相對復雜  總結：適用于擁有較大數據集的情況    3、percona?xtrabackup  基于xtrabackup備份文件xtrabackup_binlog_info包含了GTID信息  做從庫恢復后，需要手工設置：set@@GLOBAL.GTID_PURGED='c8d960f1-83ca-11e5-a8eb-000c29ea831c:1-745497';恢復后，執行change?master?to  缺點操作相對復雜  總結：適用于擁有較大數據集的情況

二、演示從庫搭建

1、演示環境  mysql>?system?cat?/etc/redhat-release  CentOS?release?6.7?(Final)mysql>?show?variables?like?'version';  +---------------+------------+|?Variable_name?|?Value??????|  +---------------+------------+|?version???????|?5.7.12-log?|  +---------------+------------+主服務器：192.168.1.245:3306??server_id?:?245  從服務器：192.168.1.247:3306??server_id?:?247    --在主庫端創建復制用戶  mysql>?GRANT?REPLICATION?SLAVE,?REPLICATION?CLIENT?ON?*.*?TO?'repl'@'%'?IDENTIFIED?BY?'123456';?    2、直接使用change?master(針對本文第一部分，第1小點情形)    此處省略基于gtid配置的參數描述，具體可以參考：配置MySQL?GTID?主從復制  在從服務器端直接change?master，如下：SLAVE>?show?variables?like?'server_id';  +---------------+-------+|?Variable_name?|?Value?|  +---------------+-------+|?server_id?????|?247???|  +---------------+-------+Slave>?CHANGE?MASTER?TO??  ????->?MASTER_HOST='192.168.1.245',????????->?MASTER_USER='repl',????????->?MASTER_PASSWORD='123456',????????->?MASTER_PORT=3306,????????->?MASTER_AUTO_POSITION?=?1;Query?OK,?0?rows?affected,?2?warnings?(0.12?sec)    Slave>?start?slave;  Query?OK,?0?rows?affected?(0.01?sec)    Slave>?start?slave;  Query?OK,?0?rows?affected?(0.01?sec)    Slave>?show?slave?status?G***************************?1.?row?***************************???????????????Slave_IO_State:?Waiting?for?master?to?send?event??????????????????Master_Host:?192.168.1.245??????????????????Master_User:?repl??????????????????Master_Port:?3306????????????????Connect_Retry:?60??????????????Master_Log_File:?node3-binlog.000001??????????Read_Master_Log_Pos:?457???????????????Relay_Log_File:?node5-relay-bin.000002????????????????Relay_Log_Pos:?676????????Relay_Master_Log_File:?node3-binlog.000001?????????????Slave_IO_Running:?Yes????????????Slave_SQL_Running:?Yes??????????????...............--主服務器端操作如下  Master>?create?database?tempdb;  Query?OK,?1?row?affected?(0.02?sec)    Master>?use?tempdb  Database?changed  Master>?create?table?t1(id?int,ename?varchar(20));  Query?OK,?0?rows?affected?(0.09?sec)    Master>?insert?into?t1?values(1,'leshami');  Query?OK,?1?row?affected?(0.08?sec)    --從服務器端驗證Slave>?select?*?from?tempdb.t1;  +------+---------+|?id???|?ename???|  +------+---------+|????1?|?leshami?|  +------+---------+1?row?in?set?(0.01?sec)    3、基于mysqldump搭建gtid從庫?  --準備環境，從庫端執行  Slave>?stop?slave;??????????--停止重庫  Query?OK,?0?rows?affected?(0.01?sec)    Slave>?reset?slave?all;?????--重置主從配置信息  Query?OK,?0?rows?affected?(0.02?sec)???    --準備環境，主庫端執行??  Master>?source?sakila-db/sakila-schema.sql??--導入mysql自帶的sakila數據庫  Master>?source?sakila-db/sakila-data.sql????--填充數據???    --使用mysqldump導出數據庫??  #?mysqldump?--all-databases?--single-transaction?--triggers?--routines?--events?  >?--host=localhost?--port=3306?--user=root?--password=MyP@ssw0rd?>/tmp/alldb.sql????????    --導出的文件中已經包含了GTID_PURGED的信息  #?grep?GTID_PURGED?/tmp/alldb.sql???  SET?@@GLOBAL.GTID_PURGED='78336cdc-8cfb-11e6-ba9f-000c29328504:1-38';--將備份文件copy到從服務器  #?scp?/tmp/alldb.sql?192.168.1.247:/tmp--?執行reset?master，重置從服務器上的binlog  Slave>?reset?master;  Query?OK,?0?rows?affected?(0.03?sec)    Slave>?source?/tmp/alldb.sqlSlave>?show?databases;????--此時tempdb已產生  +--------------------+|?Database???????????|  +--------------------+|?information_schema?|  |?mysql??????????????|  |?performance_schema?|  |?sakila?????????????|  |?sys????????????????||?tempdb?????????????|  +--------------------+--執行change?master  Slave>?CHANGE?MASTER?TO??  ????->?MASTER_HOST='192.168.1.245',????????->?MASTER_USER='repl',????????->?MASTER_PASSWORD='123456',????????->?MASTER_PORT=3306,????????->?MASTER_AUTO_POSITION?=?1;Query?OK,?0?rows?affected,?2?warnings?(0.06?sec)    Slave>?start?slave;  Query?OK,?0?rows?affected?(0.00?sec)    Slave>?show?slave?status?G***************************?1.?row?***************************???????????????Slave_IO_State:?Waiting?for?master?to?send?event??????????????????Master_Host:?192.168.1.245??????????????????Master_User:?repl??????????????????Master_Port:?3306????????????????Connect_Retry:?60??????????????Master_Log_File:?node3-binlog.000001??????????Read_Master_Log_Pos:?25637???????????????Relay_Log_File:?node5-relay-bin.000002????????????????Relay_Log_Pos:?423????????Relay_Master_Log_File:?node3-binlog.000001?????????????Slave_IO_Running:?Yes????????????Slave_SQL_Running:?Yes--主庫端執行一些事務  Master>?alter?table?tempdb.t1?modify?ename?varchar(50);  Query?OK,?0?rows?affected?(0.05?sec)  Records:?0??Duplicates:?0??Warnings:?0    Master>?insert?into?tempdb.t1?values(2,'http://blog.csdn.net/leshami');  Query?OK,?1?row?affected?(0.02?sec)    --從庫端驗證結果Slave>?desc?tempdb.t1;  +-------+-------------+------+-----+---------+-------+|?Field?|?Type????????|?Null?|?Key?|?Default?|?Extra?|  +-------+-------------+------+-----+---------+-------+|?id????|?int(11)?????|?YES??|?????|?NULL????|???????||?ename?|?varchar(50)?|?YES??|?????|?NULL????|???????|  +-------+-------------+------+-----+---------+-------+2?rows?in?set?(0.00?sec)Slave>?select?*?from?tempdb.t1;  +------+------------------------------+|?id???|?ename????????????????????????|  +------+------------------------------+|????1?|?leshami??????????????????????||????2?|?http://www.php.cn/?|  +------+------------------------------+

以上就是基于mysqldump搭建gtid主從的內容，更多相關內容請關注PHP中文網（www.php.cn）！

? 版權聲明
THE END
數據庫
# 數據庫
喜歡就支持一下吧
點贊6 分享