数据库表大小查询

数据库表大小查询

SELECT 
    TABLE_NAME,
    TABLE_SCHEMA,
    TABLE_ROWS,
    DATA_LENGTH,
    INDEX_LENGTH,
    (DATA_LENGTH + INDEX_LENGTH) AS TOTAL_SIZE,
    CONCAT(ROUND(SUM(data_length + index_length) / (1024*1024), 2), ' MB') AS '表大小(MB)',
    CONCAT(ROUND(SUM(data_length + index_length) / (1024*1024*1024), 2), ' GB') AS '表大小(GB)'
FROM 
    information_schema.TABLES 
WHERE 
    TABLE_SCHEMA = 'DBNAME'  -- 替换为目标数据库
GROUP BY 
    table_schema, table_name 
-- HAVING TOTAL_SIZE < 1024 * 1024 * 100   -- 小于100MB
ORDER BY 
    SUM(data_length + index_length) DESC;   -- 降序排序

mysqldump导出数据库

#导出指定数据库，但是忽略指定表
mysqldump -uroot -p123456 DBNAME \
  --ignore-table=dbname.table1 \
  --ignore-table=dbname.table2 \
  --ignore-table=dbname.table3 \
  > DBNAME.sql

导入数据库

#1.单条命令
mysql -uroot -p123456 DBNAME < DBNAME.sql

#2.交互式
mysql -uroot -p123456
USE DBNAME;
SOURCE /path/to/DBNAME.sql;