Documentation
Rate This Document
Findability
Accuracy
Completeness
Readability
SubmitClose

Deploying MyCAT Load Balancing

  1. Configure the schema.xml file of MyCAT.
    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
      		 
      <?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="sysbench" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
        <!--        <table name="sbtest1" dataNode="dn1"/>-->

        </schema>

        <dataNode name="dn1" dataHost='dh1' database="sysbench" />
        <dataHost name="dh1" maxCon="1000" minCon="10" balance="2" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>show slave status</heartbeat>
                <writeHost host="hostm" url="localhost:3312" user="root"
                                   password="123456">
                <readHost host="hosts2" url="localhost:3313" user="root" password="123456"> </readHost>
                </writeHost>
        </dataHost>
</mycat:schema>

    3. Press Esc, type :wq!, and press Enter to save the file and exit.
  2. Start MyCAT and check its status.
    1
2
3
    		 
    mycat stop
mycat start
mycat status
  3. Perform a query test.
    1. Open the file. 
      1
      		 
      vim /home/read.sh
    2. Add the following content to the file: 
      1
2
3
4
5
      		 
      #!/bin/bash
for i in {1..1000} 
do 
/usr/local/mysql/bin/mysql -uroot -p1234567 -P8066 -h127.0.0.1 --default_auth=mysql_native_password -Dsysbench -e "select @@report_host" 
done
    3. Press Esc, type :wq!, and press Enter to save the file and exit.
    4. Execute the script. 
      1
      		 
      sh /home/read.sh

      The query operation is randomly routed to the primary or secondary database for execution.

      MyCAT uses parameters balance and writetype to control load balancing.

      • balance:

        0: Read/Write splitting is disabled. All read operations are sent to the currently available writeHosts.

        1: All readHosts and standby writeHosts participate in load balancing of SELECT statements.

        2: All read requests are sent to the writeHosts and readHosts randomly. The balance parameter is set to 2 in this test case.

        3: All read requests are randomly distributed to the readHosts corresponding to the writeHosts. The writeHosts do not handle the read requests.

      • writetype:

        0: All write operations are sent to the first writeHost. When the first writeHost fails, services are switched to the second writeHost. After the database is restarted, the writetype parameter setting of the second writeHost is used. The switchover is recorded in the dnindex.properties file.

        1: All write requests are sent to the configured writeHost.

        2: unavailable

  4. Perform a write test.
    1. Open the file. 
      1
      		 
      vim /home/write.sh
    2. Add the following content to the file: 
      1
2
3
4
5
      		 
      #!/bin/bash
for i in {1..20000}
do
/usr/local/mysql/bin/mysql -uroot -p1234567 -P8066 -h127.0.0.1 --default_auth=mysql_native_password -Dsysbench -e "insert into test1(id,report_hostname) values($i,@@report_host)"
done
    3. Press Esc, type :wq!, and press Enter to save the file and exit.
    4. Execute the script. 
      1
      		 
      sh /home/write.sh
  5. Confirm the test data.
    1
    		 
    /usr/local/mysql/bin/mysql -uroot -p1234567 -P8066 -h127.0.0.1 --default_auth=mysql_native_password -Dsysbench -e "select * from test1"

    All insert operations are performed on the primary database.