MYSQL高可用技术方案实施手册-工程实施.doc

京信通信系统（中国）有限公司 内部文件
MYSQL高可用技术方案实施手册
软件业务部技术支持室
　 MySQL HA目前有多种解决方案，比如heartbeat、drbd、mmm、共享存储，
但是它们各有优缺点。heartbeat、drbd配置较为复杂，需要自己写脚本才能实现
MySQL自动切换需要自己写脚本！
；前期已经预演过此技术，稳定性不高，对于mmm，生产环境
中很少有人用，且mmm 管理端需要单独运行一台服务器上要是想实现高可用，
就得对mmm管理端做HA，这样无疑又增加了硬件开支；对于共享存储，个人
觉得MySQL数据还是放在本地较为安全，存储设备毕竟存在单点隐患，而且需
要额外购置存储，增加成本共享存储存在单点隐患。
。
使用MySQL双master+keepalived是一种非常好的解决方案，在MySQL HA环境中，MySQL互为主从关系，这样就保证了两台MySQL数据的一致性，然后用keepalived实现虚拟IP，通过keepalived自带的服务监控功能来实现MySQL故障时自动切换keeplived实现虚拟IP和服务监控自动切换。
。
此方案将是软件技术研究部后期在HMS网管重点考虑的方案，譔方案经济、安全、可靠，并能实现数据库无缝切换，高可用性得到很好展现，给技术支持室实施项目带来方便，下面给出详细安装指导，安装时请按照以下步骤进行。
Ø OS版本：RedHat 5.4 (64位)
Ø MySQL版本：MySQL-5.5.20
Ø Keepalived版本：1.2.2
keepalived-1.2.2.tar.gz
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-f /etc/f
vi /etc/f
log-bin= mysql-bin
server-id=1
binlog-do-db= test
Ø mysql2机器:
cp /usr/share/mysql/ my-f /etc/f
vi /etc/f
log-bin= mysql-bin
server-id=2
binlog-do-db= test
Ø 分别启动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
}
trackscript {
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是谁都不属于的）
实验环境测试全部通过完。。。。。。。。。。。。。。。。。。。。

2012-2-23