Documentation
Rate This Document
Findability
Accuracy
Completeness
Readability
SubmitClose

Verifying Read/Write Splitting and Load Balancing

  1. Create a test database and a test table.
    1. Connect to the MySQL database. 
      1
      		 
      /usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql/run/mysqlm.sock
    2. Create a test database and a test table. 
      1
2
3
4
5
6
7
      		 
      create database sysbench;
use sysbench;
CREATE TABLE `test1` (
  `id` bigint(20) NOT NULL,
  `report_serverid` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    3. Confirm that data is synchronized to secondary database 1. 
      1
2
3
4
      		 
      /usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql/run/mysqls.sock
show databases;
use sysbench;
show tables;
    4. Confirm that data is synchronized to secondary database 2. 
      1
2
3
4
      		 
      /usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql/run/mysqls2.sock
show databases;
use sysbench;
show tables;
  2. Start MySQL Router.
    1
    		 
    mysqlrouter &
  3. Verify that the MySQL Router ports are started properly.
    1
2
    		 
    netstat -an|grep 6446
netstat -an|grep 6447

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

  4. Perform a query test.
    1. Open the file. 
      vim /home/read_6446.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 -p123456 -P6446 -h127.0.0.1 -e "select @@server_id"
done
    3. Press Esc, type :wq!, and press Enter to save the file and exit.
    4. Execute the read request script. 
      1
      		 
      sh /home/read_6446.sh

      All read requests of the write port (port 6446) are sent to the primary node as expected.

    5. Open the file. 
      vim /home/read_6447.sh
    6. 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 -p123456 -P6447 -h127.0.0.1 -e "select @@server_id"
done
    7. Press Esc, type :wq!, and press Enter to save the file and exit.
    8. Execute the read request script. 
      1
      		 
      sh /home/read_6447.sh

      Read requests sent to the read port 6447 are routed to the two secondary nodes in polling mode as expected.

  5. Perform a write test.
    1. Open the file. 
      vim /home/write_6446.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 -p123456 -P6446 -h127.0.0.1 -Dsysbench -e "insert into test1(id,report_serverid) values($i,@@server_id)"
done
    3. Press Esc, type :wq!, and press Enter to save the file and exit.
    4. Execute the write request script. 
      1
      		 
      sh /home/write_6446.sh
    5. Confirm the test data. 
      1
      		 
      /usr/local/mysql/bin/mysql -uroot -p123456 -P6446 -h127.0.0.1 -Dsysbench -e "select * from test1"

      All write requests of the write port (port 6446) are sent to the primary node as expected.

    6. Perform a write test through port 6447. 
      /usr/local/mysql/bin/mysql -uroot -p123456 -P6447 -h127.0.0.1 -Dsysbench -e "insert into test1(id,report_serverid) values(1001,@@server_id)"

Parent topic: Deploying MySQL Router