Mysql+mycat build a stable high-availability cluster, load balancing, master and standby replication, read/write separation

• Main ideas
• Test environment
• Implementing MySQL Master and slave replication

• Configuring a Master MySQL
• Configure B to prepare MySQL
• Verifying synchronization configuration Results

• Verifying that synchronization is in sync
• Turn off B to synchronize MySQL, verify read-Write separation

• Implement read-Write separation

• Installing Mycat
• Configure Mycat
• Start Mycat
• Test read/write separation

• Verifying that synchronization is in sync
• Turn off B to synchronize MySQL, verify read-Write separation

Database performance optimization is generally used in cluster mode, Oracle cluster hardware and software investment is expensive, today spent a day to build a MySQL-based cluster environment.

Main ideas

Simply put, MySQL master and slave replication is implemented with Mycat for load balancing.

Comparison of common reading and writing separation methods, recommended Mycat, community active, stable performance.

Test environment

MySQL version: Server version:5.5.53, to the official website can download WINDWOS installation package.

Note: To ensure that the MySQL version is 5.5, the previous version master/slave synchronization is configured differently.

The Linux implementation idea is similar, modifies the my.cnf to be able.

• A master MySQL. 192.168.110.1:3306, user root, password root. Operating system: Win7 x64, Memory: 4g

Installation path: C:\Program files\mysql\mysql Server 5.5\bin

• b prepare MySQL. 192.168.110.2:3306, user root, password root. Operating system: Win2003 x64, Memory: 1g

Installation path: C:\Program files\mysql\mysql Server 5.5\bin

• A master, B-prepared MySQL in the creation of the Sync_test database

Implementing MySQL Master and slave replication

Main idea: A main MySQL open log, b standby MySQL read operation log, synchronous execution.

General synchronization, primary master synchronization is not recommended.

Configuring a Master MySQL

1) Modify the My.ini. You need to create the log directory and the Mysql-bin.log file in the relevant location of log-bin= "C:/Program files/mysql/mysql Server 5.5/log/mysql-bin.log".

[mysqld]server-id=1 #主机标示, integer port=3306    log-bin= "c:/program files/mysql/mysql server 5.5/log/mysql-bin.log"  #确保此文件可写read-only=0   #主机, reading and writing can be binlog-do-db=sync_test   #需要备份数据库, multiple lines binlog-ignore-db=mysql #不需要备份的数据库, More than one write multiple lines

2) Allow MySQL remote access

#登录mysql console enters%home%/bin to perform mysql-uroot-proot# authorization. Allow root user to remotely access a master mysqlmysql>grant all privileges on * * to ' root ' @ ' 192.168.110.* ' identified by ' from 192.168.110.* IP range Root ' with GRANT OPTION; #生效. The operation is important! Mysql>flush privileges;

3) Restart a primary MySQL database

Enter%home%/bin, execute mysql-uroot-prootmysql>net stop mysql;mysql>net start MySQL;

4) View the primary MySQL log status

Mysql> Show Master status\g;*************************** 1. Row ***************************            file:mysql-bin.000003        position:107    binlog_do_db:sync_testbinlog_ IGNORE_DB:MYSQL1 row in Set (0.00 sec) Error:no query specified

Configure B to prepare MySQL

1) Modify the My.ini. You need to create the log directory and the Mysql-bin.log file in the relevant location of log-bin= "C:/Program files/mysql/mysql Server 5.5/log/mysql-bin.log".

[mysqld]# Add for SYCN testserver-id=2                       #从机标识log-bin= "C:/Program files/mysql/mysql Server 5.5/log/mysql-bin.log" # Make sure this file is writable #master-host= "192.168.110.1" #主机Ip #master-user=root                  #数据库访问用户名 #master-pass=root                     #数据库访问密码 # master-port=3306          #主机端口 #master-connect-retry=60     #如果从服务器发现主服务器断掉, reconnection time difference (seconds) replicate-do-db=sync_test     #只复制某个库replicate-ignore-db=mysql     #不复制某个库

2) Restart B to prepare MySQL database

Enter%home%/bin, execute mysql-uroot-prootmysql>net stop mysql;mysql>net start MySQL;

