在centos下实现mysql双机热备

heartbeat只是起到了ip漂移的功能，但是实际应用当中必须要做到文件和数据的实时同步，这里我们来动手搭建一下mysql的双机互备，及搭建过程中碰到的常见问题和解决方法。

mysql的双机热备其实是基于二进制日志文件的，从服务器读取主服务器的日志文件然后更新，从而达到同步，具体过程是这样的：mysql支持单向、异步复制，复制过程中要有一个主服务器，一个或者多个充当从服务器，主服务器将更新写入二进制日志文件，并维护日志文件的索引以跟踪日志的循环，服务器在日志中读取最后一次更新的位置，从服务器接收从那时起发起的任何更新，然后封锁并等待主服务器通知的下一次更新。

实际项目中两台服务器互为主备，当其中一台服务器出现故障时，另外一台服务器接管主服务器上的应用（结合上一篇结合上一篇：http://www.iamlintao.com/动手搭建centos下用heartbeat搭建双机热备/ 实现），此时便需要mysql的实时双机互备。我的搭建过程如下：

服务器环境：

主服务器：

操作系统：centos6.4 (Linux 2.6.18-348.12.1.el5)
ip：10.0.0.211
web环境：wdcp搭建
mysql：5.1.69

备服务器：

操作系统：centos6.4 (Linux 2.6.18-348.12.1.el5)
ip：10.0.0.212
web环境：wdcp搭建
mysql：5.1.69

创建同步账号：

数据库能相互访问受限要创建可以远程登陆的账号，通过这个远程账号才能实现数据同步。首先登陆mysql(登陆方法自行google),登陆mysql之后用以下命令创建远程登陆账号：


<span style="font-family: 'Times New Roman';">MySQL>GRANT all privileges ON *.* TO zuiw@'10.0.0.212' IDENTIFIED BY  '******'; </span>

这条命令是在主服务器上自行的，备服务器自己来写就行。

语法解释：

grant 权限1,权限2,…权限n on 数据库名称.表名称 to 远程登陆用户名@远程登陆地址 identified by ‘远程登陆连接口令’;

·权限1,权限2,…权限n代表select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file等14个权限。
·当权限1,权限2,…权限n被all privileges或者all代替，表示赋予用户全部权限。
·当数据库名称.表名称被*.*代替，表示赋予用户操作服务器上所有数据库所有表的权限。
·用户地址可以是localhost，也可以是ip地址、机器名字、域名。也可以用’%’表示从任何地址连接。
· ‘连接口令’ 不能为空，否则创建失败。

配置服务器的数据库参数：

对于wdcp来说，mysql的配置文件在 /www/wdlinux/etc/my.cnf ；对于独立安装的mysql来说在 /etc/my.cnf下，用vi或者vim打开后按照如下配置：

log-bin=mysql-bin # 二进制文件的开头标示，必写项
server-id= 1 # 主服务器写1，备服务器写2，必写项，主备服务器的一定不能相同
binlog-do-db=test # 主服务器用，需要同步的数据库，如果有多个自行复制多行
#binlog-ignore-db = mysql # 主服务器用，不需要同步的数据库，多个数据库自行复制
master-host=10.0.0.212 # 允许同步服务器的ip，双机互备的情形下主服务器写备服务器ip，备服务器写主服务器ip
master-user=zuiw # 用于同步的账号，在上一步中已经创建的那个远程登陆账号
master-password=****** # 用于同步账号的密码，在上一步创建远程登陆账号时候设置的密码
master-port=3306 # 同步服务器的mysql的端口
master-connect-retry=3 #同步的时间间隔，单位是秒
replicate-do-db=test # 备服务器用，需要同步的数据库，如果有多个自行复制多行
#replicate-ignore-db=test #备服务器用，不需要同步的数据库，如果有多个自行复制多行
slave-skip-errors=all # 忽略所有类型的错误
sync_binlog=1 # 每向二进制日志文件写入n条sql或n个食物后，这把二进制日志问及那的数据刷新到新磁盘上，如果为0则不主动刷新而有操作系统决定，为了保险起见，这里建议写成1
auto_increment_increment=1 # 控制列中的值的增量值
auto_increment_offset=1 # 控制AUTO_INCREMENT 列值的起点

以上配置为主服务器的配置，理解了上面的这几项，从服务器就容易了，只需要修改master-host和server-id即可，注意：这些项有的是存在的，有的是新增加的，有的是需要修改的，请自行核对。

