Kingshard SQL blacklist feature Introduction

This is a creation in Article, where the information may have evolved or changed.

Kingshard SQL blacklist feature Introduction

1. Introduction to the application scenario

After Kingshard open source, there are many times that users have mentioned can be added to the Kingshard SQL blacklist mechanism, so that kingshard can be based on specific rules to intercept the SQL in the Blacklist. There are several typical application scenarios:

1. The DBA defines some of the more dangerous SQL that is placed in the SQL blacklist file. You can avoid the SQL that is sent by the front-end app to harm the database. This SQL could have been written by a developer carelessly, or it might have been SQL injection generated. For example delete from mytable , this SQL without a where condition will delete the entire table.

2. After the Kingshard project was launched, a large amount of SQL was found to be causing a lot of pressure on the db through log. This time the SQL can be added to the blacklist dynamically to prevent the execution of the SQL, thereby reducing the database pressure. For example select count(*) from mytable where xxxx , if this kind of SQL is not optimized properly, it is very easy to cause the system IO too high.

2. Function Introduction

In Kingshard if you want to use the SQL blacklist feature, simply add it in the configuration:

blacklist_sql_file: /Users/flike/blacklist

Then we define the SQL blacklist in blacklist so that when the Kingshard is forwarded, it blocks the SQL forwarding in the blacklist.

The

Blacklist SQL is defined as a regular expression. For values in SQL ? or ? + instead. To ensure that the blacklist is valid, it is best to manually verify that Kingshard correctly intercepted the SQL in the Blacklist. Define the rule (the previous is the original SQL, the corresponding next is the blacklist form of SQL) can refer to the following example:

 SELECT C from T where Id=1select C from t where id=? SELECT * from Prices.rt_5min where Id=1select * from prices.rt_5min where id=?select null, 5.001, 5001. From Fooselect?,?,? From Fooselect ' Hello ', ' \nhello\n ', \ ' hello\ ', ' \ \ ' from Fooselect?,?,?,? From Fooselect ' Hello ' \nselect? Select * from T where (Base.nid in (' 1412 ', ' 1410 ', ' 1411 ')) SELECT * from T where (BASE.N ID in (? +)) SELECT * from Foo where A in (5) and B in (5, 8,9, 9, ten) SELECT * from Foo where a with (? +) and B in (? +) SELECT * from Foo limit 5select * from foo limit? SELECT * from foo limit 5, 10select * from foo limit?,? select * from foo limit 5 offset 10select * from foo limit? Offset? INSERT into T (TS) VALUES (now ()) insert into T (TS) VALUES (? +) insert into foo (A, B, c) the values (2, 4, 5) INSERT into foo (A, B , c) VALUES (? +) call Foo (1, 2, 3) Call fooload data INFILE '/tmp/foo.txt ' into db.tblload data INFILE? Into Db.tbladministrator command:init dbadministrator command:init dbuse ' foo ' use? 

3. Function demonstration

Add the following SQL to blacklist:

select count(*) from test_shard_hash where id > ?select count(*) from test_shard_rangeSELECT * FROM WORLDDELETE FROM WORLD

To connect Kingshard, execute SQL is displayed as follows:

mysql> select * from world;ERROR 1105 (HY000): sql in blacklist.mysql> select * from world where a > 0;+------+------+| a    | b    |+------+------+|   10 |   23 ||   45 |  565 |+------+------+2 rows in set (0.00 sec)mysql> delete from world;ERROR 1105 (HY000): sql in blacklist.mysql> delete from world where a =10;Query OK, 1 row affected (0.00 sec)#注意在SQL黑名单中该SQL是大于后面有个空格，必须要严格匹配，否则#kingshard不会认为是黑名单SQLmysql> select count(*) from test_shard_hash where id >1;+----------+| count(*) |+----------+|       24 |+----------+1 row in set (0.02 sec)mysql> select count(*) from test_shard_hash where id > 1;ERROR 1105 (HY000): sql in blacklist.

Using Sysbench to test the performance of blacklist when there is a Kingshad, the performance has not been significantly reduced, so you can rest assured that the function.