Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to understand the separation of Mycat reading and writing". In the daily operation, I believe that many people have doubts about how to understand the separation of reading and writing in Mycat. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts about "how to understand the separation of reading and writing in Mycat". Next, please follow the editor to study!

1 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 through 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, such a scheme to deploy and implement.

1.1 working principle of read-write separation:

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:

1.2 Why should read and write be separated:

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

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

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

4) increase redundancy and improve availability.

1.3 ways to achieve read-write separation:

There are generally two ways to implement it.

1) Application layer implementation, website program implementation

2) 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 very 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: it is 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 problems existing in cobar at that time, and added many new functions in it. At present, the MyCAT community is very active, and some companies are already using MyCAT. Generally speaking, the degree of support is relatively 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, some friends often say that they will often hang up under high concurrency. If you want to use it, you need to do a good test in advance.

MaxScale and MySQL Route:

Both 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: the impact on business code is small and secure at the same time.

Disadvantages:

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

2 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.

3 Service installation and configuration

Experimental framework

Server

IP

Description

Xuegod65

192.168.1.65

Mycat server, connect to this server when connecting to the database

Xuegod63

192.168.1.63

Physical database 1Personnal master

Xuegod64

192.168.1.64

Physical database 2

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

3.1 File structure

You can download Mycat-server-xxxxx.linux.tar.gz under linux and decompress it in a directory. Please note that there can be no spaces in the directory.

Under Linux (Unix), it is recommended to put it in the / usr/local/Mycat directory

Directory structure

Catalogue

Description

Bin

Mycat command, start, restart, stop, etc.

Catlet

Catlet is an extension of Mycat

Conf

Mycat configuration information, focusing on

Lib

Jar package referenced by Mycat. Mycat was developed by java.

Logs

Log files, including Mycat startup logs and running logs.

Configuration file

File

Description

Server.xml

Mycat configuration file, setting account, parameters, etc.

Schema.xml

Configuration of physical databases and database tables corresponding to Mycat

Rule.xml

Mycat fragmentation (subdatabase and table) rules

3.2 mycat installation (xuegod65) 1. Install JDK 1.7 or above

Step 1: download the jdk-8u131-linux-x64.tar.gz file http://haixi.sfkcn.com:8080/201704/tools/jdk-linux-x64.tar.gz

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@xuegod65 java] # source / etc/profile # to make the environment variable effective

View java-version

two。 Extract the mycat installation

Under linux, you can download Mycat-server-xxxxx.linux.tar.gz and extract it to / usr/local/,. Note that the directory cannot have spaces.

Create a mycat user and change the password useradd mycat

Modify permissions chown-R mycat.mycat / usr/local/mycat

Configure environment variables

1) 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.

Edit the hosts file

192.168.1.63 xuegod63.cn xuegod63

192.168.1.64 xuegod64.cn xuegod64

192.168.1.65 xuegod65.cn xuegod65

4 Service startup and startup settings

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

[root@xuegod65 ~] # Vim / usr/local/mycat/conf/server.xml

Druidparser

123456

Ha

User

Ha

True

Parameter description

User user configuration Node

-- the user name of the name login, that is, the user name of the connection to Mycat. This is for external applications.

-- password for password login, that is, password for connecting to Mycat

-- schemas database name, which is associated with the configuration in schema.xml. Multiple databases are separated by commas. For example, if you need this user to manage two databases db1,db2, configure db1,dbs.

-- privileges configures users' permissions to add, delete, modify and query tables. For more information, please see the document.

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

[root@xuegod65 ~] # vim / usr/local/mycat/conf/schema.xml

Select user ()

Here is a template explanation

Select user ()

The load balancer type of balance attribute. Currently, there are 4 values:

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

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, M2-> S2, and M1 and M2 are the master and standby of each other), under normal circumstances, M2Magi S1 and S2 participate in the load balancing of select statements.

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

Balance= "3", all read requests are randomly distributed to the corresponding readhost of wiriterHost for execution. WriterHost does not bear the read pressure. Note that balance=3 is only available in 1.4 and later versions, but not in 1.3.

WriteType attribute, load balancer type. Currently, there are three values:

WriteType= "0", all write operations are sent to the first writeHost of the configuration, and the first one hangs the second writeHost that is still alive. After restarting, the switch is recorded in the configuration file: dnindex.properties.

WriteType= "1", all write operations are randomly sent to the configured writeHost.

WriteType= "2", not realized.

SwitchType attribute

-1 means no automatic switching

1 default value, automatic switching

2 decide whether to switch based on the status of MySQL master-slave synchronization

5 configure Mysql master-slave

Create databases and tables on xuegod63 for write operations:

[root@xuegod63] # 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@ "192.168.1%" identified by "123456"

Mysql > flush privileges

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

Scp ha.sql xuegod64.cn:/root

[root@xuegod64 ~] # vim / etc/my.cnf

Log-bin=mysql-bin-master # enable binary logging

Server-id=1 # Native database ID marking

Binlog-do-db=HA # libraries that can be copied from the server, binary database names that need to be synchronized

Binlog-ignore-db=mysql # libraries that cannot be copied from the server

To turn off password strength audit, add validate-password=off to my.cnf

[root@xuegod64 ~] # systemctl restart mysqld

Import databases and tables on xuegod64 for read operations:

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

Mysql > flush privileges

[root@xuegod64 ~] # vim / etc/my.cnf

Server-id=2 # Native database ID marking

[root@xuegod64 ~] # systemctl restart mysqld

Mysql > stop slave; # stop slave

Mysql > change master to master_host='192.168.1.63',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@xuegod65~] # / usr/local/mycat/bin/mycat start

Test read-write separation

6 what happens when we hang up slave

Simulated failure: hung up from the server

[root@xuegod64 ~] # systemctl stop mysqld

Test read and write on the client

[root@xuegod66] # mysql-uroot-p123456-h 192.168.1.65-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@xuegod63 ~] # systemctl stop mysqld

Test read and write on the client

[root@xuegod66] # mysql-uroot-p123456-h 192.168.1.65-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

At this point, the study on "how to understand the separation of Mycat reading and writing" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.