Alliot's blog

MySQL 主从同步配置

  MySQL 主从同步是最为常见的架构之一,通常被用来做数据的热备或读写分离(主库写,从库读)。 本文记录了 CentOS 下的 MySQL 安装方法与主从同步的配置方案 。

概述

原理

  master 开启 binlog 日志,将改变记录到 binlog 中,slave 每隔一段时间检测 master 的 binlog 是否有改变,如果有,则启动一个 IO 线程与主库的 dump 线程连接,将主库的 binlog 以事件的形式同步到自己的中继日志(relay log)中,之后启动 SQL 线程,从中继日志重做 binlog,解析成SQL,将变更应用到从库中,实现与主库数据的同步。

条件

一个 slave 只能有一个 master
一个 master 可以有多个 slave(即一主多从)
每个库的 server-id 必须不同

安装MySQL

注: 以 CentOS 7 为例

配置yum源

1
2
wget http://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm
yum -y install mysql57-community-release-el7-9.noarch.rpm

安装mysql

1
2
yum list | grep mysql-community-server
yum -y install mysql-community-server

定义数据路径

1
2
3
4
mkdir -p /mysqldata/datadir
mkdir -p /data/mysql
mkdir -p /var/lib/mysql/
mkdir -p /var/run/mysqld/

覆盖my.cnf

参考配置:

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
97
98
99
100
101
102
[client]
default-character-set = utf8
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysql]
default-character-set=utf8
[mysqld]
character_set_server=utf8
user = mysql
port = 3306
# 主从同步配置(指定同步哪个库,多个库重复多行,所有库同步,则直接注释掉该配置)
replicate-do-db = appdb
# 忽略的库
binlog-ignore-db = mysql
binlog-ignore-db = test
binlog-ignore-db = information_schema
socket = /var/lib/mysql/mysql.sock
# 路径配置
basedir = /data/mysql
datadir = /mysqldata/datadir
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
# 监听本机内网IP
bind-address=192.168.1.111
open_files_limit = 10240
back_log = 600
max_connections = 5000
max_connect_errors = 6000
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 1M
join_buffer_size = 1M
thread_cache_size = 300
#thread_concurrency = 8
query_cache_size = 512M
query_cache_limit = 2M
query_cache_min_res_unit = 2k
default-storage-engine = INNODB
thread_stack = 192K
transaction_isolation = READ-COMMITTED
tmp_table_size = 256M
max_heap_table_size = 256M
long_query_time = 3
log-slave-updates
# 启用binlog(必选)
log-bin=mysql-bin
binlog_cache_size = 4M
binlog_format = MIXED
max_binlog_cache_size = 8M
max_binlog_size = 1G
expire_logs_days = 30
key_buffer_size = 256M
read_buffer_size = 1M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 1G
myisam_repair_threads = 1
#myisam_recover
#interactive_timeout = 120
#wait_timeout = 120
skip-name-resolve
# 该 id 主从库必须不同
server-id = 1
# 自增字段相关配置,一般在主主同步架构时需要配置,避免自增 ID 冲突,其他情况可以忽略:
# 自增长字段每次递增的量,其默认值是1,取值范围是[1,65535]
auto-increment-increment = 2
# 自增长字段的起始值,取值范围是[1, 65535]
auto-increment-offset = 1
innodb_buffer_pool_size = 4096M
#innodb_data_file_path = ibdata1:512M:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 16M
innodb_log_file_size = 128M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 1
# 慢日志相关:
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysqlslow.log
[mysqldump]
quick
max_allowed_packet = 32M

启动服务

1
systemctl enable mysqld && systemctl start mysqld

查看初始密码

1
2
grep "temporary password" /var/log/mysqld.log
mysql -uroot -p

基本安全设置

生产环境中 MySQL 安装完成后,需要执行安全配置向导:

1
2
mysql_secure_installation
# 这里使用上面得到的初始密码来登陆

根据此向导,将依次执行以下操作:

  • 为 root 用户设置密码
  • 删除匿名账号
  • 取消 root 用户远程登录
  • 删除 test 库和对 test 库的访问权限
  • 刷新授权表使修改生效

配置主从

环境信息

master: 192.168.1.110
slave: 192.168.1.111

master配置

