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

Master-slave configuration read-write separation and sub-table of mycat experiment

1. Experimental environment:

3 vmware virtual machines (there are too few virtual machines, the computer is a little difficult, and the 3 virtual machines can only reach the basic test)

System centos7 (the experiment is done by turning off the firewall and selinux)

Mysql version 5.7

Mycat version 1.6

Virtual machine name and ip:

Mysql1 192.168.211.138

Mysql2 192.168.211.139

Mysql3 192.168.211.142

Mycat is installed on mysql1 (192.168.211.138)

This host must be able to resolve the address of the mysql2,mysql3.

Parsing can be added manually through / etc/hosts

two。 Experimental purpose

Realize the function of mycat read-write separation and sub-table.

3. Experimental thinking

Create a logical library in mycat: hello.

Create four tables:

T1 this table is used for slicing and slicing with enumerated rules.

T2 this table is used as an overall table.

T3 this table is used as a regular table, define it to be put into one of the datanode, and put into mysql1

T4 this table is used as a regular table, define it to be put into one of the datanode, and put into mysql3

Allocation of virtual machines:

Mysql1 and mysql2 are master and slave configurations, in which mysql1 is the master, mysql2 is the slave, mysql1 is used to write, and mysql2 is used to read.

Mysql3 is a separate datanode.

Actually we only have two datanode,mysql1,mysql3.

4. Experimental procedure

Install mysql5.7 on the virtual machine respectively, with brief steps.

Set up the mysql user, step by step. Please note that you must set up a user to allow an intranet address to connect or simply allow anywhere to connect. For example, set 'root'@'%'.

Install mycat. The steps are brief.

Configure mysql1 and mysql2 master and slave, with brief steps.

The above are all common configurations, and I don't bother to record them.

Now that the above environment is configured, we can begin to experiment with the configuration of the core step mycat.

Now start the configuration of the whole process.

1. Create a db1 in mysql1 (192.168.211.138).

Mysql > create database db1; Query OK, 1 row affected (0.01 sec)

Check that mysql2 (192.168.211.139) is synchronized?

Mysql > show databases +-+ | Database | +-+ | information_schema | | db1 | | logs | | mysql | | performance_schema | | sys | | wordpress | +-+ 7 rows in set (0.00 sec)

Synchronized.

two。 Create a db2 in mysql3 (192.168.211.142).

Mysql > show databases +-+ | Database | +-+ | information_schema | | db2 | | logs | | mysql | | performance_schema | | sys | | wordpress | +-+ 7 rows in set (0.01 sec)

3. Configure mycat in mysql1, and my mycat is installed in mysql1.

My profile path:

[root@mysql1 conf] # pwd/usr/local/mycat/conf

The main configuration files are:

[root@mysql1 conf] # lsautopartition-long.txt log4j2.xml schema.xml.bk server.xml.bkauto-sharding-long.txt migrateTables.properties schema.xml.bk2 sharding-by-enum.txtauto-sharding-rang-mod.txt myid.properties sequence_conf.properties wrapper.confcacheservice.properties partition-hash -int.txt sequence_db_conf.properties zkconfdnindex.properties partition-range-mod.txt sequence_distributed_conf.properties zkdownloadehcache.xml rule.xml sequence_time_conf.propertiesindex_to_charset.properties schema.xml server.xml [root@mysql1 conf] #

What needs to be used is: schema.xml # # this file configures the read-write separation strategy of sub-table.

Rule.xml # # this is the definition of the sub-table rule

Server.xml # # account password and firewall settings for logging in to mycat

4. First configure: schema.xml

Back up the original configuration file:

[root@mysql1 conf] # cp schema.xml schema.xml.bk3

Empty the profile:

[root@mysql1 conf] # echo "" > schema.xml

To reconfigure schema.xml, here is the entire contents of my configuration file:

Select user () select user ()

For the explanation of these fields, you can see the parsing in my previous slicing rules.

Explain the meaning of the datahost balance field

Balance attribute

There are three types of load balancer:

1. Balance= "0". The read-write separation mechanism is not enabled, and all read operations are sent to the currently available writeHost.

