###########################################################################################
linux: CentOS 5.6
mysql: 5.6.22
MASTER A : centos152（192.168.1.152）
MASTER B : centos153（192.168.1.153）
#主备都启用：log-bin 和 relay_log
#若主主双方都操作，最好设置auto-increment-offset 和 auto-increment-increment，以避免冲突。若只在其中一个库操作，可不需要设置
###########################################################################################
#master A 主服务器配置：
[root@centos152 ~]# vi /etc/my.cnf
[mysqld]
basedir =/usr/local/mysql
datadir =/usr/local/mysql/data
socket=/usr/local/mysql/mysql.sock
port = 3306
server_id = 1
log-bin= mysql-bin
relay_log=mysql-relay-bin
binlog_format = row
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#auto-increment-offset=1
#auto-increment-increment=2
###########################################################################################
#master B 备服务器配置：
[root@centos153 ~]# vi /etc/my.cnf
[mysqld]
basedir =/usr/local/mysql
datadir =/usr/local/mysql/data
socket=/usr/local/mysql/mysql.sock
port=3306
server_id=2
log-bin= mysql-bin
relay_log=mysql-relay-bin
binlog_format = row
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#auto-increment-offset=2
#auto-increment-increment=2
###########################################################################################
#配置 my.cnf 后 master A 和 master B 都重启mysqld服务
[root@centos152 ~]# service mysqld restart
[root@centos153 ~]# service mysqld restart
#master A ：备份数据
[root@centos152 ~]# mysqldump -uroot -p --single-transaction --events --triggers --routines --flush-logs --master-data=2 --databases test > /tmp/test.sql
#master A ：备份远程传输到服务器153 (master B)
[root@centos152 ~]# scp -r /tmp/test.sql root@192.168.1.153:/tmp
#master B ：还原 数据库
[root@centos153 ~]# mysql -u root -p test< /tmp/test.sql
#master A 和 master B 添加账号
mysql> grant replication slave on *.* to 'replication'@'192.168.1.%' identified by '123456';
mysql> flush privileges;
###########################################################################################
#查看备份时的日志位置
[root@centos152 ~]# grep -i "change master" /tmp/test.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=120;
#master B 连接到 master A
mysql> STOP SLAVE;
mysql>
CHANGE MASTER TO
MASTER_HOST='192.168.1.152',
MASTER_USER='replication',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000015',
MASTER_LOG_POS=120;
mysql> START SLAVE;
#master B 查看同步信息：
mysql> show slave status \G;
#若看到：Slave_IO_Running 和 Slave_SQL_Running 为 Yes ，则表示两个同步线程正常运行中。
#此时 master A 可以正常同步到 master B 了。
#接下来设置 master B 同步到 master A
###########################################################################################
#master B (192.168.1.153)查看状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000010 | 8690 | | mysql | |
+------------------+----------+--------------+------------------+-------------------+
#master A (192.168.1.152)设置同步
mysql> STOP SLAVE;
mysql>
CHANGE MASTER TO
MASTER_HOST='192.168.1.153',
MASTER_USER='replication',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000010',
MASTER_LOG_POS=8690;
mysql> START SLAVE;
#master A 查看同步信息
mysql> show slave status \G;
#若看到：Slave_IO_Running 和 Slave_SQL_Running 为 Yes ，则表示两个同步线程正常运行中。
###########################################################################################
#查看其他信息
mysql> show master status;
mysql> show slave status \G;
mysql> show slave hosts;
mysql> show processlist \G;
mysql> show full processlist \G;
mysql> show master logs;
###########################################################################################
MySQL 主从复制