Mysql5.5 双机热备
实现计划
装置两台 Mysql
装置 Mysql5.5
sudo apt-get update
apt-get install aptitude
aptitude install mysql-server-5.5
或
sudo apt-cache search mariadb-server
apt-get install -y mariadb-server-5.5卸载
sudo apt-get remove mysql-*
dpkg -l |grep ^rc|awk '{print $2}' |sudo xargs dpkg -P配置权限
vim /etc/mysql/my.cnf
#bind-address = 127.0.0.1
mysql -u root -p
grant all on *.* to root@'%' identified by 'root' with grant option;
flush privileges;配置两台 Mysql 主主同步
配置节点 1
vim /etc/mysql/my.cnf
server-id = 1 #节点 ID
log_bin = mysql-bin.log #日志
binlog_format = "ROW" #日志格局
auto_increment_increment = 2 #自增 ID 距离(= 节点数，避免 ID 抵触)
auto_increment_offset = 1 #自增 ID 起始值(节点 ID)
binlog_ignore_db=mysql #不同步的数据库
binlog_ignore_db=information_schema
binlog_ignore_db=performance_schema重启 mysql
service mysql restart
mysql -u root -p记录节点 1 的 binlog 日志地位
show master status;
mysql-bin.000001 245 mysql,information_schema,performance_schema配置节点 2
vim /etc/mysql/my.cnf
server-id = 2
log_bin = mysql-bin.log
relay_log = mysql-relay-bin.log #中继日志
log_slave_updates = ON #中继日志执行后，变动计入日志
read_only = 0
binlog_format = "ROW"
auto_increment_increment = 2
auto_increment_offset = 2
binlog_ignore_db=mysql
binlog_ignore_db=information_schema
binlog_ignore_db=performance_schema
replicate_ignore_db=mysql
replicate_ignore_db=information_schema
replicate_ignore_db=performance_schema配置主从
mysql -u root -p
CHANGE MASTER TO
MASTER_HOST='192.168.1.21',
MASTER_USER='root',
MASTER_PASSWORD='root',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=245;
#开启同步
start slave
#查看同步状态 Slave_IO_Running 和 Slave_SQL_Running 须要均为 Yes
show slave status; 记录节点 2 的 binlog 日志地位
show master status;
mysql-bin.000001 1029 mysql,information_schema,performance_schema配置主主（节点 1）
vim /etc/mysql/my.cnf
relay_log = mysql-relay-bin.log
log_slave_updates = ON
read_only = 0
replicate_ignore_db=mysql
replicate_ignore_db=information_schema
replicate_ignore_db=performance_schema开启同步
mysql -u root -p
CHANGE MASTER TO
MASTER_HOST='192.168.1.20',
MASTER_USER='root',
MASTER_PASSWORD='root',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=1029;
#开启同步
start slave
#查看同步状态 Slave_IO_Running 和 Slave_SQL_Running 须要均为 Yes
show slave status; 异样解决
Could not initialize master info structure, more error messages can be found in the MySQL error log
解决：reset slave装置配置 Keepalived
装置 Keepalived
# 依赖
sudo apt-get install -y libssl-dev
sudo apt-get install -y openssl
sudo apt-get install -y libpopt-dev
sudo apt-get install -y libnl-dev libnl-3-dev libnl-genl-3.dev
apt-get install daemon
apt-get install libc-dev
apt-get install libnfnetlink-dev
apt-get install libnl-genl-3.dev
#装置
apt-get install keepalived
#编译装置
cd /usr/local
wget https://www.keepalived.org/software/keepalived-2.2.2.tar.gz
tar -zxvf keepalived-2.2.2.tar.gz
mv keepalived-2.2.2 keepalived
./configure --prefix=/usr/local/keepalived
sudo make && make install
#开启日志
sudo vim /etc/rsyslog.d/50-default.conf
*.=info;*.=notice;*.=warn;\
auth,authpriv.none;\
cron,daemon.none;\
mail,news.none -/var/log/messages
sudo service rsyslog restart
tail -f /var/log/messages
sudo mkdir /etc/sysconfig
sudo cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
sudo cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/
sudo cp /usr/local/keepalived/sbin/keepalived /sbin/
sudo mkdir /etc/keepalived
sudo cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/配置节点信息
节点 1 192.168.1.21
vim /etc/keepalived/keepalived.conf
global_defs {router_id MYSQL_HA #以后节点名}
vrrp_instance VI_1 {
state BACKUP #两台配置节点均为 BACKUP
interface eth0 #绑定虚构 IP 的网络接口
virtual_router_id 51 #VRRP 组名，两个节点的设置必须一样，以指明各个节点属于同一 VRRP 组
priority 101 #节点的优先级, 另一台优先级改低一点
advert_int 1 #组播信息发送距离，两个节点设置必须一样
nopreempt #不抢占，只在优先级高的机器上设置即可，优先级低的机器不设置
authentication { #设置验证信息，两个节点必须统一
auth_type PASS
auth_pass 123456
}
virtual_ipaddress { #指定虚构 IP, 两个节点设置必须一样
192.168.1.111
}
}
virtual_server 192.168.1.111 3306 {#linux 虚构服务器 (LVS) 配置
delay_loop 2 #每个 2 秒查看一次 real_server 状态
lb_algo wrr #LVS 调度算法,rr|wrr|lc|wlc|lblc|sh|dh
lb_kind DR #LVS 集群模式 ,NAT|DR|TUN
persistence_timeout 60 #会话放弃工夫
protocol TCP #应用的协定是 TCP 还是 UDP
real_server 192.168.1.21 3306 {
weight 3 #权重
notify_down /usr/local/bin/mysql.sh #检测到服务 down 后执行的脚本
TCP_CHECK {
connect_timeout 10 #连贯超时工夫
nb_get_retry 3 #重连次数
delay_before_retry 3 #重连间隔时间
connect_port 3306 #健康检查端口
}
}
}节点 2 192.168.1.20
vim /etc/keepalived/keepalived.conf
global_defs {router_id MYSQL_HA #以后节点名}
vrrp_instance VI_1 {
state BACKUP #两台配置节点均为 BACKUP
interface eth0 #绑定虚构 IP 的网络接口
virtual_router_id 51 #VRRP 组名，两个节点的设置必须一样，以指明各个节点属于同一 VRRP 组
priority 100 #节点的优先级, 另一台优先级改低一点
advert_int 1 #组播信息发送距离，两个节点设置必须一样
nopreempt #不抢占，只在优先级高的机器上设置即可，优先级低的机器不设置
authentication { #设置验证信息，两个节点必须统一
auth_type PASS
auth_pass 123456
}
virtual_ipaddress { #指定虚构 IP, 两个节点设置必须一样
192.168.1.111
}
}
virtual_server 192.168.1.111 3306 {#linux 虚构服务器 (LVS) 配置
delay_loop 2 #每个 2 秒查看一次 real_server 状态
lb_algo wrr #LVS 调度算法,rr|wrr|lc|wlc|lblc|sh|dh
lb_kind DR #LVS 集群模式 ,NAT|DR|TUN
persistence_timeout 60 #会话放弃工夫
protocol TCP #应用的协定是 TCP 还是 UDP
real_server 192.168.1.20 3306 {
weight 3 #权重
notify_down /usr/local/bin/mysql.sh #检测到服务 down 后执行的脚本
TCP_CHECK {
connect_timeout 10 #连贯超时工夫
nb_get_retry 3 #重连次数
delay_before_retry 3 #重连间隔时间
connect_port 3306 #健康检查端口
}
}
}编写异样解决脚本
vim /usr/local/bin/mysql.sh
#!/bin/sh
killall keepalived调配权限
chmod +x /usr/local/bin/mysql.sh
测试
重启 keepalived
service keepalived restart查看日志
tail -f /var/log/messages查看虚构 IP
ip addr #或 ip a 或 ifconfig
#主节点会有虚构 IP
eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 52:54:9e:17:53:e5 brd ff:ff:ff:ff:ff:ff
inet 192.168.1.21/24 brd 192.168.1.255 scope global eth0
valid_lft forever preferred_lft forever
inet 192.168.1.111/32 scope global eth0
valid_lft forever preferred_lft forever敞开主节点的 mysql 服务
service mysql stop日志信息
# 主节点
Aug 10 15:00:30 i-7jaope92 Keepalived_healthcheckers[4949]: TCP connection to [192.168.1.20]:3306 failed !!!
Aug 10 15:00:30 i-7jaope92 Keepalived_healthcheckers[4949]: Removing service [192.168.1.20]:3306 from VS [192.168.1.111]:3306
Aug 10 15:00:30 i-7jaope92 Keepalived_healthcheckers[4949]: Executing [/usr/local/bin/mysql.sh] for service [192.168.1.20]:3306 in VS [192.168.1.111]:3306
Aug 10 15:00:30 i-7jaope92 Keepalived_healthcheckers[4949]: Lost quorum 1-0=1 > 0 for VS [192.168.1.111]:3306
Aug 10 15:00:30 i-7jaope92 Keepalived_vrrp[4950]: VRRP_Instance(VI_1) sending 0 priority
Aug 10 15:00:30 i-7jaope92 kernel: [100918.976041] IPVS: __ip_vs_del_service: enter
#从节点
Aug 10 15:00:31 i-6gxo6kx7 Keepalived_vrrp[718]: VRRP_Instance(VI_1) Transition to MASTER STATE
Aug 10 15:00:32 i-6gxo6kx7 Keepalived_vrrp[718]: VRRP_Instance(VI_1) Entering MASTER STATE虚构 IP 从主节点漂移到从节点
ip a
eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 52:54:9e:e7:26:5c brd ff:ff:ff:ff:ff:ff
inet 192.168.1.20/24 brd 192.168.1.255 scope global eth0
valid_lft forever preferred_lft forever
inet 192.168.1.111/32 scope global eth0
valid_lft forever preferred_lft foreverMysql 连贯测试
mysql -h 192.168.1.111 -u root -p参考资料：
1.Mysql 主主复制
2.mysql5.5 三种 binlog 日志格局
3. 高可用的 Mysql 双机热备(Mysql_HA)
4.Ubuntu 装置 keepalived
5. 五大常见的 MySQL 高可用计划