mysql双主模式和简单应对主键冲突问题


Mysql双机热备,简单的说,就是要保持两台数据库的数据同步。始终保持两个数据库数据一致。 主要有主备方式、双主方式;本文介绍双主的配置简单实现过程,实现双主互备,双主都可以写入。实现简单的负载均衡。
Mysql双主的优点:
1.可以做灾备,比如某台服务器宕机了,可以通过命令行切换。 
2.可以做负载均衡,可以将请求分摊到其中任何一台上,提高网站吞吐量。  
 
 
  1. 系统:Red Hat Enterprise Linux Server release 6.5 
  2. Mysql版本:5.5.36 
  3.  
  4. 主机分配: 
  5. Mysql-01    ip:192.168.40.101 
  6. Mysql-02    ip:192.168.40.102 
 
关于Mysql的安装过程这里要不详细说明了。直接配置主主复制过程;
其实主主跟主从差不多,主从的时候一个为主一个为备,而备服务器只能从主服务器上得到数据,保持数据同步。而主主复制时,两台主机的地位是一样,两台主机都能同时写入数据,同时互为各自的主从。
 
实施过程:
修改两台Mysql服务器上的配置文件:如果有可以去掉#号开启,没有则手动添加。
 
Mysql-01修改后的:
 
  1. #server-id 
  2. server-id=101 
  3. log-bin=mysql-bin 
  4. #relay log 
  5. relay-log=mysql-relay 
  6. binlog_format=mixed         #这个是日志格式。 
Mysql-02修改后的:
 
  1. #server-id 
  2. server-id=102 
  3. log-bin=mysql-bin 
  4. #relay log 
  5. relay-log=mysql-relay 
  6. binlog_format=mixed 
 
 
重启Mysql服务后,登陆进去建立同步的帐号:(两台主机上都要操作)
 
  1. grant replication client,replication slave on *.* to 'repl'@'192.168.40.%' identified by 'repl123'
 
接下来要互相切换到对方的slave端。
Mysql-01上bin-log日志数据记录:
 
Mysql-02上bin-log日志数据记录:
 
由于我两台机Mysql都是刚安装完未做任何修改,所以记录位置也一样。
 
即然一样那就好办多了:
在Mysql-01主机上执行:
 
 
  1. change master to 
  2. master_host='192.168.40.102'
  3. master_user='repl'
  4. master_password='repl123'
  5. master_log_file='mysql-bin.000001'
  6. master_log_pos=107; 
  
在Mysql-02主机上执行:
 
  1. change master to 
  2. master_host='192.168.40.101'
  3. master_user='repl'
  4. master_password='repl123'
  5. master_log_file='mysql-bin.000001'
  6. master_log_pos=107; 
 
接下来在两台Mysql服务器上都启动slave;
 
  1. start slave; 
 
通过查看slave状态可以看出,两台主机的主从同步已经在等待状态了。
 
 
接下来创建一个数据库的测试表;
 
 
  1. mysql> create database web58jb character set utf8; 
  2. mysql> create table user_tb( 
  3.     -> id int not null primary key auto_increment, 
  4.     -> name varchar(20) not null default '' 
  5.     -> ); 
  6. Query OK, 0 rows affected (0.01 sec) 
 
在Mysql-01上插入数据: 
 
  1. mysql> insert into user_tb (name) values('limi'); 
  2. Query OK, 1 row affected (0.02 sec) 
  3.   
  4. mysql> insert into user_tb (name) values('li5'); 
  5. Query OK, 1 row affected (0.01 sec) 
 
在Mysql-02查询,并插入数据; 
 
 
  1. mysql> insert into user_tb (name) values('李小芳'); 
  2. Query OK, 1 row affected (0.00 sec) 
 
再回到Mysql-01查询:
 
 
通过上面可以看到数据已经成功互相同步了,这样就已经实现各自互为主从的双主模式了。
但是,由于两台主机的地位是一样的,都可以写入数据,那么如何避免主键的数据冲突呢?我们建表的时候ID是使用自增的主键,如果两台主机都同时写入ID=1的数据那就冲突了。
 
简单的解决方法:
Mysql-01:通过1、3、5、7增长
Mysql-02:通过2、4、6、8增长
 
使用命令:
Mysql-01上使用:
 
  1. set session auto_increment_increment=2;      
  2. set session auto_increment_offset=1;                 
  3. set global auto_increment_increment=2; 
  4. set global auto_increment_offset=2; 
 
 
Mysql-02上使用:
 
  1. set session auto_increment_increment=2;  
  2. set session auto_increment_offset=2;                 
  3. set global auto_increment_increment=2; 
  4. set global auto_increment_offset=2; 
 
 上面两段,在真实服务器上操作是要写入到配置文件里。
 
接下来再测试一次;
在Mysql-02插入数据:
 
  1. mysql> insert into user_tb(name) values('h007'); 
  2. Query OK, 1 row affected (0.01 sec) 
  3.   
  4. mysql> insert into user_tb(name) values('h008'); 
  5. Query OK, 1 row affected (0.02 sec) 
 
可以看到数据是以2增加,跟上面的配置一样:走2、4、6、8
 
Mysql-01上插入数据:
 
 
  1. mysql> insert into user_tb(name) values('k009'); 
  2. Query OK, 1 row affected (0.00 sec) 
  3. mysql> insert into user_tb(name) values('101-kk'); 
  4. Query OK, 1 row affected (0.01 sec) 
 
可以看到数据同样是以2增长,走1、3、5、7
 
这样就即可以实现负载均衡,也同时解决到自增主键冲突的问题。 
 
如果上面双主只做简单的热备,就是只有一台可以,另一台处于standby状态,如果主服务宕机,即可快速切换到另一台服务器上,可以直接在一台服务器上的my.cnf配置里写上read-only=1即可。