利用keepalived构建高可用MySQL-HA - 数据库编程

介绍利用keepalived构建高可用MySQL-HA，保证两台MySQL数据的一致性，然后用keepalived实现虚拟IP，通过keepalived自带的服务监控功能来实现MySQL故障时自动切换。

AD：

关于MySQL-HA，目前有多种解决方案，比如heartbeat、drbd、mmm、共享存储，但是它们各有优缺点。heartbeat、drbd配置较为复杂，需要自己写脚本才能实现MySQL自动切换，对于不会脚本语言的人来说，这无疑是一种脑裂问题；对于mmm，生产环境中很少有人用，且mmm 管理端需要单独运行一台服务器上，要是想实现高可用，就得对mmm管理端做HA，这样无疑又增加了硬件开支；对于共享存储，个人觉得MySQL数据还是放在本地较为安全，存储设备毕竟存在单点隐患。使用MySQL双master+keepalived是一种非常好的解决方案，在MySQL-HA环境中，MySQL互为主从关系，这样就保证了两台MySQL数据的一致性，然后用keepalived实现虚拟IP，通过keepalived自带的服务监控功能来实现MySQL故障时自动切换。

下面，我把即将上线的一个生产环境中的架构与大家分享一下，看一下这个架构中，MySQL-HA是如何实现的，环境拓扑如下

MySQL-VIP：192.168.1.90

MySQL-master1：192.168.1.91

MySQL-master2：192.168.1.92

OS版本：CentOS 5.4

MySQL版本：5.0.89

Keepalived版本：1.1.20

一、MySQL master-master配置

[root@localhost ~]# mysql -uroot

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.0.77 Source distribution



Type 'help;' or '\h' for help. Type '\c' toclear the buffer.



mysql> GRANT ALL PRIVILEGES ON *.* TOroot@'%';

Query OK, 0 rows affected (0.01 sec)



mysql> use mysql;

Reading table information for completion oftable and column names

You can turn off this feature to get aquicker startup with -A



Database changed

mysql> update user setPassword=password('bee') where user='root';

Query OK, 4 rows affected (0.01 sec)

Rows matched: 4 Changed: 4 Warnings: 0

mysql> show master status;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB |Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| MySQL-bin.000001 | 328 | | |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

1、修改MySQL配置文件

[mysqld]

log-bin=MySQL-bin

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

server-id = 2

replicate-same-server-id = 0

auto-increment-increment = 2

auto-increment-offset = 2



replicate-ignore-db=test

replicate-ignore-db=mysql

slave-skip-errors=all

# Default to using old password format forcompatibility with mysql 3.x

# clients (those using the mysqlclient10compatibility package).

old_passwords=1



# Disabling symbolic-links is recommended toprevent assorted security risks;

# to do so, uncomment this line:

# symbolic-links=0

[replication]

master-host=192.168.1.91

master-user=replication

master-password=replication

master-port=3306

master-connect-retry=60



[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

两台MySQL均如要开启binlog日志功能，开启方法：在MySQL配置文件[MySQLd]段中加上log-bin=MySQL-bin选项

两台MySQL的server-ID不能一样，默认情况下两台MySQL的serverID都是1，需将其中一台修改为2即可

2、将192.168.1.201设为192.168.1.202的主服务器

在192.168.1.201上新建授权用户

MySQL> grant replication slave on *.* to 'replication'@'%' identified by 'replication';

Query OK, 0 rows affected (0.00 sec)



MySQL> show master status;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| MySQL-bin.000003 | 374 | | |

+----

利用keepalived构建高可用MySQL-HA(一)