主库为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;`

zhouyuqiang

Mysqldump实现mysql的master-slave主从复制

公告