一、測試環境
1.1 結構圖
1.2 版本
作業系統：CentOS 7.3
MySQL版本：5.6.35
Docker版本：18.06.1-ce
使用root使用者操作
IP位址說明
IP位址
用途
備注
172.16.10.83
MySQL
mysqla
172.16.10.62
MySQL
mysqlb
172.16.10.199
VIP(keepalived)
二、安裝mysql
2.1 建立目錄
mkdir -p /data/mysqldb
mkdir -p /data/docker-compose/mysql-compose
2.2 編寫docker-compose.yml檔案
cd /data/docker-compose/mysql-compose
vim docker-compose.yml
version: '3'
services:
mysql:
image: mysql:5.6.35
ports:
- "3306:3306"
volumes:
- ./my.cnf:/etc/mysql/conf.d/my.cnf
- /data/mysqldb:/var/lib/mysql
- /etc/localtime:/etc/localtime:ro
environment:
- MYSQL_ROOT_PASSWORD=123456
restart: always
将自己的my.cnf檔案放置在/data/docker-compose/mysql-compose目錄下
my.cnf
[client]
default-character-set = utf8
port = 3306
[mysql]
port = 3306
default-character-set = utf8
[mysqld]
port = 3306
basedir = /var/lib/mysql
datadir = /var/lib/mysql
character-set-server = utf8
log-bin = mysql-bin
binlog_cache_size = 1M
expire_logs_days = 10
max_binlog_size = 128M
server_id = 1235
binlog_format=MIXED
read-only=0
auto-increment-increment=10
auto-increment-offset=1
skip-external-locking
slow-query-log = on
long_query_time = 1
slow_query_log_file = /var/lib/mysql/slow.log
lower_case_table_names = 1
max_connections=1100
max_user_connections=100
max_connect_errors=1000
innodb_buffer_pool_size = 100M
innodb_buffer_pool_instances = 8
innodb_log_file_size = 200M
innodb_log_buffer_size = 16M
innodb_log_files_in_group = 3
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 10
innodb_sync_spin_loops = 40
innodb_max_dirty_pages_pct = 90
innodb_support_xa = 0
innodb_thread_concurrency = 0
innodb_thread_sleep_delay = 500
innodb_concurrency_tickets = 1000
log_bin_trust_function_creators = 1
innodb_flush_method = O_DIRECT
innodb_file_per_table
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_io_capacity = 2000
innodb_file_format = Barracuda
innodb_purge_threads=1
innodb_purge_batch_size = 32
innodb_old_blocks_pct=75
innodb_change_buffering=all
innodb_stats_on_metadata=OFF
sql_mode=ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
log-error=/var/lib/mysql/mysql.log
pid-file=/var/lib/mysql/mysql.pid
配置檔案中需要增加(以上檔案已加)
server-id=123
log-bin=mysql-bin
binlog_format=MIXED
read-only=0
auto-increment-increment=10
auto-increment-offset=1
read-only:辨別資料庫是否為隻讀，這裡我們設定為0即非隻讀，該參數針對使用者沒有SUPER權限設定。
auto-increment-increment和auto-increment-offset這兩個參數主要控制MySQL自增列的值，
用于Master-Master之間的複制，防止出現重複值。做了如上配置後，我們向該MySQLA服務中插入第一個id就是1，
第二行的id就是11，而不是2，那麼在MySQLB服務插入第一個id就是2，第二行的id就是12，這樣就不會出現主鍵沖突。
2.3 啟動mysql
docker-compose up -d
#docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
bba3674e9c44 mysql:5.6.35 "docker-entrypoint.s…" 3 hours ago Up 41 minutes 0.0.0.0:3306->3306/tcp mysqlcompose_mysql_1
2.4 同樣方法啟動另一個mysql
# 在另一台啟動mysql前，其配置檔案my.cnf修改
server-id=190
log-bin=mysql-bin
binlog_format=MIXED
relay_log=mysql-relay-bin
log-slave-updates=ON
read-only=0
auto-increment-increment=10
auto-increment-offset=2
三、MySQLA - > MySQLB同步配置
3.1 建立MySQL同步賬号
GRANT REPLICATION SLAVE ON *.* TO 'mysqla'@'172.16.%' IDENTIFIED BY 'mysqla';
#該同步賬号主要是給MySQLB使用。
3.2 檢視MySQLA的master狀态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
3.3 登入MySQLB，執行如下指令
change master to
master_host='172.16.10.83',
master_user='mysqla',
master_password='mysqla',
master_log_file='mysql-bin.000006',
master_log_pos=120;
3.4 在MySQLB中執行同步指令
mysql> start slave;
3.5 在MySQLB中執行,檢視是否配置成功
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.10.83
Master_User: mysqla
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 120
Relay_Log_File: mysql-relay-bin.000006
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
四、MySQLB - > MySQLA同步配置
4.1 建立MySQL同步賬号
GRANT REPLICATION SLAVE ON *.* TO 'mysqlb'@'172.16.%' IDENTIFIED BY 'mysqlb';
#該同步賬号主要是給MySQLA使用。
4.2 登入MySQLB檢視master狀态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 | 473 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
4.3 登入MySQLA資料庫執行如下同步指令
change master to
master_host='172.16.10.62',
master_user='mysqlb',
master_password='mysqlb',
master_log_file='mysql-bin.000006',
master_log_pos=473;
4.4 在MySQLA庫中執行啟動同步指令
mysql> start slave;
4.5 登入MySQLA驗證同步指令是否執行成功
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.10.62
Master_User: mysqlb
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 756
Relay_Log_File: mysql-relay-bin.000006
Relay_Log_Pos: 467
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
五、主主互備驗證
5.1 登入MySQLA資料庫建立test庫
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
5.2 在MySQLB上檢視建立的資料庫test
資料庫同步過來了
5.3 在MySQLB上删除資料庫test
5.4 檢視MySQLA中資料庫test是否被删除
确實已經不存在了，到此MySQL主主互備完成。
六、keepalived
6.1 分别在兩台mysql伺服器上執行安裝
yum -y install keepalived
systemctl enable keepalived
6.2 MySQL服務狀态監控腳本
MySQL服務狀态監測腳本，主要監控MySQL服務狀态是否正常，如果不正常則将該MySQL所在服務的Keepalived服務殺死，監控MySQL服務是否正常的方法有多種可以通過端口号、程序ID以及執行MySQL指令，這裡我們使用mysladmin執行指令來監測MariaDB服務是否正常，腳本内容如下(check_mysql.sh)
cd /etc/keepalived
vim check_mysql.sh
#!/bin/bash
MYSQL_PING=`docker exec mysqlcompose_mysql_1 mysqladmin -h127.0.0.1 -uroot -p123456 ping 2>/dev/null`
MYSQL_OK="mysqld is alive"
if [[ "$MYSQL_PING" != "$MYSQL_OK" ]];then
echo "mysql is not running."
killall keepalived
else
echo "mysql is running"
fi
注：兩台伺服器都要配置
6.4 MySQLA伺服器Keepalived配置
! Configuration File for keepalived
global_defs {
}
vrrp_script check_mysql {
script "/etc/keepalived/check_mysql.sh"
interval 2
}
vrrp_instance VI_1 {
state MASTER
interface eth0
virtual_router_id 51
priority 100
advert_int 1
nopreempt
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
172.16.10.199
}
track_script {
check_mysql
}
}
注：這個是master
6.5 MySQLB伺服器Keepalived配置
! Configuration File for keepalived
global_defs {
}
vrrp_script check_mysql {
script "/etc/keepalived/check_mysql.sh"
interval 2
}
vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 51
priority 90
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
172.16.10.199
}
track_script {
check_mysql
}
}
6.6 分别啟動keepalived
systemctl start keepalived
6.7 檢視VIP是否被綁定在MASTER的網卡上
Master在172.16.10.83這個伺服器上
七、驗證
7.1 驗證聯通性
在兩台伺服器上pingVIP
172.16.10.83
172.16.10.62
7.2 驗證VIP是否會漂移
将keepalive的master這個停掉，VIP就應該不在這個伺服器上了
# MySQLA伺服器
systemctl stop keepalived
檢視BACKUP伺服器，确實已經漂移過來了
7.3 驗證mysql停止VIP是否會漂移
在MySQLA伺服器上停止mysql容器
#docker stop mysqlcompose_mysql_1
mysqlcompose_mysql_1
[[email protected] /etc/keepalived]
#docker ps | grep mysqlcompose_mysql_1
[[email protected] /etc/keepalived]
#ip addr list eth0
2: eth0: mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether fa:36:67:e1:b4:00 brd ff:ff:ff:ff:ff:ff
inet 172.16.10.83/24 brd 172.16.10.255 scope global eth0
valid_lft forever preferred_lft forever
inet6 fe80::f836:67ff:fee1:b400/64 scope link
valid_lft forever preferred_lft forever
可見VIP已經不在了
事實上keepalived也停止了
VIP自動漂移到MySQLB這台伺服器上了