您的位置: 首页  >  文章  >  linux下的mysql 的主从复制、半同步复制、并行复制

linux下的mysql 的主从复制、半同步复制、并行复制

分类: 文章 • 2022-10-23 19:35:09

 

一、主从复制

1.主从复制原理

linux下的mysql 的主从复制、半同步复制、并行复制

原理：主从复制一共有三个进程，从库生成两个线程，一个I/O线程，一个SQL线程；

i/o线程去请求主库 的binlog，并将得到的binlog日志写到relay log（中继日志） 文件中；

主库会生成一个 log dump 线程，用来给从库 i/o线程传binlog；

SQL 线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，而最终数据一致;
2.主从复制的部署

实验环境：

server1      master

server2      slave

1.安装数据库

master：

[[email protected] ~]# tar xf  mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar
[[email protected] ~]# ls
mysql-community-client-5.7.17-1.el6.x86_64.rpm
mysql-community-common-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm
mysql-community-server-5.7.17-1.el6.x86_64.rpm
[[email protected] ~]# yum install * -y
[[email protected] ~]# systemctl start mysqld
[[email protected] ~]# grep password /var/log/mysqld.log   ##查看密码
[[email protected] ~]# mysql_secure_installation  ##安全初始化，并设置新密码
[[email protected] ~]# vim  /etc/my.cnf
 
server-id=1
log-bin=mysql-bin
 
[[email protected] ~]# systemctl restart mysqld
 
[[email protected] ~]# mysql  -p   ##为slave授权
mysql> grant replication slave on *.* to [email protected]'172.25.77.%' identified by 'Qqweqwe123..';
mysql> flush privileges;  
 
mysql> show master status;

linux下的mysql 的主从复制、半同步复制、并行复制

linux下的mysql 的主从复制、半同步复制、并行复制

linux下的mysql 的主从复制、半同步复制、并行复制

2.slave：

[[email protected] ～]# vim  /etc/my.cnf
 
server-id=2
 
[[email protected] ～]# systemctl start mysqld
 
[[email protected] ~]# mysql -p 
mysql>  change master to master_host='172.25.77.10',master_user='repl',master_password='Qqweqwe123..',master_log_file='mysql-bin.000001',master_log_pos=599;          
mysql> start  slave;
mysql> show  slave  status\G;

linux下的mysql 的主从复制、半同步复制、并行复制 

linux下的mysql 的主从复制、半同步复制、并行复制

3.测试：

master

mysql> create  database  westos;
Query OK, 1 row affected (0.15 sec)
mysql> use  westos;
Database changed
mysql> create  table userlist(
    -> name varchar(15) not null,
    -> passwd  varchar(25) not null);
Query OK, 0 rows affected (0.82 sec)
mysql> insert into userlist values ('user1','242424');
Query OK, 1 row affected (0.23 sec)
mysql> select * from  userlist;
+------+--------+
| name | passwd |
+------+--------+
| user1 | 242424 |
+------+--------+
1 row in set (0.00 sec)

linux下的mysql 的主从复制、半同步复制、并行复制

linux下的mysql 的主从复制、半同步复制、并行复制

slave:

[[email protected] ~]# mysql -p
mysql> use westos
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------+
| Tables_in_westos |
+------------------+
| userlist         |
+------------------+
1 row in set (0.00 sec)

mysql> select * from userlist;
+------+--------+
| name | passwd |
+------+--------+
| user1 | 242424 |
+------+--------+
2 rows in set (0.00 sec)

linux下的mysql 的主从复制、半同步复制、并行复制

二、基于gtid的主从复制

从 MySQL 5.6.5 开始新增了一种基于 GTID 的复制方式。通过 GTID 保证了每个在主库上提交的事务在集群中有一个唯一的ID。这种方式强化了数据库的主备一致性，故障恢复以及容错能力。
在原来基于二进制日志的复制中，从库需要告知主库要从哪个偏移量进行增量同步，如果指定错误会造成数据的遗漏，从而造成数据的不一致。借助GTID，在发 生主备切换的情况下，MySQL的其它从库可以自动在新主库上找到正确的复制位置，这大大简化了复杂复制拓扑下集群的维护，也减少了人为设置复制位置发生 误操作的风险。另外，基于GTID的复制可以忽略已经执行过的事务，减少了数据发生不一致的风险。

1.修改配置文件

master：

[[email protected] ~]# vim  /etc/my.cnf
server_id=1
log-bin=mysql-bin
gtid_mode = ON
enforce-gtid-consistency = true
[[email protected] ~]# systemctl restart mysqld

