当前位置：首页 > 数据库 > oracle > 详细页面

Mariadb之半同步复制集群配置

时间：2020-07-05来源：www.pcxitongcheng.com作者：电脑系统城

首先我们来了解下在mariadb/mysql数据库主从复制集群中什么是同步，什么是异步，什么是半同步；所谓同步就是指主节点发生写操作事件，它不会立刻返回，而是等到从节点接收到主节点发送过来的写操作事件，并在从节点完成重放后，从节点返回一个重放完成的消息给主节点，然后主节点才返回，这种叫同步；简单讲就是主节点要等从节点把写操作重放完成后再接收到从节点发来的重放完成消息后，然后返回告诉客户端；异步指的是在主节点发生写操作后，它只管把写操作产生的事件，写入到自己的二进制日志中后，就返回客户端；它不会等从节点重放完成；这种叫异步；默认情况mariadb/mysql主从复制集群就是这种异步同步的方式进行；半同步指的是在主节点发生写操作事件后，它会把该操作的事件发送给从节点，当从节点接收到主节点发送过来的事件后，就立刻告诉主节点，从节点已经接收到主节点发送过来的事件，此时主机点并不会等到从节点重放完成，而是接收到从节点接收到主节点发送过去的的事件确认消息后，就返回给客户端；而在mariadb/mysql主从复制集群中的半同步，并不是我们刚才说的这种半同步机制；在mariadb/mysql主从复制集群中的半同步指的是，一主多从的复制集群中，一个从节点或一部分从节点和主节点是同步复制，一部分从节点是异步复制；

　　配置mariadb半同步复制

