Introduce

Introduce

 

Find out all the tables sorted descendingly by
size.

Official website:

4、sys.all_sql_modules就是sys.sql_modules和sys.system_sql_modules这个视图的并集结果的,当然也不支持系统定义的类型为C、D、R、TR类型的对象。

 1 [root@zlm2 09:33:14 /data/mysql/mysql3308/data]
 2 #pt-find -h192.168.1.101 -P3308 -uzlm -pzlmzlm --empty
 3 `mysql`.`columns_priv`
 4 `mysql`.`event`
 5 `mysql`.`func`
 6 `mysql`.`ndb_binlog_index`
 7 `mysql`.`plugin`
 8 `mysql`.`procs_priv`
 9 `mysql`.`servers`
10 `mysql`.`slave_master_info`
11 `mysql`.`slave_relay_log_info`
12 `mysql`.`slave_worker_info`
13 `mysql`.`time_zone`
14 `mysql`.`time_zone_leap_second`
15 `mysql`.`time_zone_name`
16 `mysql`.`time_zone_transition`
17 `mysql`.`time_zone_transition_type`
18 `performance_schema`.`metadata_locks`
19 `performance_schema`.`prepared_statements_instances`
20 `performance_schema`.`replication_group_member_stats`
21 `performance_schema`.`replication_group_members`

 

澳门微尼斯人手机版 1

Procedure

1. Installation

注意:

**Find out all the** ta**bles over
16K.**

Procedure

3、dbo.helptext2的选择性测试

 1 [root@zlm2 09:44:10 /data/mysql/mysql3308/data]
 2 #pt-find -h192.168.1.101 -P3308 -uzlm -pzlmzlm --tablesize +16
 3 `mysql`.`columns_priv`
 4 `mysql`.`db`
 5 `mysql`.`engine_cost`
 6 `mysql`.`event`
 7 `mysql`.`func`
 8 `mysql`.`gtid_executed`
 9 `mysql`.`help_category`
10 `mysql`.`help_keyword`
11 `mysql`.`help_relation`
12 `mysql`.`help_topic`
13 `mysql`.`innodb_index_stats`
14 `mysql`.`innodb_table_stats`
15 `mysql`.`ndb_binlog_index`
16 `mysql`.`plugin`
17 `mysql`.`proc`
18 `mysql`.`procs_priv`
19 `mysql`.`proxies_priv`
20 `mysql`.`server_cost`
21 `mysql`.`servers`
22 `mysql`.`slave_master_info`
23 `mysql`.`slave_relay_log_info`
24 `mysql`.`slave_worker_info`
25 `mysql`.`tables_priv`
26 `mysql`.`time_zone`
27 `mysql`.`time_zone_leap_second`
28 `mysql`.`time_zone_name`
29 `mysql`.`time_zone_transition`
30 `mysql`.`time_zone_transition_type`
31 `mysql`.`user`
32 `sys`.`sys_config`
33 `zlm`.`t1`
34 `zlm`.`t2`

*    The configuration of ProxySQL is a three-layer structure:*

其他对象类型的测试不在全部列举。

    We used to use “find” command in linux
or AIX when we need to get a certain file but cannot rember the precise
name.We will execute “find /pathtobegin -name xxx” in our OS prompt.Is
there any tool can find a specific table of MySQL database which
similarly as “find” does?

 

数据库DDL触发器测试

