主從複製、雙主複製及半同步複製、以及基於SSL的複製
MySQL主從複製配置
1：設定mysql主從配置的優點：
1.1、解決web應用系統，資料庫出現的效能瓶頸，採用資料庫叢集的方式來實現查詢負載；一個系統中資料庫的查詢操作比更新操作要多得多，通過多臺查詢伺服器將 資料庫的查詢分擔到不同的查詢伺服器上從而提高查詢效率。
1.2、Mysql資料庫支援資料庫的主從複製功能，使用主資料庫進行資料的插入、刪除與更新操作，而從資料庫則專門用來進行資料查詢操作，這樣可以將更新操作和 查詢操作分擔到不同的資料庫上，從而提高了查詢效率。
2:主從配置的原理：
主伺服器將更新寫入二進位制日誌檔案，並維護檔案的一個索引以跟蹤日誌迴圈。這些日誌可以記錄傳送到從伺服器的更新。當一個從伺服器連線主伺服器時，它通知主伺服器從伺服器在日誌中讀取的最後一次成功更新的位置。從伺服器接收從那時起發生的任何更新，然後封鎖並等待主伺服器通知新的更新。MySQL複製基於主伺服器在二進位制日誌中跟蹤所有對資料庫的更改(更新、刪除等等)。因此，要進行復制，必須在主伺服器上啟用二進位制日誌。每個從伺服器從主伺服器接收主伺服器已經記錄到其二進位制日誌的儲存的更新，以便從伺服器可以對其資料拷貝執行相同的更新。從伺服器設定為複製主伺服器的資料後，它連線主伺服器並等待更新過程。如果主伺服器失敗，或者從伺服器失去與主伺服器之間的連線，從伺服器保持定期嘗試連線，直到它能夠繼續幀聽更新。由--master-connect-retry選項控制重試間隔，預設為60秒。
每個從伺服器跟蹤複製時間。主伺服器不知道有多少個從伺服器或在某一時刻有哪些被更新了。
3：MySql複製的基本過程：
3.1、Slave 上面的IO執行緒連線上 Master，並請求從指定日誌檔案的指定位置(或者從最開始的日誌)之後的日誌內容;
3.2、Master 接收到來自 Slave 的 IO 執行緒的請求後，通過負責複製的 IO執行緒根據請求資訊讀取指定日誌指定位置之後的日誌資訊，
返回給 Slave 端的 IO執行緒。返回資訊中除了日誌所包含的資訊之外，還包括本次返回的資訊在 Master 端的 Binary Log 檔案的名稱以及在 BinaryLog 中的位置;
3.3、 Slave 的 IO 執行緒接收到資訊後，將接收到的日誌內容依次寫入到 Slave 端的RelayLog檔案(mysql-relay-lin.xxxxxx)的最末端，並將讀取到的Master端的bin-log的檔名和位置記錄到 master-info檔案中，以便在下一次讀取的時候能夠清楚的高速Master“我需要從某個bin-log的哪個位置開始往後的日誌內容，請發給我”;
3.4、Slave 的 SQL 執行緒檢測到 Relay Log 中新增加了內容後，會馬上解析該 Log 檔案中的內容成為在 Master端真實執行時候的那些可執行的 Query 語句，並在自身執行這些 Query。這樣，實際上就是在 Master 端和 Slave端執行了同樣的 Query，所以兩端的資料是完全一樣的。
下圖描述了複製的過程：
換種說法再來描述一下：
- 該過程的第一部分就是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上並行操作。
2 .複製配置 有兩臺MySQL資料庫伺服器Master和slave，Master為主伺服器，slave為從伺服器，初始狀態時，Master和slave中的資料資訊相同，當Master中的資料發生變化時，slave也跟著發生相應的變化，使得master和slave的資料資訊同步，達到備份的目的。
要點：
負責在主、從伺服器傳輸各種修改動作的媒介是主伺服器的二進位制變更日誌，這個日誌記載著需要傳輸給從伺服器的各種修改動作。因此，主伺服器必須啟用二進位制日誌功能。從伺服器必須具備足以讓它連線主伺服器並請求主伺服器把二進位制變更日誌傳輸給它的許可權。
一、實現主從複製：
1、修改MySQL主伺服器master
[root@centos7 ~]#vim /etc/my.cnf.d/server.cnf //編輯[mysqld]
log-bin = mysql-bin #[必須]啟用二進位制日誌
server-id = 1 #[必須]伺服器唯一ID，預設是1，一般取IP最後一段
#選填引數
innodb_file_per_table = ON //使用每表單獨空間
skip_name_resolve = ON //跳過名稱解析
read-only = 0 //主機，讀寫都可以
binlog-do-db = test #需要備份資料，多個寫多行
binlog-ignore-db =mysql #不需要備份的資料庫，多個寫多行
expire_logs_days = 2 # 自動清理 2 天前的log檔案，可根據需要修改
[root@centos7 ~]#systemctl start mariadb.service
2、從伺服器配置
[root@centos7 ~]#vim /etc/my.cnf.d/server.cnf //編輯mysql配置檔案
[mysqld]
server-id = 2 //從伺服器的ID
relay-log = relay-log //開啟從節點的中繼日誌檔案
skip_name_resolve = ON //跳過名稱解析
innodb_file_per_table = ON //使用每表單獨空間
read-only = 1 //開啟只讀模式
[root@centos7 ~]#systemctl start mariadb.service
3、在主節點建立有複製許可權的使用者賬號
MariaDB [(none)]> show master status; 檢視二進位制日誌節點位置
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000003 | 245 | | |
+-------------------+----------+--------------+------------------+
MariaDB [(none)]> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.18.%' IDENTIFIED BY 'replpass'; //建立擁有複製使用者賬號 （最小許可權法則）
MariaDB [(none)]> flush privileges; //重新整理
MariaDB [(none)]> show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000003 | 424 | | |
+-------------------+----------+--------------+------------------+
從伺服器如果不建立使用者，就從主節點建立使用者後的424位置開始複製。
4、開啟從伺服器複製執行緒：
MariaDB [(none)]> show slave status;
Empty set (0.00 sec) //沒有配置為空，
MariaDB [(none)]> show processlist; //從伺服器還沒有啟動複製執行緒
+----+------+-----------+------+---------+------+-------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+------+-----------+------+---------+------+-------+------------------+----------+
| 2 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 |
+----+------+-----------+------+---------+------+-------+------------------+----------+
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.18.131', MASTER_USER='repluser', MASTER_PASSWORD='replpass', MASTER_LOG_FILE='master-log.000003', MASTER_LOG_POS=424;
//使用有複製許可權的使用者賬號連線至主伺服器，並啟動複製執行緒
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.18.131 //主伺服器IP
Master_User: repluser //授權的使用者
Master_Port: 3306 //主伺服器埠
Connect_Retry: 60 //重試時間
Master_Log_File: master-log.000003 //從哪個二進位制的檔案開啟讀取
Read_Master_Log_Pos: 424 //讀取二進位制的開始位置
Relay_Log_File: relay-log.000001 //讀取的中繼日誌檔案
Relay_Log_Pos: 4 //讀取的中繼日誌位置
Relay_Master_Log_File: master-log.000003
Slave_IO_Running: No // I/O Thread啟動情況 現在是沒有開啟
Slave_SQL_Running: No //SQL Thread啟動情況 現在是沒有開啟
Replicate_Do_DB:
......
MariaDB [(none)]> start slave; //啟動兩執行緒
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.18.131
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000003
Read_Master_Log_Pos: 499
Relay_Log_File: relay-log.000002 //這裡中繼做了初始化
Relay_Log_Pos: 605
Relay_Master_Log_File: master-log.000003
Slave_IO_Running: Yes //已開啟
Slave_SQL_Running: Yes //已開啟
Replicate_Do_DB:
MariaDB [(none)]> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
| 2 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 |
| 3 | system user | | NULL | Connect | 20 | Waiting for master to send event | NULL | 0.000 |
| 4 | system user | | NULL | Connect | 791 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | 0.000 |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
//可以看到啟動了兩個執行緒，一個時等待主伺服器傳送的時間，一個是讀取中繼日誌做同步
[root@cnetos7 ~]#ll /var/lib/mysql/master.info
//這個檔案儲存了主伺服器的連結資訊，包括賬號密碼等，下次重啟服務會自動通過此檔案連線主伺服器
[root@cnetos7 ~]#ll /var/lib/mysql/relay-log.info
//此檔案儲存從伺服器使用的中繼日誌、POS和主伺服器使用的二進位制日誌、POS，伺服器啟動也會讀取此檔案
5、測試
主伺服器：
MariaDB [(none)]> create database testdb;
從伺服器：
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| testdb |
+--------------------+
二、實現雙主複製
雙主複製容易產生資料不一致，請慎用。
1.在兩臺伺服器上設定：
server 1
[root@centos7 ~]#vim /etc/my.cnf.d/server.cnf
server-id = 1
log_bin = master-log
relay_log = relay-log
innodb_file_per_table = ON
skip_name_resolve = ON
auto_increment_offset = 1 //1開始，
auto_increment_increment = 2 //自動增長2
server 2
[root@centos7 ~]#vim /etc/my.cnf.d/server.cnf
server-id = 2
log_bin = master-log
relay_log = relay-log
innodb_file_per_table = ON
skip_name_resolve = ON
auto_increment_offset = 2 //2開始，
auto_increment_increment = 2 //自動增長2
要實現雙主複製，這時每臺主機上都要開啟二進位制日誌，中繼日誌，以及每臺主機的唯一server-id
2.開啟伺服器複製執行緒
server 1
MariaDB [(none)]> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.18.%' IDENTIFIED BY 'replpass';
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000003 | 509 | | |
+-------------------+----------+--------------+------------------+
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.18.98', MASTER_USER='repluser', MASTER_PASSWORD='replpass', MASTER_LOG_FILE='master-log.000003', MASTER_LOG_POS=509;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.18.98
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000003
Read_Master_Log_Pos: 509
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
server 2
MariaDB [(none)]> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.18.%' IDENTIFIED BY 'replpass';
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000003 | 509 | | |
+-------------------+----------+--------------+------------------+
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.18.131', MASTER_USER='repluser', MASTER_PASSWORD='replpass', MASTER_LOG_FILE='master-log.000003', MASTER_LOG_POS=509;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.18.131
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000003
Read_Master_Log_Pos: 509
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
3.測試：
server 1
MariaDB [(none)]> create database mydb;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> use mydb;
Database changed
MariaDB [mydb]> create table tb1(id int unsigned auto_increment primary key,name varchar(200));
Query OK, 0 rows affected (0.01 sec)
MariaDB [mydb]> insert into tb1 (name) values ('xiaoming'),('xiaohua'),('xiaogang');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [mydb]> select * from tb1;
+----+----------+
| id | name |
+----+----------+
| 1 | xiaoming |
| 3 | xiaohua |
| 5 | xiaogang |
+----+----------+
server 2
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> use mydb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [mydb]> insert into tb1 (name) values ('xiaodong'),('xiaoli'),('xiaoghong');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [mydb]> select * from tb1;
+----+-----------+
| id | name |
+----+-----------+
| 1 | xiaoming |
| 3 | xiaohua |
| 5 | xiaogang |
| 6 | xiaodong |
| 8 | xiaoli |
| 10 | xiaoghong |
+----+-----------+
server 1
MariaDB [mydb]> select * from tb1;
+----+----------+
| id | name |
+----+----------+
| 1 | xiaoming |
| 3 | xiaohua |
| 5 | xiaogang |
+----+----------+
3 rows in set (0.01 sec)
MariaDB [mydb]> select * from tb1;
+----+-----------+
| id | name |
+----+-----------+
| 1 | xiaoming |
| 3 | xiaohua |
| 5 | xiaogang |
| 6 | xiaodong |
| 8 | xiaoli |
| 10 | xiaoghong |
+----+-----------+
6 rows in set (0.00 sec)
三、半同步複製的實現
基於外掛實現，亦支援多種外掛：/usr/lib64/mysql/plugin/
[root@cnetos7 mysql]#rpm -ql mariadb-server
......
/usr/lib64/mysql/plugin/auth_0x0100.so
/usr/lib64/mysql/plugin/auth_pam.so
/usr/lib64/mysql/plugin/auth_socket.so
/usr/lib64/mysql/plugin/auth_test_plugin.so
/usr/lib64/mysql/plugin/daemon_example.ini
/usr/lib64/mysql/plugin/dialog_examples.so
/usr/lib64/mysql/plugin/ha_innodb.so
/usr/lib64/mysql/plugin/ha_sphinx.so
/usr/lib64/mysql/plugin/handlersocket.so
/usr/lib64/mysql/plugin/libdaemon_example.so
/usr/lib64/mysql/plugin/mypluglib.so
/usr/lib64/mysql/plugin/qa_auth_client.so
/usr/lib64/mysql/plugin/qa_auth_interface.so
/usr/lib64/mysql/plugin/qa_auth_server.so
/usr/lib64/mysql/plugin/query_cache_info.so
/usr/lib64/mysql/plugin/semisync_master.so //半同步複製使用外掛
/usr/lib64/mysql/plugin/semisync_slave.so //半同步複製使用外掛
/usr/lib64/mysql/plugin/server_audit.so
/usr/lib64/mysql/plugin/sphinx.so
/usr/lib64/mysql/plugin/sql_errlog.so
......
主伺服器：
1.編輯配置檔案。
[root@centos7 ~]#vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id = 1
log_bin = master-log
innodb_file_per_table = ON
skip_name_resolve = ON
[root@centos7 ~]#systemctl start mariadb
2.建立複製使用者
MariaDB [(none)]> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.18.%' IDENTIFIED BY 'replpass';
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000003 | 499 | | |
+-------------------+----------+--------------+------------------+
3.安裝半同步複製主節點外掛
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; //安裝外掛
MariaDB [(none)]> SHOW PLUGINS; //檢視是否完成
+--------------------------------+----------+--------------------+--------------------+---------+
| Name | Status | Type | Library | License |
+--------------------------------+----------+--------------------+--------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
......
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL |
+--------------------------------+----------+--------------------+--------------------+---------+
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%'; //檢視半同步複製相關資訊
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | OFF | //半同步複製的主節點(ON|OFF)
| rpl_semi_sync_master_timeout | 10000 | //等待從節點的超時時間，10s
| rpl_semi_sync_master_trace_level | 32 | //跟蹤級別
| rpl_semi_sync_master_wait_no_slave | ON | //在沒有從節點的時候是否等待
+------------------------------------+-------+
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled = ON; //開啟半同步複製為主節點
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 |
+--------------------------------------------+-------+
等從伺服器開啟半同步複製作為從節點，在查詢一次
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 |
+--------------------------------------------+-------+
從伺服器：
1.編輯配置檔案。
[root@centos7 ~]#vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id = 2
relay_log = relay-log
innodb_file_per_table = ON
skip_name_resolve = ON
[root@centos7 ~]#systemctl start mariadb
2安裝半同步複製從節點外掛
MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; //安裝外掛
MariaDB [(none)]> SHOW PLUGINS; //檢視是否完成
+--------------------------------+----------+--------------------+-------------------+---------+
| Name | Status | Type | Library | License |
+--------------------------------+----------+--------------------+-------------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
......
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| rpl_semi_sync_slave | ACTIVE | REPLICATION | semisync_slave.so | GPL |
+--------------------------------+----------+--------------------+-------------------+---------+
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%'; //檢視半同步複製相關資訊
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
MariaDB [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled = ON; /開啟半同步複製為從節點
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
3.連線主節點
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.18.131', MASTER_USER='repluser', MASTER_PASSWORD='replpass', MASTER_LOG_FILE='master-log.000003', MASTER_LOG_POS=499; //連線主節點
MariaDB [(none)]> start slave; //開啟從節點
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.18.131
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000003
Read_Master_Log_Pos: 499
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
測試：
MariaDB [(none)]> create database medb;
MariaDB [(none)]> use medb;
MariaDB [medb]> create table tb1(id int unsigned auto_increment primary key,name varchar(200));
MariaDB [medb]> insert into tb1 (name) values ('hong'),('yan'),('L');
MariaDB [medb]> select * from tb1;
+----+------+
| id | name |
+----+------+
| 1 | hong |
| 2 | yan |
| 3 | L |
+----+------+
MariaDB [medb]> SHOW GLOBAL STATUS LIKE '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 724 |
| Rpl_semi_sync_master_net_wait_time | 2174 |
| Rpl_semi_sync_master_net_waits | 3 |
| 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 | 1169 |
| Rpl_semi_sync_master_tx_wait_time | 2339 |
| Rpl_semi_sync_master_tx_waits | 2 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 3 |
+--------------------------------------------+-------+
四、實現基於SSL的複製
主伺服器
1.配置CA
[root@centos7 ~]#cd /etc/pki/CA
[root@centos7 CA]#touch index.txt //生成證照索引資料庫檔案(預設沒有）
[root@centos7 CA]#echo 01 > serial //指定第一個頒發證照的序列號（預設也沒有 這裡01兩位採用的是十六進位制）
2.生成自簽證照
[root@centos7 CA]#(umask 077;openssl genrsa -out private/cakey.pem 2048)
[root@centos7 CA]#openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 3650
3.生成用於mysql的ssl檔案及修改許可權許可權
[root@centos7 CA]#mkdir /var/lib/mysql/ssl
[root@centos7 CA]#cd /var/lib/mysql/ssl
[root@centos7 ssl]#(umask 077;openssl genrsa -out master.key 2048)
[root@centos7 ssl]#openssl req -new -key master.key -out master.csr
[root@centos7 ssl]#openssl ca -in master.csr -out master.crt -days 365
[root@centos7 ssl]#cp /etc/pki/CA/cacert.pem /var/lib/mysql/ssl/
[root@centos7 ssl]#ll
total 20
-rw-r--r-- 1 root root 1257 Sep 14 16:10 cacert.pem
-rw-r--r-- 1 root root 4353 Sep 14 16:09 master.crt
-rw-r--r-- 1 root root 972 Sep 14 16:06 master.csr
-rw------- 1 root root 1675 Sep 14 16:05 master.key
[root@centos7 ssl]#chown -R mysql.mysql /var/lib/mysql/ssl/*
[root@centos7 ssl]#ll
total 20
-rw-r--r-- 1 mysql mysql 1257 Sep 14 16:10 cacert.pem
-rw-r--r-- 1 mysql mysql 4353 Sep 14 16:09 master.crt
-rw-r--r-- 1 mysql mysql 972 Sep 14 16:06 master.csr
-rw------- 1 mysql mysql 1675 Sep 14 16:05 master.key
從伺服器：
1.生成私鑰
[root@cnetos7 mysql]#mkdir /var/lib/mysql/ssl
[root@cnetos7 mysql]#cd /var/lib/mysql/ssl
[root@cnetos7 ssl]#(umask 077;openssl genrsa -out slave.key 2048)
[root@cnetos7 ssl]#openssl req -new -key slave.key -out slave.csr
[root@cnetos7 ssl]#scp slave.csr 192.168.18.131:/etc/pki/CA/csr //傳給主伺服器
2.主伺服器上CA頒發證照
[root@centos7 ssl]#cd /etc/pki/CA/csr
[root@centos7 csr]#openssl ca -in slave.csr -out ../certs/slave.crt -days 365
[root@centos7 csr]#scp ../certs/slave.crt 192.168.18.98:/var/lib/mysql/ssl
[root@centos7 csr]#scp /etc/pki/CA/cacert.pem 192.168.18.98:/var/lib/mysql/ssl
3.改許可權許可權
[root@cnetos7 ssl]#ll
總用量 20
-rw-r--r--. 1 root root 1257 9月 14 16:27 cacert.pem
-rw-r--r--. 1 root root 4394 9月 14 16:26 slave.crt
-rw-r--r--. 1 root root 997 9月 14 16:24 slave.csr
-rw-------. 1 root root 1675 9月 14 16:15 slave.key
[root@cnetos7 ssl]#chown -R mysql.mysql /var/lib/mysql/ssl/*
[root@cnetos7 ssl]#ll
總用量 20
-rw-r--r--. 1 mysql mysql 1257 9月 14 16:27 cacert.pem
-rw-r--r--. 1 mysql mysql 4394 9月 14 16:26 slave.crt
-rw-r--r--. 1 mysql mysql 997 9月 14 16:24 slave.csr
-rw-------. 1 mysql mysql 1675 9月 14 16:15 slave.key
主伺服器mysql配置
[root@centos7 ~]#vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id = 1
log_bin = master-log
innodb_file_per_table = ON
skip_name_resolve = ON
ssl
ssl-ca = /var/lib/mysql/ssl/cacert.pem
ssl-cert = /var/lib/mysql/ssl/master.crt
ssl-key = /var/lib/mysql/ssl/master.key
[root@centos7 ~]#systemctl start mariadb
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%ssl%'; //驗證主節點配置檔案是否成功
+---------------+-------------------------------+
| Variable_name | Value |
+---------------+-------------------------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /var/lib/mysql/ssl/cacert.pem |
| ssl_capath | |
| ssl_cert | /var/lib/mysql/ssl/master.crt |
| ssl_cipher | |
| ssl_key | /var/lib/mysql/ssl/master.key |
+---------------+-------------------------------+
出現以上資訊表示成功
從伺服器mysql配置
[root@centos7 ~]#vim /etc/my.cnf.d/server.cnf
[mysqld]
server-id = 2
relay_log = relay-log
innodb_file_per_table = ON
skip_name_resolve = ON
ssl
ssl-ca = /var/lib/mysql/ssl/cacert.pem
ssl-cert = /var/lib/mysql/ssl/slave.crt
ssl-key = /var/lib/mysql/ssl/slave.key
[root@centos7 ~]#systemctl start mariadb
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%ssl%'; //驗證從節點配置檔案是否成功
+---------------+-------------------------------+
| Variable_name | Value |
+---------------+-------------------------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /var/lib/mysql/ssl/cacert.pem |
| ssl_capath | |
| ssl_cert | /var/lib/mysql/ssl/slave.crt |
| ssl_cipher | |
| ssl_key | /var/lib/mysql/ssl/slave.key |
+---------------+-------------------------------+
出現以上資訊表示成功，
建立複製使用者
MariaDB [(none)]> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.18.%' IDENTIFIED BY 'replpass';
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000003 | 499 | | |
+-------------------+----------+--------------+------------------+
從節點連線主節點
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.18.131', MASTER_USER='repluser', MASTER_PASSWORD='replpass', MASTER_LOG_FILE='master-log.000003', MASTER_LOG_POS=499,MASTER_SSL=1,MASTER_SSL_CA='/var/lib/mysql/ssl/cacert.pem',MASTER_SSL_CERT='/var/lib/mysql/ssl/slave.crt',MASTER_SSL_KEY='/var/lib/mysql/ssl/slave.key';
//可（MariaDB [(none)]> help change master to）檢視連線配置幫助
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.18.131
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000003
Read_Master_Log_Pos: 499
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: 499
Relay_Log_Space: 818
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes //連線成功
Master_SSL_CA_File: /var/lib/mysql/ssl/cacert.pem
Master_SSL_CA_Path:
Master_SSL_Cert: /var/lib/mysql/ssl/slave.crt
Master_SSL_Cipher:
Master_SSL_Key: /var/lib/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
1 row in set (0.00 sec)
驗證是否使用了ssl加密
[root@cnetos7 ssl]#mysql --ssl （可mysql --help檢視連線幫助）
MariaDB [(none)]> status;
--------------
mysql Ver 15.1 Distrib 5.5.52-MariaDB, for Linux (x86_64) using readline 5.1
Connection id: 8
Current database:
Current user: root@localhost
SSL: Cipher in use is DHE-RSA-AES256-GCM-SHA384 //可以看到是於SSL加密
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 5.5.52-MariaDB MariaDB Server
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 20 min 21 sec
Threads: 2 Questions: 24 Slow queries: 0 Opens: 4 Flush tables: 2 Open tables: 30 Queries per second avg: 0.019
--------------
如有不足，請多多指教！
相關文章
- MySQL主從複製、半同步複製和主主複製MySql
- MySQL主從複製、半同步複製和主主複製概述MySql
- MySQL的主從複製、半同步複製、主主複製詳解MySql
- MySQL(二)：主從複製結構、半同步複製、雙主複製結構、利用SSL實現安全的MySQL主從複製MySql
- 配置mysql5.5主從複製、半同步複製、主主複製MySql
- MySQL主從複製之半同步複製MySql
- MySQL5.7主從複製-半同步複製搭建MySql
- MySQL主從複製之非同步複製MySql非同步
- MySQL主從複製與主主複製MySql
- mysql複製--主從複製配置MySql
- mysql5.7主從複製，主主複製MySql
- mysql主主複製（雙主複製）配置步驟MySql
- MySQL主從雙向同步複製MySql
- MySQL的主從複製與MySQL的主主複製MySql
- redis系列--主從複製以及redis複製演進Redis
- MySQL主從複製_複製過濾MySql
- 主從複製
- MySQL主主複製（雙主複製）配置過程介紹MySql
- MySQL主從複製之GTID複製MySql
- MySQL 8 複製（二）——半同步複製MySql
- MySQL的非同步複製和半同步複製MySql非同步
- 主從複製--非同步篇非同步
- Windows 環境下，MySQL 的主從複製和主主複製WindowsMySql
- windows環境下，Mysql的主從複製和主主複製WindowsMySql
- Linux下Redis主從複製以及SSDB主主複製環境部署記錄LinuxRedis
- MysqL主從複製_模式之GTID複製MySql模式
- MySQL 主從複製之多執行緒複製MySql執行緒
- Redis - 主從複製Redis
- Redis：主從複製Redis
- MongoDB主從複製MongoDB
- MySQL 主從複製MySql
- 【MySql】主從複製MySql
- Redis主從複製Redis
- MySQL主從複製MySql
- 聊聊MySQL主從複製的幾種複製方式MySql
- mysql資料庫的主從複製和主主複製實踐MySql資料庫
- 主從複製是啥或者主從複製的原理是什麼？
- MySQL的主從複製MySql