查看数据库文件大小
SELECT
file_name,
concat(TOTAL_EXTENTS, 'M') as 'FIle_size'
FROM
INFORMATION_SCHEMA.FILES
order by
TOTAL_EXTENTS DESC;
查看所有数据库容量大小
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
order by data_length desc, index_length desc;
查看指定数据库容量大小
例:查看库容量大小
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
where table_schema='db_name';
查看指定数据库各表容量大小
例:查看库各表容量大小
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_name'
order by data_length desc, index_length desc;
表数量对比
SELECT
TABLE_SCHEMA,
count(*)
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema')
GROUP BY TABLE_SCHEMA
ORDER BY 2 DESC;
触发器对比
SELECT
TRIGGER_SCHEMA,
count(*)
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema')
GROUP BY TRIGGER_SCHEMA
ORDER BY 2 DESC;
字符集对比
SELECT SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA;
表中数据量对比
查询对比2917-07-08日之前表的数据量看两库是否一致。
select 'mobile_card.wepay_wallet_record' `name`, count(*) CNT from mobile_card.wepay_wallet_record where create_time <'2017-07-08' union all
select 'mobile_card.mcc_wescore_record' `name`, count(*) CNT from mobile_card.mcc_wescore_record where id <100712118 union all
select 'mobile_card.mcc_customer_grant' `name`, count(*) CNT from mobile_card.mcc_customer_grant where id <39691660 union all
select 'mobile_card.mcc_credit_score' `name`, count(*) CNT from mobile_card.mcc_credit_score where id <35485079 union all
select 'mobile_card.mcc_consume_repayment' `name`, count(*) CNT from mobile_card.mcc_consume_repayment where id <29256981 union all
select 'mobile_card.mcc_customer' `name`, count(*) CNT from mobile_card.mcc_customer where id <23204782 union all
select 'mobile_card.mcc_repayment_record' `name`, count(*) CNT from mobile_card.mcc_repayment_record where id <26479142 union all
select 'mobile_card.mcc_credit_card' `name`, count(*) CNT from mobile_card.mcc_credit_card where id <15473828 union all
select 'mobile_card.mcc_customer_card' `name`, count(*) CNT from mobile_card.mcc_customer_card where id <3509303 ;
创建数据库命令
CREATE DATABASE IF NOT EXISTS db_name DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
GRANT ALL PRIVILEGES ON *.* TO 'app_rw'@'%' IDENTIFIED BY PASSWORD ""