mySQL常用语句

1.表空间使用情况

USE information_schema;

-- 每个表占用空间

SELECT TABLE_NAME,DATA_LENGTH+INDEX_LENGTH AS total_size,TABLE_ROWS FROM TABLES WHERE TABLE_SCHEMA='cloudcc_release' ORDER BY total_size DESC;

-- 所有表数据占用空间

SELECT CONCAT(ROUND(SUM(DATA_LENGTH/1024/1024), 2), 'MB') AS DATA FROM TABLES;

2.数据库备份

mysqldump  --single-transaction -R  --ignore-table=cloudcc_release.t_history   -ucloudcc -pcloudcc cloudcc_release|gzip  > /tmp/db_bak_everyday/cloudcc_release_db_`date +%Y%m%d-%T`.sql.gz

恢复的时刻关闭二进制日志
mysql>set sql_log_bin=0;

mysql>source xx.sql;