MySQL双主-高可用
单台数据库实例安装
请参考:二进制包安装MySQL
资源规划
主机名 | os 版本 | MySQL 版本 | 主机 IP | MySQL VIP |
db01.lyk.com | centos 6.4 | mysql-5.6.21-linux-glibc2.5-x86_64 | 172.31.30.12 | 172.31.30.222 |
db02.lyk.com | centos 6.4 | mysql-5.6.21-linux-glibc2.5-x86_64 | 172.31.30.11 |
3.修改MySQL配置文件
修改DB01的配置文件:
vi /etc/my.cnf #在[mysqld]添加如下内容# server-id = 100 log-bin = /usr/local/mysql/data/ttpai-bin binlog_format = MIXED #非必需 relay-log = /usr/local/mysql/data/ttpai-relay-bin binlog-ignore-db = mysql binlog-ignore-db = test binlog-ignore-db = information_schema binlog-ignore-db = performance_schema replicate-wild-ignore-table = mysql.% replicate-wild-ignore-table = test.% replicate-wild-ignore-table = information_schema.% replicate-wild-ignore-table = performance_schema.%
修改DB02的配置文件:
vi /etc/my.cnf #在[mysqld]添加如下内容# server-id = 110 log-bin = /usr/local/mysql/data/ttpai-bin binlog_format = MIXED #非必需 relay-log = /usr/local/mysql/data/ttpai-relay-bin binlog-ignore-db = mysql binlog-ignore-db = test binlog-ignore-db = information_schema binlog-ignore-db = performance_schema replicate-wild-ignore-table = mysql.% replicate-wild-ignore-table = test.% replicate-wild-ignore-table = information_schema.% replicate-wild-ignore-table = performance_schema.%
4.手动同步数据库
如果DB01上已经有MySQL数据,那么执行主主互备之前,需要将DB01和DB02上的两个MySQL的数据保持同步,首先在DB01上备份MySQL数据,执行如下SQL:
mysql> FLUSH TABLES WITH READ LOCK;
在不退出终端的情况下(推出锁失效),再开启一个session,直接打包MySQL的数据文件或者mysqldump工具导出:
cd /usr/local/mysql/ tar zcvf data.tar.gz data/
将data.tar.gz 传输到DB02,依次重启DB01和DB02。
其实,可以在不执行READ LOCK语句,直接使用mysqldump语句备份,最起码个人测试是数据不会丢失或者说出现同步异常。使用如下命令:
mysqldump --default-character-set=gbk --opt --triggers -R -E --hex-blob --single-transaction --master-data=2 ttpai > ttpai.sql
其中--master-data=2可以锁定binlog的文件名及坐标。
5.创建授权复制用户
DB01执行:
mysql> grant REPLICATION SLAVE ON *.* TO lyk@'172.31.30.11' IDENTIFIED BY 'lyk123';
DB02执行:
mysql> grant REPLICATION SLAVE ON *.* TO lyk@'172.31.30.12' IDENTIFIED BY 'lyk123';
其中关于坐标的值,可以执行如下SQL得到:
mysql> show master status; +------------------+-----------+--------------+--------------------------------------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+-----------+--------------+--------------------------------------------------+-------------------+ | ttpai-bin.000093 | 502389306 | | mysql,test,information_schema,performance_schema | | +------------------+-----------+--------------+--------------------------------------------------+-------------------+
如果使用mysqldump 可以使用如下命令:
head -n50 ttpai.sql
6.启动互为主从的模式
在DB02上执行:
mysql>CHANGE MASTER TO MASTER_LOG_FILE='ttpai-bin.000050', MASTER_LOG_POS=754861035, MASTER_HOST='172.31.30.12', MASTER_USER='lyk', MASTER_PASSWORD='lyk123'; mysql>start slave;
在DB01上执行:
mysql>CHANGE MASTER TO MASTER_LOG_FILE='ttpai-bin.000050', MASTER_LOG_POS=754861035, MASTER_HOST='172.31.30.12', MASTER_USER='lyk', MASTER_PASSWORD='lyk123'; mysql>start slave;
验证同步结果:
mysql>show slave status\G;
自此Mysql互为主从已完毕。
7.安装lvs+keepalived
yum instal keepalived ipvsadm
8.编辑keepalived配置文件
DB01的配置文件:
vi /etc/keepalived/keepalived.conf global_defs { notification_email { sysadmin@lyk.com } notification_email_from monitor@lyk.com smtp_server 127.0.0.1 smtp_connect_timeout 30 router_id LVS_Mysql } vrrp_instance HA_1 { state BACKUP interface bond0 virtual_router_id 80 priority 100 advert_int 2 nopreempt authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 172.31.30.222 } } virtual_server 172.31.30.222 3306 { delay_loop 2 lb_algo wlc lb_kind DR nat_mask 255.255.255.0 protocol TCP persistence_timeout 60 real_server 172.31.30.12 3306 { weight 1 notify_down /etc/keepalived/mysqlcheck/mysql.sh TCP_CHECK { connect_port 3306 connect_timeout 3 nb_get_retry 2 delay_before_retry 1 } } }
其中需要注意的是:
state BACKUP #DB01和DB02均配置为BACKUP状态
nopreempt #为不强占模式,DB02(slave)不用设置
notify_down #定义监测realserver失败下一步的动作
priority #DB02要比DB01低
interface bond0 #没什么特别,却是我的整个搭建最耗时的痛点,稍后解释
创建notify_down的脚本(DB01和DB02都需创建):
mkdir -p /etc/keepalived/mysqlcheck/ cd /etc/keepalived/mysqlcheck/ vi mysql.sh #!/bin/sh pkill keepalived chmod u+x mysql.sh
其实就是干掉keepalived进程,释放VIP。
DB02的配置文件:
global_defs { notification_email { sysadmin@lyk.com } notification_email_from monitor@lyk.com smtp_server 127.0.0.1 smtp_connect_timeout 30 router_id LVS_Mysql } vrrp_instance HA_1 { state BACKUP interface bond0 virtual_router_id 80 priority 90 advert_int 2 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 172.31.30.222 } } virtual_server 172.31.30.222 3306 { delay_loop 2 lb_algo wlc lb_kind DR nat_mask 255.255.255.0 protocol TCP persistence_timeout 60 real_server 172.31.30.11 3306 { weight 1 notify_down /etc/keepalived/mysqlcheck/mysql.sh TCP_CHECK { connect_port 3306 connect_timeout 3 nb_get_retry 2 delay_before_retry 1 } } }
DB01和DB02的启动keepalived:
/etc/init.d/keepalived start
9.测试
测试主从同步,通过VIP连接:
mysql -h172.31.30.222 -utest -ptest ttpai mysql> show vaiables like 'server_id'; #可以确认那台为master状态 mysql> create table .... #到slave状态的数据库看都是否正常 .....
测试keepalived实现MySQL故障转移:
可以在DB01执行
/etc/init.d/mysqld stop
远程一直执行
show vaiables like 'server_id';
发现会卡1~3s,server_id变为110,即master变为DB02。
即使重启DB01,VIP也不会转移,因为采用的是不抢占模式,直到DB02的服务挂,才会转移VIP。
10:总结
来说下interface bond0的事儿:
一切配置都很顺,但是在测试的时候,发现VIP连接数据库,执行SQL时卡时不卡,在windows下ping VIP或者VIP所在的DB的主机IP,丢包;有没有蛋碎的感觉。开始一头雾水,后来仔细又看了一边lvs DR模式的原理,原来都和MAC地址相关联,而自己的环境网卡做了bond0(mode=0),却在交换机上没做端口聚合,可以认为网卡MAC发生紊乱。
解决:
配置交换机(H3C)
#创建端口聚合组(不同型号,命令不同,叫法不一样) interface Bridge-Aggregation2 description bonding port access vlan 30 interface Bridge-Aggregation3 description bonding port access vlan 30 #将做bonding的网卡加入对应的聚合组 interface GigabitEthernet1/0/3 port access vlan 30 port link-aggregation group 3 ......
一切恢复正常,发生这个事件,不知是自己太低端,还是大家都没遇到,搜资料真没收到,呵呵。
注意:
LVS DR原理
bonding 模式的注意点
转载于:https://blog.51cto.com/lgdvsehome/1591525