7 - MySQL主从同步｜主从同步模式_数据库主从同步

数据库系列文章

1 - 数据库服务概述 | 构建MySQL服务 | 数据库基本管理 | MySQL基本类型
2 - 表结构 | MySQL键值
3 - 字段约束｜MySQL索引｜MySQL用户管理
4 - 基础查询进阶｜连接查询
5 - 视图｜存储过程
6 - 数据备份与恢复｜innobackupex
7 - MySQL主从同步｜主从同步模式
8 - MySQL数据读写分离｜MySQL多实例
9 - 数据分片概述｜部署MyCat服务
10 - MHA集群概述｜部署MHA集群
11 - PXC集群｜MySQL存储引擎

MySQL主从同步

主从同步介绍

存储数据的服务结构
主服务器： 接收客户端访问连接
从服务器：自动同步主服务器数据

主从同步工作过程

启动binlog日志

• Slave
  • Slave_IO ：复制master主机 binlog日志文件里的SQL命令到本机的relay-log文件里
  • Slave_SQL：执行本机relay-log文件里的SQL语句，实现与Master数据一致

主从同步结构模式

一主一从同步结构 ：存储数据的服务结构，能够实现数据的自动同步（也就是数据备份）
一主多从同步结构
主从从同步结构 （给一主一从服务器的从服务器也配置一台从服务器）
主主同步结构 （两台服务器 彼此是对方的主服务器 同时还是对方的从服务器）

配置主从同步

一主一从同步结构

• 配置主服务器

1. 启动binlog日志

vim /etc/my.cnf
[mysqld]
server_id=51
log_bin=master51  # 日志默认存储在数据库目录下
--------
systemctl restart mysqld
1
2
3
4
5
6

2. 授权用户

mysql -uroot -p密码
# replication slave 让用户有复制命令的权限
mysql> grant replication slave on *.* to repluser@"%" identified by "密码"
1
2
3

3. 查看binlog日志信息

# 
mysql> show master status;
1
2

• 配置从服务器

1. 设置server_id 服务器编号 并重启服务

vim /etc/my.cnf
[mysqld]
server_id=52

systemctl restart mysqld
1
2
3
4
5

2. 确保与主服务器数据一致
3. 指定主库信息

mysql -uroot -p密码
mysql > change master to master_host="192.168.4.51",master_user="repluser",master_password="密码",master_log_file="master51.000001",master_log_pos=441 # 和主一样
1
2

4. 启动slave程序

mysql> start slave;
1

5. 查看状态信息（io线程和SQL线程必须同时是yes状态）

mysql> show slave status
1

一主多从同步结构

一台主 两台从
在从服务器做如下服务：

1. 设置server_id 服务器编号 并重启服务

vim /etc/my.cnf
[mysqld]
server_id=53

systemctl restart mysqld
1
2
3
4
5

2. 确保与主服务器数据一致
   第一步 在主服务器 对db1库做完全备份 然后吧备份文件拷贝给53

# --master-data 做完全备份数据时，在备份文件里记录使用的日志名和偏移量
mysqldump -uroot -pxxx --master-data -B db1 > /root/db1.sql
scp /root/db1.sql 192.168.4.53:/opt/
1
2
3

第二步 主机53使用备份文件恢复数据

]# mysql -uroot -pxxx < /opt/db1.sql
]# mysql -uroot -pxxx -e 'show databases'
]# mysql -uroot -pxxx -e 'select count(*) from db1.t1'
1
2
3

3. 指定主库信息

# 在备份文件里查看日志名和偏移量
grep master51 /opt/db1.sql
#显示如下信息
CHANGE MASTER TO MASTER_LOG_FILE='master51.000001',MASTER_LOG_POS=2000;
mysql -uroot -p密码
mysql > change master to master_host="192.168.4.51",master_user="repluser",master_password="密码",master_log_file="master51.000001",master_log_pos=20000 # 和主一样
1
2
3
4
5
6

4. 启动slave程序

mysql> start slave;
1

5. 查看状态信息（io线程和SQL线程必须同时是yes状态）

mysql> show slave status
1

测试主从

# 在主服务器上插入新数据
mysql> insert into db1.t1 values(999)
# 2台从服务器可以看到一样的数据
] # mysql -uroot -pxxx -e 'select * from db1.t1'
1
2
3
4

