Documentation
Rate This Document
Findability
Accuracy
Completeness
Readability
SubmitClose

Deploying MyCAT Database and Table Partitioning

  1. Create a service table for each of the three databases.
     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 `test1` ( 
`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 `test2` ( 
`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 `test3` ( 
`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 file. 
      1
      		 
      vim /usr/local/mycat/conf/schema.xml
    2. Add the following content to the file: 
      1
2
3
      		 
      <table name="test1"  dataNode="dn1" > </table>
<table name="test2"  dataNode="dn2" > </table>
<table name="test3"  dataNode="dn3" > </table>

    3. Press Esc, type :wq!, and press Enter to save the file and exit.
  3. Restart MyCAT and check the status.
    1
2
3
    		 
    mycat stop
mycat start
mycat status
  4. Insert test data.
    1. Open the file. 
      1
      		 
      vim /home/testdist.sh
    2. Add the following content to the file: 
       1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
      		 
      #!/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 test1(id,name) values($i,'ccc')"
done
for i in {10001..20000}
do
/usr/local/mysql/bin/mysql -uroot -p1234567 -P8066 -h127.0.0.1 --default_auth=mysql_native_password -Dsysbench -e "insert into test2(id,name) values($i,'ccc')"
done
for i in {20001..30000}
do
/usr/local/mysql/bin/mysql -uroot -p1234567 -P8066 -h127.0.0.1 --default_auth=mysql_native_password -Dsysbench -e "insert into test3(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/testdist.sh
  5. Check the data distribution.
    1
    		 
    /usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql/run/mysql1.sock -Dsysbench -e "select max(id) from test1"


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


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

    The 30,000 data records are distributed in different tables of different databases.