Fork me on GitHub

MySQL 主从复制全方位解决方案

主从复制基础概念

在了解主从复制之前必须要了解的就是数据库的二进制日志( binlog ),主从复制架构大多基于二进制日志进行,二进制日志相关信息参考

二进制日志管理说明

  • 二进制日志位置
    my.cnf 文件中使用 log-bin 指定;
1
2
3
4
5
6
7
8
9
10
11
12
mysql>  show variables like '%log_bin%' ;
+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mysql-bin |
| log_bin_index | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+--------------------------------+
6 rows in set (0.00 sec)
  • 二进制日志命名
    命名规则为 mysql-bin.000000 ( 后为 6 位数字 );
1
2
3
4
5
6
7
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 16479 |
+------------------+-----------+
1 row in set (0.00 sec)
  • 二进制日志记录
    二进制日志中记录的是一个个完成的事件

  • 二进制日志格式
    推荐使用 row 格式, 查看当前使用的日志格式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> show variables like '%format%';
+---------------------------+-------------------+
| Variable_name | Value |
+---------------------------+-------------------+
| binlog_format | ROW |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| innodb_default_row_format | dynamic |
| innodb_file_format | Barracuda |
| innodb_file_format_check | ON |
| innodb_file_format_max | Barracuda |
| time_format | %H:%i:%s |
+---------------------------+-------------------+
9 rows in set (0.01 sec)
  • 二进制日志滚动
    每次重启都会刷新日志,也可以通过命令进行刷新 reset master ;

  • 二进制日志作用
    备份恢复

  • 二进制日志操作命令

1
2
3
4
5
# 查看都有哪些二进制日志
mysql> show binary logs;

# 查看当前使用的二进制日志文件
mysql> show master status;

MySQL 传统备份方式和缺陷

  • 二进制日志备份
  • mysqldump
    • 必须有数据库服务器完成逻辑工作,需要更多地 cpu 周期
    • 逻辑备份还原速度慢:需要 MySQL 加载和解释语句、转化存储格式、重建引擎
  • xtrabackup
    • 文件大
    • 不总是可以跨平台、操作系统和 MySQL 版本

MySQL 主从复制应用场景

高可用、辅助备份、分担负载

MySQL 主从复制介绍

复制技术

作用

  • 保证数据安全(异机实时备份)
  • 保证服务持续运行(宕机接管)

主从复制实现基本原理

  • 自带功能,复制是 MySQL 的一项功能,允许服务器将更改从一个实例复制到另一个实例。
  • 主服务器将所有数据和结构更改记录到二进制日志中。
  • 从属服务器从主服务器请求该二进制日志并在本地应用其内容。即通过把主库的 binlog 传送到从库,从新解析应用到从库。

复制架构

MySQL 复制的应用常见场景:

  • 从服务器作为主服务器的实时数据备份
  • 主从服务器实现读写分离,从服务器实现负载均衡
  • 把多个从服务器根据业务重要性进行拆分访问

复制过程

  • 开启 binlog 日志,通过把主库的 binlog 传送到从库,从新解析应用到从库。
  • 复制需要 3 个线程(dump、io、sql)完成。
  • 复制是异步的过程。主从复制是异步的逻辑的 SQL 语句级的复制。

复制前提:

  • 主服务器一定要打开二进制日志
  • 必须两台服务器(或者是多个实例)
  • 从服务器需要一次数据初始化
    • 如果主从服务器都是新搭建的话,可以不做初始化
    • 如果主服务器已经运行了很长时间了,可以通过备份将主库数据恢复到从库
  • 主库必须要有对从库复制请求的用户
  • 从库需要有 relay-log 设置,存放从主库传送过来的二进制日志 show variables like '%relay%';
  • 在第一次的时候,从库需要 change master to 去连接主库
  • 从库 change master 信息需要存放到 master.infoshow variables like '%master_info%';
  • 从库怎么知道主库发生了新的变化? 通过 relay-log.info 记录的已经应用过的 relay-log 信息
  • 在复制过程中涉及到的线程
    • 从库会开启一个 IO thread (线程),负责连接主库,请求 binlog,接收 binlog 并写入 relay-log
    • 从库会开启一个 SQL thread (线程),负责执行 relay-log 中的事件
    • 主库会开启一个 dump thrad (线程),负责响应从 IO thread 的请求

