Master-Slave 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.

Configuration Procedures

Setup on Master

1. 配置主机名解析。

   "/etc/hosts"

   172.17.0.3      db2c998a052d   # master1
   172.17.0.5      2e7566513c36   # slave1

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

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

   [mysqld]
   log-bin
   expire_logs_days=5
   server-id=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 \
   --routines
   --flush-logs > /mnt/e/mysql/`date +%Y%m%d`_backup.sql

Setup on Slave

1. 从库设置 server-id。

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

   [mysqld]
   server-id=2

   重启 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_log_file='38a497b84982-bin.000004', \
   master_log_pos=154;

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

   start slave;
   show slave status\G