Verifying HA

Simulate a scenario where a primary node is stopped and test the routing of read and write requests.

         
              /usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql/run/mysqls.sock

Stop the database.
1

shutdown;

         
              /usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql/run/mysqlm.sock
select * from performance_schema.replication_group_members;

Execute the read request script.
1

sh /home/read_6447.sh
The 127.0.0.1:3307 database service is disabled. Read requests are sent to the other two primary nodes in polling mode as expected.

Execute the write request script.

Open the file.

           
                vim /home/write_6447.sh

Replace the file content with the following:

           
                #!/bin/bash
for i in {2001..3000}
do
/usr/local/mysql/bin/mysql -uroot -p123456 -P6447 -h127.0.0.1 -Dsysbench -e "insert into test1(id,report_serverid) values($i,@@server_id)"
done

Press Esc, type :wq!, and press Enter to save the file and exit.

Execute the write request script.

           
                sh /home/write_6447.sh

Confirm the test data.

           
                /usr/local/mysql/bin/mysql -uroot -p123456 -P6447 -h127.0.0.1 -Dsysbench -e "select * from test1"

The 127.0.0.1:3307 database service is disabled. Write requests are sent to the other two primary nodes in polling mode as expected.

Simulate a scenario where two primary nodes are stopped and test the routing of read and write requests.

         
              /usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql/run/mysqls2.sock

Stop the database.
1

shutdown;

         
              /usr/local/mysql/bin/mysql -uroot -p123456 -S /data/mysql/run/mysqlm.sock
select * from performance_schema.replication_group_members;

Execute the read request script.
1

sh /home/read_6447.sh
The 127.0.0.1:3307 and 127.0.0.1:3308 database services are disabled. Read requests are sent to the remaining primary node as expected.

Execute the write request script.

Open the file.

           
                vim /home/write_6447.sh

Replace the file content with the following:

           
                #!/bin/bash
for i in {3001..4000}
do
/usr/local/mysql/bin/mysql -uroot -p123456 -P6447 -h127.0.0.1 -Dsysbench -e "insert into test1(id,report_serverid) values($i,@@server_id)"
done

Press Esc, type :wq!, and press Enter to save the file and exit.

Execute the write request script.

           
                sh /home/write_6447.sh

Confirm the test data.

           
                /usr/local/mysql/bin/mysql -uroot -p123456 -P6447 -h127.0.0.1 -Dsysbench -e "select * from test1"

The 127.0.0.1:3307 and 127.0.0.1:3308 database services are disabled. Write requests are sent to the remaining primary node as expected.

Parent topic: Deploying MGR (Multi-Primary Mode)