# 最近有项目应用到了 mysql 双主结构，现在贴出来共享

# mysql 版本： 5.6.11

# 操作系统版本: rhel 6.2

# Master 的 my.cnf 配置( 只贴M/M 结构部分）

log-bin=fabianserver-id=1binlog-do-db=TSCbinlog-do-db=adbbinlog-ignore-db=mysqlreplicate-do-db=TSCreplicate-do-db=adbreplicate-ignore-db=mysqllog-slave-updatesslave-skip-errors=allauto_increment_increment=2auto_increment_offset=1

# Slave 的my.cnf 配置(只贴M/M结构部分)

log-bin=fabianserver-id=2binlog-do-db=TSCbinlog-do-db=adbbinlog-ignore-db=mysqlreplicate-do-db=TSCreplicate-do-db=adbreplicate-ignore-db=mysqllog-slave-updatesslave-skip-errors=allauto_increment_increment=2auto_increment_offset=2

log-bin=fabian #M/S 需开启log-bin 日记文件server-id=1 #指定server-id 必须不一致，M/s 结构时 M > Sbinlog-do-db=TSC #同步数据库名称binlog-ignore-db=mysql #忽略数据名称replicate-do-db=TSC #用于控制slave来执行同步的行为replicate-ignore-db=mysql #用于控制slave来执行同步的行为log-slave-updates #把更新的记录写到二进制文件中slave-skip-errors=all #跳过错误，继续执行复制auto_increment_increment=2 #设置主键单次增量auto_increment_offset=1 #设置单次增量中主键的偏移量#expire_logs_days = 20 #设置log-bin 超过多少天删除max-binlog-size= 512M# auto_increment_increment、auto_increment_offset 可以防止双主主键冲突问题

#!/bin/bash # Setting Variables _REMOTEHOST=192.168.1.51 #远程主机IP _LOCALHOST=192.168.1.52 #本地主机IP _USER=root #用户名 _REMOTEPASD=123456 #远程主机密码 _LOCALPASD=123456 #本地主机密码 _BASE=TSC _LF=`mysql -u root -h $_REMOTEHOST -p$_REMOTEPASD -e "show master status/G;" | awk '/File/ {print $2}'` _LLF=`mysql -u root -p$_LOCALPASD -e "show master status/G;" | awk '/File/ {print $2}'` _PS=`mysql -u root -h $_REMOTEHOST -p$_REMOTEPASD -e "show master status/G;" | awk '/Position/ {print $2}'` _LPS=`mysql -u root -p$_LOCALPASD -e "show master status/G;" | awk '/Position/ {print $2}'`# Backup Mysql mysqldump -u root -h $_REMOTEHOST -p$_REMOTEPASD $_BASE > $_BASE.sqlmysql -u root -p$_LOCALPASD $_BASE < $_BASE.sqlrm -rf $_BASE.sqlmysql -uroot -p$_LOCALPASD -e "stop slave;"mysql -h $_REMOTEHOST -uroot -p$_LOCALPASD -e "stop slave;"echo "mysql -uroot -p$_LOCALPASD -e +change master to master_REMOTEHOST=*${_REMOTEHOST}*,master_user=*${_USER}*,master_password=*${_REMOTEPASD}*,master_log_file=*${_LF}*,master_log_pos=${_PS};+" > tmpecho "mysql -h $_REMOTEHOST -uroot -p$_LOCALPASD -e +change master to master_REMOTEHOST=*${_LOCALHOST}*,master_user=*${_USER}*,master_password=*${_LOCALPASD}*,master_log_file=*${_LLF}*,master_log_pos=${_LPS};+" > tmp2 sed -ri 's//+/"/g' tmp sed -ri 's//+/"/g' tmp2 sed -ri "s//*//'/g" tmp sed -ri "s//*//'/g" tmp2 sh tmp sh tmp2 rm -rf tmp rm -rf tmp2mysql -uroot -p$_LOCALPASD -e "start slave;"mysql -h $_REMOTEHOST -uroot -p$_LOCALPASD -e "start slave;"mysql -uroot -p$_LOCALPASD -e "show slave status/G;" | awk '$0 ~/Host/ || $0 ~/State/'mysql -h $_REMOTEHOST -uroot -p$_LOCALPASD -e "show slave status/G;" | awk '$0 ~/Host/ || $0 ~/State/'

#脚本执行完成后出现下图则表示成功：

[root@ORA2 fabian]# sh gant.sh Warning: Using a password on the command line interface can be insecure.Warning: Using a password on the command line interface can be insecure.Warning: Using a password on the command line interface can be insecure.Warning: Using a password on the command line interface can be insecure.Warning: Using a password on the command line interface can be insecure.Warning: Using a password on the command line interface can be insecure.Warning: Using a password on the command line interface can be insecure.Warning: Using a password on the command line interface can be insecure.Warning: Using a password on the command line interface can be insecure.Warning: Using a password on the command line interface can be insecure.Warning: Using a password on the command line interface can be insecure.Warning: Using a password on the command line interface can be insecure. Slave_IO_State: Queueing master event to the relay log Master_Host: 192.168.1.51 Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update itWarning: Using a password on the command line interface can be insecure. Slave_IO_State: Queueing master event to the relay log Master_Host: 192.168.1.52 Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it[root@ORA2 fabian]# 

至此完成！