# MySQL Cluster
# MySQL 主从复制原理
参考：https://zhuanlan.zhihu.com/p/96212530
# MySQL 主从同步
环境(MySQL5.7)：
| 角色 | IP |
|---|---|
| Master | 172.16.1.128 |
| Slave | 172.16.1.136 |
在默认安装的test数据库中建一个测试表：
mariadb root@172.16.1.128:test> SHOW CREATE TABLE test\G
***************************[ 1. row ]***************************
Table | test
Create Table | CREATE TABLE `test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL COMMENT '姓名',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试表'
1 row in set
Time: 0.004s
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
# 在 Master 主服务上
在主服务器上为从服务器建立一个连接帐户，并授予REPLICATION SLAVE权限。
mysql> grant replication slave on *.* to 'backup'@'172.16.1.136' identified by '123';
mysql> flush privileges;
1
2
2
创建好用户之后，在从服务器进行登录测试：
mysql -ubackup -p123 -h172.16.1.128
1
修改 mysql 配置文件，/etc/my.cnf：
# 在[mysqld]配置段添加以下内容
server-id=1 # mysql主从里，id必须唯一，范围：1~255
log-bin=master-bin # 其中这两行是本来就有的，可以不用动，添加下面两行即可.指定日志文件
binlog_format="mixed"
binlog-do-db=test # 记录日志的数据库
binlog-ignore-db=mysql # 不记录日志的数据库
1
2
3
4
5
6
2
3
4
5
6
修改完保存退出，然后重启服务，systemctl restart mariadb
查看主服务器状态：
mysql> flush tables with read lock;
mysql> > show master status\G
*************************** 1. row ***************************
File: master-bin.000003
Position: 2007
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
锁表，为了产生环境中不让进新的数据，好让从服务器定位同步位置，初次同步完成后，记得解锁。
mysql> unlock tables;
1
# 在 Slave 从服务器上
编辑配置文件my.cnf：
# 在[mysqld]配置段添加以下内容
server-id=2
log-bin=master-bin
replicate-do-db=test
replicate-ignore-db=mysql,information_schema,performance_schema
1
2
3
4
5
2
3
4
5
修改完保存退出，然后重启服务，systemctl restart mariadb
使用change master命令指定同步位置：
mysql> stop slave;
# 如果发生错误，需要使用 `reset slave;` 来进行重置
mysql> change master to master_host='172.16.1.128',master_user='backup',master_password='123', master_log_file='master-bin.000003',master_log_pos=2007;
1
2
3
2
3
⚠️注意：上面的master_log_file和master_log_pos值要与 Master 主机一致。
查看 Slave 状态：
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.1.128
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 1024
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 783
Relay_Master_Log_File: master-bin.000003
Slave_IO_Running: Yes # 如果看到这一项和下面一项均为 Yes，则表明从服务器配置完成
Slave_SQL_Running: Yes
Replicate_Do_DB: test
Replicate_Ignore_DB: mysql,information_schema,performance_schema
Replicate_Do_Table:
...
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 测试同步
在 Master 服务器中向之前创建好的test空表插入数据：
mysql> insert into test.test(id,name) value(2,'suofeiya');
1
在 Slave 端进行查看：
mysql> select * from test.test;
+----+-----------+
| id | name |
+----+-----------+
| 2 | suofeiya |
+----+-----------+
1
2
3
4
5
6
2
3
4
5
6
# MySQL 双主同步
| 角色 | IP |
|---|---|
| Master/Slave(server1) | 172.16.1.128 |
| Slave/Master(server2) | 172.16.1.136 |
同时在主服务器上建立一个连接用户，并授予REPLIATION SLAVE权限，这里服务器1和服务器2都互为主从，所以需要分别建立。
# 在 server1 上
mysql> grant replication slave on *.* to 'backup'@'172.16.1.136' identified by '123';
mysql> flush privileges;
# 在 server2 上
mysql> grant replication slave on *.* to 'backup'@'172.16.1.128' identified by '123';
mysql> flush privileges;
1
2
3
4
5
6
2
3
4
5
6
双方测试连接，确保同步能够正常进行
修改配置文件/etc/my.cnf的[mysqld]配置段(仅作为参考)：
# --------- server1 ---------
server-id=1
log-bin=mysql-bin
binlog-do-db=test
binlog-ignore-db=mysql
# 主－主形式需要多添加的部分
log-slave-updates
sync_binlog=1
auto_increment_offset=1
auto_increment_increment=2
replicate-do-db=test
replicate-ignore-db=mysql,information_schema
# --------- server2 ---------
server-id=2
log-bin=mysql-bin
replicate-do-db=test
replicate-ignore-db=mysql,information_schema,performance_schema
# 主－主形式需要多添加的部分
binlog-do-db=test
binlog-ignore-db=mysql
log-slave-updates
sync_binlog=1
auto_increment_offset=2
auto_increment_increment=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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
修改完保存退出，随后重启 server1 和server2 的服务，systemctl restart mariadb
查看 server1 和 server2 两主机作为主服务器的状态：
-- server1
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 245
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
-- server2
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 245
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
分别在两主机上使用change master to指定同步位置：
-- server1
mysql> change master to master_host='172.16.1.136',master_user='backup',master_password='123', master_log_file='mysql-bin.000001',master_log_pos=245;
-- server2
mysql> change master to master_host='172.16.1.128',master_user='backup',master_password='123', master_log_file='mysql-bin.000001',master_log_pos=245;
1
2
3
4
2
3
4
然后，分别在两主机上启动从服务器线程并查看其状态：
-- server1
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.1.136
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 245
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running:` Yes` # 看到这两个Yes即表示成功启动
Slave_SQL_Running:` Yes`
Replicate_Do_DB: test
Replicate_Ignore_DB: mysql,information_schema
...
-- server2
mysql>start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.1.128
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 245
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: `Yes`
Slave_SQL_Running: `Yes`
Replicate_Do_DB: test
Replicate_Ignore_DB: mysql,information_schema,performance_schema
Replicate_Do_Table:
...
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
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
测试双主同步：
-- 在 server1 上插入一条数据，看 server2 是否成功同步
mysql> insert into test.test(id,name) value(3,'agou-ops');
-- 在 server2 上进行查看
mysql> select * from test.test;
+----+-----------+
| id | name |
+----+-----------+
| 2 | suofeiya |
`| 3 | agou-ops | `
+----+-----------+
-- 在 server2 上创建一张测试数据表xxx，看 server1 是否成功同步
mysql> create table test.xxx(id INT(10),name VARCHAR(25));
-- 在 server1 上进行查看
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test |
| xxx |
+----------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
至此，双主同步已经成功搭建。
# 基于 SSL 加密传输(主从)
由于 mysql 在复制过程中是明文的，所以就大大降低了安全性，因此需要借助于ssl加密来增加其复制的安全性。
# 预先准备
在配置 ssl 前我们先看下一下 ssl 的状态信息：
mysql> show variables like '%ssl%';
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| have_openssl | DISABLED |
| have_ssl | DISABLED |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_key | |
+---------------+----------+
7 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
可以看到 SSL 处于 DISABLED状态，表示未开启。
要开启 SSL 只需要在配置文件/etc/my.cnf中加入 ssl 即可：
# 在 [mysqld] 配置段
ssl
1
2
2
随后保存退出，重启服务即可，systemctl restart mariadb
查看确认：
mysql> show variables like '%ssl%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_key | |
+---------------+-------+
7 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
# 配置主从服务的 SSL 功能
# 配置 CA 和颁发证书
在Master主机上，配置 CA服务器：
cd /etc/pki/CA
touch index.txt
echo 01 > serial
(umask 077;openssl genrsa -out private/cakey.pem 1024)
openssl req -x509 -new -key private/cakey.pem -out cacert.pem -days 365
1
2
3
4
5
2
3
4
5
为Master准备私钥并颁发证书：
# 创建存放证书的目录
mkdir /usr/local/mysql/ssl -pv
cd /usr/local/mysql/ssl
# 创建所需要的证书
(umask 077;openssl genrsa 1024 > master.key)
openssl req -new -key master.key -out master.csr
openssl ca -in master.csr -out master.crt -days 365
cp /etc/pki/CA/cacert.pem /usr/local/mysql/ssl
chown -R mysql:mysql /usr/local/mysql/ssl
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
在Slave主机上，准备私钥并申请证书：
# 创建存放证书的目录
mkdir /usr/local/mysql/ssl -pv
cd /usr/local/mysql/ssl
# 创建所需要的证书
(umask 077;openssl genrsa 1024 > slave.key)
openssl req -new -key slave.key -out slave.csr
scp ./slave.csr 172.16.1.128:/root
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
在 Master(主服务器)上为 Slave (从服务器)签发证书：
openssl ca -in slave.csr -out slave.crt
scp slave.crt /etc/pki/CA/cacert.pem 172.16.1.136:/usr/local/mysql/ssl
1
2
2
到此为止证书已经准备完成，请确保Master和Slave主机上有如下文件，并且属主和属组为mysql：
# master 主机上
[root@stor1 ssl]\# ll
total 16
-rw-r--r-- 1 mysql mysql 960 May 12 00:05 cacert.pem
-rw-r--r-- 1 mysql mysql 3065 May 12 00:05 master.crt
-rw-r--r-- 1 mysql mysql 647 May 12 00:05 master.csr
-rw------- 1 mysql mysql 887 May 12 00:04 master.key
# slave 主机上
[root@stor2 ssl]\# ll
total 12
-rw-r--r-- 1 mysql mysql 960 May 12 00:10 cacert.pem
-rw-r--r-- 1 mysql mysql 0 May 12 00:10 slave.crt
-rw-r--r-- 1 mysql mysql 647 May 12 00:09 slave.csr
-rw------- 1 mysql mysql 891 May 12 00:06 slave.key
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2
3
4
5
6
7
8
9
10
11
12
13
14
# 修改配置文件
修改Master和Slave主机配置文件：
# 在 [mysqld] 段添加以下内容
# ------------ Master 主机 ------------
server-id=1
log-bin=mysql-bin
binlog-do-db=test
binlog-ignore-db=mysql
relay-log=mysql-relay
auto-increment-increment=2
auto-increment-offset=1
ssl
ssl-ca=/usr/local/mysql/ssl/cacert.pem
ssl-cert=/usr/local/mysql/ssl/master.crt
ssl-key=/usr/local/mysql/ssl/master.key
# ------------ Slave 主机 ------------
server-id=2
log-bin=mysql-bin
relay-log=mysql-relay
skip-slave-start=1
ssl
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
完成后保存退出，然后都重启服务，systemctl restart mariadb
在Master主机进行查看：
mysql> 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_key | /usr/local/mysql/ssl/master.key |
+---------------+---------------------------------+
7 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
在Master主机上创建具有复制权限的用户并授权给从服务器，参考上面的主从，这里就不再赘述。
查看Master主机状态信息并记录下来：
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000005
Position: 245
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
1
2
3
4
5
6
7
2
3
4
5
6
7
配置Slave端：
mysql> CHANGE MASTER TO MASTER_HOST='172.16.1.128',
MASTER_USER='backup',
MASTER_PASSWORD='123',
MASTER_LOG_FILE='mysql-bin.000005',
MASTER_LOG_POS=245,
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';
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
⚠️注意：如果之前已经启动过slave和配置过slave需要将其停掉，并重置：
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> reset slave;
Query OK, 0 rows affected (0.03 sec)
1
2
3
4
5
2
3
4
5
在slave客户端使用 SSL 进行测试连接：
[root@stor2 ssl]\# mysql --ssl-ca=/usr/local/mysql/ssl/cacert.pem --ssl-cert=/usr/local/mysql/ssl/slave.crt --ssl-key=/usr/local/mysql/ssl/slave.key -ubackup -h172.16.1.128 -p123
...
mysql> \s
--------------
mysql Ver 15.1 Distrib 5.5.65-MariaDB, for Linux (x86_64) using readline 5.1
Connection id: 5
Current database:
Current user: backup@172.16.1.136
SSL: Cipher in use is DHE-RSA-AES256-GCM-SHA384
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 5.5.65-MariaDB MariaDB Server
Protocol version: 10
Connection: 172.16.1.128 via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 3 min 40 sec
Threads: 2 Questions: 16 Slow queries: 0 Opens: 0 Flush tables: 2 Open tables: 26 Queries per second avg: 0.072
--------------
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
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
启动slave并查看状态信息：
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.1.128
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 245
Relay_Log_File: mysql-relay.000003
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
测试主从同步：
-- 在 Master 主机上创建一个测试表yyy
mysql> create table test.yyy(id INT(12),name VARCHAR(25));
-- 在 Slave 端进行查看
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| tb_mobile |
| test |
| xxx |
| `yyy` |
+----------------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
# 基于 SSL 加密传输(双主)
参考[基于 SSL 加密传输(主从)](#基于 SSL 加密传输(主从))