mysql fabric 性能_Mysql Fabric高可用集群分片功能测试

分片后，group_id-1组插入的数据行数如下：

mysql -P 3306 -h 192.168.1.70 -u root -e "select count(*) from test.subscribers"

返回结果：

+----------+

| count(*) |

+----------+

| 794287 |

+----------+

分片后，group_id-2组插入的数据行数如下：

mysql -P 3309 -h 192.168.1.76 -u root -e "select count(*) from test.subscribers"

返回结果：

+----------+

| count(*) |

+----------+

| 315752 |

+----------+

添加一个新HA组(group_id-3)，对HA组(group_id-1)进行分片，操作如下：

a)    在机器：192.168.1.71 192.168.1.76 192.168.1.230上各起一个数据库实例，添加为组group_id-3

mysqlfabric group add group_id-3 192.168.1.71:3313

mysqlfabric group add group_id-3 192.168.1.76:3315

mysqlfabric group add group_id-3 192.168.1.230:3317

选举primary角色

mysqlfabric group promote group_id-3

b)    重置HA组group_id-3所有的数据库实例

mysql -P 3313 -h 192.168.1.71 -u root -e "reset master"

mysql -P 3315 -h 192.168.1.76 -u root -e "reset master"

mysql -P 3317 -h 192.168.1.230 -u root -e "reset master"

c)    查看HA组(group_id-1)的shard_id值，在储存机查看fabric数据库

mysql -h 127.0.0.1 -P3306 -u root -e 'select * from fabric.shards'

返回结果：

+----------+------------+---------+

| shard_id | group_id | state |

+----------+------------+---------+

| 4 | group_id-1 | ENABLED |

| 5 | group_id-2 | ENABLED |

+----------+------------+---------+

d)    对HA组(group_id-1)进行分片分离，将

mysqlfabric sharding split_shard 4 group_id-3

ps：如果这里执行分片不成功，错误如下：

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'could not find next log; the first event '' at 4, the last event read from './binlog.000003' at 621, the last byte read from './binlog.000003' at 621.'

解决方法：

将master角色的数据dump出来，如下：

mysqldump -h 192.168.1.76 -u root -P3315 --all-databases --flush-privileges --single-transaction --flush-logs --triggers --routines --events --hex-blob >/data/database/full_backup_master.sql

停掉slave机

mysql -P 3317 -h 192.168.1.230 -u root -e "stop slave"

mysql -P 3317 -h 192.168.1.230 -u root -e "reset master"

mysql -P 3317 -h 192.168.1.230 -u root

mysql -P 3317 -h 192.168.1.230 -u root -e "start slave"

再次执行分片，会报以下错误：

Procedure :

{ uuid = 4da230c2-31c3-4242-bd88-ccafd51bfac1,

finished = True,

success = False,

return = BackupError: ('Error while restoring the backup using the mysql client\n, %s', "ERROR 1840 (HY000) at line 24 in file: 'MySQL_192.168.1.76_3306.sql': @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.\n"),

activities =

}

解决方法：

在HA组(group_id-3)的全部数据库实例上执行“reset master”，再分片即可

e)    现在HA组有group_id-1、group_id-2、group_id-3，尝试插入100条记录

group_id-1的插入的数据行数有：

mysql -P 3306 -h 192.168.1.70 -u root -e "select count(*) from test.subscribers where sub_no between 20000 and 20100"

返回结果：

+----------+

| count(*) |

+----------+

| 70 |

+----------+

group_id-2、group_id-3的插入的数据行数有：

mysql -P 3309 -h 192.168.1.76 -u root -e "select count(*) from test.subscribers where sub_no between 20000 and 20100"

mysql -P 3313 -h 192.168.1.230 -u root -e "select count(*) from test.subscribers where sub_no between 20000 and 20100"

返回结果：

+----------+

| count(*) |

+----------+

| 30 |

+----------+

用select语句可知，HA组group_id-2、group_id-3插入的数据是一样的

3.hash分片测试结论

在插入1万条记录时，HA组(group_id-1)插入了7138条记录，HA组(group_id-2)插入了2903条记录

在插入10万条记录时，HA组(group_id-1)插入了78719条记录，HA组(group_id-2)插入了31321条记录

在插入100万条记录时，HA组(group_id-1)插入了794287条记录，HA组(group_id-2)插入了315752条记录

因此，对mysql fabric高集群进行hash分片后，分片比例为7:3，负载并不是十分均衡

三、调查PHP、Erlang等语言如何连接MySQL Fabric

目前Fabric提供了Php, Java, Python三大类的API，大家可以通过编程的方式来操作Fabric。

python脚本如上面所示

Connector/Python:http://dev.mysql.com/doc/mysql-utilities/1.5/en/connector-python-fabric.html

Connector/J:http://dev.mysql.com/doc/mysql-utilities/1.5/en/connector-j-fabric.html

Connector:http://php.net/manual/zh/book.mysqlnd-ms.php

四、mysqlfabric简单性能测试

1、重新初始化环境，对数据重新分片，如上

2、测试mysqlfabric的ips

43/4<1234>