Mysql主从配置

服务器a ip:192.168.52.131

服务器b ip:192.168.52.132

第一步:创建同步用户

A=>

grant replication slave on . to 'comuser'@'192.168.1.90' identified by '123456';

flush privileges;

B=>

grant replication slave on . to 'comuser'@'192.168.1.106' identified by '123456';

flush privileges;

二 修改 my.cnf 配置文件,为其添加以下内容:

A=>

server-id=1

binlog-do-db=test      #test是需要同步的数据库

binlog-ignore-db=mysql   #不需要同步的数据库

replicate-do-db=test    #test是需要同步的数据库

replicate-ignore-db=mysql,information_schema #不需要同步的数据库

log-slave-updates

sync_binlog=1

auto_increment_offset=1

auto_increment_increment=2

slave-skip-errors=all

B=>

server-id=2

binlog-do-db=test

binlog-ignore-db=mysql

replicate-do-db=test

replicate-ignore-db=mysql,information_schema

log-slave-updates

sync_binlog=1

auto_increment_offset=2

auto_increment_increment=2

slave-skip-errors=all

三、分别重启A和B服务器的MySQL服务

四、分别在服务器A、B 上查看做为主服务器状态

A=>

flush tables with read lock;

show master status\G;

B=>

flush tables with read lock;

show master status\G;

五、分别在服务器A、B上用change master语句指定同步位置 :

A=>

change master to master_host='192.168.1.90',master_user='comuser',master_password='123456',master_log_file='mysql-bin.000007',master_log_pos=339;

B=>

change master to master_host='192.168.52.132',master_user='root',master_password='123456',master_log_file='mysql-bin.000008',master_log_pos=107;

A=>

unlock tables;

B=>

unlock tables;

六、分别在服务器A、B上启动从服务器线程

A=>

start slave;

B=>

start slave;

A=>

show slave status\G;

B=>

show slave status\G;