Preface
There's a video that often entices me to "play to death" and a big BOSS that keeps "pushing me to learn faster".It is these two extremes of love that often convince me of where I am going.Well, it's not worth it!
Introduction to SCHEMA.XML
The last one was written: Database middleware DBLE learning (1) Basic introduction and fast building, mainly introducing fast installation.During the installation, we configured the schema.xml file.Schema.xml is an important file.This file provides configurations for logical libraries, logical tables, partitioning rules, data partitioning, physical databases, and so on.It consists mainly of the following tags.
- schema tags are mainly the configuration of logical libraries and logical tables, among which there are many types of logical tables, such as global tables, fragmented tables, ER fragmented tables, non-fragmented tables, and so on.
- A dataNode tag, which mainly defines the nodes where data is stored in fragments, often corresponds to a data fragment.
- The dataHost tag, which defines the configuration of the physical database and the master-slave read-write separation.
Actual SCHEMA.XML Configuration
Next, let's have a real fight. We will take the classic PRODUCT table and ORDERS order table to do the experiment.As mentioned earlier, there are many types of logical table splitting.First of all, let's plan. Suppose we are a new e-commerce company, selling products produced by our own company on the platform of the commerce. There are 5-6 kinds of goods in total, but we can't stop the customers'love for the products we produce. The money is explosive. Once the orders are sold, the amount is amazing.So how do we split the library tables?
- PRODUCTS product list, we sell several explosives, we want to make it a global table.That is, there is data for this PRODUCTS product table on each slice.That won't cost much, because this table has a small amount of data.It is also easier to associate with slice tables.
- ORDERS order form with tens of millions of orders.This is a large amount of data. We want to make the ORDERS table into a piecewise table.There is a one-to-many relationship between the PRODUCTS product table and the ORDERS table.We can use order_id to partition the modules.This way, there will be no data overheating, and the product table is a small table on each slice, so we can easily use the product table to associate with the order table.
Say so much, it's better to take a picture to show you.
As shown, applications that connect directly to database middleware, operate on logical libraries testdb, PRODUCTS, and ORDERS.The above logical libraries and tables are stored on physical database hosts A and B.There is fragmented DN1 on MySQL host A and fragmented DN2 on MySQL host B.The global table PRODUCTS and ORDERS table modeled by order_id are stored on dn1.Global table PRODUCTS and ORDERS Table 2 modeled by order_id are stored on dn2.
The whole thread is cleared up, which is our configuration environment.
| The server | IP Address | describe |
|---|---|---|
| DBLE Server | 192.168.56.185 | DBLE instance, database middleware, responsible for receiving SQL for routing distribution |
| MySQL A Server | 192.168.56.181 | Physical instance A, with db_1 database |
| MySQL B1 Server Master | 192.168.56.182 | Physical instance B, with db_2 database |
| MySQL B2 server from | 192.168.56.183 | Slave Library of Physical Instance B |
Physical MySQL Environment Configuration
Install the master and slave environments for MySQL Server A and MySQL Server B.Buddy users are then created on both sets of physical databases.
create user 'buddy'@'%' identified by '123456'; GRANT ALL PRIVILEGES ON *.* TO 'buddy'@'%' IDENTIFIED BY '123456';
Configure schema Tags
Go to the conf folder of dble to configure the schema.xml file.Let's configure the schema tag first.The main configurations are as follows:
<schema name="testdb">
<!--er tables-->
<table name="orders" primaryKey="order_id" dataNode="dn1,dn2" rule="rule_mod"/>
<!--global tables-->
<table name="products" primaryKey="product_id" type="global" dataNode="dn1,dn2"/>
</schema>-
sehama tag
For the definition of a logical library, name specifies the name of the logical library.
-
table tag
For table definition, name specifies the name of the table, dataNode specifies the data nodes involved in the table, where both tables specify dn1,dn2.rule specifies the rules for fragmentation, where is the modularization algorithm.If it is a global table, it needs to be declared global in the type attribute column.
Configure dataNode Tags
<dataNode name="dn1" dataHost="dataHost1" database="db_1"/> <dataNode name="dn2" dataHost="dataHost2" database="db_2"/>
-
dataNode tag
Name specifies the name of the data fragment node, dataHost specifies the corresponding database instance, and database pairs specify the schema in the mysql physical instance.
Configure dataHost Tags
<dataHost name="dataHost1" maxCon="1000" minCon="10" balance="0" switchType="-1" slaveThreshold="100">
<heartbeat>show slave status</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="192.168.56.181:3306" user="buddy" password="buddy">
</writeHost>
</dataHost>
<dataHost name="dataHost2" maxCon="1000" minCon="10" balance="0" switchType="-1" slaveThreshold="100">
<heartbeat>show slave status</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM2" url="192.168.56.182:3306" user="buddy" password="buddy">
<readHost host="hostS2" url="192.168.56.183:3306" user="buddy" password="buddy"/>
</writeHost>
</dataHost>-
dataHost tag
The database instance is specified, where name represents the name of the database instance.maxCon defines the maximum number of connections, and minCon defines the minimum number of connections to keep idle.balance refers to the load balancing mode for read operations, where 0 is not balanced.switchType represents the highly available switching type for write operations.Equal to -1 means no automatic switch.SlaveThreshold specifies a threshold of 100 for the specified master-slave delay.This configuration is mainly related to load balancing when reading data. It takes the Seconds_Behind_Master value from show slave statutes first, and if it is greater than slaveThreshold, it filters out the slave when reading.Prevent reading old data and affect business at the front desk.
-
heartbeat tag
Specifies the heartbeat detection, where the statement to detect the heartbeat is show slave status
-
WteHost tag
Specifies the write node.host is the name of the write node.url specifies the ip and port number to write to the node.User specifies the user name to write to the node database and password specifies the password to write to the node database.
-
readHost tag
Specifies the read node.host is the name of the read node.url specifies the ip and port number of the read node.User specifies the user name of the read node database and password specifies the password of the read node database.
Start dble
Once the configuration is complete, we can start dble.
[root@mycat bin]# ./dble start Starting dble-server... Removed stale pid file: /dble/dble.pid
Looking at the wrapper log after boot unexpectedly reported an error.
INFO | jvm 1 | 2019/12/27 00:34:15 | com.actiontech.dble.config.util.ConfigException: Illegal table conf : table [ orders ] rule function [ func_mod ] partition size : 4 > table datanode size : 2, please make sure table datanode size = function partition size
The error is obvious here, mainly because the modulus function defaults to 4, which is larger than our dataNode.We need to change the partitionCount of the func_mod function to 2 in rule.xml.
<function name="func_mod" class="Hash">
<property name="partitionCount">2</property>
<property name="partitionLength">1</property>
</function>Restart dble to start successfully.
Create slices and validate
Log in using the administrative port to create a datanode.
[root@mysql5 ~]# mysql -uman1 -p -P9066 -h192.168.56.185 -p654321 mysql> create database @@dataNode='dn$1-2'; Query OK, 1 row affected (0.03 sec)
Log in to MySQL Server A and MySQL Server B to see how the fragmentation was created.You can see that db_1 was created on 181 and db_2 on 182.
[root@mysql5 ~]# mysql -ubuddy -p -h192.168.56.181 -P3306 -p123456 -e "show databases" +--------------------+ | Database | +--------------------+ | information_schema | | db_1 | | mysql | | performance_schema | | sys | +--------------------+ [root@mysql5 ~]# mysql -ubuddy -p -h192.168.56.182 -P3306 -p123456 -e "show databases" +--------------------+ | Database | +--------------------+ | information_schema | | db_2 | | mysql | | performance_schema | | sys | +--------------------+
Create tables and insert data
Log in using the service port to create tables and insert data.At this point we are working on logical libraries and tables.
[root@mysql5 ~]# mysql -uroot -h192.168.56.185 -P8066 -p123456
mysql> show databases;
+----------+
| DATABASE |
+----------+
| testdb |
+----------+
1 row in set (0.00 sec)
mysql> use testdb;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> create table if not exists products (
-> product_id int not null,
-> product_name varchar(250),
-> expire_time date,
-> primary key(product_id)
-> )engine=innodb charset=utf8;
Query OK, 0 rows affected, 1 warning (0.08 sec)
mysql> create table if not exists orders (
-> order_id int not null,
-> product_id int not null,
-> effective_time date,
-> cust_name varchar(20),
-> cust_address varchar(200),
-> primary key(order_id)
-> )engine=innodb charset=utf8;
Query OK, 0 rows affected (0.04 sec)
insert into products values(1,'Package A','2015-12-31')
insert into products values(2,'Package B','2022-07-31');
insert into products values(3,'Package C','2025-12-31');
insert into products values(4,'Package D','2028-05-31');
insert into orders values(1,1,'2019-12-25','caocao','gz');
insert into orders values(1,1,'2019-12-25','caocao','gz');
insert into orders values(2,1,'2019-12-25','liubei','sz');
insert into orders values(3,2,'2019-12-25','sunquan','sh');
insert into orders values(4,2,'2019-12-25','zhugeliang','nj');
insert into orders values(5,3,'2019-12-25','simayi','hz');
insert into orders values(6,4,'2019-12-25','caopi','wh');
insert into orders values(7,1,'2019-12-25','guanyu','cd');
insert into orders values(8,1,'2019-12-25','zhengfei','bj');
insert into orders values(9,3,'2019-12-25','zhaoyun','cd');
insert into orders values(10,1,'2019-12-25','weiyan','cq');
insert into orders values(11,4,'2019-12-25','machao','cs');
insert into orders values(12,1,'2019-12-25','lvbu','cd');
insert into orders values(13,1,'2019-12-25','diaochan','sh');
insert into orders values(14,2,'2019-12-25','huangzhong','hz');
mysql> insert into orders values(1,1,'2019-12-25','caocao','gz');
ERROR 1064 (HY000): bad insert sql, sharding column/joinKey:ID not provided,INSERT INTO ordersError again when inserting the orders table, it is obvious that the slicing key ID is not provided.This is because our columns field in rule.xml is not the specified slice field order_id.Configure the columns field as order_id in rule.xml.
<tableRule name="rule_mod">
<rule>
<columns>order_id</columns>
<algorithm>func_mod</algorithm>
</rule>
</tableRule>Verify that the fragmented data is correct
Validate the data on the db_1 fragment.
[root@mysql5 ~]# mysql -ubuddy -h192.168.56.181 -P3306 -p123456 -D db_1 -e "select * from products" +------------+--------------+-------------+ | product_id | product_name | expire_time | +------------+--------------+-------------+ | 1 | Package A | 2015-12-31 | | 1 | Package B | 2022-07-31 | | 1 | Package C | 2025-12-31 | | 1 | Package D | 2028-05-31 | +------------+--------------+-------------+ [root@mysql5 ~]# mysql -ubuddy -h192.168.56.181 -P3306 -p123456 -D db_1 -e "select * from orders" +----------+------------+----------------+------------+--------------+ | order_id | product_id | effective_time | cust_name | cust_address | +----------+------------+----------------+------------+--------------+ | 2 | 1 | 2019-12-25 | liubei | sz | | 4 | 2 | 2019-12-25 | zhugeliang | nj | | 6 | 4 | 2019-12-25 | caopi | wh | | 8 | 1 | 2019-12-25 | zhengfei | bj | | 10 | 1 | 2019-12-25 | weiyan | cq | | 12 | 1 | 2019-12-25 | lvbu | cd | | 14 | 2 | 2019-12-25 | huangzhong | hz | +----------+------------+----------------+------------+--------------+
Validate the data on the db_2 fragment.
[root@mysql5 ~]# mysql -ubuddy -h192.168.56.182 -P3306 -p123456 -D db_2 -e "select * from products" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+--------------+-------------+ | product_id | product_name | expire_time | +------------+--------------+-------------+ | 1 | Package A | 2015-12-31 | | 1 | Package B | 2022-07-31 | | 1 | Package C | 2025-12-31 | | 1 | Package D | 2028-05-31 | +------------+--------------+-------------+ [root@mysql5 ~]# mysql -ubuddy -h192.168.56.182 -P3306 -p123456 -D db_2 -e "select * from orders" +----------+------------+----------------+-----------+--------------+ | order_id | product_id | effective_time | cust_name | cust_address | +----------+------------+----------------+-----------+--------------+ | 1 | 1 | 2019-12-25 | caocao | gz | | 3 | 2 | 2019-12-25 | sunquan | sh | | 5 | 3 | 2019-12-25 | simayi | hz | | 7 | 1 | 2019-12-25 | guanyu | cd | | 9 | 3 | 2019-12-25 | zhaoyun | cd | | 11 | 4 | 2019-12-25 | machao | cs | | 13 | 1 | 2019-12-25 | diaochan | sh | +----------+------------+----------------+-----------+--------------+
summary
In this article, we have learned the basic configuration method of schema.xml, which is still furry.In the next section, we will introduce some algorithms for fragmentation.