MySQL系列之十三 MySQL的復(fù)制
發(fā)布日期：2022-02-10 08:51 | 文章來源：源碼中國
一、MySQL復(fù)制相關(guān)概念
- 主從復(fù)制：主節(jié)點(diǎn)將數(shù)據(jù)同步到多個(gè)從節(jié)點(diǎn)
- 級聯(lián)復(fù)制：主節(jié)點(diǎn)將數(shù)據(jù)同步到一個(gè)從節(jié)點(diǎn)，其他的從節(jié)點(diǎn)在向從節(jié)點(diǎn)復(fù)制數(shù)據(jù)
- 同步復(fù)制：將數(shù)據(jù)從主節(jié)點(diǎn)全部同步到從節(jié)點(diǎn)時(shí)才返回給用戶的復(fù)制策略叫同步復(fù)制
- 異步復(fù)制：只要數(shù)據(jù)寫入到主節(jié)點(diǎn)就立即返回給用戶同步完成
- 讀寫分離：在前端加一個(gè)調(diào)度器，負(fù)責(zé)將改變數(shù)據(jù)的語句和查詢數(shù)據(jù)的語句分開調(diào)度，把寫操作調(diào)度到主節(jié)點(diǎn)，讀操作調(diào)度到從節(jié)點(diǎn)
主節(jié)點(diǎn)：
- dump Thread：為每個(gè)Slave的I/O Thread啟動一個(gè)dump線程，用于向其發(fā)送binary log events
從節(jié)點(diǎn)：
- I/O Thread：向Master請求二進(jìn)制日志事件，并保存于中繼日志中
- SQL Thread：從中繼日志中讀取日志事件，在本地完成重放
跟復(fù)制功能相關(guān)的文件：
- master.info：用于保存slave連接至master時(shí)的相關(guān)信息，例如賬號、密碼、服務(wù)器地址等
- relay-log.info：保存在當(dāng)前slave節(jié)點(diǎn)上已經(jīng)復(fù)制的當(dāng)前二進(jìn)制日志和本地replay log日志的對應(yīng)關(guān)系
復(fù)制架構(gòu)：
- 一主一從
- 一主多從
- 主主復(fù)制
- 環(huán)狀復(fù)制
- 級聯(lián)復(fù)制
- 多主一從
常見的架構(gòu)有主從架構(gòu)或者級聯(lián)架構(gòu)
二、簡單的一主一從架構(gòu)實(shí)現(xiàn)
1、新數(shù)據(jù)庫搭建主從架構(gòu)
1）主服務(wù)器配置
~]# vim /etc/my.cnf
[mysqld]
log_bin
binlog_format=ROW
log-basename=master1
server_id=1
~]# systemctl restart mariadb
~]# mysql
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO testuser@'192.168.0.8' IDENTIFIED BY 'testpass'; #授權(quán)同步賬戶
MariaDB [(none)]> SHOW MASTER LOGS;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| master1-bin.000001 | 26756 |
| master1-bin.000002 | 921736 |
| master1-bin.000003 | 401 | #記錄此位置，從服務(wù)器從這里開始同步
+--------------------+-----------+
2）從服務(wù)器配置
~]# vim /etc/my.cnf
[mysqld]
server_id=2 #服務(wù)器ID唯一
relay_log=relay-log
relay_log_index=relay-log.index
read_only=ON
~]# systemctl restart mariadb
~]# mysql
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.0.7', #指定主節(jié)點(diǎn)IP
-> MASTER_USER='testuser', #同步用戶的用戶名
-> MASTER_PASSWORD='testpass', #密碼
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='master1-bin.000003', #以上記錄的文件
-> MASTER_LOG_POS=401, #位置
-> MASTER_CONNECT_RETRY=10; #重試時(shí)間10秒
MariaDB [(none)]> START SLAVE; #開始主從復(fù)制
3）測試
在主節(jié)點(diǎn)上生成一些數(shù)據(jù)：
MariaDB [(none)]> CREATE DATABASE testdb;
MariaDB [(none)]> use testdb
MariaDB [testdb]> create table testlog (id int auto_increment primary key,name char(30),age int default 20);
MariaDB [testdb]> delimiter $$
MariaDB [testdb]> create procedure pro_testlog()
-> begin
-> declare i int;
-> set i = 1;
-> while i < 100000
-> do insert into testlog(name,age) values (concat('testuser',i),i);
-> set i = i +1;
-> end while;
-> end$$
MariaDB [testdb]> delimiter ;
MariaDB [testdb]> START TRANSACTION;
MariaDB [testdb]> CALL pro_testlog;
MariaDB [testdb]> COMMIT;
在從節(jié)點(diǎn)上查看同步情況：
MariaDB [(none)]> SELECT COUNT(*) FROM testdb.testlog;
+----------+
| COUNT(*) |
+----------+
| 99999 | #同步成功
+----------+
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ****************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.7
Master_User: testuser
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: master1-bin.000003
Read_Master_Log_Pos: 10389814
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 10389944
Relay_Master_Log_File: master1-bin.000003
Slave_IO_Running: Yes #IO線程已啟動
Slave_SQL_Running: Yes #SQL線程已啟動
Seconds_Behind_Master: 0 #主從復(fù)制的時(shí)間差
Master_Server_Id: 1
2、舊數(shù)據(jù)庫新加從服務(wù)器
1）主服務(wù)器配置
~]# vim /etc/my.cnf
[mysqld]
log_bin
binlog_format=ROW
log-basename=master1
server_id=1
~]# systemctl restart mariadb
~]# mysqldump -A -F --single-transaction --master-data=1 > full.sql
~]# scp full.sql root@192.168.0.8:/root/
~]# mysql -e 'GRANT REPLICATION SLAVE ON *.* TO testuser@'192.168.0.8' IDENTIFIED BY 'testpass';'
2）從服務(wù)器配置
~]# vim /etc/my.cnf
[mysqld]
server_id=2
relay_log=relay-log
relay_log_index=relay-log.index
read_only=ON
~]# systemctl restart mariadb
~]# vim full.sql #在備份的SQL文件中加入以下信息
CHANGE MASTER TO
MASTER_HOST='192.168.0.7',
MASTER_USER='testuser',
MASTER_PASSWORD='testpass',
MASTER_PORT=3306,
MASTER_LOG_FILE='master1-bin.000005',
MASTER_LOG_POS=245,
MASTER_CONNECT_RETRY=10;
~]# mysql < full.sql #導(dǎo)入SQL的同時(shí)配置已經(jīng)完成
MariaDB [(none)]> SELECT COUNT(*) FROM testdb.testlog;
+----------+
| COUNT(*) |
+----------+
| 99999 |
+----------+
MariaDB [(none)]> START SLAVE; #啟動復(fù)制
三、級聯(lián)復(fù)制架構(gòu)實(shí)現(xiàn)
1）主節(jié)點(diǎn)
[root@master ~]# vim /etc/my.cnf [mysqld] log_bin binlog_format=ROW log-basename=master server_id=1 [root@master ~]# systemctl restart mariadb MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.0.%' IDENTIFIED BY 'replpass'; MariaDB [(none)]> SHOW MASTER LOGS; +-------------------+-----------+ | Log_name | File_size | +-------------------+-----------+ | master-bin.000001 | 26753 | | master-bin.000002 | 921736 | | master-bin.000003 | 401 | +-------------------+-----------+
2）從節(jié)點(diǎn)
[root@slave1 ~]# vim /etc/my.cnf
[mysqld]
log_bin #注意，級聯(lián)架構(gòu)中中繼從節(jié)點(diǎn)一定得開二進(jìn)制日志功能
binlog_format=ROW
read_only=ON
server_id=2
log_slave_updates #這項(xiàng)為關(guān)鍵，作用是將從服務(wù)的數(shù)據(jù)改變記錄到二進(jìn)制日志文件中
relay_log=relay-log
relay_log_index=relay-log.index
[root@slave1 ~]# systemctl start mariadb
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.0.7',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='replpass',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='master-bin.000003',
-> MASTER_LOG_POS=401,
-> MASTER_CONNECT_RETRY=10;
MariaDB [(none)]> START SLAVE;
MariaDB [(none)]> SHOW MASTER LOGS;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 245 |
+--------------------+-----------+
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.0.%' IDENTIFIED BY 'replpass';
3）從節(jié)點(diǎn)的從節(jié)點(diǎn)
[root@slave2 ~]# vim /etc/my.cnf
[mysqld]
read_only=ON
server_id=3
relay_log=relay-log
relay_log_index=relay-log.index
[root@slave2 ~]# systemctl start mariadb
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.0.8',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='replpass',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mariadb-bin.000001',
-> MASTER_LOG_POS=245,
-> MASTER_CONNECT_RETRY=10;
MariaDB [(none)]> START SLAVE;
4）從節(jié)點(diǎn)的從節(jié)點(diǎn)2
[root@slave3 ~]# vim /etc/my.cnf
[mysqld]
read_only=ON
server_id=4
relay_log=relay-log
relay_log_index=relay-log.index
[root@slave3 ~]# systemctl start mariadb
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.0.8',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='replpass',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mariadb-bin.000001',
-> MASTER_LOG_POS=245,
-> MASTER_CONNECT_RETRY=10;
MariaDB [(none)]> START SLAVE;
到此已經(jīng)搭建好了級聯(lián)復(fù)制，接下來測試一下把~
四、主主復(fù)制架構(gòu)
容易產(chǎn)生的問題：數(shù)據(jù)不一致，因此慎用；考慮要點(diǎn)：自動增長id
配置一個(gè)節(jié)點(diǎn)使用奇數(shù)id
auto_increment_offset=1 開始點(diǎn)
auto_increment_increment=2 增長幅度
另一個(gè)節(jié)點(diǎn)使用偶數(shù)id
auto_increment_offset=2
auto_increment_increment=2
1）主1
[mysqld]
log_bin
binlog_format=ROW
log-basename=master1
server_id=1
relay_log=relay-log
relay_log_index=relay-log.index
auto_increment_offset=1 #自增長字段從1開始
auto_increment_increment=2 #每次增長2，也就是說master1節(jié)點(diǎn)寫入的數(shù)據(jù)的id字段全部是奇數(shù)
[root@master ~]# systemctl start mariadb
MariaDB [(none)]> SHOW MASTER LOGS;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| master1-bin.000001 | 27033 |
| master1-bin.000002 | 942126 |
| master1-bin.000003 | 245 |
+--------------------+-----------+
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.0.%' IDENTIFIED BY 'replpass';
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.0.8',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='replpass',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='master2-bin.000003',
-> MASTER_LOG_POS=245,
-> MASTER_CONNECT_RETRY=10;
MariaDB [(none)]> START SLAVE;
2）主2
[mysqld]
log_bin
binlog_format=ROW
log-basename=master2
server_id=2
relay_log=relay-log
relay_log_index=relay-log.index
auto_increment_offset=2 #自增長字段從1開始
auto_increment_increment=2 #每次增長2，也就是說master1節(jié)點(diǎn)寫入的數(shù)據(jù)的id字段全部是偶數(shù)
[root@master2 ~]# systemctl start mariadb
MariaDB [(none)]> SHOW MASTER LOGS;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| master2-bin.000001 | 27036 |
| master2-bin.000002 | 942126 |
| master2-bin.000003 | 245 |
+--------------------+-----------+
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.0.%' IDENTIFIED BY 'replpass';
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.0.7',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='replpass',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='master1-bin.000003',
-> MASTER_LOG_POS=245,
-> MASTER_CONNECT_RETRY=10;
MariaDB [(none)]> START SLAVE;
3）測試
在master1上創(chuàng)建表，增加數(shù)據(jù)
MariaDB [(none)]> CREATE DATABASE db1;
MariaDB [(none)]> use db1
MariaDB [db1]> CREATE TABLE t1(id INT(2) AUTO_INCREMENT PRIMARY KEY,name CHAR(30));
MariaDB [db1]> INSERT t1(name) VALUES ('tom');
MariaDB [db1]> INSERT t1(name) VALUES ('maria');
MariaDB [db1]> SELECT * FROM t1;
+----+-------+
| id | name |
+----+-------+
| 1 | tom |
| 3 | maria |
+----+-------+
在master2上增加數(shù)據(jù)
MariaDB [db1]> INSERT t1(name) VALUES ('jerry');
MariaDB [db1]> INSERT t1(name) VALUES ('tony');
MariaDB [db1]> SELECT * FROM t1;
+----+-------+
| id | name |
+----+-------+
| 1 | tom |
| 3 | maria |
| 4 | jerry |
| 6 | tony |
五、半同步復(fù)制的實(shí)現(xiàn)
默認(rèn)情況下，MySQL的復(fù)制功能是異步的，異步復(fù)制可以提供最佳的性能，主庫把binlog日志發(fā)送給從庫即結(jié)束，并不驗(yàn)證從庫是否接收完畢。這意味著當(dāng)主服務(wù)器或從服務(wù)器端發(fā)生故障時(shí)，有可能從服務(wù)器沒有接收到主服務(wù)器發(fā)送過來的binlog日志，這就會造成主服務(wù)器和從服務(wù)器的數(shù)據(jù)不一致，甚至在恢復(fù)時(shí)造成數(shù)據(jù)的丟失；半同步復(fù)制的機(jī)制是只有當(dāng)主節(jié)點(diǎn)和從節(jié)點(diǎn)同步完成，僅有一臺同步完成即可，返回寫入完成，這樣的機(jī)制保證了數(shù)據(jù)的安全性。
1）主節(jié)點(diǎn)
[root@master ~]# vim /etc/my.cnf [mysqld] log_bin binlog_format=ROW log-basename=master server_id=1 relay_log=relay-log relay_log_index=relay-log.index [root@master ~]# systemctl restart mariadb MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.0.%' IDENTIFIED BY 'replpass'; MariaDB [(none)]> SHOW MASTER LOGS; +-------------------+-----------+ | Log_name | File_size | +-------------------+-----------+ | master-bin.000001 | 26753 | | master-bin.000002 | 921736 | | master-bin.000003 | 401 | +-------------------+-----------+ MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; #安裝模塊 MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled=1; #開啟半同步功能 MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%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 | +------------------------------------+-------+ MariaDB [(none)]> SHOW GLOBAL STATUS LIKE '%semi%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 0 | | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 0 | | Rpl_semi_sync_master_no_times | 0 | | Rpl_semi_sync_master_no_tx | 0 | | Rpl_semi_sync_master_status | ON | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 0 | | Rpl_semi_sync_master_tx_wait_time | 0 | | Rpl_semi_sync_master_tx_waits | 0 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 0 | +--------------------------------------------+-------+
2）從節(jié)點(diǎn)1
[root@slave1 ~]# vim /etc/my.cnf
[mysqld]
read_only=ON
log_bin
binlog_format=ROW
log-basename=slave
server_id=2
relay_log=relay-log
relay_log_index=relay-log.index
[root@slave1 ~]# systemctl restart mariadb
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.0.7',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='replpass',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='master-bin.000003',
-> MASTER_LOG_POS=401,
-> MASTER_CONNECT_RETRY=10;
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled=1;
MariaDB [(none)]> START SLAVE;
MariaDB [(none)]> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| slave-bin.000001 | 26753 |
| slave-bin.000002 | 921736 |
| slave-bin.000003 | 245 |
+------------------+-----------+
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.0.%' IDENTIFIED BY 'replpass';
3）從節(jié)點(diǎn)2
[root@slave2 ~]# vim /etc/my.cnf
[mysqld]
read_only=ON
server_id=3
relay_log=relay-log
relay_log_index=relay-log.index
[root@slave2 ~]# systemctl restart mariadb
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.0.8',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='replpass',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='slave-bin.000003',
-> MASTER_LOG_POS=245,
-> MASTER_CONNECT_RETRY=10;
MariaDB [(none)]> START SLAVE;
4）從節(jié)點(diǎn)3
[root@slave3 ~]# vim /etc/my.cnf
[mysqld]
read_only=ON
server_id=4
relay_log=relay-log
relay_log_index=relay-log.index
[root@slave3 ~]# systemctl restart mariadb
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.0.8',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='replpass',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='slave-bin.000003',
-> MASTER_LOG_POS=245,
-> MASTER_CONNECT_RETRY=10;
MariaDB [(none)]> START SLAVE;
六、加密傳輸復(fù)制的實(shí)現(xiàn)
在默認(rèn)的主從復(fù)制過程或遠(yuǎn)程連接到MySQL/MariaDB所有的鏈接通信中的數(shù)據(jù)都是明文的，外網(wǎng)里訪問數(shù)據(jù)或則復(fù)制，存在安全隱患。通過SSL/TLS加密的方式進(jìn)行復(fù)制的方法，來進(jìn)一步提高數(shù)據(jù)的安全性
主服務(wù)器開啟SSL：[mysqld] 加一行ssl
主服務(wù)器配置證書和私鑰；并且創(chuàng)建一個(gè)要求必須使用SSL連接的復(fù)制賬號
從服務(wù)器使用CHANGER MASTER TO 命令時(shí)指明ssl相關(guān)選項(xiàng)
MariaDB [(none)]> SHOW VARIABLES LIKE '%ssl%'; +---------------+----------+ | Variable_name | Value | +---------------+----------+ | have_openssl | DISABLED | | have_ssl | DISABLED | | ssl_ca | | | ssl_capath | | | ssl_cert | | | ssl_cipher | | | ssl_key | | +---------------+----------+特別提示：在配置之前先檢查mysql服務(wù)是否支持ssl功能，如果have_ssl的值為'DISABLED'則支持；如果為'NO'則不支持，需要再重新編譯安裝或者安裝具有ssl功能的版本
1）CA
[root@CA ~]# mkdir /etc/my.cnf.d/ssl/
[root@CA ~]# cd /etc/my.cnf.d/ssl/
[root@CA ssl]# openssl genrsa 2048 > cakey.pem
[root@CA ssl]# openssl req -new -x509 -key cakey.pem -out cacert.pem -days 3650 #自簽證書
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:beijing
Locality Name (eg, city) [Default City]:beijing
Organization Name (eg, company) [Default Company Ltd]:testmysqlca
Organizational Unit Name (eg, section) []:opt
Common Name (eg, your name or your server's hostname) []:ca.testmysqlca.com
[root@CA ssl]# openssl req -newkey rsa:2048 -days 365 -nodes -keyout master.key > master.csr
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:beijing
Locality Name (eg, city) [Default City]:beijing
Organization Name (eg, company) [Default Company Ltd]:testmysqlca
Organizational Unit Name (eg, section) []:opt
Common Name (eg, your name or your server's hostname) []:master.testmysqlca.com
[root@CA ssl]# openssl x509 -req -in master.csr -CA cacert.pem -CAkey cakey.pem -set_serial 01 > master.crt #簽署master證書
[root@CA ssl]# openssl req -newkey rsa:2048 -days 365 -nodes -keyout slave.key > slave.csr
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:beijing
Locality Name (eg, city) [Default City]:beijing
Organization Name (eg, company) [Default Company Ltd]:testmysqlca
Organizational Unit Name (eg, section) []:opt
Common Name (eg, your name or your server's hostname) []:slave.testmysqlca.com
[root@CA ssl]# openssl x509 -req -in slave.csr -CA cacert.pem -CAkey cakey.pem -set_serial 02 > slave.crt #簽署slave證書
[root@CA ssl]# openssl req -newkey rsa:2048 -days 365 -nodes -keyout slave2.key > slave2.csr
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:beijing
Locality Name (eg, city) [Default City]:beijing
Organization Name (eg, company) [Default Company Ltd]:testmysqlca
Organizational Unit Name (eg, section) []:opt
Common Name (eg, your name or your server's hostname) []:slave2.testmysqlca.com
[root@CA ssl]# openssl x509 -req -in slave2.csr -CA cacert.pem -CAkey cakey.pem -set_serial 03 > slave2.crt #簽署slave2證書
[root@CA ssl]# openssl verify -CAfile cacert.pem master.crt slave.crt slave2.crt #檢查證書是否可用
master.crt: OK
slave.crt: OK
slave2.crt: OK
先在各個(gè)節(jié)點(diǎn)上創(chuàng)建/etc/my.cnf.d/ssl/文件夾，將各自的證書，CA的證書和各自的秘鑰文件復(fù)制過去
[root@CA ssl]# scp cacert.pem master.crt master.key root@192.168.0.7:/etc/my.cnf.d/ssl/
[root@CA ssl]# scp cacert.pem slave.crt slave.key root@192.168.0.8:/etc/my.cnf.d/ssl/
[root@CA ssl]# scp cacert.pem slave2.crt slave2.key root@192.168.0.9:/etc/my.cnf.d/ssl/
2）master
[root@master ~]# mkdir /etc/my.cnf.d/ssl/ [root@master ~]# vim /etc/my.cnf [mysqld] log_bin binlog_format=ROW log-basename=master server_id=1 ssl #開啟ssl功能 ssl-ca=/etc/my.cnf.d/ssl/cacert.pem #指定CA證書的路徑 ssl-cert=/etc/my.cnf.d/ssl/master.crt #指定自己的證書的路徑 ssl-key=/etc/my.cnf.d/ssl/master.key #指定自己的秘鑰文件路徑 [root@master ~]# systemctl restart mariadb MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.0.%' IDENTIFIED BY 'replpass' REQUIRE SSL; #授權(quán)用戶并且強(qiáng)制迫使用戶開啟ssl登錄 MariaDB [(none)]> SHOW MASTER LOGS; +-------------------+-----------+ | Log_name | File_size | +-------------------+-----------+ | master-bin.000001 | 26753 | | master-bin.000002 | 921736 | | master-bin.000003 | 413 | +-------------------+-----------+
3）slave1
[root@slave1 ~]# mkdir /etc/my.cnf.d/ssl/
[root@slave1 ~]# mysql -urepluser -preplpass -h192.168.0.7 --ssl-ca=/etc/my.cnf.d/ssl/cacert.pem --ssl-cert=/etc/my.cnf.d/ssl/slave.crt --ssl-key=/etc/my.cnf.d/ssl/slave.key
[root@slave1 ~]# vim /etc/my.cnf
[mysqld]
read_only=ON
server_id=2
relay_log=relay-log
relay_log_index=relay-log.index
ssl
ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
ssl-cert=/etc/my.cnf.d/ssl/slave.crt
ssl-key=/etc/my.cnf.d/ssl/slave.key
[root@slave1 ~]# systemctl restart mariadb
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.0.7',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='replpass',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='master-bin.000003',
-> MASTER_LOG_POS=413,
-> MASTER_CONNECT_RETRY=10,
-> MASTER_SSL=1; #注意，需要指明開啟ssl鏈接
MariaDB [(none)]> START SLAVE;
4）slave2
[root@slave2 ~]# mkdir /etc/my.cnf.d/ssl/
[root@slave2 ~]# mysql -urepluser -preplpass -h192.168.0.7 --ssl-ca=/etc/my.cnf.d/ssl/cacert.pem --ssl-cert=/etc/my.cnf.d/ssl/slave2.crt --ssl-key=/etc/my.cnf.d/ssl/slave2.key
[root@slave2 ~]# vim /etc/my.cnf
[mysqld]
read_only=ON
server_id=3
relay_log=relay-log
relay_log_index=relay-log.index
ssl
ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
ssl-cert=/etc/my.cnf.d/ssl/slave2.crt
ssl-key=/etc/my.cnf.d/ssl/slave2.key
[root@slave2 ~]# systemctl restart mariadb
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.0.7',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='replpass',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='master-bin.000003',
-> MASTER_LOG_POS=413,
-> MASTER_CONNECT_RETRY=10,
-> MASTER_SSL=1;
MariaDB [(none)]> START SLAVE;
七、MySQL復(fù)制的相關(guān)指令和變量總結(jié)
選項(xiàng)：
- log_bin 啟用二進(jìn)制日志，在主節(jié)點(diǎn)或級聯(lián)復(fù)制中間的從節(jié)點(diǎn)必須要開啟
- binlog_format=ROW 二進(jìn)制日志記錄方式為基于行的方式記錄，強(qiáng)烈建議開啟
- log-basename=master | slave ... 二進(jìn)制日志的前綴名，不是必須向，但建議標(biāo)識
- server_id = # 服務(wù)器ID，各個(gè)節(jié)點(diǎn)的ID必須唯一
- relay_log = relay-log 開啟中繼日志，并以relay-log為文件名開頭，從節(jié)點(diǎn)開啟
- relay_log_index = relay-log.index 中繼日志索引文件
- log_slave_updates 作用是SQL線程重讀中繼日志時(shí)將改變數(shù)據(jù)的操作記錄為二進(jìn)制日志，在級聯(lián)復(fù)制中使用
- ssl 開啟ssl功能
- ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
- ssl-cert=/etc/my.cnf.d/ssl/master.crt
- ssl-key=/etc/my.cnf.d/ssl/master.key
- sync_binlog=1 每次寫后立即同步二進(jìn)制日志到磁盤
- innodb_flush_log_at_trx_commit=1 每次事務(wù)提交立即同步日志寫磁盤
- sync_master_info=# #次事件后master.info同步到磁盤
- skip_slave_start=ON 不自動啟動slave
- sync_relay_log=# #次寫后同步relay log到磁盤
- sync_relay_log_info=# #次事務(wù)后同步relay-log.info到磁盤
- auto_increment_offset=1 自動增長開始點(diǎn)，在主主復(fù)制中使用
變量：
- replicate_do_db= 指定復(fù)制庫的白名單
- replicate_ignore_db= 指定復(fù)制庫黑名單
- replicate_do_table= 指定復(fù)制表的白名單
- replicate_ignore_table= 指定復(fù)制表的黑名單
- replicate_wild_do_table= foo%.bar% 支持通配符
- replicate_wild_ignore_table= 指定復(fù)制的表，黑名單
- rpl_semi_sync_slave_enabled=1 開啟半同步復(fù)制，需要安裝模塊
指令：
- START SLAVE; 啟動主從復(fù)制
- STOP SLAVE; 停止復(fù)制
- SHOW SLAVE STATUS; 查看復(fù)制狀態(tài)
- Seconds_Behind_Master: 0 從服務(wù)器是否落后于主服務(wù)
- RESET SLAVE ALL; 重置從服務(wù)器的配置
- MASTER_SSL=1, 配合 CHANGE MASTER TO 使用，開啟ssl加密復(fù)制
- MASTER_SSL_CA = '/etc/my.cnf.d/ssl/cacert.pem',
- MASTER_SSL_CERT = '/etc/my.cnf.d/ssl/slave.crt',
- MASTER_SSL_KEY = '/etc/my.cnf.d/ssl/slave.key';
- PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr } 刪除二進(jìn)制日志，謹(jǐn)慎操作
- SHOW MASTER STATUS 查看二進(jìn)制日志狀態(tài)
- SHOW BINLOG EVENTS 查看二進(jìn)制日志
- SHOW BINARY LOGS 查看二進(jìn)制日志
到此這篇關(guān)于MySQL系列之十三 MySQL的復(fù)制的文章就介紹到這了,更多相關(guān)MySQL的復(fù)制內(nèi)容請搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站！
版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。
相關(guān)文章
關(guān)注官方微信