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

This article mainly introduces "how MyCat realizes the separation of one master and two slaves from reading and writing in MySQL". In daily operation, I believe many people have doubts about how to achieve the separation of reading and writing from one master and two slaves in MyCat. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for everyone to answer the doubts of "how to achieve the separation of one master and two slaves in MySQL by MyCat". Next, please follow the editor to study!

Environment construction (one master and two slaves):

Macat:192.168.8.30

Master:192.168.8.31

Slave1:192.168.8.32

Slave2:192.168.8.33

Toolkit:

Java-1.8.0-openjdk-1.8.0.161-2.b14.el7.x86_64

Mycat-server-1.6.6.1-release-20181031195535-linux.tar.gz

First, install JDK and MyCat

JDK is installed on the system. Check the installation path.

[root@mycat] # java-versionopenjdk version "1.8.0mm 161" OpenJDK Runtime Environment (build 1.8.0_161-b14) OpenJDK 64-Bit Server VM (build 25.161-b14, mixed mode) [root@mycat] # ls-l / usr/bin/javalrwxrwxrwx. 1 root root 22 Oct 22 10:30 / usr/bin/java-> / etc/alternatives/java [root@mycat ~] # ls-l / etc/alternatives/javalrwxrwxrwx. 1 root root 71 Oct 22 10:30 / etc/alternatives/java-> / usr/lib/jvm/java-1.8.0-openjdk-1.8.0.161-2.b14.el7.x86_64/jre/bin/java

Install MyCat

Cd / softwaretar zxvf Mycat-server-1.6.6.1-release-20181031195535-linux.tar.gzcp-r mycat / usr/local/

Configure environment variables

[root@mycat] # cat ~ / .bash_profile# .bash _ profile# Get the aliases and functionsif [- f ~ / .bashrc]; then. ~ / .bashrcfi # User specific environment and startup programsexport JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.161-2.b14.el7.x86_64export JRE_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.161-2.b14.el7.x86_64/jreexport MYCAT_HOME=/usr/local/mycatPATH=$PATH:/mysql/app/mysql/bin:/mysql/app/xtrabackup/bin:$HOME/bin:/ Usr/bin:/sbin:/bin:$MYCAT_HOME/bin:$JAVA_HOME/binexport PATH

Second, create a test library

Master creation, slave synchronization

Create database testdb1;create table testdb1.t11 (name1 varchar (40), name2 varchar (40), name3 varchar (40)); create database testdb2;create table testdb2.t21 (name1 varchar (40), name2 varchar (40), name3 varchar (40)); create database testdb3;create table testdb3.t31 (name1 varchar (40), name2 varchar (40), name3 varchar (40))

3. Configure schema.xml

Select user ()

Schema tag: the schema name attribute specifies the logical library name, and the dataNode attribute specifies the following dataNode

CheckSQLschema indicates whether to check and filter cases where schema is contained in SQL. If the logic library is mycatdb1, it may be written as select * from mycatdb1.t11. In this case, mycatdb1,SQL will be automatically filtered to select * from T11. If the above writing method does not appear, you can disable the attribute to false.

The maximum number of records returned by sqlMaxLimit is limited by default. In the MyCat1.4 version, the user's Limit parameter overrides the sqlMaxLimit default setting of MyCat.

DataNode tag: dataHost specifies the following dataHost,database specifies the specific database

DataHost tag: balance refers to the type of load balancer, and switchType refers to the switching mode.

The value of the host attribute in each dataHost in schema must be unique, otherwise the master-slave switch will occur in all dataHost.

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, M2LJS 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.

Therefore, the balance= "1" in the configuration file means that hostS1 and hostS2, as stand by writeHost, will participate in the load balancing of select statements, which realizes the read-write separation of master and slave.

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.

MyCat1.4 starts to support the read-write separation mechanism of MySQL master-slave replication state binding, which makes the read more secure and reliable. The configuration is as follows:

The MyCat heartbeat check statement is configured to define two new attributes on show slave status,dataHost: switchType= "2" and slaveThreshold= "100", which means that the read-write separation and switching mechanism of MySQL master-slave replication state binding is enabled. The MyCat heartbeat mechanism detects "Seconds_Behind_Master", "Slave_IO_Running" and "Slave_SQL_Running" in show slave status.

Three fields are used to determine the status of the current master-slave synchronization and the Seconds_Behind_Master master-slave replication delay. When the Seconds_Behind_Master is greater than slaveThreshold, the read-write separation filter will filter out the Slave machine to prevent reading the old data from a long time ago. When the master node is down, the switching logic will check whether the Seconds_Behind_Master on the Slave is 0. If 0 means master-slave synchronization, it can be switched safely, otherwise it will not be switched.

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

