MySQL高可用之多源複製

語言: CN / TW / HK

theme: awesome-green

持續創作,加速成長!這是我參與「掘金日新計劃 · 6 月更文挑戰」的第20天,點選檢視活動詳情

📢📢📢📣📣📣\ 哈嘍!大家好,我是【IT邦德】,江湖人稱jeames007,10年DBA工作經驗\ 中國DBA聯盟(ACDU)成員,目前從事DBA及程式程式設計😜😜😜\ 擅長Oracle、MySQL、PG 運維開發,備份恢復,安裝遷移,效能優化、故障應急處理。\ ❤️❤️❤️感謝各位大可愛小可愛!❤️❤️❤️

摘要:在 MySQL 8.0 版本中,提供了多源複製,多源複製的出現對於分庫分表的業務提供了極大的便利,本文做了詳細的說明

⛳️ 1.多源複製簡介

在 MySQL 8.0 版本中,提供了多源複製,多源複製的出現對於分庫分表的業務提供了極大的便利, 目前我們已經部署了多套多源複製供統計使用。 MySQL 5.7 之前只能實現一主一從、一主多從或者多主多從的複製。 如果想實現多主一從的複製,只能使用 MariaDB,但是 MariaDB 又與官方的MySQL 版本不相容。 MySQL 5.7 開始支援了多主一從的複製方式,也就是多源複製。 MySQL 8.0 版本相比之前的版本,無論在功能還是效能、安全等方面都已經有不少的提升。 拓展:MySQL之父Widenius先生離開了Sun之後,覺得依靠Sun/Oracle來發展MySQL, 實在很不靠譜,於是決定另開分支,這個分支的名字叫做MariaDB。

📢📢📢 多源複製的好處

可以集中備份,在從庫上備份,不會影響線上的資料正常執行 節約購買從庫伺服器的成本,只需要一個從庫伺服器即可 資料彙總在一起,方便後期做資料統計 減輕DBA維護工作量。

⛳️ 2.多源複製使用場景

Backing up multiple servers to a single server. \ Merging table shards.\ Consolidating data from multiple servers to a single server.

在這裡插入圖片描述

如上圖,多源複製採用多通道的模式,和普通的複製相比,就是使用 FOR CHANNEL進行了分離。 CHANGE MASTERTO....FORCHANNEL'm1'; CHANGE MASTERTO....FORCHANNEL'm2'; 要開啟多源複製功能必須需要在從庫上設定 master-info-repository 和 relay-log-info-repository 這兩個引數。 這兩個引數是用來儲存同步資訊的,可以設定的值為 FILE 和 TABLE,5.7預設值是 FILE。 比如 master-info 就儲存在 master.info 檔案中, relay-log-info 儲存在 relay-log.info 檔案中, 如果伺服器意外關閉,正確的 relay-log-info 沒有來得及更新到 relay-log.info 檔案,這樣會造成資料丟失。 為了資料更加安全,通常設為 TABLE。這些表都是 innodb 型別的,支援事務。 相對檔案儲存安全得多。在 MySQL 庫下可以看見這兩個表資訊,分別是 mysql.slave_master_info 和 mysql.slave_relay_log_info。 這兩個引數也是可以動態調整的。 SET GLOBAL master_info_repository='TABLE'; SET GLOBAL relay_log_info_repository='TABLE';

⛳️ 3.多源複製部署

📢📢📢 搭建過程支援GTID複製模式和binlog+position方式複製。 在這裡插入圖片描述

✨ 3.1 下載映象

docker pull mysql:8.0.27 --docker network create --subnet=172.72.0.0/24 mysql-network docker rm -f mysql8027M33265 mysql8027M33266 mysql8027M33267 mysql8027M33268

✨ 3.2 建立對映目錄

mkdir -p /mysqlmultiple/master1/conf.d mkdir -p /mysqlmultiple/master1/data mkdir -p /mysqlmultiple/master2/conf.d mkdir -p /mysqlmultiple/master2/data mkdir -p /mysqlmultiple/master3/conf.d mkdir -p /mysqlmultiple/master3/data mkdir -p /mysqlmultiple/slave/conf.d mkdir -p /mysqlmultiple/slave/data

✨ 3.3 建立容器,安裝MySQL

