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

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 statusG
... ...
             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 statusG
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 statusG

客户端验证 客户端连接主服务器更新数据，查看从服务器同步内容 主从从配置

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 statusG

从服务器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 statusG

客户端验证 在主服务器上添加授权用户，允许客户端进行连接

[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 statusG

验证 以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)