Docker application -6 (MYSQL+MYCAT build DB cluster)

In the previous section, a cross-host Docker container cluster was built by using the overlay network. Below, in this cross-host Docker container cluster environment, build MySQL database cluster.

MySQL master-slave automatic backup and automatic switching

From data security considerations, it is important to back up database data in a timely manner. MySQL provides a master-slave backup mechanism, from the database will be very timely update the operation of the main database from the library, so when the primary database is down, the data will not be lost, but also through the keepalived and other programs to achieve the main library down, Quickly switch the data access service to the slave library to maintain uninterrupted operation of the database service as much as possible.

Mycat Database Middleware

Mycat is a completely open source of innovative database middleware products. It will completely end the bottleneck of the database, so that the high availability of the database, high load becomes possible. Mycat is powerful, it realizes the separation and automatic switching of master-slave database by using Mycat.

build MySQL master-slave architecture

The

uses two physical machines dev-11, two container on dev-12, one container as MySQL master and the other as slave
1. Build a docker network across hosts
Docker Network create-d overlay Multihost
and creates a Container-control container for connecting to database tests.
Docker Create--name container-contorl--net multihost centos:7 bash
2. Download Image
Code>docker Pull mysql:5.7
3. Create a folder locally and store the database data locally
/home/skc/workspace/ Gradute_design/docker_data/mysql
4. Creating a container
deploying mysql-master containers on dev-11

docker create -it --name mysql-master            --net multihost            -v /home/skc/workspace/graduate_design/docker_data/mysql/data:/var/lib/mysql            -v /home/skc/workspace/graduate_design/docker_data/mysql/conf.d:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=root mysql:5.7 bash

Deploying the Mysql-slave container on dev-12

docker create -it --name mysql-slave            --net multihost            -v /home/skc/workspace/graduate_design/docker_data/mysql/data:/var/lib/mysql            -v /home/skc/workspace/graduate_design/docker_data/mysql/conf.d:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=root mysql:5.7 bash

Where MySQL data files are placed in the/home/skc/workspace/graduate_design/docker_data/mysql/data directory (via volume mapping)
The MySQL configuration file is placed in the/HOME/SKC/WORKSPACE/GRADUATE_DESIGN/DOCKER_DATA/MYSQL/CONF.D directory for configuring the master-slave database

In the/home/skc/workspace/graduate_design/docker_data/mysql/conf.d/directory on dev-11, configure file 101.cnf

[mysqld]log-bin=mysql-binserver-id=101

In the/home/skc/workspace/graduate_design/docker_data/mysql/conf.d/directory on dev-12, configure file 102.cnf

[mysqld]log-bin=mysql-binserver-id=102

5. Configure the database
Launch Docker start on dev-11 and dev-12 respectively Mysql-master/mysql-slave
Connect to Mysql-master from the Container-control container to view information

MySQL [(none)]> show master status;+------------------+----------+--------------+------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000005 | 154 | | | |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)

To connect to Mysql-slave from the Container-control container, set

mysql\> change master to master_host=‘10.0.0.5’, master_user=‘root‘, master_password=‘root‘, master_log_file=‘mysql-bin.000005‘,master_log_pos=154;mysql\> start slave;

(Once created, you will find that master and slave are synchronized, such as creating a database in master, automatically creating it in slave, changing the data in master, and automatically modifying it in slave)

Build Mycat

Use the Docker container to run Mycat.
1. Writing Dockerfile

############################################# version:debugman007/c7-mycat:v1# Desc:centos7 installed on the mycat############ ################################# settings inherit from CENTOS7 official image from CentOS:7 RUN Echo"Root:root" | Chpasswdrun yum-y Install net-tools# install Javaadd http:Mirrors.linuxeye.com/jdk/jdk-7u80-linux-x64.tar.gz/usr/local/run cd/usr/local && tar-zxvf JDK-7u80-linux-x64.tar.gz && rm-f jdk- 7u80-linux-x64.tar.gzenv java_home/usr/local/jdk1.7.0_80ENV CLASSPATH ${java_home}/lib/dt.jar: $JAVA _home/lib/tools.jarenv PATH $PATH: ${java_home}/bin#install mycatadd http:< Span class= "Hljs-comment" >//dl.mycat.io/1.6-release/mycat-server-1.6-release-20161028204710-linux.tar.gz/usr/ Localrun cd/usr/local && tar-zxvf mycat-server-1.6-release -20161028204710-linux.tar.gz && rm-f mycat-server-1.6 -release-20161028204710-linux.tar.gz volume/usr/local/mycat/confexpose 8066 9066cmd ["/usr/local/mycat/bin/mycat ", Span class= "hljs-string" > "console"]           

