Shulou(Shulou.com)06/02 Report--

I. brief introduction

In this paper, docker is used to realize the functions of mysql master-slave configuration, read-write separation, sub-database and sub-table.

II. Environmental preparation

1. Basic environment

Java

Java version "1.8.0o111" Java (TM) SE Runtime Environment (build 1.8.0_111-b14) Java HotSpot (TM) 64-Bit Server VM (build 25.111-b14, mixed mode)

Docker

Client: Version: 18.03.0-ce API version: 1.37 Go version: go1.9.4 Git commit: 0520e24 Built: Wed Mar 21 23:09:15 2018 OS/Arch: linux/amd64 Experimental: false Orchestrator: swarmServer: Engine: Version: 18.03.0-ce API version: 1.37 (minimum version 1.12) Go version: go1.9.4 Git commit: 0520e24 Built: Wed Mar 21 23:13: 03 2018 OS/Arch: linux/amd64 Experimental: false Install Mysql master-slave configuration

1. Deployment Inode name data name node IP port mastermarster192.168.92.503306slaveslave192.168.92.513307mycatmycat192.168.92.508066/90662. Master Node Profil

Create a mysql master node profile

Mkdir / usr/local/mysql/mastermkdir conf data

Create a master node profile docker.cnf

[mysqld] server-id=1log-bin=master-bin # only reads and writes, as long as the main library is configured. If you want to switch between master and slave, both master and slave libraries need to be enabled. Skip-host-cacheskip-name-resolvecollation-server = utf8_unicode_ciinit-connect='SET NAMES utf8'character-set-server = utf8 [mysql] default-character-set=utf8 [client] default-character-set=utf8

Create a slave node profile docker.conf

Mkdir / usr/local/mysql/slavemkdir conf data [mysqld] server-id=2log-bin=master-bin skip-host-cacheskip-name-resolvecollation-server = utf8_unicode_ciinit-connect='SET NAMES utf8'character-set-server = utf8 [mysql] default-character-set=utf8 [client] default-character-set=utf83. Create mysql container

The container is created here using the latest stable image of mariadb

Create the primary node myslq

Docker run-name master-p 3306 MYSQL_ROOT_PASSWORD=123456 3306-v / usr/local/mysql/master/conf:/etc/mysql/conf.d-v / usr/local/mysql/master/data:/var/lib/mysql-e MYSQL_ROOT_PASSWORD=123456-idt mariadb:latest-- character-set-server=utf8mb4-- collation-server=utf8mb4_unicode_ci

Create a slave node mysql

Docker run-- name slave-p 3307 usr/local/mysql/slave/conf:/etc/mysql/conf.d-v / usr/local/mysql/slave/data:/var/lib/mysql-e MYSQL_ROOT_PASSWORD=123456-idt mariadb:latest-- character-set-server=utf8mb4-- collation-server=utf8mb4_unicode_ci4. Turn on master-slave replication

Step 1 enters the primary node

Docker exec-it master / bin/bashmysql-uroot-p

Step 2 create user

Create user 'backUser'@'%' identified by' root';grant replication slave on *. * to 'backUser'@'%';flush privileges;show master status;MariaDB [(none)] > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +- -+-+ | master-bin.000003 | 787 | +-+ 1 row in set (0.000 sec)

Step 3 enters the slave node to create the user to enable synchronization

Docker exec-it slave / bin/bashmysql-uroot-prootchange master to master_host='192.168.92.51',master_port=3306,master_user='backUser',master_password='root',master_log_file='master-bin.000003',master_log_pos=787

Enable master-slave replication:

Start slave

The slave node sets the log and file name to correspond to the master node information, including the logging start location position

Check whether the master-slave replication is OK

Show slave status\ G * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.92.50 Master_User: backUser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000005 Read_ Master_Log_Pos: 343 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 556 Relay_Master_Log_File: master-bin.000005 Slave_IO_Running: Yes Slave_SQL_Running: Yes

There are 2 YES in Slave, which means that the master-slave replication setting is successful.

5. Install Mycat

Download mycat and install it at 50 nodes

