MySQL InnoDB表空间重新释放实战

实战：

------备份DB

mysqldump -quick  --database ixxx> ixxx.sql

 

------db所在的目录情况

[root@localhost mysql]# ls -all
总计 64422864
drwxr-xr-x 3 mysql mysql        4096 09-08 23:21 .
drwxr-xr-x 4 root  root         4096 06-02 22:08 ..
-rw-r----- 1 mysql mysql 64938311680 09-08 23:21 ibdata1
-rw-r----- 1 mysql mysql   268435456 09-08 23:21 ib_logfile0
-rw-r----- 1 mysql mysql   268435456 09-08 21:05 ib_logfile1
-rw-r----- 1 mysql mysql   268435456 09-08 23:18 ib_logfile2
-rw-rw---- 1 mysql mysql         403 09-08 23:21 localhost.localdomain.err
-rw-rw---- 1 mysql mysql       30362 09-08 19:41 localhost.localdomain.err-old
-rw-rw---- 1 mysql mysql    23292032 09-08 23:14 localhost-slow.log
drwx--x--x 2 mysql mysql        4096 05-19 01:50 mysql
-rw-rw---- 1 mysql mysql   137589360 09-08 23:21 mysql-bin.000001
-rw-rw---- 1 mysql mysql          19 09-08 19:42 mysql-bin.index

 

 

--------所有的db：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ixxx             |
| mysql              |
+--------------------+
3 rows in set (0.00 sec)

 

-------删除掉占用空间的

drop database ixxx;

 

quit;

 

------停止mysql

[root@localhost mysql]# service mysql stop
Shutting down MySQL..                                      [确定]

------删除innodb相关文件

[root@localhost mysql]# ls -all
总计 64422864
drwxr-xr-x 3 mysql mysql        4096 09-08 23:21 .
drwxr-xr-x 4 root  root         4096 06-02 22:08 ..
-rw-r----- 1 mysql mysql 64938311680 09-08 23:21 ibdata1
-rw-r----- 1 mysql mysql   268435456 09-08 23:21 ib_logfile0
-rw-r----- 1 mysql mysql   268435456 09-08 21:05 ib_logfile1
-rw-r----- 1 mysql mysql   268435456 09-08 23:18 ib_logfile2
-rw-rw---- 1 mysql mysql         403 09-08 23:21 localhost.localdomain.err
-rw-rw---- 1 mysql mysql       30362 09-08 19:41 localhost.localdomain.err-old
-rw-rw---- 1 mysql mysql    23292032 09-08 23:14 localhost-slow.log
drwx--x--x 2 mysql mysql        4096 05-19 01:50 mysql
-rw-rw---- 1 mysql mysql   137589360 09-08 23:21 mysql-bin.000001
-rw-rw---- 1 mysql mysql          19 09-08 19:42 mysql-bin.index
[root@localhost mysql]# rm -rf ibdata1

[root@localhost mysql]# rm -rf ib_logfile0
[root@localhost mysql]# rm -rf ib_logfile1
[root@localhost mysql]# rm -rf ib_logfile2
[root@localhost mysql]# ls -all
总计 157332
drwxr-xr-x 3 mysql mysql      4096 09-08 23:22 .
drwxr-xr-x 4 root  root       4096 06-02 22:08 ..
-rw-rw---- 1 mysql mysql       403 09-08 23:21 localhost.localdomain.err
-rw-rw---- 1 mysql mysql     30362 09-08 19:41 localhost.localdomain.err-old
-rw-rw---- 1 mysql mysql  23292032 09-08 23:14 localhost-slow.log
drwx--x--x 2 mysql mysql      4096 05-19 01:50 mysql
-rw-rw---- 1 mysql mysql 137589360 09-08 23:21 mysql-bin.000001
-rw-rw---- 1 mysql mysql        19 09-08 19:42 mysql-bin.index

-----启动mysql

[root@localhost mysql]# service mysql start
Starting MySQL....................                         [确定]

此时文件重新生成了：

[root@localhost mysql]# ls -all
总计 954808
drwxr-xr-x 3 mysql mysql      4096 09-08 23:23 .
drwxr-xr-x 4 root  root       4096 06-02 22:08 ..
-rw-rw---- 1 mysql mysql  10485760 09-08 23:23 ibdata1
-rw-rw---- 1 mysql mysql 268435456 09-08 23:23 ib_logfile0
-rw-rw---- 1 mysql mysql 268435456 09-08 23:23 ib_logfile1
-rw-rw---- 1 mysql mysql 268435456 09-08 23:23 ib_logfile2
-rw-rw---- 1 mysql mysql      1912 09-08 23:23 localhost.localdomain.err
-rw-rw---- 1 mysql mysql     30362 09-08 19:41 localhost.localdomain.err-old
-rw-rw---- 1 mysql mysql         5 09-08 23:23 localhost.localdomain.pid
-rw-rw---- 1 mysql mysql  23292220 09-08 23:23 localhost-slow.log
drwx--x--x 2 mysql mysql      4096 05-19 01:50 mysql
-rw-rw---- 1 mysql mysql 137589360 09-08 23:21 mysql-bin.000001
-rw-rw---- 1 mysql mysql       106 09-08 23:23 mysql-bin.000002
-rw-rw---- 1 mysql mysql        38 09-08 23:23 mysql-bin.index
srwxrwxrwx 1 mysql mysql         0 09-08 23:23 mysql.sock

 

 

---进入查询下mysql是否正常

 

[root@localhost mysql]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.45-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
2 rows in set (0.00 sec)

mysql> quit
Bye

 

--------建库、重新导入

CREATE DATABASE `ixxx` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

 

mysql ixxx<ixxx.sql