简介
MySQL默认会有存在一个库information_schema，这个库提供了对数据库元数据的访问，库中的表不是真正的表，磁盘上也没有对应的物理文件，里面的表实际上是视图。（是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。）
INFORMATION_SCHEMA库
mysql> SHOW TABLES FROM information_schema;
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
| KEY_COLUMN_USAGE |
| OPTIMIZER_TRACE |
| PARAMETERS |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| SESSION_STATUS |
| SESSION_VARIABLES |
| STATISTICS |
| TABLES |
| TABLESPACES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
| INNODB_LOCKS |
| INNODB_TRX |
| INNODB_SYS_DATAFILES |
| INNODB_FT_CONFIG |
| INNODB_SYS_VIRTUAL |
| INNODB_CMP |
| INNODB_FT_BEING_DELETED |
| INNODB_CMP_RESET |
| INNODB_CMP_PER_INDEX |
| INNODB_CMPMEM_RESET |
| INNODB_FT_DELETED |
| INNODB_BUFFER_PAGE_LRU |
| INNODB_LOCK_WAITS |
| INNODB_TEMP_TABLE_INFO |
| INNODB_SYS_INDEXES |
| INNODB_SYS_TABLES |
| INNODB_SYS_FIELDS |
| INNODB_CMP_PER_INDEX_RESET |
| INNODB_BUFFER_PAGE |
| INNODB_FT_DEFAULT_STOPWORD |
| INNODB_FT_INDEX_TABLE |
| INNODB_FT_INDEX_CACHE |
| INNODB_SYS_TABLESPACES |
| INNODB_METRICS |
| INNODB_SYS_FOREIGN_COLS |
| INNODB_CMPMEM |
| INNODB_BUFFER_POOL_STATS |
| INNODB_SYS_COLUMNS |
| INNODB_SYS_FOREIGN |
| INNODB_SYS_TABLESTATS |
+---------------------------------------+
61 rows in set (0.00 sec)
TABLES视图
存放数据库中所有表的信息
mysql> DESC TABLES;
+-----------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512) | NO | | | |
| TABLE_SCHEMA | varchar(64) | NO | | | | -- 表所在的库名字
| TABLE_NAME | varchar(64) | NO | | | | -- 表名字
| TABLE_TYPE | varchar(64) | NO | | | | -- 表类型
| ENGINE | varchar(64) | YES | | NULL | | -- 表的引擎
| VERSION | bigint(21) unsigned | YES | | NULL | |
| ROW_FORMAT | varchar(10) | YES | | NULL | |
| TABLE_ROWS | bigint(21) unsigned | YES | | NULL | | -- 表的行数
| AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | | -- 表中行的平均长度
| DATA_LENGTH | bigint(21) unsigned | YES | | NULL | | -- 表数据行占用的字节数
| MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
| INDEX_LENGTH | bigint(21) unsigned | YES | | NULL | | -- 表中的索引所占用的字节数
| DATA_FREE | bigint(21) unsigned | YES | | NULL | |
| AUTO_INCREMENT | bigint(21) unsigned | YES | | NULL | |
| CREATE_TIME | datetime | YES | | NULL | |
| UPDATE_TIME | datetime | YES | | NULL | |
| CHECK_TIME | datetime | YES | | NULL | |
| TABLE_COLLATION | varchar(32) | YES | | NULL | |
| CHECKSUM | bigint(21) unsigned | YES | | NULL | |
| CREATE_OPTIONS | varchar(255) | YES | | NULL | |
| TABLE_COMMENT | varchar(2048) | NO | | | |
+-----------------+---------------------+------+-----+---------+-------+
21 rows in set (0.00 sec)
利用元数据获取数据库状态的示例
统计mydb数据库下所有表的行数和数据量
SELECT
table_schema,
table_name,
table_rows,
(AVG_ROW_LENGTH + INDEX_LENGTH) / 1024 / 1024 AS size -- 平均行长度+索引长度就是每行占用的数据量
FROM
TABLES
WHERE
table_schema = 'mydb';
运行结果:
mysql> SELECT
-> table_schema,
-> table_name,
-> table_rows,
-> (AVG_ROW_LENGTH + INDEX_LENGTH) / 1024 / 1024 AS size -- 平均行长度+索引长度就是每行占用的数据量
-> FROM
-> TABLES
-> WHERE
-> table_schema = 'mydb';
+--------------+-----------------+------------+------------+
| table_schema | table_name | table_rows | size |
+--------------+-----------------+------------+------------+
| mydb | Customers | 5 | 0.00312424 |
| mydb | OrderItems | 18 | 0.01649284 |
| mydb | Orders | 5 | 0.01874924 |
| mydb | Products | 9 | 0.01736069 |
| mydb | Productustomers | NULL | NULL |-- 这个是视图 所有都为空
| mydb | Vendors | 6 | 0.00260353 |
| mydb | t1 | 0 | 0.00000000 |
+--------------+-----------------+------------+------------+
7 rows in set (0.00 sec)
每个数据库下表的个数
SELECT
TABLE_SCHEMA, COUNT(*)
FROM
TABLES
GROUP BY TABLE_SCHEMA;
运行结果
mysql> SELECT
-> TABLE_SCHEMA, COUNT(*)
-> FROM
-> TABLES
-> GROUP BY TABLE_SCHEMA;
+--------------------+----------+
| TABLE_SCHEMA | COUNT(*) |
+--------------------+----------+
| crashcourse | 6 |
| cs | 2 |
| information_schema | 61 |
| mydb | 7 |
| mysql | 31 |
| performance_schema | 87 |
| school | 1 |
| sys | 101 |
| test | 1 |
+--------------------+----------+
9 rows in set (0.00 sec)
整个数据库中所有表的名字及所在的库名
SELECT
table_name, table_schema
FROM
information_schema.TABLES;
运行结果太长，贴部分
mysql> SELECT
-> table_name, table_schema
-> FROM
-> information_schema.TABLES;
+------------------------------------------------------+--------------------+
| table_name | table_schema |
+------------------------------------------------------+--------------------+
| CHARACTER_SETS | information_schema |
| COLLATIONS | information_schema |
| COLLATION_CHARACTER_SET_APPLICABILITY | information_schema |
| COLUMNS | information_schema |
| COLUMN_PRIVILEGES | information_schema |
| ENGINES | information_schema |
| EVENTS | information_schema |
| FILES | information_schema |
| GLOBAL_STATUS | information_schema |
| GLOBAL_VARIABLES | information_schema |
| KEY_COLUMN_USAGE | information_schema |
| OPTIMIZER_TRACE | information_schema |
| PARAMETERS | information_schema |
| PARTITIONS | information_schema |
| PLUGINS | information_schema |
| PROCESSLIST | information_schema |
| PROFILING | information_schema |
.......
查一下数据库引擎是innodb表，表名+库名
SELECT
table_name, table_schema , engine
FROM
information_schema.TABLES
WHERE
ENGINE = 'Innodb';
运行结果
mysql> SELECT
-> table_name, table_schema , engine
-> FROM
-> information_schema.TABLES
-> WHERE
-> ENGINE = 'Innodb';
+---------------------------+--------------------+--------+
| table_name | table_schema | engine |
+---------------------------+--------------------+--------+
| COLUMNS | information_schema | InnoDB |
| EVENTS | information_schema | InnoDB |
| OPTIMIZER_TRACE | information_schema | InnoDB |
| PARAMETERS | information_schema | InnoDB |
| PARTITIONS | information_schema | InnoDB |
| PLUGINS | information_schema | InnoDB |
| PROCESSLIST | information_schema | InnoDB |
| ROUTINES | information_schema | InnoDB |
| TRIGGERS | information_schema | InnoDB |
| VIEWS | information_schema | InnoDB |
| customers | crashcourse | InnoDB |
| orderitems | crashcourse | InnoDB |
| orders | crashcourse | InnoDB |
| products | crashcourse | InnoDB |
......
统计mydb数据库下Orders表的占用空间大小
SELECT
table_name,
(AVG_ROW_LENGTH + INDEX_LENGTH) / 1024 / 1024 AS data_mb
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = 'mydb'
AND TABLE_NAME = 'Orders';
运行结果
mysql> SELECT
-> table_name,
-> (AVG_ROW_LENGTH + INDEX_LENGTH) / 1024 / 1024 AS data_mb
-> FROM
-> information_schema.TABLES
-> WHERE
-> TABLE_SCHEMA = 'mydb'
-> AND TABLE_NAME = 'Orders';
+------------+------------+
| table_name | data_mb |
+------------+------------+
| Orders | 0.01874924 |
+------------+------------+
1 row in set (0.00 sec)
统计整个数据库的数据量
SELECT
SUM(AVG_ROW_LENGTH + INDEX_LENGTH) / 1024 / 1024 AS data_mb
FROM
information_schema.TABLES;
运行结果
mysql> SELECT
-> SUM(AVG_ROW_LENGTH + INDEX_LENGTH) / 1024 / 1024 AS data_mb
-> FROM
-> information_schema.TABLES;
+------------+
| data_mb |
+------------+
| 0.58588886 |
+------------+
1 row in set, 6 warnings (0.04 sec)
统计mydb数据库的数据量
SELECT
SUM(AVG_ROW_LENGTH + INDEX_LENGTH) / 1024 / 1024 AS data_mb
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = 'mydb';
运行结果
mysql> SELECT
-> SUM(AVG_ROW_LENGTH + INDEX_LENGTH) / 1024 / 1024 AS data_mb
-> FROM
-> information_schema.TABLES
-> WHERE
-> TABLE_SCHEMA = 'mydb';
+------------+
| data_mb |
+------------+
| 0.10520554 |
+------------+
1 row in set, 12 warnings (0.00 sec)
备份mydb库下的所有表到/tmp目录下
简单来说就是利用CONCAT()函数把备份语句拼接出来，存放到一个脚本文件中，然后一键执行就可以了
SELECT
CONCAT('mysqldump ',
' ',
table_schema,
' ',
table_name,
'> /tmp/',
table_schema,
'_',
table_name,
'.sql') AS backup
FROM
information_schema.tables
WHERE
table_schema = 'mydb';
运行结果
mysql> SELECT
-> CONCAT('mysqldump ',
-> ' ',
-> table_schema,
-> ' ',
-> table_name,
-> '> /tmp/',
-> table_schema,
-> '_',
-> table_name,
-> '.sql') as backup
-> FROM
-> information_schema.tables
-> WHERE
-> table_schema = 'mydb';
+------------------------------------------------------------------+
| backup |
+------------------------------------------------------------------+
| mysqldump mydb Customers> /tmp/mydb_Customers.sql |
| mysqldump mydb OrderItems> /tmp/mydb_OrderItems.sql |
| mysqldump mydb Orders> /tmp/mydb_Orders.sql |
| mysqldump mydb Products> /tmp/mydb_Products.sql |
| mysqldump mydb Productustomers> /tmp/mydb_Productustomers.sql |
| mysqldump mydb Vendors> /tmp/mydb_Vendors.sql |
| mysqldump mydb t1> /tmp/mydb_t1.sql |
+------------------------------------------------------------------+
7 rows in set (0.00 sec)
这样语句就生成了，接下来保存为文件就可以了，
SELECT
CONCAT('mysqldump ',
' ',
table_schema,
' ',
table_name,
'> /tmp/',
table_schema,
'_',
table_name,
'.sql') AS backup
FROM
information_schema.tables
WHERE
table_schema = 'mydb' INTO OUTFILE '/tmp/backup.sh';
运行发现会报错
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
需要给个权限，允许写入到别的目录，编辑配置文件/etc/my.cnf,添加下面一项
secure-file-priv=/tmp
重启数据库,重新执行就可以了
[root@localhost ~]# cat /tmp/backup.sh
mysqldump mydb Customers> /tmp/mydb_Customers.sql
mysqldump mydb OrderItems> /tmp/mydb_OrderItems.sql
mysqldump mydb Orders> /tmp/mydb_Orders.sql
mysqldump mydb Products> /tmp/mydb_Products.sql
mysqldump mydb Productustomers> /tmp/mydb_Productustomers.sql
mysqldump mydb Vendors> /tmp/mydb_Vendors.sql
mysqldump mydb t1> /tmp/mydb_t1.sql
运行一下文件就可以备份了
[root@localhost ~]# sh /tmp/backup.sh
[root@localhost ~]# ls -l /tmp/my
mydb_Customers.sql mydb_Products.sql mydb_Vendors.sql
mydb_OrderItems.sql mydb_Productustomers.sql mysql.sock
mydb_Orders.sql mydb_t1.sql mysql.sock.lock
[root@localhost ~]# ls -l /tmp/mydb*
-rw-r--r--. 1 root root 2688 Jan 17 15:22 /tmp/mydb_Customers.sql
-rw-r--r--. 1 root root 2727 Jan 17 15:22 /tmp/mydb_OrderItems.sql
-rw-r--r--. 1 root root 2245 Jan 17 15:22 /tmp/mydb_Orders.sql
-rw-r--r--. 1 root root 2908 Jan 17 15:22 /tmp/mydb_Products.sql
-rw-r--r--. 1 root root 2729 Jan 17 15:22 /tmp/mydb_Productustomers.sql
-rw-r--r--. 1 root root 1740 Jan 17 15:22 /tmp/mydb_t1.sql
-rw-r--r--. 1 root root 2505 Jan 17 15:22 /tmp/mydb_Vendors.sql
给mydb数据库下的每个表创建一个bak表
SELECT
CONCAT('create table ',
table_schema,
'.',
table_name,
'_bak like ',
table_schema,
'.',
table_name,
';')
FROM
information_schema.tables
WHERE
table_schema = 'mydb' INTO OUTFILE '/tmp/create.sql';
运行结果
mysql> SELECT
-> CONCAT('create table ',
-> table_schema,
-> '.',
-> table_name,
-> '_bak like ',
-> table_schema,
-> '.',
-> table_name,
-> ';')
-> FROM
-> information_schema.tables
-> WHERE
-> table_schema = 'mydb' INTO OUTFILE '/tmp/create.sql';
Query OK, 7 rows affected (0.00 sec)
mysql> source /tmp/create.sql
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.03 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
--这个是我之前创建的一个视图，所以备份失败可以无视
ERROR 1347 (HY000): 'mydb.Productustomers' is not BASE TABLE
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
mysql> SHOW TABLES FROM mydb;
+-----------------+
| Tables_in_mydb |
+-----------------+
| Customers |
| Customers_bak |
| OrderItems |
| OrderItems_bak |
| Orders |
| Orders_bak |
| Products |
| Products_bak |
| Productustomers |
| Vendors |
| Vendors_bak |
| t1 |
| t1_bak |
+-----------------+
13 rows in set (0.00 sec)
319
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
