主-主复制（192.168.0.214---192.168.0.53）
配置同步账号
主1（192.168.0.214）
GRANT all privileges ON *.* TO mmuser@'192.168.0.53' IDENTIFIED BY 'mmpass';
修改 /etc/my.cnf
[mysql@dgryxrdb ~]$less /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
server-id = 1
log-bin = mysql-bin
binlog_format = mixed
binlog-do-db=mydb --需要记录二进制日志的数据库，如果有多个数据库，可用“，”分割，或者使用多个binlog-do-db选项 --应该是主库的参数
replicate-do-db=mydb --需要进行同步的数据库，如果有多个数据库，可用“，”分给，或者使用多个replicate-do-db选项 --应该是在从库的参数，解释需要同步的库
#同步参数
#保证slave挂在任何一个master上都会接收到另一个master的写入信息
log-slave-updates --将执行的复制sql记录到二进制日志（主-主应该加，是针对与主库更新后不会循环的参数？？）
#master-host=192.168.0.53
#master-user=mmuser
#master-password=mmpass
#master-connect-retry=10
sync_binlog=1 --当有二进制日志写入binlog文件的时候，mysql服务器将它同步至磁盘。
auto_increment_increment=2 --应设置为整个结构中服务器的总数(自增量)
auto_increment_offset=1 --用来设定数据库中自动增长的起点，因为两台服务器都设定了一次自动增长值2，因此起点必须不同，这样才能避免两台服务器数据同步时出现主键冲突
#slave-skip-errors=all --过滤掉一些错误，跳过错误，继续执行复制操作，如果不加此参数，出现任何错误都将停止复制
主2（192.168.0.53）
GRANT all privileges ON *.* TO mmuser@'192.168.0.214' IDENTIFIED BY 'mmpass';
修改/etc/my.cnf
[root@dns ~]# less /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
server-id = 53
log-bin = mysql-bin
binlog_format = mixed
binlog-do-db=mydb
replicate-do-db=mydb
sync_binlog=1
log-slave-updates
auto_increment_increment=2
auto_increment_offset=2
主1
[mysql@dgryxrdb ~]$mysql -u root -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.7-rc-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> flush tables with read lock\G; --防止进入新的数据
Query OK, 0 rows affected (0.00 sec)
ERROR:
No query specified
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000008
Position: 154
Binlog_Do_DB: mydb
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
主2
[mysql@dns ~]$mysql -u root -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.7-rc-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000006
Position: 154
Binlog_Do_DB: mydb
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified
主1
mysql> stop slave
-> ;
ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction
mysql> show slave status
-> ;
Empty set (0.00 sec)
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> change master to master_host='192.168.0.53',master_user='mmuser',master_password='mmpass',master_log_file='mysql-bin.000006',master_log_pos=154;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
主2
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> change master to master_host='192.168.0.214',master_user='mmuser',master_password='mmpass',master_log_file='mysql-bin.000008',master_log_pos=154;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
在两台服务器查看：
mysql> show slave status\G;
Relay_Master_Log_File: mysql-bin.000008
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
注意：LOAD DATA FROM MASTER（手动执行数据同步）目前只在所有表使用MyISAM存储引擎的数据库上有效。