Mysql主从同步/复制

发表于 | 分类于

主从同步原理

在master机器上，主从同步事件会被写到特殊的log文件中(binary-log);在slave机器上，slave读取主从同步事件，并根据读取的事件变化，在slave库上做相应的更改。

主从同步作用

  • 数据备份：主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。
  • 架构扩展：水平扩展数据库的负载能力。当业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。
  • 读写分离：写服务使用master，读服务使用slave，读写服务分离互不影响，提高并发访问性能。

主从同步格式

binlog日志格式有3种，mysql默认采用 Statement：

  • Statement：基于语句的复制，主库把 sql语句 写入到binlog中，完成复制

优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep、last_insert_id以及user-defined functions等函数会出现问题)。

  • Row：基于行数据的复制，主库把每一行 数据变化 的信息作为事件，写入到binlog，完成复制

优点是不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是 alter table的时候会让日志暴涨。

  • Mixed：statement与row的 混合

默认用语句复制，出问题时候自动切换成行数据复制,MySQL会根据执行的SQL语句选择日志保存方式。

主从同步过程

主从同步需要3个线程协作：

  • 主库-binlog输出线程: 每当有从库连接到主库的时候，主库都会创建一个线程然后发送binlog内容到从库。
  • 从库-I/O线程: 当START SLAVE语句在从库开始执行之后，从库创建一个I/O线程，它读取主库的binlog输出线程发送的更新并拷贝这些更新到本地relay log文件。
  • 从库-SQL线程: 读取从库I/O线程写到relay log的更新事件并执行。

主从同步实践

主从部署必要条件：

  • 主库开启binlog日志
  • 主从server-id不同
  • 从库服务器能连通主库

环境准备

本地docker安装两个mysql:

1
2
3
mysql:5.6
mysql1(master): 172.17.0.3:3307
mysql2(slave): 172.17.0.2:3308

配置文件

mysql1(master): 172.17.0.3 配置:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
#mysql master1 config 
[mysqld]
server-id = 1        # 节点ID，确保唯一

# log config
log-bin = mysql-bin     #开启mysql的binlog日志功能
sync_binlog = 1         #控制数据库的binlog刷到磁盘上去 , 0 不控制，性能最好，1每次事物提交都会刷到日志文件中，性能最差，最安全
binlog_format = mixed   #binlog日志格式，mysql默认采用statement，建议使用mixed
expire_logs_days = 7                           #binlog过期清理时间
max_binlog_size = 100m                    #binlog每个日志文件大小
binlog_cache_size = 4m                        #binlog缓存大小
max_binlog_cache_size= 512m              #最大binlog缓存大
binlog-ignore-db=mysql #不生成日志文件的数据库，多个忽略数据库可以用逗号拼接，或者 复制这句话，写多行

auto-increment-offset = 1     # 自增值的偏移量
auto-increment-increment = 1  # 自增值的自增量
slave-skip-errors = all #跳过从库错误

mysql2(slave): 172.17.0.2 配置:

1
2
3
4
5
6
7
[mysqld]
server-id = 2
log-bin=mysql-bin
relay-log = mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%

重启两个mysql，让配置生效

master数据库

进入master的数据库，为master创建复制用户

1
CREATE USER repl_user IDENTIFIED BY 'repl_passwd';

赋予该用户复制的权利

1
2
3
grant replication slave on *.* to 'repl_user'@'172.17.0.2'  identified by 'repl_passwd';

FLUSH PRIVILEGES;

查看master的状态

1
2
3
4
5
6
7
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005      120|              | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

slave数据库

1
2
3
4
5
6
7
8
9
10
11
mysql> CHANGE MASTER TO 
MASTER_HOST = '172.17.0.3',  
MASTER_USER = 'repl_user', 
MASTER_PASSWORD = 'repl_passwd',
MASTER_PORT = 3307,
MASTER_LOG_FILE='mysql-bin.000005',
MASTER_LOG_POS=120,
MASTER_RETRY_COUNT = 60,
MASTER_HEARTBEAT_PERIOD = 10000; 
# MASTER_LOG_FILE='mysql-bin.000005',#与主库File 保持一致
# MASTER_LOG_POS=120 , #与主库Position 保持一致

启动从库slave进程

1
2
mysql> start slave;
Query OK, 0 rows affected (0.04 sec)

参考

彦祖老师 wechat