MySQL 主从复制、主主复制、半同步复制
===============================================================================概述：
===============================================================================
MySQL Replication：1.主从复制的目的和架构★Master/Slave（主/从）
[*]Master: write/read
[*]Slaves: read
★主从复制的目的
[*]冗余：promte（提升为主），异地灾备
[*]负载均衡：转移一部分“读”请求；
[*]支援安全的备份操作；
[*]测试；
...★复制的方式：☉同步复制：
[*]所谓的同步复制，意思是master的变化，必须等待slave-1,slave-2,...,slave-n完成后才能返回。这样，显然不可取，也不是MYSQL复制的默认设置。比如，在WEB前端页面上，用户增加了条记录，需要等待很长时间。
☉异步复制：
[*]如同AJAX请求一样。master只需要完成自己的数据库操作即可。至于slaves是否收到二进制日志，是否完成操作，不用关心。MYSQL的默认设置。
☉半同步复制：
[*]master只保证slaves中的一个操作成功，就返回，其他slave不管。这个功能，是由google为MYSQL引入的。
★主/从架构：
[*]一主多从；
[*]一从一主；
[*]级联复制；
[*]循环复制；
[*]双主复制；
[*]一从多主：每个主服务器提供不同的数据库；
2.主从复制的工作流程★整体上来说，复制有3个步骤：
[*]master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；
[*]slave将master的binary log events拷贝到它的中继日志(relay log)；
[*]slave重做中继日志中的事件，将改变反映它自己的数据。
流程图：MySQL 复制配置1.主从复制配置步骤及操作演示★配置前注意事项：
[*]时间同步；
[*]复制的开始位置： ·从0开始； ·从备份中恢复到从节点后启动的复制；
[*]主从服务器mysqld程序版本不一致？
★主服务器：☉配置文件my.cnf
[*]server_id=#
[*]log_bin=log-bin
☉启动服务：
[*]mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'USERNAME'@'HOST' IDENTIFIED BY 'YOUR_PASSWORD';
[*]mysql> FLUSH PRIVILEGES;
★从服务器：☉配置文件my.cnf
[*]server_id=#
[*]relay_log=relay-log
☉启动服务：
[*]mysql> CHANGE MASTER TO MASTER_HOST='HOST',MASTER_USER='USERNAME',MASTER_PASSWORD='YOUR_PASSWORD',MASTER_LOG_FILE='BINLOG',MASTER_LOG_POS=#;
[*]mysql> START SLAVE ;
[*]mysql> SHOW SLAVE STATUS;
主从复制时应该注意的问题：★从服务设定为“只读”；
[*]在从服务器启动read_only，但仅对非SUPER权限的用户有效；
[*]阻止所有用户： mysql> FLUSH TABLES WITH READ LOCK;不建议使用，这样的话从服务器复制的数据就写不到磁盘中了
★尽量确保复制时的事务安全
[*]在master节点启用参数：sync_binlog = ON //每次提交立即将二进制事件从内存同步到二进制日志中
[*]如果用到的是InnoDB存储引擎： ·innodb_flush_logs_at_trx_commit=ON //在事务提交时立即刷写日志 ·innodb_support_xa=ON //支持分布式事务
★从服务器意外中止时尽量避免自动启动复制线程★从节点：设置参数
[*]sync_master_info=ON
[*]sync_relay_log_info=ON
实验：配置实现主从复制演示环境：
[*]两台CentOS 7的虚拟主机，一台做为mysql的主服务器，一台作为从服务器；
[*]两台服务器的MySQL的版本均为：mariadb-server-5.5.44-2.el7.centos.x86_64
操作步骤如下：
1.首先在主从节点同步时间，编辑/etc/chrony,添加时间服务器即可，如下：
# vim /etc/chrony.conf
server 10.1.0.1 iburst
# date;ssh 10.1.249.103 'date'//主从服务器同步时间如下
Sun Dec4 12:05:15 CST 2016
Sun Dec4 12:05:15 CST 2016
2.编辑主节点服务器的配置文件/etc/my.cnf,并启动服务，如下：
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
# vim /etc/my.cnf
skip_name_resolve = ON
innodb_file_per_table = ON
log_bin=master-log # 添加启动二进制日志文件
server-id=1 # server-id
#=================================================================================================
# systemctl start mariadb.service# 启动服务
# ss -tnl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 25 *:514 *:*
LISTEN 0 50 *:3306 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 128 127.0.0.1:631 *:*
LISTEN 0 100 127.0.0.1:25 *:*
#==================================================================================================
# ll /var/lib/mysql/ # 查看数据库目录
total 132012
-rw-rw---- 1 mysql mysql 16384 Dec3 21:47 aria_log.00000001
-rw-rw---- 1 mysql mysql 52 Dec3 21:47 aria_log_control
drwx------ 2 mysql mysql 4096 Dec3 14:48 hellodb
-rw-r----- 1 mysql mysql 18874368 Dec3 21:47 ibdata1
-rw-r----- 1 mysql mysql 50331648 Dec4 14:14 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Dec3 14:44 ib_logfile1
-rw-rw---- 1 mysql mysql 15530898 Dec3 21:47 master-log.000001 # 二进制日志文件
-rw-rw---- 1 mysql mysql 40 Dec4 14:14 master-log.index
drwx------ 2 mysql mysql 49 Dec3 14:48 mydb
drwx------ 2 mysql mysql 4096 Dec3 14:48 mysql
srwxrwxrwx 1 mysql mysql 0 Dec4 14:14 mysql.sock
drwx------ 2 mysql mysql 4096 Dec3 14:44 performance_schema
drwx------ 2 mysql mysql 19 Dec3 14:44 test
drwx------ 2 mysql mysql 79 Dec3 14:48 testdb
drwx------ 2 mysql mysql 36864 Dec3 14:48 ultrax
drwx------ 2 mysql mysql 8192 Dec3 14:48 zabbix
#==================================================================================================
MariaDB [(none)]> SHOW MASTER STATUS; # 查看主节点二进制日志的记录文件及Position
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000001 | 245 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
2.编辑从节点服务器的配置文件/etc/my.cnf,并启动服务，如下：
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
# vim /etc/my.cnf
skip-name-resolve = ON
innodb-file-per-table = ON
relay-log=relay-log # 中继日志
server-id=2 # server-id=2
#===================================================================================================
# systemctl start mariadb.service # 启动服务
# ss -tnl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 50 *:3306 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 128 127.0.0.1:631 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 128 127.0.0.1:6010 *:*
LISTEN 0 128 127.0.0.1:6011 *:*
#===================================================================================================
# ll /var/lib/mysql/ # 查看其数据库目录这时还没有发现中继日志relay-log，只有当复制数据后才会有
total 36904
-rw-rw---- 1 mysql mysql 16384 Nov 17 15:50 aria_log.00000001
-rw-rw---- 1 mysql mysql 52 Nov 17 15:50 aria_log_control
-rw-rw---- 1 mysql mysql 27262976 Dec3 15:14 ibdata1
-rw-rw---- 1 mysql mysql5242880 Dec3 15:14 ib_logfile0
-rw-rw---- 1 mysql mysql5242880 Nov 17 15:23 ib_logfile1
drwx------ 2 mysql mysql 19 Oct 30 20:23 mydb
drwx------ 2 mysql mysql 4096 Oct 30 20:02 mysql
srwxrwxrwx 1 mysql mysql 0 Dec3 15:14 mysql.sock
drwx------ 2 mysql mysql 4096 Oct 30 20:02 performance_schema
drwx------ 2 mysql mysql 6 Oct 30 20:02 test
drwx------ 2 mysql mysql 8192 Nov 17 00:35 zabbix_proxy
3.在主服务器上授权一个可以连接复制的用户账号，如下：
1
2
3
4
5
6
7
8
9
10
11
12
13
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'10.1.%.%' IDENTIFIED BY 'replpass';
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000001 | 494 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
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
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.1.252.153',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='replpass',
-> MASTER_LOG_FILE='master-log.000001', # 复制主节点的二进制的日志文件
-> MASTER_LOG_POS=245; # 复制主节点二进制日志开始的位置
Query OK, 0 rows affected (0.07 sec)
MariaDB [(none)]> SHOW SLAVE STATUS\G # 查看slave从节点的信息，发现还没有启动
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.1.252.153 # 连接的主节点主机
Master_User: repluser # 用户
Master_Port: 3306 # 端口
Connect_Retry: 60 # 重试时间默认为60s
Master_Log_File: master-log.000001 # 复制的二进制日志文件
Read_Master_Log_Pos: 245 # 复制的二进制日志文件起始位置
Relay_Log_File: relay-log.000001 # 中继日志的日志文件
Relay_Log_Pos: 4 # 中继日志的起始位置
Relay_Master_Log_File: master-log.000001
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: 245 # 现在所处的复制位置
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 # 是否基于ssl进行复制
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0 # 主节点的ID号
1 row in set (0.00 sec)
#=========================================================================================
MariaDB [(none)]> START SLAVE; # 启动复制线程
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW SLAVE STATUS\G# 再次查看slave状态
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.1.252.153
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000001
Read_Master_Log_Pos: 245
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 530
Relay_Master_Log_File: master-log.000001
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: 245
Relay_Log_Space: 818
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)
5.测试，在主节点创建一个数据库并查看二进制日志的位置，然后在从节点上查看其状态是否有延迟；
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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
# 在主节点创建数据库，查看二进制的位置
MariaDB > SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000001 | 245 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
MariaDB > CREATE DATABASE taotao;
Query OK, 1 row affected (0.01 sec)
MariaDB > SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000001 | 332 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
#========================================================================================
# 在从节点查看slave状态，看是否落后于主节点
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.1.252.153
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000001
Read_Master_Log_Pos: 332
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 617
Relay_Master_Log_File: master-log.000001
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: 332 # 现在复制所处的位置和主节点相同
Relay_Log_Space: 905
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)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| taotao |# 创建的taotao数据库
| test |
| zabbix_proxy |
+--------------------+
7 rows in set (0.00 sec)
6.从服务器我们要求其只能读不能写，所以要设置其全局变量参数为只读，如下：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
MariaDB [(none)]> SELECT @@global.read_only;
+--------------------+
| @@global.read_only |
+--------------------+
| 0 |
+--------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> SET @@global.read_only=1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SELECT @@global.read_only;
+--------------------+
| @@global.read_only |
+--------------------+
| 1 |
+--------------------+
1 row in set (0.00 sec)
如上，就是主从复制的整个过程...
2.主主复制配置步骤及操作演示★互为主从：两个节点各自都要开启binlog和relay log；1.数据不一致；2.自动增长id；
[*]定义一个节点使用奇数id·auto_increment_offset=1·auto_increment_increment=2
[*]另一个节点使用偶数id·auto_increment_offset=2·auto_increment_increment=2
★配置：1.server_id必须要使用不同值；2.均启用binlog和relay log；3.存在自动增长id的表，为了使得id不相冲突，需要定义其自动增长方式；服务启动后执行如下两步：4.都授权有复制权限的用户账号；5.各把对方指定为主节点；实验：主主复制实验环境同主从复制
[*]这里我把主从复制生成的数据库目录/var/lib/mysql全部删除了
操作步骤如下：
1.首先配置主节点1的配置文件/etc/my.cnf,如下：
1
2
3
4
5
6
7
8
9
10
11
# vim /etc/my.cnf
skip_name_resolve = ON
innodb_file_per_table = ON
log_bin=master-log
relay-log=relay-log
server-id=1
auto_increment_offset=1 # 定义节点的id从基数开始
auto_increment_increment=2
2.配置主节点2的配置文件/etc/my.cnf，如下：
1
2
3
4
5
6
7
8
9
10
# vim /etc/my.cnf
skip-name-resolve = ON
innodb-file-per-table = ON
log-bin=master-log
relay-log=relay-log
server-id=2
auto_increment_offset=2 # 定义节点的id从偶数开始
auto_increment_increment=2
3.分别启动两个主节点master1和master2的服务，并授权一个可以连接复制的用户账号，如下：
1）主节点master1
1
2
3
4
5
6
7
# systemctl start mariadb.service
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'10.1.%.%' IDENTIFIED BY 'replpass';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
2）主节点master2
1
2
3
4
5
6
7
# systemctl start mariadb.service
MariaDB [(none)]>GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'10.1.%.%' IDENTIFIED BY 'replpass';
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
4.查看两个主节点各自是否有二进制日志，以及二进制日志的起始位置
1）master1
1
2
3
4
5
6
7
8
9
10
11
# ls /var/lib/mysql/
aria_log.00000001ibdata1 ib_logfile1 master-log.000002master-log.indexmysql.sock test
aria_log_control ib_logfile0master-log.000001master-log.000003mysql performance_schema
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000003 | 504 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.01 sec)
2）master2
1
2
3
4
5
6
7
8
9
10
11
# ls /var/lib/mysql/
aria_log.00000001ibdata1 ib_logfile1 master-log.000002master-log.indexmysql.sock test
aria_log_control ib_logfile0master-log.000001master-log.000003mysql performance_schema
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000003 | 504 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
5.在两个主节点上设置分别把对方指定为主节点
1）master1
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
# 设定指定master2为主节点，二进制日志的起始位置为504
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.1.249.103', MASTER_USER='repluser', MASTER_PASSWORD='replpass', MASTER_LOG_FILE='master-log.000003', MASTER_LOG_POS=504;
Query OK, 0 rows affected (0.09 sec)
# 启动复制功能
MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.02 sec)
# 查看Slave状态如下：
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.1.249.103
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000003
Read_Master_Log_Pos: 504
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 530
Relay_Master_Log_File: master-log.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: 504
Relay_Log_Space: 818
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: 2
1 row in set (0.00 sec)
2）master2
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
# 设定指定master1为主节点，二进制日志的起始位置为504
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.1.252.153', MASTER_USER='repluser', MASTER_PASSWORD='replpass', MASTER_LOG_FILE='master-log.000003', MASTER_LOG_POS=504;
Query OK, 0 rows affected (0.03 sec)
# 启动复制功能
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: 10.1.252.153
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000003
Read_Master_Log_Pos: 504
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 530
Relay_Master_Log_File: master-log.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: 504
Relay_Log_Space: 818
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)
6.测试，两节点能否互相复制；
1）在master1上创建数据库mydb
1
2
MariaDB [(none)]> CREATE DATABASE mydb;
Query OK, 1 row affected (0.00 sec)
2）在master2上查看数据库mydb，并创建表tbl
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> USE mydb;
Database changed
MariaDB > CREATE TABLE tbl1 (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30));Query OK, 0 rows affected (0.07 sec)
3）在master1上查看创建的表tbl1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
MariaDB > SHOW TABLES;
+----------------+
| Tables_in_mydb |
+----------------+
| tbl1 |
+----------------+
1 row in set (0.00 sec)
MariaDB > DESC tbl1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name| varchar(30) | YES| | NULL | |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
4）向表中插入单个数据和连续插入数据，查看序列号的排列情况
在master1上向tbl1插入数据stu1
1
2
3
4
5
6
7
8
9
10
MariaDB > INSERT INTO tbl1 (name) VALUES ('stu1');
Query OK, 1 row affected (0.03 sec)
MariaDB > SELECT * FROM tbl1;
+----+------+
| id | name |
+----+------+
|1 | stu1 |
+----+------+
1 row in set (0.01 sec)
在master2上向tbl1插入数据stu2；
1
2
3
4
5
6
7
8
9
10
11
MariaDB > INSERT INTO tbl1 (name) VALUES ('stu2');
Query OK, 1 row affected (0.00 sec)
MariaDB > SELECT * FROM tbl1;
+----+------+
| id | name |
+----+------+
|1 | stu1 |
|2 | stu2 |
+----+------+
2 rows in set (0.00 sec)
在master1上向tbl1连续插入数据stu3，stu4，可以发现id不是按序向下排列的，是按奇数排列的，如下：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
MariaDB > INSERT INTO tbl1 (name) VALUES ('stu3'),('stu4');
Query OK, 2 rows affected (0.07 sec)
Records: 2Duplicates: 0Warnings: 0
MariaDB > SELECT * FROM tbl1;
+----+------+
| id | name |
+----+------+
|1 | stu1 |
|2 | stu2 |
|3 | stu3 |
|5 | stu4 |
+----+------+
4 rows in set (0.01 sec)
在master2上向tbl1连续插入数据stu3，stu4，可以发现id不是按序向下排列的，是按偶数排列的，如下：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
MariaDB > INSERT INTO tbl1 (name) VALUES ('stu5'),('stu6');
Query OK, 2 rows affected (0.00 sec)
Records: 2Duplicates: 0Warnings: 0
MariaDB > SELECT * FROM tbl1;
+----+------+
| id | name |
+----+------+
|1 | stu1 |
|2 | stu2 |
|3 | stu3 |
|5 | stu4 |
|6 | stu5 |
|8 | stu6 |
+----+------+
6 rows in set (0.00 sec)
如上，可以发现，向表中插入单个数据可以按顺序正常排列，但是如果连续插入数据的话，为了防止两个主节点冲突，所以按各自节点定义的序列号奇偶插入。
3.半同步复制★支持多种插件：
[*]/usr/lib64/mysql/plugins/
★需要安装插件方可使用：
[*]mysql> INSTALL PLUGIN plugin_name SONAME 'shared_library_name';
★半同步复制：
[*]semisync_master.so
[*]semisync_slave.so
★主节点：★从节点：实验：半同步复制实验环境：
[*]半同步复制是基于主从复制模型的，所以实验环境是在主从复制的基础上配置的；
[*]
操作步骤如下：
1.配置主从复制的环境，如下：
1）主节点，编辑配置文件/etc/my.cnf
1
2
3
4
5
6
# vim /etc/my.cnf
skip_name_resolve = ON
innodb_file_per_table = ON
log_bin=master-log # 添加启动二进制日志文件
server-id=1 # server-id
2)从节点，编辑配置文件如下：
1
2
3
4
5
6
# vim /etc/my.cnf
skip-name-resolve = ON
innodb-file-per-table = ON
relay-log=relay-log # 中继日志
server-id=2 # server-id=2
2.启动主节点服务器，授权一个可以连接复制的用户账号，并查看二进制日志文件的位置；
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# systemctl start mariadb.service
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'10.1.%.%' IDENTIFIED BY 'replpass';
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000001 | 419 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
3.在从节点连接设置连接主节点服务器的相关信息，并启动复制功能；
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
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.1.252.153',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='replpass',
-> MASTER_LOG_FILE='master-log.000001', # 复制主节点的二进制的日志文件
-> MASTER_LOG_POS=419; # 复制主节点二进制日志开始的位置
Query OK, 0 rows affected (0.07 sec)
MariaDB [(none)]> START SLAVE; # 启动复制线程
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW SLAVE STATUS\G# 再次查看slave状态
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.1.252.153
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000001
Read_Master_Log_Pos: 245
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 530
Relay_Master_Log_File: master-log.000001
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: 245
Relay_Log_Space: 818
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)
如上，主从复制就已经配置好了，现在我们来实现半同步复制，以为我这里只有一主一从，所以，从节点和主节点之间为同步复制，如下：
4.在主节点上安装支持同步复制的插件 semisync_master.so，并启动该插件
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)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.16 sec)
# 查看是否安装的插件 semisync_master.so 是否激活
MariaDB [(none)]> SHOW PLUGINS;
+--------------------------------+----------+--------------------+--------------------+---------+
| Name | Status | Type | Library | License |
+--------------------------------+----------+--------------------+--------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| FEDERATED | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| Aria | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEEDBACK | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL |
+--------------------------------+----------+--------------------+--------------------+---------+
43 rows in set (0.01 sec)
#========================================================================================
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';
+------------------------------------+-------+
| 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 |
+------------------------------------+-------+
4 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 GLOBAL VARIABLES LIKE 'rpl_semi%';
+------------------------------------+-------+
| 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_no_slave | ON |
+------------------------------------+-------+
4 rows in set (0.01 sec)
5.同样，在从节点上也安装支持异步复制的插件 semisync_slave.so，如下：
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
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.04 sec)
# 确保安装的插件是激活的
MariaDB [(none)]> SHOW PLUGINS;
+--------------------------------+----------+--------------------+-------------------+---------+
| Name | Status | Type | Library | License |
+--------------------------------+----------+--------------------+-------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
|INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| FEDERATED | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| Aria | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEEDBACK | DISABLED | INFORMATION SCHEMA | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| rpl_semi_sync_slave | ACTIVE | REPLICATION | semisync_slave.so | GPL |
+--------------------------------+----------+--------------------+-------------------+---------+
43 rows in set (0.00 sec)
#===================================================================================================
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | OFF |# 目前为关闭状态
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 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_semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |# 正常启动
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
2 rows in set (0.01 sec)
6.在从节点上首先关闭复制线程，然后再启动，在主节点上可以发现同步复制的从节点个数从0变为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
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
# 在从节点上首先关闭IO复制线程，然后再启动，因为刚启动同步复制插件不会自动跳转为同步；
MariaDB [(none)]> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.01 sec)
#========================================================================================
# 在主节点上查看两次同步复制的状态
MariaDB [(none)]>SHOW GLOBAL STATUS LIKE '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 | # 同步复制的客户端个数为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)
MariaDB [(none)]>SHOW GLOBAL STATUS LIKE '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 | # 同步复制的客户端个数为1
| 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)
测试：
在主节点导入hellodb数据库，再次查看同步复制的状态，如下：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
# 导入hellodb数据库
MariaDB [(none)]> \. /root/hellodb.sql
# 查看同步复制的状态，可以发现
MariaDB >SHOW GLOBAL STATUS LIKE '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 1580| # 平均等待时长1.58s
| Rpl_semi_sync_master_net_wait_time | 55334 | # 累计等待时长55334ms
| Rpl_semi_sync_master_net_waits | 35 | # 一共等待35次
| 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 | 1727|
| Rpl_semi_sync_master_tx_wait_time | 55287 |
| Rpl_semi_sync_master_tx_waits | 32 | # 等待事务提交的次数
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 35 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
页:
[1]