Mysql常备sql-1（数据库数据相关）

一、查看各个数据库的大小
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;

二、查看指定数据库的大小
select table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',truncate(data_length/1024/1024, 2) as '数据容量(MB)',truncate(index_length/1024/1024, 2)
as '索引容量(MB)'from information_schema.tables where table_schema='db_prod' order by data_length desc, index_length desc;

三、用mysqldump导出数据：
下面的sql分别是导出表和表里指定条件的数据，用&&连接后一次性执行，echo是为了加上库名，按表导出默认是不加的
echo 'use edu_health_org;' > export_$(date +%Y%m%d).sql &&
mysqldump -h 192.168.70.1 -P 3306 -udevops -p123456 --skip-add-locks edu_health_org sys_equipment_model sys_region >> export_$(date +%Y%m%d).sql &&
echo 'use edu_health_auth_v3;' >> export_$(date +%Y%m%d).sql &&
mysqldump -h 192.168.70.1 -P 3306 -udevops -p123456 --skip-add-locks edu_health_auth_v3 sys_role --where="id=1" >> export_$(date +%Y%m%d).sql && \