2. Balance= "1", all readHost and stand by writeHost participate in the load balancing of select statements, in short, when the double master and double slave mode (M1-> S1 M2-> S2, and M1 and M1

M2 is the active and standby of each other. Under normal circumstances, M2 Magi S1 and S2 participate in the load balancing of select statements.

3. Balance= "2", all read operations are randomly distributed on writeHost and readhost.

4. Balance= "3". All read requests are randomly distributed to the readhost corresponding to wiriterHost for execution. WriterHost does not bear the reading pressure.

WriteType attribute

There are three types of load balancer:

1. WriteType= "0". All write operations are sent to the first writeHost of the configuration, and the first fails to cut to the second writeHost that is still alive. After reboot, the switched one shall prevail.

The switch is recorded in the configuration file: dnindex.properties.

2. WriteType= "1". It is not recommended that all write operations are randomly sent to the configured writeHost,1.5.

What needs to be explained is:

Select user ()

Separation of reading and writing there are two ways to write:

In addition to the above, you can also write the following:

Select user ()

The difference is that if the master server is down, the slave server can't read it.

In the following way of writing, the master server is down and the slave server can still be read normally.

Let's talk about it here. Tests will be done later.

5. Check out the configuration in rule.xml

Look at the settings of subtable T1:

The sub-table rule is sharding-by-intfile

Look at the rule settings for this.

City hash-int

Why is columns city?

This city is set by me, and the planned creation table T1 (id,name,bu,city) has these four columns. I'm going to use city to do sharding columns.

Keep looking down.

Partition-hash-int.txt 1 0

This is the setting of the hash-int function used. Where mpfile is the configuration file to read. What does this mean?

Check out the contents of partition-hash-int.txt.

[root@mysql1 conf] # cat partition-hash-int.txt # 10000=0#10010=1bj=0gz=0sz=1 [root@mysql1 conf] #

My planned table T1 city has three values, bj gz sz, which means that the data of bj gz is stored in datanode1, that is, the data store of mysql1,sz.

To datanode2, that is, mysql3.

1 0

These two parameters must also be available. The default value of type is 0, and the format of 0 is interger. Note that the column of our shard is city is a character.

6. Configure server.xml, refer to the official tutorial, it is very simple.

7. Here we can start mycat, log in to create tables, and insert data tests. Can we achieve our desired goals?

[root@mysql1 conf] # mysql-uroot-p123456-P8066-h 127.0.0.1mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 2Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql >

Log in.

Mysql > show databases;+-+ | DATABASE | +-+ | hello | +-+ 1 row in set (0.02 sec) mysql >

The logic library hello that we set up

Mysql > use hello;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > show tables +-+ | Tables in hello | +-+ | T1 | | T2 | | T3 | | T4 | +-+ 4 rows in set (0.01 sec) mysql > mysql > desc T1 alternative error 1146 (42S02): Table 'db1.t1' doesn't existmysql >

Note that there is a problem here. These t1-t4 forms seem to have, but in fact they are fake. It feels like BUG. The form needs to be created manually.

Create a table:

Mysql > create table T1 (id int not null,name varchar (15) not null,bu varchar (10) not null,city varchar (5) not null); Query OK, 0 rows affected (0.36 sec) mysql > create table T2 (id int not null,name varchar (15) not null); Query OK, 0 rows affected (0.10 sec) mysql > create table T3 (id int not null,gongzi int not null); Query OK, 0 rows affected (0.07 sec) mysql > create table t4 (id int not null,shui int not null) Query OK, 0 rows affected (0.07 sec)

Where do you put all the forms you created?

Mysql > explain create table T1 (id int not null,name varchar (15) not null,bu varchar (10) not null,city varchar (5) not null) +- -DATA_NODE | SQL | +-+- -+ | dn1 | create table T1 (id int not null Name varchar (15) not null,bu varchar (10) not null,city varchar (5) not null) | | dn2 | create table T1 (id int not null,name varchar (15) not null,bu varchar (10) not null) City varchar (5) not null) | +-+- -+ 2 rows in set (0.00 sec) mysql >

