The third article of the MYCAT study note. After MYSQL master-slave synchronization exception, master-slave switch

1) System Environment description

MYSQL 5.5 Master-slave node

127.0.0.1:3306 main node, manual stop service for verifying master-slave switching effect

127.0.0.1:3307 from Junction 1

127.0.0.1:338 from Node 2, in order to verify the master-slave switching effect, after the main node stopped, add two records.

Mycat 1.5 schema.xml Configuration

Specific configuration instructions, refer to the previous article: "Mycat Study Notes" the second article. MYSQL read-write separation and log analysis--master-Slave Multi-node

<datahost name= "Localhost1" maxcon= "+" mincon= "balance=" 1 "
Writetype= "0" dbtype= "MySQL" dbdriver= "native" switchtype= "2" slavethreshold= ">"
<!--can have multi write hosts--
<writehost host= "hostM1" url= "localhost:3306" user= "root" password= "root123" >
<!--can have multi read hosts--
<readhost host= "hostS1" url= "localhost:3307" user= "root" password= "root123"/>
</writeHost>

<writehost host= "hostM2" url= "localhost:3308" user= "root" password= "root123" ></writeHost>

</dataHost>

2) Overall switching process

• Original host (3306) stop service
• Mycat Automatic detection for master-slave switching (hostM1 to hostM2)
• With Mycat insert data, it is only reflected in the 3308 database
• Start the original host MySQL 3306 service
• Manually synchronizing data MySQL Workbench (temporarily unaware of other methods,)
• Adjust the relationship between MySQL master and slave, set 3308 as host, 3306 to Slave, and 3307 to configure.
• Verify the new master-slave configuration with Mycat
• Mycat does not restart, the service automatically switches

3) Specific operation steps

3.1) Stop 3308 environment slave mode and turn on master

Stop slave

mysql> stop Slave;

Configuring 3308 for Host Mode

Mysql> GRANT REPLICATION SLAVE on * * to ' mycat_sync_3308 ' @ ' percent ' identified by ' mycat_sync_3308 ';
Query OK, 0 rows Affected (0.00 sec)

Mysql> Show master status;
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 | 284 | | |
+------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)

3.2) 3306 Environment Open Slave

3.3) Original master machine switch to slave mode

3.4) Data validation

3.5) Mycat Verification

The third article of the MYCAT study note. After MYSQL master-slave synchronization exception, master-slave switch