MYSQL主从复制

MYSQL主从复制

将主数据库（MASTER）的DDL和DML操作通过二进制日志传到从数据库（SLAVE），然后在从库重新执行（重做），从而使得从库数据和主库的数据保持同步

MYSQL主从复制要点有以下几个方面：

• 主库出现问题，可以快速切换到从库
• 实现读写分离，降低主库访问压力
• 可以在从库中执行备份（备份时会对数据库添加全局锁），避免备份期间影响主库服务
• 降低单库的读写压力

主从复制原理

MYSQL有四种日志

• 错误日志
• 二进制日志
• 查询日志
• 慢查询日志

MYSQL在数据库执行了DDL或增删改时，会将操作记录写入binlog日志

从库中有一组线程IOThread，发起请求连接MASTER数据库，读取binlog日志，读取到日志后，将其写入到SLAVE的relaylog中。然后通过另外一组线程SQLThread，读取relaylog中的数据，对数据操作进行redo，

将主库的操作反应到从库。

主从复制主要分为三步

1. MASTER在事物提交时，会把数据库变更记录保存在binlog中
2. SLAVE读取MASTER的binlog，写入到SLAVE的relaylog中继日志中
3. SLAVEredo relaylog中继日志中的事件操作，将改变反映到自身数据库

架构搭建

首先放开数据库端口或关闭防火墙

filewall-cmd--zone=public --add-port=3306/tcp -permanent

systemctl stop firewalld
systemctl disable firewalld

MASTER配置

1. 修改配置文件 etc/my.conf

   # MYSQL服务ID，保证整个集群环境中唯一，取值范围：1~2^32-1，默认为1
   server-id=1
   
   # 是否只读，1代表只读，0代表读写
   # 只针对普通用户
   # MASTER可读可写，设置为0
   read-only=0
   
   # super-read-only=0
   
   # 忽略的数据，即不需要同步的数据库
   # binlog-ignore-db=mysql
   
   # 指定需要同步的数据库
   # binlog-do-db=db01
   

2. 重启数据库服务

   systemctl restart mysqld

3. 创建远程连接的账号并赋予主从同步权限

   # 创建用户copyenable并设置密码pwd@123456，该用户可以在任意主机连接该MYSQL服务
   create user 'copyenable'@'%' identified with mysql_native_password by 'pwd@123456';
   # 为用户分配复制权限
   grant replication slave on *.* to 'copyenable'@'%';
   
   # 查看binlog
   show master status
   +---------------------------------------------------------------------------------+
   | File          | Position | Binlog_Do_DB  | Binlog_Ignore_DB | Executed_Gtid_Set |
   |+--------------------------------------------------------------------------------+
   | binlog.000004 | 412      |               |                  |                   |
   |+--------------------------------------------------------------------------------+
   # file：从哪个日志文件开始推送日志文件
   # position：从哪个位置开始推送日志
   # binlog_ignore_db：指定不需要同步的数据库
   

SLAVE配置

1. 修改配置文件 etc/my.conf

   # MYSQL服务ID，保证整个集群环境中唯一，取值范围：1~2^32-1，默认为1
   server-id=2
   
   # 是否只读，1代表只读，0代表读写
   read-only=1
   

2. 重启数据库服务

3. 关联MASTER

   # MYSQW Version upper than 8.0.0
   change replication source to 
   source_hose='xxx.xxx.xxx', 
   source_user='xxx', 
   source_password='xxx',
   source_log_file='xxx',
   source_log_pos='xxx';
   
   # MYSQL Version lower than 8.0.23
   change master to
   master_host='xxx.xxx.xxx',
   master_user='xxx',
   master_password='xxx',
   master_log_file='xxx',
   master_log_pos='xxx';
   
   # 注意8.0.23前后变量的名称不同 ！！！
   eg:
   change master to
   master_host='192.168.0.114',
   master_user='copyenable',
   master_password='pwd@123456',
   master_log_file='binlog.000004',
   master_log_pos=669;
   

4. 开启主从复制

   # MYSQW Version upper than 8.0.22
   start replica;
   
   # MYSQL Version lower than 8.0.22
   start slave;
   

5. 查看状态

   # MYSQW Version upper than 8.0.22
   show replica status;
   
   # MYSQL Version lower than 8.0.22
   show slave status;
   
   # 查看同步IO线程和SQL执行线程是否允许正常
   Replica_IO_Running: YES
   Replica_SQL_Running: YES