MariaDB数据库主从复制、双主复制、半同步复制、基于SSL的安全复制实现及其功能特性介绍
一.复制概述MariaDB/MySQL内建的复制功能是构建大型，高性能应用程序的基础。将MySQL的数据分布到多个系统上去，这种分布的机制，是通过将MySQL的某一台主机的数据复制到其它主机（slaves）上，并重新执行一遍来实现的。复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。
请注意当你进行复制时，所有对复制中的表的更新必须在主服务器上进行。否则，你必须要小心，以避免用户对主服务器上的表进行的更新与对从服务器上的表所进行的更新之间的冲突。
1.mysql支持的复制类型：
(1)：基于语句的复制:在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。
一旦发现没法精确复制时，会自动选着基于行的复制。
(2)：基于行的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍. 从mysql5.0开始支持
(3)：混合类型的复制: 默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。
2.复制解决的问题
MySQL复制技术有以下一些特点：
(1).数据分布 (Data distribution )
(2).负载平衡(load balancing)
(3).备份(Backups)
(4).高可用性和容错行 High availability and failover
3.复制如何工作
整体上来说，复制有3个步骤：
(1).master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；
(2).slave将master的binary log events拷贝到它的中继日志(relay log)；
(3).slave重做中继日志中的事件，将改变反映它自己的数据。
下图描述了复制的过程,经典的图哦：摘自《高性能MySQL_第3版》这本经典书中的复制那章；
该过程的第一部分就是master记录二进制日志。在每个事务更新数据完成之前，master在二日志记录这些改变。MySQL将事务串行的写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。
下一步就是slave将master的binary log拷贝到它自己的中继日志。首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。
SQL slave thread（SQL从线程）处理该过程的最后一步。SQL线程从中继日志读取事件，并重放其中的事件而更新slave的数据，使其与master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。
此外，在master中也有一个工作线程：和其它MySQL的连接一样，slave在master中打开一个连接也会使得master开始一个线程。复制过程有一个很重要的限制——复制在slave上是串行化的，也就是说master上的并行更新操作不能在slave上并行操作。
二.复制配置过程简介
有两台MySQL数据库服务器node3和node4，node3为主服务器，node4为从服务器，初始状态时，node3和node4中的数据信息相同，当node3中的数据发生变化时，node4也跟着发生相应的变化，使得node3和node4的数据信息同步，达到备份的目的。
要点：
负责在主、从服务器传输各种修改动作的媒介是主服务器的二进制变更日志，这个日志记载着需要传输给从服务器的各种修改动作。因此，主服务器必须激活二进制日志功能。从服务器必须具备足以让它连接主服务器并请求主服务器把二进制变更日志传输给它的权限。
配置主从复制的过程：
1）.主节点操作步骤
（1）、启用二进制日志
（2）、设置一个在当前集群中唯一的server-id；
（3）、创建一个有复制权限（replication slave，replication client）帐号；
2）.slave节点的操作步骤
（1）、启用中继日志；
（2）、设置一个在当前集群中唯一的server-id；
（3）、使用有复制权限的用户帐号连接至主服务器，并启动复制线程；
注意：
（1）、服务器版本：主从服务器版本一致；
如果版本不一致，必须保证从服务器的版本高于主服务器的版本；
（2）、如果mysql数据库的隔离级别为可读，其二进制日志格式尽量使用基于行的；
实验环境：
服务器版本为：
CentOS 6.6 64bit
数据库软件版本为：
mariadb-10.0.12.tar.gz
node3节点的IP地址：172.16.31.20/16
node4节点的IP地址：172.16.31.21/16
数据库主从复制实现
三.编译安装mariadb数据库过程略。
提供的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
# grep -v '^#' /etc/my.cnf
port = 3306
socket = /tmp/mysql.sock
port = 3306
socket = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
thread_concurrency = 4
datadir = /mydata/data
innodb_file_per_table = on
skip_name_resolve = on
quick
max_allowed_packet = 16M
no-auto-rehash
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
interactive-timeout
我这里提供一个脚本，基本上可以实现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
# cat installmysql.sh
#!/bin/bash
#install mysql
mysql_dir="/usr/local/mysql"
mysql_datadir="/mydata/data"
mysql_logdir="/mydata/data/"
mysql_passwd="oracle"
function install_mysql(){
yum groupinstall -y Development Tools
yum install -y cmake ncurses-devel openssl-devel openssl
cd /root/
useradd -M -s /sbin/nologin mysql
mkdir -p $mysql_datadir
chown mysql.mysql -R $mysql_datadir
tar zxf mariadb-10.0.12.tar.gz
cd mariadb-10.0.12
cmake . -DCMAKE_INSTALL_PREFIX=$mysql_dir/
-DMYSQL_DATADIR=$mysql_datadir
-DWITH_INNOBASE_STORAGE_ENGINE=1
-DWITH_ARCHIVE_STORAGE_ENGINE=1
-DWITH_BLACKHOLE_STORAGE_ENGINE=1
-DENABLED_LOCAL_INFILE=1
-DMYSQL_TCP_PORT=3306
-DWITH_SSL=system
-DWITH_ZLIB=system
-DWITH_LIBWRAP=0
-DCMAKE_THREAD_PREFER_PTHREAD=1
-DEXTRA_CHARSETS=all
-DDEFAULT_CHARSET=utf8
-DDEFAULT_COLLATION=utf8_general_ci
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock
-DWITH_DEBUG=0
make && make install
rm -rf /etc/my.cnf
rm -rf /etc/init.d/mysqld
cp /root/my.cnf /etc/my.cnf
cp support-files/mysql.server /etc/init.d/mysqld
chmod 755 /etc/init.d/mysqld
chkconfig --add mysqld
chkconfig mysqld on
chown mysql.mysql -R $mysql_logdir
chown mysql.mysql -R $mysql_datadir
$mysql_dir/scripts/mysql_install_db --user=mysql --basedir=$mysql_dir --datadir=$mysql_datadir
/sbin/service mysqld start
echo 'export PATH=$PATH:'$mysql_dir'/bin' >> /etc/profile
source /etc/profile
$mysql_dir/bin/mysql -e "grant all privileges on *.* to root@'%' identified by '$mysql_passwd' with grant option;"
$mysql_dir/bin/mysql -e "flush privileges;"
$mysql_dir/bin/mysql -e "delete from mysql.user where password='';"
/sbin/service mysqld restart
echo "mysql install success!"
}
install_mysql
将数据库源码包和配置好的my.cnf文件放置在/root目录下后即可进行编译安装；
四.数据库主从复制
1.主节点node3配置
1
2
3
4
5
6
7
8
9
对master进行配置，包括打开二进制日志，指定唯一的servr ID。例如，在配置文件加入如下值：
配置/etc/my.cnf在这个配置段加入如下内容：
#二进制变更日志
log-bin=mysql-bin
#二进制日志格式为混合模式
binlog_format=mixed
#为主服务器node3的ID值
server-id = 1
2.从节点node3配置：
1
2
3
4
5
6
7
配置/etc/my.cnf在这个配置段加入如下内容：
log-bin=mysql-bin
binlog_format=mixed
server-id = 10
relay-log = relay-bin
log_slave_updates = 1
read_only = on
配置简要介绍：
server_id是必须的，而且唯一。
slave没有必要开启二进制日志，但是在一些情况下，必须设置，例如，如果slave为其它slave的master，必须设置bin_log。在这里，我们开启了二进制日志，而且显示的命名(默认名称为hostname，但是，如果hostname改变则会出现问题)。
relay_log配置中继日志，log_slave_updates表示slave将复制事件写进自己的二进制日志(后面会看到它的用处)。
有些人开启了slave的二进制日志，却没有设置log_slave_updates，然后查看slave的数据是否改变，这是一种错误的配置。所以，尽量使用read_only，它防止改变数据(除了特殊的线程)。但是，read_only并不是很实用，特别是那些需要在slave上创建表的应用。
3.在Master节点node3的数据库中建立一个备份帐户：每个slave使用标准的MySQL用户名和密码连接master。进行复制操作的用户会授予REPLICATION SLAVE权限。用户名的密码都会存储在文本文件master.info中
命令如下：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.Commands end with ; or g.
Your MariaDB connection id is 5
Server version: 10.0.12-MariaDB-log Source distribution
Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
#建立一个帐户repluser，并且只能允许172.16.0.0这个网段来登陆，密码是replpass。
MariaDB [(none)]> grant replication client,replication slave on *.* to 'repluser'@'172.16.%.%' identified by 'replpass';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> q
Bye
4.拷贝数据
（假如是你完全新安装mysql主从服务器，这个一步就不需要。因为新安装的master和slave有相同的数据）
关停Master服务器，将Master中的数据拷贝到从服务器节点中，使得Master和slave中的数据同步，并且确保在全部设置操作结束前，禁止在Master和slave服务器中进行写操作，使得两数据库中的数据一定要相同！
我这里都是全新安装，一切写操作都未进行过；
5.重启mysqld服务器，主从节点都重启；
主节点：
1
2
3
4
5
6
7
8
9
10
# service mysqld restart
Shutting down MySQL.
Starting MySQL.
# ls /mydata/data/
aria_log.00000001multi-master.infomysql-bin.000005 performance_schema
aria_log_control mysql mysql-bin.000006 relaylog
binlog mysql-bin.000001 mysql-bin.000007 test
ibdata1 mysql-bin.000002 mysql-bin.index
ib_logfile0 mysql-bin.000003 node3.stu31.com.err
ib_logfile1 mysql-bin.000004 node3.stu31.com.pid
从节点：
1
2
3
4
5
6
7
8
9
10
11
# service mysqld restart
Shutting down MySQL...
Starting MySQL.
#数据复制还未发生，所有中继日志还是未出现的；
# ls /mydata/data/
aria_log.00000001multi-master.infomysql-bin.000005 performance_schema
aria_log_control mysql mysql-bin.000006 relaylog
binlog mysql-bin.000001 mysql-bin.000007 test
ibdata1 mysql-bin.000002 mysql-bin.index
ib_logfile0 mysql-bin.000003 node4.stu31.com.err
ib_logfile1 mysql-bin.000004 node4.stu31.com.pid
6.启动从节点的中继日志
接下来就是让slave连接master，并开始重做master二进制日志中的事件。你不应该用配置文件进行该操作，而应该使用CHANGE MASTER TO语句，该语句可以完全取代对配置文件的修改，而且它可以为slave指定不同的master，而不需要停止服务器。
命令介绍如下：使用help change master to即可查看命令选项
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CHANGE MASTER TO option [, option] ...
option:
MASTER_BIND = 'interface_name'
| MASTER_HOST = 'host_name' 主服务器地址
| MASTER_USER = 'user_name' 有复制权限的用户名
| MASTER_PASSWORD = 'password' 用户密码
| MASTER_PORT = port_num 主服务器的端口
| MASTER_CONNECT_RETRY = interval 连接重试时间间隔
| MASTER_HEARTBEAT_PERIOD = interval 心跳检测时间间隔
| MASTER_LOG_FILE = 'master_log_name' 主服务器二进制日志文件
| MASTER_LOG_POS = master_log_pos 二进制日志文件中的位置
| RELAY_LOG_FILE = 'relay_log_name' 中继日志文件名称定义
| RELAY_LOG_POS = relay_log_pos 中继日志时间点定义
| MASTER_SSL = {0|1} 下面都都是跟SSL安全传输相关的
| MASTER_SSL_CA = 'ca_file_name'
| MASTER_SSL_CAPATH = 'ca_directory_name'
| MASTER_SSL_CERT = 'cert_file_name'
| MASTER_SSL_KEY = 'key_file_name'
| MASTER_SSL_CIPHER = 'cipher_list'
| MASTER_SSL_VERIFY_SERVER_CERT = {0|1}
| IGNORE_SERVER_IDS = (server_id_list)
我们需要记录的二进制日志文件的时间点是最后的那个点，我们先去主服务器节点查看哪个时间点，因为我们是新服务器，主节点上没神马重要数据：
1
2
3
4
5
6
7
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000007 | 326 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
启动从节点的中继日志：
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
# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor.Commands end with ; or g.
Your MariaDB connection id is 5
Server version: 10.0.12-MariaDB-log Source distribution
Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
#查看只读打开与否；
MariaDB [(none)]> show global variables like '%read_only%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| innodb_read_only | OFF |
| read_only | ON |
| tx_read_only | OFF |
+------------------+-------+
3 rows in set (0.01 sec)
#启动中继日志的命令如下；
MariaDB [(none)]> change master to master_host='172.16.31.20',master_user='repluser',master_password='replpass',master_log_file='mysql-bin.000007',master_log_pos=326,master_connect_retry=5,master_heartbeat_period=2;
Query OK, 0 rows affected (0.07 sec)
#查看从服务器状态；
MariaDB [(none)]> show slave statusG
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.16.31.20
Master_User: repluser
Master_Port: 3306
Connect_Retry: 5
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 326
Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000007
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: 326
Relay_Log_Space: 248
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
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
1 row in set (0.00 sec)
#通过查看从服务器状态，我们知道了从服务器的IO线程和SQL线程还未开启，下面我们就开启从服务器节点的复制进程，实现主从复制；
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
#再次查看从服务器状态，主要关注IO线程和SQL线程的开启状况：
MariaDB [(none)]> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.31.20
Master_User: repluser
Master_Port: 3306
Connect_Retry: 5
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 326
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 535
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes #IOthread是否运行，如果为No代表slave运行不正常
Slave_SQL_Running: Yes #SQLthread是否运行，如果为No代表slave运行不正常
信息略......
Seconds_Behind_Master: 0
slave的I/O和SQL线程都已经开始运行，而且Seconds_Behind_Master不再是NULL。日志的位置增加了，意味着一些事件被获取并执行了。如果你在master上进行修改，你可以在slave上看到各种日志文件的位置的变化，同样，你也可以看到数据库中数据的变化。测试在下面进行，我们先来看看主从节点的线程的状态；
7.查看master和slave上线程的状态。在master上，你可以看到slave的I/O线程创建的连接：
在Master节点node3上输入show processlistG;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
MariaDB [(none)]> show processlist G
*************************** 1. row ***************************
Id: 5
User: repluser
Host: 172.16.31.21:52875
db: NULL
Command: Binlog Dump
Time: 477
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
Progress: 0.000
*************************** 2. row ***************************
Id: 6
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: init
Info: show processlist
Progress: 0.000
2 rows in set (0.00 sec)
1.row为处理slave的I/O线程的连接。
在Slave服务器节点node4上运行该语句：
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
MariaDB [(none)]> show processlist G
*************************** 1. row ***************************
Id: 5
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: init
Info: show processlist
Progress: 0.000
*************************** 2. row ***************************
Id: 6
User: system user
Host:
db: NULL
Command: Connect
Time: 587
State: Waiting for master to send event
Info: NULL
Progress: 0.000
*************************** 3. row ***************************
Id: 7
User: system user
Host:
db: NULL
Command: Connect
Time: 587
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
Progress: 0.000
3 rows in set (0.00 sec)
2.row为SQL线程状态。
3.row为I/O线程状态。
8.主从复制测试；
我们在主节点node3上进行导入数据库hellodb的操作：
1
2
# mysql -uroot -p < hellodb.sql
Enter password:
我这里提供附件了，可以到附件中下载，免费的；
数据库存储引擎尽量是innodb的哦。
导入完成后我们到主节点查看：
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
# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor.Commands end with ; or g.
Your MariaDB connection id is 10
Server version: 10.0.12-MariaDB-log Source distribution
Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| binlog |
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| relaylog |
| test |
+--------------------+
7 rows in set (0.03 sec)
MariaDB [(none)]> use hellodb
Database changed
MariaDB > show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)
MariaDB > q
Bye
到从节点查看复制状态，可以看出复制已经进行了；
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
MariaDB [(none)]> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.31.20
Master_User: repluser
Master_Port: 3306
Connect_Retry: 5
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 8640
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 8849
Relay_Master_Log_File: mysql-bin.000007
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: 8640
Relay_Log_Space: 9140
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
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
1 row in set (0.00 sec)
查看从节点的数据库复制完成与否，可以查看出我们导入的数据库是存在的：
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
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| binlog |
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| relaylog |
| test |
+--------------------+
7 rows in set (0.02 sec)
MariaDB [(none)]> use hellodb;
Database changed
MariaDB > show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.01 sec)
测试复制是成功的；
至此，数据库的主从复制的实验就完成了，下面进行半同步复制操作实验；
五.半同步复制
半同步复制简述
半同步意思：表示Master服务器只需要接收到其中一台Slave的返回信息，就会commit;否则需要等待直到超时时间然后切换成异步再提交；这样做的目的可以使主从数据库的数据延迟缩小，可以在损失很小的性能的前提下提高数据安全性；
1.半同步的开启，只需要在Master与Slave服务器上都安装上半同步的插件并启用即可；而插件在Mysql的安装目录中："/usr/local/mysql/lib/plugin/"
1
2
3
4
5
6
MariaDB数据库提供的半同步插件如下：
# ls /usr/local/mysql/lib/plugin/ |grep '^semisync'
#用于Master服务器安装的半同步插件
semisync_master.so
#用于Slave服务器安装的半同步插件
semisync_slave.so
2.在Master与Slave服务器分别安装半同步插件
在Master服务器安装半同步插件
1
2
3
4
# mysql
MariaDB [(none)]> install plugin rpl_semi_sync_master soname 'semisync_master.so'; #安装Master半同步插件
MariaDB [(none)]> set global rpl_semi_sync_master_enabled = 1; #开启Master半同步功能
MariaDB [(none)]> set global rpl_semi_sync_master_timeout = 2000; #超时时间毫秒级
在Slave服务器安装半同步插件
1
2
3
4
# mysql
MariaDB [(none)]> install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; #安装Slave半同步插件
MariaDB [(none)]> set global rpl_semi_sync_slave_enabled = 1; #开启Slave半同步功能
MariaDB [(none)]> stop slave io_thread;start slave io_thread; #重启IO线程生效
3.在主备节点查看半同步开启状态：
Master主节点：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
MariaDB [(none)]> show global 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 | 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)
Slave备节点：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
MariaDB [(none)]> show global status like '%semi%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |#半同步状态已经开启了；
+----------------------------+-------+
1 row in set (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 |
+---------------------------------+-------+
3 rows in set (0.00 sec)
4.在Master服务器上将前面导入的hellodb数据库删除，然后验证Slave服务器
Master节点删除数据库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
25
26
27
# mysql -uroot -p -e 'show databases';
Enter password:
+--------------------+
| Database |
+--------------------+
| binlog |
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| relaylog |
| test |
+--------------------+
# mysql -uroot -p -e 'drop database hellodb';
Enter password:
# mysql -uroot -p -e 'show databases';
Enter password:
+--------------------+
| Database |
+--------------------+
| binlog |
| information_schema |
| mysql |
| performance_schema |
| relaylog |
| test |
+--------------------+
到Slave节点验证数据库是否删除：
1
2
3
4
5
6
7
8
9
10
11
12
# mysql -uroot -p -e 'show databases';
Enter password:
+--------------------+
| Database |
+--------------------+
| binlog |
| information_schema |
| mysql |
| performance_schema |
| relaylog |
| test |
+--------------------+
至此，基于半同步复制的主从数据库服务器构建完毕；
注意：以上配置都不能永久生效，如果想要永久生效，将以上配置加入到配置文件重启服务即可；
六.基于SSL安全传输的主从复制的实现
基于SSL安全传输的主从复制的简要介绍：
备份数据库是生产环境中的首要任务，重中之重，有时候不得不通过网络进行数据库的复制，这样就需要保证数据在网络传输过程中的安全性，因此使用基于SSL的复制会大加强数据的安全性。
由于Mysql的主从复制是明文传送的，但如果在生产环境中跨网络我们使用主从还是明文传送的话，就保证不了数据的安全性，为了解决这一问题，我们需要加密进行传送，也就是基于SSL的加密方法进行传输数据。
1.设置Master主节点为CA证书服务器。
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
# cd /etc/pki/CA
#创建私钥；
# (umask 077;openssl genrsa -out private/cakey.pem 2048)
Generating RSA private key, 2048 bit long modulus
......................................................+++
.................................+++
e is 65537 (0x10001)
#生成自签署证书；
# openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 3650
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) :CN
State or Province Name (full name) []:HA
Locality Name (eg, city) :ZZ
Organization Name (eg, company) :magedu
Organizational Unit Name (eg, section) []:ops
Common Name (eg, your name or your server's hostname) []:node3.stu31.com
Email Address []:admin@stu31.com
创建索引库文件和序列号文件：
# touch index.txt; echo 01 > serial;
# ls
cacert.pemcertscrlindex.txtnewcertsprivateserial
2.为Master节点node3创建证书申请并由CA服务器签发证书
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
#为数据库服务器创建ssl证书存放路径
# mkdir /usr/local/mysql/ssl
# cd /usr/local/mysql/ssl
#创建私钥文件；
# (umask 077;openssl genrsa -out master.key 2048)
Generating RSA private key, 2048 bit long modulus
..........................+++
..................................................................................................+++
e is 65537 (0x10001)
#生成证书申请；
# openssl req -new -key master.key -out master.csr -days 3650
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) :CN
State or Province Name (full name) []:HA
Locality Name (eg, city) :ZZ
Organization Name (eg, company) :magedu
Organizational Unit Name (eg, section) []:ops
Common Name (eg, your name or your server's hostname) []:node3.stu31.com
Email Address []:admin@stu31.com
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
#CA服务器签署证书请求；
# openssl ca -in master.csr -out master.crt -days 3650
Using configuration from /etc/pki/tls/openssl.cnf
Check that the request matches the signature
Signature ok
Certificate Details:
Serial Number: 1 (0x1)
Validity
Not Before: Jan 24 08:16:01 2015 GMT
Not After : Jan 21 08:16:01 2025 GMT
Subject:
countryName = CN
stateOrProvinceName = HA
organizationName = magedu
organizationalUnitName = ops
commonName = node3.stu31.com
emailAddress = admin@stu31.com
X509v3 extensions:
X509v3 Basic Constraints:
CA:FALSE
Netscape Comment:
OpenSSL Generated Certificate
X509v3 Subject Key Identifier:
7A:F9:82:44:C2:D2:2B:EE:48:BF:4E:02:31:7D:50:8C:C5:A1:CB:36
X509v3 Authority Key Identifier:
keyid:A6:00:73:4C:AC:37:A2:C2:A8:A6:CD:D0:8C:D3:8F:37:46:8E:6A:E6
Certificate is to be certified until Jan 21 08:16:01 2025 GMT (3650 days)
Sign the certificate? :y
1 out of 1 certificate requests certified, commit? y
Write out database with 1 new entries
Data Base Updated
3.为Slave节点node4创建证书申请并由CA服务器签发证书
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
#为slave节点node4的数据库服务器创建证书存放路径；
# mkdir /usr/local/mysql/ssl
# cd /usr/local/mysql/ssl
#生成私钥；
# (umask 077;openssl genrsa -out slave.key 2048)
Generating RSA private key, 2048 bit long modulus
.................................................................+++
.....+++
e is 65537 (0x10001)
#生成证书申请文件；
# openssl req -new -key slave.key -out slave.csr -days 3650
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) :CN
State or Province Name (full name) []:HA
Locality Name (eg, city) :ZZ
Organization Name (eg, company) :magedu
Organizational Unit Name (eg, section) []:ops
Common Name (eg, your name or your server's hostname) []:node4.stu31.com
Email Address []:root@stu31.com
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
为Slave服务器签署证书
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
#需要将slave节点的证书申请复制到CA服务器，
# scp slave.csr node3:/tmp
root@node3's password:
slave.csr 100% 1037 1.0KB/s 00:00
#让CA服务器签署证书；
# openssl ca -in /tmp/slave.csr -out /tmp/slave.crt -days 3650
Using configuration from /etc/pki/tls/openssl.cnf
Check that the request matches the signature
Signature ok
Certificate Details:
Serial Number: 2 (0x2)
Validity
Not Before: Jan 24 08:22:04 2015 GMT
Not After : Jan 21 08:22:04 2025 GMT
Subject:
countryName = CN
stateOrProvinceName = HA
organizationName = magedu
organizationalUnitName = ops
commonName = node4.stu31.com
emailAddress = root@stu31.com
X509v3 extensions:
X509v3 Basic Constraints:
CA:FALSE
Netscape Comment:
OpenSSL Generated Certificate
X509v3 Subject Key Identifier:
11:99:B6:ED:1D:E3:87:BA:28:CA:87:0C:73:28:4C:60:D8:3E:BD:CA
X509v3 Authority Key Identifier:
keyid:A6:00:73:4C:AC:37:A2:C2:A8:A6:CD:D0:8C:D3:8F:37:46:8E:6A:E6
Certificate is to be certified until Jan 21 08:22:04 2025 GMT (3650 days)
Sign the certificate? :y
1 out of 1 certificate requests certified, commit? y
Write out database with 1 new entries
Data Base Updated
证书在CA服务器节点签署好后，拷贝到slave服务器：
1
2
3
4
5
# scp node3:/tmp/slave.crt ./
root@node3's password:
slave.crt 100% 4568 4.5KB/s 00:00
# ls
slave.crtslave.csrslave.key
4.将CA证书拷贝到Slave服务器并为Master拷贝一份
1
2
# scp /etc/pki/CA/cacert.pem node4:/usr/local/mysql/ssl/
# cp /etc/pki/CA/cacert.pem /usr/local/mysql/ssl/
#修改Master与Slave服务器证书属主、属组为mysql用户
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
主节点的权限授予：
# chown -R mysql.mysql /usr/local/mysql/ssl/
# ls /usr/local/mysql/ssl/ -l
total 20
-rw-r--r-- 1 mysql mysql 1391 Jan 24 16:25 cacert.pem
-rw-r--r-- 1 mysql mysql 4573 Jan 24 16:16 master.crt
-rw-r--r-- 1 mysql mysql 1037 Jan 24 16:14 master.csr
-rw------- 1 mysql mysql 1675 Jan 24 16:11 master.key
#从节点的权限授予：
# chown -R mysql.mysql /usr/local/mysql/ssl/
# ls -l /usr/local/mysql/ssl/
total 20
-rw-r--r-- 1 mysql mysql 1391 Jan 24 16:25 cacert.pem
-rw-r--r-- 1 mysql mysql 4568 Jan 24 16:23 slave.crt
-rw-r--r-- 1 mysql mysql 1037 Jan 24 16:18 slave.csr
-rw------- 1 mysql mysql 1679 Jan 24 16:17 slave.key
5.在Master与Slave服务器修改主配置文件开启SSL加密功能
修改Master服务器配置文件：
1
2
3
4
5
6
7
8
# vim /etc/my.cnf #添加如下选项
#在此段中添加如下配置
ssl #开启SSL功能
ssl_ca = /usr/local/mysql/ssl/cacert.pem #指定CA文件位置
ssl_cert = /usr/local/mysql/ssl/master.crt #指定证书文件位置
ssl_key = /usr/local/mysql/ssl/master.key #指定密钥所在位置
重启mysqld服务：
# service mysqld restart
修改Slave服务器的配置文件：
1
2
3
4
5
6
7
8
# vim /etc/my.cnf
ssl
ssl_ca = /usr/local/mysql/ssl/cacert.pem
ssl_cert = /usr/local/mysql/ssl/slave.crt
ssl_key = /usr/local/mysql/ssl/slave.key
重启mysqld服务：
# service mysqld restart
6.在Master服务器查看SSL加密是否开启；然后创建授权一个基于密钥认证的用户
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
# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor.Commands end with ; or g.
Your MariaDB connection id is 7
Server version: 10.0.12-MariaDB-log Source distribution
Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
#SSL加密是开启的；
MariaDB [(none)]> show variables like '%ssl%';
+---------------+---------------------------------+
| Variable_name | Value |
+---------------+---------------------------------+
| have_openssl| YES |
| have_ssl | YES |
| ssl_ca | /usr/local/mysql/ssl/cacert.pem |
| ssl_capath | |
| ssl_cert | /usr/local/mysql/ssl/master.crt |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | /usr/local/mysql/ssl/master.key |
+---------------+---------------------------------+
9 rows in set (0.00 sec)
#创建授权一个基于密钥认证的用户
MariaDB [(none)]> grant replication client,replication slave on *.* to 'slaveuser'@'172.16.%.%' identified by 'oracle' require ssl;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
#查看Master服务器二进制日志文件和事件位置用于Slave服务器连接从这个位置开始复制；
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000008 | 656 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> q
Bye
7.在Slave服务器测试使用加密用户指定密钥连接Master服务器
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# mysql -uslaveuser -poracle -h 172.16.31.20 --ssl-ca=/usr/local/mysql/ssl/cacert.pem --ssl-cert=/usr/local/mysql/ssl/slave.crt --ssl-key=/usr/local/mysql/ssl/slave.key
Welcome to the MariaDB monitor.Commands end with ; or g.
Your MariaDB connection id is 8
Server version: 10.0.12-MariaDB-log Source distribution
Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
#可以看出时间节点和上面显示的一致；
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000008 | 656 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> q
Bye
8.查看Slave服务器SSL是否开启并连接Master服务器
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
# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.Commands end with ; or g.
Your MariaDB connection id is 6
Server version: 10.0.12-MariaDB-log Source distribution
Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
#可以看出slavefuwq节点开启了SSL
MariaDB [(none)]> show variables like '%ssl%';
+---------------+---------------------------------+
| Variable_name | Value |
+---------------+---------------------------------+
| have_openssl| YES |
| have_ssl | YES |
| ssl_ca | /usr/local/mysql/ssl/cacert.pem |
| ssl_capath | |
| ssl_cert | /usr/local/mysql/ssl/slave.crt|
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | /usr/local/mysql/ssl/slave.key|
+---------------+---------------------------------+
9 rows in set (0.01 sec)
#由于半同步复制时开启的slave，我们需要先关闭；
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.03 sec)
#设置连接master节点；
MariaDB [(none)]> change master to master_host='172.16.31.20',master_user='slaveuser',master_password='oracle',master_log_file='mysql-bin.000008',master_log_pos=656,master_ssl=1,master_ssl_ca='/usr/local/mysql/ssl/cacert.pem',master_ssl_cert='/usr/local/mysql/ssl/slave.crt',master_ssl_key='/usr/local/mysql/ssl/slave.key';
Query OK, 0 rows affected (0.09 sec)
#启动slave节点的IO线程和SQL线程；
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)
#查看Slave服务器状态
MariaDB [(none)]> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.31.20
Master_User: slaveuser
Master_Port: 3306
Connect_Retry: 5
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 656
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 535
Relay_Master_Log_File: mysql-bin.000008
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: 656
Relay_Log_Space: 826
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /usr/local/mysql/ssl/cacert.pem
Master_SSL_CA_Path:
Master_SSL_Cert: /usr/local/mysql/ssl/slave.crt
Master_SSL_Cipher:
Master_SSL_Key: /usr/local/mysql/ssl/slave.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
Master_SSL_Crl: /usr/local/mysql/ssl/cacert.pem
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
1 row in set (0.00 sec)
可以发现，我们的slave的IO线程和SQL线程开启了，已经在开始同步了；
9.基于SSL安全传输的测试；
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
我们将删除的hellodb数据库重新导入仅Master服务器节点；
# mysql -uroot -p < hellodb.sql
Enter password:
# mysql -uroot -p -e 'show databases';
Enter password:
+--------------------+
| Database |
+--------------------+
| binlog |
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| relaylog |
| test |
+--------------------+
我们到Slave节点查看数据库hellodb存在与否：
1
2
3
4
5
6
7
8
9
10
11
12
13
# mysql -u root -p -e 'show databases';
Enter password:
+--------------------+
| Database |
+--------------------+
| binlog |
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| relaylog |
| test |
+--------------------+
至此，MariaDB/MySQL的基于SSL加密的复制已全部完成。
七.复制相关的文件介绍
我们到slave节点查看数据文件：
1
2
3
4
5
6
7
8
# ls /mydata/data/
aria_log.00000001master.info mysql-bin.000005 performance_schema
aria_log_control multi-master.infomysql-bin.000006 relay-bin.000001
binlog mysql mysql-bin.000007 relay-bin.000002
hellodb mysql-bin.000001 mysql-bin.000008 relay-bin.index
ibdata1 mysql-bin.000002 mysql-bin.index relaylog
ib_logfile0 mysql-bin.000003 node4.stu31.com.errrelay-log.info
ib_logfile1 mysql-bin.000004 node4.stu31.com.pidtest
这里除了基本的数据库文件和二进制日志，还有一些与复制相关的文件。如下：
(1)mysql-bin.index
服务器一旦开启二进制日志，会产生一个与二日志文件同名，但是以.index结尾的文件。它用于跟踪磁盘上存在哪些二进制日志文件。MySQL用它来定位二进制日志文件。它的内容如下：
1
2
3
4
5
6
7
8
9
# cat /mydata/data/mysql-bin.index
./mysql-bin.000001
./mysql-bin.000002
./mysql-bin.000003
./mysql-bin.000004
./mysql-bin.000005
./mysql-bin.000006
./mysql-bin.000007
./mysql-bin.000008
(2)mysql-relay-bin.index
该文件的功能与mysql-bin.index类似，但是它是针对中继日志，而不是二进制日志。内容如下：
1
2
3
# cat /mydata/data/relay-bin.index
./relay-bin.000001
./relay-bin.000002
(3)master.info
保存master的相关信息。不要删除它，否则，slave重启后不能连接master。内容如图：
(4)relay-log.info
包含slave中当前二进制日志和中继日志的信息。
1
2
3
4
5
# cat /mydata/data/relay-log.info
./relay-bin.000002
8849
mysql-bin.000008
8970
八.双主复制模型架构构建
1.双主复制模型架构简介
主-主复制（也叫双主复制或双向复制）包含两台服务器，每一个都配配置为对方的主库和备库，换句话说，它们是一对主库。
架构如图：
2.双主模型架构的配置过程：
（1）.各自使用不同的server-id；
（2）.都启用二进制日志和中继日志；
（3）.定义自动增长的id字段的增长方式
（4）.都授权有复制权限的用户帐号；
（5）.各自把对方指定为主服务器；
3.考虑一个问题：
如果A服务器上自动增长的列编号有一个35，此时还没有同步到B服务器上，在B服务器上插入一条数据，编号也是35。当同步A的35到B服务器上来的话，必然产生数据丢失。
解决方法：
我们只要保证两台服务器上插入的自增长数据不同就可以了
如：A插入奇数ID，B插入偶数ID，当然如果服务器多的话，你可以定义算法，只要不同就可以了。
4.双主复制的数据库主配置文件配置
node3节点的配置：
1
2
3
4
5
6
7
8
9
10
11
# vim /etc/my.cnf
#在此配置段中增加如下内容；
log-bin=mysql-bin
binlog_format=mixed
server-id = 1
relay-log=relay-bin
#第一个变量名 auto_increment_offset 指自增字段的起始值。
auto_increment_offset=1
#第二个变量名 auto_increment_increment 就是指字段一次递增多少；
auto_increment_increment=2
log_slave_updates = 1
node4节点的配置：
1
2
3
4
5
6
7
8
9
10
11
# vim /etc/my.cnf
log-bin=mysql-bin
binlog_format=mixed
server-id = 10
relay-log = relay-bin
log_slave_updates = 1
#第一个变量名 auto_increment_offset 指自增字段的起始值。
auto_increment_offset=2
#第二个变量名 auto_increment_increment 就是指字段一次递增多少；
auto_increment_increment=2
其它配置不变，由于我先前实验做了基于SSL的安全复制，我们需要将ssl配置段删除；
略。
配置完成后重启mysqld服务即可；
5.设置复制帐号；
两个节点都需要设置帐号，为了方便我们就全部设置一致：
1
2
MariaDB [(none)]> grant replication slave,replication client on *.* to repluser@'172.16.%.%' identified by 'replpass';
MariaDB [(none)]> flush privileges;
上述命令两个节点都执行一遍；
6.查看两个节点的时间点；
node3节点的时间点；
1
2
3
4
5
6
7
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000009 | 653 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
node4节点的时间点：
1
2
3
4
5
6
7
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000009 | 653 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
两个节点的时间点都是一致的；那就方便设置了；
7.双主复制模型开启设置
node3节点启动：
1
2
3
4
5
6
#开启复制；
MariaDB [(none)]> change master to master_host='172.16.31.21',master_user='repluser',master_password='replpass',master_log_file='mysql-bin.000009',master_log_pos=653;
Query OK, 0 rows affected (0.08 sec)
#开启复制进程；
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
node4节点启动：
1
2
3
4
MariaDB [(none)]> change master to master_host='172.16.31.20',master_user='repluser',master_password='replpass',master_log_file='mysql-bin.000009',master_log_pos=653;
Query OK, 0 rows affected (0.02 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
8.查看节点的复制状态信息；
node3的节点的主从复制状态：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
MariaDB [(none)]> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.31.21
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 653
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 535
Relay_Master_Log_File: mysql-bin.000009
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
信息略；
Seconds_Behind_Master: 0
1 row in set (0.00 sec)
node4节点的主从复制状态信息；
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
MariaDB [(none)]> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.31.20
Master_User: repluser
Master_Port: 3306
Connect_Retry: 5
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 653
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 535
Relay_Master_Log_File: mysql-bin.000009
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
信息略；
Seconds_Behind_Master: 0
1 row in set (0.00 sec)
9.双主复制架构测试：
在node3节点插入2行数据：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#创建数据库mydb；
MariaDB [(none)]> create database mydb;
Query OK, 1 row affected (0.00 sec)
#切换到数据库mydb；
MariaDB [(none)]> use mydb;
Database changed
#创建表tb1；
MariaDB > create table tb1 (id int unsigned not null auto_increment primary key, name char(30));
Query OK, 0 rows affected (0.08 sec)
#插入数据tom；
MariaDB > insert into tb1 (name) values ('tom');
Query OK, 1 row affected (0.01 sec)
#插入数据jerry；
MariaDB > insert into tb1 (name) values ('jerry');
Query OK, 1 row affected (0.01 sec)
#查看ID，可发现node3增长是按奇数增长的；
MariaDB > select * from tb1;
+----+-------+
| id | name|
+----+-------+
|1 | tom |
|3 | jerry |
+----+-------+
2 rows in set (0.00 sec)
node4节点也插入2条数据：
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
#查看数据库；
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| binlog |
| hellodb |
| information_schema |
| mydb |
| mysql |
| performance_schema |
| relaylog |
| test |
+--------------------+
8 rows in set (0.00 sec)
MariaDB [(none)]> use mydb;
Database changed
MariaDB > insert into tb1 (name) values ('andy');
Query OK, 1 row affected (0.04 sec)
MariaDB > insert into tb1 (name) values ('allen');
Query OK, 1 row affected (0.01 sec)
MariaDB > select * from tb1;
+----+-------+
| id | name|
+----+-------+
|1 | tom |
|3 | jerry |
|4 | andy|
|6 | allen |
+----+-------+
4 rows in set (0.00 sec)
让在node3节点上插入的行的自动增长都为奇数，让node4节点服务器上的自动增长都为偶数。
至此，MariaDB/MySQL的主从复制、半同步复制、基于SSL加密的复制以及MariaDB/MySQL的双主模型架构就全部完成。
九.数据库复制过滤器功能
1.复制过滤器功能的简介
主服务器只能过滤到库级别，从服务器可以过滤到表级别;
要想使设置永久生效，就写入配置文件
让slave仅复制有限的几个数据库，而非所有；
有两种实现思路：
(1) 主服务器仅向二进制日志中记录有特定数据库相关的写操作；
问题：即时点还原将无法全面实现；
binlog_do_db= # 数据库白名单
binlog_ignore_db= # 数据库黑名单
(2)从服务器的SQL_THREAD仅在中断日志中读取特定数据相关的语句并应用在本地；
问题：会造成网络带宽和磁盘IO的浪费；
replicate_do_db=(数据库白名单，多个用列表)
replicate_ignore_db=(黑名单)
不建议同时使用，
如果同时启用则以白名单为准，
如果同时出现在白黑名单中，则拒绝复制
replicate_do_table= db_name.table_name(表的白名单)
replicate_ignore_table=(表的黑名单)
replicate_wild_do_table=
replicate_wild_ignore_table=
以上两项支持通配符，进行过滤；
2.复制过滤器功能的实现；
（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
#我们在node4节点实现只允许同步testdb这个数据库；
#先停止slave进程；
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.01 sec)
#设置过滤器的白名单，允许同步的数据库；
MariaDB [(none)]> set global replicate_do_db='testdb';
Query OK, 0 rows affected (0.00 sec)
#启动slave进程；
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
#查看关于过滤器的选项参数；
MariaDB [(none)]> show global variables like '%replicat%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| innodb_replication_delay | 0 |
| replicate_annotate_row_events | OFF |
| replicate_do_db | testdb |
| replicate_do_table | |
| replicate_events_marked_for_skip | replicate |
| replicate_ignore_db | |
| replicate_ignore_table | |
| replicate_wild_do_table | |
| replicate_wild_ignore_table | |
+----------------------------------+-----------+
9 rows in set (0.00 sec)
我们到node3节点创建数据库testdb；
1
2
3
4
5
6
7
8
9
10
11
12
13
14
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| binlog |
| information_schema |
| mysql |
| performance_schema |
| relaylog |
| test |
+--------------------+
6 rows in set (0.00 sec)
MariaDB [(none)]> create database testdb;
Query OK, 1 row affected (0.00 sec)
我们在node4节点上查看数据库，发现testdb被同步过来：
1
2
3
4
5
6
7
8
9
10
11
12
13
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| binlog |
| information_schema |
| mysql |
| performance_schema |
| relaylog |
| test |
| testdb |
+--------------------+
7 rows in set (0.00 sec)
我们在node3节点新建数据库mydb；
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
MariaDB [(none)]> create database mydb;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| binlog |
| information_schema |
| mydb |
| mysql |
| performance_schema |
| relaylog |
| test |
| testdb |
+--------------------+
8 rows in set (0.00 sec)
到node4节点查看数据库，发现mydb没有被同步过来，白名单只允许testdb同步过来：
1
2
3
4
5
6
7
8
9
10
11
12
13
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| binlog |
| information_schema |
| mysql |
| performance_schema |
| relaylog |
| test |
| testdb |
+--------------------+
7 rows in set (0.00 sec)
（2）.基于表做过滤实现
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
#在从服务器节点node4上做hellodb库的students表过滤
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.04 sec)
MariaDB [(none)]> set global replicate_ignore_table='hellodb.students';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show global variables like '%replicat%';
+----------------------------------+------------------+
| Variable_name | Value |
+----------------------------------+------------------+
| innodb_replication_delay | 0 |
| replicate_annotate_row_events | OFF |
| replicate_do_db | testdb |
| replicate_do_table | |
| replicate_events_marked_for_skip | replicate |
| replicate_ignore_db | |
| replicate_ignore_table | hellodb.students |
| replicate_wild_do_table | |
| replicate_wild_ignore_table | |
+----------------------------------+------------------+
9 rows in set (0.00 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
#在从服务器查看从服务器中的hellodb库中的students表中的信息
MariaDB [(none)]> use hellodb;
Database changed
MariaDB > select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu |22 | M | 2 | 3 |
| 2 | Shi Potian |22 | M | 1 | 7 |
| 3 | Xie Yanke |53 | M | 2 | 16 |
| 4 | Ding Dian |32 | M | 4 | 4 |
| 5 | Yu Yutong |26 | M | 3 | 1 |
| 6 | Shi Qing |46 | M | 5 | NULL |
| 7 | Xi Ren |19 | F | 3 | NULL |
| 8 | Lin Daiyu |17 | F | 7 | NULL |
| 9 | Ren Yingying|20 | F | 6 | NULL |
| 10 | Yue Lingshan|19 | F | 3 | NULL |
| 11 | Yuan Chengzhi |23 | M | 6 | NULL |
| 12 | Wen Qingqing|19 | F | 1 | NULL |
| 13 | Tian Boguang|33 | M | 2 | NULL |
| 14 | Lu Wushuang |17 | F | 3 | NULL |
| 15 | Duan Yu |19 | M | 4 | NULL |
| 16 | Xu Zhu |21 | M | 1 | NULL |
| 17 | Lin Chong |25 | M | 4 | NULL |
| 18 | Hua Rong |23 | M | 7 | NULL |
| 19 | Xue Baochai |18 | F | 6 | NULL |
| 20 | Diao Chan |19 | F | 7 | NULL |
| 21 | Huang Yueying |22 | F | 6 | NULL |
| 22 | Xiao Qiao |20 | F | 1 | NULL |
| 23 | Ma Chao |23 | M | 4 | NULL |
| 24 | Xu Xian |27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.01 sec)
主服务器节点node3也是有hellodb数据库的，我们在这个库中的students表中插入一个数据：
1
2
3
4
MariaDB [(none)]> use hellodb;
Database changed
MariaDB > insert into students(stuid,name,age,gender,classid) values (26,'Qiao Feng',35,'M',5);
Query OK, 1 row affected (0.02 sec)
我们到从节点上查看hellodb库的students表，发现在主节点node3插入的数据未同步过来，实现了表过滤操作：
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
MariaDB > select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu |22 | M | 2 | 3 |
| 2 | Shi Potian |22 | M | 1 | 7 |
| 3 | Xie Yanke |53 | M | 2 | 16 |
| 4 | Ding Dian |32 | M | 4 | 4 |
| 5 | Yu Yutong |26 | M | 3 | 1 |
| 6 | Shi Qing |46 | M | 5 | NULL |
| 7 | Xi Ren |19 | F | 3 | NULL |
| 8 | Lin Daiyu |17 | F | 7 | NULL |
| 9 | Ren Yingying|20 | F | 6 | NULL |
| 10 | Yue Lingshan|19 | F | 3 | NULL |
| 11 | Yuan Chengzhi |23 | M | 6 | NULL |
| 12 | Wen Qingqing|19 | F | 1 | NULL |
| 13 | Tian Boguang|33 | M | 2 | NULL |
| 14 | Lu Wushuang |17 | F | 3 | NULL |
| 15 | Duan Yu |19 | M | 4 | NULL |
| 16 | Xu Zhu |21 | M | 1 | NULL |
| 17 | Lin Chong |25 | M | 4 | NULL |
| 18 | Hua Rong |23 | M | 7 | NULL |
| 19 | Xue Baochai |18 | F | 6 | NULL |
| 20 | Diao Chan |19 | F | 7 | NULL |
| 21 | Huang Yueying |22 | F | 6 | NULL |
| 22 | Xiao Qiao |20 | F | 1 | NULL |
| 23 | Ma Chao |23 | M | 4 | NULL |
| 24 | Xu Xian |27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
我们在主节点上创建一张表tb1：
1
2
MariaDB > create table tb1 like students;
Query OK, 0 rows affected (0.11 sec)
我们到从服务器节点查看表是否同步过来：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
MariaDB > show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| tb1 |
| teachers |
| toc |
+-------------------+
8 rows in set (0.00 sec)
至此，数据库的复制过滤器功能就介绍完毕了。
666
页:
[1]