MySQL主从复制原理及搭建过程

复制概述

• 复制即把一台服务器上的数据通过某种手段同步到另外一台或多台从服务器上，使得从服务器在数据上与主服务器保持一致。
• MySQL从3.X版本就开始支持复制功能，期间多次完善、改进、新增功能，比如并行复制的改进、GTID复制的推出，多源复制的完善等众多功能的实现，目前该技术已基本成熟。
• MySQL的复制功能是基于数据库Server层实现的，所以无论InnoDB引擎、MyISAM引擎或其他引擎都可进行同步复制。

复制过程

• MySQL的复制是通过binlog功能实现的，具体分为3个线程。
• 主节点：dump 线程，负责采集 binlog 数据与同步库 IO 线程交互。
• 从节点：IO 线程将获取到的数据转储成 relaylog 文件（relaylog内部存储的内容与binlog一致）
• 从节点：slave 线程将 relaylog 的数据读取出来，然后写入到数据库中。

复制流程图

线程信息截图

• 主节点线程信息

[root@GreatSQL][(none)]>show processlist;+----+-----------------+-----------------+------+-------------+------+-----------------------------------------------------------------+------------------+| Id | User            | Host            | db   | Command     | Time | State                                                           | Info             |+----+-----------------+-----------------+------+-------------+------+-----------------------------------------------------------------+------------------+|  8 | sync            | 127.0.0.1:35424 | NULL | Binlog Dump | 5092 | Source has sent all binlog to replica; waiting for more updates | NULL             |+----+-----------------+-----------------+------+-------------+------+-----------------------------------------------------------------+------------------+3 rows in set (0.00 sec)

• 从节点线程信息

[root@GreatSQL][(none)]>show processlist;+----+-----------------+-----------------+------+---------+-------+----------------------------------------------------------+------------------+| Id | User            | Host            | db   | Command | Time  | State                                                    | Info             |+----+-----------------+-----------------+------+---------+-------+----------------------------------------------------------+------------------+| 16 | system user     | connecting host | NULL | Connect |  5279 | Waiting for source to send event                         | NULL             || 17 | system user     |                 | NULL | Query   |  5010 | Replica has read all relay log; waiting for more updates | NULL             |+----+-----------------+-----------------+------+---------+-------+----------------------------------------------------------+------------------+5 rows in set (0.00 sec)

主从复制搭建过程

1.部署2个实例

• 部署过程忽略

2.主节点赋权

mysql> CREATE USER sync@'127.0.0.1' identified by 'GreatSQL';mysql> GRANT REPLICATION SLAVE ON *.* TO 'sync'@'127.0.0.1';mysql> FLUSH PRIVILEGES;

创建测试数据

# 创建库表[root@GreatSQL][(none)]>create database test;[root@GreatSQL][(none)]>use testDatabase changed[root@GreatSQL][test]>create table t1 (id int auto_increment, name varchar(64), primary key(`id`));Query OK, 0 rows affected (0.42 sec)# 创建数据[root@GreatSQL][test]>insert into t1 values(1,'小明'),(2,'小林'),(3,'小王');Query OK, 3 rows affected (0.03 sec)Records: 3  Duplicates: 0  Warnings: 0

导出数据

[root@GreatSQL]# mysqldump -uroot -p --single-transaction --master_data=2 --set-gtid-purged=OFF -S /tmp/mysql57_3310.sock test > ./3310.sqlEnter password:

记录binlog

[root@GreatSQL]# cat 3310.sql|grep log-bin-- CHANGE MASTER TO MASTER_LOG_FILE='log-bin.000005', MASTER_LOG_POS=4111;

导入到从节点

mysql -uroot -p -S /tmp/mysql57_3306.sock test 

建立同步

# 采用传统 position 方式建立同步[root@GreatSQL][test]>change master to master_host="127.0.0.1",master_port=3310,MASTER_USER='sync',MASTER_PASSWORD='GreatSQL',MASTER_LOG_FILE='log-bin.000005', MASTER_LOG_POS=4111;Query OK, 0 rows affected, 9 warnings (0.16 sec)[root@GreatSQL][test]>start slave;Query OK, 0 rows affected, 1 warning (0.01 sec)

查看同步是否正常

