Build a stable and highly available cluster based on mysql+mycat, load balancing, master-slave replication, read-write separation operation

Database performance optimization generally adopts clustering. The investment in Oracle cluster software and hardware is expensive. Today I spent a day to build a cluster environment based on MySQL.

Main ideas

Simply put, implement MySQL master-slave replication-->use mycat to achieve load balancing.

After comparing the commonly used read-write separation methods, mycat is recommended due to its active community and stable performance.

Test environment

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

Note: Make sure that the MySQL version is 5.5 or later. The master-slave synchronization configuration method is different for previous versions.

The implementation idea of ​​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 prepares 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 a sync_test database in MySQL on A and B
  

Implementing MySQL master-slave replication

The main idea is: A's main MySQL starts logging, B's standby MySQL reads the operation log, and executes synchronously.

Generally, master-slave synchronization is used, and master-master synchronization is not recommended.

Configure A master mysql

1) Modify my.ini. You need to create a log directory and mysql-bin.log file at the relevant location of log-bin="C:/Program Files/MySQL/MySQL Server 5.5/log/mysql-bin.log".

[mysqld]
server-id=1 #host identifier, integer port=3306  
log-bin="C:/Program Files/MySQL/MySQL Server 5.5/log/mysql-bin.log" #Ensure that this file is writable read-only=0 #Host, both read and write are possible binlog-do-db=sync_test #Database needs to be backed up, multiple write multiple lines binlog-ignore-db=mysql #Databases that do not need to be backed up, multiple write multiple lines

2) Allow MYSQL remote access

#Login to mysql console
Enter %home%/bin and execute mysql -uroot -proot
#Authorization. Allow root user to remotely access A master mysql from the IP range of 192.168.110.*
mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.110.*' IDENTIFIED BY 'root' WITH GRANT OPTION;
# Take effect. This operation is very important!
mysql> FLUSH PRIVILEGES;

3) Restart the A master MySQL database

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

mysql>net stop mysql;
mysql>net start mysql;

4) Check the main MySQL log status

mysql> show master status\G;
*************************** 1. row ***************************
      File:mysql-bin.000003
    Position: 107
  Binlog_Do_DB: sync_test
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)

ERROR:
No query specified

Configure B standby mysql

1) Modify my.ini. You need to create a log directory and mysql-bin.log file at the relevant location of log-bin="C:/Program Files/MySQL/MySQL Server 5.5/log/mysql-bin.log".

[mysqld]
# add for sycn test
server-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 disconnected, the time difference (in seconds) to reconnect
replicate-do-db=sync_test #Replicate only a certain database replicate-ignore-db=mysql #Do not replicate a certain database

2) Restart the 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 standby database B and verify whether the status of the highlighted area 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: 0
Master_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: 1
1 row in set (0.00 sec)
ERROR:
No query specified

Verify the synchronization configuration results

• A main mysql: Use navicat to create the sync_table table in the sync_test library and add some data
• B backup mysql: Use navicat tool to check the sync_test library, you can see that the sync_table table and data have been synchronized
  

Realize read-write separation

The main idea is to use mycat middleware to forward SQL commands to the backend MySQL node. 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 products such as f5 and ngnix, with functions such as access routing, multi-table sharding operations, etc. Very powerful anyway.

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

Installation complete

Configure mycat

1) server.xml. Configure access users and permissions. Modify the highlighted information, where admin and user are users accessing mycat, and TESTDB is the virtual database of mycat for upper-level applications to access.

<user name="admin">
    <property name="password">admin</property>
    <property name="schemas">TESTDB</property>
    <!-- Table-level DML permission settings-->
    <!--     
    <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 easy to understand, so I simplified it and divided it into three main configurations: schema, dataNode, and dataHost.

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

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
  <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
    <!-- No configuration here, which means all tables are sharded to dn1 node-->
  </schema>
  <dataNode name="dn1" dataHost="localhost1" database="sync_test" />
  <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
       writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <!-- can have multi write hosts -->
    <writeHost host="hostM1" url="192.168.110.1:3306" user="root" password="root">
      <!-- can have multi read hosts -->
      <readHost host="hostS2" url="192.168.110.2:3306" user="root" password="root" />
    </writeHost>
  </dataHost>
</mycat:schema>

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, please modify the following parameters in the D:\dev-bin\mycat\bin\startup_nowrap.bat file. The default memory usage 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. see logs in logs/mycat.log #The following information will be displayed if the startup is successful.

Note: If the log contains information such as "192.168.110.2 not connected", please allow remote access to MySQL server B.

#Login to mysql console
Enter %home%/bin and execute mysql -uroot -proot
#Authorization. Allow root user to remotely access Bmysql from the IP range 192.168.110.*
mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.110.*' IDENTIFIED BY 'root' WITH GRANT OPTION;
# Take effect, this operation is very important!
mysql> FLUSH PRIVILEGES;

Test read-write separation

Verify synchronization

• Use navicat to connect to mycat. The operation method is the same as connecting to the physical mysql database. 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 A and node B have been synchronized

Close the synchronization of B standby MySQL and verify the 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_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: NULL
Master_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: 1
1 row in set (0.00 sec)
ERROR:
No query specified

3) Use navicat to connect to mycat. The operation method is the same as connecting to the physical mysql database. 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.
• Execute select * from test to view the test operation, and you will see that the data is not updated.

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

Finally, for the use of mycat, please refer to the authoritative guide on the official website. The dual-active dual-standby architecture will be updated later.

Summarize

The above is what I introduced to you about building a stable and highly available cluster load balancing, primary and secondary replication, read-write separation operation based on mysql+mycat. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!