mysql主主互备工作原理_mysql主主互备

一、两台安装mysql

master-A:192.168.152.128

master-B:192.168.152.129

#yum -y install mysql-server mysql

二、配置主主同步备份文件

(1)授权用户

#server mysqld start

master-A>grant replication slave,file on *.* to 'repl1'@'192.168.152.129' identified by '123456';

master-B>grant replication slave,file on *.* to 'repl2'@'192.168.152.128' identified by '123456';

#server mysqld stop

(2)编辑配置文件vim /etc/my.cnf  加入以下内容

log-bin=mysql-bin                                               //启动二进制日志系统

server-id=1//本机数据库ID，另一台为2binlog-do-db=test                                               //二进制同步的数据库名

binlog-ignore-db=mysql                                      //避免同步mysql用户配置

replicate-ignore-db=mysql                                   //屏蔽对mysql库的同步

replicate-do-db=test                                            //同步数据库名称

log-slave-updates

slave-skip-errors=all

sync-binlog=1

auto-increment-increment=2

auto-increment-offset=1//另一台也改为2

#server  mysqld start

(3)检测

>flush tables with read lock\G                            (锁库表)

> show master status\G                                        //查看

(master-A)                                                  (master-B)

两个File会不一样，将A的mysql重启下即可

(4)用change master 语句指定同步位置

master-A>change master to master_host='192.168.152.129',master_user='repl2', master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=98;

master-B>change master to master_host='192.168.152.128',master_user='repl1', master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=98;

A,B都要启动

> start slave;

> show slave status\G;                                        //查看

IO与SQL 都是YES 即可，(有人说IO是NO解决办法是先stop slave在reset slave在start slave就正常了，不过我做的时候并不成功，毕竟可能会是个方法。)

mysql> unlock tables;     //解锁表(因为之前锁表了，所以这里要解锁)

三、测试

在master-A上使用test库，里面没表，创建个aa表

在master-B上使用test库，查看发现有aa表

在master-B上插入数据

在master-A上可以查看到

到此mysql主主互备就结束了。