This is a slicing table. Dn1,dn2 has it all.

Mysql > explain create table T2 (id int not null,name varchar (15) not null) +-- + | DATA_NODE | SQL | +- +-- + | dn1 | create table T2 (id int not null Name varchar (15) not null) | | dn2 | create table T2 (id int not null,name varchar (15) not null) | +-+-+ 2 rows in set (0.00 sec) mysql >

This is the global table, which is available in dn1,dn2. The global table means that each dn node has a table and the data is consistent.

Mysql > explain create table T3 (id int not null,gongzi int not null) +-+-+ | DATA_NODE | SQL | +-+- -+ | dn1 | create table T3 (id int not null Gongzi int not null) | +-+-- + 1 row in set (0.00 sec) mysql >

Ordinary watch, preset is put on dn1, normal

Mysql > explain create table T4 (id int not null,shui int not null) +-+-+ | DATA_NODE | SQL | +-+- -+ | dn2 | create table T4 (id int not null Shui int not null) | +-+-- + 1 row in set (0.00 sec) mysql >

Ordinary watch, preset is put on dn2, normal

Take a look at the situation from the library:

Mysql > show tables;+-+ | Tables_in_db1 | +-+ | T1 | | T2 | | T3 | +-+ 3 rows in set (0.00 sec) mysql >

Except for T4, everything else is normal.

Insert data:

Insert T1

Mysql > insert into T1 (id,name,bu,city) values; Query OK, 1 row affected (0.38 sec) mysql > insert into T1 (id,name,bu,city) values; Query OK, 1 row affected (0.03 sec) mysql > insert into T1 (id,name,bu,city) values

Insert T2

Mysql > insert into T2 (id,name) value (4 Duplicates 4'), (5 sec 5'); Query OK, 2 Duplicates (0.11 sec) Records: 2 Duplicates: 0 Warnings: 0

Insert T3

Mysql > insert into T3 (id,gongzi) values (6 Duplicates 1000), (7 sec 1200); Query OK, 2 Duplicates (0 Warnings) Records: 2 Duplicates: 0 Warnings: 0

Insert T4

Mysql > insert into T4 (id,shui) values (8 Duplicates 10), (9 sec 8); Query OK, 2 rows affected (0.04 sec) Records: 2 Duplicates: 0 Warnings: 0

Infrastructure and environment, as well as achievement, now test:

First of all, T1 according to the preset purpose, the data of bj and gz are stored in dn1 (mysql1), and the data of sz are stored in dn2 (mysql3).

Mysql > select * from T1 where city='bj' or city='gz' +-id | name | bu | city | + +-- + | DATA_NODE | SQL | +- -+-+ | dn1 | SELECT * FROM T1 WHERE city = 'bj' OR city =' gz' LIMIT 100 | +-+- -+ 1 row in set (0.00 sec) mysql > mysql > explain select * from T1 where city='sz' +-+-+ | DATA_NODE | SQL | +- -+ | dn2 | SELECT * FROM T1 WHERE city = 'sz' LIMIT 100 | +-+-+ 1 row in set (0.02 sec) mysql >

The subtable is normal.

Test read-write separation

Open debug log

# # default level is to restart the mycat service after changing info to debug.

Retrieve data:

Retrieve the data of T3, which is only available in mysql1.

Mysql > select * from t3 | id | gongzi | +-- +-+ | 6 | 1000 | | 7 | 1200 | +-+-- + 2 rows in set (0.02 sec)

The following is the mycat.log log, and you can see the data read from mysql2 (192.168.211.139)