2. Create Mirror
# docker build-t Debugman007/c7-mycat.
3. Create a container

 #docker create--name Mysql-mycat--net Multihost-v /home/skc/workspace/graduate_design/docker_data/mycat/conf:/usr/local/mycat/conf-v/home/skc/workspace/graduate_ Design/docker_data/mycat/logs:/usr/local /mycat/logs-p 8066:8066-p 9066:9066 debugman007/c7-mycat:v1  

4. Configure Mycat
After extracting the mycat-server-1.6-release-20161028204710-linux.tar.gz, copy the contents of the mycat/conf to the/home/skc/workspace/graduate_ design/docker_data/mycat/conf, modify the Server.xml

    <UserName="Root" ><PropertyName="Password" >root</Property><PropertyName="Schemas" >testdb</Property><!--table-level DML permissions Settings-<!--<privileges check= "false" > <schema name= "TESTDB" dml= "0110" > <table name= "tb01" dml= "0000" >& lt;/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>    

Configure the password and logical library for root and user users.
Modify the Schema.xml configuration Datanode.

<SchemaName="TESTDB"Checksqlschema="False"sqlmaxlimit=">"<TableName="Students_score"Type="Global"Datanode="Dn1"/></Schema><DataNodeName="DN1"datahost="Localhost1"Database="DB1"/><DataNodeName="DN2"datahost="Localhost1"Database="DB2"/><DataNodeName="Dn3"datahost="Localhost1"Database="Db3"/><DatahostName="Localhost1"maxcon="1000"mincon="10"Balance="2"Writetype="0"Dbtype="MySQL"Dbdriver="Native"Switchtype="2"Slavethreshold=">"<Heartbeat>select User ()</Heartbeat><!--can have multi write hosts--<Writehosthost="Mysql-master"Url="10.0.0.5:3306"User="Root"password="Root" ><!--can have multi read hosts--<Readhosthost= "Mysql-slave" url=" 10.0.0.6:3306 "user=" root " password= "root"/> </writehost> <writehost  Host= "Mysql-slave" url= " 10.0.0.6:3306 "user=" root "password=< Span class= "hljs-string" > "root" > </writehost> </DATAHOST>         

Primary and standby automatic switching test
At this point, the database can be read and written through the Mycat middleware, where 8066 ports operate on the data and 9066 ports are control ports.

[[Email protected]0bef73420229/]# mysql-uroot-p-hmysql-mycat-p8066enter password:welcomeTo the MariaDB Monitor. Commandsend with; or \g.your MySQL Connection ID is  7Server version: 5.6. 29-mycat-1.6-release-20161028204710 mycat Server ( OPENCLOUNDDB) Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. Type  ' help; ' or  ' \h ' for help. type  ' \c ' to clear the current input statement. MySQL [(None)]>              

At this point, the database modification operation through the Mysql-mycat interface can be seen in both Mysql-mater and Mysql-slave.
When the mysql-master is closed, go inside the Mycat 9066 port, check the database status, and find Mysql-master turned off.

MySQL [(None)]> Show @@heartbeat, +--------------+-------+----------+------+---------+-------+--------+---------+--------------+------ ---------------+-------+|NAME |TYPE |HOST |PORT |Rs_code |RETRY |STATUS |TIMEOUT |Execute_time |Last_active_time |STOP |+--------------+-------+----------+------+---------+-------+--------+---------+--------------+------------ ---------+-------+|Mysql-master |MySQL |10.0.0.5 |3306 |-2 |0 |Idle |0 |1,0,51 |2017-02-23 03:57:02 |False | | Mysql-slave |  MySQL |  10.0.0.6 |  3306 |  1 |  0 |  Idle |  0 |  1,1,1 |  2017-02-23 03:57:22 |  false | |  Mysql-slave |  MySQL |  10.0.0.6 |  3306 |  1 |  0 |  Idle |  0 |  0,1,1 |  2017-02-23 03:57:22 |  false |+--------------+-------+----------+------+---------+-------+--------+---------+ --------------+---------------------+-------+ 

At this point, modifying the database through Mycat is still successful because the database service has switched to mysql-slave from the library.

Reference

Http://blog.yaodataking.com/2016/01/mycat_mysql_docker_sample1.html

Docker application -6 (MYSQL+MYCAT build DB cluster)