Mysql Fabric High Availability cluster shard function test

One, MySQL fabric high-availability cluster A database crashes, does not affect the integrity of the data

1. Pre-Test preparation

A) View the server status of the Group_id-1 cluster group

Mysqlfabric Group Lookup_servers Group_id-1

return Result:

Command: {success = True return = [{' Status ': ' PRIMARY ', ' server_uuid ': ' 7a45f71d-7934-11e4-9e8c-782bcb74823a ', ' mode ': ' Read_write ', ' Weight ': 1.0, ' address ': ' 192.168.1.71:3306 '}, {' Status ': ' Secondary ', ' server_uuid ': ' 9cf162ca-7934-11e4-9e8d-782bcb1b6b98 ', ' mode ': ' read_only ', ' Weight ': 1.0, ' address ': ' 192.168.1.76:3306 '}, {' Status ' : ' Secondary ', ' server_uuid ': ' ae94200b-7932-11e4-9e81-a4badb30e16b ', ' mode ': ' read_only ', ' Weight ': 1.0, ' address ': ' 192.168.1.70:3306 '}] activities =}

At this time primary machine for machine: 192.168.1.71:3306,group_id-1 Cluster Group normal

b) View the data in the Group_id-1 cluster group

Mysql-p 3306-h 192.168.1.76-u root-e "select * from Test.subscribers" mysql-p 3306-h 192.168.1.71-u root-e "Select * FROM Test.subscribers "mysql-p 3306-h 192.168.1.70-u root-e" select * from Test.subscribers "

The returned results are as follows:

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

| Sub_no | first_name | last_name |

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

| 500 | Billy | Joel |

| 1500 | Arthur | Askey |

| 5000 | Billy | Fish |

| 17542 | Bobby | Ball |

| 22 | Billy | Bob |

| 8372 | Banana | Mans |

| 93846 | Bill | Ben |

| 15050 | John | Smith |

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

c) Use the Python interface to view the data table of the database test subscribers all data after the Shard

Python read_table_ha.py

The returned results are as follows:

(U ' Billy ', U ' Bob ')

(U ' Billy ', U ' Fish ')

(U ' Billy ', U ' Joel ')

(U ' Arthur ', U ' Askey ')

(U ' Banana ', U ' man ')

(U ' Billy ', U ' Fish ')

(U ' Bill ', U ' Ben ')

(U ' Jimmy ', U ' White ')

(U ' John ', U ' Smith ')

(U ' Bobby ', U ' Ball ')

2. Start testing

A) activation failure automatic switching

Even if the master role is selected by fabric, Fanric does not automatically switch the secondary role to the master role when the master role goes down, so the configuration of the HA cluster group needs to be changed to automatically switch roles

Mysqlfabric Group Activate Group_id-1

b) Stop the master role in the Group_id-1 group, that is, the DB instance 192.168.1.71:3306, and then view the state of the fabric

Mysqlfabric Group Lookup_servers Group_id-1

return Result:

Command: {success = True return = [{' Status ': ' Faulty ', ' server_uuid ': ' 7a45f71d-7934-11e4-9e8c-782bcb74823a ', ' mode ': ' Read_write ', ' Weight ': 1.0, ' address ': ' 192.168.1.71:3306 '}, {' Status ': ' Secondary ', ' server_uuid ': ' 9cf162ca-7934-11e4-9e8d-782bcb1b6b98 ', ' mode ': ' read_only ', ' Weight ': 1.0, ' address ': ' 192.168.1.76:3306 '}, {' Status ' : ' PRIMARY ', ' server_uuid ': ' ae94200b-7932-11e4-9e81-a4badb30e16b ', ' mode ': ' Read_write ', ' Weight ': 1.0, ' address ': ' 192.168.1.70:3306 '}] activities =}

Where the state of the DB instance 192.168.1.71:3306 is Faulty,mysql fabric automatically detects the failure of the HA group and automatically elects the slave role as the primary role

c) View the data in the Group_id-1 cluster group

Mysql-p 3306-h 192.168.1.76-u root-e "select * from Test.subscribers" mysql-p 3306-h 192.168.1.70-u root-e "Select * FROM Test.subscribers "

return Result:

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

| Sub_no | first_name | last_name |

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

| 500 | Billy | Joel |

| 1500 | Arthur | Askey |

| 5000 | Billy | Fish |

| 17542 | Bobby | Ball |

| 22 | Billy | Bob |

| 8372 | Banana | Mans |

| 93846 | Bill | Ben |

| 15050 | John | Smith |

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

d) Use the Python interface to view the data table of the database test subscribers all data after the Shard

Python read_table_ha.py

return Result:

(U ' Billy ', U ' Bob ')

(U ' Billy ', U ' Fish ')

(U ' Billy ', U ' Joel ')

(U ' Arthur ', U ' Askey ')

(U ' Banana ', U ' man ')

(U ' Billy ', U ' Fish ')

(U ' Bill ', U ' Ben ')

(U ' Jimmy ', U ' White ')