主从从同步结构

把数据库服务器 53 54 55 配置为主从从结构
就是给一主一从中的从服务器也配置从服务器
环境准备：把53主机恢复为独立的数据库服务器 54 55 配置主从从同步

[root@hotst53 ~]# cd /var/lib/mysql
[root@hotst53 ~]# rm -rf master.info
[root@hotst53 ~]# rm -rf relay-log.info
[root@hotst53 ~]# ls host53-relay-bin.*
[root@hotst53 ~]# rm -rf *-relay-bin.*
[root@hotst53 ~]# systemctl restart mysqld
mysql > show slave status \G
Empty set
mysql > drop database db1;
1
2
3
4
5
6
7
8
9

具体配置如下
第一步 配置主数据库服务器 53

1.启用binlog日志
vim /etc/my.cnf
[mysqld]
server_id=53
log_bin=master53

systemctl restart mysqld
2.用户授权
mysql> grant replication slave on *.* to repluser@"%" identified by "123qqq...A";
3.查看日志信息
mysql> show master status;
1
2
3
4
5
6
7
8
9
10
11

第二步 配置主机54 因为同时是2中角色，所以2种角色都要有

vim /etc/my.cnf
[mysqld]
server_id=54
log_bin=master54
log_slave_updates # 允许级联复制 54主机把自己主服务器的数据拷贝给自己的从服务器
:wq
systemctl restart mysqld
2.用户授权
mysql> grant replication slave on *.* to repluser@'%' identified by "123qqq..a";
3.指定主机服务器地址 日志名 偏移量
mysql> change master to master_host="192.168.4.53",master_user="repluser",master_password="123..." master_log_file="master53.000001",master_log_pos=441;
mysql> start slave;
mysql> show slave status \G
1
2
3
4
5
6
7
8
9
10
11
12
13

第三步 配置主机 55 （做54的从服务器）

1指定server_id 并重启mysql服务
vim /etc/my.cnf
[mysqld]
server_id=55
:wq
systemctl restart mysqld
2指定主服务器信息
mysql> change master to master_host="192.168.4.54",master_user="repluser",master_password="xxx",master_log_file="master54.000001",master_log_pos=441
3启动slave进程
4查看状态
1
2
3
4
5
6
7
8
9
10

验证结果 ： 在53创建的数据 54 55都能看到

主主同步结构

两台数据库服务器分别做彼此的从服务器和主服务器 2台服务器2种角色的配置都要有
第一步 配置68 配置69

vim /etc/my.cnf
[mysqld]
server_id=68
log_bin=master68
:wq
systemctl restart mysald
mysql> show master status
69主机mysql> grant replication slave on *.* to repluser@"%" identified by "111";
69主机mysql> change master to master_host="192.168.4.68",master_user="repluser",master_password="xxx",master_log_file="master68.000001",master_log_pos=441;
mysql>
1
2
3
4
5
6
7
8
9
10

主从同步模式

主从同步结构模式复制模式

主从数据库服务实现数据同步的工作方式
支持的工作方式：

• 第一种 异步复制模式（默认）
  主服务器执行一次事物后，立即将结果返回给客户端，不关心从服务器是否已经同步数据
• 第二种 半同步复制模式
  等待至少一个从服务器同步后 才会给客户端

---

把主服务器和从服务器的工作模式都修改为半同步复制模式
方法一 命令行设置
好处 不用重启服务 马上生效 但是不是永久化

1.安装master模块
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME "semisync_master.so"
2.安装slave模块
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME "semisync_salve.so"
3.查看模块是否安装
mysql> select plugin_name,plugin_status from information_schema.plugins where plugin_name like "%semi%"
4.启用master模块
mysql> set global rpl_semi_sync_master_enabled=1;
5.启用slave模块
mysql> set global rpl_semi_sync_slave_enabled=1;
6.查看模块是否启用
mysql> show variables like "rpl_semi_sync_%_enabled"
1
2
3
4
5
6
7
8
9
10
11
12

方法二 永久配置 编辑主配置文件

vim /etc/my.cnf
[mysqld]
# 安装模块
plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
# 启用模块
rpl_semi_sync_slave_enabled=1
rpl_semi_sync_master_enabled=1
1
2
3
4
5
6
7