background

This week, we studied the database middleware MyCat and verified the deployment scheme of MyCat stand-alone +MySQL master-slave replication. This paper will record the problems encountered in the deployment process.

What is MyCat

  1. A completely open source, enterprise-oriented large database cluster that supports transactions, ACID, and an enhanced database that can replace MySQL;
  2. An enterprise-class database that can be viewed as a MySQL cluster instead of an expensive Oracle cluster;
  3. A new SQL Server integrating memory caching technology, NoSQL technology, HDFS big data;
  4. A new generation of enterprise-level database products combining traditional database and new distributed data warehouse;
  5. A novel database middleware product.

Why MyCat? The main focus is on its distributed deployment and ability to seamlessly integrate with MySQL without changing the client code.

The author encountered several problems during the deployment process, which are summarized as follows:

  1. The status of Slave_SQL_Running is No due to the failure of MySQL synchronization from the node.
  2. MyCat startup error, unable to connect to Slave node;
  3. MyCat connection failed;
  4. MyCat connection password rejected.

The status of Slave_SQL_Running is No

Error: error: null; error: null; error: null; error: null; error: null;In this case, data inconsistency may cause synchronization failure on the secondary node. In this example, a database drop operation is performed on the secondary nodedrop database db3, and then perform the same operation on the master node, resulting in synchronization failure and termination of the synchronization thread.

By default, once the slave node encounters an abnormal synchronization operation, the synchronization thread is terminated, and manual intervention is required to ensure that the master and slave synchronization can continue. How to think about it is unreasonable, is there any solution?

The answer is to set slave-skip-errors=all and ignore all exceptions. Add this configuration under mysqld configuration and ignore it completely.

MyCat startup error

After the primary and secondary nodes are successfully deployed, configure the MyCat schema. XML and server. XML and run the MyCat start command. An error message is displayed.

can't connect to mysql server ,errmsg:Host '192.1688.182.' is not allowed to connect to this MySQL server MySQLConnection@1259448118 [id=11, lastTime=1597997840320, user=root, schema=db3, old shema=db3, borrowed=false, fromSlaveDB=true, threadId=0, charset=utf8, txIsolation=3, autocommit=true, Attachment =null, respHandler=null, host=192.168.8.117, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]Copy the code

MyCat is not allowed to connect to the secondary server when host=192.168.8.117 during startup. Therefore, it is possible that the host of the root account of the secondary node is localhost.

On the secondary server, after logging in to mysql, check that the host value of the root account is %, but do not execute Flush PRIVILEGES. Command. After the command is executed, restart MyCat and check the log file. If no ERROR message is displayed, MyCat is successfully started.

MyCat connection failed

MyCat as a database middleware, the connection command is still mysql, so you need to run the connection command on a server with mysql client installed, port and server IP point to the host where MyCat is located.

On secondary node 192.168.8.117, run the connect command on port 8066:

mysql -u root -p12345 -P 8066 -h 192.1688.182.
Copy the code

Execution Result:The cause of this exception is that the firewall of the server where MyCat resides is not closed, and the access continues after the firewall is closed:Error message changed to password error, compare server. XML password, continue login success, finally connected. The password configured in server. XML is actually the authentication information required by the application to connect to MyCat, and has nothing to do with the accounts of the primary and secondary MySQL databases.

Note that the password of the WriteHost and ReadHost connection accounts configured in schema. XML is the password of the corresponding primary and secondary MySQL nodes:


<writeHost host="hostM1" url="192.168.8.116:3306" 
    user="root" password="MasterXXX">
	<readHost host="hostS1" url="192.168.8.117:3306" 
	user="root" password="SlaveXXX!"/>
</writeHost>
Copy the code

They are used by MyCat to connect to the corresponding primary and secondary nodes for authentication, and eventually our database operations are allocated to the real MySQL database nodes.

Use MyCat

Now use MyCat’s Schema, just like MySQL, to verify that the logical database creates two new tables:MySQL > query shard database db1 where this table resides;MySQL > query shard database db1 where this table residesMaster and slave nodes, through MyCat middleware, automatic synchronization, perfect!