Table of contents
Preamble
As we have seen in a previous post MySQL Fabric can simplify a lot the management of a traditional MySQL Replication environment. The split of read only and read write transaction still has to be managed at application level, Oracle did not go to the level of MaxScale for example with an automatic splitting of read write and read only transaction. Overall MySQl Fabric does not do better than what was available with Connector/J.
But what if your master server start to be overloaded with read write transactions ? How do you scale out this master server ? The Oracle answer is to use MySQL Cluster but the clear drawbacks is that you have to forget InnoDB storage engine and move to NDB storage engine and this can be difficult to achieve. One another good non-Oracle answer could be Galera Cluster but its optimistic way of locking can create you trouble and has to be managed by application. A third answer is to shard (split) your data across multiple server. Overall this is not new but MySQL Fabric aim at easing such quite complex implementation.
Every big objects of your data model will have to be sharded based on a shard key (either RANGE or HASH), the small objects (the dimension ones for example) can remain non-sharded and be available for all shards of the sharded objects. One big limitation of sharding is that any query can only access to one shard at a time, in other words the cross shard queries are forbidden.
For testing I have two virtual machines:
- server1.domain.com (192.168.56.101) is my MySQL server
- server3.domain.com (192.168.56.103) is my MySQL Fabric server (that also contains a MySQL instance for the MySQL Fabric repository)
They are all running Oracle Linux Server release 7.0 64 bits and MySQL 5.6.23 community edition. I have compiled and installed MySQL Utilities 1.5.3.
On server1.domain.com I configure three MySQL instances that are respectively running on port 3316, 3326 and 3336. First instance will be used for global group which is used to propagate schema updates to all tables in the sharding setup and updates to global tables throughout the sharding scheme (from official documentation). Two remaining instances will be used for the two sharding groups I’m gonna create.
Here we immediately see that for a perfectly high available architecture I would need to have a master/slave configuration for each group. Means that a minimum of six servers are required to implement a professional MySQL Fabric sharding architecture. This starts to be quite a lot of servers to manage and administer so expressly reserved for your highly critical applications…
I’m not re-entering in MySQL Fabric (part of MySQL Utilities package) installation has we have already seen it in previous post… Do not forget to create the fabric account on all instances.
All MySQL Fabric limitations are quite fair I would say, except maybe one that did not come immediately to my mind:
Sharding is not completely transparent to the application. While the application need not be aware of which server stores a set of rows and it doesn’t need to be concerned when that data is moved, it does need to provide the sharding key when accessing the database.
As we will see later on, your application will need to be completely redesigned to instruct on which shard the SQL commands should be executed…
MySQL Fabric sharding configuration
I start by creating three MySQL Fabric groups, the global group and two shard groups. The global group must only contains the skeleton and MySQL replication will be used to spread DDL to all shards. The global group is also used for global statements to be used on objects:
[root@server3 ~]# mysqlfabric group create group01-shard01 Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e Time-To-Live: 1 uuid finished success result ------------------------------------ -------- ------- ------ 87309979-579f-451b-ac59-d72cd44a0187 1 1 1 state success when description ----- ------- ------------- ------------------------------------------------------------- 3 2 1424790574.08 Triggered by <mysql.fabric.events.Event object at 0x20787d0>. 4 2 1424790574.08 Executing action (_create_group). 5 2 1424790574.09 Executed action (_create_group). [root@server3 ~]# mysqlfabric group create group01-shard02 Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e Time-To-Live: 1 uuid finished success result ------------------------------------ -------- ------- ------ 62f99b5e-45dc-4530-b0bd-6798b832d9c4 1 1 1 state success when description ----- ------- ------------- ------------------------------------------------------------- 3 2 1424790577.63 Triggered by <mysql.fabric.events.Event object at 0x20787d0>. 4 2 1424790577.64 Executing action (_create_group). 5 2 1424790577.64 Executed action (_create_group). [root@server3 ~]# mysqlfabric group create group01-global Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e Time-To-Live: 1 uuid finished success result ------------------------------------ -------- ------- ------ 0a18d78b-d0c2-4de7-aadb-3a329eccbfcb 1 1 1 state success when description ----- ------- ------------- ------------------------------------------------------------- 3 2 1424790581.87 Triggered by <mysql.fabric.events.Event object at 0x20787d0>. 4 2 1424790581.87 Executing action (_create_group). 5 2 1424790581.88 Executed action (_create_group). [root@server3 ~]# mysqlfabric group lookup_groups Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e Time-To-Live: 1 group_id description failure_detector master_uuid --------------- ----------- ---------------- ----------- group01-global None 0 None group01-shard01 None 0 None group01-shard02 None 0 None |
Then I add one server to each group, as already stated the group will so not be highly available:
[root@server3 ~]# mysqlfabric group add group01-global server1.domain.com:3316 Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e Time-To-Live: 1 uuid finished success result ------------------------------------ -------- ------- ------ 071061a2-8f3b-47d9-b6db-eae894f5d836 1 1 1 state success when description ----- ------- ------------- ------------------------------------------------------------- 3 2 1424793456.95 Triggered by <mysql.fabric.events.Event object at 0x2078b90>. 4 2 1424793456.96 Executing action (_add_server). 5 2 1424793457.11 Executed action (_add_server). [root@server3 ~]# mysqlfabric group add group01-shard01 server1.domain.com:3326 Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e Time-To-Live: 1 uuid finished success result ------------------------------------ -------- ------- ------ 50018d26-4d31-4b74-bd04-e989e011dd69 1 1 1 state success when description ----- ------- ------------- ------------------------------------------------------------- 3 2 1424793592.89 Triggered by <mysql.fabric.events.Event object at 0x2078b90>. 4 2 1424793592.9 Executing action (_add_server). 5 2 1424793592.98 Executed action (_add_server). [root@server3 ~]# mysqlfabric group add group01-shard02 server1.domain.com:3336 Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e Time-To-Live: 1 uuid finished success result ------------------------------------ -------- ------- ------ 1e20d632-0414-4d46-9d0e-4aa608463f0f 1 1 1 state success when description ----- ------- ------------- ------------------------------------------------------------- 3 2 1424793600.68 Triggered by <mysql.fabric.events.Event object at 0x2078b90>. 4 2 1424793600.69 Executing action (_add_server). 5 2 1424793600.73 Executed action (_add_server). |
All servers have been added as SECONDARY server to their respective group:
[root@server3 ~]# mysqlfabric group lookup_servers group01-global Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e Time-To-Live: 1 server_uuid address status mode weight ------------------------------------ ----------------------- --------- --------- ------ 40b23f27-b5fb-11e4-aadc-0800271a543a server1.domain.com:3316 SECONDARY READ_ONLY 1.0 |
To promote them as PRIMARY (read write) use (not displaying log as too verbose):
[root@server3 ~]# mysqlfabric group promote group01-global [root@server3 ~]# mysqlfabric group promote group01-shard01 [root@server3 ~]# mysqlfabric group promote group01-shard02 |
Can be checked with:
[root@server3 ~]# mysqlfabric group lookup_servers group01-global Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e Time-To-Live: 1 server_uuid address status mode weight ------------------------------------ ----------------------- ------- ---------- ------ 40b23f27-b5fb-11e4-aadc-0800271a543a server1.domain.com:3316 PRIMARY READ_WRITE 1.0 |
As MySQL Replication will be automatically implemented from your global group to all your shards it is a good practice to issue on global group instance:
mysql> reset master; Query OK, 0 rows affected (0.02 sec) |
MySQL Fabric sharding implementation
I start with below table that I plan to shard by range, range 1-19 will go to shard one and range 20- will go to shard two. Again the documentation is really poor, sometimes it is written that sharding is only RANGE or HASH while I have seen mention of LIST sharding…
mysql> select * from replicationdb.test1; +------+---------------+ | val | descr | +------+---------------+ | 1 | One | | 2 | Two | | 3 | Three | | 4 | Four | | 5 | Five | | 6 | Six | | 7 | Seven | | 8 | Eight | | 20 | Twenty | | 21 | Twenty-one | | 22 | Twenty-two | | 23 | Twenty-three | | 24 | Twenty-four | | 25 | Twenty-five | | 26 | Twenty-six | | 27 | Twenty-seven | | 28 | Twenty-height | +------+---------------+ 17 rows in set (0.00 sec) |
The table is created and filled on first shard instance. You need to create its skeleton on all shards and on global group of your architecture. To create such skeleton you may use:
[mysql@server1 ~]$ /mysql/software/mysql01/bin/mysqldump --no-data --user=root -p --single-transaction --socket=/mysql/software/mysql01/conf/mysql01.sock \ --databases replicationdb > /tmp/replicationdb.sql Enter password: Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. [mysql@server1 ~]$ /mysql/software/mysql01/bin/mysqldump --no-data --user=root -p --single-transaction --socket=/mysql/software/mysql01/conf/mysql01.sock --set-gtid-purged=OFF \ --databases replicationdb > /tmp/replicationdb.sql Enter password: |
While testing it is also a good idea to create a dump file with data:
[mysql@server1 ~]$ /mysql/software/mysql01/bin/mysqldump --user=root -p --single-transaction --socket=/mysql/software/mysql01/conf/mysql01.sock --set-gtid-purged=OFF \ --databases replicationdb > /tmp/replicationdb_with_data.sql Enter password: |
Then load the skeleton file on all shards and on global group…
I create a RANGE shard in global group (we see that shard id is 1, we will use it later on):
[root@server3 ~]# mysqlfabric sharding create_definition RANGE group01-global Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e Time-To-Live: 1 uuid finished success result ------------------------------------ -------- ------- ------ 1a0b98f5-e66c-4e2d-abf5-8a6995f227c2 1 1 1 state success when description ----- ------- ------------- ------------------------------------------------------------- 3 2 1424796839.61 Triggered by <mysql.fabric.events.Event object at 0x1ee4d90>. 4 2 1424796839.61 Executing action (_define_shard_mapping). 5 2 1424796839.63 Executed action (_define_shard_mapping). [root@server3 ~]# mysqlfabric sharding list_definitions Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e Time-To-Live: 1 mapping_id type_name global_group_id ---------- --------- --------------- 1 RANGE group01-global |
I add a table to the shard RANGE mapping just created, you also need to supply a shard key (val column in my example):
[root@server3 ~]# mysqlfabric sharding add_table 1 replicationdb.test1 val Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e Time-To-Live: 1 uuid finished success result ------------------------------------ -------- ------- ------ 76f9bec2-f99f-4c90-9b8e-0e204182e91e 1 1 1 state success when description ----- ------- ------------- ------------------------------------------------------------- 3 2 1424797043.67 Triggered by <mysql.fabric.events.Event object at 0x1ee4ed0>. 4 2 1424797043.68 Executing action (_add_shard_mapping). 5 2 1424797043.71 Executed action (_add_shard_mapping). [root@server3 ~]# mysqlfabric sharding list_tables RANGE Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e Time-To-Live: 1 mapping_id type_name table_name global_group column_name ---------- --------- ------------------- -------------- ----------- 1 RANGE replicationdb.test1 group01-global val [root@server3 ~]# mysqlfabric sharding lookup_table replicationdb.test1 Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e Time-To-Live: 1 mapping_id type_name table_name global_group column_name ---------- --------- ------------------- -------------- ----------- 1 RANGE replicationdb.test1 group01-global val |
At this stage you have two situations. Either it is a new empty object and you can create the shards and figures will be inserted later on by the application. Or, most probable situation, it is an existing objects and you will have to automatically distribute it on all shards. If sharding a new objects you may use something like below. The numbers after each shard are the lower limit of the shard means 1-19 and 20-unlimited:
[root@server3 ~]# mysqlfabric sharding add_shard 1 "group01-shard01/1, group01-shard02/20" --state=enabled Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e Time-To-Live: 1 uuid finished success result ------------------------------------ -------- ------- ------ 84dc4e29-67df-441d-8d06-56cb55c53e9f 1 1 1 state success when description ----- ------- ------------- ------------------------------------------------------------- 3 2 1424855253.6 Triggered by <mysql.fabric.events.Event object at 0x20eded0>. 4 2 1424855253.62 Executing action (_add_shard). 5 2 1424855255.34 Executed action (_add_shard). |
I do not see any MySQL Fabric command to display the created shards to get shard id for example. But we can select directly from MySQL Fabric repository database:
mysql> select * from fabric.shards; +----------+-----------------+---------+ | shard_id | group_id | state | +----------+-----------------+---------+ | 3 | group01-shard01 | ENABLED | | 4 | group01-shard02 | ENABLED | +----------+-----------------+---------+ 2 rows in set (0.00 sec) |
If you have objects with existing row (so my test case) the idea is to load your objects figures on first shard and then split this first shard to second one (and so on if more shards). I start by creating the first shard. At this stage figures will not be moved and will remain on unique first shard:
[mysql@server3 ~]$ mysqlfabric sharding add_shard 1 group01-shard01/1 --state=enabled Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e Time-To-Live: 1 uuid finished success result ------------------------------------ -------- ------- ------ 634fbe4a-561b-45bf-86a7-841294426b9e 1 1 1 state success when description ----- ------- ------------- ------------------------------------------------------------- 3 2 1425036017.84 Triggered by <mysql.fabric.events.Event object at 0x2a73e90>. 4 2 1425036017.85 Executing action (_add_shard). 5 2 1425036019.03 Executed action (_add_shard). |
We want to split this first shard in a second one starting at range 20. My MySQL Fabric process is running as mysql Linux account, I add this error so thought I have to launch it with root. In a way it solved my problem but while performing further testing I have not been able to reproduce it while MySQL Fabric daemon was running with mysql:
[root@server3 ~]# mysqlfabric sharding split_shard 9 group01-shard02 --split_value=20 Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e Time-To-Live: 1 IOError: [Errno 13] Permission denied: 'MySQL_server1.domain.com_3326.sql' |
I also had below error because my MySQL Fabric configuration file was not fully filled:
[root@server3 ~]# mysqlfabric sharding split_shard 9 group01-shard02 --split_value=20 Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e Time-To-Live: 1 BackupError: ('Error while taking backup using MySQLDump\n, %s', "mysqldump: Got error: 1045: Access denied for user 'fabric'@'server3' (using password: NO) when trying to connect\n") |
I modified /etc/mysql/fabric.cfg configuration file for client section to put fabric MySQL account password:
[client] password = secure_password
And this time process executed correctly (!!). The id of the shard to split can be found when selecting in fabric.shards in your MySQL Fabric repository instance:
[root@server3 ~]# mysqlfabric sharding split_shard 9 group01-shard02 --split_value=20 Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e Time-To-Live: 1 uuid finished success result ------------------------------------ -------- ------- ------ 528d8728-2d9c-4b4a-b36e-b4198e10193f 1 1 1 state success when description ----- ------- ------------- ------------------------------------------------------------- 3 2 1424949651.62 Triggered by <mysql.fabric.events.Event object at 0x1a09750>. 4 2 1424949651.62 Executing action (_check_shard_information). 5 2 1424949651.64 Executed action (_check_shard_information). 3 2 1424949651.63 Triggered by <mysql.fabric.events.Event object at 0x1a097d0>. 4 2 1424949651.64 Executing action (_backup_source_shard). 5 2 1424949652.38 Executed action (_backup_source_shard). 3 2 1424949652.36 Triggered by <mysql.fabric.events.Event object at 0x1a09810>. 4 2 1424949652.38 Executing action (_restore_shard_backup). 5 2 1424949654.11 Executed action (_restore_shard_backup). 3 2 1424949654.1 Triggered by <mysql.fabric.events.Event object at 0x1a09850>. 4 2 1424949654.11 Executing action (_setup_replication). 5 2 1424949654.31 Executed action (_setup_replication). 3 2 1424949654.31 Triggered by <mysql.fabric.events.Event object at 0x1a09890>. 4 2 1424949654.31 Executing action (_setup_sync). 5 2 1424949654.42 Executed action (_setup_sync). 3 2 1424949654.42 Triggered by <mysql.fabric.events.Event object at 0x1a098d0>. 4 2 1424949654.42 Executing action (_setup_resharding_switch). 5 2 1424949655.8 Executed action (_setup_resharding_switch). 3 2 1424949655.79 Triggered by <mysql.fabric.events.Event object at 0x1a09910>. 4 2 1424949655.8 Executing action (_prune_shard_tables_after_split). 5 2 1424949655.87 Executed action (_prune_shard_tables_after_split). |
My replicationdb.test1 test table has been well split and shard01 contains only val from 1 to 8 and shard02 contains val from 20 to 28.
While testing a bit MySQL Fabric sharding with Connector/J so in Java. I have experimented the non transparency while manipulating figures. Means for example in my small test program I need to set, non directly however, the server on which I’m planning to insert figures.
package jdbcdemo6; import java.sql.ResultSet; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.Random; import com.mysql.fabric.jdbc.JDBC4FabricMySQLConnection; //Formatter libraries import com.ibm.icu.text.NumberFormat; import com.ibm.icu.text.RuleBasedNumberFormat; public class jdbcdemo6 { public static void main(String[] args) throws Exception { ResultSet rs; String variable_value; Connection rawconn = null; JDBC4FabricMySQLConnection conn = null; Random rn = new Random(); int answer; String JDBC_URL = "jdbc:mysql:fabric://server3.domain.com:32274/replicationdb?fabricUsername=fabric&fabricPassword=secure_password"; NumberFormat formatter = new RuleBasedNumberFormat(RuleBasedNumberFormat.SPELLOUT); System.out.println("\n------------ MySQL Connector/J and MySQL Fabric Testing ------------\n"); try { rawconn = DriverManager.getConnection(JDBC_URL,"yjaquier","secure_password"); } catch (SQLException e) { System.out.println("Connection Failed! Check output console"); System.out.println("Error cause: "+e.getCause()); System.out.println("Error message: "+e.getMessage()); return; } conn = (JDBC4FabricMySQLConnection)rawconn; answer=rn.nextInt(30) + 1; conn.setShardTable("test1"); conn.setShardKey(String.valueOf(answer)); System.out.println("Val : " + answer); System.out.println("Shard table used : " + conn.getShardTable()); System.out.println("Current server group : " + conn.getCurrentServerGroup()); conn.createStatement().executeUpdate("insert into replicationdb.test1 values("+answer+",'"+formatter.format(answer)+"')"); conn.close(); } } |
The library to convert number in letter comes from ICU – International Components for Unicode
In my small example I take a random number, I instruct that I’m gonna work on test1 shard table (here strangely the table must not be prefixed by database name). I set the shard key to the value (in string) of random number. I finally display on which shard server I work and insert the value in table. We note that the server on which you insert rows is chosen transparently by MySQL Fabric so the application code is independent from number of shard that you have implemented.
If you check at MySQL level you will see that row is inserted on correct shard and you also see that it is really easy to make mistake and corrupt the sharding implementation. So clearly no it is not transparent and you will have to think twice to implement it for your application.
References
- SHARDING AND SCALE-OUT USING MYSQL FABRIC
- MySQL Fabric GA – Adding High Availability and/or Scaling to MySQL
- MySQL Fabric now Generally Available – Automating High Availability and Sharding for MySQL
- MySQL Fabric – adding Scaling to MySQL
- setting up fabric
siva says:
Hi,
mysqlfabric sharding split_shard 4 group01-shard01 –split_value=20
Invalid lower_bound value for RANGE sharding specification 20
siva says:
table data is not splitting on multiple servers
mysqlfabric sharding split_shard 3 group01-shard01/1 –split_value=35
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1
ShardingError: Invalid lower_bound value for RANGE sharding specification 35
varsha says:
Thank you for the information. I am not sure why my data is not getting sharded when I use the shard key to insert data. I will try to get Oracle technical support on this.
varsha says:
Hello Mr.Jaquier,
Thank you for providing the information.
I have a question. I followed the instructions and able to create 1 global group and 1 shard group. When I insert data through python using fabric IP address connection, I see data is also inserted in the databases belonging to Global group. Is that normal? If that is the case, we will have the data both on global group databases and shard group databases?
Yannick Jaquier says:
Hello Varsha,
Unfortunately I do not have any more my test environment but I don’t think it’s normal. You have figures in global group only for non-sharded objects. If your object is sharded then figures are spread among the shards…
Thanks,
Yannick.
Tim says:
In my fafric.cfg, I have the following settings under the servers section:
[servers]
user = fabric
password = fabric
backup_user = fabric
backup_password = fabric
restore_user = fabric
restore_password = fabric
unreachable_timeout = 5
I set all of them to be the same. Do I still miss something?
Yannick Jaquier says:
No, using same account for all should work… If not hitting a bug or something…
Tim says:
When I try to split the shard, I get the following error message:
ConfigurationError: Configuration option not found servers . backup_user
Does I miss something? I use 1.5.6.
Yannick Jaquier says:
Apparently backup_user option is new starting with 1.5.5:
https://dev.mysql.com/doc/mysql-utilities/1.5/en/fabric-cfgref.html
Either a bug or you have forgotten to create the user you setup with this option…