Shulou(Shulou.com)06/02 Report--

This article introduces how to use mycat in MySQL to achieve read-write separation. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

An overview of MySQL read-write separation

Mysql, as the most widely used free database in the world, is believed to have been contacted by all engineers engaged in system operation and maintenance. But in the actual production environment, a single Mysql as an independent database can not meet the actual needs, whether in security, high availability, high concurrency and other aspects.

Therefore, generally speaking, it is deployed and implemented by master-slave replication (Master-Slave) to synchronize data, and then through read-write separation (MySQL-Proxy/Amoeba) to improve the concurrent load capacity of the database.

How read-write separation works:

The basic principle is to let the master database handle transactional add, modify, and delete operations (INSERT, UPDATE, DELETE), while the slave database handles SELECT query operations. Database replication is used to synchronize changes caused by transactional operations to slave databases in the cluster.

Internal data exchange process:

Why read-write separation:

In the face of increasing access pressure, the performance of a single server has become a bottleneck and needs to share the load.

The master and slave are only responsible for their own writing and reading, which greatly alleviates the contention between X lock and S lock.

Myisam engine can be configured from the library to improve query performance and save system overhead.

Increase redundancy and availability

The way to achieve read-write separation:

There are generally two ways to implement it.

Application layer implementation, website program implementation

Application layer implementation refers to the separation of read and write within the application and in the connector.

Advantages:

A: the separation of read and write is implemented inside the application, and the installation can use the

B: reduce the difficulty of deployment

C: the access pressure is below a certain level and the performance is good.

Disadvantages:

A: once the architecture is adjusted, the code will change accordingly

B: it is difficult to implement advanced applications, such as automatic database division and table division.

C: not suitable for large-scale application scenarios

Middleware layer implementation

Middleware layer implementation refers to the separation of reading and writing in external middleware programs.

Common middleware programs:

Cobar:

Alibaba B2B developed a relational distributed system that manages nearly 3000 MySQL instances. After Ali withstood the test, there was no one to maintain cobar due to the author's walking away, and Ali also developed tddl to replace cobar.

MyCAT:

Community enthusiasts carried out secondary development on the basis of Ali cobar, solved some of the problems existing in cobar at that time, and added many new functions in it. At present, the activity of the MyCAT community is very high, and some companies are already using MyCAT. Generally speaking, the support ratio is high, and it will be maintained all the time.

OneProxy:

Database industry Daniel, the former Alipay database team leader building chief development, based on mysql official proxy ideas using c for development, OneProxy is a commercial charge for middleware, the building gave up some function points, focusing on performance and stability. A friend has tested that it is stable under high concurrency.

Vitess:

This middleware is used by Youtube production, but the architecture is very complex. Different from the previous middleware, it is necessary to use the API interface of the language provided by him to use Vitess applications. We can learn from some of his design ideas.

Kingshard:

Kingshard was developed by Chen Fei of the former 360Atlas middleware development team using the go language during business time. At present, there are about 3 people involved in the development. At present, it is not a mature product that can be used and needs to be continuously improved.

Atlas:

The team rewrote lua in C based on mysql proxy. The original version supports sub-tables, and the sub-database sub-table version has been released. On the Internet, I see that some friends often say that they will often hang up under high and high hair. If you want to use it, you need to do a good test in advance.

MaxScale and MySQL Route:

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

Advantages:

A: architecture design is more flexible

B: some advanced controls can be implemented programmatically, such as transparent horizontal split, failover, monitoring

C: we can rely on some technical means to improve the performance of mysql

D: small impact on business code and security at the same time

Disadvantages:

Need the support of a certain development, operation and maintenance team

What is MYCAT?

A thoroughly open source large database cluster for enterprise application development

Enhanced database that supports transactions, ACID, and can replace MySQL

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

A new SQL Server that integrates memory cache technology, NoSQL technology and HDFS big data

A New Generation of Enterprise Database products combining traditional Database and New distributed data Warehouse

A novel database middleware product

2.1 Service installation and configuration

MyCAT provides compiled installation packages that can be installed and run on windows, Linux, Mac, Solaris and other systems. Official download home page http://www.mycat.io

You can download Mycat-server-xxxxx.linux.tar.gz under linux and decompress it in a certain directory. Note that the directory cannot have spaces. Under Linux (Unix), it is recommended to put it under the usr/local/Mycat directory, as follows:

Here is how to change the password of a MyCAT user (for reference only):

The catalog is explained as follows:

The bin program directory stores the window version and the linux version. In addition to the version encapsulated as a service, it also provides the shell script command of nowrap, which is convenient for you to select and modify, and enter the bin directory:

Run:. / mycat console under Linux, first of all, chmod + x *

Note: the command supported by mycat {console | start | stop | restart | status | dump}

The configuration file is stored in the conf directory, server.xml is the configuration file for Mycat server parameter adjustment and user authorization, schema.xml is the configuration file for logic library definition and table and sharding definition, rule.xml is the configuration file for sharding rules, and some specific parameter information of sharding rules is stored separately as files, also in this directory, the configuration file is modified and Mycat needs to be restarted or through port 9066 reload.

The lib directory mainly stores some jar files that mycat depends on.

