这里我们来动手搭建一下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之后用以下命令创建远程登陆账号:

MySQL>GRANT all privileges ON *.* TO zuiw@’10.0.0.212′ IDENTIFIED BY ‘******’;

这条命令是在主服务器上自行的,备服务器自己来写就行。
语法解释:
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>purge binary logs to 'mysql-bin.000018'; # 删除mysql-bin.000018之前的日志

或者直接用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;

发表评论

电子邮件地址不会被公开。 必填项已用*标注