[root@GreatSQL][(none)]>show slave statusG;*************************** 1. row ***************************               Slave_IO_State: Waiting for source to send event                  Master_Host: 127.0.0.1                  Master_User: sync                  Master_Port: 3310                Connect_Retry: 60              Master_Log_File: log-bin.000006          Read_Master_Log_Pos: 196               Relay_Log_File: mgr3-relay-bin.000003                Relay_Log_Pos: 367        Relay_Master_Log_File: log-bin.000006             Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_DB:          Replicate_Ignore_DB:           Replicate_Do_Table:       Replicate_Ignore_Table:      Replicate_Wild_Do_Table:  Replicate_Wild_Ignore_Table:                   Last_Errno: 0                   Last_Error:                 Skip_Counter: 0          Exec_Master_Log_Pos: 196              Relay_Log_Space: 741              Until_Condition: None               Until_Log_File:                Until_Log_Pos: 0           Master_SSL_Allowed: No           Master_SSL_CA_File:           Master_SSL_CA_Path:              Master_SSL_Cert:            Master_SSL_Cipher:               Master_SSL_Key:        Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:               Last_SQL_Errno: 0               Last_SQL_Error:  Replicate_Ignore_Server_Ids:             Master_Server_Id: 2013712                  Master_UUID: 68420c27-77ff-11ec-825e-00155dcff90e             Master_Info_File: mysql.slave_master_info                    SQL_Delay: 0          SQL_Remaining_Delay: NULL      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates           Master_Retry_Count: 86400                  Master_Bind:      Last_IO_Error_Timestamp:     Last_SQL_Error_Timestamp:               Master_SSL_Crl:           Master_SSL_Crlpath:           Retrieved_Gtid_Set:            Executed_Gtid_Set: 68420c27-77ff-11ec-825e-00155dcff90e:6-9,9f18f115-4621-11ec-8e5b-00155dcff902:1-43                Auto_Position: 0         Replicate_Rewrite_DB:                 Channel_Name:           Master_TLS_Version:       Master_public_key_path:        Get_master_public_key: 0            Network_Namespace:1 row in set, 1 warning (0.08 sec)ERROR:No query specified

测试数据同步情况

# 主节点写入1条测试数据[root@GreatSQL][(none)]>insert into test.t1 values(4,'小清');Query OK, 1 row affected (0.15 sec)# 从节点查验，正常同步了[root@GreatSQL][(none)]>select * from test.t1;+------+--------+| id   | name   |+------+--------+|    1 | 小明   ||    2 | 小林   ||    3 | 小王   ||    4 | 小清   |+------+--------+4 rows in set (0.15 sec)

• 几个关键参数介绍

binlog设置

binlog格式介绍

binlog文件有3种格式，分别为 statement、mixed、row 3 种格式存在部分差异。

statement

• 记录的是每一条执行的SQL内容，相对会节省比较多的空间。

row

• 记录的是每一行结果的变化和上下文信息，占用比较多的空间。

mixed

• 以上2种的混合，MySQL会根据具体执行 SQL 语句来区分，具体选择哪一种方式进行记录。

binlog功能

• 1.提供主从复制功能
• 2.任意时间点恢复

如何查看二进制日志

• 参数1：ON状态，代表开启binlog日志记录，OFF状态，代表关闭binlog日志记录
• 参数2：binlog日志存放的具体路径
• 参数3：binlog日志的索引文件，这个文件与从节点的IO线程相关，如果丢失，则同步库提示异常。

mysql> show global variables like '%log_bin%';+---------------------------------+-----------------------------------------------+| Variable_name                   | Value                                         |+---------------------------------+-----------------------------------------------+| log_bin                         | ON                                            || log_bin_basename                | /data/logs/mysql57/3310/log-bin/log-bin       || log_bin_index                   | /data/logs/mysql57/3310/log-bin/log-bin.index |                                         |+---------------------------------+-----------------------------------------------+5 rows in set (0.00 sec)

如何禁止变更写入日志呢

• 通常有一些需求是不将当前session操作的变更写入日志，可以进行如下设置，此时你在这个该连接下的操作都不会记录到log-bin文件种，注意设置的时候不要加 global 参数，否则同步记录都会出问题。

# 临时关闭当前session的log-binmysql> set session sql_log_bin=0;Query OK, 0 rows affected (0.00 sec)# 开启当前session的log-binmysql> set session sql_log_bin=1;Query OK, 0 rows affected (0.00 sec)

如何调整bin-log保留时间

# 查看当前bin-log保留的时长mysql> show global variables like '%expire_logs_days%';+------------------+-------+| Variable_name    | Value |+------------------+-------+| expire_logs_days | 30    |+------------------+-------+1 row in set (0.00 sec)# 动态调整时长，调整后记得配置文件也需要同步更新mysql> set global expire_logs_days=10;Query OK, 0 rows affected, 1 warning (0.00 sec)

如何清理某个时间段之前的bin-log文件

# 查看当前的binlog文件mysql> show binary logs;+----------------+-----------+-----------+| Log_name       | File_size | Encrypted |+----------------+-----------+-----------+| log-bin.000001 |       784 | No        || log-bin.000002 |      1779 | No        || log-bin.000003 |       241 | No        || log-bin.000004 |       196 | No        || log-bin.000005 |      1337 | No        |+----------------+-----------+-----------+5 rows in set (0.02 sec)# 使用 purge 进行切割，这样之前的binlog就会都被清理掉mysql> purge binary logs to 'log-bin.000003';Query OK, 0 rows affected (0.03 sec)mysql> show binary logs;+----------------+-----------+-----------+| Log_name       | File_size | Encrypted |+----------------+-----------+-----------+| log-bin.000003 |       241 | No        || log-bin.000004 |       196 | No        || log-bin.000005 |      1337 | No        |+----------------+-----------+-----------+3 rows in set (0.00 sec)

总结

• 篇幅所限暂时写这么多，下一篇会介绍基于GTID形式的复制，以及binlog不同格式的差异点，欢迎追更，另外受限于个人能力，内容难免错漏，若有错误欢迎评论区指出更正。

Enjoy GreatSQL 🙂