Mycat in MySQL master-slave mode (1 master 1 slave) Read and write separation and automatic switching mode verification

Experimental environment

Two CENTOS7 MySQL5.7.12 IP addresses are: 192.168.10.36 192.168.10.37

One CENTOS7 mycat IP address is: 192.168.10.31

One: Install MySQL as shown here (yum installation is used here):

Two: Configure MySQL, and build MySQL master-slave

1: Initialize the password, because I did not find the first startup of the random password in the/root directory, so I can only use the following actions

Vi/etc/my.cnf

Mysqd field Add #skip-grant-tables

Then restart MySQL systemctl restart mysqld can go in without a password at this time; Execute the following statement:

Update Mysql.user set Authentication_string=password (123456) where user= ' root ' and Host = ' localhost ';

After restarting MySQL, comment out the field you just added, systemctl restart mysqld will tell you that the password does not meet the complexity requirements, need to change again;

SET PASSWORD for ' root ' @ ' localhost ' = PASSWORD ('[email protected]');

FLUSH privileges;

Then sign in with your new password!

2: Configure MySQL master-slave, MySQL configuration file content modification such as, master-slave server_id One is 36, the other one is 37, here must be inconsistent:

3: Execute the following command to establish a master-slave relationship, 192.168.10.37 for 192.168.10.36 slave

Execute on 192.168.10.36:

Grant Replication Slave on * * to ' sync ' @ ' 192.168.10.37 ' identified by ' sync ';

Execute on 192.168.10.37:

Change Master to master_host= ' 192.168.10.36 ', master_user= ' repluser ', master_password= ' Replpass ', master_auto_ Position=1; (used here is Gtid multithreaded replication)

Start slave;

Use show slave status\g at this time to verify that the master-slave relationship is complete, such as:

Three: Configuration Mycat

1: The logical Library is myyangtest and the logical table is:

2: View the Schema.xml configuration file as shown in:

Here are 1 datahost;3 datanode nodes;

3: View the Rule.xml configuration file as shown in:

4: View the contents of the Server.xml configuration file, as shown in the main content:

5: Start Mycat, and turn on debug mode of Mycat log,

./bin/mycat Start

6: Create the Myyang table and start inserting data;

7: First verify the Mycat read-write separation, which first set the balance to 3,writetype set to 0, first comment out the second writehost.

The following query statement is executed first, and after getting this result,

Then open the Mycat logs directory under the Mycat.log file, you can see the following content;

The routes that can be verified by the query are sent to 192.168.10.37 (from the server).

Then execute the following insert statement;

Then open the Mycat logs directory Mycat.log file again, you can see the following content;

The route that can be seen by the INSERT statement is sent to the 192.168.10.36 (master server) complete!

At this point, you can verify that the Mycat read/write separation has been implemented!

8: Verify MYCAT auto-switch function

Modify the Schema.xml content as shown in:

Restart mycat! After the modification is complete

The INSERT statement is executed at this point such as:

Open the log file again to see (for example) INSERT statements or execute on 192.168.10.36 (master server);

At this point we will be 192.168.10.36 of the MySQL service to shut down! , the file will print the error message, as shown in;

At this point we are going to execute an INSERT statement:

Open the log file again, as shown in:

As you can see, the INSERT statement is routed to 192.168.10.37 (from the server)! Alternatively we can log in to 192.168.10.37 's MySQL Db_test3 library for verification (e.g.):

This mycat auto-switching function verifies success!

Mycat in MySQL master-slave mode (1 master 1 slave) Read and write separation and automatic switching mode verification