mysql数据库双向的关系_mysql数据库双向同步

1. 主从关系的同步

master端 192.168.5.10

slave端  192.168.5.4

修改master端的/etc/my.cnf文件

[mysqld]:

log-bin

server-id = 1

进入mysql

创建一个要同步的数据库abc

create database abc;

创建一个用来同步的用户,指定只能在192.168.5.4登录

grant file on *.* to bak@'192.168.5.4 identified by '123456';

grant all privileges on backup.* to bak@’192.168.5.4’ identified by ‘123456’;

flush privileges;

重启mysql

修改slave端的/etc/my.cnf文件

[mysqld]:

server_id = 2

log-bin = mysql-bin

master-host = 192.168.5.10

master-user = bak

master-password = '123456' (如果为空也要打上两个单引号)

master-port = 3306 (根据具体设置的端口号填)

master-connect-retry = 10 连接重试次数

replicate-do-db = abc 要接收的数据库名，如果有多个数据库，每个数据库一行

replicate-ignore-db= mysql   设置不要接收的数据库，每个数据库一行   (一般这条可以不写)

重启mysql

slave start;

配置成功后会在mysql目录下生成master.info,如果要更改slave设置,要先将master.info删除,才会起作用。

然后进入mysql用show slave status 或show master status 查看同步情况

3、 双向同步

master端的设置

[mysqld]:

log-bin

server-id = 1

sql-bin-update-same 同步模式 ，在mysql5以上的版本都不需要这句，否则会启动不了

mysql服务

binlog-do-db= abc    设置同步数据库，如果有多个数据库，每个数据库一行

binlog-ignore-db = mysql 设置不要同步的数据库，如有多个数据库，每个数据库一行

master-host=192.168.5.4

master-user=bak

master-password='123456'

master-port=3306

master-connect-retry=10

replicate-do-db=abc   设置要接收的数据库，如有多个数据库，每个数据库一行

replicate-ignore-db= mysql   设置不要接收的数据库，每个数据库一行   (一般这条可以不写)

log-slave-updates    在mysql5以上的版本都不需要这句

进入mysql

创建一个数据库abc

create database abc;

创建一个用来同步的用户,指定只能在192.168.5.4登录

grant file on *.* to bak@'192.168.5.4 identified by '123456';

重启mysql服务

slave start;

slave设置

log-bin

server-id = 2

binlog-do-db= abc    设置同步数据库，如果有多个数据库，每个数据库一行

binlog-ignore-db = mysql 设置不要同步的数据库，如有多个数据库，每个数据库一行

sql-bin-update-same   同步模式 ，在mysql5以上的版本都不需要这句，否则会启动不了mysql服务

master-host=192.168.5.10

master-user= bak

master-password='123456'

master-port=3306

master-connect-retry=10

replicate-do-db=abc   设置要接收的数据库，如有多个数据库，每个数据库一行

replicate-ignore-db= mysql   设置不要接收的数据库，每个数据库一行   (一般这条可以不写)

log-slave-updates   在mysql5以上的版本都不需要这句

进入mysql

创建一个数据库abc

create database abc;

创建一个用来同步的用户,指定只能在192.168.5.10登录

grant file on *.* to bak@'192.168.5.10 identified by '123456';

重启mysql服务

slave start;

注意，如果有一端修改了数据后，另一端接收不到，手工执行命令load data from master;就可以了

如果因为误删了二进制日志文件导致无法同步，则执行

reset slave;

stop slave;

start slave;

三、可能遇到的问题

1、my.ini是WINDOWS系统下的mysql配置文件，而在linux系统下该文件会命名为my.cnf。此文件修改后mysql必须重启才能生效。在用PDM方式安装mysql的时候，在系统的默认位置/etc/my,cnf会找不到my.cnf文件，此时mysql则会去读mysql/support-files/my-medium.cnf这个位置的文件来进行初始化设置，因此可以直接把后一个文件拷到前面的默认位置。

2、配置完后，可以用“mysql –u root -p”命令进行sql环境，用“show master status”及”show slave status”命令查看主用和备用进程的运行情况，可以发现问题。同时可以用”show variables”来查看系统设置的变量。Slave进程有两个线程非常重要，分别是“Slave_IO_Running和          Slave_SQL_Running”。必须都启动。

3、如果配置完了但没有同步，可以查/var/lib/mysql/localhost.localdomain.err下的这个错误日志。日志以主机名命名，不同的机器可能不一样，不同的安装路径也会导致前面的路径不同。在此日志里会详细提示发生的错误，根据错误在网上查询或者分析配置，基本上就能解决大部分问题了。

4、如果修改了主服务器的配置，记得删除从服务器上的master.info文件。否则从服务器使用的还是老配置，可能会导致错误。这是因为从服务器会直接把master.info文件中的内容去替代my.cnf中的配置。

5、单向同步的时候，有可能报“[ERROR] Slave: Error 'Duplicate entry '589' for key 1' on query. Default database: plication_master'. Query: 'insert into as_card_type values ('589','sdddddsd',25,'dfsdfd')', Error_code: 1062”错，这时到数据库里去把该条数据删除即可。因为导致报此错的原因是数据库内已有此条数据，而二进制文件又要求再插入一条此数据。发生此错误后一般会终止slave进程。

6、错误“[ERROR] Failed to open the relay log './localhost-relay-bin.000609' (relay_log_pos 288)”，此错误的解决方法为：

-> stop mysql on the slave server

-> find the directory that contains the relay logs (usually /var/lib/mysql)

-> delete all relay logs called hostname-relay-bin.XXX, hostname-relay-bin.index and relay-log.info

-> restart mysql

我觉得发生此错误的原因是读本地二进制日志出问题，那么就把本地日志删除，强制去读主机的日志信息。

7、有时候数据库同步自己会停下来，原因是发生了错误。那么用下面的语句可以避免这个问题。

#出现错误后忽略，如果不加这个，出现任何错误，同步进程会终止

slave-skip-errors=all

8、有人说此选项重要，我觉得此选项不过是让从机日志记载了所有的操作，将来便于恢复或者回滚。我觉得可以不用。

log-slave-updates //当Slave从Master数据库读取日志时将跟新写入日志中

log-bin 用于启动数据库的跟新日记，当针对启动这个选项的数据库本身进行数据跟新时更新操作将被记录。log-slave-updates 告诉Slave在二进制记录中记录从主数据库同步到的更新数据。如果只启动log-bin 而没有启动log-slave-updates则Slave只记录针对自己数据库操作的更新。

9、今天又碰到一个错误：前段时间还同步的很正常的系统今天四台全部失效，重启后报“Warning: World-writable config file '/etc/my.cnf ' is ignored ”。我估计是我以前把my.cnf文件权限全设成了666，所以系统认为危险，跳过去没有读，于是我用chmod 660重设了一下，果然没有再报此错，一切正常了。