Highly available architecture chapter--mycat read and write separation based on MySQL master-slave replication

Click the link to join the group "Dubbo Technology 2 Group": Https://jq.qq.com/?_wv=1027&k=46DcDFI

First, the environment

Operating system: Centos-6.6-x86_64-bin-dvd1.iso

JDK version: jdk1.7.0_45

Mycat version: mycat-server-1.4-release-20151019230038-linux.tar.gz

MYCAT node ip:192.168.1.203 host name: EDU-MYCAT-01 Host Configuration: 4-core CPU, 4G memory

MySQL version: mysql-5.6.26.tar.gz

Master Node ip:192.168.1.205 hostname: EDU-MYSQL-01 Host Configuration: 4-core CPU, 4G memory

From node ip:192.168.1.206 hostname: EDU-MYSQL-02 Host Configuration: 4-core CPU, 4G memory

Ii. Dependent Courses

High-Availability Architecture-section 13th--mysql Source compilation installation (centos-6.6+mysql-5.6)

High-Availability Architecture-section 14th--mysql configuration of Master-slave replication (centos-6.6+mysql-5.6)

Note: In the previous lesson, the MySQL master-slave replication configuration, in the use of Mycat master from the read-write separation or its actual project scenario application, the principal-slave replication configuration also needs to be adjusted according to the actual demand situation.

(Adjusted master-slave database my.cnf configuration file, supplied with video tutorial compression pack)

Three, Mycat introduction (MYCAT official website: http://mycat.org.cn/)

Mycat's read-write separation is based on the master-slave synchronization of the backend MySQL cluster, while the MYCAT provides the distribution of the statement. MyCat1.4 began to support MySQL master-slave replication state binding read-write separation mechanism, so that read more secure and reliable.

Iv. installation of Mycat

1. Set the host name and IP and hostname mappings for Mycat

# vi/etc/sysconfig/network

Networking=yes

Hostname=edu-mycat-01

# vi/etc/hosts

127.0.0.1 edu-mycat-01

192.168.1.203 edu-mycat-01

192.168.1.205 edu-mysql-01

192.168.1.206 edu-mysql-02

2, because the MYCAT is developed in Java, so the Mycat run requires the installation of JDK (the JRE is accurate enough), and requires JDK1.7 or above version

# Vi/etc/profile

# # Java ENV

Export java_home=/usr/local/java/jdk1.7.0_72

Export Jre_home= $JAVA _home/jre

Export classpath=.: $JAVA _home/lib/dt.jar: $JAVA _home/lib/tools.jar: $JRE _home/lib/rt.jar

Export path= $PATH: $JAVA _home/bin: $JRE _home/bin

# Source/etc/profile

# java-version

3. Create Mycat user and set password

# Useradd Mycat

# passwd Mycat

4, upload the installation package mycat-server-1.4-release-20151019230038-linux.tar.gz to the MYCAT server/home/mycat directory, and extract and move to the/usr/local/mycat directory

$ TAR-ZXVF mycat-server-1.4-release-20151019230038-linux.tar.gz

$ su Root

Password:

# mv/home/mycat/mycat/usr/local/

# cd/usr/local/mycat/

# LL

5. Setting MYCAT Environment variables

# Vi/etc/profile

# # Mycat Env

Export Mycat_home=/usr/local/mycat

Export path= $PATH: $MYCAT _home/bin

# Source/etc/profile

V. Configuration Mycat

1. Before configuring Mycat, make sure that MySQL's master-slave copy installation configuration is complete and functioning properly. MySQL master-slave data synchronization is configured in MySQL, Mycat is not responsible for data synchronization problems.

Add:

(1) in MySQL master-slave replication configuration, if it involves synchronous replication of functions or stored procedures, you need to increase the configuration log_bin_trust_function_creators=true in the [mysqld] segment in/ETC/MY.CNF or set it in the client Set global log_bin_trust_function_creators = 1;

(2) If you want to do a master-slave switchover under the read/write separation, then the slave node may also become a write node, so the slave node cannot be set to read-only Read_only=1.

(3) The Linux version of MySQL needs to be set to MySQL case insensitive, otherwise it may occur that the table is not found. You can add Lower_case_table_names=1 in the [mysqld] segment of/ETC/MY.CNF.

2, configure the Mycat schema.xml

Schema.xml is one of Mycat's most important configuration files for setting Mycat logical libraries, tables, data nodes, Datahost, and more.

[Email protected] conf]$ cd/usr/local/mycat/conf/

[Email protected] conf]$ VI schema.xml

<?xml version= "1.0"?>

<! DOCTYPE mycat:schema SYSTEM "SCHEMA.DTD" >

<mycat:schema xmlns:mycat= "http://org.opencloudb/" >

<schema name= "Rc_schema2" checksqlschema= "false" sqlmaxlimit= "datanode=" "RC_DN2" >schema>

<schema name= "Pay_schema2" checksqlschema= "false" sqlmaxlimit= "datanode=" "PAY_DN2" >schema>

<datanode name= "rc_dn2" datahost= "DtHost2" database= "Roncoo"/>

<datanode name= "pay_dn2" datahost= "DtHost2" database= "Edu_simple_pay"/>

<datahost name= "DtHost2" maxcon= "$" mincon= "balance=" 1 "

