MySQL 主从同步配置

发表于 评论数： 本文字数： 1.5k 阅读时长 ≈ 6 分钟

　　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 指定了只同步部分数据库。