Mysqldump實現mysql的master-slave主從複製

主庫為mydb1，從庫為mydb2，使用mysqldump --master-data、--single-transaction、--all-databases選項將主庫資料匯出，這樣匯出資料是發出mysqldump會話這一刻的資料，再匯入從庫，就算從庫已經存在表也沒關係因為匯出檔案裡面會執行drop table再create table的操作，再透過匯出檔案裡面記錄的二進位制binlog檔名稱和位置進行主從複製的配置



mysqldump --help
--master-data[=#]   
This causes the binary log position and filename to be appended to the output.
If equal to 1, will print it as a CHANGE MASTER command;
if equal to 2, that command will be prefixed with a comment symbol.
This option will turn --lock-all-tables on, unless --single-transaction is specified too (on servers before MariaDB 5.3 this will still take a global read lock for a short time at the beginning of the dump; don't forget to read about --single-transaction below).
In all cases, any action on logs will happen at the exact moment of the dump. Option automatically turns --lock-tables off.   
在匯出的檔案開頭裡面記錄二進位制binlog檔名稱和位置
如果值等於1，就會新增一個CHANGE MASTER語句，1是預設值
如果值等於2，就會在CHANGE MASTER語句前新增註釋
這個引數會--lock-all-tables鎖表，除非你指定了--single-transaction
在所有情況下，對日誌的任何操作都將在轉儲的確切時刻發生。dump結束之後自動關閉--lock-tables。
簡單的說，就是主從複製在做全量備份的時候，這個選項可以自動幫我們鎖表和識別binlog檔案的位置和名稱，匯出結果是發出mysqldump會話這一刻的資料


--single-transaction
Creates a consistent snapshot by dumping all tables in a single transaction.
Works ONLY for tables stored in storage engines which support multiversioning (currently only InnoDB does);
the dump is NOT guaranteed to be consistent for other storage engines. While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log position), no other connection should use the following
statements: ALTER TABLE, DROP TABLE, RENAME TABLE,TRUNCATE TABLE, as consistent snapshot is not isolated from them.
Option automatically turns off --lock-tables.
透過在單個事務中轉儲所有表來建立一致的快照。
只適用於儲存在支援多版本化的儲存引擎中的表(目前只有InnoDB可以);
不能保證轉儲對於其他儲存引擎是一致的。在處理--single-transaction轉儲時，為了確保轉儲檔案有效(正確的表內容和二進位制日誌位置)，其他連線不應該使用以下內容
語句:ALTER TABLE, DROP TABLE, RENAME TABLE,TRUNCATE TABLE，因為一致快照沒有與它們隔離。
--single-transaction會自動關閉--lock-tables。

--single-transaction實際上透過做了下面兩個操作：
1、在mysqldump開始的時候把該session的事務隔離級別設定成repeatable read；
2、然後啟動一個事務（執行bigin），mysqldump備份結束的時候結束該事務（執行commit）
有了這兩個操作，在備份過程中，該session讀到的資料都是啟動mysqldump備份時的資料（同一個點）。可以理解為對於innodb引擎來說加了該引數，備份開始時就已經把要備份的資料定下來了，備份過程中的提交的事務時是看不到的，也不會備份進去。也就是說匯出的資料是mysqldump會話產生這一時刻的，mysqldump過程中DML生成資料不會被匯出來（mysqldump是一張張表來匯出的，--single-transaction是執行mysqldump開始時生效，而不是匯出某張表時，只對這張表生效）




操作步驟
1、主庫的資料庫列表和binlog檔名稱和位置
mysql>  show databases;

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

+--------------------+ | Database           | +--------------------+ | cdt_wp             | | ibdcmsbeta_wp      | | ibdmarketingstg_wp | | information_schema | | mysql              | | performance_schema | | sys                | +--------------------+ mysql>  show master status; +------------------+----------+--------------+------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000031 | 13467727 |              |                  | +------------------+----------+--------------+------------------+

2、從庫的資料庫列表

1 2 3 4 5 6 7 8 9 10 11 12

mysql>  show databases; +--------------------+ | Database           | +--------------------+ | cdt_wp             | | ibdcmsbeta_wp      | | ibdmarketingstg_wp | | information_schema | | mysql              | | performance_schema | | sys                | +--------------------+

3、主庫匯出資料並建立用於同步的賬號
mysqldump -uroot -p --master-data=2 --single-transaction --all-databases --log-error=all.log >alldatabases.sql
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slaveuser'@'%' identified by 'mima123456';

4、從庫匯入，需要先把alldatabases.sql從主庫複製到從庫，再進行復制的配置
[root@mydb2 ~]# head -50 alldatabases.sql
alldatabases.sql檔案中獲取binlog檔名稱和位置，即CHANGE MASTER TO MASTER_LOG_FILE這一行資訊
[root@mydb2 ~]# vi /etc/my.cnf.d/server.cnf
增加read_only=true

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

mysql -uroot -p < alldatabases.sql mysql> show global variables like "%read_only%"; mysql> set global read_only=1; mysql> show global variables like "%read_only%"; mysql> show slave status\G mysql> stop slave; mysql> change master to master_host='mydb1',master_port=3306,master_user='slaveuser', master_password='mima123456', MASTER_LOG_FILE='mysql-bin.000031', MASTER_LOG_POS=13579255; ERROR 1201 (HY000): Could not initialize master info structure for '' mysql> reset slave; mysql> change master to master_host='mydb1',master_port=3306,master_user='slaveuser', master_password='mima123456', MASTER_LOG_FILE='mysql-bin.000031', MASTER_LOG_POS=13579255; mysql> show warnings; mysql> show slave status\G mysql> start slave; mysql> show slave status\G

此外，可以利用GTID(global transaction identifier)的方式進行主從搭建，此方式需要在匯出資料時開啟-set-gtid-purged=on，這樣匯出檔案開頭就多了SET @@GLOBAL.GTID_PURGED='XXX';

1 2	mysqldump -uroot -p --master-data=2 --single-transaction --all-databases  --set-gtid-purged=on --log-error=all.log >alldatabases.sql

從庫匯入資料之後，由於已經有了GTID的資訊，那麼搭建從庫時只需要指定master_auto_position=1即可：

1 2	change master to master_host='mydb1',master_port=3306,master_user='slaveuser', master_password='mima123456', master_auto_position=1;