mysql互为主备设置_mysql互为主从(双主)配置

---------------master1服务器操作记录---------------

在my.cnf文件的[mysqld]配置区域添加下面内容：

[root@master1 ~]# vim /etc/my.cnf

server-id = 1

log-bin = mysql-bin

sync_binlog = 1

binlog_checksum = none

binlog_format = mixed

auto-increment-increment = 2

auto-increment-offset = 1

slave-skip-errors = all

[root@master1 ~]# cd /usr/local/mysql/support-files

[root@master1 ~]#./mysql.server restart

Shutting down MySQL. SUCCESS!

Starting MySQL.. SUCCESS!

数据同步授权(iptables防火墙开启3306端口)这样I/O线程就可以以这个用户的身份连接到主服务器，并且读取它的二进制日志。

mysql>grant all privileges on *.* to root@'%' identified by "123456";

#grant replication slave,replication client on *.* to root@'192.168.85.%' identified by "123456";

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

最好将库锁住，仅仅允许读，以保证数据一致性；待主主同步环境部署后再解锁；

锁住后，就不能往表里写数据，但是重启mysql服务后就会自动解锁！

mysql> flush tables with read lock; //注意该参数设置后，如果自己同步对方数据，同步前一定要记得先解锁！

Query OK, 0 rows affected (0.00 sec)

查看下log bin日志和pos值位置

mysql> show master status;

+------------------+----------+--------------+--------------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+--------------------------+-------------------+

| mysql-bin.000004 | 430 | | mysql,information_schema | |

+------------------&#