Mycat realizes the separation of read and write in Mysql cluster

Overview of MySQL read-write separation

MySQL is currently the most widely used free database in the world, and I believe that all engineers engaged in system operation and maintenance must have contacted it.

In an actual production environment, a single MySQL as an independent database cannot meet actual needs at all, whether in terms of security, high availability, and high concurrency.

Therefore, in general, data is synchronized through master-slave replication (Master-Slave) , and then read-write separation (MySQL-Proxy/Amoeba) is used to improve the concurrent load capacity of the database for deployment and implementation.

Working principle of read-write separation

The basic principle is:

The main database handles transactional addition, modification, and deletion operations (INSERT, UPDATE, DELETE)

Handle SELECT query operations from the database

Database replication is used to synchronize changes caused by transactional operations to the slave database in the cluster.

Why should read and write separation

Faced with increasing access pressure, the performance of a single server has become a bottleneck, and the load needs to be shared

The master and the slave are only responsible for their own writing and reading, which greatly alleviates the contention of X (write) lock and S (read) lock

The myisam engine can be configured from the library to improve query performance and save system overhead

Increase redundancy, improve availability

Ways to achieve separation of read and write

  • There are generally two ways to achieve
  • Application layer realization, website program realization
  • Application layer implementation refers to the separation of read and write within the application and in the connector

advantage:

Read and write separation is realized inside the application, which can be used for installation

Reduce the difficulty of deployment

The access pressure is below a certain level, and the performance is very good

Disadvantages:

Once the architecture is adjusted, the code will change accordingly

Difficult to realize advanced applications, such as automatic sub-database, sub-table

Not applicable to large-scale application scenarios

Middleware layer realization:

The realization of the middleware layer refers to the separation of reading and writing in the external middleware program

Common middleware programs

Cobar:

The relational distributed system developed by Alibaba B2B manages nearly 3000 MySQL instances. It has withstood the test in Ali, and no one maintains cobar due to the author's departure. Ali also developed tddl to replace cobar.

MyCAT:

Community enthusiasts conducted secondary development on the basis of Ali cobar, which solved some of the problems that existed at the time of cobar, and added many new functions to it. The MyCAT community is currently very active, and some companies are already using MyCAT. Overall support ratio

If it is higher, it will continue to be maintained.

OneProxy:

A big cow in the database industry, the former Alipay database team led the development of the general manager. Based on the official proxy idea of ​​mysql, it was developed using c. OneProxy is a commercial fee-based middleware. The general manager has left out some functional points, focusing on performance and stability. Sexually. Someone tested

Tried to say that it is stable under high concurrency.

Vitess:

This middleware is used by YouTube production, but the architecture is very complicated. Different from the previous middleware, the application of Vitess has changed a lot. To use the API interface of the language provided by him, we can learn from some of his design ideas.

Kingshard:

Kingshard was developed by Chen Fei of the former 360Atlas middleware development team in go language in his spare time. There are currently about 3 people involved in the development. It is not a mature and usable product at present, and needs to be continuously improved.

Atlas:

The 360 ​​team rewrites lua with C based on mysql proxy. The original version supports sub-tables, and the sub-database and sub-table versions have been released. On the Internet, some friends often say that they will often hang up under high concurrency. If you want to use it, you need to do a test in advance.

MaxScale and MySQL Route:

These two middlewares are both official. MaxScale was developed by mariadb (a version maintained by the original author of MySQL). The current version does not support sub-database sub-table. MySQL Route is a middleware released by the official MySQL company Oracle.

advantage:

More flexible architecture design

Some advanced controls can be implemented in the program, such as: transparent horizontal splitting, failover, monitoring can rely on technical means to improve the performance of mysql. The impact on business code is small, and it is also safe.

Disadvantages:

A certain amount of support from the development and operation and maintenance team is required.


What is MyCAT

A completely open source large database cluster for enterprise application development

Support transactions, ACID, and an enhanced database that can replace MySQL

An enterprise-level database that can be regarded as a MySQL cluster to replace the expensive Oracle cluster

A new SQL Server integrating memory caching technology, NoSQL technology, and HDFS big data

A new generation of enterprise-level database products that combine traditional databases and new distributed data warehouses

A novel database middleware product


MyCat service installation and configuration

MyCat provides a compiled installation package, which supports installation and operation on Windows, Linux, Mac, Solaris and other systems

