MySQL and Mycat building for cluster and 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. The main idea is simply to implement MySQL master and slave replication and load balancing with Mycat. 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 standby MySQL create sync_test database implementation MySQL Master The main idea of preparation and replication: a main MySQL open log, b standby MySQL read operation log, synchronous execution. General synchronization, primary master synchronization is not recommended. Configure a master MYSQL1) to 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". Copy code [mysqld]server-id=1 #主机标示, Integer port=3306 log-bin= "c:/program files/mysql/mysql server 5.5/log/mysql-bin.log" # Ensure that this file can be written read-only=0 #主机, read and write can be binlog-do-db=sync_test #需要备份数据库, more than one binlog-ignore-db=mysql #不需要备份的数据库 to write multiple lines, Multiple write multi-line copy code 2) allow MySQL remote access copy code # Log in to MySQL console to enter%home%/bin, execute Mysql-uroot-proot# authorized. 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; Copy Code 3) Restart a primary MySQL database into%home%/bin, execute mysql-uroot-prootmysql>net stop mysql;mysql>net Start mysql;4) View the primary MySQL log status copy code mysql> show Master status\g;*************************** 1. Row *************************** file:mysql-bin.000003 position:107 Binlog_do_db:sync_testbinlog_ig NORE_DB:MYSQL1 row in Set (0.00 sec) error:no Query specified copy code configuration b prepare mysql1) modify 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". Copy code [mysqld]# add for SYCN testserver-id=2 #从机标识log-bin= "C:/Program files/mysql/mysql Server 5.5/log                     /mysql-bin.log "#确保此文件可写 #master-host=" 192.168.110.1 "#主机Ip #master-user=root #数据库访问用户名 #master-pass=root #数据库访问密码 #master-port=3306 #主机端口 #master-connect-retry=60     #如果从服务器发现主服务器断掉, the time difference of reconnection (seconds) replicate-do-db=sync_test #只复制某个库replicate-ignore-db=mysql #不复制某个库复制代码2) Restart B to prepare MySQL number According to the library into%home%/bin, perform mysql-uroot-prootmysql>net stop mysql;mysql>net start mysql;3) Configure the data source of the database, verify the status of the highlight is normal. Copy Code 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_Wil d_do_table:replicate_wild_ignore_table:last_errno:0 Last_error:sk               ip_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_file:        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_error:  last_sql_errno:0 Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id:    One row in Set (0.00 sec) error:no Query specified copy code verify synchronization configuration result A primary MySQL: Use the Navicat tool, create a sync_test table in the Sync_table library, and add some data b prepare MySQL: Use the Navicat tool to view SYNc_test Library, you can see the sync_table table and data has been synchronized implementation of read and write separation of the main idea: the use of MYCAT middleware, forwarding SQL instructions to the backend MySQL node. Mycat is not responsible for database synchronization. What is the installation mycatmycat? 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 decompression Mycat-server-1.6-release-20161012170031-win.tar, to D:\dev-bin\ The Mycat directory ensures that the Java environment is above jdk1.7, otherwise mycat will not support the installation complete configuration MYCAT1) 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. Copy Code<Username= "Admin">        < Propertyname= "Password">Admin</ Property>        < Propertyname= "Schemas">TESTDB</ Property>        <!--table-level DML permission settings -        <!--<privileges check= "false" > <schema name= "TESTDB" dml= "0110" > &            Lt;table name= "tb01" dml= "0000" ></table> <table name= "tb02" dml= "1111" ></table> </schema> </privileges> -    </User>    <Username= "User">        < Propertyname= "Password">User</ Property>        < Propertyname= "Schemas">TESTDB</ Property>        < Propertyname= "ReadOnly">True</ Property>    </User>Copy Code 2) schema.xml. This part is not very good understanding, streamlined, the main sub-schema, DataNode, datahost three major configurations. <Scheme>The 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. Copy Code<?XML version= "1.0"?><!DOCTYPE mycat:schema SYSTEM "Schema.dtd"><Mycat:schemaXmlns:mycat= "http://io.mycat/">    <Schemaname= "TESTDB"Checksqlschema= "false"Sqlmaxlimit= "+"DataNode= "DN1">        <!--Not configured here, representing all of the table shards to the DN1 node -    </Schema>    <DataNodename= "DN1"Datahost= "Localhost1"Database= "Sync_test" />    <Datahostname= "Localhost1"Maxcon= "+"Mincon= "Ten"Balance= "1"Writetype= "0"DbType= "MySQL"Dbdriver= "Native"Switchtype= "1"Slavethreshold= "+">        <Heartbeat>Select User ()</Heartbeat>        <!--can have multi write hosts -        <WritehostHost= "HostM1"URL= "192.168.110.1:3306"User= "root"Password= "root">            <!--can have multi read hosts -            <ReadhostHost= "HostS2"URL= "192.168.110.2:3306"User= "root"Password= "root" />        </Writehost>    </Datahost></Mycat:schema>Copy Code start MYCAT1) Start Mycatd:\dev-bin\mycat\bin>startup_nowrap.bat background information as follows: Copy code D:\DEV-BIN\MYCAT\BIN&GT;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&GT;CD. D:\dev-bin\mycat>set "Mycat_home=d:\dev-bin\mycat" D:\DEV-BIN\MYCAT&GT;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 #启动成功将看到如下信息. Copy Code Note: For information such as 192.168.110.2 not connected appears in the log, allow B to have MySQL remote access. Copy code # Log in to MySQL console to enter%home%/bin, 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; Copy code test read-write separation verify synchronization using Navicat connection Mycat, operation mode and connection physical MySQL library consistent, user admin, password Admin, Port 8066 in TestDB Virtual Library, Create a new table test2, add some data to view a node, b node data is synchronized off B standby MySQL synchronization, verify read-Write separation copy code 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_running:no R      Eplicate_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:936 Until_con Dition:none until_log_file:until_log_pos:0 Master_ssl_allowed:no Mast Er_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Ma                Ster_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 copy code 3) Use Navicat connection Mycat, operation mode and connection physical MySQL library consistent, user admin, password admin, Port 8066 connected successfully, will look To the TestDB database and the test data table, add some data to the test table, save the Execute select * from test to view the test operation, you will see that the data is not updated because: Mycat The query sq is routed to B, so the result set read is inconsistent.

MySQL and Mycat building for cluster and read-write separation