mysql数据库双向同步

By | 2011/06/22

mysql数据库双向同步的配置过程来说其实很简单,如果不出意外的话,不到十条的命令就可以实现。可是要明白为什么要这么操作、各个参数有什么作用却是很难的,虽然数据库主从配置的文章很多,可大部分都是介绍如何操作,少有说明各条命令作用的。本人也没有那么高深的理论水平,谨以此文记录自己实际配置过程中遇到的一些问题及个人对这些问题的猜想,以供自己回味,能帮到别人更好。

首先说说mysql数据库双向同步如果实现的,其实就是分别做了主从同步而已。举个例子:有A、B两台mysql数据库服务器,先做A主B从的设置,然后再做B主A从的设置,从而实现了双向同步。

对于要同步的数据库的选择,可以设置同步所有的数据库,也可以同步部分数据,还可以选择禁止同步某些数据库。看起来好像3个选项,其实是通过my.cnf(mysql数据库的配置文件,[mysqld]段内)两个参数实现的:

binlog-do-db=dojocn
binlog-do-db=test

binlog-ignore-db=mysql

前者为要同步的数据库,后者为禁止同步的数据库。如果想同步所有的数据库,那么无需设置这两个选项;如果只同步某几个数据库,那么设置第一个选项,多个数据库分多行写就行了;如果只是禁止某几个数据库,那么就设置最后的那个选项。这三种方式需根据各自不同的环境进行选择。上面的命令是主服务器端的,从服务器端也有相仿的两条命令进行相关的设置,作用类似:

#需要同步的数据库
replicate-do-db=dojocn
#忽略同步的数据库
replicate-ignore-db=mysql

了解了上面这些,基本上就知道怎么设置了,下面说一下具体配置流程:

1.配置主服务器端

首先要确保数据库没有人在进行读写操作。然后修改mysql服务的配置文件,一般是/etc/my.cnf文件,在[mysqld]字段下面,添加下面字段即可:

#master
binlog-do-db=dojocn
binlog-ignore-db=mysql
server-id=1
log-bin=mysql_log_bin

前两个不做解释了,根据之前的描述进行修改即可,server-id只要保证各同步的数据库之间不重复即可。设置好后重启。

在mysql中运行show master status;

mysql> show master status;

+————————+————-+——————+———————–+
| File                                 | Position     | Binlog_Do_DB | Binlog_Ignore_DB |
+————————+————-+——————+———————–+
| mysql-bin.000048 |  5199773  | dojocn                | mysql                           |
+————————+——— —+——————+————————+

结果应该是上面的样子。如果提示为空(为no ****,具体内容忘记了),尝试添加 log-bin=mysql_log_bin 到配置文件里面。

这里要记录下file的“mysql-bin.000048”和Position的“5199773”,能否同步成功这是关键点,

2.添加备份用户

grant replication slave on *.* to ‘backuser’@’74.82.187.177’ identified by ‘password’;
flush privileges;

第一句为添加用户同步用户backuser,并只允许来自74.82.187.177的访问,密码为password。可能网上的语句比我的这个复杂,那是因为他们可能授予了更多的权限,但是作为同步的用户,只赋予同步权限即可。

第二句是立即刷新权限,使权限生效。

3.配置从服务器

如果需要同步的数据库在主服务器上早已存在,那么需要拷贝相应的数据库文件到从服务器上(在保证主服务器数据库在不写的情况下,直接copy相应的文件夹即可)。然后是修改mysql服务的配置文件的[mysqld]字段:

#slave
master-host=215.186.11.18
master-user=backuser
master-password=password
master-port=3306

server-id=2
replicate-do-db=dojocn
replicate-ignore-db=mysql
relay-log=mysqld-relay-bin

这是一般的配置,其实个人觉得server-id之前的完全没有必要配置,因为后面的语句还会进行相关的设置(不进行设置,测试成功)。此处的要点是保证server-id和主服务器不一样;replicate开头的两条按照需要进行设置;那么relay-log的作用是什么?其实就是用来保存从端操作日志的,添加的原因最后进行解释。设置好后记得重启数据库。

4.启动从端服务

在mysql中执行下面的命令,里面有5处需要按照你的情况修改,master_log_file和master_log_pos就是之前我们在主数据库上执行“show master status;”得到的,一定要保证这此处的值和copy数据库的时候的值一致。在sql中执行下面的语句:

change master to  master_host=’74.82.187.177′, master_user=’backuser’, master_password=’pasword’, master_log_file=’mysql_log_bin.000001′, master_log_pos=23418;

上面命令执行成功后,运行“start slave”提示成功,再运行“show slave status\G;”查看运行状态,看到

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

就是配置成功了!同样的方法再反过来配置一遍主从,即可实现数据库的双向同步。

是不是很简单?其实我的配置过程远远没有这么顺利,首先两台服务器环境不一样,数据库版本不一样(主从一般要求从的数据库版本不低于主的,因此做双向同步最好一个版本),然后一个mysql是编译的,一个书rpm包安装的,导致了配置文件也有很大的不同。

第一个问题出现在“start slave”上,运行后提示“mysql not configured as slave please fix config file or with change master to”,不解。查资料,说可能是因为copy过来的数据库权限不对,chown修改后还是不行。此时找到了一个通用的解决办法:查看日志文件。在数据库目录下会有一个log文件,有的是带有err字样的,具体要看my.cnf中的相关设置。里面会记录具体是什么原因导致的从服务启动不了,而且也会给出修改建议。比如我的就告诉我添加“relay-log=mysqld-relay-bin”,添加后就立马解决了。

第二个问题是出现在从服务器设置主的参数的时候,show master status老提示提示为空啊空的,解决办法上面提到了添加 log-bin=mysql_log_bin 到配置文件里面即可。

第三个问题是在主做从配置的时候,使用“show slave status\G;” 结果Slave_SQL_Running:No,其实这个最好解决,继续往下看就会看到错误的原因,直接根据原因解决就ok了。

总的说来配置还是很简单的,我的这个也只是一个简单的应用。其实还会面临更多的问题,比如写数据库时,如果同时insert操作包含自增字段,可能会导致自增字段不唯一。这个的解决办法还没有研究下,反正不好处理就是了。最后列举下常用到的相关命令;

1.查询mysql版本号

mysql -V

2.查询mysql服务器各配置文件的路径

除了查看相应的启动脚本和my.cnf配置文件外,ps auxwww|grep mysql |grep -v grep 这个命令也很好用

ps:今天就到此为止,抽空再捣鼓mysql数据库的备份及恢复去,顺便看看怎么解决自增字段的问题,有心得了再分享。

2 thoughts on “mysql数据库双向同步

  1. satsun

    你这是拿我的数据库做测试啊 。你大爷的、、、太狠了!!!

    Reply
    1. admin Post author

      此言差矣,是免费给你做个远程备份。其实就是用了下数据库名,真是没有备份!

      Reply

回复 admin 取消回复

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

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据