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.

  1. Complete the deployment of MySQL master-slave replication (portal).
  2. Again, based on Docker, run a MyCat container.
  3. 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:

  1. MyCat is Java based and therefore usedopenjdk:8The image builds the MyCat image we used.
  2. Rename the downloadmycat.tar.gzAdded to the container/usr/local/Directory.
  3. Directory MyCat configuration files/usr/local/mycat/confIt is exposed to the host and mapped directly to the previous host at startupconfDirectory.
  4. Setting environment VariablesMYCAT_HOME, the value is the installation directory of MyCat.
  5. 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.
  6. 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.