環(huán)境搭建
由于我的電腦配置不是很高, 這里就使用docker搭建環(huán)境. 如果你的電腦配置夠好也是可以使用4臺虛擬機的.
宿主機: Win10
VMware: VMware? Workstation 15 Pro
虛擬機系統(tǒng): Centos7
Docker: 19.03.13
MySQL: 5.7
初始化
- 1.在
/docker/mysql目錄下創(chuàng)建4個文件夾分別是:master1,master2,slave1,slave2分別在每個目錄下建立data,conf,logs用于數(shù)據(jù)持久化, 目錄結(jié)構(gòu)如下
mysql/
├── master1
│ ├── conf
│ ├── data
│ │ ├── ibdata1
│ │ └── ib_logfile101
│ └── logs
├── master2
│ ├── conf
│ ├── data
│ └── logs
├── slave1
│ ├── conf
│ ├── data
│ └── logs
└── slave2
├── conf
├── data
└── logs
└── logs
- 2.初始化容器
master1
docker run -p 4306:3306 --name master01 \
-v /docker/mysql/master1/conf:/etc/mysql/conf.d \
-v /docker/mysql/master1/logs:/var/log/mysql \
-v /docker/mysql/master1/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=MASTERROOT@12345678 -itd mysql:5.7
master2
docker run -p 4307:3306 --name master02 \
-v /docker/mysql/master2/conf:/etc/mysql/conf.d \
-v /docker/mysql/master2/logs:/var/log/mysql \
-v /docker/mysql/master2/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=MASTERROOT@12345678 -itd mysql:5.7
salve1
docker run -p 5306:3306 --name slave01 \
-v /docker/mysql/slave1/conf:/etc/mysql/conf.d \
-v /docker/mysql/slave1/logs:/var/log/mysql \
-v /docker/mysql/slave1/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=SLAVEROOT@12345678 -itd mysql:5.7
salve2
docker run -p 5307:3306 --name slave02 \
-v /docker/mysql/slave2/conf:/etc/mysql/conf.d \
-v /docker/mysql/slave2/logs:/var/log/mysql \
-v /docker/mysql/slave2/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=SLAVEROOT@12345678 -itd mysql:5.7
驗證
docker ps # 檢查是否將mysql容器創(chuàng)建成功
完成后的環(huán)境
查看容器ip可以使用下面的命令
docker exec -it master01 sh # 進入到容器終端
cat /etc/hosts # 查看容器ip
# 也是可以使用下面的命令的
docker inspect master01 | grep IPAddress # master01 為容器名
| 編號 | 角色 | IP地址 |
|---|---|---|
| 1 | Master01 | 172.17.0.5 |
| 2 | Master02 | 172.17.0.4 |
| 3 | Slave01 | 172.17.0.3 |
| 4 | Slave02 | 172.17.0.2 |
配置
雙主機配置
- 1.master01
[root@localhost ~]# touch /docker/mysql/master1/conf/my.cnf
配置內(nèi)容如下:
[mysqld]
# master01主服務(wù)器01唯一ID
server-id=4306
# 啟用二進制日志
log-bin=mysql-bin
#從庫的中繼日志疼进，主庫日志寫到中繼日志，中繼日志再重做到從庫
# relay-log=myslql-relay-bin
# binlog保留時間7天
expire_logs_days=7
# binlog 文件的大小
max_binlog_size=1G
#設(shè)置logbin格式宿百。取值：STATEMENT （默認(rèn)）傅瞻，ROW，MIXED
binlog_format=ROW
# 設(shè)置不要賦值的數(shù)據(jù)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
# 設(shè)置需要復(fù)制的數(shù)據(jù)(可選)
# 如果配置了此項,就是只復(fù)制那個數(shù)據(jù)庫, 如果不指定就是所有
# binlog-do-db=需要復(fù)制的主數(shù)據(jù)庫1
# 設(shè)置login格式
binlog_format=STATEMENT
# 在作為從數(shù)據(jù)庫的時候，有寫入操作也要更新二進制日志文件
log-slave-updates
# 該從庫是否寫入二進制日志。如果需要成為多主則可啟用。只讀可以不需要
log-slave-updates=1
#表示自增長字段每次遞增的量帮非，指自增字段的起始值，其默認(rèn)值是1讹蘑，取值范圍是1 .. 65535
auto-increment-increment=2
# 表示自增長字段從哪個數(shù)開始末盔，指字段一次遞增多少，他的取值范圍是1 .. 65535
auto-increment-offset=1
- 2.master02
[root@localhost ~]# touch /docker/mysql/master2/conf/my.cnf
配置內(nèi)容如下:
[mysqld]
# master01主服務(wù)器01唯一ID
server-id=4307
# 啟用二進制日志
log-bin=mysql-bin
#從庫的中繼日志衔肢，主庫日志寫到中繼日志庄岖，中繼日志再重做到從庫
# relay-log=myslql-relay-bin
# binlog保留時間7天
expire_logs_days=7
# binlog 文件的大小
max_binlog_size=1G
#設(shè)置logbin格式。取值：STATEMENT （默認(rèn)）角骤，ROW隅忿，MIXED
binlog_format=ROW
# 設(shè)置不要賦值的數(shù)據(jù)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
# 設(shè)置需要復(fù)制的數(shù)據(jù)(可選)
# 如果配置了此項,就是只復(fù)制那個數(shù)據(jù)庫, 如果不指定就是所有
# binlog-do-db=需要復(fù)制的主數(shù)據(jù)庫1
# 設(shè)置login格式
binlog_format=STATEMENT
# 在作為從數(shù)據(jù)庫的時候，有寫入操作也要更新二進制日志文件
log-slave-updates
# 該從庫是否寫入二進制日志邦尊。如果需要成為多主則可啟用背桐。只讀可以不需要
log-slave-updates=1
#表示自增長字段每次遞增的量，指自增字段的起始值蝉揍，其默認(rèn)值是1链峭，取值范圍是1 .. 65535
auto-increment-increment=2
# 表示自增長字段從哪個數(shù)開始，指字段一次遞增多少又沾，他的取值范圍是1 .. 65535
auto-increment-offset=1
雙從機配置
- slave01
touch /docker/mysql/slave1/conf/my.cnf
配置內(nèi)容如下:
[mysqld]
# 從服務(wù)唯一ID
server-id=5306
# 啟用中繼日志
relay-log=mysql-relay
- slave02
touch /docker/mysql/slave2/conf/my.cnf
配置內(nèi)容如下:
[mysqld]
# 從服務(wù)唯一ID
server-id=5307
# 啟用中繼日志
relay-log=mysql-relay
重啟容器
docker restart $(docker ps -q)
雙主機賬戶配置
- master01
sql> grant replication slave on *.* TO 'master01slave'@'%' identified by 'ABC123456789@';
sql>flush privileges;
查看狀態(tài)
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 154
Binlog_Do_DB:
Binlog_Ignore_DB: mysql,information_schema,performance_schema,sys
Executed_Gtid_Set:
1 row in set (0.00 sec)
- master02
sql> grant replication slave on *.* TO 'master02slave'@'%' identified by 'ABC123456789@';
sql>flush privileges;
查看狀態(tài)
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 608
Binlog_Do_DB:
Binlog_Ignore_DB: mysql,information_schema,performance_schema,sys
Executed_Gtid_Set:
1 row in set (0.00 sec)
從機復(fù)制主機
slave01復(fù)制master01, slave02復(fù)制master02
復(fù)制命令格式
#復(fù)制主機的命令
CHANGE MASTER TO MASTER_HOST='主機的IP地址',
MASTER_USER='username',
MASTER_PASSWORD='passworld',
MASTER_LOG_FILE='mysql-bin.具體數(shù)字',
MASTER_LOG_POS=具體值;
- slave01的復(fù)制命令
mysql> change master to master_host='172.17.0.5',
-> master_user='master01slave',
-> master_password='ABC123456789@',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=154;
啟用同步進程
mysql> start slave;
查看從庫狀態(tài)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.17.0.5
Master_User: master01slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
當(dāng)我們看到Slave_IO_Running 和Slave_SQL_Running 都為YES時, 說明我們就配置成功了.
- slave02
mysql> change master to master_host='172.17.0.4',
-> master_user='master02slave',
-> master_password='ABC123456789@',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=608;
開啟同步進程
mysql>start slave;
查看從庫狀態(tài)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.17.0.4
Master_User: master02slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 608
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
兩主機互相復(fù)制
master01復(fù)制master02, master2復(fù)制master01
- master01
mysql> change master to master_host='172.17.0.4',
-> master_user='master02slave',
-> master_password='ABC123456789@',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=608;
解析
這里的IP地址為master02的ip,用戶名也是master02提供的.
開啟同步進程
start slave;
查看狀態(tài)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.17.0.4
Master_User: master02slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 608
Relay_Log_File: 65b73f7c9183-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
- master02
mysql> change master to master_host='172.17.0.5',
-> master_user='master01slave',
-> master_password='ABC123456789@',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=154;
啟用同步進程
mysql> start slave;
查看狀態(tài)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.17.0.5
Master_User: master01slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: 0d5426412439-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
測試
在主庫master01上創(chuàng)建一個數(shù)據(jù)庫test_db
mysql> CREATE DATABASE test_db;
在test_db上創(chuàng)建一個數(shù)據(jù)表user
mysql> use test_db;
mysql> CREATE TABLE IF NOT EXISTS `user`(
-> `id` INT UNSIGNED AUTO_INCREMENT,
-> `name` VARCHAR(30) NOT NULL,
-> `age` INT(3) NOT NULL,
-> PRIMARY KEY ( `id` )
-> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
在數(shù)據(jù)表中插入數(shù)據(jù)
mysql> INSERT INTO user (name, age) VALUES ("zhaoming", 18);
mysql> SELECT * FROM user \G;
*************************** 1. row ***************************
id: 1
name: zhaoming
age: 18
1 row in set (0.32 sec)
MySQL雙主雙從配置成功
