### 环境准备
系统: `CentOS7`
数据库: `MySQL5.7`
Master1节点: `172.16.0.100(node1)`
Master2节点: `172.16.0.101(node2)`
VIP: `172.16.0.188`
同步数据库名:`replicatest`
 
### 安装MySQL
`Master1/Master2节点`
```bash
yum localinstall https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
yum install mysql-community-server
```
启动数据库
```bash
systemctl enable mysqld
systemctl start mysqld
```
获取数据库临时密码
```bash
grep 'temporary password' /var/log/mysqld.log
```
对数据库进行加固
```bash
mysql_secure_installation
```
 
### 配置Master1
修改数据库配置
```bash
vim /etc/my.cnf
```
```ini
bind-address = 172.16.0.100
server-id = 1
log_bin = mysql-bin
binlog-do-db = replicatest
replicate-do-db = replicatest
auto-increment-increment = 2
auto-increment-offset = 1
```
重启数据库
```bash
systemctl restart mysqld
```
创建复制账号
```sql
mysql> CREATE USER 'replica'@'172.16.0.101' IDENTIFIED BY 'strong_password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replica'@'172.16.0.101';
mysql> FLUSH PRIVILEGES;
```
查看二进制日志文件和位置
```sql
mysql> FLUSH TABLES WITH READ LOCK #锁表，备份数据，恢复到master2，再让master2从当前位置去同步数据，保证了数据的一致性
mysql> SHOW MASTER STATUS\G
```
```
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 623
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
```
`注意：文件:mysql-bin.000001,位置:623`
备份replicatest数据
```bash
mysqldump -h127.0.0.1 -uroot -pxxxx --databases replicatest --events --triggers --routines >replicatest.sql
```
### 拷贝数据到master2
```bash
scp replicatest.sql root@node2:/root
```
 
### 配置Master2
修改数据库配置
```bash
vim /etc/my.cnf
```
```ini
bind-address = 172.16.0.101
server-id = 2
log_bin = mysql-bin
binlog-do-db = replicatest
replicate-do-db = replicatest
#binlog_ignore_db = mysql
#binlog_ignore_db = infomation_schema
#binlog_ignore_db = performance_schema
auto-increment-increment = 2
auto-increment-offset = 2
```
重启数据库
```bash
systemctl restart mysqld
```
恢复数据库
```sql
mysql>source replicatest.sql
```
停止slave线程
```sql
mysql> STOP SLAVE;
```
配置master2复制master1
```sql
mysql> CHANGE MASTER TO
mysql> MASTER_HOST='172.16.0.100',
mysql> MASTER_USER='replica',
mysql> MASTER_PASSWORD='strong_password',
mysql> MASTER_LOG_FILE='mysql-bin.000001',
mysql> MASTER_LOG_POS=623;
```
启动slave线程
```sql
mysql> START SLAVE;
```
查看slave状态
```sql
mysql > show slave status\G
```
```
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.0.100
Master_User: replica
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 623
Relay_Log_File: node2-relay-bin.000001
Relay_Log_Pos: 323
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 623
Relay_Log_Space: 544
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: f8cc4c10-b429-11e9-9ff9-5600023106ec
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
```
创建复制账号
```bash
mysql> CREATE USER 'replica'@'172.16.0.100' IDENTIFIED BY 'strong_password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replica'@'172.16.0.100';
mysql> FLUSH PRIVILEGES;
```
查看当前的二进制日志
```sql
mysql> SHOW MASTER STATUS\G
```
```
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 504
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
```
`注意：文件:mysql-bin.000001,位置:504`
 
