Documentation
Rate This Document
Findability
Accuracy
Completeness
Readability
SubmitClose

Configuring the auto-sharding-long Range Sharding Strategy

  1. Create tables for testing range-based sharding. Connect to the MySQL databases and create users2 tables.
     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
    		 
    dn1:
# Connect to the database.
/usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql/run/mysql1.sock -Dsysbench
# Create a table.
CREATE TABLE `users2` ( 
`id` int(11) NOT NULL, 
`name` varchar(50) NOT NULL DEFAULT '', 
`indate` datetime NOT NULL DEFAULT '1910-01-01 00:00:00', 
PRIMARY KEY (`id`) 
) ENGINE=InnoDB;
dn2:
# Connect to the database.
/usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql/run/mysql2.sock  -Dsysbench
# Create a table.
CREATE TABLE `users2` ( 
`id` int(11) NOT NULL, 
`name` varchar(50) NOT NULL DEFAULT '', 
`indate` datetime NOT NULL DEFAULT '1910-01-01 00:00:00', 
PRIMARY KEY (`id`) 
) ENGINE=InnoDB;
dn3:
# Connect to the database.
/usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql/run/mysql3.sock  -Dsysbench
# Create a table.
CREATE TABLE `users2` ( 
`id` int(11) NOT NULL, 
`name` varchar(50) NOT NULL DEFAULT '', 
`indate` datetime NOT NULL DEFAULT '1910-01-01 00:00:00', 
PRIMARY KEY (`id`) 
) ENGINE=InnoDB;
  2. Modify the schema.xml file.
    1. Open the schema.xml file. 
      1
      		 
      vim /usr/local/mycat/conf/schema.xml
    2. Add the following information: 
      1
      		 
      <table name="users2" primaryKey="id" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" autoIncrement="true" fetchStoreNodeByJdbc="true"> </table>

    3. Press Esc, type :wq!, and press Enter to save the file and exit.
  3. Modify the autopartition-long.txt file.
    1. Open the file. 
      1
      		 
      vim /usr/local/mycat/conf/autopartition-long.txt
    2. Modify the file as follows. 
      1
2
3
4
5
      		 
      # range start-end ,data node index
# K=1000,M=10000.
1-10000=0
10001-20000=1
20001-30000=2

      Row data whose IDs range from 1 to 10000 is stored in shard dn1, row data whose IDs range from 10001 to 20000 is stored in shard dn2, and row data whose IDs range from 20001 to 30000 is stored in shard dn3.

    3. Press Esc, type :wq!, and press Enter to save the file and exit.
  4. Restart MyCAT and query the status.
    1
2
3
    		 
    mycat stop
mycat start
mycat status
  5. Insert test data.
    1. Open the file. 
      1
      		 
      vim /home/insert-autopartition.sh
    2. Add the following content to the file: 
      1
2
3
4
5
      		 
      #!/bin/bash
for i in {1..30000}
do
/usr/local/mysql/bin/mysql -uroot -p1234567 -P8066 -h127.0.0.1 --default_auth=mysql_native_password -Dsysbench -e "insert into users2(id,name) values($i,'ccc')"
done
    3. Press Esc, type :wq!, and press Enter to save the file and exit.
    4. Execute the script. 
      1
      		 
      sh /home/insert-autopartition.sh
  6. Check the data distribution.
    1
2
    		 
    /usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql/run/mysql1.sock -Dsysbench -e "select count(*) from users2"
/usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql/run/mysql1.sock -Dsysbench -e "select max(id) from users2"


    1
2
    		 
    /usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql/run/mysql2.sock -Dsysbench -e "select count(*) from users2"
/usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql/run/mysql2.sock -Dsysbench -e "select max(id) from users2"


    1
2
    		 
    /usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql/run/mysql3.sock -Dsysbench -e "select count(*) from users2"
/usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql/run/mysql3.sock -Dsysbench -e "select max(id) from users2"

    The 30,000 data records are evenly distributed on the specified data nodes based on the rules customized in the autopartition-long.txt file.