4. Configure server.xml

Mysql mycatdb1,mycatdb2,mycatdb3 user mycatdb1,mycatdb2,mycatdb3 true

5. Modify the MyCat log level

Vi log4j2.xml

Modify

For

6. Start mycat

[root@mycat conf] # / usr/local/mycat/bin/mycat startStarting Mycat-server...

View the log

STATUS | wrapper | 11:08:50 on 2018-11-21 |-- > Wrapper Started as DaemonSTATUS | wrapper | 11:08:51 on 2018-11-21 | Launching a JVM...INFO | jvm 1 | 11:08:51 on 2018-11-21 | OpenJDK 64-Bit Server VM warning: ignoring option MaxPermSize=64M Support was removed in 8.0INFO | jvm 1 | 11:08:54 on 2018-11-21 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.orgINFO | jvm 1 | 11:08:54 on 2018-11-21 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.INFO | jvm 1 | 11:08:54 on 2018-11-21 | INFO | jvm 1 | 11:08:58 on 2018-11-21 | MyCAT Server startup successfully. See logs in logs/mycat.log

View Port

[root@mycat conf] # netstat-an | grep 8066tcp6 0 0:: 8066

View mycat processes

[root@mycat conf] # ps-ef | grep mycatavahi 646 10 09:56? 00:00:03 avahi-daemon: running [mycat.local] root 2653 1 0 11:08? 00:00:00 / usr/local/mycat/bin/./wrapper-linux-x86-64 / usr/local/mycat/conf/wrapper.conf wrapper.syslog.ident=mycat wrapper.pidfile=/usr/local/mycat/logs/mycat.pid wrapper.daemonize=TRUE wrapper.lockfile=/var / lock/subsys/mycaroot 2655 2653 7 11:08? 00:00:07 java-DMYCAT_HOME=. -server-XX:MaxPermSize=64M-XX:+AggressiveOpts-XX:MaxDirectMemorySize=2G-Dcom.sun.management.jmxremote-Dcom.sun.management.jmxremote.port=1984-Dcom.sun.management.jmxremote.authenticate=false-Dcom.sun.management.jmxremote.ssl=false-Xmx4G-Xms1G-Djava.library.path=lib-classpath lib/wrapper.jar:conf:lib/asm-4.0.jar:lib/commons-collections-3.2.1.jar:lib/commons-lang-2.6.jar:lib/curator-client-2.11.0. Jar:lib/curator-framework-2.11.0.jar:lib/curator-recipes-2.11.0.jar:lib/disruptor-3.3.4.jar:lib/dom4j-1.6.1.jar:lib/druid-1.0.26.jar:lib/ehcache-core-2.6.11.jar:lib/fastjson-1.2.12.jar:lib/guava-19.0.jar:lib/hamcrest-core-1.3.jar:lib/hamcrest-library- 1.3.jar:lib/jline-0.9.94.jar:lib/joda-time-2.9.3.jar:lib/jsr305-2.0.3.jar:lib/kryo-2.10.jar:lib/leveldb-0.7.jar:lib/leveldb-api-0.7.jar:lib/libwrapper-linux-ppc-64.so:lib/libwrapper-linux-x86-32.so:lib/libwrapper-linux-x86-64.soVera libmax log4jMel 1.2- Api-2.5.jar:lib/log4j-1.2.17.jar:lib/log4j-api-2.5.jar:lib/log4j-core-2.5.jar:lib/log4j-slf4j-impl-2.5.jar:lib/mapdb-1.0.7.jar:lib/minlog-1.2.jar:lib/mongo-java-driver-2.11.4.jar:lib/Mycat-server-1.6.6.1-release.jar:lib/mysql-binlog- Connector-java-0.16.1.jar:lib/mysql-connector-java-5.1.35.jar:lib/netty-3.7.0.Final.jar:lib/netty-buffer-4.1.9.Final.jar:lib/netty-common-4.1.9.Final.jar:lib/objenesis-1.2.jar:lib/reflectasm-1.03.jar:lib/sequoiadb-driver-1.12.jar:lib/slf4j-api-1.6.1.jar: Lib/univocity-parsers-2.2.1.jar:lib/velocity-1.7.jar:lib/wrapper.jar:lib/zookeeper-3.4.6.jar-Dwrapper.key=G87oQ7EZv67RXK9D-Dwrapper.port=32000-Dwrapper.jvm.port.min=31000-Dwrapper.jvm.port.max=31999-Dwrapper.pid=2653-Dwrapper.version=3.2.3-Dwrapper.native_library=wrapper-Dwrapper.service=TRUE-Dwrapper.cpu.timeout=10-Dwrapper.jvmid=1 org.tanukisoftware.wrapper.WrapperSimpleApp io.mycat.MycatStartup start