Official download homepage http://www.mycat.org.cn/

  • Experimental architecture:
  • 192.168.2.2 Mycat CentOS 8.3.2011
  • 192.168.2.3 main server CentOS 7.6
  • 192.168.2.5 Slave server CentOS 7.6
  • JDK 1.7 or above is required to run Mycat
  • bin program directory, run under Linux: ./mycat console, first chmod +x *  
    Note: the commands supported by mycat {console | start | stop | restart | status | dump} The  
    configuration file is stored in the conf directory: server.xml is Mycat The configuration file for server parameter adjustment and user authorization. schema.xml is the configuration file for logic library definitions, tables, and shard definitions, rule.xml is the configuration file for fragmentation rules, and specific parameter information for fragmentation rules is stored separately as a file , Also in this directory, if the configuration file is modified, Mycat needs to be restarted to take effect.
    The lib directory mainly stores some jar files that mycat depends on.
    The log is stored in logs/mycat.log, one file per day. The configuration of the log is in conf/log4j.xml. According to your needs, you can adjust the output level to debug. Under the debug level, more information will be output. Facilitate troubleshooting.

MyCat service startup and startup settings

When MyCAT is deployed and started in Linux, you first need to configure MYCAT_HOME in the environment variables of the Linux system. The operation method is as follows:

sudo vim /etc/profile.d/mycat.sh
MYCAT_HOME=/usr/local/mycat PATH=$MYCAT_HOME/bin:$PATH

Make environment variables take effect

. /etc/profile.d/mycat.sh

  • Start service
  • /usr/local/mycat/bin/mycat start
  • cat /usr/local/mycat/logs/wrapper.log

The user account and authorization information of mycat are configured in the conf/server.xml file

vim /usr/local/mycat/conf/server.xml

What is defined here is the user name and password for logging in to mycat on 192.168.2.2. The name can be customized. There is no mysqld service running on 192.168.2.2, the database name specified in schemes is a database that must exist on the server side!

Edit the MyCAT configuration file schema.xml, the configuration information about dataHost is as follows:

  • Back up the original configuration file
  • \cp /usr/local/mycat/conf/schema.xml{,.bak}
  • Edit configuration file

vim /usr/local/mycat/conf/schema.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat =" http://io.mycat/ ">      Pay attention to the URL here . Wrongly written will cause the startup to fail!
        <schema name="mydata" checkSQLschema="false" sqlMaxLimit="100" dataNode='dn1'>
        </schema>

<dataNode name="dn1" dataHost="dthost" database="mydata"/>
        <dataHost name="dthost" maxCon="500" minCon="10" balance="1" writeType="0" dbType="mysql "dbDriver="native" switchType="-1" slaveThreshold="100">
                <heartbeat> show slave status </heartbeat>
               <writeHost host="c7_2_3" url="192.168.2.3:3306" user="mycat" password ="123456"> Write server
                </writeHost>
                <writeHost host="c7_2_5" url="192.168.2.5:3306" user="mycat" password="123456"> Read server
                </writeHost>
         </dataHost>
 </mycat:schema>

Force all read operations to run on the read server, and only switch to the write server when writing data

Note that the mycat user here must be authorized on 192.168.2.3 and 2.5 on the master-slave database

GRANT ALL PRIVILEGES ON *.* TO'mycat'@'%' IDENTIFIED BY '123456';

Or specify the network segment

GRANT ALL PRIVILEGES ON *.* TO'mycat'@'192.168.2.%' IDENTIFIED BY '123456';

flush privileges;

If this error is reported and the server is operating normally, first check if there is authorization

ERROR 1184 (HY000): Invalid DataSource:0

schema: Logic library, corresponding to the Database in MySQL. The included Table is defined in a logic library.
  table : A table, that is, a table stored in a physical database, which is different from a traditional database. The table here needs to declare the logical data node DataNode stored in it. This is achieved through the definition of table fragmentation rules. The table can be defined It belongs to the "childTable". The fragmentation of the child table depends on the specific fragment address of the "parent table". Simply put, all the records of the child table belonging to a record A in the parent table are related to A is stored on the same slice.
Fragmentation rule: It is a binding definition of a field and a function. According to the value of this field, the serial number of the stored shard (DataNode) is returned. Each table can define a sharding rule, and the sharding rule can be flexibly extended. The default Provides number-based fragmentation rules, string fragmentation rules, etc.
  dataNode:  The logical data node of MyCAT is the specific physical node that stores the table, also called the shard node, which is related to a specific back-end database through the DataSource. Generally speaking, for high availability, each DataNode is set Two DataSources, one master and one slave. When the master node goes down, the system automatically switches to the slave node.
  dataHost: Defines the access address of a physical library, which is used to bind to the dataNode.