### 配置Master1
解锁只读锁
```sql
mysql> UNLOCK TABLES
```
停止slave线程
```sql
mysql> STOP SLAVE;
```
配置同步
```sql
mysql> CHANGE MASTER TO
mysql> MASTER_HOST='172.16.0.101',
mysql> MASTER_USER='replica',
mysql> MASTER_PASSWORD='strong_password',
mysql> MASTER_LOG_FILE='mysql-bin.000001',
mysql> MASTER_LOG_POS=504;
```
启动slave线程
```sql
mysql> START SLAVE;
```
查看同步状态
```sql
mysql> show slave status\G
```
```
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.0.101
Master_User: replica
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 504
Relay_Log_File: node1-relay-bin.000001
Relay_Log_Pos: 245
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 504
Relay_Log_Space: 213
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_UUID: 1c505063-b42a-11e9-96c3-560002320f46
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
```
 
### 安装Keepalived
`Master1节点/Master2节点`
```bash
yum install keepalived -y
```
### 配置Keepalived
`Master1`
```bash
vim /etc/keepalived/keepalived.conf #除了参数priority两台Master设置优先级不一样，其他参数保存一致
```
```ini
! Configuration File for keepalived
global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 192.168.200.1
smtp_connect_timeout 30
router_id LVS_DEVEL
vrrp_skip_check_adv_addr
vrrp_strict
vrrp_garp_interval 0
vrrp_gna_interval 0
}
vrrp_script vs_mysql_100 {
script "/etc/keepalived/checkmysql.sh"
interval 10
}
vrrp_instance VI_100 { #集群的名字
state BACKUP #这里两台都配置BACKUP
nopreempt #设置为不抢占模式
interface eth1 #VIP绑定的网卡eth1
virtual_router_id 51 #vrid的两台一样
priority 100 #优先级，两台设置不一样，master1设置为100，master2设置为90
advert_int 5 #主备之前同步建成的时间间隔是5秒
authentication {
auth_type PASS #验证方式通过密码
auth_pass 9898 #验证密码
}
track_script {
vs_mysql_100 #执行监控的脚本
}
virtual_ipaddress {
172.16.0.188 #虚拟VIP地址
}
}
```
#### 创建数据库监控脚本
`Master1/Master2节点`
```bash
cat /etc/keepalived/checkmysql.sh
```
```sh
#!/bin/bash
mysqlstr=/usr/bin/mysql
host=127.0.0.1
user=root
password=xxxxxxx
port=3306
mysql_status=1
$mysqlstr -h $host -u $user -p$password -P$port -e "show status;" >/dev/null 2>&1
if [ $? -eq 0 ];then
echo "mysql_status=1"
exit 0
else
echo "systemctl stop keepaliaved"
systemctl stop keepaliaved
fi
```
添加执行权限
```bash
chmod +x /etc/keepalived/checkmysql.sh
```
### 启动keepalived
`Master1/Master2节点`
```bash
systemctl start keepalived
```
 
### 测试主主
`Master1`
插入测试数据
```sql
mysql> use replicatest;
mysql> insert into test(name) values('master1 write');
```
`Master2`
查看是否同步
```sql
mysql> use replicatest;
mysql> select * from test;
```
```
+----+---------------+
| id | name |
+----+---------------+
| 19 | master1 write |
+----+---------------+
```
插入测试数据
```sql
mysql> insert into test(name) values('master2 write');
```
`Master1`
查看是否同步
```sql
select * from test;
```
```
+----+---------------+
| id | name |
+----+---------------+
| 19 | master1 write |
| 20 | master2 write |
+----+---------------+
```
`通过输出可以看到master1和master2都能同步成功!`
 
### 测试keepalived
**停止Master1上的MySQL，查看VIP是否转移到MASTER2**
`Master1`
```bash
systemcat stop mysqld
```
查看VIP是否漂移成功
`Master2`
```bash
ip a
```
```
...
3: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1450 qdisc pfifo_fast state UP group default qlen 1000
link/ether 5a:00:02:32:0f:46 brd ff:ff:ff:ff:ff:ff
inet 172.16.0.101/16 brd 172.16.255.255 scope global eth1
valid_lft forever preferred_lft forever
inet 172.16.0.188/32 scope global eth1
valid_lft forever preferred_lft forever
```
`通过测试发现VIP漂移成功`
