mysqlfabric性能_MysqlFabric高可用集群分片功能测试

分片后&＃xff0c;group_id-1组插入的数据行数如下&＃xff1a;

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

返回结果&＃xff1a;

&＃43;----------&＃43;

| count(*) |

&＃43;----------&＃43;

| 794287 |

&＃43;----------&＃43;

分片后&＃xff0c;group_id-2组插入的数据行数如下&＃xff1a;

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

返回结果&＃xff1a;

&＃43;----------&＃43;

| count(*) |

&＃43;----------&＃43;

| 315752 |

&＃43;----------&＃43;

添加一个新HA组(group_id-3)&＃xff0c;对HA组(group_id-1)进行分片&＃xff0c;操作如下&＃xff1a;

a)    在机器&＃xff1a;192.168.1.71 192.168.1.76 192.168.1.230上各起一个数据库实例&＃xff0c;添加为组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值&＃xff0c;在储存机查看fabric数据库

mysql -h 127.0.0.1 -P3306 -u root -e &＃39;select * from fabric.shards&＃39;

返回结果&＃xff1a;

&＃43;----------&＃43;------------&＃43;---------&＃43;

| shard_id | group_id | state |

&＃43;----------&＃43;------------&＃43;---------&＃43;

| 4 | group_id-1 | ENABLED |

| 5 | group_id-2 | ENABLED |

&＃43;----------&＃43;------------&＃43;---------&＃43;

d)    对HA组(group_id-1)进行分片分离&＃xff0c;将

mysqlfabric sharding split_shard 4 group_id-3

ps&＃xff1a;如果这里执行分片不成功&＃xff0c;错误如下&＃xff1a;

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

解决方法&＃xff1a;

将master角色的数据dump出来&＃xff0c;如下&＃xff1a;

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"

再次执行分片&＃xff0c;会报以下错误&＃xff1a;

Procedure :

{ uuid &＃61; 4da230c2-31c3-4242-bd88-ccafd51bfac1,

finished &＃61; True,

success &＃61; False,

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

activities &＃61;

}

解决方法&＃xff1a;

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

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

group_id-1的插入的数据行数有&＃xff1a;

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

返回结果&＃xff1a;

&＃43;----------&＃43;

| count(*) |

&＃43;----------&＃43;

| 70 |

&＃43;----------&＃43;

group_id-2、group_id-3的插入的数据行数有&＃xff1a;

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"

返回结果&＃xff1a;

&＃43;----------&＃43;

| count(*) |

&＃43;----------&＃43;

| 30 |

&＃43;----------&＃43;

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

3.hash分片测试结论

在插入1万条记录时&＃xff0c;HA组(group_id-1)插入了7138条记录&＃xff0c;HA组(group_id-2)插入了2903条记录

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

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

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

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

目前Fabric提供了Php, Java, Python三大类的API&＃xff0c;大家可以通过编程的方式来操作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、重新初始化环境&＃xff0c;对数据重新分片&＃xff0c;如上

2、测试mysqlfabric的ips

43/4<1234>