Linux之MySQL主从复制方式

用户黄岩岛的高楼大厦

概述

1. MySQL

复制代码

的主从复制（

1. Master-Slave Replication

复制代码

）是一种数据复制解决方案，将主数据库的

1. DDL

复制代码

（数据定义语言）和

1. DML

复制代码

（数据操纵语言）操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行（也叫重做），从而是的从库和主库的数据保存同步。

1. MySQL

复制代码

支持将数据从一个

1. MySQL

复制代码

服务器（主服务器）复制到一个或多个其他

1. MySQL

复制代码

服务器（从服务器），从库同时也可以作为其他从服务器的主库，实现链状复制。

1. MySQL

复制代码

主从复制的优点主要包含以下三个方面：
主库出现问题，可以快速切换到从库提供服务；实现读写分离，降低主库的访问压力；可以在从库中执行备份，以避免备份期间影响主库服务；
需要注意的是，

1. MySQL

复制代码

的主从复制是异步的，这意味着从服务器的数据可能会与主服务器的数据存在一定的延迟。因此，在使用主从复制时，需要根据具体的业务场景和需求来选择合适的配置和策略。
工作原理

从上图来看，主从复制分成三步：

• 1. Master

  复制代码
  主库在事务提交时，会把数据变更记录在二进制日志文件
  1. Binlog

  复制代码
  中；
  
• 从库读取主库的二进制日志文件
  1. Binlog

  复制代码
  ，写入到从库的中继日志
  1. Relay Log

  复制代码
  ；
  
• 1. Slave

  复制代码
  重做中继日志中的事件，将改变数据更新同步到从库中；
  

说白了就是

1. Master

复制代码

主库上执行的增删改的

1. SQL

复制代码

语句同步到对应的

1. Slave

复制代码

从库上，然后再在

1. Slave

复制代码

从库中同样再次执行一遍

1. SQL

复制代码

语句以作备份。

综合案例


前期准备

准备两台虚拟机，需要提前安装好

1. MySQL

复制代码

数据库（必须要开启二进制日志）。
如下所示：
主从库IP地址主库192.168.111.135从库192.168.111.137
注意：以上只是示例说明，具体以自己的虚拟机情况为主。
例外如果克隆的两台虚拟机IP地址一致，可根据以下操作修改实现动态ip（基于mac地址发配IP）
切换目录到：/etc/netplan 并且编辑00-installer-config.yaml文件

如下图指定位置加入：dhcp-identifier: mac(严格缩进格式要求)

重启网络刷新修改：netplan apply

主库配置

修改主库服务器的

1. MySQL

复制代码

核心配置文件

1. /etc/mysql/mysql.conf.d/mysqld.cnf

复制代码

，并添加如下配置信息（开启二进制日志）：

1. [mysqld]
   
2. ...
   
3. # 开启二进制日志（必须）
   
4. log-bin = mysql-bin
   
5. # MySQL服务ID，保证整个集群环境中唯一，默认为1（必须）
   
6. server-id = 1
   
7. # 二进制日志格式，默认ROW（可选）
   
8. binlog_format = ROW
   
9. # 忽略的数据，不需要同步的数据库
   
10. # binlog-ignore-db = db1
    
11. # binlog-ignore-db = db2
    
12. # 指定同步的数据库
    
13. # binlog-do-db = db3

复制代码

• 注意：这里
  1. binlog-ignore-db

  复制代码
  和
  1. binlog-do-db

  复制代码
  配置项没有指定，默认同步所有数据库信息。
  
• 从 MySQL 5.7 开始，
  1. binlog-ignore-db

  复制代码
  的优先级高于
  1. binlog-do-db

  复制代码
  。这意味着即使某个数据库被
  1. binlog-do-db

  复制代码
  指定，如果它同时出现在
  1. binlog-ignore-db

  复制代码
  的列表中，那么它的更改将不会被记录到二进制日志中
  

重启

1. MySQL

复制代码

服务器。

1. systemctl restart mysql

复制代码

（追求安全，否则可跳过）登录

1. MySQL

复制代码

数据库，创建远程连接的账号，并授予主从复制权限。

1. # 创建xx用户，并设置密码，该用户可在任意主机连接该MySQL服务
   
2. create usxx'@'%' identified with mysql_native_password by 'xx1234';
   
3. # 为'xx'@'%'用户分配主从复制权限
   
4. grant replication slave on *.* to 'zking'@'%';

复制代码

通过指令，查看二进制日志坐标

1. show master status;

复制代码

从库配置

1）修改从库服务器的

1. MySQL

复制代码

核心配置文件

1. /etc/mysql/mysql.conf.d/mysqld.cnf

复制代码

，并添加如下配置信息：

1. [mysqld]
   
2. ...
   
3. # 开启二进制日志（必须）
   
4. log-bin = mysql-bin
   
5. # MySQL服务ID，保证整个集群环境中唯一，默认为1（必须）
   
6. server-id = 2
   
7. # 二进制日志格式，默认ROW（可选）
   
8. binlog_format = ROW
   
9. # 是否只读，1代表只读，0代表读写
   
10. read-only = 1

复制代码

2）重启

1. MySQL

复制代码

服务器。

1. systemctl restart mysql

复制代码

3）登录

1. MySQL

复制代码

数据库，设置主库配置。

• 1. MySQL8.0.23

  复制代码
  之前的版本，执行如下
  1. SQL

  复制代码
  语句：
  

