MySQL主从同步
主从同步概述
- 实现数据自动同步的服务结构
- 主服务器：接受客户端访问连接
- 从服务器：自动同步主服务器数据
主从同步原理
Master
- 启用binlog日志
Slave：
- Slave_IO：复制master主机 binlog日志文件里的SQL命令到本机的relay-log文件里。
- Slave_SQL：执行本机relay-log文件里的SQL语句，实现与Master数据一致。
构建主从同步
graph LR
master(master:192.168.1.11)-->slave(slave:192.168.1.12)
主从同步实现步骤
主服务器
- 启用binlog日志
- 授权用户
- 查看binlog日志信息
从服务器
- 设置server_id
- 确保与主服务器数据一致
- 指定主库信息
- 启动slave程序
- 查看状态信息
准备主服务器数据
[root@zzgrhel8 ~]# scp -r /root/tedu_nsd/dbs/mysql_scripts/ 192.168.1.11:/root
[root@zzgrhel8 ~]# ssh 192.168.1.11
[root@mysql1 ~]# mysql -uroot -pNSD2021@tedu.cn < mysql_scripts/nsd2021_data.sql
主服务器配置
// 修改配置文件
[root@mysql1 ~]# vim /etc/my.cnf
[mysqld]
server_id = 11
log-bin = master11
... ...
// 启动服务
[root@mysql1 ~]# systemctl restart mysqld
// 验证配置
[root@mysql1 ~]# ls /var/lib/mysql/master11.*
/var/lib/mysql/master11.000001 /var/lib/mysql/master11.index
[root@mysql1 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master11.000001 | 154 | | | |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
// 如果日志不是master11.000001，希望回到最初状态，可以执行以下命令：
mysql> reset master;
// 授权辅助服务器可以同步数据
mysql> grant replication slave on *.* to repluser@'%' identified by 'NSD2021@tedu.cn';
mysql> show grants for repluser@'%';
+--------------------------------------------------+
| Grants for repluser@% |
+--------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%' |
+--------------------------------------------------+
1 row in set (0.00 sec)
配置从服务器
- 修改配置文件
[root@mysql2 ~]# vim /etc/my.cnf
[mysqld]
server_id = 12
... ...
[root@mysql2 ~]# systemctl restart mysqld
- 如果主服务器上已有一段时间的数据，需要将主服务器数据完全同步到从服务器。
# 主服务器备份数据
[root@mysql1 ~]# mysqldump -uroot -pNSD2021@tedu.cn --master-data nsd2021 > /root/fullbackup.sql
[root@mysql1 ~]# scp /root/fullbackup.sql 192.168.4.12:/root/
# 从服务器恢复数据
mysql> create database nsd2021 default charset utf8mb4;
[root@mysql2 ~]# mysql -uroot -pNSD2021@tedu.cn nsd2021 < /root/fullbackup.sql
--master-data的作用：如果完全备份完成后，又有新的数据产生，它可记录备份时的数据状态信息。
- 查看binlog日志名和偏移量
// 使用备份的方式同步主服务器数据，在备份文件中查找
[root@mysql2 ~]# grep master11 fullbackup.sql
CHANGE MASTER TO MASTER_LOG_FILE='master11.000001', MASTER_LOG_POS=174149;
// 没有进行数据同步的，查看主服务器日志状态
[root@mysl2 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master11.000001 | 174149 | | | |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
- 在从服务器上配置同步的主库信息
[root@mysql2 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> change master to
-> master_host="192.168.1.11",
-> master_user="repluser",
-> master_password="NSD2021@tedu.cn",
-> master_log_file="master11.000001",
-> master_log_pos=174149;
Query OK, 0 rows affected, 2 warnings (0.10 sec)
- 启动slave程序
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
- 验证：查看状态信息
mysql> show slave status\G
... ...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
... ...
- 验证：查看从服务器上的相关文件
[root@mysql2 ~]# ls /var/lib/mysql
| 文件 | 说明 |
|---|---|
| master.info | 主库信息 |
| relay-log.info | 中继日志信息 |
| 主机名-relay-bin.xxxxxx | 中继日志 |
| 主机名-relay-bin.index | 索引文件 |
-
排错
通过
show slave status查看错误说明-
如果在从库上配置主库信息输入错误需要关闭后slave功能配置，然后再开启slave功能
mysql> stop slave; mysql> show slave status\G mysql> start slave; master.info文件和relay-log.info文件有配置记录，可用于排错
将表中的4个文件全部删除重启服务，可以还原为独立的、非从服务器。
验证主从同步效果
- 在主服务器上添加授权用户，允许客户端进行连接
[root@mysql1 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> grant all on nsd2021.* to admin@'%' identified by 'NSD2021@tedu.cn';
Query OK, 0 rows affected, 1 warning (0.06 sec)
- 客户端连接主服务器，更新数据
// 确认客户端已安装客户端软件
[root@zzgrhel8 ~]# rpm -q mysql-community-client
mysql-community-client-5.7.17-1.el7.x86_64
[root@zzgrhel8 ~]# mysql -h 192.168.1.11 -uadmin -pNSD2021@tedu.cn
// 查看自己的权限
mysql> show grants;
+----------------------------------------------------+
| Grants for admin@% |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO 'admin'@'%' |
| GRANT ALL PRIVILEGES ON `nsd2021`.* TO 'admin'@'%' |
+----------------------------------------------------+
2 rows in set (0.00 sec)
// 增加数据
mysql> use nsd2021;
mysql> insert into departments(dept_name) values('sales1');
Query OK, 1 row affected (0.05 sec)
mysql> insert into departments(dept_name) values('sales2');
Query OK, 1 row affected (0.05 sec)
mysql> insert into departments(dept_name) values('sales3');
Query OK, 1 row affected (0.08 sec)
- 从服务器验证同步数据
[root@mysql2 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> use nsd2021;
mysql> select * from departments where dept_name like 'sales_';
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 9 | sales1 |
| 10 | sales2 |
| 11 | sales3 |
+---------+-----------+
3 rows in set (0.00 sec)
主从同步结构
结构类型
- 一主一从
graph LR
m(master)-->s(slave)
- 一主多从
graph LR
m(master)-->s1(slave1)
m-->s2(slave2)
- 主从从
graph LR
m(master)-->s1(slave1)
s1-->s2(slave2)
- 互为主从（双主结构）
graph LR
m(master)-->s(slave)
s-->m
一主多从配置
graph LR
m(master:192.168.1.11)-->s1(slave1:192.168.1.12)
m-->s2(slave2:192.168.1.13)
主服务器数据备份
[root@mysql1 ~]# mysqldump -uroot -pNSD2021@tedu.cn --master-data nsd2021 > /root/fullbackup.sql
从服务器配置
- 修改配置文件
[root@mysql3 ~]# vim /etc/my.cnf
[mysqld]
server_id=13
... ...
[root@mysql3 ~]# systemctl restart mysqld
- 同步数据
# 主服务器拷贝完全备份数据到从服务器
[root@mysql1 ~]# scp fullbackup.sql 192.168.1.13:/root
# 创建数据库
[root@mysql3 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> create database nsd2021 default charset utf8mb4;
# 还原数据
[root@mysql3 ~]# mysql -uroot -pNSD2021@tedu.cn nsd2021 < fullbackup.sql
- 配置从服务器
# 获取日志文件名和偏移量
[root@mysql3 ~]# grep master11 fullbackup.sql
CHANGE MASTER TO MASTER_LOG_FILE='master11.000001', MASTER_LOG_POS=175286;
# 配置同步
[root@mysql3 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> change master to
-> master_host="192.168.1.11",
-> master_user="repluser",
-> master_password="NSD2021@tedu.cn",
-> master_log_file="master11.000001",
-> master_log_pos=175286;
Query OK, 0 rows affected, 2 warnings (0.16 sec)
# 启动从库
mysql> start slave;
# 查看状态
mysql> show slave status\G
客户端验证
- 客户端连接主服务器更新数据，查看从服务器同步内容
主从从配置
graph LR
m(master:192.168.1.13)-->s1(slave1:192.168.1.14)
s1-->s2(slave2:192.168.1.15)
主服务器配置
- 将一主多从配置中的从服务器192.168.1.13改为主服务器
[root@mysql3 ~]# cd /var/lib/mysql
[root@mysql3 mysql]# rm -f master.info relay-log.info mysql3-relay-bin.*
[root@mysql3 ~]# systemctl restart mysqld
[root@mysql3 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> show slave status;
Empty set (0.00 sec)
- 创建用于同步的用户
mysql> grant replication slave on *.* to repluser@'%' identified by 'NSD2021@tedu.cn';
- 修改配置文件
[root@mysql3 ~]# vim /etc/my.cnf
[mysqld]
server_id=13
log-bin=master13
... ...
- 重启并验证
[root@mysql3 ~]# systemctl restart mysqld
[root@mysql3 ~]# ls /var/lib/mysql/master13.*
/var/lib/mysql/master13.000001 /var/lib/mysql/master13.index
从服务器配置
- 由于该机器也是192.168.1.14的主服务器，所以它也需要有主服务器的配置
- 创建用于同步的用户
[root@mysql4 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> grant replication slave on *.* to repluser@'%' identified by 'NSD2021@tedu.cn';
- 修改配置文件
[root@mysql4 ~]# vim /etc/my.cnf
[mysqld]
server_id = 14
log-bin = master14
log_slave_updates # 在主上同步过来的日志，写到自己的binlog一份
... ...
[root@mysql4 ~]# systemctl restart mysqld
- 从主服务器同步数据
# 主服务器备份数据
[root@mysql3 ~]# mysqldump -uroot -pNSD2021@tedu.cn --master-data nsd2021 > /root/fullbackup.sql
[root@mysql3 ~]# scp fullbackup.sql 192.168.1.14:/root/
# 从服务器恢复数据
mysql> create database nsd2021 default charset utf8mb4;
[root@mysql4 ~]# mysql -uroot -pNSD2021@tedu.cn nsd2021 < /root/fullbackup.sql
- 查看binlog日志名和偏移量
[root@mysql4 ~]# grep master13 /root/fullbackup.sql
CHANGE MASTER TO MASTER_LOG_FILE='master13.000001', MASTER_LOG_POS=154;
- 在从服务器上配置同步的主库信息
mysql> change master to
-> master_host="192.168.1.13",
-> master_user="repluser",
-> master_password="NSD2021@tedu.cn",
-> master_log_file="master13.000001",
-> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.15 sec)
mysql> start slave;
mysql> show slave status\G
从服务器192.168.1.15配置
- 修改配置文件
[root@mysql5 ~]# vim /etc/my.cnf
[mysqld]
server_id = 15
... ...
[root@mysql5 ~]# systemctl restart mysqld
- 从主服务器同步数据
# 主服务器备份数据
[root@mysql4 ~]# mysqldump -uroot -pNSD2021@tedu.cn --master-data nsd2021 > /root/fullbackup.sql
[root@mysql4 ~]# scp fullbackup.sql 192.168.1.15:/root/
# 从服务器恢复数据
mysql> create database nsd2021 default charset utf8mb4;
[root@mysql5 ~]# mysql -uroot -pNSD2021@tedu.cn nsd2021 < /root/fullbackup.sql
- 查看binlog日志名和偏移量
[root@mysql5 ~]# grep master14 /root/fullbackup.sql
CHANGE MASTER TO MASTER_LOG_FILE='master14.000001', MASTER_LOG_POS=173457;
- 在从服务器上配置同步的主库信息
mysql> change master to
-> master_host="192.168.1.14",
-> master_user="repluser",
-> master_password="NSD2021@tedu.cn",
-> master_log_file="master14.000001",
-> master_log_pos=173457;
Query OK, 0 rows affected, 2 warnings (0.15 sec)
mysql> start slave;
mysql> show slave status\G
客户端验证
- 在主服务器上添加授权用户，允许客户端进行连接
[root@mysql3 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> grant all on nsd2021.* to admin@'%' identified by 'NSD2021@tedu.cn';
- 客户端连接主服务器更新数据
[root@mysql3 ~]# mysql -h192.168.1.13 -uadmin -pNSD2021@tedu.cn
mysql> use nsd2021;
mysql> insert into departments(dept_name) values('sales10');
- 查看从服务器同步内容
[root@mysql4 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> use nsd2021;
mysql> select * from departments where dept_name like 'sales1_';
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 13 | sales10 |
+---------+-----------+
1 row in set (0.00 sec)
[root@mysql5 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> use nsd2021;
mysql> select * from departments where dept_name like 'sales1_';
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 13 | sales10 |
+---------+-----------+
1 row in set (0.00 sec)
主主模式
graph LR
m(master:192.168.1.16)-->s(slave:192.168.1.17)
s-->m
将192.168.1.16作为主服务器
- 服务器配置
[root@mysql6 ~]# vim /etc/my.cnf
[mysqld]
server_id = 16
log_bin = master16
... ...
[root@mysql6 ~]# systemctl restart mysqld
mysql> grant replication slave on *.* to repluser@'%' identified by 'NSD2021@tedu.cn';
// 查看日志文件和偏移量
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master16.000002 | 701 | | | |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
- 从服务器配置
[root@mysql7 ~]# vim /etc/my.cnf
[mysqld]
server_id = 17
... ...
[root@mysql7 ~]# systemctl restart mysqld
[root@mysql7 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> change master to
-> master_host="192.168.1.16",
-> master_user="repluser",
-> master_password="NSD2021@tedu.cn",
-> master_log_file="master16.000002",
-> master_log_pos=701;
Query OK, 0 rows affected, 2 warnings (0.65 sec)
mysql> start slave;
mysql> show slave status \G
将192.168.1.17作为主服务器
- 服务器配置
[root@mysql7 ~]# vim /etc/my.cnf
[mysqld]
server_id = 17
log_bin = master17
... ...
[root@mysql7 ~]# systemctl restart mysqld
mysql> grant replication slave on *.* to repluser@'%' identified by 'NSD2021@tedu.cn';
// 查看日志文件和偏移量
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master17.000001 | 441 | | | |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
- 从服务器配置
[root@mysql6 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> change master to
-> master_host="192.168.1.17",
-> master_user="repluser",
-> master_password="NSD2021@tedu.cn",
-> master_log_file="master17.000001",
-> master_log_pos=441;
Query OK, 0 rows affected, 2 warnings (0.31 sec)
mysql> start slave;
mysql> show slave status\G
验证
- 以192.168.1.16作为主进行验证
// 主服务器上创建数据库和表
[root@mysql6 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> create database mydb;
mysql> use mydb;
mysql> create table students(id int primary key, name varchar(20));
// 从服务器上查看
[root@mysql7 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use mydb;
mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| students |
+----------------+
1 row in set (0.00 sec)
- 以192.168.1.17作为主进行验证
// 主服务器上添加用户
[root@mysql7 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> use mydb;
mysql> insert into students values(1, 'tom');
// 从服务器查看
[root@mysql6 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> use mydb;
mysql> select * from students;
+----+------+
| id | name |
+----+------+
| 1 | tom |
+----+------+
1 row in set (0.00 sec)
复制模式
异步复制（Asynchronous replication）
- 主服务器执行完一次事务后，立即将结果返给客户端，不关心从服务器是否已经同步数据。
半同步复制（Semisynchronous replication）
- 介于异步复制和全同步复制之间
- 主服务器在执行完一次事务后，等待至少一台从服务器同步数据完成，才将结果返回给客户端
模式配置
- 查看是否允许动态加载模块
mysql> show variables like 'have_dynamic_loading';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| have_dynamic_loading | YES |
+----------------------+-------+
1 row in set (0.01 sec)
- 主主模式下启用半同步复制
# 在两台服务器上修改配置文件
[root@mysql6 ~]# vim /etc/my.cnf
[mysqld]
server_id = 16
log_bin = master16
plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_master_enabled=1
rpl_semi_sync_slave_enabled=1
... ...
[root@mysql6 ~]# systemctl restart mysqld
[root@mysql7 ~]# vim /etc/my.cnf
[mysqld]
server_id = 17
log_bin = master17
plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_master_enabled=1
rpl_semi_sync_slave_enabled=1
[root@mysql7 ~]# systemctl restart mysqld
- 查看结果
mysql> select plugin_name, plugin_status from information_schema.plugins where plugin_name like '%semi%';
+----------------------+---------------+
| plugin_name | plugin_status |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE |
| rpl_semi_sync_slave | ACTIVE |
+----------------------+---------------+
2 rows in set (0.01 sec)
