catalogue
1.master server: 192.168.68.200
five mycat server: 192.168.68.40
6.master server: 192.168.68.200
1, Mycat application scenario
Mycat has a wide range of application scenarios. The following are several typical application scenarios, which are simply read-write separation. At this time, the configuration is the simplest. It supports read-write separation. The master-slave switches to separate tables and libraries. For more than 10 million tables, it supports up to 100 billion single table fragmentation and multi tenant applications. Each application has a library, but the application is only connected to Mycat, so it does not transform the program itself, Realize the multi tenant report system. With the help of Mycat's table splitting ability, handle the statistics of large-scale reports instead of Hbase, and analyze big data as a simple and effective scheme for real-time query of massive data. For example, 10 billion frequently queried records need to query the results within 3 seconds. In addition to the query based on primary key, there may also be range query or other attribute query, At this point, Mycat may be the simplest and most effective choice.
2, Mycat summary
- 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
3, Specific operation
master server: 192.168.68.200
slave server: 192.168.68.30
mycat server: 192.168.68.40 ## no service can occupy port 3306, such as mysql
Client: 192.168.68.195
1.master server: 192.168.68.200
[root@localhost ~]# systemctl stop firewalld.service [root@localhost ~]# setenforce 0 [root@localhost ~]# vim /etc/my.cnf server-id = 1 log-bin=master-bin binlog_format=MIXED log-slave-updates=true [root@localhost ~]# systemctl restart mysqld.service [root@localhost ~]# mysql -u root -p123123 mysql> grant replication slave on *.* to 'myslave'@'192.168.68.%' identified by '123456'; mysql> flush privileges; mysql> show master status; +-------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+-------------------+ | master-bin.000001 | 603 | | | | +-------------------+----------+--------------+------------------+-------------------+
[root@localhost ~]# vim /etc/my.cnf
[ root@localhost ~]# systemctl restart mysqld.service ## Restart database service
2.slave server: 192.168.68.30
[root@localhost ~]# systemctl stop firewalld.service
[root@localhost ~]# setenforce 0
[root@localhost ~]# vim /etc/my.cnf
server-id = 2
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
[root@localhost ~]# systemctl restart mysqld.service
[root@localhost ~]# mysql -u root -padmin123
mysql> change master to master_host='192.168.68.200',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=603;
mysql> start slave;
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[root@localhost ~]# vim /etc/my.cnf
[ root@localhost ~]# systemctl restart mysqld.service ## Restart database service
3.mycat server: 192.168.68.40
[root@localhost ~]# systemctl stop firewalld.service [root@localhost ~]# setenforce 0 [root@localhost ~]# yum install -y java [root@localhost ~]# wget http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz ## download software [root@localhost ~]# mkdir /apps [root@localhost ~]# tar zxf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /apps/ ##Unzip package [root@localhost ~]# echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh [root@localhost ~]# source /etc/profile.d/mycat.sh [root@localhost ~]# mycat start [root@localhost ~]# tail -f /apps/mycat/logs/wrapper.log
4. Client: 192.168.68.195
[root@localhost ~]# systemctl stop firewalld.service [root@localhost ~]# setenforce 0 [root@localhost ~]# mysql -uroot -p123456 -h 192.168.68.105 -P8066 mysql> show databases; +----------+ | DATABASE | +----------+ | TESTDB | +----------+ 1 row in set (0.01 sec)
five mycat server: 192.168.68.40
[root@localhost conf]# vim server.xml
45 <property name="serverPort">3306</property> <property name="managerPort">9066</property>
46 <property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property>
47 <property name="dataNodeIdleCheckPeriod">300000</property>
48 <property name="frontWriteQueueSize">4096</property> <property name="processors">32</property>
[root@localhost conf]# vim schema.xml
<?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"></schema>
<dataNode name="dn1" dataHost="localhost1" database="hellodb" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="host1" url="192.168.68.200:3306" user="root" password="123456">
<readHost host="host2" url="192.168.68.30:3306" user="root" password="123456"/>
</writeHost>
</dataHost>
</mycat:schema>
[root@localhost conf]# mycat restart
[root@localhost conf]# mycat status
[root@localhost conf]# ss -natp |grep 3306
[root@localhost conf]# cat /apps/mycat/logs/wrapper.log ##Successful means normal[root@localhost conf]# vim server.xml
[root@localhost conf]# vim schema.xml
6.master server: 192.168.68.200
to grant authorization
7. Client: 192.168.68.195
8. Verification
On the master-slave server
set global general_log=1;
On the primary server
On the client
Enter data on the data sheet
Master server detected
But there is no record to add data from the server
