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

Links to mycat:

Mycat official website

Mycat download link

Mycat official document, Baidu online disk official document download link, extraction code: f9nq.

Mycat abbreviated documentation

You can read the Mycat brief documentation to get a general idea of it before you begin.

Note: MyCAT supports multiple database access, including mongodb, oracle, sqlserver, hive, db2 and postgresql.

Mycat application scenarios are as follows: simple read-write separation, when the configuration is the simplest, support read-write separation, master-slave switching; sub-table and sub-library, for more than 10 million tables, up to 100 billion single-table sharding; multi-tenant applications, each application has one library, but the application only connects to Mycat, so as not to transform the program itself and realize multi-tenancy. The report system, with the help of the table-dividing ability of Mycat, handles the statistics of large-scale reports; instead of Hbase, it analyzes big data; as a simple and effective scheme for real-time query of massive data, for example, 10 billion records that are frequently queried need to query the results within 3 seconds, in addition to the query based on the primary key, there may also be range query or other attribute query, Mycat may be the most simple and effective choice.

There are three important technical terms in mycat, which are briefly written here:

Schema: logical library, corresponding to the databases (database) in MySQL. A logical library defines the included table. Table: a logical table is a table stored in a physical database. Different from the traditional database, the table here declares its stored logical data node datanode, and can specify the slicing rules of the table again. The logical data node of the datanode:mycat is the specific physical node where the table is stored, also known as the sharding node, which is associated to a specific database at the back end through datasource. Datasource: defines the access address of a physical library, which is used to bind to the datanode.

The functions of this blog are as follows:

There are two MySQL servers, which are used for master-slave replication, and one mycat server, which separates reads and writes from two MySQL servers.

The above environment is similar to the web cluster, the front-end nginx agent (in this case, mycat), the back-end two MySQL services, the slave server for reading data, and the master server for writing data.

1. Prepare the mycat deployment environment system IP hostname service Centos 7.5192.168.20.2mysql01MySQL 5.7.24Centos 7.5192.168.20.3mysql02MySQL 5.7.24Centos 7.5192.168.20.4mycatMycat

The installation and deployment of mycat requires version jdk1.7 or above, and version 5.5 or above is recommended for mysql.

For self-deployment of two MySQL masters and slaves, please refer to the blog post: MySQL High availability solution-dual Master (Note: you only need to refer to the blog article to make the master / slave effect, and you don't need double masters or keepalived to do high availability).

2. Install JDK [root @ mycat ~] # java-version # to see if the jdk version is suitable for openjdk version "1.8.0root 161" OpenJDK Runtime Environment (build 1.8.0_161-b14) OpenJDK 64-Bit Server VM (build 25.161-b14, mixed mode) # if the above instruction returns an error, you can execute the following command to install the jdk environment [root@mycat ~] # yum-y install java 3 and deploy mycat

Download the mycat package, decompress it directly and use it.

# download and extract [root@mycat src] # wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz[root@mycat src] # tar zxf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz-C / usr/local/# to view the extracted directory structure [root@mycat src] # tree-L 1 / usr/local/mycat//usr/local/mycat/ ├── Bin ├── catlet ├── conf ├── lib ├── logs └── version.txt

Where:

Bin: startup directory; lib:mycat 's own jar package or dependent jar package storage directory; logs:mycat log storage directory, the log is stored in logs/log, a file per day; conf: configuration directory, used to store configuration files-server.xml: configuration file for Mycat server parameter adjustment and user authorization. -- schema.xml: is the configuration file for logical library definitions and tables and shard definitions. -- rule.xml: the configuration file of the sharding rule. Some specific parameter information of the sharding rule is stored separately as a file. Also in this directory, MyCAT needs to be restarted when the configuration file is modified. -- log4j.xml: the log is stored in logs/log, and one file per day. The log is configured in conf/log4j.xml. You can adjust the output level to debug debug level according to your needs. More information will be output to facilitate troubleshooting. -- autopartition-long.txt,partition-hash-int.txt,sequence_conf.properties, id sharding rule configuration files related to sequence_db_conf.properties sharding

The following picture describes the three most important configuration files of mycat:

4. Modify the mycat configuration file to achieve read-write separation for the back-end database. 1) modify the server.xml file # specify the user name and password of the client connection mycat, where the account password has nothing to do with the MySQL database [root@mycat mycat] # vim conf/server.xml # navigate to about line 80 Modify the following # mycat as the user name pwd@123 # password teset_mycat # here the logical library name 2) modify the schema.xml file select user ()

Note: the dataHost field balance load balancer type mentioned above has the following four values:

Balance= "0", the read-write separation mechanism is not enabled, and all read operations are sent to the currently available writeHost (the default). 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-> S1J M2-> S2, and M1 and M2 are the master and standby of each other), under normal circumstances, M2M S1jue S2 is involved 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.

The writeTyep field has values from the following 3:

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

2. WriteType= "1". It is not recommended that all write operations are randomly sent to the configured writeHost,1.5. SwitchType attribute

-1 means that the switch is not automatic. 1 default value, automatically switched. 2 decide whether to switch based on the status of MySQL master-slave synchronization. 3) Test whether the mycat host can log in to the two backend databases # copy a mysql command [root@mycat mycat] # scp root@192.168.20.2:/usr/local/mysql/bin/mysql / usr/local/bin/# to log in to the two backend databases [root@mycat mycat] # mysql-uroot-p123.com-h 192.168.20.2 [root@mycat mycat] # mysql-uroot-p123.com-h 192. 168.20. Ensure that when you log in to the database by executing the above instructions Can log in successfully. # if the login is not successful, consider whether the root user of the database has the permission to log in remotely, or the firewall. 4) since some configurations are to be deleted in the above configuration file, if they are not deleted, the startup will fail, so the modified complete configuration file is attached here.

The server.xml file is as follows:

0 0 2 0 0 1 1m 1k 0 384m true pwd@123 teset_mycat

The conf/schema.xml file is as follows:

Select user () 5. Start mycat./mycat start start. / mycat stop stop. / mycat console foreground run. / add mycat install to system auto startup (not implemented). / mycat remove cancel auto start with system (not implemented). / mycat restart restart service. / mycat pause pause. / mycat status view startup status # create command soft connection [root@mycat mycat] # ln-sf / usr/local/mycat/bin/mycat / usr/local/bin/ # launch mycat [root @ mycat mycat] # mycat start # it is more recommended to use mycat console foreground to launch If there is an error, it will be output directly to the screen to facilitate troubleshooting # after debugging is normal, then use mycat start background to start. Starting Mycat-server... [root@mycat conf] # ss-lnp | grep 8066 # if the port is not listening, the startup failed. Check the log and check it. Tcp LISTEN 0 100: 8066:: * users: (("java", pid=62070,fd=78)) # Log in with the username and password defined in the server.xml file to view [root@mycat conf] # mysql-umycat-ppwd@123-h 192.168.20.4-P 8066mysql > show databases # the teset_ mycat library below corresponds to the backend test library +-+ | DATABASE | +-+ | teset_mycat | +-+ 1 row in set (0.00 sec) mysql > all the table data in the use teset_mycat# library and the backend database correspond to mysql > show tables +-+ | Tables_in_test | +-+ | T1 | +-+ 1 row in set (0.00 sec) mysql > select * from T1 +-+-+ | id | name | +-+-+ | 1 | a | 2 | b | 3 | c | 4 | d | +-+-+

So far, I have successfully connected to mycat to connect to the backend database, but the main features of mycat have not been demonstrated yet. I will continue to update the following content. In fact, it is more recommended to read the official Mycat documentation. Official documents are also relatively easy to understand.

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.