Find
out all tables in all databases.

  1 [root@zlm2 08:30:12 ~]
  2 #service proxysql start
  3 Starting ProxySQL: 2018-08-10 08:30:38 [INFO] Using config file /etc/proxysql.cnf
  4 DONE!
  5 
  6 [root@zlm2 08:30:38 ~]
  7 #ps aux|grep proxysql
  8 root      4307  0.0  0.5  58688  5184 ?        S    08:30   0:00 proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql
  9 root      4308  0.3  1.8 102612 19020 ?        Sl   08:30   0:00 proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql
 10 root      4335  0.0  0.0 112640   960 pts/0    R+   08:30   0:00 grep --color=auto proxysql
 11 
 12 [root@zlm2 08:32:20 ~]
 13 #cd /var/lib/proxysql
 14 
 15 [root@zlm2 08:33:55 /var/lib/proxysql]
 16 #ls -l
 17 total 244
 18 -rw------- 1 root root 122880 Aug 10 08:16 proxysql.db
 19 -rw------- 1 root root   7320 Aug 10 08:30 proxysql.log
 20 -rw-r--r-- 1 root root      5 Aug 10 08:30 proxysql.pid
 21 -rw------- 1 root root 110592 Aug 10 08:33 proxysql_stats.db
 22 -rw-r--r-- 1 root root      2 Aug 10 08:29 reload
 23 
 24 [root@zlm2 08:33:57 /var/lib/proxysql]
 25 #mysql -uadmin -padmin -h127.0.0.1 -P6032
 26 mysql: [Warning] Using a password on the command line interface can be insecure.
 27 Welcome to the MySQL monitor.  Commands end with ; or \g.
 28 Your MySQL connection id is 1
 29 Server version: 5.5.30 (ProxySQL Admin Module)
 30 
 31 Copyright (c) 2009-2018 Percona LLC and/or its affiliates
 32 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 33 
 34 Oracle is a registered trademark of Oracle Corporation and/or its
 35 affiliates. Other names may be trademarks of their respective
 36 owners.
 37 
 38 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 39 
 40 (admin@127.0.0.1 6032)[(none)]>show databases;
 41 +-----+---------------+-------------------------------------+
 42 | seq | name          | file                                |
 43 +-----+---------------+-------------------------------------+
 44 | 0   | main          |                                     |
 45 | 2   | disk          | /var/lib/proxysql/proxysql.db       |
 46 | 3   | stats         |                                     |
 47 | 4   | monitor       |                                     |
 48 | 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |
 49 +-----+---------------+-------------------------------------+
 50 5 rows in set (0.00 sec)
 51 
 52 //There's five databases in all in PorxySQL database.In the previous version,there's no stats_history database;
 53 
 54 (admin@127.0.0.1 6032)[(none)]>show tables from main;
 55 +--------------------------------------------+
 56 | tables                                     |
 57 +--------------------------------------------+
 58 | global_variables                           | //It contains all the variables of ProxySQL.
 59 | mysql_collations                           |
 60 | mysql_group_replication_hostgroups         |
 61 | mysql_query_rules                          | //It contains all the query rules we'd like to use.
 62 | mysql_query_rules_fast_routing             |
 63 | mysql_replication_hostgroups               |
 64 | mysql_servers                              | //It contains the information of all servers relevant with MySQL.
 65 | mysql_users                                | //It contains the information of all users relevant with MySQL.
 66 | proxysql_servers                           | //It contains the information of all servers relevant with ProxySQL.
 67 | runtime_checksums_values                   |
 68 | runtime_global_variables                   |
 69 | runtime_mysql_group_replication_hostgroups |
 70 | runtime_mysql_query_rules                  |
 71 | runtime_mysql_query_rules_fast_routing     |
 72 | runtime_mysql_replication_hostgroups       |
 73 | runtime_mysql_servers                      |
 74 | runtime_mysql_users                        |
 75 | runtime_proxysql_servers                   |
 76 | runtime_scheduler                          |
 77 | scheduler                                  |
 78 +--------------------------------------------+
 79 20 rows in set (0.00 sec)
 80 
 81 //All the "runtime" prefixed tables in "main" database contain the current configuration in runtime mode.The other tables can be modified in memory mode.
 82 
 83 (admin@127.0.0.1 6032)[(none)]>show tables from disk;
 84 +------------------------------------+
 85 | tables                             |
 86 +------------------------------------+
 87 | global_variables                   |
 88 | mysql_collations                   |
 89 | mysql_group_replication_hostgroups |
 90 | mysql_query_rules                  |
 91 | mysql_query_rules_fast_routing     |
 92 | mysql_replication_hostgroups       |
 93 | mysql_servers                      |
 94 | mysql_users                        |
 95 | proxysql_servers                   |
 96 | scheduler                          |
 97 +------------------------------------+
 98 10 rows in set (0.00 sec)
 99 
