MYSQL高可用技术方案实施手册
软件业务部技术支持室
MySQL HA目前有多种解决方案,比如heartbeat、drbd、mmm、共享存储,
但是它们各有优缺点。heartbeat、drbd配置较为复杂,需要自己写脚本才能实现
MySQL自动切换[z1] ;前期已经预演过此技术,稳定性不高,对于mmm,生产环境
中很少有人用,且mmm 管理端需要单独运行一台服务器上,要是想实现高可用,
就得对mmm管理端做HA,这样无疑又增加了硬件开支;对于共享存储,个人
觉得MySQL数据还是放在本地较为安全,存储设备毕竟存在单点隐患,而且需
要额外购置存储,增加成本[z2] 。
使用MySQL双master+keepalived是一种非常好的解决方案,在MySQL HA环境中,MySQL互为主从关系,这样就保证了两台MySQL数据的一致性,然后用keepalived实现虚拟IP,通过keepalived自带的服务监控功能来实现MySQL故障时自动切换[z3] 。
此方案将是软件技术研究部后期在HMS网管重点考虑的方案,譔方案经济、安全、可靠,并能实现数据库无缝切换,高可用性得到很好展现,给技术支持室实施项目带来方便,下面给出详细安装指导,安装时请按照以下步骤进行。
Ø OS版本:RedHat 5.4 (64位)
Ø MySQL版本:MySQL-5.5.20
Ø Keepalived版本:1.2.2
IP地址分配如下:
计算机名 | 网卡 | IP地址 |
主机: MySQL1 | Eth2 Eth3(心跳) | 181.181.181.196/255.255.255.0 172.16.1.1/255.255.255.0 |
备机: MySQL2 | Eth2 Eth3(心跳) | 181.181.181.197/255.255.255.0 172.16.1.2/255.255.255.0 |
浮动IP地址 | 181.181.181.198/255.255.255.0 |
一、 分别在主备机安装MySQL
在主备机节点上安装mysql数据库
ftp上传mysql软件到服务器root目录下
1) 解压缩源码包
[root@mysql1 ~]# tar -xvf Mysql-5.5.20-1.rhel5.x86_64.tar
rpm –ivh Mysql-server-5.5.20-1.rhel5.x86_64.rpm
rpm –ivh Mysql-client-5.5.20-1.rhel5.x86_64.rpm
2) 启动mysql
[root@mysql1 mysql-5.0.22]#service mysql start
3) 在mysql2服务器上安装mysql如同上面的步骤
4) 分别登陆mysql数据库测试操作
mysql -u root -p
(执行后提示输入密码,首次登录默认密码为空)
修改密码命令
mysqladmin –u root password ‘root’
二、 MySQL master-master配置(实现主备机数据库完全同步)
1) 修改MySQL配置文件
两台MySQL均如要开启binlog日志功能,开启方法:在MySQL配置文件[MySQLd]段中
加上log-bin=MySQL-bin选项
两台MySQL的server-ID不能一样,默认情况下两台MySQL的serverID都是1,需将
其中一台修改为2即可
Ø Mysql1机器:
cp /usr/share/mysql/ my-medium.cnf /etc/my.cnf
vi /etc/my.cnf
log-bin= mysql-bin
server-id=1
#for rep1
auto-increment-increment=2
auto-increment-offset=1
binlog-do-db= test
binlog-ignore-db=mysql #避免同步 mysql 用户配置,以免不必要的麻烦
Ø mysql2机器:
cp /usr/share/mysql/ my-medium.cnf /etc/my.cnf
vi /etc/my.cnf
log-bin= mysql-bin
server-id=2
#for rep1
auto-increment-increment=2
auto-increment-offset=2
binlog-do-db= test
binlog-ignore-db=mysql #避免同步 mysql 用户配置,以免不必要的麻烦
Ø 分别启动mysql服务
2) 将181.181.181.196设为181.181.181.197的主服务器
在181.181.181.196上新建授权用户
MySQL> GRANT REPLICATION SLAVE,REPLICATION CLIENT,RELOAD,SUPER ON *.* to 'sync'@'181.181.181.197' identified by 'sync';
Query OK, 0 rows affected (0.00 sec)
MySQL> show master status;
File: mysql-bin.000005
Position: 294
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
在181.181.181.197上将181.181.181.196设为自己的主服务器
MySQL>
change master to master_host='181.181.181.196',master_user='sync',master_password='sync',master_log_file='MySQL-bin.000005',master_log_pos=294;
Query OK, 0 rows affected (0.05 sec)
MySQL> start slave;
Query OK, 0 rows affected (0.00 sec)
MySQL> show slave status\G
******************************************************
Slave_IO_State: Connecting to master
Master_Host: 181.181.181.196
Master_User: sync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 294
Relay_Log_File: localhost-relay-bin.000005
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: Connecting
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: 0
Relay_Log_Space: 107
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1045
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
3) 将181.181.181.197设为181.181.181.196的主服务器
在181.181.181.197上新建授权用户
MySQL> GRANT REPLICATION SLAVE,REPLICATION CLIENT,RELOAD,SUPER ON *.* to 'sync'@'181.181.181.196' identified by 'sync';
Query OK, 0 rows affected (0.00 sec)
MySQL> show master status;
File: mysql-bin.000002
Position: 294
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)1 row in set (0.00 sec)
在181.181.181.196上,将181.181.181.197设为自己的主服务器
MySQL> change master to master_host='181.181.181.197',master_user='sync',master_password='sync',master_log_file='MySQL-bin.000002',master_log_pos=294;
Query OK, 0 rows affected (0.05 sec)
MySQL> start slave;
Query OK, 0 rows affected (0.00 sec)
MySQL> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 181.181.181.197
Master_User: sync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: MySQL-bin.000001
Read_Master_Log_Pos: 417
Relay_Log_File: mysql1-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: MySQL-bin.000001
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: 417
Relay_Log_Space: 107
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1236
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
1 row in set (0.00 sec)
4) MySQL同步测试
在其中一台机器上新建表并插入数据,备机将同步数据。
Ø create table data(name VARCHAR(20), address VARCHAR(50), phone VARCHAR(20));
Query OK, 0 rows affected (0.11 sec)
Ø 在表中添加一条记录,用insert into语句:
insert into data(name,address,phone)values('wang','guangzhou3','13800000009');
如果备机也存在数据,表示双机主主同步功能完好。
三、 keepalived安装及配置
1) 181.181.181.196服务器上keepalived安装及配置
Ø 安装keepalived
#tar –zxvf keepalived-1.2.2.tar.gz
#cd keepalived-1.2.2
#./configure --prefix=/
#make
#make install
Ø 配置keepalived
我们自己在新建一个配置文件,默认情况下keepalived启动时会去
/etc/keepalived目录下找配置文件
#vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
router_id comba
}
vrrp_script check_run {
script "/root/keepalived_check_mysql.sh"
interval 5
vrrp_sync_group VG1 {
group {
VI_1
}
}
vrrp_instance VI_1 {
state MASTER
interface eth0
virtual_router_id 51
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass comba
}
track_script {
check_run
}
virtual_ipaddress {
179.179.179.177
}
}
2) 181.181.181.196服务器上keepalived安装及配置
Ø 安装keepalived
#tar –zxvf keepalived-1.2.2.tar.gz
#cd keepalived-1.2.2
#./configure --prefix=/
#make
#make install
Ø 配置keepalived
我们自己在新建一个配置文件,默认情况下keepalived启动时会去
/etc/keepalived目录下找配置文件
#vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
router_id comba
}
vrrp_script check_run {
script "/root/keepalived_check_mysql.sh"
interval 5
vrrp_sync_group VG1 {
group {
VI_1
}
}
vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 51
priority 80
advert_int 1
authentication {
auth_type PASS
auth_pass comba
}
track_script {
check_run
}
virtual_ipaddress {
179.179.179.177
}
}
3) 监控脚本配置(主备机)
#vim /root/keepalived_check_mysql.sh
#!/bin/bash
MYSQL=/usr/bin/mysql
MYSQL_HOST=179.179.179.179
MYSQL_USER=root
MYSQL_PASSWORD=123456
CHECK_TIME=3
#mysql is working MYSQL_OK is 1, mysql down MYSQL_OK is 0
MYSQL_OK=1
function check_mysql_helth (){
$MYSQL -h $MYSQL_HOST -u $MYSQL_USER -p${MYSQL_PASSWORD} -e "show status;" >/dev/null 2>&1
if [ $? = 0 ] ;then
MYSQL_OK=1
else
MYSQL_OK=0
fi
return $MYSQL_OK
}
while [ $CHECK_TIME -ne 0 ]
do
let "CHECK_TIME -= 1"
check_mysql_helth
if [ $MYSQL_OK = 1 ] ; then
CHECK_TIME=0
exit 0
fi
if [ $MYSQL_OK -eq 0 ] && [ $CHECK_TIME -eq 0 ]
then
/etc/init.d/keepalived stop
exit 1
fi
sleep 1
done
4) 主备机启动keepalived
service keepalived start
service keepalived stop 为停止命令
停止主机MySQL服务,看keepalived健康检查程序是否会触发我们编写的脚本,
浮动IP是否漂移至备机
四、 测试
MySQL远程登录测试
我们找一台安装有MySQL客户端的windows,然后登录VIP,看是否能登录,在登录之两台MySQL服务器都要授权允许从远程登录
Ø keepalived故障转移测试
※在windows客户端一直去ping VIP,然后关闭181.181.181.196上的keepalived,正常情况下VIP就会切换到181.181.181.197上面去
※开启181.181.181.196上的keepalived,关闭181.181.181.197上的keepalived,看
是否能自动切换,正常情况下VIP又会属于181.181.181.196
注:keepalived切换速度还是非常块的,整个切换过程只需1-3秒
Ø MySQL故障转移测试
※在181.181.181.196上关闭MySQL服务,看VIP是否会切换到181.181.181.197
上
※开启181.181.181.196上的MySQL和keepalived,然后关闭181.181.181.197上的
MySQL,看VIP是否会切换到181.181.181.196上
下面是用windows客户端连接的MySQL的VIP,在切换时我执行了一个MySQL
查询命令,从执行show databases到显示出结果时间为3-5秒(大家可以看到上
面有个错误提示,不过不用担心,因为我们的keepalived切换大概为3秒左右,
这3秒左右VIP是谁都不属于的)
实验环境测试全部通过完。。。。。。。。。。。。。。。。。。。。