文档
我要评分
获取效率
正确性
完整性
易理解
提交关闭

Verifying Read/Write Splitting and Load Balancing of MySQL Router

  1. Configure the MySQL Router file.
    1. Open the file. 
      vim /etc/mysqlrouter/mysqlrouter.conf
    2. Replace the file content with the following: 
      [DEFAULT]
logging_folder = /var/log/mysqlrouter
runtime_folder = /var/run/mysqlrouter
config_folder = /etc/mysqlrouter

[logger]
level = INFO

# If no plugin is configured which starts a service, keepalive
# will make sure MySQL Router will not immediately exit. It is
# safe to remove once Router is configured.
[keepalive]
interval = 60

# The following options can be used in the policy part of the route distinguisher.
[routing:basic_failover]
# Router address
bind_address = 192.168.53.22
# Router port
bind_port = 7001
routing_strategy = first-available
# Target server
destinations = 192.168.53.22:3306,192.168.53.22:3307
connect_timeout = 2

[routing:load_balance]
# Router address
bind_address = 192.168.53.22
# Router port
bind_port = 7002
routing_strategy = round-robin
# Target server
destinations = 192.168.53.22:3306,192.168.53.22:3307,192.168.53.22:3308
connect_timeout = 1
    3. Press Esc, type :wq!, and press Enter to save the file and exit.
  2. Start MySQL Router.
    1. Query the process ID of MySQL Router. 
      ps -ef|grep mysqlrouter

    2. Stop the MySQL Router process. 
      kill -9 72890
    3. Start MySQL Router. 
      1
      		 
      mysqlrouter &
    4. Check whether the startup is successful. 
      1
2
      		 
      netstat -an|grep 7001
netstat -an|grep 7002

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

  3. Perform a query test.
    1. Open the file. 
      vim /home/read_7001.sh
    2. Replace the file content with the following: 
      1
2
3
4
5
      		 
      #!/bin/bash
for i in {1..1000}
do
/usr/local/mysql/bin/mysql -uroot -p123456 -P7001 -h192.168.53.22 -e "select @@report_host"
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_7001.sh

      As shown in the figure above, all read requests sent to port 7001 are routed to the first primary database (192.168.53.22:3306).

    5. Open the file. 
      vim /home/read_7002.sh
    6. Replace the file content with the following: 
      1
2
3
4
5
      		 
      #!/bin/bash
for i in {1..1000}
do
/usr/local/mysql/bin/mysql -uroot -p123456 -P7002 -h192.168.53.22 -e "select @@report_host"
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_7002.sh

      As shown in the figure above, all read requests sent to port 7002 are routed to the three MySQL servers in polling mode to achieve read load balancing.

  4. Perform a write test.
    1. Open the file. 
      vim /home/write_7001.sh
    2. Replace the file content with the following: 
      1
2
3
4
5
      		 
      #!/bin/bash
for i in {1..2000}
do
/usr/local/mysql/bin/mysql -uroot -p123456 -P7001 -h192.168.53.22 -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 write request script. 
      1
      		 
      sh /home/write_7001.sh
    5. Confirm the test data. 
      1
      		 
      /usr/local/mysql/bin/mysql -uroot -p123456 -P7001 -h192.168.53.22 -Dsysbench -e "select * from test1"

      As shown in the figure above, all write requests sent to port 7001 are routed to the first primary database (192.168.53.22:3306).

      It can be learned from the foregoing exercises that read/write splitting can be implemented as long as write requests are sent to port 7001 and read requests are sent to port 7002.

  5. Establish the primary-primary replication scheme.

    Initialize the two databases by following instructions in Initializing the MySQL Database. Primary database 1 is 192.168.53.22:3309, and primary database 2 is 192.168.53.22:3310.

    1. Add the following configurations to the parameter configuration files of the two databases:

      Primary database 1:

      1
2
      		 
      auto_increment_increment=2
auto_increment_offset=1

      Primary database 2:

      1
2
      		 
      auto_increment_increment=2
auto_increment_offset=2

      auto_increment_increment and auto_increment_offset are generally used in primary-primary synchronization to stagger self-increment and prevent key value conflicts.

      Refer to Semisynchronous Replication and Parallel Replication in the MySQL Primary-Secondary Replication Deployment Guide to establish the primary-secondary replication relationship and then switch the role.

    2. Configure the MySQL Router file.
      1. Open the file. 
        vim /etc/mysqlrouter/mysqlrouter.conf
      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
        		 
        [DEFAULT]
logging_folder = /var/log/mysqlrouter
runtime_folder = /var/run/mysqlrouter
config_folder = /etc/mysqlrouter

[logger]
level = INFO

# If no plugin is configured which starts a service, keepalive
# will make sure MySQL Router will not immediately exit. It is
# safe to remove once Router is configured.
[keepalive]
interval = 60

[routing:load_balance]
# Router address
bind_address = 192.168.53.22
# Router port
bind_port = 7001
routing_strategy = round-robin
# Target server
destinations = 192.168.53.22:3309,192.168.53.22:3310
connect_timeout = 1
      3. Press Esc, type :wq!, and press Enter to save the file and exit.
    3. Create a test database and a test table.
      1. Connect to primary database 1. 
        /usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql/run/mysqlm1.sock
      2. Create a test database and table. 
        1
2
3
4
5
6
7
        		 
        create database sysbench;
use sysbench;
CREATE TABLE `test1` (
  `id` bigint(20) NOT NULL,
  `report_hostname` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
      3. Confirm that data is synchronized to primary database 2. 
        1
2
3
4
        		 
        /usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql/run/mysqlm2.sock
show databases;
use sysbench;
show tables;

    4. Start MySQL Router.
      1. Query the process ID of MySQL Router. 
        ps -ef|grep mysqlrouter

      2. Stop the MySQL Router process. 
        kill -9 72890
      3. Start MySQL Router. 
        mysqlrouter &
      4. Check whether the startup is successful. 
        1
        		 
        netstat -an|grep 7001

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

    5. Perform a write test. 
      1
      		 
      sh /home/write_7001.sh
    6. Confirm the test data. 
      1
      		 
      /usr/local/mysql/bin/mysql -uroot -p123456 -P7001 -h192.168.53.22 -Dsysbench -e "select * from test1"

      According to the preceding exercises, to achieve read/write load balancing, you need to configure primary-primary replication. Set the routing strategy to round-robin so that read and write requests can be handled in polling mode.