Linux under MySQL based on the mycat from the copy and read-write separation of the basic article

Linux MySQL-based mycat for master-slave replication and read-write separation
1. Infrastructure

Two virtual machines: 172.20.79.232 (Master) 172.20.79.233 (from)
　　1.1 Software facilities

mysql5.6.39, Mycat1.6-release jdk1.7 and above
2. Implementing step One (Mycat for read-write separation)

　1. First install MySQL on two servers

1. Download the MySQL repo source

$ wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm

2. Install the MYSQL-COMMUNITY-RELEASE-EL7-5.NOARCH.RPM package

$ sudo rpm-ivh mysql-community-release-el7-5. noarch.rpm

After installing this package, you will get two MySQL yum repo sources:

/etc/yum.repos.d/mysql-community.repo/etc/yum.repos.d/mysql-community-source.repo

3. Install MySQL

$ sudo yum install Mysql-server

Follow the prompts to install, but there is no password after installation, you need to reset the password
4. Reset MySQL Password

$ mysql-u Root

This error may be reported at login: Error 2002 (HY000): Can ' t connect to local MySQL server through socket '/var/lib/mysql/mysql.sock ' (2) because/var /lib/mysql access rights issues. The following command changes the owner of the/var/lib/mysql to the current user:

　　　　　　

$ sudo chown-r root:root/var/lib/mysql

5. Turn on MySQL remote connection
　　　　

Grant all on * * to [e-mail protected] '% ' identified by ' admin123 ' with grant Option;flush privileges;

6. Restart the MySQL service
　　　　　

$ Service Mysqld Restart

7. Next Login to reset Password:
　　

$ mysql-u root  //Direct enter into MySQL console mysql > use MySQL;　　　　　　MySQL > Update user set Password=password (' 123456 ') where user= ' root '; MySQL > exit;

　　2. Install Mycat on the primary server
1. Download mycat1.6
　　

Https://github.com/MyCATApache/Mycat-download

　　

2. Upload to main server decompression installation　　　　

Tar zxvf  mycat-server-1.6-release-20160405120037-linux.tar.gz-c/usr/local cd/usr/local/mycat groupadd Myc At Useradd-g mycat mycat passwd mycat chown-r mycat.mycat/usr/local/mycat Export  

2.1 Modifications (new)　　　

Vi/etc/hosts 172.20.79.232 root232 172.20.79.233 root233

2.2 Modifying the Mycat boot JDK path
　　　　　

Vi/usr/local/mycat/conf/wrapper.conf modifying Wrapper.java.command=%java_home%/bin/java

2.3 Configure the Mycat file Schema.xml
　　　　　　　　

Vi/usr/local/mycat/conf/schema.xml <?xml version= "1.0"?> <! DOCTYPE mycat:schema SYSTEM "Schema.dtd" > <mycat:schema xmlns:mycat= "http://io.mycat/" > <!-- Here the Mycatdb and Server.xml inside the name consistent datanode a few nodes on the configuration of several nodes, separated by commas--<schema name= "Mycatdb" checksqlschema= "false" sqlmaxlimit= "datanode=" dn1 "/> <!--database has several databases to configure a few lines--<datanode name=" DN1 "datahost=" Localhost1 "database=" db01 "/> <datahost name=" Localhost1 "maxcon=" + "mincon=" balance= "3" writetype= "0" db Type= "MySQL" dbdriver= "native" switchtype= "1" slavethreshold= "> <!--balance=" 0 ", does not open the read and write separation mechanism,　　All read operations are sent to the currently available writehost. Balance= "1", all readhost and stand by writehost participate in the load balancing of the SELECT statement, simply speaking, when the dual master dual slave mode (M1-&GT;S1,M2-&GT;S2, and M1 and M2 are mainly prepared), under normal circumstances, M2　　, the S1,S2 participates in load balancing of the SELECT statement.　　Balance= "2", all read operations are randomly distributed on the Writehost, Readhost. Balance= "3", all read requests are randomly distributed to wiriterhost corresponding readhost execution, Writerhost does not bear reading pressure Writetype indicates write mode writetype= "0", All operations are sent to the configured first writehost writetype= "1", which is randomly sent to all configured Writehost WritetypE= "2", do not perform write operation Switchtype refers to the mode of switching, the current value also has 4 kinds: switchtype= '-1 ' means do not automatically switch switchtype= ' 1 ' default value, indicating automatic switching switchtype= ' 2 ' based on MySQL master-slave The state of the synchronization determines whether to toggle, heartbeat statement for show slave status switchtype= ' 3 ' based on MySQL galary cluster switching mechanism (for cluster) (1.4.1), heartbeat statement for show status like ' Wsrep　　%‘。 --
　　


2.4 Configure the Mycat file Server.xml
Vi/usr/local/mycat/conf/schema.xml<?xml version= "1.0" encoding= "UTF-8"?> <! DOCTYPE mycat:server SYSTEM "Server.dtd" > <mycat:server xmlns:mycat= "http://io.mycat/" > <system> < Property Name= "Usesqlstat" >0</property> <!--1 To turn on real-time statistics, 0 off---<property name= "Useglobletablecheck ">0</property> <!--1 for turn on full overtime consistency detection, 0 for off-<property name=" Sequncehandlertype ">2</property > <!--default to type 0:directbytebufferpool | Type 1 bytebufferarena--> <property name= "Processorbufferpooltype" >0</property> <property name= " Handledistributedtransactions ">0</property> <!--off heap for Merge/order/group/limit 1 on 0 off--<p Roperty name= "Useoffheapformerge" >1</property> <!--units M-and <property name= "Memorypagesize" >1m </property> <!--units K--<property name= "spillsfilebuffersize" >1k</property> <property na Me= "Usestreamoutput" >0</propertY> <!--units M--<property name= "Systemreservememorysize" >384m</property> <!--use zookeeper Toggle--<property name= "Usezkswitch" >true</property> </system> <user name= "root" > <prop Erty name= "password" >admin123</property> <property name= "schemas" >mycatdb</property> </ user> <user name= "user" > <property name= "password" >user</property> <property name= "schemas" & gt;mycatdb</property> <property name= "readOnly" >true</property> </user> </mycat:server >
　　
3. Implement step Two (MySQL implements master-slave replication)
Linux under MySQL based on the mycat from the copy and read-write separation of the basic article