尘封网

让学习成为一种习惯!

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

Mysql双机热备,简单的说,就是要保持两台数据库的数据同步。始终保持两个数据库数据一致。 主要有主备方式、双主方式;本文介绍双主的配置简单实现过程,实现双主互备,双主都可以写入;实现简单的负载均衡。

Mysql双主的优点:

  • 可以做灾备,比如某台服务器宕机了,可以通过命令行切换。
  • 可以做负载均衡,可以将请求分摊到其中任何一台上,提高网站吞吐量。

系统:Red Hat Enterprise Linux Server release 6.5
Mysql版本:5.5.36

主机分配:
Mysql-01 ip:192.168.40.101
Mysql-02 ip:192.168.40.102

关于Mysql的安装过程这里要不详细说明了。直接配置主主复制过程;
其实主主跟主从差不多,主从的时候一个为主一个为备,而备服务器只能从主服务器上得到数据,保持数据同步。而主主复制时,两台主机的地位是一样,两台主机都能同时写入数据,同时互为各自的主从。

实施过程:
修改两台Mysql服务器上的配置文件:如果有可以去掉#号开启,没有则手动添加。

Mysql-01修改后的:

#server-id
server-id=101
log-bin=mysql-bin
#relay log
relay-log=mysql-relay
binlog_format=mixed         #这个是日志格式

Mysql-02修改后的:

#server-id
server-id=102
log-bin=mysql-bin
#relay log
relay-log=mysql-relay
binlog_format=mixed

重启Mysql服务后,登陆进去建立同步的帐号:(两台主机上都要操作)

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主机上执行:

change master to
master_host='192.168.40.102',
master_user='repl',
master_password='repl123',
master_log_file='mysql-bin.000001',
master_log_pos=107;

在Mysql-02主机上执行:

change master to
master_host='192.168.40.101',
master_user='repl',
master_password='repl123',
master_log_file='mysql-bin.000001',
master_log_pos=107;

接下来在两台Mysql服务器上都启动slave;

start slave;

通过查看slave状态可以看出,两台主机的主从同步已经在等待状态了。

接下来创建一个数据库的测试表;

mysql> create database web58jb character set utf8;
mysql> create table user_tb(
    -> id int not null primary key auto_increment,
    -> name varchar(20) not null default ''
    -> );
Query OK, 0 rows affected (0.01 sec)

在Mysql-01上插入数据:

mysql> insert into user_tb (name) values('limi');
Query OK, 1 row affected (0.02 sec)

mysql> insert into user_tb (name) values('li5');
Query OK, 1 row affected (0.01 sec)

在Mysql-02查询,并插入数据;

mysql> insert into user_tb (name) values('李小芳');
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上使用:

set session auto_increment_increment=2;
set session auto_increment_offset=1;
set global auto_increment_increment=2;
set global auto_increment_offset=2;

Mysql-02上使用:

set session auto_increment_increment=2;
set session auto_increment_offset=2;
set global auto_increment_increment=2;
set global auto_increment_offset=2;

上面两段,在真实服务器上操作是要写入到配置文件里。

接下来再测试一次;
在Mysql-02插入数据:

mysql> insert into user_tb(name) values('h007');
Query OK, 1 row affected (0.01 sec)

mysql> insert into user_tb(name) values('h008');
Query OK, 1 row affected (0.02 sec)


可以看到数据是以2增加,跟上面的配置一样:走2、4、6、8

Mysql-01上插入数据:

mysql> insert into user_tb(name) values('k009');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user_tb(name) values('101-kk');
Query OK, 1 row affected (0.01 sec)


可以看到数据同样是以2增长,走1、3、5、7

这样就即可以实现负载均衡,也同时解决到自增主键冲突的问题。

如果上面双主只做简单的热备,就是只有一台可以,另一台处于standby状态,如果主服务宕机,即可快速切换到另一台服务器上,可以直接在一台服务器上的my.cnf配置里写上read-only=1即可。

提醒:本文最后更新于 1249 天前,文中所描述的信息可能已发生改变,请谨慎使用。