7. Log in to mysql and view the logic library

[root@mycat] # mysql-uroot-pmysql-P8066-h292.168.8.30mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 1Server version: 5.6.29-mycat-1.6.6.1-release-20181031195535 MyCat Server (OpenCloudDB) Copyright (c) 2009-2018 Percona LLC and/or its affiliatesCopyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c' to clear the current input statement.mysql > show databases;+-+ | DATABASE | +-+ | mycatdb1 | | mycatdb2 | | mycatdb3 | +-+ 3 rows in set (0.01 sec) mysql > use mycatdb1;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > show tables +-+ | Tables_in_testdb1 | +-+ | T11 | +-+ 1 row in set (0.04 sec) mysql > select * from t11 politics empty set (0.22 sec)

VIII. Read-write separation verification

[root@mycat conf] # mysql-uroot-pmysql-P8066-h292.168.30-e "select @ @ hostname" mysql: [Warning] Using a password on the command line interface can be insecure.+-+ | @ @ hostname | +-+ | slave1 | +-+ [root@mycat conf] # mysql-uroot-pmysql-P8066-h292.168.30-e "select @ @ hostname" mysql: [Warning] Using a password on the command line interface can be insecure.+-+ | @ @ hostname | +-+ | slave1 | +-+ [root@mycat conf] # mysql-uroot-pmysql-P8066-h292.168.30-e "select @ @ hostname" mysql: [Warning] Using a password on the command line interface can be insecure. +-+ | @ @ hostname | +-+ | slave1 | +-+ [root@mycat conf] # mysql-uroot-pmysql-P8066-h292.168.30-e "select @ @ hostname" mysql: [Warning] Using a password on the command line interface can be insecure.+-+ | @ @ hostname | +- -+ | slave2 | +-+ [root@mycat conf] # mysql-uroot-pmysql-P8066-h292.168.30-e "select @ @ hostname" mysql: [Warning] Using a password on the command line interface can be insecure.+-+ | @ @ hostname | +-+ | slave1 | +-+ [root@mycat conf] # mysql-uroot-pmysql-P8066-h292.168.30-e "select @ @ hostname" mysql: [Warning] Using a password on the command line interface can be insecure.+-+ | @ @ hostname | +-+ | slave2 | +-+ [root@mycat conf] # mysql-uroot-pmysql-P8066-h292.168.30-e "select @ @ hostname" mysql: [Warning] Using a password on the command line interface can be insecure.+-+ | @ @ hostname | +-+ | slave2 | +-+ [root@mycat conf] # mysql-uroot-pmysql-P8066-h292.168.30-e "select @ @ hostname" mysql: [Warning] Using a password on the command line interface can be insecure. +-+ | @ @ hostname | +-+ | slave2 | +-+ [root@mycat conf] # mysql-uroot-pmysql-P8066-h292.168.30-e "select @ @ hostname" mysql: [Warning] Using a password on the command line interface can be insecure.+-+ | @ @ hostname | +- -+ | slave1 | +-+ [root@mycat conf] # mysql-uroot-pmysql-P8066-h292.168.30-e "select @ @ hostname" mysql: [Warning] Using a password on the command line interface can be insecure.+-+ | @ @ hostname | +-+ | slave2 | +-+ [root@mycat conf] # mysql-uroot-pmysql-P8066-h292.168.30-e "select @ @ hostname" mysql: [Warning] Using a password on the command line interface can be insecure.+-+ | @ @ hostname | +-+ | slave1 | +-+ [root@mycat conf] # mysql-uroot-pmysql-P8066-h292.168.30-e "select @ @ hostname" mysql: [Warning] Using a password on the command line interface can be insecure.+-+ | @ @ hostname | +-+ | slave1 | +-+ [root@mycat conf] # mysql-uroot-pmysql-P8066-h292.168.30-e "select @ @ hostname" mysql: [Warning] Using a password on the command line interface can be insecure. +-+ | @ @ hostname | +-+ | slave2 | +-+ [root@mycat conf] # mysql-uroot-pmysql-P8066-h292.168.30-e "select @ @ hostname" mysql: [Warning] Using a password on the command line interface can be insecure.+-+ | @ hostname | +-+ | slave2 |

You can see that the read operations are all in slave1 and slave2.

