1.安装环境:
服务器 | IP | OS | MySQL同步用户名/密码 | VIP |
---|---|---|---|---|
master1 | 192.168.0.110 | CentOS 7.5 | sync/syncpwd | 192.168.0.250 |
master2 | 192.168.0.111 | CentOS 7.5 | sync/syncpwd | 192.168.0.250 |
MySQL:5.7
Keepalived:2.0.10,VIP:192.168.0.250
MySQL安装参考CentOS 7.5安装MySQL 5.7教程 免安装版
Keepalived安装参考CentOS 7.5安装Keepalived教程
2.MySQL双主配置
服务器中已经成功安装MySQL服务器
master1修改my.cnf,新增如下配置:
>vim /etc/my.cnf
[msqyld]
server-id=110
log-bin=mysql-bin
sync-binlog=1
binlog-checksum=none
binlog-format=mixed
auto-increment-increment=2
auto-increment-offset=1
log-slave-updates
slave-skip-errors=all
master2修改my.cnf,新增如下配置:
>vim /etc/my.cnf
[msqyld]
server-id=111
log-bin=mysql-bin
sync-binlog=1
binlog-checksum=none
binlog-format=mixed
auto-increment-increment=2
auto-increment-offset=2
log-slave-updates
slave-skip-errors=all
master1和master2重启MySQL服务:
>service mysqld restart
master1和master2新增同步账户,见MySQL安装参考
在master1中为mysql从库账户授权:
>grant replication slave on *.* to 'sync'@'%' identified by 'syncpwd';
>flush privileges;
>show master status; #当前主库状态,即master1
show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 | 599 | | | |
+------------------+----------+--------------+------------------+-------------------+
在master2中为mysql从库账户授权:
>grant replication slave on *.* to 'sync'@'%' identified by 'syncpwd';
>flush privileges;
>show master status; #当前主库状态,即master2
show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 468 | | | |
+------------------+----------+--------------+------------------+-------------------+
在maste1中指定master2为主库:
>stop slave;
>change master to master_host='192.168.0.111',master_user='sync',master_password='syncpwd',master_log_file='mysql-bin.000002',master_log_pos=468;
>flush privileges;
在maste2中指定master1为主库:
>stop slave;
>change master to master_host='192.168.0.110',master_user='sync',master_password='syncpwd',master_log_file='mysql-bin.000004',master_log_pos=599;
>flush privileges;
MySQL双主配置完成,验证配置成功:
>show slave status\G #master1中显示的信息
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.111
Master_User: sync
Master_Port: 3306
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
>show slave status\G #master2中显示的信息
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.110
Master_User: sync
Master_Port: 3306
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
在任一服务器中创建数据库表或写入数据都可以在另一服务器中看到
3.Keepalived高可用配置
master1和master2中已经正确安装Keepalived,并指定VIP:192.168.0.250
,且keepalived可正常漂移
master1修改keepalived.conf,新增配置:
>vim /etc/keepalived/keepalived.conf
virtual_server 192.168.0.250 3306 {
delay_loop 3
lb_algo wrr
lb_kind DR
persistence_timeout 60
protocol TCP
real_server 192.168.0.110 3306 {
weight 3
notify_down /usr/local/mysql/chk_mysql.sh
TCP_CHECK {
connect_timeout 10
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
}
chk_mysql.sh内容
>vim /usr/local/mysql/chk_mysql.sh
#!/bin/bash
pkill keepalived
sleep 30
/etc/init.d/keeplived start
master2修改keepalived.conf,新增配置:
>vim /etc/keepalived/keepalived.conf
virtual_server 192.168.0.250 3306 {
delay_loop 3
lb_algo wrr
lb_kind DR
persistence_timeout 60
protocol TCP
real_server 192.168.0.111 3306 {
weight 3
notify_down /usr/local/mysql/chk_mysql.sh
TCP_CHECK {
connect_timeout 10
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
}
chk_mysql.sh内容
>vim /usr/local/mysql/chk_mysql.sh
#!/bin/bash
pkill keepalived
sleep 30
/etc/init.d/keeplived start
4.测试
交叉验证,重启MySQL服务器后需要手动启动Keepalived