slave；

[[email protected] mysql]# vim  /etc/my.cnf
server_id=2
gtid_mode = ON
enforce-gtid-consistency = true
[[email protected] ~]# systemctl restart mysqld

2.slave做认证

[[email protected] ~]# mysql -p
mysql> stop slave;
Query OK, 0 rows affected (0.27 sec)

mysql> change master to master_host='172.25.9.1',master_user='repl',master_password='Qqweqwe123..',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.15 sec)

mysql> start slave;
Query OK, 0 rows affected (0.06 sec)

mysql> show slave status\G;

             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

linux下的mysql 的主从复制、半同步复制、并行复制

3.测试：

master写入数据

[[email protected] ~]# mysql -p
mysql> use westos
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into userlist values('user3','654321');
Query OK, 1 row affected (0.10 sec)
mysql> select * from userlist;
+-------+----------+
| user  | password |
+-------+----------+
| user1 | 123123   |
| user2 | 123234   |
| user3 | 654321   |
+-------+----------+
3 rows in set (0.00 sec)

linux下的mysql 的主从复制、半同步复制、并行复制

slave查看：


mysql> use westos[[email protected] ~]# mysql -p
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from userlist;
+-------+----------+
| user  | password |
+-------+----------+
| user1 | 123123   |
| user2 | 123234   |
| user3 | 654321   |
+-------+----------+
3 rows in set (0.00 sec)

linux下的mysql 的主从复制、半同步复制、并行复制

三、半同步复制

异步复制即是master数据库把binlog日志发送给 slave数据库，当slave服务器发生故障了，那么肯定会导致主从数据库服务器的数据不一致。
为了解决上面的问题，MySQL5.5引入一种叫做半同步复制模式。开启这种模式，可以保证slave数据库接收完master数据库发送过来的binlog日志并写入自己的中继日志中，然后反馈给master数据库，告知已经复制完毕。
开启这种模式后，当出现超时，主数据库将会自动转为异步复制模式，直到至少有一台从服务器接受到主数据库的binlog，并且反馈给主数据库。这时主数据库才会切换回半同步复制模式。

linux下的mysql 的主从复制、半同步复制、并行复制

linux下的mysql 的主从复制、半同步复制、并行复制

1.master：

[[email protected] ~]# mysql -p
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.27 sec)      ##导入半同步插件
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
    -> FROM INFORMATION_SCHEMA.PLUGINS
    -> WHERE PLUGIN_NAME LIKE '%semi%';    ##查看插件是否加载成功，方式二：show plugins;
+----------------------+---------------+
| PLUGIN_NAME          | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
+----------------------+---------------+
1 row in set (0.00 sec)


mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;  #在安装完插件后，半同步复制默认是关闭的，这时需设置参数来开启半同步
Query OK, 0 rows affected (0.00 sec)

以上的启动方式是在登录mysql后的命令行操作，也可写在my.cnf配置文件中。

mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.00 sec)
mysql> show status like 'Rpl_semi_sync%';   ##查看半同步是否在运行（% 代表Rpl_semi_sync后面的所有）
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)

2.slave:

[[email protected] mysql]# mysql -p
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.26 sec)

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
    -> FROM INFORMATION_SCHEMA.PLUGINS
    -> WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME          | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
+----------------------+---------------+
1 row in set (0.00 sec)

mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> STOP SLAVE IO_THREAD;    ##重启从数据库中的I/O线程
Query OK, 0 rows affected (0.07 sec)

mysql> START SLAVE IO_THREAD;   
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.00 sec)

mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+
14 rows in set (0.01 sec)

注意：要在从数据库上进行I/O线程重启，如果没有重启，则默认的还是异步复制模式！，重启后，slave会在master上注册为半同步复制的slave角色。

需要注意下，其实Mysql半同步复制并不是严格意义上的半同步复制。当半同步复制发生超时时（由 rpl_semi_sync_master_timeout参数控制，单位是毫秒，默认为10000，即10s），会暂时关闭半同步复制，转而使用异步复 制。当master dump线程发送完一个事务的所有事件之后，如果在rpl_semi_sync_master_timeout内，收到了从库的响应，则主从又重新恢复为 半同步复制。

mysql> show variables like "rpl_semi_sync_master_timeout";
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| rpl_semi_sync_master_timeout | 10000 |
+------------------------------+-------+
1 row in set (0.01 sec)

3.测试：

master:

mysql> insert into userlist values ('user4','123123');
mysql> SHOW STATUS LIKE 'Rpl%';

 linux下的mysql 的主从复制、半同步复制、并行复制

将slave机的io线程停掉，master机写入数据，会默认等待10s，如果超时还未等到slave机的ack，将自动切换到异步复制，如果slave机的io线程再次开启，复制将自动切换到半同步方式.

slave:

stop  slave io_thread;

master:

mysql> insert into userlist values('user5','324234');
Query OK, 1 row affected (0.11 sec)

mysql> show status like 'Rpl%';

linux下的mysql 的主从复制、半同步复制、并行复制

在slave上并为同步，

linux下的mysql 的主从复制、半同步复制、并行复制

在开启io线程是同步。

linux下的mysql 的主从复制、半同步复制、并行复制

注释：

环境变量（show variables like ‘%Rpl%‘;）

mysql> show variables like ‘%Rpl%‘;
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
| rpl_stop_slave_timeout                    | 31536000   |
+-------------------------------------------+------------+
7 rows in set (0.30 sec)

rpl_semi_sync_master_wait_for_slave_count
MySQL 5.7.3引入的，该变量设置主需要等待多少个slave应答，才能返回给客户端，默认为1。

rpl_semi_sync_master_wait_no_slave
ON
默认值，当状态变量Rpl_semi_sync_master_clients中的值小于rpl_semi_sync_master_wait_for_slave_count时，Rpl_semi_sync_master_status依旧显示为ON。

OFF
当状态变量Rpl_semi_sync_master_clients中的值于rpl_semi_sync_master_wait_for_slave_count时，Rpl_semi_sync_master_status立即显示为OFF，即异步复制。

简单来说，如果mysql架构是1主2从，2个从都采用了半同步复制，且设置的是 rpl_semi_sync_master_wait_for_slave_count=2，如果其中一个挂掉了，对于 rpl_semi_sync_master_wait_no_slave设置为ON的情况，此时显示的仍然是半同步复制，如果 rpl_semi_sync_master_wait_no_slave设置为OFF，则会立刻变成异步复制。

 

状态变量（show status like ‘%Rpl_semi%‘;）

mysql> show status like ‘%Rpl_semi%‘;
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 6     |
| Rpl_semi_sync_master_no_times              | 1     |
| Rpl_semi_sync_master_no_tx                 | 1     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 1120  |
| Rpl_semi_sync_master_tx_wait_time          | 4483  |
| Rpl_semi_sync_master_tx_waits              | 4     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 4     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)

Rpl_semi_sync_master_clients
当前半同步复制从的个数，如果是一主多从的架构，并不包含异步复制从的个数。

Rpl_semi_sync_master_no_tx
The number of commits that were not acknowledged successfully by a slave.
具体到上面的测试中，指的是insert into bobo.ceshi values(2)这个事务。

Rpl_semi_sync_master_yes_tx
The number of commits that were acknowledged successfully by a slave.
具体到上面的测试中，指的是以下四个事务:
mysql> create database bobo;
mysql> create table bobo.ceshi(id int);
mysql> insert into bobo.ceshi values(1);
mysql> insert into bobo.ceshi values(3); 

四、并行复制

server1:

mysql> show processlist;

linux下的mysql 的主从复制、半同步复制、并行复制

slave:server2


[[email protected] ~]# vim /etc/my.cnf
server-id=2
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=true
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16    ##线程数量
master_info_repository=TABLE  ##master_info存储方式为：table
relay_log_info_repository=TABLE   ##relay_log_info存储方式为: table
relay_log_recovery=ON

[[email protected] mysql]# systemctl restart mysqld
[[email protected] mysql]# mysql -p

mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                  | Info             |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
|  1 | system user |           | NULL | Connect |   83 | Waiting for master to send event                       | NULL             |
|  2 | system user |           | NULL | Connect |   23 | Slave has read all relay log; waiting for more updates | NULL             |
|  4 | system user |           | NULL | Connect |   83 | Waiting for an event from Coordinator                  | NULL             |
|  5 | system user |           | NULL | Connect |   83 | Waiting for an event from Coordinator                  | NULL             |
|  6 | system user |           | NULL | Connect |   83 | Waiting for an event from Coordinator                  | NULL             |
|  7 | system user |           | NULL | Connect |   83 | Waiting for an event from Coordinator                  | NULL             |
|  8 | system user |           | NULL | Connect |   83 | Waiting for an event from Coordinator                  | NULL             |

mysql> show processlist;

linux下的mysql 的主从复制、半同步复制、并行复制

 

相关推荐