Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to achieve the separation of reading and writing in MyCat. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

1. Mycat realizes the separation of read and write.

The mysql03 host is not used here, and the implementation of read-write separation is relatively simple. You only need to modify the following two configuration files of mycat. As follows:

1) modify server.xml file [root@mycat mycat] # pwd/usr/local/mycat [root@mycat mycat] # vim conf/server.xml [root@mycat mycat] # vim conf/server.xml pwd@123 test_mycat 2) modify schema.xml file Select user ()

Note: the dataHost field balance load balancer type mentioned above has the following four values:

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

Balance= "1", all readHost and stand by writeHost participate in the load balancing of select statements. To put it simply, when the double master and double slave mode (M1-> S1J M2-> S2, and M1 and M2 are the master and standby of each other), under normal circumstances, M2M S1jue S2 is involved in the load balancing of select statements.

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

Balance= "3", all read requests are randomly distributed to the corresponding readhost of wiriterHost for execution. WriterHost does not bear the read pressure. Note that balance=3 is only available in 1.4 and later versions, but not in 1.3.

The writeTyep field has values from the following 3:

1. WriteType= "0". All write operations are sent to the first writeHost of the configuration, and the first one hangs the second writeHost that is still alive. After restarting, 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. SwitchType attribute

-1 means that the switch is not automatic.

1 default value, automatically switched.

2 decide whether to switch based on the status of MySQL master-slave synchronization.

The complete modified configuration file is as follows (to avoid redundancy Some comments have been deleted): the contents of the server.xml configuration file are as follows: [root@mycat mycat] # vim conf/server.xml 0 0200 1 1m 1k 0384m The true pwd@123 test_mycat schema.xml configuration file is as follows: [root@mycat mycat] # vim conf/schema.xml select user ()

Note: at this point, users with relevant permissions are created on the backend database. For example, if the user value of the writehost and readhost fields I specified in the datahost field above is root, then the root account of the database must have the corresponding login permission (by default, root users are only allowed to log in at localhost, so you have to modify the root permission or change it to other authorized users).

Summary:

1. The specified backend database login user must have the appropriate permissions

two。 The backend real database specified above is test, so the backend must have this test library.

3) start mycat and test the effect of read-write separation [root@mycat mycat] # mycat start # start mycat [root @ mycat mycat] # mysql-umycat-ppwd@123-h 192.168.20.4-P 8066 # Log in to mycat#192.168.20.4 as the mycat host IP,mycat listening port 8066, using the login user created in the server.xml file # to create a table and insert some test data mysql > use test_mycat Mysql > create table T1 (id int,name varchar (4)); mysql > insert into T1 values (1), (2), (3)); # at this point, check the back-end database, there should be the above data. # now you can temporarily disable the master-slave replication of the backend database, and then continue to insert data on the mycat host to test whether you can read the # turn off the master-slave replication command: mysql > insert into T1 values on the mycat host or on the mycat host; # insert a data Query OK, 1 row affected (0.01 sec) mysql > select * from T1 # cannot query the data that has just been inserted It indicates that the read and write operations are not performed on the same host +-+-+ | id | name | +-+-+ | 1 | Zhang San | 2 | Lisi | | 3 | Wang Wu | +-+-+ # you can go to the backend two databases to view the data # the data of the mysql01 host Below (indicating that the write operation is in mysql01): mysql > select * from T1 +-+-+ | id | name | +-+-+ | 1 | Zhang San | 2 | Li Si | 3 | Wang Wu | 4 | Zhao Liu | +-+-+ 4 rows in set (0.00 sec) # data of mysql02 hosts are as follows: mysql > select * from T1 (indicating that the read operation is in mysql02) +-+-+ | id | name | +-+-+ | 1 | Zhang San | 2 | Li Si | 3 | Wang Wu | +-+-+ 3 rows in set (0.00 sec)

The effect of read-write separation ends here. (in order not to affect the subsequent testing, it is best to restore master-slave replication of mysql01 and mysql02 hosts.)

2. Mycat implements sub-libraries.

A database consists of many tables, and each table corresponds to a different business. The so-called sub-database means that the tables are classified according to the business and cannot be assigned to different databases, so that the data or pressure is shared to different databases. The principle of sub-database: tables that are closely related to each other should be in one database, and tables that are not or not related to each other can be divided into different databases.

Examples of sub-libraries:

Suppose there are four tables: customer, orders, orders_detail, and dict_order_type, each with millions of pieces of data. If these four tables are to be divided into libraries, the customer table can be separated into one database, and the other three tables can be separated in another database.

1) modify server.xml file (just change the logical library name) [root@mycat mycat] # cat conf/server.xml 0 0200 1 1m 1k 0384m true pwd@123 test_db 2) modify schema.xml file [root@mycat mycat] # cat conf/schema.xml Select user () select user () 3) restart mycat [root@mycat mycat] # mycat restart

Note: the real database specified by the backend must have a test database, because the dataNode field in the above schema.xml file specifies to connect to the backend test database.

