Sharding

Usage

Pre-work

  1. Start the MySQL service
  2. Create MySQL database (refer to ShardingSphere-Proxy data source configuration rules)
  3. Create a role or user with creation permission for ShardingSphere-Proxy
  4. Start Zookeeper service (for persistent configuration)

Start ShardingSphere-Proxy

  1. Add mode and authentication configurations to server.yaml (please refer to the example of ShardingSphere-Proxy)
  2. Start ShardingSphere-Proxy (Related introduction)

Create a distributed database and sharding tables

  1. Connect to ShardingSphere-Proxy
  2. Create a distributed database
  1. CREATE DATABASE sharding_db;
  1. Use newly created database
  1. USE sharding_db;
  1. Configure data source information
  1. ADD RESOURCE ds_0 (
  2. HOST=127.0.0.1,
  3. PORT=3306,
  4. DB=ds_1,
  5. USER=root,
  6. PASSWORD=root
  7. );
  9. ADD RESOURCE ds_1 (
  10. HOST=127.0.0.1,
  11. PORT=3306,
  12. DB=ds_2,
  13. USER=root,
  14. PASSWORD=root
  15. );
  1. Create sharding rule
  1. CREATE SHARDING TABLE RULE t_order(
  2. RESOURCES(ds_0,ds_1),
  3. SHARDING_COLUMN=order_id,
  4. TYPE(NAME=hash_mod,PROPERTIES("sharding-count"=4)),
  5. GENERATED_KEY(COLUMN=order_id,TYPE(NAME=snowflake,PROPERTIES("worker-id"=123)))
  6. );
  1. Create sharding table
  1. CREATE TABLE `t_order` (
  2.   `order_id` int NOT NULL,
  3.   `user_id` int NOT NULL,
  4.   `status` varchar(45) DEFAULT NULL,
  5.   PRIMARY KEY (`order_id`)
  6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  1. Drop sharding table
  1. DROP TABLE t_order;
  1. Drop sharding rule
  1. DROP SHARDING TABLE RULE t_order;
  1. Drop resource
  1. DROP RESOURCE ds_0, ds_1;
  1. Drop distributed database
  1. DROP DATABASE sharding_db;

Notice

  1. Currently, DROP DATABASE will only remove the logical distributed database, not the user’s actual database.
  2. DROP TABLE will delete all logical fragmented tables and actual tables in the database.
  3. CREATE DATABASE will only create a logical distributed database, so users need to create actual databases in advance.
  4. The Auto Sharding Algorithm will continue to increase to cover the user’s various sharding scenarios.