执行同步：

·首先登陆服务器重启mysql

service MySQLd restart

·然后停止同步、锁定表、查看读取的二进制文件和position的值，然后执行 CHANGE MASTER TO 命令，步骤如下：

MySQL>stop slave;   # 停止同步
MySQL>flush tables with read lock;  # 锁定表为只读状态
MySQL>SHOW MASTER STATUS; # 查看主服务器的日志名称和position的值

注:如果出现“ Can’t execute the given command because you have active locked tables or an active transaction”字样，则按照字面意思解决，执行先锁定再停止，总之命令就这几个，顺序自己搞定。

从返回结果获取二进制的名和position的值：

CHANGE MASTER TO 这条命令把数据保存到master.info文件中去了，在数据库存放的路径下。

在主服务器上的mysql命令下执行如下语句：

change master to master_host='10.0.0.212',master_user='zuiw',master_password='****',master_log_file='mysql-bin.000001', master_log_pos=1787;

可以这样理解，以那个用户/密码读取那个服务器(ip)的那个文件，从那个position开始读取。格式说明：

change master to master_host=’主服务器上写备服务器ip，备服务器写主服务器ip’,master_user=’同步用户名’,master_password=’同步密码’,master_log_file=’上面获取到的二进制文件名’, master_log_pos=获取到的position的值;

备服务器自行编写。

最后启动同步：

MySQL>start slave;
MySQL>unlock tables;

如果正确，在主、备服务器上分别写入数据就能看到同步。

容易出现的几个问题：

1. 同步用户创建不成功

现象：执行“SHOW SLAVE STATUS;” 命令是返回带有“error connecting to master ‘zuiw@10.0.0.211:3306’ – retry-time: 60 retries: 86400”字样。

解决：提示说明连接到主/备服务器失败。在网上流传有三种分析：网络问题、配置问题、同步用户写错了，除了这三个之外还可能有另外一种情况。用phpmyadmin可以打开mysql数据库中的user表，如果成功创建同步账户则在user表中会存在此用户，如果创建失败则不会存在；如果创建正确，执行“show processlist;”会得到如下：

注意这里的同步所对应的host不是ip而是搭建服务器时候设置的服务器名(结合上一篇：http://www.iamlintao.com/动手搭建centos下用heartbeat搭建双机热备/),所以连接不上可以考虑是不是host文件有问题，但是我发现我的host上是这样写的“127.0.0.1 localhost localhost.localdomain localhost”，于是改成“127.0.0.1 localhost.localdomain localhost”后再新建同步账户成功。

2.日志重置

现象：经过多次的测试或者试验会出现多个日志，需要重新设置。

解决：在mysql下执行

mysql>reset master;   #清除日志
mysql><code>purge binary logs to </code><code>'mysql-bin.000018'</code><code>; # 删除mysql-bin.000018之前的日志</code>

或者直接用ssh工具登陆到服务器直接删除 master.info、 mysql-bin.000001、 mysql-bin.index、 relay-log.info 这几个文件，重启mysql后会自己生成。

3.在没有解锁的情况下停止slave进程：


mysql> stop slave;
ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction

4.change master语法错误，落下逗号

 mysql> change master to

-> master_host='IP'
 -> master_user='USER',
 -> master_password='PASSWD',
 -> master_log_file='mysql-bin.000002',
 -> master_log_pos=106;
 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version     for the right syntax to use near 'master_user='USER',
 master_password='PASSWD',
 master_log_file='mysql-bin.000002' at line 3

5.在没有停止slave进程的情况下change master

mysql> change master to master_host=‘IP', master_user='USER', master_password='PASSWD', master_log_file='mysql-    bin.000001',master_log_pos=106;

ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first

6.A B的server-id相同：


Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids;
these ids must be different for replication to work (or the --replicate-same-server-id option must be used on
slave but this does not always make sense; please check the manual before using it).
查看server-id
mysql> show variables like 'server_id';
手动修改server-id
mysql> set global server_id=2; #此处的数值和my.cnf里设置的一样就行
mysql> slave start;

最后：调试期间可以借助phpmyadmin、SSH Secure Shell 等工具，便于解决问题。

转载请注明来自：http://www.iamlintao.com/亲测centos下实现mysql双机互备/

如需转载请注明： 转载自26点的博客