```bash docker run -d --name mysql8027M33265 \ -h master1 -p 33265:3306 --net=mysql-network --ip 172.72.0.10 \ -v /mysqlmultiple/master1/conf.d:/etc/mysql/conf.d -v /mysqlmultiple/master1/data:/var/lib/mysql/ \ -e MYSQL_ROOT_PASSWORD=jem \ mysql:8.0.27

docker run -d --name mysql8027M33266 \ -h master2 -p 33266:3306 --net=mysql-network --ip 172.72.0.11 \ -v /mysqlmultiple/master2/conf.d:/etc/mysql/conf.d -v /mysqlmultiple/master2/data:/var/lib/mysql/ \ -e MYSQL_ROOT_PASSWORD=jem \ mysql:8.0.27

docker run -d --name mysql8027M33267 \ -h master3 -p 33267:3306 --net=mysql-network --ip 172.72.0.12 \ -v /mysqlmultiple/master3/conf.d:/etc/mysql/conf.d -v /mysqlmultiple/master3/data:/var/lib/mysql/ \ -e MYSQL_ROOT_PASSWORD=jem \ mysql:8.0.27

docker run -d --name mysql8027S33268 \ -h slave1 -p 33268:3306 --net=mysql-network --ip 172.72.0.13 \ -v /mysqlmultiple/slave/conf.d:/etc/mysql/conf.d -v /mysqlmultiple/slave/data:/var/lib/mysql/ \ -e MYSQL_ROOT_PASSWORD=jem \ mysql:8.0.27 ```

✨ 3.4 引數檔案配置

```bash cat > /mysqlmultiple/master1/conf.d/my.cnf << "EOF" [mysqld] user=mysql port=3306 character_set_server=utf8mb4 secure_file_priv= server-id = 802733265 log-bin = binlog_format=row expire_logs_days = 30 max_binlog_size = 100M binlog-ignore-db = mysql binlog-ignore-db = information_schema binlog-ignore-db = performance_schema binlog-ignore-db = sys replicate_ignore_db=information_schema replicate_ignore_db=performance_schema replicate_ignore_db=mysql replicate_ignore_db=sys gtid-mode=ON enforce-gtid-consistency=on skip_name_resolve report_host=172.72.0.10 EOF

cat > /mysqlmultiple/master2/conf.d/my.cnf << "EOF" [mysqld] user=mysql port=3306 character_set_server=utf8mb4 secure_file_priv= server-id = 802733266 log-bin = binlog_format=row expire_logs_days = 30 max_binlog_size = 100M binlog-ignore-db = mysql binlog-ignore-db = information_schema binlog-ignore-db = performance_schema binlog-ignore-db = sys replicate_ignore_db=information_schema replicate_ignore_db=performance_schema replicate_ignore_db=mysql replicate_ignore_db=sys gtid-mode=ON enforce-gtid-consistency=ON skip_name_resolve report_host=172.72.0.11 EOF

cat > /mysqlmultiple/master3/conf.d/my.cnf << "EOF" [mysqld] user=mysql port=3306 character_set_server=utf8mb4 secure_file_priv= server-id = 802733267 log-bin = binlog_format=row expire_logs_days = 30 max_binlog_size = 100M binlog-ignore-db = mysql binlog-ignore-db = information_schema binlog-ignore-db = performance_schema binlog-ignore-db = sys replicate_ignore_db=information_schema replicate_ignore_db=performance_schema replicate_ignore_db=mysql replicate_ignore_db=sys gtid-mode=ON enforce-gtid-consistency=ON skip_name_resolve report_host=172.72.0.12 EOF

cat > /mysqlmultiple/slave/conf.d/my.cnf << "EOF" [mysqld] user=mysql port=3306 character_set_server=utf8mb4 secure_file_priv='' server-id = 802733268 log-bin = binlog_format=row expire_logs_days = 30 max_binlog_size = 100M binlog-ignore-db = mysql binlog-ignore-db = information_schema binlog-ignore-db = performance_schema binlog-ignore-db = sys replicate_ignore_db=information_schema replicate_ignore_db=performance_schema replicate_ignore_db=mysql replicate_ignore_db=sys gtid-mode=ON enforce-gtid-consistency=ON skip_name_resolve report_host=172.72.0.13 master-info-repository = table relay-log-info-repository = table EOF

docker restart mysql8027M33265 docker restart mysql8027M33266 docker restart mysql8027M33267 docker restart mysql8027S33268 docker ps ```

