Mysql数据库优化备份恢复

数据库空间清理

 

 

删除操作会带来一些数据碎片，正是这些碎片在占用硬盘空间

MySQL的 information_schema 数据库中的TABLES 表记录了MySQL数据库中每个表占用的空间、表记录的行数，更新时间，说明等，这个表主要字段如下：

TABLE_SCHEMA : 数据库名

TABLE_NAME：表名

ENGINE：所使用的存储引擎

TABLES_ROWS：记录数，即表的行数

DATA_LENGTH：数据大小

INDEX_LENGTH：索引大小

CREATE_TIME：创建时间

UPDATE_TIME：最近更新时间

DATA_FREE：该参数与mysql碎片有关，如果是共享表空间，该字段表示共享表空间的大小而非数据的大小。只有使用独占表空间时，该字段才表示该表的剩余空间；

说明：当MySQL从列表中删除一行内容，该段空间就会被留空。在一段时间内执行大量删除操作后，往往会使碎片空间变得比存储列表内容所使用的空间更大。

通俗的讲：Data_free字段即为多占的物理空间，通过‘show table status’可以查看指定表的Data_free字段，对应的值就是多占用的物理空间，drop表重建或是重新导入可以释放这部分空间。

 

1.查看数据库的表结构

USE information_schema;

SELECT table_name,table_rows FROM TABLES WHERE table_schema = 'school' ORDER BY table_rows DESC;

SELECT sum(table_rows) FROM tables WHERE table_schema = 'school';

SELECT table_name,table_rows FROM TABLES WHERE table_schema = 'school' ORDER BY table_rows DESC LIMIT 100;

select*from --查询say表

order by id desc --按id列大小降序排列

limit 100，15 --从查询的结果中取出一条数据，也就是从第100条开始取出15条数据

 

2.查询表占的空间大小

SELECT TABLE_NAME, (DATA_LENGTH+INDEX_LENGTH)/1048576, TABLE_ROWS FROM information_schema.tables WHERE TABLE_SCHEMA='dbname' AND TABLE_NAME='tablename（你的表名）';

 

 

打开Mysql命令行，输入数据库密码，进入要操作的数据库（命令行是：use databaseName;），然后运行optimize table tableName;

 

生产案例：释放表空间

 

思路1：

 

数据不删除：备份表，drop表，导入备份的表到数据库；

show table status like 'oldtb%';  -->重点关注Data_free字段，对应的值就是多占用的物理空间，即碎片空间

mysqldump -uroot -p123456  mydb oldtb >/mnt/oldtb.sql  -->导出可以恢复系统空间的表格。

drop table oldtb;                        -->drop旧表释放表空间

source /mnt/oldtb.sql;                -->导出备份的表到数据库。

show table status like 'oldtb%';  -->对比恢复前后的状态，重点关注Data_free字段

该操作通过多次单表操作，多次对表进行备份和恢复，从而释放了碎片空间，适用于不允许停机的业务。

如果系统业务允许在一段时间内停止服务，可以备份整个数据库，然后清空，重建mysql数据根目录，然后恢复整个数据库。

 

 

思路2 ：

 

删除部分旧数据：适用于可以删除部分旧数据的场景

CREATE TABLE IF NOT EXISTS `newtb` LIKE `course` ;  -->新建newtb表，表结构同旧表

insert into newtb select * from oldtb where time > 'xxx' and time<='xxx';

-->备份表的新数据，可以按时间段备份最近一个月或半个月

drop table oldtb;                                        -->drop旧表释放表空间

alter tables newtb rename to oldtb;           -->重命名新表“newdtb”

 

 

思路3：直接优化表：optimize table 表名;

 

select table_name,data_free,engine from information_schema.tables where table_schema='mydb';

-->查看库中各表data_free值，单位是字节，data_free/1024/1024/1024即为理论上释放后可以恢复空间G。

show table status like 'oldtb';                  -->单个表的data_free大小

mysql> OPTIMIZE TABLE  hellodb.students;

-->显示不支持，实际上已进行重建和分析，空间已经回收

 

 

 

 

数据库备份

1、备份命令

格式：cmd 下 mysqldump -h主机名 -P端口 -u用户名 -p密码 --database 数据库名 > d:\文件名.sql

例如： mysqldump -h 192.168.1.100 -p 3306 -uroot -ppassword --database cmdb > /data/backup/cmdb.sql;

 

2.恢复数据

use 数据库；

source d:\mysql.sql