基于GTID 搭建 MySQL主从复制

1、 配置文件

• 主库（master）：配置文件my3306.cnf
  binlog_format = row
  server_id = 3306101
  log_bin = /data/mysql/mysql3306/logs/mysql-bin
  gtid-mode=on
  enforce-gtid-consistency=1
  binlog_cache_size = 4M
  skip-slave-start=1

• 从库 (slave)：配置文件my3308.cnf
  binlog_format = row
  server-id = 3308222
  log-bin = /data/mysql/mysql3308/logs/mysql-bin
  log_slave_updates #用于中继
  read_only=1
  gtid-mode=on
  enforce-gtid-consistency=1
  skip-slave-start=1

2、 主库创建复制账号

mysql> create user 'repl'@'%' identified by 'a';
mysql> grant replication slave on . to 'repl'@'%';

3、 主库备份

mysqldump -uroot -pa -S /tmp/mysql3306.sock -A --master-data=2 --single-transaction -R -E --triggers > /backup/full.sql

4、 从库恢复

mysql -uroot -p -S /tmp/mysql3308.sock </backup/full.sql
或

mysql> set sql_log_bin=0;
mysql> source /backup/full.sql
mysql> set sql_log_bin=1;

5、 从库设置同步

mysql> CHANGE MASTER TO
MASTER_HOST='172.16.215.101',
MASTER_USER='repl',
MASTER_PASSWORD='a',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1,
MASTER_CONNECT_RETRY=10;

6、 从库开启同步

mysql> start slave;
mysql> show slave status\G;