Writetype= "0" dbtype= "MySQL" dbdriver= "native" switchtype= "2" slavethreshold= ">"

<writehost host= "hostM2" url= "192.168.1.205:3306" user= "root" password= "www.roncoo.com"/>

<writehost host= "hostS2" url= "192.168.1.206:3306" user= "root" password= "www.roncoo.com"/>

Datahost>

Mycat:schema>

MyCat1.4 began to support the MySQL master-slave replication state binding read-write separation mechanism, so that read more secure and reliable, configured as follows:

The Mycat heartbeat Check statement is configured to show slave status, and two new attributes are defined on Datahost: switchtype= "2" and slavethreshold= "100", This means that the read-write separation and switching mechanism of the MySQL master-slave replication state binding is turned on, and the mycat heartbeat mechanism is detected by detecting show slave status

"Seconds_behind_master", "slave_io_running", "slave_sql_running" three fields to determine the current state of master-slave synchronization and Seconds_behind_master master-slave replication delay, When Seconds_behind_master is greater than slavethreshold, the read-write separation filter filters out this slave machine to prevent the reading of old data long before, and when the primary node goes down, the switching logic checks the Seconds_behind on slave _master is 0, 0 is the master-slave synchronization, you can safely switch, otherwise it will not switch.

3, Configuration Server.xml

Server.xml is primarily used to set system variables, manage users, set user permissions, and more.

[Email protected] conf]$ VI server.xml

<?xml version= "1.0" encoding= "UTF-8"?>

<! DOCTYPE mycat:server SYSTEM "SERVER.DTD" >

<mycat:server xmlns:mycat= "http://org.opencloudb/" >

<system>

<property name= "Defaultsqlparser" >druidparserproperty>

<property name= "CharSet" >utf8mb4property>

System>

<user name= "User2" >

<property name= "Password" >roncoo.2property>

<property name= "Schemas" >rc_schema2,pay_schema2property>

User>

<user name= "User3" >

<property name= "Password" >roncoo.3property>

<property name= "Schemas" >rc_schema2,pay_schema2property>

<property name= "ReadOnly" >trueproperty>

User>

Mycat:server>

4. Open 8066 and 9066 ports in the firewall

The default data port for Mycat is 8066,mycat to receive requests for access to database clients through this port.

The management port is 9066, which is used to receive MYCAT monitoring commands, query Mycat health, reload configuration files, and so on.

[Email protected] mycat]# Vi/etc/sysconfig/iptables

Increase:

# # Mycat

-A input-m state--state new-m tcp-p TCP--dport 8066-j ACCEPT

-A input-m state--state new-m tcp-p TCP--dport 9066-j ACCEPT

To restart the firewall:

[Email protected] mycat]# service iptables restart

5, modify the log log level for debug, in order to confirm the Mycat-based MySQL database cluster read and write separated data operation status (can be changed to the information level before the official production)

[Email protected] conf]$ Vi/usr/local/mycat/conf/log4j.xml

6. Start Mycat

[Email protected] bin]$ cd/usr/local/mycat/bin/

(1) Console start, this startup mode after the console is closed, the Mycat service will also be closed, suitable for debugging use:

[Email protected] bin]$/mycat Console

(2) You can use the following background boot mode:

[[email protected] bin]$./mycat start

Starting mycat-server ...

(corresponding, restart: mycat restart, close: Mycat stop)

7. Mycat Connection Test

(1) If you have MySQL installed on your local windows, you can use the existing MySQL client remote operation Mycat

(2) If for convenience, you need to operate the Mycat on the Mycat host (to operate the mycat as a local MySQL), you can install the MySQL client on the MYCAT node host:

[[email protected] bin]$ su root

[[email protected] bin]# yum install MySQL

Using the installed MySQL client login Mycat

[Email protected] bin]$ mysql-uuser2-proncoo.2-h192.168.1.203-p8066

[Email protected] mycat]# VI/ETC/MY.CNF

Increase:

[Client]

Default-character-set=utf8

After saving and then query, garbled problem solved, as follows:

(3) Use third-party MySQL management Client Connection Mycat test (navicat support, mysql-front compatibility is not good), take navicat as an example:

8. Read/write Separation test

(1) Monitor Mycat log

[Email protected] ~]$ cd/usr/local/mycat/logs/

[Email protected] logs]$ tail-f Mycat.log

(2) Read test

$ mysql-uuser2-proncoo.2-h192.168.1.203-p8066

mysql> show databases;

mysql> use RC_SCHEMA2;

Mysql> Show tables;

Mysql> select * from Edu_user;

Execute the above query statement, at this time the corresponding Mycat log information is as follows:

Executes the SELECT * FROM Edu_user statement multiple times, mycat the printed log information to show that the read operation request is routed to the slave node (192.168.1.206).

(2) Write test

mysql> INSERT INTO Edu_user (UserName, PWD) VALUES (' Wu Shui ', ' roncoo.com ');

After executing the new INSERT statement above, the corresponding Mycat log information is as follows:

Execute the above INSERT statement multiple times, discovering that the new data is inserted from the master node (192.168.1.205), and the slave node synchronizes the data in the master node through Binlog.

In summary, the MYCAT-based read-write separation cluster configuration is successful.

Concern:

Highly available architecture chapter--mycat read and write separation based on MySQL master-slave replication