mysql数据库自动换表储存数据_mysql表ibdata1数据切换到单个表储存、默认导出的表结构...

释放MySQL ibdata1文件的空间

mysqldump选项：

--add-drop-table：在每个创建数据库表语句前添加删除数据库表的语句；

--add-locks：备份数据库表时锁定数据库表；

--all-databases：备份MySQL服务器上的所有数据库；

--comments：添加注释信息；

--compact：压缩模式，产生更少的输出；

--complete-insert：输出完成的插入语句；

--databases：指定要备份的数据库；

--default-character-set：指定默认字符集；

--force：当出现错误时仍然继续备份操作；

--host：指定要备份数据库的服务器；

--lock-tables：备份前，锁定所有数据库表；

--no-create-db：禁止生成创建数据库语句；

--no-create-info：禁止生成创建数据库库表语句；

--password：连接MySQL服务器的密码；

--port：MySQL服务器的端口号；

--user：连接MySQL服务器的用户名。

mysqldump默认导出的表结构如下：

mysqldump  -uroot -p-d  -B testdb >testdb.sql

DROP TABLE IF EXISTS `test`;

CREATE TABLE `test` (

`id` int(11) NOTNULL AUTO_INCREMENT,

`name` varchar(5)DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

在MySQL数据库中，如果不指定innodb_file_per_table参数，单独存在每个表的数据，MySQL的数据都会存放在ibdata1文件。mysql ibdata1存放数据，索引等，是MYSQL的最主要的数据。

步骤：

1，备份数据库

从命令行进入MySQL Server 5.1/bin备份全部数据库，执行命令mysqldump -q -uusername -pyourpassword --add-drop-table --all-databases > /backup/all.sql

删除数据库drop database db1;drop database db2;

此处不删除应用数据库，在全库恢复数据库时，就会报异常错误，一些表的*.ibd会报不存在。

解决方式：

move db1 /backup/db1_bak

move db2 /backup/db2_bak

做完此步后，然后停止数据库。

2，修改mysql配置文件

修改my.cnf文件，增加下面配置

innodb_file_per_table

对每张表使用单独的innoDB文件, 修改/etc/my.cnf文件

3，删除原数据文件

删除原来的ibdata1文件及日志文件ib_logfile*，删除data目录下的应用数据库文件夹(mysql,test,information_schema数据库本身文件夹不要删除)

4，还原数据库

启动数据库服务

从命令行进入MySQL Server 5.1/bin还原全部数据库，执行命令mysql -uusername -pyourpassword < /backup/*.sql

大公告成。

经过以上几步后，可以看到新的ibdata1文件就只有几十M了，数据及索引都变成了针对单个表的小ibd文件了，它们在相应数据库的文件夹下面。