100 //All the tables contains the relevant data which we have saved in disk by "SAVE MYSQL xxx TO DISK;" command.
101 //All the tables have the the same structure as those in "main" database.
102 
103 (admin@127.0.0.1 6032)[(none)]>show tables from monitor;
104 +------------------------------------+
105 | tables                             |
106 +------------------------------------+
107 | mysql_server_connect_log           |
108 | mysql_server_group_replication_log |
109 | mysql_server_ping_log              |
110 | mysql_server_read_only_log         |
111 | mysql_server_replication_lag_log   |
112 +------------------------------------+
113 5 rows in set (0.00 sec)
114 
115 (admin@127.0.0.1 6032)[(none)]>show tables from stats;
116 +--------------------------------------+
117 | tables                               |
118 +--------------------------------------+
119 | global_variables                     |
120 | stats_memory_metrics                 |
121 | stats_mysql_commands_counters        |
122 | stats_mysql_connection_pool          |
123 | stats_mysql_connection_pool_reset    |
124 | stats_mysql_global                   |
125 | stats_mysql_prepared_statements_info |
126 | stats_mysql_processlist              |
127 | stats_mysql_query_digest             |
128 | stats_mysql_query_digest_reset       |
129 | stats_mysql_query_rules              |
130 | stats_mysql_users                    |
131 | stats_proxysql_servers_checksums     |
132 | stats_proxysql_servers_metrics       |
133 | stats_proxysql_servers_status        |
134 +--------------------------------------+
135 15 rows in set (0.00 sec)

系统定义存储测试:

*Find out all the tables in database “zlm” which does not
have auto_increment column.***

Configure the MySQL servers in different
hostgroup.

2、sys.sql_modules包括所有用户定义的可编程对象的,当然也不支持计算列和服务器触发器的。

Usage

Start ProxySQL and check tables of
it.

澳门微尼斯人手机版 2

 

 

澳门微尼斯人手机版 3

 1 [root@zlm2 09:28:33 /data/mysql/mysql3308/data]
 2 #pt-find -h192.168.1.101 -P3308 -uzlm -pzlmzlm --engine=MyISAM
 3 `mysql`.`columns_priv`
 4 `mysql`.`db`
 5 `mysql`.`event`
 6 `mysql`.`func`
 7 `mysql`.`ndb_binlog_index`
 8 `mysql`.`proc`
 9 `mysql`.`procs_priv`
10 `mysql`.`proxies_priv`
11 `mysql`.`tables_priv`
12 `mysql`.`user`

 

澳门微尼斯人手机版 4

*    pt-find is a very useful tool to find
out a sepcific table with a condition you’ve specified.It even can
execute a sql operation when finding it simutaneously.

*

 

6、object_definition函数支持以上表格中除服务器除触发器和计算列外的可以变成对象。

Preface

  • LOAD
    MYSQL object FROM MEMORY or LOAD MYSQL object TO RUNTIME 
  • SAVE
    MYSQL object TO MEMORY or SAVE MYSQL object FROM RUNTIME 
  • LOAD
    MYSQL object TO MEMORY or LOAD MYSQL object FROM DISK 
  • SAVE
    MYSQL object FROM MEMORY or SAVE MYSQL object TO DISK 
  • LOAD
    MYSQL object FROM CONFIG