The log is stored in logs/mycat.log, one file a day, and the configuration of the log is in conf/log4j.xml. According to your own needs, you can adjust the output level to debug,debug level, which will output more information to facilitate troubleshooting.

Note: when deploying and installing MySQL under Linux, table name case is not ignored by default. You need to manually configure lower_case_table_names=1 under / etc/my.cnf to make MySQL in Linux environment ignore table name case, otherwise an error will be prompted when using MyCAT.

Mycat requires JDK 1.7 or above to be installed

Step 1: download the jdk-8u131-linux-x64.tar.gz file

Step 2: create a new / usr/java folder, put jdk-8u131-linux-x64.tar.gz in this folder, and extract it to the current directory

Step 3: configure the environment variable to add the following at the bottom of / etc/profile:

JAVA_HOME=/usr/java/jdk1.8.0_131

PATH=$JAVA_HOME/bin:$PATH CLASSPATH=$JAVA_HOME/jre/lib/ext:$JAVA_HOME/lib/tools.jar

Export PATH JAVA_HOME CLASSPATH

[root@xuegod68 java] # source / etc/profile # to make the environment variable effective

View java-version

2.2 Service startup and startup settings

When MyCAT is deployed and launched in Linux, you first need to configure MYCAT_HOME in the environment variables of the Linux system, as follows:

Vi / etc/profile, added to the system environment variable file

MYCAT_HOME=/usr/local/mycat PATH=$MYCAT_HOME/bin:$PATH

2) execute the source / etc/profile command to make the environment variable effective.

If you are building a MyCAT cluster in multiple Linux systems, you need to configure the mapping of other ip and hostnames on the server where the MyCAT Server resides, as follows:

Vi / etc/hosts

For example, I have three machines, which are configured as follows:

IP hostname:

10.10.10.67 xuegod67.cn

10.10.10.68 xuegod68.cn

10.10.10.70 xuegod70.cn

When you are finished editing, save the file.

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

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

Druidparser

123456

Ha

User

Ha

True

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

Vim / usr/local/mycat/conf/schema.xml

Select user ()

There are two parameters to note, balance and switchType.

Among them, balance refers to the type of load balancer. Currently, there are four values:

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

2. Balance= "1", all readHost and stand by writeHost participate in the load balancing of select statements. To put it simply, when the double master and double slave mode (M1-> S1 less M2-> S2, and M1 and M2 are the master and standby of each other), under normal circumstances, M2Magin S1 S2 participates in the load balancing of select statements.

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

4. Balance= "3". All read requests are randomly distributed to the readhost corresponding to wiriterHost for execution. WriterHost does not bear the read pressure.

SwitchType refers to the switching mode. Currently, there are 4 values:

1. SwitchType='-1' means no automatic switching

2. Default value of switchType='1', which means automatic switching

3. SwitchType='2' decides whether to switch based on the status of MySQL master-slave synchronization, and the heartbeat statement is show slave status.

4. SwitchType='3' 's MySQL galary cluster-based handover mechanism (suitable for clusters) (1.4.1), and the heartbeat statement is show status like 'wsrep%'.

After the configuration of the above two steps, you can execute it under the / usr/local/mycat/bin directory:

. / mycat start

Start the mycat service!

Configure the Mysql master to create databases and tables on the xuegod68 for write operations:

[root@xuegod68] # mysql-uroot-p123456

Mysql > create database ha

Mysql > use ha

Mysql > create table test (id int,name varchar (20))

Mysql > insert into test values (1)

Account authorized to be used by mycat to log in to the database

Mysql > GRANT ALL PRIVILEGES ON *. * TO 'mycat'@ "%" IDENTIFIED BY "123456"

Mysql > grant replication slave on *. * to slave@ "10.10.10.%" identified by "123456"

Mysql > flush privileges

Mysqldump-uroot-p123456-B ha > ha.sql # Export to slave library

Turn off password strength audit and add validate-password=off in my.cnf

Import databases and tables on xuegod70 for read operations:

[root@xuegod70] # mysql-uroot-p123456 grant all on db.* to mycat@'%' identified by '123456'

Mysql > flush privileges

Mysql > stop slave; # stop slave

Mysql > change master to master_host='10.10.10.68',master_user='slave',master_password='123456'

Mysql > start slave; # start slave

Check the status of mysql > show slave status\ G. Two yes masters and slaves synchronized successfully!

Start the service mycat service

[root@xuegod67 ~] # / usr/local/mycat/bin/mycat start

Test read-write separation

What happens when we hang up slave?

Simulated failure: hung up from the server

[root@xuegod70 ~] # systemctl stop mysqld

Test read and write on the client

[root@xuegod67] # mysql-uroot-p123456-h 10.10.10.67-P8066

Mysql > use ha

Mysql > select * from T1

It shows that the route of the read operation has been switched to master, and there is no external impact!

What happens when we hang up master?

Simulated failure: the primary server is down

[root@xuegod68 ~] # systemctl stop mysqld

Test read and write on the client

[root@xuegod67] # mysql-uroot-p123456-h 10.10.10.67-P8066

Mysql > create table T3 (id int)

ERROR 1184 (HY000): deny connection # master database stopped, cannot write operation, but does not affect read.

Mysql > use ha

Mysql > select * from T1

On how to use mycat in MySQL to achieve read-write separation is shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.