在MySQL master 执行如下操作检查

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 登录到数据库
mysql -uroot -p
# 检查master状态
mysql> show master status\G
# 创建一个用于复制的用户
mysql> CREATE USER 'copy'@'192.168.%.%' IDENTIFIED BY 'Aa.123456789';
# 赋予权限
mysql> GRANT REPLICATION SLAVE ON *.* TO 'copy'@'192.168.%.%';
# 关闭ONLY_FULL_GROUP_BY
mysql> set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

slave配置

在MySQL slave 执行如下操作

1
2
3
4
5
6
7
# 修改 my.cnf 文件参数中的 server-id,要求与 master 的 server-id 不同
server-id = 10
# 重启服务后,验证上述账号是否能正常登录连通主库
mysql -ucopy -p -P 3306 -h192.168.1.110
mysql> SHOW DATABASES;

加锁并导出master数据

在MySQL master继续如下操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 对主库上所有表加锁
mysql> FLUSH TABLES WITH READ LOCK;
# 记录对应值
mysql> SHOW MASTER STATUS;
# 新开终端,对数据库进行备份
mysqldump --all-databases --master-data -u root -p -P 3306 > master.sql
# 备份完成后解锁
mysql> UNLOCK TABLES;

拷贝主库备份到从库中

在MySQL slave继续如下操作:
将主库导出的 sql 拷贝到从库中,并让从库导入主库的 sql

1
2
3
4
5
# 登录到数据库
mysql -uroot -p
# 加载备份
mysql> source master.sql;

将从库与主库建立连接

在 MySQL slave 继续如下操作。将从库与主库建立连接,启动同步进程:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# MASTER_LOG_FILE 参数为前面主库中获取的binlog文件名
# MASTER_LOG_POS 参数为前面从主库获取的 Position 值,忘记了可以填 0 一般系统会自动匹配对齐
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.1.110',
MASTER_PORT=3306,
MASTER_USER='copy',
MASTER_PASSWORD='Aa.123456789',
MASTER_LOG_FILE='mysql-bin.000005',
MASTER_LOG_POS=1074;
# 启动从库复制
mysql> START SLAVE;
# 检查主从状态
mysql> SHOW SLAVE STATUS\G
# 主要检查以下两个字段:
# Slave_IO_Running: Yes   # 观察IO进程是否为yes,如果为yes说明正常,如果长时间处于 "Connecting" 状态需检查你的从库指定的主库的链接信息是否正确;如果为NO,检查my.ini的 server-id 是否已经修改并重启了mysqld
# Slave_SQL_Running: Yes  # 观察SQL进程是否为yes
# Seconds_Behind_Master: 0 # 该参数表示从库和主库有多少秒的延迟,咱们可以理解为再过多少秒数据和主库保持一致,如果为 0 表示当前从库和主库的数据是一致的,如果该数较大,需考虑它的合理性。需要注意下该参数的值。

验证主从同步

在 master 创建数据:

1
2
3
4
5
6
7
8
9
# 登录到 master 数据库
mysql -uroot -p
# 新建一个测试复制的数据库
mysql> CREATE DATABASE test_copy DEFAULT CHARACTER SET = utf8mb4;
# 新建数据库对应的用户(此步骤可以省略)
mysql> GRANT ALL PRIVILEGES ON test_copy.* TO 'test'@'192.168.%.%' IDENTIFIED BY 'Aa.123456789' WITH GRANT OPTION;

登录到 slave 数据库检查数据库是否有创建:

1
2
3
4
5
6
7
8
9
10
11
12
mysql -uroot -p
mysql> SHOW SLAVE STATUS\G
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test_copy |
| mysql |
| performance_schema |
| sys |
+--------------------+

如果同步正常,则表示主从同步成功,之后可以删除示例数据库与示例用户:

1
2
mysql> DROP DATABASE test_copy;
mysql> DROP USER 'test'@'192.168.%.%'

问题排查

  • 如果发现 SHOW SLAVE STATUS\\G 状态正常,但从库没有正确同步数据,可以看看主库与从库的 my.cnf 是不是用 replicate-do-db 指定了只同步部分数据库。
------ 本文结束 ------

本文标题:MySQL 主从同步配置

文章作者:Alliot

发布时间:2019年10月30日 - 19:10

最后更新:2021年12月08日 - 00:12

原始链接:https://www.iots.vip/post/mysql-master-slave-replication.html

许可协议: 署名-非商业性使用-相同方式共享 4.0 国际 转载请保留原文链接及作者。

若文章为您解决燃眉之急或是带来些许明朗,不妨打赏 Alliot 一杯香茗或是一杯咖啡