MYSQL主从同步测试及常见问题处理

今天调试MYSQL主从同步测试，现把其中过程做一个简单的记录。

一、准备服务器

由于MySQL不同版本之间的(二进制日志)binlog格式可能会不一样，因此最好的搭配组合是Master的MySQL版本和Slave的版本相同或者更低，

Master的版本肯定不能高于Slave版本。

我用的主服务器(Master)和从服务器(Slave)的版本都是5.0.22，操作系统是Linux CentOS 5.2.x。

Master的主机IP为:192.168.1.129
Slave的主机IP为:192.168.1.131，

2个MySQL的basedir目录都是/usr/local/mysql，datadir都是：/usr/local/mysql/data。

二、设置同步服务器

1、master 配置
 
每个同步服务器都必须设定一个唯一的编号，否则同步就不能正常运行了。接下来开始修改 my.cnf，在[mysqld]下面增加以下几行：

server-id = 1    //服务器编号

log-bin = mysql-bin    //开启日志 

set-variable=binlog-ignore-db=mysql //不需要同步的表

set-variable=binlog-do-db = test  // 要同步的表，如果全部同步可以不写此行

然后在Master上增加一个账号专门用于同步，如下：

登录mysql服务器
#./mysql -uroot -p
mysql>GRANT REPLICATION SLAVE ON *.* TO slave@192.168.1.131 IDENTIFIED BY '密码';

从库连接主库进行测试： >mysql -h192.168.1.129 -uslave -p123456

此处测试应该是通过的，否则 mysql 配置有问题.

2、slave 配置
 修改my.cnf配置，在[mysqld]下面增加以下几行：

    server-id=2  //服务器编号

    master-host = '192.168.1.129'

    master-user = 'slave'

    master-password = '123456'

    master-port = 3306

    master-connect-retry = 120

重新启动mysql,登录mysql查看:
 mysql>SHOW SLAVE STATUS\G;
如果出现:
          Slave_IO_Running: Yes
          Slave_SQL_Running: Yes

表明配置成功，可以进行测试。

如果Slave_IO_Runing:No 则说明权限分配有错误，重新分配权限。
如果Slave_SQL_Runing:No 则可用下面方法解决:

解决办法一、

Slave_SQL_Running: No
1.程序可能在slave上进行了写操作

2.也可能是slave机器重起后，事务回滚造成的.

一般是事务回滚造成的：
解决办法：
mysql> slave stop;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> slave start;

 

解决办法二、

首先停掉Slave服务：slave stop
到主服务器上查看主机状态：
记录File和Position对应的值

进入master

mysql> show master status;
+----------------------+----------+--------------+------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+------------------+
| localhost-bin.000094 | 33622483 |              |                  |
+----------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

 

然后到slave服务器上执行手动同步：

mysql> change master to
> master_host='master_ip',
> master_user='user',
> master_password='pwd',
> master_port=3306,
> master_log_file='localhost-bin.000094',
> master_log_pos=33622483 ;
1 row in set (0.00 sec)
mysql> slave start;

手动同步需要停止master的写操作！