主从实现

  • 通过二进制日志
  • 至少两台(主、从)
  • 主服务器的二进制日志传到从服务器上再运行一遍。
  • 通过网络连接两台机器,一般都会出现延迟的状态,也可以说是异步的。
    mysql-replication

主从搭建配置

1
2
3
4
5
6
# 备份主库当前所有数据
mysqldump -uroot -p123 -A -B -F --master-data=2 -S /var/lib/mysql/mysql.sock >/tmp/full.sql

# 从库进行恢复数据
mysql> set sql_log_bin=0;
mysql> source /tmp/full.sql

Master 配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
# 配置 /etc/my.cnf 文件( Mac /usr/local/etc/my.cnf)
# 服务 id,主服务器 server-id 比从服务器 server-id 小
server-id=1
# 启动二进制日志系统
log-bin=mysql-bin
# 需要同步的二进制数据库名
binlog-do-db=pb
# 避免同步的数据库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=sys
binlog-ignore-db=performance_schema


# 重启 MySQL 服务
sudo service mysqld start

# 建立用于同步的用户
xiaoju@10.96.109.11: mysql -uroot -proot1234 -h 127.0.0.1
mysql> grant replication slave,super,reload on *.* to 'copier'@'10.96.109.10' identified by 'copier1234';
mysql> flush privileges;

# 查看是否授权成功
mysql> select user,Repl_slave_priv,host from mysql.user;
+---------------+-----------------+--------------+
| user | Repl_slave_priv | host |
+---------------+-----------------+--------------+
| root | Y | localhost |
| mysql.session | N | localhost |
| mysql.sys | N | localhost |
| root | Y | % |
| copier | Y | 10.96.109.10 |
+---------------+-----------------+--------------+
5 rows in set (0.00 sec)

# 查看所有 binlog 日志
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 617 |
+------------------+-----------+
1 row in set (0.00 sec)

# 查看所有 binlog 事件
mysql> show binlog events in 'mysql-bin.000003’;

# 查看 master 的状态信息,记录下 file 和 position 的值,在 slave 中配置需要配置
mysql> show master status\G;

备份 Master 数据同步至 Slave

Slave 配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
# 配置 /etc/my.cnf 文件
# 服务 id,从服务器 server-id 比主服务器 server-id 大
server-id=2
# 屏蔽不需要同步的数据库
# replication-ignore-db=mysql
# replication-ignore-db=information_schema
# replication-ignore-db=sys
# replication-ignore-db=performance_schema
# 要同步的数据库名,与主服务器配置保持一致
replication-do-db=pb
# 断点重连时间
master-connect-retry=60
master-host=10.96.112.151
master-user=copier
master-password=copier1234
master-port=3306

# 停止 slave 服务; Windows 下使用 stop slave;
mysql> slave stop;

# 配置 slave 同步
mysql> change master to master_host='10.96.112.151',master_user='copier',master_password='copier1234',master_log_file='mysql-bin.000003',master_log_pos=79;
mysql> change master to
master_host = '10.96.112.151', # 主服务器ip
master_user = 'copier',
master_password = 'copier1234',
master_log_file = 'mysql-bin.000003', # 上面记录的 file
master_log_pos = 79; # 上面记录的 position
# 查看帮助
mysql> help CHANGE MASTER TO

# 开启 slave 服务
mysql> slave start;

# 查看 slave 状态
# 主要查看 Slave_IO_Running 与 Slave_SQL_Running 是否都为Yes
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.96.11.151
Master_User: copier
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 44101
Relay_Log_File: 10-relay-bin.000002
Relay_Log_Pos: 43804
Relay_Master_Log_File: mysql-bin.000001
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: 44101
Relay_Log_Space: 44008
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: 0
Master_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: 1
Master_UUID: 01826661-01bd-11e9-ba58-aaa3a20cda98
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave 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:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

参考链接:

坚持原创技术分享,您的支持将鼓励我继续创作!