Documentation
Rate This Document
Findability
Accuracy
Completeness
Readability
SubmitClose

Verifying the Availability

  1. Configure MySQL Router.
    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
24
25
26
27
28
29
30
31
32
33
34
      		 
      [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.
    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. 
        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.

        When 192.168.53.22:3306 is available, all requests from port 7001 are routed to 192.168.53.22:3306. Requests from port 7002 are sent to 192.168.53.22:3306, 192.168.53.22:3307, and 192.168.53.22:3308 in polling mode. This has been proved in steps 3 and 4 in Verifying Read/Write Splitting and Load Balancing of MySQL Router.

  2. Simulate a scenario where secondary database 1 is stopped and the services of the primary and secondary database 2 are normal. Test the routing of read and write requests.
    1. Log in to secondary database 1. 
      /usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql/run/mysqls.sock
    2. Stop the database. 
      shutdown;
    3. Execute the read script. 
      sh /home/read_7002.sh

      When secondary database 1 is disabled, read requests sent to port 7002 are routed to the primary database and secondary database 2 in polling mode, which meets the expectation.

    4. Execute the write script.
      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 {2001..3000}
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), which meets the expectation.

  3. Simulate a scenario where secondary databases 1 and 2 are stopped and the primary database works properly. Test the routing of read and write requests.
    1. Log in to secondary database 2. 
      /usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql/run/mysqls2.sock
    2. Stop the database. 
      shutdown;
    3. Execute the read script. 
      sh /home/read_7002.sh

      When secondary databases 1 and 2 are disabled, read requests sent to port 7002 are routed only to the primary database, which meets the expectation.

    4. Execute the write script.
      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 {3001..4000}
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), which meets the expectation.

  4. Simulate a scenario where the primary database is stopped and the secondary databases 1 and 2 work properly. Test the routing of read and write requests.
    1. Start secondary database 1 and secondary database 2. 
      /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf-slave &
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf-slave-2 &
    2. Establish the primary-secondary replication relationship.

      For details about MySQL primary-secondary replication, see Semisynchronous Replication, Parallel Replication, and Replication Modes in the MySQL Primary-Secondary Replication Deployment Guide. You will see that the data of secondary databases 1 and 2 has been synchronized from the primary database and is consistent with that of the primary database.

    3. Stop the primary database. 
      1
2
      		 
      /usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql/run/mysqlm.sock
shutdown;
    4. Execute the read 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). However, the primary database has been shut down, so the requests are automatically routed to secondary database 1 (192.168.53.22:3307).

    5. Execute the read script. 
      1
      		 
      sh /home/read_7002.sh

      As shown in the figure above, read requests sent to port 7002 are routed to the three MySQL servers in polling mode. However, the primary database has been shut down, so the requests are sent to secondary database 1 (192.168.53.22:3307) and secondary database 2 (192.168.53.22:3308).

    6. Execute the write script.
      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 {4001..5000}
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"

        According to the preceding exercises, after the primary database (192.168.53.22:3306) breaks down, all requests of port 7001 are sent to a secondary database (192.168.53.22:3307).

        Data cannot be synchronized from secondary database 1 to secondary database 2 due to data writing. Therefore, you need to set secondary database 1 is set to the primary to ensure data synchronization.

    7. Set up secondary databases 1 and 2 in the primary/secondary relationship.

      For details about MySQL primary-secondary replication, see Semisynchronous Replication, Parallel Replication, and Replication Modes in the MySQL Primary-Secondary Replication Deployment Guide.

      After the setup, you will see the data of secondary database 2 is synchronized and is the same as that of secondary database 1 (the new primary database).

    8. Start the original primary database 192.168.53.22:3306. 
      /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf-master &
    9. Set the original primary database as a secondary.

      For details about MySQL primary-secondary replication, see Semisynchronous Replication, Parallel Replication, and Replication Modes in the MySQL Primary-Secondary Replication Deployment Guide.

      After the setting is successful, you will see the data of the original primary database (3306) is synchronized with the current primary database (3307). So far, the new primary/secondary relationship is established, with 192.168.53.22:3307 as the primary database, 192.168.53.22:3308 as secondary database 1, and 192.168.53.22:3306 as secondary database 2.

  5. Verify the routing of read and write requests.
    1. Execute the read script. 
      1
      		 
      sh /home/read_7001.sh

      As shown in the figure above, all read requests sent to port 7001 are routed to the primary database (192.168.53.22:3307), which meets the expectation.

    2. Execute the read script. 
      1
      		 
      sh /home/read_7002.sh

      As shown in the figure above, read requests sent to port 7002 are routed to the three MySQL servers in polling mode, which meets the expectation.

    3. Execute the write script.
      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 {5001..6000}
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 handled on the primary database (192.168.53.22:3307) as expected.

  6. If the MySQL Router service is restarted, requests sent to port 7001 are routed only to 192.168.53.22:3306. This is because MySQL Router forwards the first request from a client to the first destination database in the destinations list. Verify as follows:
    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.

    5. Execute the read request script. 
      1
      		 
      sh /home/read_7001.sh

      As shown in the figure above, all requests sent to port 7001 are handled on the secondary database 2 (192.168.53.22:3306), which meets the expectation.

      After MySQL Router is restarted, 192.168.53.22:3306 is still the secondary role in the primary-secondary replication relationship. However, because 192.168.53.22:3306 receives read and write requests, it actually serves as the primary. The restart of MySQL Router has damaged the consistency of the primary-secondary replication, therefore, in this scenario, you need to re-set up the roles for primary-secondary replication. Alternatively, you can modify the MySQL Router configuration file. Because requests of port 7001 are forwarded to the first destination database in the destinations list, just set 192.168.53.22:3307 as the first destination database.