最近在做MySQL数据库的双向主从，了解到keepalived能够自动判断并切换到可用数据库，自己试了一下，整理出文档来。 先声明一下环境 iptables开启3306端口或者关掉，关闭selinux MySQL-01：192.168.204.138 MySQL-02：192.168.204.139 VIP：192.168.204.200#w
最近在做MySQL数据库的双向主从，了解到keepalived能够自动判断并切换到可用数据库，自己试了一下，整理出文档来。
先声明一下环境
iptables开启3306端口或者关掉，关闭selinux
MySQL-01：192.168.204.138
MySQL-02：192.168.204.139
VIP：192.168.204.200 #web服务器连接的ip，自己可以使用工具连接试一下。
MySQL的安装过程就略过了，根据个人情况自己安装即可。
1.修改数据库的配置文件/etc/my.cnf：
1.1修改MySQL-01的数据库文件,在[mysql]下添加如下内容
server_id = 1log_bin = mysql-bin
1.2修改MySQL-02的数据库文件,在[mysql]下添加如下内容
server_id = 2log_bin = mysql-bin
2.搭建单向主从
2.1在MySQL-01上
2.1.1操作授权
mysql -u root -p #输入密码mysql> grant replication slave on *.* to 'slave'@'192.168.204.139' identified by '123456';mysql> flush privileges;
2.1.2数据传递给MySQL-02
mysql -u root -p #输入密码flush tables with read lock; #锁表操作mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000012 | 120 | | | |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)mysqldump -u root -p --all-databases > mysqldump.sqlmysql -u root -pmysql> unlock tables;mysql> quit scp myqsldump.sql 192.168.204.139:/root/
2.2在MySQL-02上操作
2.2.1恢复数据库数据
mysql -u root -p < mysqldump.sql
2.2.2建立主从同步
mysql -u root -pmysql> change master to master_host='192.168.204.138',master_user='slave',master_password='123456',master_log_file='mysql-bin.000012',master_log_pos=120，master_port=3306;start slave；
检查是否成功
show slave status\G；
Slave_IO_Running: YesSlave_SQL_Running: Yes
都为Yes表示同步成功。
注：master_port=3306,默认时不需要加，但是修改过端口的则需要该选项
3.搭建互为主从
3.1在MySQl-01上操作
3.1.1用户授权
mysql -u root -p123456mysql> grant replicatio<b>本文来源gao@!dai!ma.com搞$$代^@码!网</b>n slave on *.* to 'slave'@'192.168.204.138' identified by '123456'; //建立权限mysql> flush privileges;
注：因为做的是所有库的主从，所以在MySQL-01上的操作会同步到MySQL-02上
注2：假如你是线上环境，主库在不断的写数据，建议锁一下表，最好找个夜深人静的时候做吧
3.2在MySQL-02上的操作
mysql -u root -pmysql> show master status\G;+------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000009 | 120 | | | |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)
3.1.2在MySQL-01上操作授权
mysql> change master to master_host='192.168.204.138',master_user='slave',master_password='123456',master_log_file='mysql-bin.000009',master_log_pos=120;mysql> start slave;
检查是否成功
show slave status\G；
Slave_IO_Running: YesSlave_SQL_Running: Yes
表示成功。
此，mysql的双向主从同步已完成
4.keepalived的搭建
4.1在两台服务器依次做以下操作
a.安装keepalived
wget http://www.keepalived.org/software/keepalived-1.2.15.tar.gztar zxvf keepalived-1.2.15.tar.gzcd keepalived-1.2.15./configure --prefix=/usr/local/keepalivedmake && make install
假如编译过程出现错误，请安装 gcc，openssl，openssl-devel
b.复制相关文件
cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/mkdir /etc/keepalived/cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
4.2修改配置文件
请先将/etc/keepalived/keepalived.conf文件清空
4.2.1在MySQL-01上操作
将一下内容复制进去
! Configuration File for keepalived global_defs { notification_email { [email protected] } notification_email_from [email protected] smtp_server 127.0.0.1 smtp_connect_timeout 30 router_id MySQL-ha }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 { 192.168.204.222 }}virtual_server 192.168.204.222 3306 { delay_loop 6 lb_algo wrr lb_kind DR persistence_timeout 50 protocol TCP real_server 192.168.204.138 3306 { weight 3 notify_down /var/lib/mysql/killkeepalived.sh TCP_CHECK { connect_timeout 10 nb_get_retry 3 delay_before_retry 3 connect_port 3306 } }}
4.2.2在MySQL-02上操作
vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived global_defs { notification_email { [email protected] } notification_email_from [email protected] smtp_server 127.0.0.1 smtp_connect_timeout 30 router_id MySQL-ha }vrrp_instance VI_1 { state backup interface eth0 virtual_router_id 51 priority 100 advert_int 1 nopreempt authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.204.222 }}virtual_server 192.168.204.222 3306 { delay_loop 6 lb_algo wrr lb_kind DR persistence_timeout 50 protocol TCP real_server 192.168.204.139 3306 { weight 3 notify_down /var/lib/mysql/killkeepalived.sh TCP_CHECK { connect_timeout 10 nb_get_retry 3 delay_before_retry 3 connect_port 3306 } }}
4.3在两台服务器做以下操作
vim /var/lib/mysql/killkeepalived.sh #!/bin/sh pkill keepalived chmod +x /var/lib/mysql/killkeepalived.sh
4.4此时先不要启动keepalived，因为我们要观察日志
5.测试是否可用
创建一个新的用户
mysql> grant all privileges on *.* to 'test'@'%' identified by '123456';mysql> flush privileges;
5.1在MySQL-01上多开一个终端，tailf /var/log/messages,然后再另一个终端启动keepalived服务service keepalived start。日志信息如下
650) this.width=650;” src=”https://img.gaodaima.com/d/file/2021/11/10/e42b58264dd52dc60e466ab8b5e865aa.jpg” title=”mysqk-01.png” alt=”wKiom1Va82yQAiAkAAYgchBcbco747.jpg” />
5.2在MySQL-02同样打开一个新的终端查看日志信息,然后启动keepalived
650) this.width=650;” src=”https://img.gaodaima.com/d/file/2021/11/10/e9f84dd2001a9552ac50746d2a6d23cd.jpg” title=”mysql-02.png” alt=”wKiom1Va9kWjSchKAAhnGfPuAJE019.jpg” />
5.3在MySQL-01上手动停掉MySQL服务。同时得到日志信息
650) this.width=650;” src=”https://img.gaodaima.com/d/file/2021/11/10/3a5a882da2bd85ea68136752c8270310.jpg” title=”mysql-01.png” alt=”wKiom1Va99ThHQytAALtbXucftM408.jpg” /> 连接3306端口失败，准备移除虚拟ip，然后停掉keepalived服务。最后虚拟ip地址移除。
MySQL-02日志信息没啥变化！话说两边启动keepalived的日志信息差不多是一样的。
此时可以使用连接MySQL的工具测试一下，虚拟ip地址还是可用的。
5.4假设MySQL-01恢复此时还应该启动keepalived服务。
转载请注明原文链接：MySQL之双向主从加keepalived高可用