澳门微尼斯人手机版 5

 

 1 (admin@127.0.0.1 6032)[main]>show create table mysql_query_rules\G
 2 *************************** 1. row ***************************
 3        table: mysql_query_rules
 4 Create Table: CREATE TABLE mysql_query_rules (
 5     rule_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
 6     active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 0,
 7     username VARCHAR,
 8     schemaname VARCHAR,
 9     flagIN INT NOT NULL DEFAULT 0,
10     client_addr VARCHAR,
11     proxy_addr VARCHAR,
12     proxy_port INT,
13     digest VARCHAR,
14     match_digest VARCHAR,
15     match_pattern VARCHAR,
16     negate_match_pattern INT CHECK (negate_match_pattern IN (0,1)) NOT NULL DEFAULT 0,
17     re_modifiers VARCHAR DEFAULT 'CASELESS',
18     flagOUT INT,
19     replace_pattern VARCHAR,
20     destination_hostgroup INT DEFAULT NULL,
21     cache_ttl INT CHECK(cache_ttl > 0),
22     reconnect INT CHECK (reconnect IN (0,1)) DEFAULT NULL,
23     timeout INT UNSIGNED,
24     retries INT CHECK (retries>=0 AND retries <=1000),
25     delay INT UNSIGNED,
26     next_query_flagIN INT UNSIGNED,
27     mirror_flagOUT INT UNSIGNED,
28     mirror_hostgroup INT UNSIGNED,
29     error_msg VARCHAR,
30     OK_msg VARCHAR,
31     sticky_conn INT CHECK (sticky_conn IN (0,1)),
32     multiplex INT CHECK (multiplex IN (0,1,2)),
33     log INT CHECK (log IN (0,1)),
34     apply INT CHECK(apply IN (0,1)) NOT NULL DEFAULT 0,
35     comment VARCHAR)
36 1 row in set (0.00 sec)
37 
38 (admin@127.0.0.1 6032)[main]>insert into mysql_query_rules(active,username,match_pattern,schemaname,destination_hostgroup,apply) values(1,'zlm','^select','zlm',20,1);
39 Query OK, 1 row affected (0.00 sec)
40 
41 (admin@127.0.0.1 6032)[main]>select active,username,match_pattern,schemaname,destination_hostgroup,apply from mysql_query_rules;
42 +--------+----------+---------------+------------+-----------------------+-------+
43 | active | username | match_pattern | schemaname | destination_hostgroup | apply |
44 +--------+----------+---------------+------------+-----------------------+-------+
45 | 1      | zlm      | ^select       | zlm        | 20                    | 1     |
46 +--------+----------+---------------+------------+-----------------------+-------+
47 1 row in set (0.00 sec)
48 
49 (admin@127.0.0.1 6032)[main]>load mysql query rules to runtime;save mysql query rules to disk;
50 Query OK, 0 rows affected (0.00 sec)
51 
52 Query OK, 0 rows affected (0.02 sec)
53 
54 (admin@127.0.0.1 6032)[main]>select active,username,match_pattern,schemaname,destination_hostgroup,apply from runtime_mysql_query_rules;
55 +--------+----------+---------------+------------+-----------------------+-------+
56 | active | username | match_pattern | schemaname | destination_hostgroup | apply |
57 +--------+----------+---------------+------------+-----------------------+-------+
58 | 1      | zlm      | ^select       | zlm        | 20                    | 1     |
59 +--------+----------+---------------+------------+-----------------------+-------+
60 1 row in set (0.00 sec)
61 
62 (admin@127.0.0.1 6032)[main]>select active,username,match_pattern,schemaname,destination_hostgroup,apply from disk.mysql_query_rules;
63 +--------+----------+---------------+------------+-----------------------+-------+
64 | active | username | match_pattern | schemaname | destination_hostgroup | apply |
65 +--------+----------+---------------+------------+-----------------------+-------+
66 | 1      | zlm      | ^select       | zlm        | 20                    | 1     |
67 +--------+----------+---------------+------------+-----------------------+-------+
68 1 row in set (0.00 sec)

2、整合实现所有可编程对象定义的查看功能的存储dbo.usp_helptext2

 

Configure
the hostgroup id.

 

Find
out all the MyISAM tables.

 

 

 

7、sys.sp_helpttext和object_definition有个功能的限制:1、在SSMS客户端中如果使用字符串类型变量接收返回的而结果,有可能受制于SSMS客户端针对字符串变量的最大限制(sql
server
2012中的最大限制是43679双字节字符长度)不能全部输出到客户端,这个缺点可以从通过程序编码实现得到完美体现。

*澳门微尼斯人手机版,*Find out table in “zlm” database which
name is like “t3” then drop it.***

Percona
websit:

用户定义约束测试:

 

Configure the monitor user and product
user.

 


 

用户定义检查约束测试:

Summary

The
comparison with other popular middleware tools.

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注