3) Configure B to prepare the data source of the database, verify the status of the highlight is normal.

Mysql>change Master to master_host= ' 192.168.110.1 ', master_port= ' 3306 ', master_user= ' root ', master_password= ' root '; mysql>slave start;mysql>show slave status\g;*************************** 1. Row *************************** slave_io_state:waiting for master to send even Master_host              : 192.168.110.1 master_user:root master_port:3306 connect_retry:60 master_log_file:mysql-bin.000003 read_master_log_pos:107 relay_log_file:wjt-1c698d8a03 2-relay-bin.00001 relay_log_pos:253 relay_master_log_file:mysql-bin.000003 Slave_io_ru           Nning:yes Slave_sql_running:yes replicate_do_db:sync_test Replicate_ignore_db:mysql                   Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:              last_errno:0 Last_error:   skip_counter:0 exec_master_log_pos:107 relay_log_space:565 Until_condition:none until_log_file:until_log_pos:0 Master_ssl_allowed:no Master_ssl_ca_fil         E:master_ssl_ca_path:master_ssl_cert:master_ssl_cipher:master_ssl_key: Seconds_behind_master:0master_ssl_verify_server_cert:no last_io_errno:0 last_io_e rror:last_sql_errno:0 Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Serve R_id:11 row in Set (0.00 sec) Error:no query specified

Verifying synchronization configuration Results

• A master MySQL: Use the Navicat tool to create a sync_table table in the Sync_test library and add some data
• b standby MySQL: Use the Navicat tool to view the Sync_test library and see that the Sync_table table and data have been synchronized

Implement read-Write separation

Main idea: Using MYCAT middleware, forward SQL instructions to the backend MySQL node. Mycat is not responsible for database synchronization.

Installing Mycat

What is Mycat? Can be considered as a database access middleware, but more like F5, Ngnix and other products, with access to routing, multi-table sub-table shard operation and other functions. It's very powerful anyway.

• Download: http://www.mycat.io/, this article uses: 1.6-release
• Unzip the Mycat-server-1.6-release-20161012170031-win.tar to the D:\dev-bin\mycat directory
• Make sure the Java environment is above jdk1.7, otherwise mycat will not support

Installation Complete

Configure Mycat

1) server.xml. Configure access users and permissions. Modify the highlight information, where admin, user for access to Mycat users, TestDB for the Mycat virtual database for the upper-level application access.

<user name= "admin" >        <property name= "password" >admin</property>        <property name= " Schemas >TESTDB</property>        <!--table-level DML permissions set-        <!--                 <privileges check= "false" >            <schema name= "TESTDB" dml= "0110" >                <table name= "tb01" dml= "0000" ></table>                <table Name= "TB02" dml= "1111" ></table>            </schema>        </privileges>                 -    </user >    <user name= "user" >        <property name= "password" >user</property>        <property Name= "schemas" >TESTDB</property>        <property name= "readOnly" >true</property>    </ User>

2) Schema.xml. This part is not very good understanding, streamlined, the main sub-schema, DataNode, datahost three major configurations.

The <scheme> node defines the MYCAT virtual database as TestDB, balance= "1": The write operation is routed to a machine, and the read operation is routed to B.