Tar-zxvf Mycat-server-1.6.7.1-release-20190213150257-linux.tar.gzmv mycat/ / usr/local/

Modify the server.xml file of conf in the mycat directory to specify the user name and password

0 1 0 0 0 false 0 0 1 64k 1k 0 384m false False true 123456 test

The above configuration specifies that the user name is root and the password is 123456 to access the virtual logical database test.

Configure rule rules

Modify the corresponding rules of the schema.xml configuration database under the mycat conf folder

Select user ()

The above configuration scheam name corresponds to the virtual database of the server configuration file, specifying two table information, the tb_user table primary key is self-growing, has four database nodes, and uses userrule table rules.

DataNode specifies the real corresponding physical database node, and the corresponding dataHost describes the user and node information specified for reading and writing.

Configure table sharding rule file rule.xml

Id func1 id jump-consistent-hash 0 2 160 Partition-hash-int.txt autopartition-long.txt 3 8 128 24 Yyyy-MM-dd 2015-01-01 partition-range-mod.txt 3

The above file focuses on the table field in which the first tableRule and rule specify the sharding rule, and the algorithm specifies the sharding algorithm, where func1 corresponds to the function name at the end of the file as func1, and PartitionByLong sharding algorithm is used here.

Add sequence_conf.properties file under conf, and its contents are as follows:

TB_USER.HISIDS=TB_USER.MINID=1TB_USER.MAXID=20000TB_USER.CURID=1

It mainly declares the strategy of primary key growth.

IV. Practical test of Mycat sub-database and sub-table.

The 1.master node manually creates the database

The master node manually creates 4 database db1,db2,db3,db4 (do not operate the slave node)

When you open the slave node, you will observe that slave also automatically creates four databases.

two。 Turn on mycat

Use the command. / mycat start to open mycat

. / mycat start start

. / mycat stop stop

. / mycat console foreground operation

. / mycat restart restart the service

. / mycat pause paused

. / mycat status to view startup status

If startup fails, check the wrapper.log log file information for / usr/local/mycat.

FATAL | wrapper | 14:36:09 on 2019-04-21 | ERROR: Could not write pid file / usr/local/mycat/logs/mycat.pid: No such file or directory

If you encounter the above error, please create a logs folder in the mycat directory and restart it.

[root@localhost mycat] # bin/mycat statusMycat-server is running (5065).

The above message indicates that mycat started successfully.

two。 Release mycat communication port

Firewall-cmd-zone=public-add-port=8066/tcp-permanentfirewall-cmd-zone=public-add-port=9066/tcp-permanentfirewall-cmd-reload

Use docker image to open mycat container instance

Docker run-- name mycat-v / usr/local/mycat/conf/schema.xml:/usr/local/mycat/conf/schema.xml-v / usr/local/mycat/conf/rule.xml:/usr/local/mycat/conf/rule.xml-v / usr/local/mycat/conf/server.xml:/usr/local/mycat/conf/server.xml-v / usr/local/mycat/conf/sequence_conf.properties:/usr/local/mycat/conf/sequence_conf.properties-- Privileged=true-p 8066 MYSQL_ROOT_PASSWORD=123456-d longhronshens/mycat-docker

Or turn off the firewall.

3. Mycat connection

Connect to mycat using navicate, port 8066 (9066 is the management port)

Use the command to connect to mycat

[root@localhost] # mysql-h227.0.0.1-uroot-p123456-P8066 Welcome to the MariaDB monitor. Commands end with; or\ g.Your MySQL connection id is 12Server version: 5.6.29-mycat-1.6.7.1-release-20190213150257 MyCat Server (OpenCloudDB) Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.MySQL [(none)] >

Management side commands:

Mysql-h227.0.0.1-uroot-proot-P9066

View the virtual logic library:

MySQL [(none)] > show databases;+-+ | DATABASE | +-+ | test | +-+ 1 row in set (0.00 sec) MySQL [(none)] >

Create tables using logical libraries:

