正文
编译安装keepalived，实现双主mysql高可用
小程序：扫一扫查出行【扫一扫了解最新限行尾号】
复制小程序
安装keepalived
1、官网下载源码包，解压
# wget http://www.keepalived.org/software/keepalived-1.1.20.tar.gz
# tar xvf keepalived-1.1.20.tar.gz
2、编译安装，这里需要指定一下安装路径
# cd keepalived-1.1.20/
# ./configure --prefix=/usr/local/keepalived
# make && make install
3、拷贝配置文件和启动脚本到相应的目录
# mkdir /etc/keepalived
# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf
# cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/
# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
4、设置开机启动
# chkconfig keepalived on
配置mysql双主
所谓双主，即两个节点互为主从，各自把对方设为master，两个节点都需要开启binlog和relaylog。
两个节点server_id必须不一样。
由于两个节点都可写，为了保证自增主键ID不冲突，需要设置 auto_increment_offset 和 auto_increment_increment。
主从复制过程：
a、master将改变记录到二进制日志（binlog）中；
b、slave将master的binlog拷贝到它的中继日志（relaylog）；
c、slave重做中继日志中的事件，将改变反映到它自己的数据库。
① slave将master的binary log拷贝到它自己的中继日志。
② I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。
③ SQL线程从中继日志读取事件，并重放其中的事件而更新slave的数据，使其与master中的数据一致。
1、修改节点一的配置文件
# vim /etc/my.cnf
server-id=1
datadir=/nh/mysql3307/data
log-bin=/nh/mysql3307/data
log-bin-index=/nh/mysql3307/data
relay_log=relay-log
auto_increment_offset=1
auto_increment_increment=2
innodb_file_per_table=1
# /etc/init.d/mysqld start
2、修改配置节点二的配置文件
# vim /etc/my.cnf
server-id=2
datadir=/nh/mysql3307/data
log-bin=/nh/mysql3307/data
log-bin-index=/nh/mysql3307/data
relay_log=relay-log
auto_increment_offset=2
auto_increment_increment=2
innodb_file_per_table=1
# /etc/init.d/mysqld start
3、登录节点一的mysql，设置有复制权限的账号
# mysql -uroot -S mysql-3307.sock
mysql> grant replication slave,replication client on *.* to repl@'192.168.%' identified by 'Fl0wer926';
mysql> flush privileges;
查看当前正在使用的binlog文件和所在位置
mysql> show master status;
+-------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------+----------+--------------+------------------+
| data.00005 | 106 | | |
+-------------+----------+--------------+------------------+
登录节点二的mysql，将节点一设置为其master
mysql> change master to master_host='192.168.1.101',MASTER_PORT = 3307,master_user='repl',master_password='Fl0wer926',master_log_file='data.000005',master_log_pos=106;
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.101
Master_User: repl
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: data.000010
Read_Master_Log_Pos: 106
Relay_Log_File: relay-log.000021
Relay_Log_Pos: 246
Relay_Master_Log_File: data.000010
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: 106
Relay_Log_Space: 535
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:
1 row in set (0.00 sec)
mysql> show processlist;
+-------+-------------+---------------------+------+-------------+-------+-----------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+-------------+---------------------+------+-------------+-------+-----------------------------------------------------------------------+------------------+
| 7 | system user | | NULL | Connect | 43454 | Waiting for master to send event | NULL |
| 8 | system user | | NULL | Connect | 36323 | Has read all relay log; waiting for the slave I/O thread to update it | NULL |
| 33612 | repl | 192.168.1.101:57914 | NULL | Binlog Dump | 967 | Has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 34514 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+-------+-------------+---------------------+------+-------------+-------+-----------------------------------------------------------------------+------------------+
交换两个节点的角色重复一次以上操作，即可实现主主复制。
4、修改节点一的keepalived配置文件
# vim /etc/keepalived/keepalived.conf
global_defs {
router_id MYSQL_MM
}
vrrp_script chk_mysqld3307 {
script "nc 192.168.1.101 3307"
interval 1
weight -10
fall 2
rise 1
}
vrrp_instance VI_1 {
state MASTER
interface bond0
virtual_router_id 51
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1234
}
virtual_ipaddress {
192.168.6.12
}
track_script {
chk_mysqld3307
}
}
# /etc/init.d/keepalived start
5、修改节点二的keepalived配置文件
# vim /etc/keepalived/keepalived.conf
global_defs {
router_id MYSQL_MM
}
vrrp_script chk_mysqld3307 {
script "nc 192.168.1.102 3307"
interval 1
weight -10
fall 2
rise 1
}
vrrp_instance VI_1 {
state BACKUP
interface bond0
virtual_router_id 51
priority 95
advert_int 1
authentication {
auth_type PASS
auth_pass 1234
}
virtual_ipaddress {
192.168.6.12
}
track_script {
chk_mysqld3307
}
}
# /etc/init.d/keepalived start
至此，通过keepalived实现mysql双主高可用就完成了，然后可以尝试停止vip所在节点测试vip是否会自动漂移，然后再把节点启动看vip是否会被抢占回来。
