数据库排查操作

查看数据库下哪个表比较大

SELECT TABLE_NAME,ROUND(SUM(data_length/1024/1024),2) AS data_size , ROUND(SUM(INDEX_LENGTH/1024/1024),2) AS index_size 
FROM information_schema.TABLES WHERE TABLE_SCHEMA='cloud_houseorder'
GROUP BY TABLE_NAME
ORDER BY data_size DESC

查看数据库连接数

SHOW PROCESSLIST

查看数据库大小

SELECT
table_schema AS '数据库',
SUM(table_rows) AS '记录数',
SUM(TRUNCATE(data_length/1024/1024, 2)) AS '数据容量(MB)',
SUM(TRUNCATE(index_length/1024/1024, 2)) AS '索引容量(MB)'
FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length) DESC, SUM(index_length) DESC;

数据库回档快速恢复

回档 houseorder 数据库，回档后会有 houseorder_bak 数据库，需要把 houseorder_bak 的数据，转移到 houseorder 里面。
1、执行语句，拼接出需要执行的sql语句。

SELECT CONCAT('RENAME TABLE houseorder_bak.',table_name,' To houseorder.',table_name,';')
FROM INFORMATION_SCHEMA.tables
WHERE table_schema='houseorder_bak';

2、执行所有拼接出sql语句，即可。

RENAME TABLE houseorder_bak.t_a_backtoken TO houseorder.t_a_backtoken;