mysql5.6双机热备配置

配置mysql双机热备最好两台机器安装相同版本的mysql，下面是我配置是的完整记录

 

------------------------------------------------------------------------------
#---1---授权用户replicate

#服务器72
grant replication slave on *.* to 'replicate'@'210.7.6.176' identified by '111111';
flush privileges;

#服务器176

grant replication slave on *.* to 'replicate'@'210.7.6.72' identified by '111111';

flush privileges;

------------------------------------------------------------------------------
#---2---修改my.ini(两个目录)
#C:\ProgramData\MySQL\MySQL Server 5.6
#C:\Program Files\MySQL\MySQL Server 5.6
#服务器72
[mysqld]
log-output=FILE
server-id = 1
log-bin=mysql-bin
binlog-do-db = mtest
binlog-ignore-db = mysql

#主－主形式需要多添加的部分
log-slave-updates
sync_binlog = 1
auto_increment_offset = 1
auto_increment_increment = 2
replicate-do-db = mtest
replicate-ignore-db = mysql,information_schema

#服务器176
[mysqld]
log-output=FILE
server-id = 2
log-bin=mysql-bin
replicate-do-db = mtest
replicate-ignore-db = mysql,information_schema,performance_schema

#主－主形式需要多添加的部分
binlog-do-db = mtest
binlog-ignore-db = mysql
log-slave-updates
sync_binlog = 1
auto_increment_offset = 2
auto_increment_increment = 2


------------------------------------------------------------------------------
#---3---重启服务

两个服务器都需要重启
net stop mysql56
net start mysql56

------------------------------------------------------------------------------
#---4---查看服务器状态

#服务器72
show master status;
# | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
# +------------------+----------+--------------+------------------+-------------------+
# | mysql-bin.000003 | 120 | mtest | mysql | |

 

#服务器176
show master status;
# | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
# +------------------+----------+--------------+------------------+-------------------+
# | mysql-bin.000004 | 120 | mtest | mysql | |

------------------------------------------------------------------------------
#---5---指定同步位置
stop slave;
服务器72：
change master to
master_host='210.7.6.176',master_user='replicate',master_password='111111',
master_log_file='mysql-bin.000004',master_log_pos=120;
服务器176：
change master to
master_host='210.7.6.72',master_user='replicate',master_password='111111',
master_log_file='mysql-bin.000003',master_log_pos=120;

------------------------------------------------------------------------------
#---6---重启从服务线程

两个服务器都需要重起
start slave;

------------------------------------------------------------------------------
#---7---查看从服务器状态

两个服务器都查看
show slave status;
#Slave_IO_Running: Yes
#Slave_SQL_Running: Yes