1. change master to master_host='xxx.xxx.xxx.xxx',master_user='xxx',master_password='xxx',master_log_file='xxx',master_log_pos=xxx;
   
2. change master to master_host='192.168.111.135',master_user='root',master_password='123',master_log_file='mysql_bin.000008',master_log_pos=2756;

复制代码

• 1. MySQL8.0.23

  复制代码
  之后的版本，执行如下
  1. SQL

  复制代码
  语句：
  

1. change replication source to source_host='xxx.xxx.xxx.xxx',source_user='xxx',source_password='xxx',source_log_file='xxx',source_log_pos=xxx;

复制代码

参数说明：
参数名含义8.0.23之前source_host主库IP地址master_hostsource_user连接主库的用户名master_usersource_password连接主库的密码master_passwordsource_log_filebinlog日志文件名master_log_filesource_log_posbinlong日志文件位置master_log_pos4）开启同步操作

1. # 8.0.22之后
   
2. start replica;
   
3. # 8.0.22之前
   
4. start slave;

复制代码

5）查看主从同步状态

1. # 8.0.22之后
   
2. show replica status;
   
3. # 8.0.22之前
   
4. show slave status;

复制代码

格式化显示：show slave status\G;

上述图中显示

1. Slave_IO_Running: No

复制代码

，很明显主从复制开启失败。经过问题分析之后，发现是虚拟机是克隆的，导致主库和从库的

1. MySQL

复制代码

的

1. server id

复制代码

都是一样的。
解决方案：修改任意主库和从库的

1. server id

复制代码

即可解决问题。
修改

1. /var/lib/mysql/auto.cnf

复制代码

文件。将

1. server-uuid

复制代码

属性修改为唯一值即可。

1. [auto]
   
2. server-uuid = 任意uuid

复制代码

方案二：

• 停止mysql服务
  
• 删除auto.cnf
  
• 启动mysql服务
  

修改完毕保存并退出，最后重启

1. MySQL

复制代码

服务后，并再次登录

1. MySQL

复制代码

查看主从复制是否成功。

数据测试

1）登录主库

1. MySQL

复制代码

，并执行以下

1. SQL

复制代码

语句：

1. # 切换数据库
   
2. use db1;
   
3. # 创建数据表t_student
   
4. create table t_student(sid int primary key auto_increment,sname varchar(20) not null,sage int default 0,ssex varchar(2) default '1');
   
5. # 批量添加数据
   
6. insert into t_student(sname,sage,ssex) values('张三',26,'男'),('王五',22,'女'),('小七',23,'女');

复制代码

2）登录从库

1. MySQL

复制代码

，查看主从复制结果：

1. # 切换数据库
   
2. use db1;
   
3. # 查看是否存在t_student表
   
4. show tables;
   
5. # 查看t_student表中是否存在数据
   
6. select * from t_student;

复制代码

存在数据即

1. MySQL

复制代码

主从复制同步成功（主库操作，从库也会有）。

异常处理

1. # 授权&创建用户
   
2. mysql> grant select,insert,file on test.* to test@'%' identified by '123';
   
3. ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
   
4. mysql> use mysql;
   
5. Reading table information for completion of table and column names
   
6. You can turn off this feature to get a quicker startup with -A
   
7. ​
   
8. Database changed
   
9. mysql> select host,user from user;(test并没有权限)
   
10. +-----------+---------------+
    
11. | host      | user          |
    
12. +-----------+---------------+
    
13. | %         | root          |
    
14. | %         | test          |
    
15. | localhost | mysql.session |
    
16. | localhost | mysql.sys     |
    
17. +-----------+---------------+
    
18. 4 rows in set (0.00 sec)
    
19. mysql> show grants for test;
    
20. +----------------------------------+
    
21. | Grants for test@% |
    
22. +----------------------------------+
    
23. | GRANT USAGE ON *.* TO 'test'@'%' |【为默认权限，所有用户都有】
    
24. +----------------------------------+
    
25. 1 row in set (0.00 sec)
    
26. 
    
27. mysql> grant select,insert on test.* to test@'%' identified by '123';
    
28. 
    
29. Query OK, 0 rows affected, 1 warning (0.00 sec)
    
30. mysql> show grants for test;
    
31. +------------------------------------------------+
    
32. | Grants for test@% |
    
33. +------------------------------------------------+
    
34. | GRANT USAGE ON *.* TO 'test'@'%' |
    
35. | GRANT SELECT, INSERT ON `test`.* TO 'test'@'%' |
    
36. +------------------------------------------------+
    
37. 2 rows in set (0.00 sec)

复制代码

在创建用户时对

1. test

复制代码

库授予 SELECT、INSERT、FILE 权限，因 FILE 权限不能授予某个数据库而导致语句执行失败。
但最终结果是：

1. test@'%'

复制代码

创建成功，授权部分失败。
从上面的测试可知，使用 GRANT 创建用户其实是分为两个步骤：创建用户和授权。
权限有问题并不影响用户的创建，上述语句会导致主库在 binlog 写 INCIDENT_EVENT，从而导致主从复制报错
故障解决

1. mysql> stop slave;
   
2. mysql> set global sql_slave_skip_counter=1; #指定跳过事务个数
   
3. mysql> start slave;

复制代码

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持脚本之家。

来源:https://www.jb51.net/database/330772ej7.htm
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！