📢📢📢 登陸容器,確認資料 docker ps --format "table {{.ID}}\t{{.Names}}\t{{.Status}}\t{{.Ports}}" 在這裡插入圖片描述

✨ 3.5 登陸MySQL

```bash docker exec -it mysql8027M33265 mysql -uroot -pjem mysql -uroot -pjem -h192.168.1.54 -P33265 -e "select @@hostname,@@server_id,@@server_uuid" mysql -uroot -pjem -h192.168.1.54 -P33266 -e "select @@hostname,@@server_id,@@server_uuid" mysql -uroot -pjem -h192.168.1.54 -P33267 -e "select @@hostname,@@server_id,@@server_uuid" mysql -uroot -pjem -h192.168.1.54 -P33268 -e "select @@hostname,@@server_id,@@server_uuid"

遠端登陸需要修改密碼 docker exec -it mysql8027M33266 mysql -uroot -pjem mysql> alter user root@'%' identified with mysql_native_password by 'root'; --更改密碼 mysql> flush privileges; docker exec -it mysql8027S33268 mysql -uroot -pjem ```

⛳️ 4.主從配置

✨ 4.1 主庫配置

```bash --在 3 臺主庫 mysql -uroot -proot -h192.168.1.54 -P33265 mysql -uroot -proot -h192.168.1.54 -P33266 mysql -uroot -proot -h192.168.1.54 -P33267

mysql> create user repl@'%' identified with mysql_native_password by 'root'; mysql> grant all on . to repl@'%' with grant option; mysql> flush privileges;

select user,host,grant_priv,password_last_changed,authentication_string from mysql.user;

show master status \G; show slave hosts; select @@hostname,@@server_id,@@server_uuid; ```

✨ 4.2 從庫配置

```bash mysql -uroot -proot -h192.168.1.54 -P33268

change master to master_host='172.72.0.10', master_port=3306,master_user='repl', master_password='root', master_auto_position=1 FOR CHANNEL 'Master1'; show slave status \G;

-- 啟動所有 SLAVE mysql> START SLAVE;

--主庫建立資料測試 mysql -uroot -proot -h192.168.1.54 -P33265 mysql> create database test;

--主庫2和3 change master to master_host='172.72.0.11', master_port=3306, master_user='repl', master_password='root', master_auto_position=1 FOR CHANNEL 'Master2';

change master to master_host='172.72.0.12', master_port=3306, master_user='repl', master_password='root', master_auto_position=1 FOR CHANNEL 'Master3';

-- 也可以單獨啟動需要同步的通道 START SLAVE FOR CHANNEL 'master2'; START SLAVE FOR CHANNEL 'master3';

select a.master_log_pos,a.host,a.user_name,a.user_password,a.port,a.uuid,a.channel_name from mysql.slave_master_info a; ``` 在這裡插入圖片描述 在這裡插入圖片描述

⛳️ 5.測試多源複製

``bash -- mysql -uroot -proot -h192.168.1.54 -P33265 create database master1; use master1; CREATE TABLEtest1(idint(11) DEFAULT NULL,count` int(11) DEFAULT NULL); insert into test1 values(1,1);

-- mysql -uroot -proot -h192.168.1.54 -P33266 create database master2; use master2; CREATE TABLE test2 (id int(11) DEFAULT NULL,count int(11) DEFAULT NULL); insert into test2 values(2,2);

-- mysql -uroot -proot -h192.168.1.54 -P33267 create database master3; use master3; CREATE TABLE test3 (id int(11) DEFAULT NULL,count int(11) DEFAULT NULL); insert into test3 values(3,3);

--從庫查詢 -- mysql -uroot -proot -h192.168.1.54 -P33268 show databases; SELECT * FROM master1.test1; SELECT * FROM master2.test2; SELECT * FROM master3.test3; ```

⛳️ 6. 注意的點

1、初次配置耗時較長,需要將各個 master 的資料 dump 下來,再 source 到 slave 上。\ 2、需要考慮各 master 資料增長頻率,slave 的資料增長頻率是這些資料的總和。如果太高,會導致大量的磁碟 IO,造成資料更新延遲,最嚴重的是會影響正常的查詢。\ 3、如果多個主資料庫例項中存在同名的庫,則同名庫的表都會放到一個庫中;\ 4、如果同名庫中的表名相同且結構相同,則資料會合併到一起;如果結構不同,則先建的有效。 在這裡插入圖片描述