Note: the load balancer here does not poll on slave1 and slave2, but maintains the load balance as a whole.

[root@mycat conf] # mysql-uroot-pmysql-P8066-h292.168.30-e "insert into mycatdb1.t11 values (@ @ hostname,@@hostname,@@hostname)" mysql: [Warning] Using a password on the command line interface can be insecure. [root@mycat conf] # mysql-uroot-pmysql-P8066-h292.168.30-e "insert into mycatdb1.t11 values (@ @ hostname,@@hostname) @ @ hostname) "mysql: [Warning] Using a password on the command line interface can be insecure. [root@mycat conf] # mysql-uroot-pmysql-P8066-h292.168.30-e" insert into mycatdb1.t11 values (@ @ hostname,@@hostname,@@hostname) "mysql: [Warning] Using a password on the command line interface can be insecure. [root@mycat conf] # mysql-uroot-pmysql-P8066-h292.168.30-e" insert into mycatdb1.t11 values (@ @ hostname,@@hostname) @ @ hostname) "mysql: [Warning] Using a password on the command line interface can be insecure. [root@mycat conf] # mysql-uroot-pmysql-P8066-h292.168.30-e" insert into mycatdb1.t11 values (@ @ hostname,@@hostname,@@hostname) "mysql: [Warning] Using a password on the command line interface can be insecure. [root@mycat conf] # mysql-uroot-pmysql-P8066-h292.168.30-e" insert into mycatdb1.t11 values (@ @ hostname,@@hostname) @ @ hostname) "mysql: [Warning] Using a password on the command line interface can be insecure. [root@mycat conf] # mysql-uroot-pmysql-P8066-h292.168.30-e" insert into mycatdb1.t11 values (@ @ hostname,@@hostname,@@hostname) "mysql: [Warning] Using a password on the command line interface can be insecure. [root@mycat conf] # mysql-uroot-pmysql-P8066-h292.168.30-e" insert into mycatdb1.t11 values (@ @ hostname,@@hostname) @ @ hostname) "mysql: [Warning] Using a password on the command line interface can be insecure. [root@mycat conf] # mysql-uroot-pmysql-P8066-h292.168.30-e" insert into mycatdb1.t11 values (@ @ hostname,@@hostname @ @ hostname) "mysql: [Warning] Using a password on the command line interface can be insecure. [root@mycat conf] # mysql-uroot-pmysql-P8066-h292.168.30-e" select * from mycatdb1.t11 "mysql: [Warning] Using a password on the command line interface can be insecure.+-+ | name1 | name2 | name3 | + -+-+ | master | master | master | | master | master | | master | +-+

IX. Master-slave switching test

Close the MySQL process on master

[root@mycat conf] # mysql-uroot-pmysql-P8066-h292.168.30-e "insert into mycatdb1.t11 values (@ @ hostname,@@hostname,@@hostname)" mysql: [Warning] Using a password on the command line interface can be insecure.ERROR 1184 (HY000) at line 1: java.net.ConnectException: Connection refused [root@mycat conf] # mysql-uroot-pmysql-P8066-h292.168.30-e "insert into mycatdb1.t11 values (@ @ hostname,@@hostname) @ @ hostname) "mysql: [Warning] Using a password on the command line interface can be insecure.ERROR 1184 (HY000) at line 1: java.net.ConnectException: Connection refused [root@mycat conf] # mysql-uroot-pmysql-P8066-h292.168.30-e" insert into mycatdb1.t11 values (@ @ hostname,@@hostname @ @ hostname) "mysql: [Warning] Using a password on the command line interface can be insecure. [root@mycat conf] # mysql-uroot-pmysql-P8066-h292.168.30-e" insert into mycatdb1.t11 values (@ @ hostname,@@hostname,@@hostname) "mysql: [Warning] Using a password on the command line interface can be insecure. [root@mycat conf] # mysql-uroot-pmysql-P8066-h292.168.30-e" insert into mycatdb1.t11 values (@ @ hostname,@@hostname) @ @ hostname) "mysql: [Warning] Using a password on the command line interface can be insecure. [root@mycat conf] # mysql-uroot-pmysql-P8066-h292.168.30-e" select * from mycatdb1.t11 "mysql: [Warning] Using a password on the command line interface can be insecure.+-+ | name1 | name2 | name3 | + -+ + | master | master | master | | master | master | | slave1 | +-+

After a brief connection failure, the write operation is connected to the slave1, and the master-slave switch is successful.

At this point, the study on "how to achieve the separation of one master and two slaves in MySQL by MyCat" 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.