环境：mysql-master1：192.168.0.103
mysql-master2：192.168.0.104
mysql-VIP：192.168.0.109
mysql version:5.7.22
keepalived version:1.2.1 目标使用keepalived的VIP实现mysql的高可用
1.两个服务器上的server id不要一样即可
[root@rhel64-64bit mysql]# cat /etc/my.cnf|grep -iv -e '^#' -e '^$'
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id=3
log-bin=mybinlogg
2.103服务器上：
create user 'repli'@'%' identified by '123456789';
grant replication slave on *.* to 'repli'@'%'
mysql> show master status; +------------------+----------+--------------+------------------+----------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+----------------------------------------+ | mybinlogg.000007 | 904 | | | 61b93c79-3cbf-11e8-93cc-54e1adb62550:1 | +------------------+----------+--------------+------------------+----------------------------------------+ 1 row in set (0.00 sec) mysql>
104服务器上把103设为master：
change master to master_host='192.168.0.103',master_user='repli',master_password='123456789',MASTER_LOG_FILE='mybinlogg.000007', MASTER_LOG_POS=904;
start slave；
show slave status；报错:
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.0.103
Master_User: repli
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mybinlogg.000007
Read_Master_Log_Pos: 904
Relay_Log_File: rhel64-64bit-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mybinlogg.000007
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Last_IO_Errno: 1593
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
解决办法：/var/lib/mysql/auto.cnf 里面的uuid和103服务器一样----》mv auto.cnf auto.cnf.bak 重启mysql服务自动生成新的uuid
3.在104服务器上：
create user 'repli'@'%' identified by '123456789';
grant replication slave on *.* to 'repli'@'%'；
mysql> show master status; +------------------+----------+--------------+------------------+----------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+----------------------------------------+ | mybinlogg.000011 | 597 | | | 61b93c79-3cbf-11e8-93cc-54e1adb62550:1 | +------------------+----------+--------------+------------------+----------------------------------------+ 1 row in set (0.00 sec)
103服务器上把104设为master：
change master to master_host='192.168.0.104',master_user='repli',master_password='123456789',MASTER_LOG_FILE='mybinlogg.000011', MASTER_LOG_POS=597;
start slave；
show slave status；
4.103服务器创建一个新的root2，会自动同步到104服务器此时
create user 'root2'@'%' identified by 'password2';
grant all privileges on *.* to 'root2';
flush privileges;
5.两个服务器上安装keepalived：yum install -y keepalived
6.103上的/etc/keepalived/keepalived.conf文件的配置：
vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 51
priority 100
advert_int 1
nopreempt ---只在优先级高的设置nopreempt
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.0.109/24
}
}
virtual_server 192.168.0.109 3306 {
delay_loop 2
lb_algo wrr
lb_kind DR
persistence_timeout 60
protocol TCP
real_server 192.168.0.103 3306 {
weight 3
notify_down /usr/share/mysql/monitor.sh ----里面就只有一行pkill keepalived,即检测到3306端口down就kill到keepalived
TCP_CHECK {
connect_timeout 10
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
启动服务service keepalived start
6.104服务器上的keepalived.conf文件配置：同上，只是修改一下
priority 90
real_server 192.168.0.104 3306
启动服务service keepalived start
7.现在vip在103服务器上：
ip addr
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:22:98:4d brd ff:ff:ff:ff:ff:ff
inet 192.168.0.103/24 brd 192.168.0.255 scope global eth0
inet 192.168.0.109/24 scope global secondary eth0
inet6 fe80::20c:29ff:fe22:984d/64 scope link
valid_lft forever preferred_lft forever
1>以vip109连接mysql：
[root@rhel64-64bit Desktop]# mysql -uroot2 -h192.168.0.109 -p Enter password: Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
2>103上停止mysql服务，检查keepalived是否停止：
[root@rhel64-64bit Desktop]# service mysqld stop Stopping mysqld: [ OK ] [root@rhel64-64bit Desktop]# service keepalived status keepalived dead but subsys locked
发现vip已经到了104服务器上了：
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:5c:b2:0e brd ff:ff:ff:ff:ff:ff
inet 192.168.0.104/24 brd 192.168.0.255 scope global eth0
inet 192.168.0.109/24 scope global secondary eth0
inet6 fe80::20c:29ff:fe5c:b20e/64 scope link
valid_lft forever preferred_lft forever
3>启动103服务器上的mysql和keepalived后，再停止104服务器上的mysql，此时的VIP回到了103服务器，切换过程需要几秒,mysql这时会连不上服务器的错误：
mysql> show databases; ERROR 2013 (HY000): Lost connection to MySQL server during query mysql> show databases; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 60 Current database: *** NONE *** +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | | test2 | | wuwei | +--------------------+ 7 rows in set (0.07 sec)