MySQL [(none)] > use test CREATE TABLE `user` (`id` bigint (20) NOT NULL AUTO_INCREMENT, `username` varchar (50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'username', `password` varchar (32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'password Encrypted storage', `phone` varchar (20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'register mobile phone number', `email` varchar (50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'registered mailbox', `created` datetime (0) NOT NULL, `updated` datetime (0) NOT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `username` (`username`) USING BTREE, UNIQUE INDEX `phone` (`phone`) USING BTREE UNIQUE INDEX `email` (`email`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 54 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 'user table' ROW_FORMAT = Compact

You can see that both the mycat,mysql master and slave have created the table

Before creating a table:

CREATE TABLE `tb_ roomy` (`id` varchar (5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `name` varchar (200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'name', `sort_ order` int (4) NOT NULL DEFAULT 1 COMMENT 'indicates the order in which the purposes of the same class are displayed, and if the values are equal, they are arranged in name order. Value range: integer greater than zero', `created` datetime (0) NULL DEFAULT NULL, `updated` datetime (0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, INDEX `updated` (`updated`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact

Insert a piece of data:

INSERT INTO `tb_ user` (id,username,password,phone,email,created,updated) VALUES (7, 'zhangsan',' e10adc3949ba59abbe56e057f20f88888, 'aa@a',' 2015-04-06 17VR 0355 VALUES, '2015-04-06 17V 03RV 55')

To test the effect of sub-libraries, let's insert data from different ID to observe:

INSERT INTO `tb_ user` (id,username,password,phone,email,created,updated) VALUES (128, 'zhang02',' e10adc3949ba59abbe56e057f20f88882mm, 'aa@01.com',' 2015-04-06 17id,username,password,phone,email,created,updated 03VRV 57V, '2015-04-06 17V 04VR 55') INSERT INTO `tb_ user` (id,username,password,phone,email,created,updated) VALUES (256, 'zhang03',' e10adc3949ba59abbe56e057f20f8888, '134888883mm,' aa@02.com', '2015-04-06 17 id,username,password,phone,email,created,updated 03V 57,' 2015-04-06 17V 04V 55') INSERT INTO `tb_ user` (id,username,password,phone,email,created,updated) VALUES (384, 'zhang05',' e10adc3949ba59abbe56e057f20f8888, '13488888885,' aa@05.com', '2015-04-06 17 id,username,password,phone,email,created,updated 0315 57,' 2015-04-06 17V 04lav 55')

You can see that the data is distributed in the db1/db2/db3/db4, and the distribution rules depend on the shard rule constraints set by the primary key of the inserted data in the rule.xml.

To check the health status of the mycat node, enter the following command on the primary node:

[root@localhost] # mysql-h227.0.0.1-uroot-p123456-P9066 Welcome to the MariaDB monitor. Commands end with; or\ g.Your MySQL connection id is 16Server version: 5.6.29-mycat-1.6.7.1-release-20190213150257 MyCat Server (monitor) Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.Type 'help;' or'\ h' for help. Type'\ c' to clear the current input statement.MySQL [(none)] > show @ @ heartbeat +- -- + | NAME | TYPE | HOST | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME | STOP | +- -+ | hostM1 | mysql | 192.168.92.50 | 3306 | 1 | 0 | idle | 30000 | 1meme 9 6 | 2019-04-21 20:44:40 | false | | hostS2 | mysql | 192.168.92.51 | 3307 | 1 | 0 | idle | 30000 | 1Magi 9 67381 | 2019-04-21 20:44:40 | false | +- -+-+ 2 rows in set (0.36 sec)

The above RS_CODE 1 indicates that the node is normal, and-1 indicates that the node is abnormal.

Query all mycat commands:

MySQL [(none)] > show @ @ help +-+ | STATEMENT | | DESCRIPTION | +-+ | Show @ @ time.current | Report current timestamp | | show @ @ time.startup | Report startup timestamp | | show @ @ version | Report Mycat Server version | | show @ @ server | Report server status | | show @ @ threadpool | Report threadPool status | | show @ @ database | Report databases | | show @ @ datanode | Report dataNodes | | show @ @ datanode where schema =? | Report dataNodes | | show @ @ datasource | Report dataSources | | show @ @ datasource where dataNode =? | Report dataSources | | | show @ @ datasource.synstatus | Report datasource data synchronous | | show @ @ datasource.syndetail where name=? | Report datasource data synchronous detail | | show @ @ datasource.cluster | Report datasource galary cluster variables | | show @ @ processor | Report processor status | | show @ @ command | Report commands status | | show @ | @ connection | Report connection status | | show @ @ cache | Report system cache usage | | show @ @ backend | Report backend connection status | | show @ @ session | Report front session details | | show @ @ connection.sql | Report connection sql | | show | @ @ sql.execute | Report execute status | | show @ @ sql.detail where id =? | Report execute detail status | | show @ @ sql | Report SQL list | | show @ @ sql.high | Report Hight Frequency SQL | | show @ @ sql.slow | | | Report slow SQL | | show @ @ sql.resultset | Report BIG RESULTSET SQL | | show @ @ sql.sum | Report User RW Stat | | show @ @ sql.sum.user | Report User RW Stat | | show @ @ sql.sum.table | Report Table RW Stat | | | show @ @ parser | Report parser status | | show @ @ router | Report router status | | show @ @ heartbeat | Report heartbeat status | | show @ @ heartbeat.detail where name=? | Report heartbeat current detail | | show @ @ slow where schema =? | | Report schema slow sql | | show @ @ slow where datanode =? | Report datanode slow sql | | show @ @ sysparam | Report system param | | show @ @ syslog limit=? | Report system mycat.log | | show @ @ white | show mycat white host | | show @ @ white.set=? | | | set mycat white host, [ip,user] | | show @ @ directmemory=1 or 2 | show mycat directmemory usage | | show @ @ check_global-SCHEMA=?-TABLE=?-retry=?-interval=? | check mycat global table consistency | | switch @ @ datasource name:index | Switch dataSource | | kill @ @ connection id1,id2 | | Kill the specified connections | | stop @ @ heartbeat name:time | Pause dataNode heartbeat | | reload @ @ config | Reload basic config from file | | reload @ @ config_all | Reload all config from file | | reload @ @ route | Reload route config from file | Reload @ @ user | Reload user config from file | | reload @ @ sqlslow= | Set Slow SQL Time (ms) | | reload @ @ user_stat | Reset show @ @ sql @ @ sql.sum @ @ sql.slow | | rollback @ @ config | Rollback all config from memory | rollback @ @ route | | Rollback route config from memory | | rollback @ @ user | Rollback user config from memory | | reload @ @ sqlstat=open | Open real-time sqlstat analyzer | | reload @ @ sqlstat=close | Close real-time sqlstat analyzer | | offline | Change MyCat status to OFF | | online | | | Change MyCat status to ON | | clear @ @ slow where schema =? | Clear slow sql by schema | | clear @ @ slow where datanode =? | Clear slow sql by datanode | +-| -+-+ 59 rows in set (0.16 sec)

The following error was encountered:

Modify the schema.xml file properties checkSQLschema:

The following error was encountered:

Jvm 1 | Caused by: io.mycat.config.util.ConfigException: org.xml.sax.SAXParseException; lineNumber: 97; columnNumber: 42 Attribute "defaultAccount" must be declared for element type "user" .jvm1 | at io.mycat.config.loader.xml.XMLServerLoader.load (XMLServerLoader.java:111) jvm 1 | at io.mycat.config.loader.xml.XMLServerLoader. (XMLServerLoader.java:69) jvm 1 | at io.mycat.config.loader.xml.XMLConfigLoader. (XMLConfigLoader.java:56) jvm 1 | at io.mycat.config.ConfigInitializer. (ConfigInitializer.java:77) jvm 1 | At io.mycat.config.MycatConfig. (MycatConfig.java:72) jvm 1 | at io.mycat.MycatServer. (MycatServer.java:144) jvm 1 | at io.mycat.MycatServer. (MycatServer.java:96) jvm 1 |. 7 morejvm 1 | Caused by: org.xml.sax.SAXParseException LineNumber: 97; columnNumber: 42; Attribute "defaultAccount" must be declared for element type "user".

Please modify the server.xml file to cancel the defaultAccount of the user module:

123456 test

Finally, put a screenshot of the mycat query result:

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.