MySQL資料庫主從復制--高級進階功能
- 前言
- 一、延時同步
- 二、過濾復制
- 1、限制主庫
- 2、限制從庫
- 三、GTID 復制（重點掌握）
- 四、半同步復制（了解）
前言
本環境是基于 Centos 7.8 系統構建MySQL-5.7.14
具體構建，請參考 MySQL-5.7.14 環境構建
一、延時同步
功能介紹
是我們人為配置的一種特殊從庫.人為配置從庫和主庫延時N小時.
為什么要有延時從：
解決資料庫故障的問題
- 物理損壞: 主從復制非常擅長解決物理損壞，比如主庫rm 洗掉資料庫資料，直接將應用切換到從庫.
- 邏輯損壞：普通主從復制沒辦法解決邏輯損壞，比如主庫 drop database dbname
物理故障
邏輯故障
故障恢復思路
故障恢復思路:
- 1主1從,從庫延時5分鐘,主庫誤洗掉1個庫
- 5分鐘之內 偵測到誤洗掉操作
- 停從庫SQL執行緒
- 截取relaylog
- 起點 :停止SQL執行緒時,relay最后應用位置
- 終點:誤洗掉之前的position(GTID)
- 恢復截取的日志到從庫
- 從庫身份解除,替代主庫作業
案例實施
從庫配置延時同步
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> change master to master_delay=300;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
主庫創建relay資料庫、relay表，并插入兩條資料
mysql> create database relay;
Query OK, 1 row affected (0.00 sec)
mysql> use relay;
Database changed
mysql> create table tb1(id int);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into tb1 values(1),(2);
Query OK, 2 rows affected (0.17 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from tb1;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
查看從庫延時同步狀態
查看從庫資料庫資訊
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
從庫
# 停止sql執行緒
mysql> stop slave sql_thread;
Query OK, 0 rows affected (0.00 sec)
查看從庫中繼日志位置點資訊
提取從庫中繼日志資訊
[root@mysql-source_code ~]# mkdir /backup/mysqld/ -p
[root@mysql-source_code ~]# mysqlbinlog --start-position=385 --stop-position=908 mysql-source_code-relay-bin.000002 -r /backup/mysqld/relay.sql
[root@mysql-source_code ~]# ll /backup/mysqld/relay.sql
-rw-r----- 1 root root 2707 Feb 6 11:35 /backup/mysqld/relay.sql
恢復從庫中繼日志
mysql> source /backup/mysqld/relay.sql
查看從庫恢復資料狀況
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| mysql |
| performance_schema |
| relay |
| sys |
+--------------------+
7 rows in set (0.00 sec)
mysql> use relay
Database changed
mysql> select * from tb1;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
從庫身份解除
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> reset slave all;
Query OK, 0 rows affected (0.01 sec)
二、過濾復制
功能介紹
過濾復制可以從兩方面下手：
- 配置主庫的dump執行緒, 讓其只發送需要同步的db二進制
- 配置從庫的sql執行緒, 讓其只回放我們需要同步的db二進制檔案
具體原理
過濾復制應用場景：
案例實施
1、限制主庫
修改主控組態檔
[root@mysql-yum ~]# vim /etc/my.cnf
binlog_ignore_db=d2
[root@mysql-yum ~]# systemctl restart mysqld
主庫創建資料庫
mysql> create database d1;
Query OK, 1 row affected (0.00 sec)
mysql> create database d2;
Query OK, 1 row affected (0.00 sec)
mysql> create database d3;
Query OK, 1 row affected (0.00 sec)
mysql> show databases like 'd_';
+---------------+
| Database (d_) |
+---------------+
| d1 |
| d2 |
| d3 |
+---------------+
3 rows in set (0.00 sec)
從庫查看
mysql> show databases like 'd_';
+---------------+
| Database (d_) |
+---------------+
| d1 |
| d3 |
+---------------+
2 rows in set (0.00 sec)
2、限制從庫
修改從庫組態檔
[root@mysql-source_code ~]# vim /etc/my.cnf
[mysqld3307]
replicate_ignore_db=mysql_1
[root@mysql-source_code ~]# mysqld_multi stop 3307
[root@mysql-source_code ~]# mysqld_multi start 3307
主庫創建資料庫
mysql> create database mysql_1;
Query OK, 1 row affected (0.00 sec)
mysql> create database mysql_2;
Query OK, 1 row affected (0.00 sec)
mysql> create database mysql_3;
Query OK, 1 row affected (0.00 sec)
mysql> show databases like 'mysql__';
+--------------------+
| Database (mysql__) |
+--------------------+
| mysql_1 |
| mysql_2 |
| mysql_3 |
+--------------------+
3 rows in set (0.00 sec)
從庫查看
mysql> show databases like 'mysql__';
+--------------------+
| Database (mysql__) |
+--------------------+
| mysql_2 |
| mysql_3 |
+--------------------+
2 rows in set (0.00 sec)
三、GTID 復制（重點掌握）
無論是級聯情況，還是一主多從情況，都可以通過GTID自動找點兒，而無需像之前那樣通過File_name和File_position找點兒了，
具體原理
- master更新資料時，會在事務前產生GTID，一同記錄到binlog日志中，
- slave端的i/o 執行緒將變更的binlog，寫入到本地的relay log中，
- sql執行緒從relay log中獲取GTID，然后對比slave端的binlog是否有記錄，
- 如果有記錄，說明該GTID的事務已經執行，slave會忽略，
- 如果沒有記錄，slave就會從relay log中執行該GTID的事務，并記錄到binlog
案例實施
配置主庫組態檔
[root@mysql-yum ~]# vim /etc/my.cn
[mysqld]
log_bin=mysql-bin
server_id=1
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[root@mysql-yum ~]# systemctl restart mysqld
主庫創建同步賬號
mysql> grant replication slave on *.* to zwq@'192.168.5.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec
配置從庫組態檔
[root@localhost ~]# vim /etc/my.cnf
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user = zwq
pass = 123456
[mysqld3306]
socket = /mysql/3306/mysql.sock
port = 3306
pid-file = /mysql/3306/mysql.pid
datadir = /mysql/3306/data
basedir = /usr/local/mysql
server_id=2
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysqld3307]
socket = /mysql/3307/mysql.sock
port = 3307
pid-file = /mysql/3307/mysql.pid
datadir = /mysql/3307/data
basedir = /usr/local/mysql
server_id=3
replicate_ignore_db=mysql_1
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[root@mysql-source_code ~]# mysqld_multi stop 3306,3307
[root@mysql-source_code ~]# netstat -lnutp | grep 330
[root@mysql-source_code ~]# mysqld_multi start 3306,3307
[root@mysql-source_code ~]# netstat -lnutp | grep 330
tcp6 0 0 :::3306 :::* LISTEN 3932/mysqld
tcp6 0 0 :::3307 :::* LISTEN 3949/mysqld
查看主庫二進制檔案資訊
配置從服務器
--- 3306
mysql> change master to
-> master_host='192.168.5.11',
-> master_user='zwq',
-> master_password='123456',
-> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.06 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
--- 3307
mysql> change master to
-> master_host='192.168.5.11',
-> master_user='zwq',
-> master_password='123456',
-> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.11 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
查看從服務器狀態
3306
3307
測驗：
創建資料庫
mysql> create databases s1;
mysql> show databases like 's_';
+---------------+
| Database (s_) |
+---------------+
| s1 |
+---------------+
1 row in set (0.00 sec)
從庫查看
--- 3306
mysql> show databases like 's_';
+---------------+
| Database (s_) |
+---------------+
| s1 |
+---------------+
1 row in set (0.01 sec)
--- 3307
mysql> show databases like 's_';
+---------------+
| Database (s_) |
+---------------+
| s1 |
+---------------+
1 row in set (0.00 sec)
四、半同步復制（了解）
半同步簡介: 解決主從資料一致性問題
在默認情況下，MySQL的復制是異步的，這意味著主服務器及其從服務器是獨立的，異步復制可以提供最佳的性能，因為主服務器在將更新的資料寫入它的二進制日志（Binlog）檔案中后，無需等待驗證更新資料是否已經復制到從服務器中，就可以自由處理其它進入的事務處理請求，但這也同時帶來了很高的風險，如果在主服務器或從服務器端發生故障，會造成主從資料的不一致，甚至在恢復時造成資料丟失，
從MySQL5.5開始引入了一種半同步復制功能，該功能可以確保主服務器和訪問鏈中至少一臺從服務器之間的資料一致性和冗余，在這種配置結構中，一臺主服務器和其許多從服務器都進行了配置，這樣在復制拓撲中，至少有一臺從服務器在父主服務器進行事務處理前，必須確認更新已經收到并寫入了其中繼日志 （Relay Log），當出現超時，源主服務器必須暫時切換到異步復制模式重新復制，直到至少有一臺設定為半同步復制模式的從服務器及時收到資訊，
繼5.5半同步復制后，MySQL5.6又對其進行了優化和改進，其中有兩個地方較為重要：
- 在主從切換后，在傳統的方式里，需要找到binlog和POS點，然后更改master指向，而在mysql5.6里，你無須再知道binlog和POS點，你只需要知道master的IP、埠，賬號密碼即可，因為同步復制是自動的，mysql通過內部機制GTID自動找點同步，
- 多執行緒復制，以前的版本，同步復制是單執行緒的，只能一個一個執行，在MySQL5.6里，可以做到多個庫之間的多執行緒復制，但一個庫里的表，多執行緒復制是無效的，
配置思路
加載插件
主:
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
從:
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
查看是否加載成功:
show plugins;
啟動:
主:
SET GLOBAL rpl_semi_sync_master_enabled = 1;
從:
SET GLOBAL rpl_semi_sync_slave_enabled = 1;
重啟從庫上的IO執行緒
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;
查看是否在運行
主:
show status like 'Rpl_semi_sync_master_status';
從:
show status like 'Rpl_semi_sync_slave_status';
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/257435.html
標籤：其他