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 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)
最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
平台声明:文章内容(如有图片或视频亦包括在内)由作者上传并发布,文章内容仅代表作者本人观点,简书系信息发布平台,仅提供信息存储服务。

推荐阅读更多精彩内容