Example of using mycat to implement MySQL database read-write separation

What is MyCAT

1. A completely open source large database cluster for enterprise application development
2. An enhanced database that supports transactions, ACID, and can replace MySQL
3. An enterprise-class database that can be viewed as a MySQL cluster to replace expensive Oracle clusters
4. A new SQL Server that integrates memory cache technology, NoSQL technology, and HDFS big data
5. A new generation of enterprise-level database products that combines traditional databases with new distributed data warehouses
6. A novel database middleware product

MyCAT Key Features

1. Support SQL92 standard
2. Supports common SQL syntax for MySQL, Oracle, DB2, SQL Server, PostgreSQL, etc.
3. A universal middleware proxy that complies with the MySQL native protocol and is cross-language, cross-platform, and cross-database.
4. Heartbeat-based automatic failover, support for read-write separation, support for MySQL master-slave, and Galera cluster.
5. Supports Galera for MySQL cluster, Percona Cluster or MariaDB cluster
6. Based on Nio implementation, it effectively manages threads and solves high concurrency problems.
7. It supports automatic routing and aggregation of multiple pieces of data, common aggregation functions such as sum, count, max, and cross-database paging.
8. It supports arbitrary joins within a single database, joins between two tables across databases, and even multi-table joins based on calllets.
9. It supports sharding strategies through global tables and ER relationships, and implements efficient multi-table join queries.
10. Supports multi-tenant scenarios.
11. Support distributed transactions (weak xa).
12. Support for XA distributed transactions (1.6.5).
13. Supports global serial numbers to solve the problem of primary key generation in distributed systems.
14. Rich sharding rules, plug-in development, and easy expansion.
15. Powerful web and command line monitoring.
16. The front end supports MySQL as a general proxy, and the back end JDBC method supports Oracle, DB2, SQL Server, mongodb, and Sequoia.
17. Support password encryption
18. Support service downgrade
19. Support IP whitelist
20. Support SQL blacklist and SQL injection attack interception
21. Support prepare precompilation instructions
22. Support non-heap memory (Direct Memory) aggregation calculation
23. Support PostgreSQL native protocol
24. Supports MySQL and Oracle stored procedures, out parameters, and multiple result sets returned
25. Support Zookeeper coordination of master-slave switching, ZK sequence, and ZK configuration
26. Supports table partitioning within the database
27. The cluster is based on ZooKeeper management, online upgrade, capacity expansion, intelligent optimization, and big data processing (2.0 development version).

1. Here, based on the implementation of MySQL master-slave replication, mycat is used for read-write separation. The architecture diagram is as follows:

2. Demo

2.1 Create a database on mysql master Create db1

2.2 Create table student in database db1

At the same time, because MySQL master-slave replication is configured, there are also the same databases and tables on MySQL slave

2.3 Edit the mycat configuration file server.xml

   <!-- indicates the login username of mycat -->
  <user name="test">
        <!-- indicates the login password of mycat -->
        <property name="password">test</property>
         <!-- indicates the logical database name of mycat, which can be customized -->
        <property name="schemas">TESTDB</property>
    </user>

    <user name="user">
        <property name="password">user</property>
        <property name="schemas">TESTDB</property>
        <property name="readOnly">true</property>
    </user>

2.4 Edit the mycat configuration file schema.xml

<!--TESTDB represents the logical database name of mycat. When the schema node has no child node table, the dataNode attribute must exist (pointing to the real mysql database).
-->
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
    </schema>
    <!--Specify the master database db1-->
    <dataNode name="dn1" dataHost="192.168.0.4" database="db1" />
    <!--Specify the masteret ip -->
    <dataHost name="192.168.0.4" maxCon="1000" minCon="10" balance="3"
        writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
        <!--Indicates the heartbeat status of MySQL-->
        <heartbeat>select user()</heartbeat>
        <!-- master is responsible for writing -->
        <writeHost host="hostM1" url="192.168.0.4:3306" user="root"
            password="admin">
        <!--slave is responsible for reading-->
        <readHost host="hostS2" url="192.168.0.5:3306" user="root" password="admin" />
        </writeHost>
</dataHost>

At this point, the configuration of using mycat for read-write separation has been completed

Note the following three properties of the dataHost node

balance, switchType, writeType

balance="0", the read-write separation mechanism is not enabled, and all read operations are sent to the currently available writeHost.

balance="1", all readHosts and stand by writeHosts participate in the load balancing of the select statement. Simply put, when in dual-master and dual-slave mode (M1->S1, M2->S2, and M1 and M2 are mutually master and slave), under normal circumstances, M2, S1, and S2 all participate in the load balancing of the select statement.

balance="2", all read operations are randomly distributed on writeHost and readhost.

balance="3", all read requests are randomly distributed to the readhost under writeHost for execution, and writeHost does not bear the read pressure

writeType indicates the write mode

writeType="0", all operations are sent to the first configured writehost

writeType="1", randomly send to all configured writehosts

writeType="2", no write operation is performed

switchType refers to the switching mode, and currently there are 4 possible values:

switchType='-1' means no automatic switching

switchType='1' default value, indicating automatic switching

switchType='2' determines whether to switch based on the status of MySQL master-slave synchronization. The heartbeat statement is show slave status

switchType='3' is based on the switching mechanism of MySQL galary cluster (suitable for cluster) (1.4.1), and the heartbeat statement is show status like 'wsrep%'.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.