　　在mariadb/mysql中，半同步的实现是基于插件的方式实现的，在早期的版本中我们需要手动安装模块，然后才能实现半同步复制；目前mairadb10.5.4的版本中，默认就支持半同步，不需要手动安装模块；

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53 [root@lxc ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.5.4-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show global variables like 'rpl%';
+---------------------------------------+--------------+
| Variable_name | Value |
+---------------------------------------+--------------+
| rpl_semi_sync_master_enabled | OFF |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_COMMIT |
| rpl_semi_sync_slave_delay_master | OFF |
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_kill_conn_timeout | 5 |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------------+--------------+
9 rows in set (0.002 sec)

MariaDB [(none)]> show global status like 'rpl%';
+--------------------------------------------+-------------+
| Variable_name | Value |
+--------------------------------------------+-------------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_get_ack | 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_request_ack | 0 |
| Rpl_semi_sync_master_status | OFF |
| 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 |
| Rpl_semi_sync_slave_send_ack | 0 |
| Rpl_semi_sync_slave_status | OFF |
| Rpl_status | AUTH_MASTER |
| Rpl_transactions_multi_engine | 0 |
+--------------------------------------------+-------------+
20 rows in set (0.001 sec)

MariaDB [(none)]>

　　提示：以上变量信息和状态信息可以了解到mariadb是支持半同步的，只是没有开启；如果在mariadb/mysql数据库上查不到上面的信息，就需要我们手动安装模块；如下所示

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26 [root@docker_node01 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.65-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show global variables like 'rpl%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| rpl_recovery_rank | 0 |
+-------------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]> show global status like 'rpl%';
+---------------+-------------+
| Variable_name | Value |
+---------------+-------------+
| Rpl_status | AUTH_MASTER |
+---------------+-------------+
1 row in set (0.00 sec)

MariaDB [(none)]>

　　提示：mariadb5.5.65默认是没有启动半同步插件，我们需要手动安装插件；

　　安装插件

　　提示：默认情况在/usr/lib64/mysql/plugin/目录下就有semisync_master.so和semisync_slave.so这两个模块；从名字上我们就能清楚知道semisync_master.so这个模块用于主节点，semisync_slave.so用于从节点；安装方法如下

　　主节点安装semisync_master.so

　　提示：如果是主节点就安装semisync_master.so，从节点就安装semisync_slave.so；当然你把两个模块都安装上也是可以的，只不过启动时对应启动就行；

　　从节点安装semisync_slave.so

　　提示：可以看到安装好插件后，我们就可以在插件列表中相应的插件，在全局变量中也能查到对应的变量和状态信息；

　　到此半同步需要要点插件就已经装载完成；接下来就可以直接配置主从复制了，然后在开启半同步插件

　　配置mariadb/mysql主从复制

　　主节点开启二进制日志，设置server-id为1，开启sync_binlog=on

　　提示：以上在主节点开启了二进制日志，开启同步刷新二进制日志到磁盘二进制日志；开启刷新事务日志，设置服务器id为1

　　重启主节点，在主节点创建具有复制权限的账号，并查看当前二进制日志文件名和对应位置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25 [root@docker_node01 ~]# systemctl restart mariadb
[root@docker_node01 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.65-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> grant replication slave,replication client on *.* to 'rpluser'@'192.168.0.%' identified by 'admin';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 642 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MariaDB [(none)]>

　　从节点配置server-id为2，配置从节点为只读，开启从节点的中继日志

　　提示：以上配置表示，设置服务器id为2，开启中继日志，并保持到/var/lib/mysql/relay-log中；开启只读（仅对非super权限的用户生效）；开启同步刷新master_info到磁盘；开启同步刷新relay_log_info到磁盘；后面两项主要是确保事实时记录复制和重放二进制日志的位置，避免出现故障，重复复制；

　　重启从节点，配置连接从服务器连接主服务的相关信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64 [root@docker-node03 ~]# systemctl restart mariadb
[root@docker-node03 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.65-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.0.22',
-> MASTER_USER='rpluser',
-> MASTER_PASSWORD='admin',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000003',
-> MASTER_LOG_POS=642;
Query OK, 0 rows affected (0.06 sec)

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.0.22
Master_User: rpluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 642
Relay_Log_File: relay-log.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 642
Relay_Log_Space: 245
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
1 row in set (0.00 sec)

MariaDB [(none)]>

　　启动io线程和sql线程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48 MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.22
Master_User: rpluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 642
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 642
Relay_Log_Space: 817
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)

MariaDB [(none)]>

　　提示：到此主从复制就搭建好了，但是我们还没有启动半同步插件，到此之前mariadb的主从复制还是基于一部的方式进行，接下来我们启动半同步插件；

　　在主节点启用半同步插件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59 [root@docker_node01 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 5.5.65-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show variables like 'rpl%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_recovery_rank | 0 |
| rpl_semi_sync_master_enabled | OFF |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
5 rows in set (0.00 sec)

MariaDB [(none)]> set global rpl_semi_sync_master_enabled=ON;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show variables like 'rpl%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_recovery_rank | 0 |
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
5 rows in set (0.01 sec)

MariaDB [(none)]> show status like 'rpl%';
+--------------------------------------------+-------------+
| 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 |
| Rpl_status | AUTH_MASTER |
+--------------------------------------------+-------------+
15 rows in set (0.00 sec)

MariaDB [(none)]>

　　提示：可以看到半同步主节点插件已经开启

　　从节点启用半同步插件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30 [root@docker-node03 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 5.5.65-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show global variables like 'rpl%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_recovery_rank | 0 |
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
3 rows in set (0.00 sec)

MariaDB [(none)]> set global rpl_semi_sync_slave_enabled=on;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show global variables like 'rpl%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_recovery_rank | 0 |
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+

　　重启从节点IO线程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51 MariaDB [(none)]> stop slave io_thread;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.22
Master_User: rpluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 642
Relay_Log_File: relay-log.000004
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 642
Relay_Log_Space: 1101
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)

MariaDB [(none)]>

　　主节点查看状态信息，看看是否有从节点以半同步的方式连接上来了

　　提示：在主节点上可以看到状态信息中有一个从节点已经连接上来了；到此半同步复制就配置完了；

　　测试：在主节点上执行写操作，看看对应状态有什么变化？

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49 MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.01 sec)

MariaDB [(none)]> create database first_db;
Query OK, 1 row affected (0.01 sec)

MariaDB [(none)]> show global variables like 'rpl%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_recovery_rank | 0 |
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
5 rows in set (0.00 sec)

MariaDB [(none)]> show global status like 'rpl%';
+--------------------------------------------+-------------+
| Variable_name | Value |
+--------------------------------------------+-------------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 1037 |
| Rpl_semi_sync_master_net_wait_time | 1037 |
| Rpl_semi_sync_master_net_waits | 1 |
| 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 | 1321 |
| Rpl_semi_sync_master_tx_wait_time | 1321 |
| Rpl_semi_sync_master_tx_waits | 1 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 1 |
| Rpl_status | AUTH_MASTER |
+--------------------------------------------+-------------+
15 rows in set (0.00 sec)

MariaDB [(none)]>