?雙主多從架構原理介紹

IP端口賬號密碼服務名

10.1.1.2	3306	root/sync	abc123!	數據庫A
10.1.1.3	3306	root/sync	abc123!	數據庫B
10.1.1.4	3306	root/sync	abc123!	數據庫C

1.兩臺mysql都可讀寫，互為主備，默認只使用一臺（masterA）負責數據的寫入，另一臺（masterB）備用；

2.masterA是masterB的主庫，masterB又是masterA的主庫，它們互為主從；

3.兩臺主庫之間做高可用,可以采用MyCAT中間件暴露一個統一的服務給client,同時當2主任意一臺宕機時，還能提供服務。宕掉的機器重啟恢復之后，數據可以同步得到；

4.所有提供服務的從服務器與masterB進行主從同步（雙主多從）;

#創建目錄 mkdir -p /home/docker-compose/mysql cd /home/docker-compose/mysql/ && mkdir data conf vi docker-compose.yml

version: '3.1' services:mysql:restart: alwaysimage: mysql:5.7container_name: mysqlports:- 3306:3306environment:TZ: Asia/ShanghaiMYSQL_ROOT_PASSWORD: abc123! ##密碼command:--character-set-server=utf8mb4--collation-server=utf8mb4_general_ci--explicit_defaults_for_timestamp=true--lower_case_table_names=1--max_allowed_packet=128M--sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO"volumes:- ./data:/var/lib/mysql- ./conf:/etc/mysql/mysql.conf.d

進入目錄

cd /home/docker-compose/mysql/conf

編寫MySQL配置文件

vi mysqld.cnf

主節點1配置

[mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql default-time-zone='+8:00' symbolic-links=0 server-id = 40 log-bin = mysql-bin enforce_gtid_consistency = ON gtid_mode = ON auto_increment_increment=1 auto_increment_offset=1 log-slave-updates sync_binlog=1 max_connections=1000 innodb_file_per_table=1 innodb_file_format=Barracuda innodb_page_size=32K innodb_log_file_size=148M

主節點2配置

[mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql default-time-zone='+8:00' symbolic-links=0 server-id = 66 log-bin = mysql-bin enforce_gtid_consistency = ON gtid_mode = ON auto_increment_increment=2 auto_increment_offset=2 log-slave-updates sync_binlog=1 max_connections=1000 innodb_file_per_table=1 innodb_file_format=Barracuda innodb_page_size=32K innodb_log_file_size=148

啟動mysql

docker-compose up -d docker-compose down //直接刪除容器 docker-compose stop //不刪除容器 docker-compose start //直接啟動

進入容器：

docker exec -it mysql /bin/bash mysql -uroot -pabc123!

設置2個主節點的主從關系

查看10.1.1.2的日志信息:

File: mysql-bin.000005

Position: 1495

mysql> show master status G; *************************** 1. row ***************************File: mysql-bin.000005Position: 1495Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: c83eb7e9-2556-11eb-b4ad-0242ac120002:20-21, d52d0074-2556-11eb-93b5-0242ac120002:1-20 1 row in set (0.00 sec)ERROR: No query specifiedmysql>

查看10.1.1.3的日志信息:

File: mysql-bin.000004

Position: 1796

mysql> show master status G; *************************** 1. row ***************************File: mysql-bin.000004Position: 1796Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: c83eb7e9-2556-11eb-b4ad-0242ac120002:1-21, d52d0074-2556-11eb-93b5-0242ac120002:20 1 row in set (0.00 sec)ERROR: No query specifiedmysql>

在兩個節點執行

create user 'sync'@'%' identified by 'password'; grant replication slave on *.* to sync;##Mysql復制錯誤error_code: 1045 Access denied for usergrant replication slave on *.* to 'sync'@'%' identified by 'password';flush privileges; //刷新MySQL的系統權限相關表，否則會無法用賬號鏈接

修改master_host與master_log_file與master_log_pos三個值

#節點設置 需要同步的節點（主庫節點）

#在10.1.1.2設置

FLUSH TABLES WITH READ LOCK; change master to master_host='10.1.1.3',master_port=3306,master_user='sync',master_password='password',master_log_file='mysql-bin.000004',master_log_pos=1796; UNLOCK TABLES;

#在10.1.1.3設置

FLUSH TABLES WITH READ LOCK; change master to master_host='10.1.1.2',master_port=3306,master_user='sync',master_password='password',master_log_file='mysql-bin.000005',master_log_pos=1495; UNLOCK TABLES;

開啟主從復制 :

start slave;

檢查

mysql> show slave status G; *************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 10.1.1.3Master_User: syncMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000004Read_Master_Log_Pos: 1796Relay_Log_File: a389ed970e74-relay-bin.000005Relay_Log_Pos: 822Relay_Master_Log_File: mysql-bin.000004Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table:

以下三項為正常

Slave_IO_State: Waiting for master to send event

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

---

未完待續

總結

以上是生活随笔為你收集整理的mysql 互为主备 宕机 数据丢失_Devops部署-mysql主备多从搭建的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。