搭建MySQL主从复制及原理详解
搭建MySQL主从复制及原理详解
MySQL的复制有3中常见架构，分别是一主多从复制架构、多级复制架构和双主复制架构。本片文章主要讲解的是一主多从架构及其搭建过程，其它两种有兴趣的读者可以留言。
1.复制简述
MySQL从3.23版本开始提供复制的功能。复制是指将主数据库的DDL和DML操作通过二进制日志传到复制服务器（也叫从库）上，然后在从库上对这些日志重新执行（也叫重做），从而使得从库和主库的数据保持同步。
MySQL支持一台主库同时向多台从库进行复制，从库同时也可以作为其他服务器的主库，实现链状的复制。
MySQL复制的优点主要包括以下3个方面：
- 如果主库出现问题，可以快速切换到从库提供服务；
- 可以在从库上执行查询操作，降低主库的访问压力；
- 可以在从库上执行备份，以避免备份期间影响主库的服务。
2.复制原理
（1）首先，MySQL主库在事务提交时会把数据变更作为事件Events记录在二进制日志文件Binlog中；MySQL主库上的sync_binlog参数控制Binlog日志刷新到磁盘。
（2）主库推送二进制日志文件Binlog中的事件到从库的中继日志Relay Log，之后从库根据中继日志Relay Log重做数据变更操作，通过逻辑复制以此来达到主库和从库的数据一致。
MySQL通过 3个线程来完成主从库间的数据复制：其中Binlog Dump线程跑在主库上， I/O线程和SQL线程跑在从库上。当在从库上启动复制（START SLAVE）时，首先创建 I/O线程连接主库，主库随后创建Binlog Dump线程读取数据库事件并发送给 I/O线程，I/O线程获取到事件数据后更新到从库的中继日志Relay Log中去，之后从库上的SQL线程读取中继日志Relay Log中更新的数据库事件并应用，如下图所示：
<img src="C:\Users\50312\AppData\Roaming\Typora\typora-user-images\image-20201207083143526.png" alt="image-202
01207083143526" style="zoom:80%;" />
3.复制涉及的各类文件
二进制日志（Binlog）
二进制日志文件（Binlog）会把 MySQL 中的所有数据修改操作以二进制的形式记录到日志文件中，包括Create、Drop、Insert、Update、Delete操作等，但二进制日志文件（Binlog）不会记录Select操作，因为Select操作并不修改数据。
二进制日志文件Binlog格式有以下3种：
- Statement：基于SQL语句级别的Binlog，每条修改数据的SQL都会保存到Binlog里
- Row：基于行级别，记录每一行数据的变化，也就是将每行数据的变化都记录到Binlog里面，记录得非常详细，但是并不记录原始SQL；在复制的时候，并不会因为存储过程或触发器造成主从库数据不一致的问题，但是记录的日志量较Statement格式要大得多。
- Mixed：混合Statement和Row模式，默认情况下采用Statement模式记录，某些情况下会切换到Row模式，例如SQL中包含与时间、用户相关的函数等。
中继日志（Relay Log）
中继日志文件Relay Log的文件格式、内容和二进制日志文件Binlog一样，唯一的区别在于从库上的SQL线程在执行完当前中继日志文件Relay Log中的事件之后，SQL线程会自动删除当前中继日志文件Relay Log，避免从库上的中继日志文件Relay Log占用过多的磁盘空间。
4.搭建步骤
关闭主从机器的防火墙
# systemctl stop iptables（需要安装iptables服务）
# systemctl stop firewalld（默认）
# systemctl disable firewalld.service（设置开启不启动）
主服务器配置
第一步：修改my.cnf文件
修改主数据库服务器的配置文件my.cnf，开启BINLOG，并设置server-id的值。这两个参数的修改需要重新启动数据库服务才可以生效。
[mysqld]
#启用二进制日志
log-bin=mysql-bin
#服务器唯一ID，一般取IP最后一段
server-id=136
第二步：重启MySQL
# systemctl restart mysqld
第三步：授予Slave从机权限
mysql> GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.211.138'IDENTIFIED BY '123456';
第四步：刷新权限
FLUSH PRIVILEGES;
第五步：查看master的状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
上图表示，主机将从 mysql-bin.000006这个二进制文件的120行这个位置开始同步。
从服务器配置
第一步：修改my.cnf文件
[mysqld]
server-id=138
确保集群中的各个服务器的server-id唯一。
第二步：重启并登录到从库
先使用如下命令查看从机是否在运行，是的话先停止运行
mysql> show slave status \G;
mysql> stop slave;
第三步：配置从数据库服务器
指定复制用户，主数据库服务器IP、端口以及开始执行复制的日志文件和位置等，具体如下：
mysql> change master to
-> master_host='master_host_name',
-> master_port='master_host_port',
-> master_user='replication_user_name',
-> master_password='replication_password',
-> master_log_file='recorded_log_file_name',
-> master_log_pos=recorded_log_position;
举例说明如下：
mysql> change master to
-> master_host='192.168.211.136',
-> master_port=3306,
-> master_user='root',
-> master_password='123456',
-> master_log_file='mysql-bin.000006',
-> master_log_pos=120;
第四步：启动slave线程
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
第五步：检查从服务器复制功能状态
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.211.136
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 120
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
。。。。。。。省略
Slave_IO_Running: Yes 说明从库IO线程已就绪。
Slave_SQL_Running: Yes 说明从库SQL线程已就绪。
测试
搭建成功之后，往主机中插入数据，看看从机中是否有数据
注： 如果出现复制不成功，可以使用
mysql> set global sql_slave_skip_counter =1; # 忽略一个错误
mysql> start slave
5.存在问题：主从延时
查看主机处理状态
可以通过SHOW PROCESSLIST命令在主库上查看Binlog Dump线程，从BinlogDump线程的状态可以看到，MySQL 的复制是主库主动推送日志到从库去的，是属于“推”日志的方式来做同步
mysql> mysql> show processlist \g;
+----+------+-----------------------+------+-------------+-------+-----------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------------+------+-------------+-------+-----------------------------------------------------------------------+------------------+
| 3 | root | 192.168.211.1:50147 | NULL | Sleep | 11225 | | NULL |
| 6 | root | 192.168.211.138:52846 | NULL | Binlog Dump | 6174 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 7 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+------+-----------------------+------+-------------+-------+-----------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
查看从机处理状态
在从库上通过SHOW PROCESSLIST可以看到 I/O线程和SQL线程，I/O线程等待主库上的Binlog Dump线程发送事件并更新到中继日志Relay Log，SQL线程读取中继日志Relay Log并应用变更到数据库:
mysql> show processlist \g;
+----+-------------+---------------------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+---------------------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| 4 | root | 192.168.211.1:50207 | NULL | Sleep | 572 | | NULL |
| 6 | system user | | NULL | Connect | 6343 | Waiting for master to send event | NULL |
| 7 | system user | | NULL | Connect | 5859 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |
| 9 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+-------------+---------------------+------+---------+------+-----------------------------------------------------------------------------+------------------+
4 rows in set (0.00 sec)
