Master-Slave GTID Replication on MySQL 5.7.13

Architecture

1. All database operations are copied to the master’s binary log.
2. Salves connect to the master and asks for the data.
3. The slave servers get the masters binary log.
4. Slaves then apply the binary log to its realy log.
5. The relay log is read by the SQL thread process and it applies all the operations/data to the slave’s database and its binary log.

Setup on Master

1. 配置主机名解析。

   "/etc/hosts"

   172.17.0.3      38a497b84982   # master
   172.17.0.4      c40f021e17ea   # slave

2. 主库开启二进制日志，设置 server-id。

   "/etc/mysql/conf.d/master.cnf"

   [mysqld]
   log-bin
   expire_logs_days=5
   server-id=1
   gtid_mode=ON
   enforce_gtid_consistency=1

   重启 MySQL

   systemctl restart mysqld

3. 主库创建复制用户，授予复制权限。

   create user 'repl'@'172.17.0.%' identified by 'welcome';
   grant replication slave, replication client on *.* to 'repl'@'172.17.0.%';
   flush privileges;

4. 主库备份数据。

   mysqldump -uroot -p'welcome' \
   --all-databases \
   --single-transaction \
   --master-data=1 \
   --flush-logs > `date +%Y%m%d`_backup.sql

Setup on Slave

1. 从库开启二进制日志，设置 server-id。

   "/etc/mysql/conf.d/slave.cnf"

   [mysqld]
   log-bin
   expire_logs_days=5
   server-id=2
   gtid_mode=ON
   enforce_gtid_consistency=1
   master-info-repository=TABLE
   relay-log-info-repository=TABLE

   重启 MySQL

   systemctl restart mysqld

2. 从库导入数据。

   mysql -uroot -pwelcome < 20240119_backup.sql

   也可使用 source 方式导入

   set sql_log_bin=0
   source 20240119_backup.sql

3. 从库启动复制线程。

   change master to \
   master_host='38a497b84982', \
   master_user='repl', \
   master_password='welcome', \
   master_auto_position=1;

   使用 source 导入时，不需要再指定 master_log_file 和 master_log_pos 参数。

   start slave;
   show slave status\G