4) Test the effect of sub-library # log in to mycat [root @ mycat mycat] # mysql-umycat-ppwd@123-h 192.168.20.4-P 806 create the corresponding table mysql > use test_db;mysql > CREATE TABLE customer (- > id_a INT AUTO_INCREMENT,-> NAME VARCHAR (200),-> PRIMARY KEY (id_a)->) Query OK, 0 rows affected (0.02 sec) mysql > CREATE TABLE orders (- > id_b INT AUTO_INCREMENT,-> order_type INT,-> customer_id INT,-> amount DECIMAL (10L2),-> PRIMARY KEY (id_b)->) Query OK, 0 rows affected (0.01 sec) mysql > CREATE TABLE orders_detail (- > id_c INT AUTO_INCREMENT,-> detail VARCHAR (2000),-> order_id INT,-> PRIMARY KEY (id_c)->); Query OK, 0 rows affected (0.02 sec) mysql > CREATE TABLE dict_order_type (- > id_d INT AUTO_INCREMENT,-> order_type VARCHAR (200),-> PRIMARY KEY (id_d)->) Query OK, 0 rows affected (0.01 sec) # Log in to the backend database to see if the table is split as expected [root@mycat mycat] # mysql-uroot-p123.com-h 192.168.20.2mysql > use test;mysql > show tables # you can see that the host mysql01 has three tables +-+ | Tables_in_test | +-+ | dict_order_type | | orders | | orders_detail | +-+ 3 rows in set (0.00 sec) # Log in to mysql03 to view [root@mycat] Mycat] # mysql-uroot-p123.com-h 192.168.20.21mysql > use test Mysql > show tables; # you can see that there is only customer table +-+ | Tables_in_test | +-+ | customer | +-+ 1 row in set (0.00 sec)

So far, you have implemented a sub-library, with multiple tables in different libraries (in the chestnut above, multiple tables are placed on different hosts), but for customers logging in through mycat, it is still a library with four tables in the library.

3. Mycat realizes sub-table.

Here a new table emp is split.

1) schema.xml file is modified as follows (full configuration file) [root@mycat mycat] # cat conf/schema.xml select user () select user () 2) rule.xml file is added The following content [root@mycat mycat] # vim conf/rule.xml # modifies the rule.xml file id mod_long. 23) restart mycat and log in to mycat [root @ mycat mycat] # mycat restart4) Log in to mycat to insert data to test the split table effect [root@mycat mycat] # mysql-umycat-ppwd@123-h 192.168.20.4-P 8066mysql > use test_db;# create emp table (this table will be available on both back-end database nodes) mysql > create table emp (- > id int,-> name varchar (10)->) Query OK, 0 rows affected (0.41 sec) # insert multiple data (for this sub-table, you need to specify a column name, otherwise an error will be reported) mysql > insert into emp (id,name) values (1pm 'Zhang San'), (2m'Li Si'), (3J 'Wang Wu'), (4 'Zhao Liu'); # query the newly inserted data mysql > select * from emp +-+-+ | id | name | +-+-+ | 2 | Li Si | 4 | Zhao Liu | 1 | Zhang San | | 3 | Wang Wu | +-+ + 4 rows in set (0.04 sec) mysql > select * from emp order by id # sort the results +-+-+ | id | name | +-+-+ | 1 | Zhang San | 2 | Li Si | 3 | Wang Wu | | 4 | Zhao Liu | +-+ 4 rows in set (0.01sec) # the data queried by the backend mysql01 host is as follows: mysql > select * from emp +-+-+ | id | name | +-+-+ | 2 | Li Si | | 4 | Zhao Liu | +-+-+ 2 rows in set (0.00 sec) # the data queried by mysql03 host is as follows: mysql > select * from emp +-+-+ | id | name | +-+-+ | 1 | Zhang San | | 3 | Wang Wu | +-+-+ 2 rows in set (0.00 sec)

You can see that the data is evenly distributed on different nodes.

However, after slicing the ordinary table, it is impossible to join directly with other tables.

In order to solve the problem of multi-table join, we also need to modify the schema.xml configuration file and add the childTable field under the table field of the sub-table. how to implement it is still under study. However, the type of the subtable can be set to the global table, which is a relatively simple way to implement.

3. Overall table

After dividing the table, how to join with other tables becomes a thorny problem. We can choose to set the sub-table as a global table, which is a solution to join (but not the only way), but the global table has some limitations, as follows:

The global table does not change frequently

The overall amount of data has not changed much.

The scale of the data is small, and there are few records of more than hundreds of thousands.

Global tables have the following characteristics:

The insert and update operations of the global table will be performed on all nodes in real time to maintain the data consistency of each fragment.

The query operation of the global table, which is obtained from only one node

Global tables can JOIN with any table.

To create a global table, simply add type= "global" to the table field. As follows:

1) define global table [root@mycat conf] # cat schema.xml select user () select user () 2) verify global table mysql > drop table emp # need to delete the previously created emp table Query OK, 0 rows affected (0.03 sec) # recreate the emp table mysql > create table emp (- > id int,-> name varchar (10)->); Query OK, 0 rows affected (0.38 sec) # insert data test mysql > insert into emp (id,name) values (1), (2) Li Si), (3) Wang Wu), (4) Zhao Liu) # query the inserted data on mycat as follows: mysql > select * from emp +-+-+ | id | name | +-+-+ | 1 | Zhang San | 2 | Li Si | 3 | Wang Wu | 4 | Zhao Liu | +-+-+ 4 rows in set (0.02 sec) # View the data in the emp table on the backend host: # the data on the mysql01 host is as follows: mysql > select * from emp +-+-+ | id | name | +-+-+ | 1 | Zhang San | 2 | Li Si | 3 | Wang Wu | 4 | Zhao Liu | +-+ 4 rows in set (0.00 sec) # data on mysql03 hosts are as follows: mysql > select * from emp +-+-+ | id | name | +-+-+ | 1 | Zhang San | 2 | Li Si | 3 | Wang Wu | 4 | Zhao Liu | +-+-+ 4 rows in set (0.00 sec) the above is how to achieve read-write separation in the MyCat shared by Xiaobian, if you happen to have similar doubts It may be understood with reference to the above analysis. If you want to know more about it, you are welcome to follow the industry information channel.

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.