(U ' John ', U ' Smith ')

(U ' Bobby ', U ' Ball ')

Conclusion: The data returned from the above test indicates that when the master role in the HA Group (group_id-1) crashes, MySQL fabric automatically elects a slave role as master and can query the original data normally, so when MySQL A database in a fabric high availability cluster crashes and does not affect the integrity of the data

PS: Manually restore the fault state of the database instance

There are four server states in the MySQL fabric high availability cluster: Primary, secondary, faulty, spare

Shutdown primary,primary cannot be removed from the group, you need to use Mysqlfabric Group demote group_id-1 to close primary in the group, do not re-elect a new master, and do not turn off fault detection

A) when one of the DB instances crashes, you can remove the DB instance from the group, start the DB instance, and then add it again, for example:

Mysqlfabric Group Remove group_id-1 7a45f71d-7934-11e4-9e8c-782bcb74823amysqlfabric Group Add group_id-1 192.168.1.71:3306mysqlfabric Group Lookup_servers Group_id-1

return Result:

Command: {success = True return = [{' Status ': ' Secondary ', ' server_uuid ': ' 7a45f71d-7934-11e4-9e8c-782bcb74823a ', ' mode ' : ' Read_Only ', ' Weight ': 1.0, ' address ': ' 192.168.1.71:3306 '}, {' Status ': ' Secondary ', ' server_uuid ': ' 9cf162ca-7934-11e4-9e8d-782bcb1b6b98 ', ' mode ': ' read_only ', ' Weight ': 1.0, ' address ': ' 192.168.1.76:3306 '}, {' Status ' : ' PRIMARY ', ' server_uuid ': ' ae94200b-7932-11e4-9e81-a4badb30e16b ', ' mode ': ' Read_write ', ' Weight ': 1.0, ' address ': ' 192.168.1.70:3306 '}] activities =}

b) When all servers in the HA group are down, the state of the fabric does not automatically recover after the instance restarts, and the command must be executed first: Mysqlfabric group demote group_id-1, Because the state of the server cannot be directly modified by faulty to secondary, the state needs to be modified to spare, and changed to secondary, as follows:

Mysqlfabric Group Demote group_id-1mysqlfabric Group Deactivate group_id-1mysqlfabric server Set_status 7a45f71d-7934-11e4-9e8c-782bcb74823a sparemysqlfabric Server Set_status 7a45f71d-7934-11e4-9e8c-782bcb74823a Secondary

# # #当所有的数据库实例都为secondary状态时, the master role can be elected with the following commands:

Mysqlfabric Group Promote Group_id-1

Second, MySQL fabric high-availability cluster storage equalization

1. Pre-Test preparation

A) test script

Cat test_add_subs_shards.py

Import mysql.connectorfrom mysql.connector import fabricimport mathdef add_ Subscriber (Conn, sub_no, first_name, last_name):     conn.set_property ( tables=["Test.subscribers"], key=sub_no,         mode=fabric . Mode_readwrite)     cur = conn.cursor ()     cur.execute (          "insert into subscribers values  (%s, %s,  %s) ",         (sub_no, first_name, last_name)          ) Conn = mysql.connector.connect (     fabric={"host"  :  "localhost",  "port"  : 32274,  "username":  "admin",           "Password"  :  "admin"},    user= " Root ",  database=" test ", &NBSp;password= "",     autocommit=true) Conn.set_property (tables=["Test.subscribers"],  Scope=fabric. scope_local) For num in range:     add_subscriber (conn,  "%s"  %  num,  "k%s"  % num,  "kw%s"  % num)

2. Start Hash shard test

A) Insert 10 records, when three servers in the Group_id-1 group are normal, and only one server in the GROUP_ID-2 group is functioning properly,

The data for the Group_id-1 group are as follows:

650) this.width=650; "style=" border-bottom:0px;border-left:0px;border-top:0px;border-right:0px; "title=" Wps2235.tmp "border=" 0 "alt=" wps2235.tmp "src=" Http://s3.51cto.com/wyfs02/M01/57/47/wKioL1SXpo_ Ds9pfaaa38tnhnao286.jpg "height=" 118 "/>

The data for the Group_id-2 group are as follows:

650) this.width=650; "style=" border-bottom:0px;border-left:0px;border-top:0px;border-right:0px; "title=" Wps2236.tmp "border=" 0 "alt=" wps2236.tmp "src=" http://s3.51cto.com/wyfs02/M01/57/4A/ Wkiom1sxpefbogxjaaatqemkpku008.jpg "height="/>

b) Test insert 10,000 records, GROUP_ID-1 Group and Group_id-2 Group have three servers working properly

After sharding, the number of data rows inserted by the Group_id-1 group is as follows:

Mysql-p 3306-h 192.168.1.70-u root-e "SELECT COUNT (*) from Test.subscribers"

return Result:

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

| COUNT (*) |

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

| 7138 |

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

After sharding, the number of data rows inserted by the Group_id-2 group is as follows:

Mysql-p 3309-h 192.168.1.76-u root-e "SELECT COUNT (*) from Test.subscribers"

return Result:

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

| COUNT (*) |

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

| 2903 |

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

c) Test insert 100,000 records, GROUP_ID-1 Group and Group_id-2 Group have three servers working properly

After sharding, the number of data rows inserted by the Group_id-1 group is as follows:

Mysql-p 3306-h 192.168.1.70-u root-e "SELECT COUNT (*) from Test.subscribers"

return Result:

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

| COUNT (*) |

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

| 78719 |

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

After sharding, the number of data rows inserted by the Group_id-2 group is as follows:

Mysql-p 3309-h 192.168.1.76-u root-e "SELECT COUNT (*) from Test.subscribers"

return Result:

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

| COUNT (*) |

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

| 31321 |

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

d) Test insert 1 million records, Group_id-1 Group and Group_id-2 Group have three servers working properly

After sharding, the number of data rows inserted by the Group_id-1 group is as follows:

Mysql-p 3306-h 192.168.1.70-u root-e "SELECT COUNT (*) from Test.subscribers"

return Result:

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

| COUNT (*) |

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

| 794287 |

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

After sharding, the number of data rows inserted by the Group_id-2 group is as follows:

Mysql-p 3309-h 192.168.1.76-u root-e "SELECT COUNT (*) from Test.subscribers"

return Result:

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

| COUNT (*) |

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

| 315752 |

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

Add a new Ha group (group_id-3) to shard the HA Group (group_id-1) as follows:

A) on the machine: 192.168.1.71 192.168.1.76 192.168.1.230 A DB instance, added as a group group_id-3

Mysqlfabric Group Add group_id-3 192.168.1.71:3313mysqlfabric Group add group_id-3 192.168.1.76:3315mysqlfabric Group Add Group_id-3 192.168.1.230:3317

Election primary role

Mysqlfabric Group Promote Group_id-3

b) Resetting the HA group group_id-3 all DB instances

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 331 7-h 192.168.1.230-u root-e "Reset Master"

c) View the shard_id value of the HA group (group_id-1) and view the fabric database on the storage machine

Mysql-h 127.0.0.1-p3306-u root-e ' select * from Fabric.shards '

return Result:

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

| shard_id | group_id | State |

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

| 4 | group_id-1 | ENABLED |

| 5 | group_id-2 | ENABLED |

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

D) The HA Group (group_id-1) is partitioned to separate the

Mysqlfabric sharding Split_shard 4 group_id-3

PS: If the Shard execution is unsuccessful, the error is as follows:

Last_io_error:got fatal Error 1236 from master if 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 6 21. '

Workaround:

Dump the data from the master role as follows:

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

Shut down the slave machine.

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 331 7-h 192.168.1.230-u root </data/full_backup_master.sqlmysql-p 3317-h 192.168.1.230-u root-e "Start Slave"

Performing the Shard again will report the following error:

Procedure: {uuid = 4da230c2-31c3-4242-bd88-ccafd51bfac1, finished = True, success = False, return = Backuperror: (' ERR or while restoring the backup using the MySQL client\n,%s ', "ERROR 1840 (HY000) at line @ file: ' Mysql_192.168.1.76_3 306.sql ': @ @GLOBAL. Gtid_purged can only is set when @ @GLOBAL. Gtid_executed is empty.\n "), activities =}

Workaround:

Perform "Reset Master" on all DB instances of the HA Group (group_id-3), and then Shard to

e) Now HA Group has group_id-1, Group_id-2, group_id-3, try to insert 100 records

The number of data rows inserted by the group_id-1 are:

Mysql-p 3306-h 192.168.1.70-u root-e "SELECT COUNT (*) from test.subscribers where sub_no between 20000 and 20100"

return Result:

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

| COUNT (*) |

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

| 70 |

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

The number of data rows inserted by Group_id-2 and group_id-3 are:

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"

return Result:

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

| COUNT (*) |

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

| 30 |

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

With the SELECT statement, the HA group group_id-2, group_id-3 inserted data is the same

3.hash Shard Test Conclusion

When 10,000 records are inserted, the HA Group (group_id-1) inserts 7,138 records, and the HA Group (group_id-2) inserts 2,903 Records

When 100,000 records are inserted, the HA Group (group_id-1) inserts 78,719 records, and the HA Group (group_id-2) inserts 31,321 Records

When 1 million records are inserted, the HA Group (group_id-1) inserts 794,287 records, and the HA Group (group_id-2) inserts 315,752 Records

Therefore, after the MySQL fabric Gaoji Group Hash shard, the Shard ratio is 7:3, the load is not very balanced

Third, investigate how PHP, Erlang and other languages connect MySQL Fabric

At present, the fabric provides PHP, Java, python three categories of API, you can programmatically manipulate the fabric.

The Python script is as shown above

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

This article is from the "Ops Pawn" blog, declined to reprint!

Mysql Fabric High Availability cluster shard function test