This is the fourth day of my participation in the August Text Challenge.More challenges in August
This article is based on the MySQL master-slave replication example in another article, to use MyCat as database middleware, to achieve MySQL read and write separation that exposes only one database connection entry.
Premise 0.
- Complete the deployment of MySQL master-slave replication (portal).
- Again, based on Docker, run a MyCat container.
- That should be it.
1. MyCat container deployment
First, download MyCat. Since we are deploying in a Docker container, download the Linux version.
$wget HTTP: / / http://dl.mycat.io/1.6.7.1/Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gzCopy the code
At this point, you get a mycat-server-1.6.7.1-release-20190627191042-linux.tar. gz file. Give it a different name for later use.
$mv Mycat - server - 1.6.7.1 - release - 20190627191042 - Linux. Tar. Gz Mycat. Tar. GzCopy the code
Unzip it in the current directory and make a copy of the conf directory for the Docker container to mount, so you don’t need to go into the container to modify MyCat configuration.
$ tar -zxvf mycat.tar.gz
$ cp -r mycat/conf/ conf/
Copy the code
Once the preparation is complete, start building the container image, create a Dockerfile and write the following to it:
FROM openjdk:8
ADD mycat.tar.gz /usr/local/
VOLUME /usr/local/mycat/conf
ENV MYCAT_HOME=/usr/local/mycat
EXPOSE 8066 9066
CMD ["/usr/local/mycat/bin/mycat","console","&"]
Copy the code
Explain the contents of a Dockerfile line by line:
- MyCat is Java based and therefore used
openjdk:8
The image builds the MyCat image we used. - Rename the download
mycat.tar.gz
Added to the container/usr/local/
Directory. - Directory MyCat configuration files
/usr/local/mycat/conf
It is exposed to the host and mapped directly to the previous host at startupconf
Directory. - Setting environment Variables
MYCAT_HOME
, the value is the installation directory of MyCat. - Ports 8066 and 9066 are exposed. Port 8066 is used to connect to external databases, similar to port 3306 of MySQL. 9066 is the management port of MyCat.
- Execute the command to start MyCat.
Build image in current directory:
$docker build-t mycat-1.6.7.1.Copy the code
Then start an instance of the container:
$ docker run --name mycat -p 8066:8066 -p 9066:9066 -v /path/to/conf/:/usr/local/mycat/conf/ -d mycat-1.6.7.1 --link mysql-dn-s:mysql-slave --link mysql-dn-m:mysql-master
Copy the code
Note: /path/to/conf/ refers to the absolute path of the conf directory on the actual host. At the same time, a network connection is created between the two databases with –link.
The MyCat container is now up and running, but not ready to use until the data source is configured.
2. Modify the MyCat configuration
Since we mapped the configuration file directory when we started the container, we changed the configuration of MyCat directly in the host’s conf directory.
Modify schema.xml as follows:
<! DOCTYPEmycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<! -- MyCat schema exposed -->
<schema name="test" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
<! -- Data node, here we only configure the part of the read-write separation, so only one node is configured to do the read-write separation example -->
<dataNode name="dn1" dataHost="localhost1" database="test" />
<! Balance =1
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<! -- Query statement for heartbeat notifications using default -->
<heartbeat>select user()</heartbeat>
<! -- Write library configuration -->
<writeHost host="hostM" url="mysql-master:3306" user="root" password="admin123">
<! -- Read library configuration -->
<readHost host="hostS" url="mysql-slave:3306" user="root" password="admin123" />
</writeHost>
</dataHost>
</mycat:schema>
Copy the code
Because this example only demonstrates read/write separation and does not involve configurations such as partition sharding, this configuration file is simple.
To configure server.xml, simply open the file and add one external access user for MyCat, or multiple, depending on the situation:
<user name="root" defaultAccount="true">
<property name="password">admin123</property>
<property name="schemas">test</property>
</user>
Copy the code
As an example, only the basic user name, password and library are configured. In practice, you can also configure read and write permissions, or even table-level DML permissions. For details, you can consult official documents.
After the configuration is complete, restart the container to take effect.
3. Test and verify
Connect to MyCat on the host using the same method as connect to MySQL:
$ mycli -hlocalhost -P8066 -uroot -padmin123
Copy the code
ALTER USER ‘root’@’%’ IDENTIFIED WITH mysql_native_password BY ‘admin123’; .
After the connection is successful, you can perform database operations just as you would with MySQL directly.
4. Test for read-write separation
When testing read/write separation, you need to look at MyCat’s logs to determine which database each operation was performed on, so you first need to modify the following MyCat log configuration file to have MyCat print out this part of the log.
In the log4j2. XML file in the conf directory, modify the following information:
<asyncRoot level="info" includeLocation="true">
Copy the code
Set the log level to DEBUG, save the changes and restart MyCat.
Now, enter the MyCat container and view the logs in real time using the following command:
$ tail -f /usr/local/mycat/logs/mycat.log
Copy the code
Do not close the window to view the log, start another terminal, connect MyCat to operate on the data, in the log you can see the SQL statement for each step of operation, and more information.
For example, the following two logs:
The 2019-09-07 08:46:42. 628 the DEBUG [$_NIOREACTOR-5-RW] (io.mycat.server.NonBlockingSession.releaseConnection(NonBlockingSession.java:386)) - release connection MySQLConnection [id=14, lastTime=1567846002623, user=root, schema=test, old shema=test, borrowed=true, fromSlaveDB=true, threadId=380, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{select * from test}, respHandler=SingleNodeHandler [node=dn1{select * from test}, packetId=9], host=mysql-slave, port=3306, statusSync=io.mycat.backend.mysql.nio.MySQLConnection$StatusSync@230d92a6, writeQueue=0, modifiedSQLExecuted=false]
Copy the code
The 2019-09-07 08:48:23. 387 the DEBUG [$_NIOREACTOR-5-RW] (io.mycat.server.NonBlockingSession.releaseConnection(NonBlockingSession.java:386)) - release connection MySQLConnection [id=5, lastTime=1567846103379, user=root, schema=test, old shema=test, borrowed=true, fromSlaveDB=false, threadId=587, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{insert into test(value) values(15)}, respHandler=SingleNodeHandler [node=dn1{insert into test(value) values(15)}, packetId=1], host=mysql-master, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
Copy the code
From these two logs, you can see that MyCat performed a query in the read library and an insert in the write library.