mysql5.6 双机热备_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