Mysql Replication - Mysql主从同步复制简单配置

简要介绍Mysql主从同步、切换、备份

1. 配置Master实例

1.1. 初始化Master,开启binlog,设置serverId

默认情况,Mysql有开启了log_bin=binlog,我们可以设置成:

// 主节点ID
server-id = 1

// 开启binlog
log-bin=mysql-bin

1.2. 查看binary logs情况

mysql> show binary logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |       345 | No        |
+------------------+-----------+-----------+
1 row in set (0.00 sec)

1.3. 创建Master的同步账号,提供给到Slave做从同步用

// 创建独立的复制账号
mysql> create user repl_user@'172.%' IDENTIFIED BY 'youpasswd';

// 授权账号复制权限
mysql> grant replication slave on *.* to repl_user@'172.%';

1.4. 将Master的库备份导出,同步到Slave01

// 直接导出
$ mysqldump -u root -p --opt tkstorm_blog > tkstorm_blog.sql

// 配置了.my.cnf的情况
$ mysqldump tkstorm_blog > /tmp/tkstorm_blog.sql

1.5. 查看Master的binlog位置

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      345 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

2. 新增Slave实例并配置与主同步

从官网下载,安装Mysql

2.1. 初始化一个Slave实例 - Slave01

// 从master同步到从
$ scp /tmp/tkstorm_blog.sql  slaveuser@slave01:/tmp

// 创建数据库
$ mysqladmin create tkstorm_blog;

// 导入主库到从实例
$ mysql tkstorm_blog < /tmp/tkstorm_blog.sql

2.2. 修改Slave01配置,设置server id,以及相关中继日志、binlog等,重启从Mysql服务

查看/etc/my.cnf

// 服务器ID
server-id = 2
// binlog日志
log-bin=mysql-bin
// 中继日志
relay-log = mysql-relay-bin

2.3. 在从节点开启从服务,并查看相关状态

// 重启Mysql服务
$ sudo systemctl restart mysqld

// 更改Master
mysql> change master to master_host='172.18.137.39', master_user='repl_user', master_password='youpasswd', master_log_file='mysql-bin.000001', master_log_pos=345;

// 开启Slave
mysql> START SLAVE;

// 查看Slave状态
mysql> SHOW SLAVE STATUS\G

...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...

常见问题,可以基于show slave status\G看到,比如Slave_IO_StateLast_IO_ErrorSlave_SQL_Running_State基本判断可知

3. 停主节点服务,模拟故障转移,提升Slave01为新主

// 停止Master服务
$ sudo systemctl stop mysqld

3.1. 一主多从,先让每个从都完成旧主IO线程中继日志

// 确保所有从slave02\03\04停IO复制线程
mysql> STOP SLAVE IO_THREAD

// 确保所有从slave02\03\04都已处理其中继日志中的所有语句
mysql> SHOW PROCESSLIST

确保所有从站都已处理其中继日志中的所有语句, SHOW PROCESSLIST直到看到 Has read all relay log

3.2. 将slave01从节点状态改成主节点,重置从节点的master状态

当所有从站都已读完中继日志,在Slave01上面,执行STOP SLAVERESET MASTER,让Slave成为新主

// Slave01停主从服务
mysql> STOP SLAVE

// Slave01重置Master,RESET MASTER删除所有现有的二进制日志文件并重置二进制日志索引文件,将服务器重置为开始二进制日志记录之前的状态。
mysql> RESET MASTER

3.3. 在Slave02\03\04,让其他从变更Master节点

// slave02\03\04,先停止Slave
mysql> STOP SLAVE

// 再更改Master到新的slave01,无需指定master_log_pos位置,因为第一个二进制日志文件ID和位置是默认的
mysql> change master to master_host='slave01', master_user='repl_user', master_password='youpasswd', master_log_file='mysql-bin.000001', master_log_pos=345;

// slave02\03\04,再启动Slave
mysql> START SLAVE

4. 重新恢复Master节点的身份

4.1. 将Master启动后,需要先作为slave01的从,完成停服之间的数据同步

// 原有master上面执行,让master与slave01同步完成(追上master中间遗漏的更新)
mysql> change master to master_host='slave01', master_user='repl_user', master_password='youpasswd', master_log_file='mysql-bin.000001', master_log_pos=345;

4.2. 重置Master的binlog后,才能让slave01~04重新跟随Master

master数据同步后,先停止客户端的请求,将Master设置为新主

// 停Slave01,Master为新主,在Master上面执行
mysql> STOP SLAVE
mysql> RESET MASTER

4.3. 将slave01~04,重新跟随master

// slave01\02\03\04,先停止Slave
mysql> STOP SLAVE

// 再更改slave01\02\03\04,到新的Master
mysql> change master to master_host='slave01', master_user='repl_user', master_password='youpasswd', master_log_file='mysql-bin.000001', master_log_pos=345;

// slave02\03\04,再启动Slave
mysql> START SLAVE

5. 常见的问题

5.1. serverID问题

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

原因:change的master的master_host配置错误,或者my.cnf中设定的server-id一致

5.2. 密码插件问题

Last_IO_Error: error connecting to master 'repl_user@xxx:3306' - retry-time: 60 retries: 6 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

// 检测默认认证插件
mysql> show variables like 'default_authentication_plugin';

// 对比当前用户配置的认证插件
mysql> select host,user,plugin from mysql.user;

// 修改用户配置的认证插件
mysql> ALTER USER 'repl_user'@'172.%' IDENTIFIED WITH mysql_native_password BY 'youpasswd.';

5.3. 同步失败,因为手动加入了重复的账户

Last_SQL_Error: Error 'Operation CREATE USER failed for 'repl_user'@'172.%'' on query. Default database: ''. Query: 'CREATE USER 'repl_user'@'172.%' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$HRddxxfasdfadsfT0cqdgvQwa2''

// 基于my.cnf中忽略某些库表的同步
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%

这里也特别注意下,服务的配置,尽量统一处理,不要手动操作设定或更改配置,否则会导致从库同步失败。

还有一个配置slave-skip-errors = all #可以跳过从库错误

6. 参考

  1. https://dev.mysql.com/doc/mysql-replication-excerpt/5.7/en/replication-solutions-switch.html
  2. https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-in-mysql
  3. https://www.redips.net/mysql/add-new-slave/
  4. https://dev.mysql.com/doc/refman/5.7/en/replication-howto-additionalslaves.html
  5. https://blog.csdn.net/vkingnew/article/details/80105323
  6. https://dev.mysql.com/doc/refman/5.7/en/reset-master.html