1. Introduction to Mycat
- A completely open source large database cluster for enterprise application development
- Support transaction, ACID and enhanced database that can replace MySQL
- An enterprise database that can be regarded as a MySQL Cluster is used to replace the expensive Oracle cluster
- A new SQL Server integrating memory cache technology, NoSQL technology and HDFS big data
- A new generation of enterprise database products combining traditional database and new distributed data warehouse
- A novel database middleware product
Official website: http://www.mycat.org.cn/
2. Mycat advantages
Based on Alibaba's open source Cobar products, the stability, reliability, excellent architecture and performance of Cobar and many mature use cases make MYCAT have a good starting point from the beginning. Standing on the shoulders of giants, we can see further. Excellent open source projects and innovative ideas in the industry have been widely integrated into MYCAT's gene, making MYCAT ahead of other similar open source projects in many aspects, and even surpassing some commercial products.
there is a strong technical team behind Mycat. Its participants are software engineers, architects and DBA s for more than 5 years. The excellent technical team ensures the product quality of Mycat.
Mycat does not rely on any commercial company, so unlike some open source projects, it closes some important features in its commercial products, making the open source project a decoration.
3. Preliminary preparation
3.1. Install Mysql master-slave replication (refer to mysql8 master-slave replication)
3.2 installation jdk
# yum source installation $ yum search jdk $ yum -y install java-1.8.0-openjdk.x86_64 # View jsk version $ java -version
3.3. Download Mycat1.6.7.6 installation package
wget http://dl.mycat.org.cn/1.6.7.6/20211016233725/Mycat-server-1.6.7.6-release-20211016233725-linux.tar.gz
4. Install mycat
# decompression $ tar -zxvf Mycat-server-1.6.7.6-release-20211016233725-linux.tar.gz # Rename (negligible) $ mv mycat/ mycat-1.6.7.6 # Move to / usr/local directory $ mv mycat-1.6.7.6/ /usr/local/
5. Configuration (one master and one slave)
# Enter the configuration file directory and view the file
$ cd /usr/local/mycat-1.6.7.6/conf
# Modify server.xml
$ vi server.xml
# Modify the administrator user and read-only user information and password at the bottom, and configure schemas
<user name="root" defaultAccount="true">
<property name="password">Xinnet@2021</property>
<property name="schemas">ZABBIXDB</property>
<property name="defaultSchema">ZABBIXDB</property>
</user>
<user name="zabbixuser" defaultAccount="true">
<property name="password">zabbixuser@2021</property>
<property name="schemas">ZABBIXDB</property>
<property name="defaultSchema">ZABBIXDB</property>
</user>
<user name="user">
<property name="password">user</property>
<property name="schemas">ZABBIXDB</property>
<property name="readOnly">true</property>
<property name="defaultSchema">ZABBIXDB</property>
</user>
#One more zabbixuser user was created
# Modify schema.xml
vi schema.xml
# Replace with the following
# Where 10.12.69.51 is the IP of the write host (master database) and 10.12.69.52 is the IP of the read host (slave database).
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="ZABBIXDB" checkSQLschema="false" sqlMaxLimit="1000" dataNode="dn1">
</schema>
<dataNode name="dn1" dataHost="host1" database="zabbix" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="jdbc:mysql://10.12.69.51:3306" user="root" password="Xinnet@2021">
<readHost host="hostS1" url="jdbc:mysql://10.12.69.52:3306" user="root" password="Xinnet@2021" />
</writeHost>
</dataHost>
</mycat:schema>
There are four types of balance attribute values in the dataHost node:
- balance
balance="0"
do not enable the read-write separation mechanism, and 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. In short, when there are two masters and two slaves
Mode (M1 - > S1, M2 - > S2, and M1 and M2 are both primary and standby). Under normal circumstances, M2, S1 and S2 all participate in the load balancing of the select statement;
balance="2"
all read operations are randomly distributed on writeHost and readhost;
balance="3"
all read requests are randomly distributed to readhost for execution, and writerHost does not bear the reading pressure.
- writeType
writeType = "0", all write operations are sent to the configured first writeHost, and the first one hangs and switches to the normal second writeHost; writeType = "1", all write operations are sent to the configured writeHost randomly, which is not recommended after version 1.5. - writeHost
after Mycat restarts, the one after switching shall prevail, that is, the change of write host may be sent due to abnormal write host. The switching is recorded in the configuration file: dnindex.properties. - switchType
switchType = "1", the default value is automatic switching; switchType="-1", it means no automatic switching; switchType = "2", it determines whether to switch based on the status of Mysql master-slave synchronization.
6. Start mycat
# Restart $ cd /usr/local/mycat-1.6.7.6/bin/ $ ./mycat restart # View startup status $ ./mycat status # Be careful to turn off the firewall or release port 8066 #Check whether the log is started successfully tail -f ../logs/switch.log tail -f ../logs/mycat.log -n 400
7. Test read write separation
7.1. Open the master-slave database log (both machines operate)
#Check whether the main database logging switch and logging path are on mysql> show variables like 'general_log'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | general_log | OFF | +---------------+-------+ 1 row in set (0.00 sec) mysql> show variables like 'general_log_file'; +------------------+------------------------------------------------------+ | Variable_name | Value | +------------------+------------------------------------------------------+ | general_log_file | /var/lib/mysql/bj-smzy-devops-zabbix-10-12-69-51.log | +------------------+------------------------------------------------------+ mysql> set global general_log=ON; ## Turn on the log switch Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'general_log'; #View again +---------------+-------+ | Variable_name | Value | +---------------+-------+ | general_log | ON | +---------------+-------+
7.2 monitoring log
Open multiple terminal monitoring logs
Master database:
$ tail -f /var/lib/mysql/bj-smzy-devops-zabbix-10-12-69-51.log
From database:
$ tail -f /var/lib/mysql/bj-smzy-devops-zabbix-10-12-69-52.log
7.3. Connect the database through mycat (10.12.69.53 bit mycat machine)
$ mysql -h 10.12.69.53 -uroot -p -P8066mysql> show databases;+----------+| DATABASE |+----------+| ZABBIXDB |+----------+# Create test table mysql> create table t_data2_test(`id`int not null primary key auto_increment, `value` varchar(255));# Insert a record mysql> insert into t_data2_test(`value`) values('C3Stones');# Query MySQL > select * from t_data2_test;
7.4 log verification
Whether the read query statement is executed only from the library
Whether the write library statement is executed only from the main library
It can be seen that the read is only executed from the read database 52 and the write database is only executed from the master database 51 (because master-slave replication is configured, the time of writing the database log depends on the time. The master database writes first and the slave database then performs the write operation through binlog)
8. mycat configuring multi-user login
Scenario: the application uses the zbbixuser user to connect to mycat
8.1 execution of master-slave database
#Create zabbixuser user in master-slave libraries and access authorization MySQL > create user 'zabbixuser' @ '%' identified with mysql_native_password by ' zabbixuser@2021 ';Query OK, 0 rows affected (0.33 sec)mysql> grant all privileges on *.* to 'zabbixuser'@'%' with grant option; Query OK, 0 rows affected (0.11 sec) mysql> flush privileges;Query OK, 0 rows affected (0.02 sec)
8.2. mycat configuration server.xml
<user name="root" defaultAccount="true"> <property name="password">Xinnet@2021</property> <property name="schemas">ZABBIXDB</property> <property name="defaultSchema">ZABBIXDB</property> </user> <user name="zabbixuser" defaultAccount="true"> <property name="password">zabbixuser@2021</property> <property name="schemas">ZABBIXDB</property> <property name="defaultSchema">ZABBIXDB</property> </user> <user name="user"> <property name="password">user</property> <property name="schemas">ZABBIXDB</property> <property name="readOnly">true</property> <property name="defaultSchema">ZABBIXDB</property> </user>
New user zabbixuser, others remain unchanged, restart macat
$ cd /usr/local/mycat-1.6.7.6/bin/$ ./mycat restart# Test using zabbixuser user to connect to the database $MySQL - H 10.12.69.53 - uzabixuser - P - p8066enter password: welcome to the MySQL monitor. Commands end with; or \ g.your MySQL connection ID is 7server version: 5.6.29-mycat-1.6.7.6-release-20211018151034 MYCAT server (openclouddb) Copyright (c) 2000, 2021, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>
success!