MyCAT currently uses configuration files to define logic libraries and related configurations:  
  logic libraries, tables,
  shard nodes, etc. are defined in MYCAT_HOME/conf/schema.xml;  sharding rules are defined in MYCAT_HOME/conf/rule.xml;  
  MYCAT_HOME/ Define user and system related variables, such as port, in conf/server.xml.

Note: The
schema tag is used to define the logic library in the MyCat instance, name: is followed by the logic library name. MyCat can have multiple logic libraries, and each logic library has its own related configuration. You can use schema tags to divide these different logic libraries.
The attribute checkSQLschema is false by default. The official document means whether to remove the name of the database in front of the table, "select * from db1.testtable", set it to true to remove db1. But if the name of db1 is not the name of the
schema, it will not be removed, so the official recommendation is not to use this syntax. At the same time, it is set to false by default.
sqlMaxLimit When the value is set to a certain value. For each executed SQL statement, if the limit statement is not added, MyCat will automatically add the corresponding value. For example, if you set the value to 100 and execute "select * from test_table", the effect will be
"selelct * from test_table limit 100". The
dataNode tag defines the data node in MyCat, which is what we usually call the data shard

  • Restart service
  • /usr/local/mycat/bin/mycat restart
    Stopping Mycat-server...
    Stopped Mycat-server.
    Starting Mycat-server...
    tail /usr/local/mycat/logs/wrapper.log

Configure MySQL master and slave

First edit /etc/my.cnf on the main database side 192.168.2.3

  • /etc/my.cnf
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock

    symbolic-links=0

    log-bin=/data/mysql/mysql-bin
    server-id= 1
    binlog-ignore-db=mysql
    binlog-ignore-db=information_schema
    binlog-ignore-db=performance_schema
    binlog-ignore-db=test
    innodb_flush_log_at_trx_commit=1
    binlog-do-db= mydata
    replicate-do-db= mydata
  • lower_case_table_names=1 Turn on case matching
  • Note that the database to be synchronized must exist in advance

After the startup is correct, configure /etc/my.cnf on the slave server 192.168.2.5

  • vim /etc/my.cnf  1  
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock

    symbolic-links=0

    log-bin=/data/mysql/mysql-bin
    server -id=2
    relay-log-index=/data/mysql/slave-relay-bin.index
    relay-log=/data/mysql/slave-relay-bin
    lower_case_table_names=1
  • read_only=1 Turn on read-only mode to prevent data from being written back, and will not affect slave synchronization and replication
  • lower_case_table_names=1 Turn on case matching
  • Perform the following operations after restarting the slave database service
  • Stop the slave from the server and create the slave database user
  • mysql -uroot -p123456 -e "stop slave"
    mysql -uroot -p123456 -e "grant replication slave on *.* to'slave'@'%' identified by '123456'"
    mysql -uroot -p123456 -e "select user ,password from mysql.user"
  • mysql -uroot -p123456 -e "change master to master_host='192.168.2.3',master_user='slave',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=245;"
    mysql -uroot- p123456 -e "start slave"
    mysql -uroot -p123456 -e "show slave status"

test

First export the backups of all the libraries of the main server 192.168.2.3

mysqldump -uroot -p --all-databases> /tmp/all_dbs.sql

Then import from the server 192.168.2.5

mysql -uroot -p </tmp/all_dbs.sql

  • Add some data on the main database side 192.168.2.3, and observe whether the slave database is synchronized
  • View from the server side
  • If there is a synchronization error, you need to stop the slave from the server, and then change the master again
  • Use slave user login test

Back to mycat server 192.168.2.2

  • Try to log in
  • mysql -uroot -p123456 -h192.168.2.2 -P8066
  • 8066 is the port number when mycat is running

Test read and write separation

  • mysql -uroot -p123456 -h192.168.2.2 -P9066 -e "show @@datasource"
  • 9066 is the management port of mycat
  • select * from mydata.mylist;

Write data or change data

  • insert into mydata.mylist values(10,'test');

Simulate failure, first stop the slave server 192.168.2.5

  • systemctl stop mariadb.service
  • Try to write data on 192.168.2.2
  • insert into mydata.mylist values(7,'gf');
  • View on the main server 192.168.2.3
  • Turn on the slave server 192.168.2.5
  • Simulate the main server 192.168.2.3 is down
  • The query is normal, try to write data
  • The query is normal but cannot be written
  • MM mode (master master copy)