mysqlHA双主热备测试是否同键值冲突

1. vip先是192.168.30.168在redis2
在redis2上创建存储过程插入50000数据
mysql-redis2> delimiter &&
mysql-redis2> create procedure  test() begin declare i int; set i =  0; create table if not exists sbtest.test(id int unsigned not null auto_increment,primary key(id),name varchar(20)); while i < 50000 do insert into sbtest.test(name) values('aa'
Query OK, 0 rows affected (0.00 sec)

mysql-redis2> delimiter ;
mysql-redis2> call test();
Query OK, 1 row affected (31.11 sec)

mysql-redis2> select count(*) from sbtest.test;
+----------+
| count(*) |
+----------+
|    50000 |
+----------+
1 row in set (0.02 sec)

mysql-redis2> select * from sbtest.test limit 10;
+----+------+
| id | name |
+----+------+
|  2 | aa   |
|  4 | aa   |
|  6 | aa   |
|  8 | aa   |
| 10 | aa   |
| 12 | aa   |
| 14 | aa   |
| 16 | aa   |
| 18 | aa   |
| 20 | aa   |
+----+------+
10 rows in set (0.00 sec)

2.停止redis1上的mysqld复制进程, 模拟redis2的mysql写入一批数据后故障导致其binlog没传送到redis1的mysql
mysql-redis1> stop slave;

3.再在redis2的mysql上创建存储过程,插入模拟的未能传送到redis1上mysql的数据
mysql-redis2> delimiter &&
mysql-redis2> create procedure  test2() begin declare i int; set i =  0;  while i < 5000 do insert into sbtest.test(name) values('bb'); set i = i + 1; end while ; end;&&
Query OK, 0 rows affected (0.01 sec)

mysql-redis2> delimiter ;
mysql-redis2>
mysql-redis2>
mysql-redis2> call test2();
Query OK, 1 row affected (3.14 sec)

mysql-redis2> select * from sbtest.test limit 50000,10;
+--------+------+
| id     | name |
+--------+------+
| 100002 | bb   |
| 100004 | bb   |
| 100006 | bb   |
| 100008 | bb   |
| 100010 | bb   |
| 100012 | bb   |
| 100014 | bb   |
| 100016 | bb   |
| 100018 | bb   |
| 100020 | bb   |
+--------+------+
10 rows in set (0.01 sec)

mysql-redis2>

4.接着停止redis2上的mysql和keepalived, 模拟故障, 此时vip漂到redis1
[root@redis2 ~]# /etc/init.d/mysqld stop
Shutting down MySQL...                                     [  OK  ]
[root@redis2 ~]# /etc/init.d/keepalived stop
Stopping keepalived:                                       [  OK  ]
[root@redis2 ~]#

5.vip已经漂到redis1
[user01@redis1 ~]$ ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP qlen 1000
    link/ether 40:f2:e9:94:d9:12 brd ff:ff:ff:ff:ff:ff
    inet 192.168.30.160/24 brd 192.168.30.255 scope global eth0
    inet 192.168.30.168/32 scope global eth0
    inet6 fe80::42f2:e9ff:fe94:d912/64 scope link
       valid_lft forever preferred_lft forever
3: eth1: <BROADCAST,MULTICAST> mtu 1500 qdisc noop state DOWN qlen 1000
    link/ether 40:f2:e9:94:d9:13 brd ff:ff:ff:ff:ff:ff
4: eth2: <BROADCAST,MULTICAST> mtu 1500 qdisc noop state DOWN qlen 1000
    link/ether 40:f2:e9:94:d9:14 brd ff:ff:ff:ff:ff:ff
5: eth3: <BROADCAST,MULTICAST> mtu 1500 qdisc noop state DOWN qlen 1000
    link/ether 40:f2:e9:94:d9:15 brd ff:ff:ff:ff:ff:ff
7: usb0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 42:f2:e9:94:d9:11 brd ff:ff:ff:ff:ff:ff
    inet 169.254.95.120/24 brd 169.254.95.255 scope global usb0
    inet6 fe80::40f2:e9ff:fe94:d911/64 scope link
       valid_lft forever preferred_lft forever
[user01@redis1 ~]$

6.使用vip连接mysql, 此时连的是redis1上的mysql
[root@redis1 ~]# mysql -u root -p -h 192.168.30.168
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 5.6.14-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql-redis1>
mysql-redis1> 
mysql-redis1>  select @@server_id;
+-------------+
| @@server_id |
+-------------+
|       30160 |
+-------------+
1 row in set (0.00 sec)

mysql-redis1>  use sbtest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql-redis1>  select * from sbtest.test limit 50000,10;
Empty set (0.02 sec)

7.在redis1的mysql上创建存储过程,模拟切换后的新主写入的数据
mysql-redis1>  delimiter &&
mysql-redis1>  create procedure test3()
    -> begin
    -> declare i int;
    -> set i = 0;
    -> while i < 10000 do
    -> insert into sbtest.test(name) values('cc');
    -> set i = i + 1;
    -> end while ;
    -> end ;
    -> &&
Query OK, 0 rows affected (0.00 sec)

mysql-redis1>  delimiter ;
mysql-redis1>  call test3();
Query OK, 1 row affected (5.92 sec)

mysql-redis1>  select * from sbtest.test limit 50000,10;
+--------+------+
| id     | name |
+--------+------+
| 100001 | cc   |
| 100003 | cc   |
| 100005 | cc   |
| 100007 | cc   |
| 100009 | cc   |
| 100011 | cc   |
| 100013 | cc   |
| 100015 | cc   |
| 100017 | cc   |
| 100019 | cc   |
+--------+------+
10 rows in set (0.02 sec)

mysql-redis1>

8.在redis1上打开mysql的复制
mysql-redis1>  start slave;
Query OK, 0 rows affected (0.01 sec)

在redis2上启动keepalived, mysql并打开mysql的复制
[root@redis2 ~]# /etc/init.d/keepalived start
Starting keepalived:                                       [  OK  ]
[root@redis2 ~]# /etc/init.d/mysqld start
Starting MySQL....                                         [  OK  ]

mysql-redis2> start slave;
Query OK, 0 rows affected (0.01 sec)

9.查询redis2上的mysql,新主后面写入的记录已经同步过来
mysql-redis2> select * from sbtest.test limit 50000,10;
+--------+------+
| id     | name |
+--------+------+
| 100001 | cc   |
| 100002 | bb   |
| 100003 | cc   |
| 100004 | bb   |
| 100005 | cc   |
| 100006 | bb   |
| 100007 | cc   |
| 100008 | bb   |
| 100009 | cc   |
| 100010 | bb   |
+--------+------+
10 rows in set (0.05 sec)

mysql-redis2> exit
Bye

10.查询redis1上的mysql,因故障未能及时同步的记录已经同步到新主上
mysql-redis1> select * from sbtest.test limit 50000,10;
+--------+------+
| id     | name |
+--------+------+
| 100001 | cc   |
| 100002 | bb   |
| 100003 | cc   |
| 100004 | bb   |
| 100005 | cc   |
| 100006 | bb   |
| 100007 | cc   |
| 100008 | bb   |
| 100009 | cc   |
| 100010 | bb   |
+--------+------+
10 rows in set (0.03 sec)