mysql主从复制1064_MYSQL双向主从复制

假如配置 192.168.2.100(主) 和 192.168.2.95(备)两台服务器 mysql 双向同步，数据库用户为test

Ø 配置文件修改

两台主机上修改mysql配置文件 mysqld.cnf

server-id = 100

log_bin = /var/log/mysql/mysql-bin.log

expire_logs_days = 10

max_binlog_size = 100M

log-bin-trust-function-creators=1

sql_mode='NO_ENGINE_SUBSTITUTION'

slave_skip_errors=1032,1064,1062

binlog-do_db = test_db#指定同步的数据库名

binlog_ignore_db = mysql,information_schema,performance_schema

replicate-do-db = test_db

replicate-ignore-db = mysql,information_schema,performance_schema

replicate-ignore-table=test_db.tb_test#不同步数据库的表

Ø 100同步到95 配置命令

100命令行执行：

mysql -uroot -ppassword123

grant replication slave on *.* to 'test'@'192.168.2.95' identified by 'password123';

flush privileges;

show variables like 'log%';

show master status;

95执行下面命令(log_file 和 log_pos 都需要读取100的结果)

mysql -uroot -ppassword123

stop slave;

change master to master_host='192.168.2.100',master_user='test',master_password='password123',master_log_file='mysql-bin.000001',master_log_pos= 80094;

start slave;

show slave status;

Ø 95 配置同步到 100

95执行

grant replication slave on *.* to 'test'@'192.168.2.100' identified by 'password123';

flush privileges;

show variables like 'log%';

show master status;

100执行

stop slave;

change master to master_host='192.168.2.95',master_user='test',master_password='password123',master_log_file='mysql-bin.000001',master_log_pos= 125389;

start slave;

show slave status;