<?xml version= "1.0"? ><! DOCTYPE mycat:schema SYSTEM "schema.dtd" ><mycat:schema xmlns:mycat= "http://io.mycat/" >    <schema Name = "TESTDB" checksqlschema= "false" sqlmaxlimit= "datanode=" DN1 ">        <!--Not configured here, representing all table shards to dn1 node    -- </schema>    <datanode name= "dn1" datahost= "Localhost1" database= "Sync_test"/>    <datahost name= "Localhost1" maxcon= "mincon=" balance= "1"              writetype= "0" dbtype= "MySQL" dbdriver= "native" switchtype= "1"  slavethreshold= >        
Start Mycat
1) Start Mycat
D:\dev-bin\mycat\bin>startup_nowrap.bat
Background information is as follows:
 d:\dev-bin\mycat\bin>startup_nowrap.batd:\dev-bin\mycat\bin>rem Check Java_home & javaD:\dev-bin\ Mycat\bin>set "Java_cmd=c:\program Files (x86) \java\jdk1.7.0_13/bin/java" D:\dev-bin\mycat\bin>if "C:\Program Files (x86) \java\jdk1.7.0_13 "= =" "Goto nojavahomed:\dev-bin\mycat\bin>if Exist" C:\Program files (x86) \java\ Jdk1.7.0_13\bin\java.exe "goto Mainentryd:\dev-bin\mycat\bin>rem set Home_dird:\dev-bin\mycat\bin>set" CURR_ Dir=d:\dev-bin\mycat\bin "D:\DEV-BIN\MYCAT\BIN>CD. D:\dev-bin\mycat>set "Mycat_home=d:\dev-bin\mycat" D:\DEV-BIN\MYCAT>CD d:\dev-bin\mycat\bin# if startup fails, modify D:\ The following parameters in the Dev-bin\mycat\bin\startup_nowrap.bat file. The default memory consumption is 2gd:\dev-bin\mycat\bin> "C:\Program Files (x86) \java\jdk1.7.0_13/bin/java"-SERVER-XMS512M-XMX512M-XX: Maxpermsize=64m-xx:+aggressiveopts-xx:maxdirectmemorysize=768m-dmycat_home=d:p ". \conf, .... \lib\* "Io.mycat.MycatStartupMyCAT Server startup successfully. See logs in Logs/mycat.log #启动成功将看到如下信息. 
Note: For information such as 192.168.110.2 not connected appears in the log, allow MySQL remote access to B.
#登录mysql console enters%home%/bin to perform mysql-uroot-proot# authorization. Allow root user to remotely access bmysqlmysql>grant all privileges on * * to ' root ' @ ' 192.168.110.* ' identified by ' from 192.168.110.* IP range Root ' with GRANT OPTION; #生效, the operation is important! Mysql>flush privileges;
Test read-Write separation verify synchronization
 
 

  
  
 
   
Use Navicat connection Mycat, operation mode and connection physical MySQL library consistent, user admin, password admin, Port 8066
 
   
In the TestDB virtual library, create a new table test2 and add some data
 
   
View A-node, b-node data synchronized
 
  
 
 
Turn off B to synchronize MySQL, verify read-Write separation
mysql> slave stop; Query OK, 0 rows Affected (0.00 sec) mysql> Show slave status\g;*************************** 1.                  Row *************************** slave_io_state:master_host:192.168.110.1 Master_user:root master_port:3306 connect_retry:60 Master_log_file:mysql                -bin.000003 read_master_log_pos:478 relay_log_file:wjt-1c698d8a032-relay-bin.00001 relay_log_pos:624 relay_master_log_file:mysql-bin.000003 Slave_io_running:no Slave_sql_r       Unning:no replicate_do_db:sync_test replicate_ignore_db:mysql replicate_do_table:                   replicate_ignore_table:replicate_wild_do_table:replicate_wild_ignore_table:last_errno:0 last_error:skip_counter:0 exec_master_log_pos:478 Relay_log_space:9 Approx. until_conditIon:none until_log_file:until_log_pos:0 Master_ssl_allowed:no Master_ SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Maste                R_SSL_Key:Seconds_Behind_Master:NULLMaster_SSL_Verify_Server_Cert:No last_io_errno:0             last_io_error:last_sql_errno:0 Last_sql_error:replicate_ignore_server_ids: Master_server_id:11 row in Set (0.00 sec) Error:no query specified
3) Use Navicat connection Mycat, operation mode and connection physical MySQL library consistent, user admin, password admin, Port 8066

 
 

  
  
 
   
After the connection is successful, you will see the TestDB database and the test data table
 
   
Add some data to the test table to save
 
   
Perform a SELECT * from test to view the test operation and see that the data is not updated
 
  
 
 
Cause: Mycat Query Sq is routed to B, so the result set read is inconsistent.
Finally, Mycat uses the authoritative guide that can be found on the official website. Dual-Master dual-standby architecture to be updated later.
Mysql+mycat build a stable high-availability cluster, load balancing, master and standby replication, read/write separation