Documentation
Rate This Document
Findability
Accuracy
Completeness
Readability
SubmitClose

Configuring the mod-long Sharding Strategy

  1. Modify the schema.xml file that is used to configure the MyCAT databases, MySQL tables, sharding strategy, and sharding type.
    1. Open the file. 
      1
      		 
      vim /usr/local/mycat/conf/schema.xml
    2. Replace the file content with the following: 
       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
31
32
33
34
35
36
37
38
39
      		 
      <?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="sysbench" checkSQLschema="false" >
                <table name="users" primaryKey="id" dataNode="dn1,dn2,dn3" rule="mod-long" autoIncrement="true" fetchStoreNodeByJdbc="true"> </table>
        </schema>

        <dataNode name="dn1" dataHost="dh1" database="sysbench" />
        <dataNode name="dn2" dataHost="dh2" database="sysbench" />
        <dataNode name="dn3" dataHost="dh3" database="sysbench" />

        <dataHost name="dh1" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="lh" url="localhost:3309" user="root"
                                password="123456">
                        <readHost host="lh" url="localhost:3309" user="root" password="123456"/>
                </writeHost>
        </dataHost>

        <dataHost name="dh2" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="lh" url="localhost:3310" user="root"
                                password="123456">
                        <readHost host="lh" url="localhost:3310" user="root" password="123456"/>
                </writeHost>
        </dataHost>

        <dataHost name="dh3" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="lh" url="localhost:3311" user="root"
                                password="123456">
                        <readHost host="lh" url="localhost:3311" user="root" password="123456"/>
                </writeHost>
        </dataHost>
</mycat:schema>

      In this example, data in the users table is evenly distributed to three databases based on the mod-long sharding strategy, and the user password, IP address, and port number of each database are configured. The MyCAT and MySQL databases are deployed on the same physical server, and use the IP address of local host as their IP address. You can also change the IP address based on the actual environment.

    3. Press Esc, type :wq!, and press Enter to save the file and exit.
  2. Start MyCAT.
    1. Start MyCAT. 
      1
      		 
      mycat start
    2. Check whether MyCAT is started. 
      1
      		 
      mycat status

      If the command output is the same as that shown in the figure, MyCAT is started.

  3. Connect to the MyCAT database and insert data.
    1. Open the file. 
      vim /home/insert.sh
    2. Add the following content to the script: 
      1
2
3
4
5
      		 
      #!/bin/bash
for i in {1..10000}
do
/usr/local/mysql/bin/mysql -uroot -p1234567 -P8066 -h127.0.0.1 --default_auth=mysql_native_password -Dsysbench -e "insert into users(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.sh
  4. View data distribution.
    1
    		 
    /usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql/run/mysql1.sock -Dsysbench -e "select count(*) from users"


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


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

    The 10,000 data records are evenly distributed on the database shards.