2017-12-15 01 io.mycat.server.NonBlockingSession.releaseConnection 47 io.mycat.server.NonBlockingSession.releaseConnection 23.280 DEBUG [$_ NIOREACTOR-0-RW] (io.mycat.server.NonBlockingSession.releaseConnection (NonBlockingSession.java:341))-release connection MySQLConnection [id=22, lastTime=1513320443251, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=true, threadId=33, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1 {SELECT * FROM t3LIMIT 100}, respHandler=SingleNodeHandler [node=dn1 {SELECT * FROM t3LIMIT 100}, packetId=6], host=192.168.211.139, port=3306, statusSync=null, writeQueue=0 ModifiedSQLExecuted=false] 2017-12-1501 NonBlockingSession.java:341)-release connection MySQLConnection [id=26, lastTime=1513320877751, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=true, threadId=31, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1 {SELECT * FROM t3LIMIT 100}, respHandler=SingleNodeHandler [node=dn1 {SELECT * FROM t3LIMIT 100}, packetId=6], host=192.168.211.139, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] mysql > select * from T1 +-+ | id | name | bu | city | +-+ | 3 | am3 | net | sz | | 1 | am1 | sy | bj | | 2 | am2 | cs | gz | +-+ 3 rows in set (0.04 sec)

Journal

2017-12-15 02 DEBUG DEBUG [$_ NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.MySQLConnection.synAndDoExecute (MySQLConnection.java:448))-con need syn, total syn cmd 1 commands SET names utf8 Schema change:false con:MySQLConnection [id=29, lastTime=1513321355817, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=true, threadId=35, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1 {SELECT * FROM t1LIMIT 100}, respHandler=io.mycat.backend.mysql.nio.handler.MultiNodeQueryHandler@11eaaf37, host=192.168.211.139, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] 2017-12-15 02purl 02bank 35.819 DEBUG [$NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.MySQLConnection.synAndDoExecute (MySQLConnection.java:448)-con need syn, total syn cmd 1 commands SET names utf8 Schema change:false con:MySQLConnection [id=20, lastTime=1513321355819, user=root, schema=db2, old shema=db2, borrowed=true, fromSlaveDB=false, threadId=65, charset=utf8, txIsolation=3, autocommit=true, attachment=dn2 {SELECT * FROM t1LIMIT 100}, respHandler=io.mycat.backend.mysql.nio.handler.MultiNodeQueryHandler@11eaaf37, host=192.168.211.142, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

Perform a few more times to retrieve the data that exists in dn1, and check the log to confirm that the data is read from mysql2 (192.168.211.139). The separation of reading and writing is realized.

8. The Test of two different ways of Reading and Writing

It was mentioned earlier that there are two writing methods for the separation of reading and writing.

Select user ()

Separation of reading and writing there are two ways to write:

In addition to the above, you can also write the following:

Select user ()

The difference is that if the master server is down, the slave server can't read it.

In the following way of writing, the master server is down and the slave server can still be read normally.

Now the server is written in the first way, the master server is down and the slave server can't read it. Take a look at the execution.

[root@mysql1 ~] # systemctl stop mysqld.service [root@mysql1 ~] # systemctl status mysqld.service ● mysqld.service-MySQL Server Loaded: loaded (/ usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) Active: inactive (dead) since Fri 2017-12-15 02:16:43 EST 35s ago Docs: man:mysqld (8) [root@mysql1] # mysql-uroot-p123456-P8066-h227.0.0.1mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 2Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql > select * from T1 * error 3000 (HY000): No MyCAT Database selectedmysql > select * from T1 * error 3000 (HY000): No MyCAT Database selected

It really can't be read.

Test the second kind

Modify schema.xml configuration file

Restart mysql and mycat to ensure a normal environment

Normal use environment, we now shut down the mysql1 is the primary node

[root@mysql1 conf] # systemctl stop mysqld.service [root@mysql1 conf] # systemctl status mysqld.service ● mysqld.service-MySQL Server Loaded: loaded (/ usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) Active: inactive (dead) since Fri 2017-12-15 02:23:55 EST; 23s ago Docs: man:mysqld (8)

Retrieve the data and have a look

[root@mysql1] # mysql-uroot-p123456-P8066-h227.0.0.1mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 1Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c' to clear the current input statement.mysql > use hello;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > select * from T1 +-+ | id | name | bu | city | +-+ | 3 | am3 | net | sz | | 1 | am1 | sy | bj | | 2 | am2 | cs | gz | +-+ 3 rows in set (.46 sec)

If so, there is no need to explain which is better or worse.

Postscript: through the construction of the environment and simple testing, the expected goal of the design has been achieved.

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.