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

This article mainly introduces how to build a stable and highly available cluster load balancer based on mysql+mycat. The read-write separation operation of active and standby replication is very detailed and has a certain reference value. Interested friends must read it!

Main ideas

To put it simply, implement mysql active and standby replication-> use mycat to achieve load balancing.

The commonly used read-write separation methods are compared and mycat is recommended. The community is active and the performance is stable.

Test environment

MYSQL version: Server version: 5.5.53. You can download the WINDWOS installation package from the official website.

Note: after ensuring that the mysql version is 5.5, the master / slave synchronization configuration of the previous version is different.

The idea of implementing linux is similar, just modify my.cnf.

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

Create sync_test database in mysql of A master and B slave

Implement mysql active and standby replication

Main idea: a master mysql opens log, B prepares mysql to read operation log, and executes synchronously.

Generally, it is master / slave synchronization, and master / master synchronization is not recommended.

Configure A Primary mysql

1) modify my.ini. You need to create the log directory in the relevant location of the log-bin= "C:/Program Files/MySQL/MySQL Server 5.5/log/mysql-bin.log", as well as the mysql-bin.log file.

[mysqld] server-id=1 # Host Mark, integer port=3306 log-bin= "C:/Program Files/MySQL/MySQL Server 5.5/log/mysql-bin.log" # ensure that this file can be written to read-only=0 # host, read and write can be binlog-do-db=sync_test # need to back up database, multiple write multiple lines binlog-ignore-db=mysql # do not need backup database, multiple write multiple lines

2) allow MYSQL remote access

# Log in to mysql console to enter% home%/bin and execute mysql-uroot-proot# authorization. Allow root users to remotely access A master mysqlmysql > GRANT ALL PRIVILEGES ON *. * TO 'root'@'192.168.110.*' IDENTIFIED BY' root' WITH GRANT OPTION;# from the IP range of 192.168.110.* effective. This operation is very important! Mysql > FLUSH PRIVILEGES

3) restart A main mysql database

Enter% home%/bin and execute mysql-uroot-proot

Mysql > net stop mysql;mysql > net start mysql

4) View the status of the master mysql log

Mysql > show master status\ Gbomber * 1. Row * * File: mysql-bin.000003 Position: 107Binlog_Do_DB: sync_testBinlog_Ignore_DB: mysql1 row in set (0.00 sec) ERROR:No query specified

Configure B standby mysql

1) modify my.ini. You need to create the log directory in the relevant location of the log-bin= "C:/Program Files/MySQL/MySQL Server 5.5/log/mysql-bin.log", as well as the mysql-bin.log file.

[mysqld] # add for sycn testserver-id=2 # Slave ID 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" # Host Ip#master-user=root # Database access username # master-pass=root # Database access password # master-port=3306 # Host Port # Master-connect-retry=60 # if the slave server finds that the master server is down Time difference of reconnection (seconds) replicate-do-db=sync_test # only copy a library replicate-ignore-db=mysql # do not copy a library

2) restart B standby mysql database

Enter% home%/bin and execute mysql-uroot-proot

Mysql > net stop mysql;mysql > net start mysql

3) configure the data source of B standby database to verify whether 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-1c698d8a032-relay-bin.00001 Relay_Log_Pos: 253 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: 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_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: 11 row in set (0.00 sec) ERROR:No query specified

Verify synchronization configuration results

A Master mysql: use the navicat tool to create the 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 you can see that the sync_table table and data have been synchronized

Achieve the separation of reading and writing

Main idea: use mycat middleware to forward sql instructions to back-end mysql nodes. Mycat is not responsible for database synchronization.

Install mycat

What is mycat? It can be considered as a database access middleware, but it is more like f5, ngnix and other products, with access routing, multi-table slicing operation and other functions. Anyway, it's powerful.

Download: http://www.mycat.io/, this article uses: 1.6-RELEASE

Extract 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 completed

Configure mycat

1) server.xml. Configure access users and permissions. Modify the highlight information, in which admin and user are the users who access mycat, and TESTDB is the virtual database of mycat, which is accessed by the upper application.

Admin TESTDB user TESTDB true

2) schema.xml. This part is not easy to understand, simplified, mainly divided into schema, dataNode, dataHost three main configuration.

The node defines the virtual database of mycat as TESTDB, balance= "1": write operations are routed to machine A, and read operations are routed to B.

Select user ()

Start mycat

1) start mycat

D:\ dev-bin\ mycat\ bin > startup_nowrap.bat

The background information is as follows:

D:\ dev-bin\ mycat\ bin > startup_nowrap.bat

D:\ dev-bin\ mycat\ bin > REM check JAVA_HOME & java

D:\ 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 noJavaHome "

D:\ dev-bin\ mycat\ bin > if exist "C:\ Program Files (x86)\ Java\ jdk1.7.0_13\ bin\ java.exe" goto mainEntry

D:\ dev-bin\ mycat\ bin > REM set HOME_DIR

D:\ 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 the following parameters in the D:\ dev-bin\ mycat\ bin\ startup_nowrap.bat file. The default memory footprint is 2G.

D:\ 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.MycatStartup

MyCAT Server startup successfully. If see logs in logs/mycat.log # starts successfully, you will see the following message.

Note: if information such as 192.168.110.2 not connected appears in the log, please allow B standby mysql remote access.

# Log in to mysql console to enter% home%/bin and execute mysql-uroot-proot# authorization. Allow root users to remotely access Bmysqlmysql > GRANT ALL PRIVILEGES ON *. * TO 'root'@'192.168.110.*' IDENTIFIED BY' root' WITH GRANT OPTION;# from the IP range of 192.168.110.*. This operation is very important! Mysql > FLUSH PRIVILEGES

Test read-write separation

Verify synchronization

Use navicat to connect to mycat, operating in the same way as connecting to the physical mysql library, user admin, password admin, port 8066

In the TESTDB virtual library, create a new table test2 and add some data

Check that the data of node An and node B are synchronized

Turn off the synchronization of B standby mysql and verify the separation of read and write

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 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: 936 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: 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 to connect to mycat, the operation mode is the same as connecting to the physical mysql library, user admin, password admin, port 8066

After the connection is successful, you will see the TESTDB database and test data table

Add some data to the test table and save it

If you perform select * from test to view test, you will see that the data has not been updated

Reason: mycat routes the query sq to B, so the result set read is inconsistent.

Finally, mycat can be used in the official website of the authoritative guide to learn. The dual-master and dual-standby architecture will be updated later.

The above is all the contents of the article "how to build a stable and highly available cluster load balance based on mysql+mycat to separate read and write operations of active and standby replication". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.