If you use the InnoDB storage engine for (some of) your MySQL tables, you’ve probably already came across a problem with its default configuration. As you may have noticed in your MySQL’s data directory (in Debian/Ubuntu – /var/lib/mysql) lies a file called ‘ibdata1′. It holds almost all the InnoDB data (it’s not a transaction log) of the MySQL instance and could get quite big. By default this file has a initial size of 10Mb and it automatically extends. Unfortunately, by design InnoDB data files cannot shrinked. That’s why DELETEs, TRUNCATEs, DROPs, etc. can’t will not reclaim the space used by the file. Instead any freed regions are marked as unused and can be used later. Theoretically speaking the file could reach the maximum size allowed by the filesystem if no limit is set in the my.cnf file (in Debian/Ubuntu it’s located in /etc/mysql/my.cnf). Guess what ? It’s not set by default. However you can configure your InnoDB engine as described MySQL’s Reference Manual. Additionally you can force the server to create an *.ibd for each newly created InnoDB table by using the ‘innodb_file_per_table‘ option (quite intuitive, huh ?
).
As mentioned above, you cannot shrink InnoDB data files. Additionally, you cannot make much changes in the settings of a InnoDB data file. So if you haven’t configured InnoDB properly right after the installation, you’ll probably have a pretty large ibdata1 file. There are three ways to reclaim your free space, but before doing so backup your whole MySQL data directory… just in case. And don’t forget to stop any services using MySQL databases.
In order to use the first two methods you should have a list of all InnoDB tables in your MySQL instance. You can easily create one if your MySQL version is 5.0+ by using the special database called ‘INFORMATION SCHEMA‘. Just invoke this query:
SELECT `TABLE_SCHEMA`,`TABLE_NAME` FROM TABLES WHERE ENGINE = ‘InnoDB’;
Changing Table Engines
1. Invoke ‘ALTER TABLE `table_name` ENGINE=MyISAM‘ for each InnoDB table;
2. Stop the MySQL server;
3. Remove InnoDB data files;
4. Make the appropriate changes in your my.cnf;
5. Start the server again;
6. Invoke ‘ALTER TABLE `table_name` ENGINE=InnoDB‘ for those tables again;
Note: Any foreign key information is lost when changing the engine to MyISAM. You should save the output of ‘SHOW CREATE TABLE `table_name`‘ for each of those tables and recreate the foreign keys manually. So, that method sucks !
Dump InnoDB Tables
1. Use mysqldump to dump all InnoDB tables, for example:
mysqldump ––add-drop-table ––extended-insert ––disable-keys ––quick ‘db_name’ ––tables ‘tbl_name’ > ‘db_name.tbl_name.sql’
2. Drop those tables using:
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE db_name.tbl_name;
DROP TABLE db_name1.tbl_name1;
–– DROP other tables here…
SET FOREIGN_KEY_CHECKS=1;
3. Stop the MySQL server;
4. Remove InnoDB data files;
5. Make the appropriate changes in my.cnf;
6. Start the MySQL server;
7. Re-import the tables. You’d better get into the ‘mysql’ console and issue the following commands:
SET FOREIGN_KEY_CHECKS=0;
SOURCE db_name.tbl_name.sql;
SOURCE db_name1.tbl_name1.sql;
–– SOURCE other files here…
SET FOREIGN_KEY_CHECKS=1;
Note: This method is quite tedious too as you have to keep track on all tables and all dumped files… Yeah, I don’t like it either.
Dump the Whole Database
In fact, this is the method I used to solve the problem. It requires much more space and time but it’s maybe the easiest one. So here it is:
1. Dump all databases by calling:
/usr/bin/mysqldump ––extended-insert ––all-databases ––add-drop-database ––disable-keys ––flush-privileges ––quick ––routines ––triggers > all-databases.sql
2. Stop the MySQL server;
3. Rename or remove (in case you’ve already backed it up) the MySQL data directory and create an empty one with the same name and permissions;
4. Make the appropriate changes in my.cnf;
5. Re-initialize the database with the following command (replace the ‘mysqld‘ with the login of the user your MySQL server runs as) (10x, Påven):
sudo -u mysqld mysql_install_db
6. Start the MySQL server;
7. Get into the ‘mysql‘ console and type:
SET FOREIGN_KEY_CHECKS=0;
SOURCE all-databases.sql;
SET FOREIGN_KEY_CHECKS=1;
8. Restart the MySQL server. (10x, czaby)
At this point everything should be fine and you can test it by starting again the services that use MySQL. If not…
Troubleshoot Me
1. Stop the MySQL server;
2. Remove its data directory and put the backed up one (you’ve made a backup as mentioned above, haven’t you ?);
3. Start the MySQL server again;
4. Let me know what went wrong;
So… that’s it for today. Have fun !
分享到:
相关推荐
Copysets Reducing the Frequency of Data Loss in Cloud Storage,Copysets Reducing the Frequency of Data Loss in Cloud Storage
语言:English提供指向项目和管理区域的快速链接。这是针对具有管理员权限并访问许多不同的Jira项目的用户。Jira Ninja面向需要定期在多个项目之间切换的管理员和高级用户。扩展程序的建立是由于在Jira上浏览的无奈而...
Batch Normalization: Accelerating Deep Network Training by Reducing Internal Covariate Shift
提供项目和管理区域的快速链接。这是针对管理和访问许多不同Jira项目的用户。 Jira Ninja面向需要定期在多个项目之间切换的管理员和高级用户。 扩展程序的建立是由于在Jira上浏览的无奈而造成的。...
一篇发表在USENIX Security'15会议上的论文LinkDroid: Reducing Unregulated Aggregation of App Usage Behaviors。这篇文章对APP之间通过信息链接造成的隐私泄漏问题进行了研究,并提出了保护方案 。 Abstract & ...
MySQL Stored Procedure Programming Advance Praise for MySQL Stored Procedure Programming Preface Objectives of This Book Structure of This Book What This Book Does Not Cover Conventions ...
cache Improving Average Memory Access Time: Reducing Hit Time
批量规范化对加速和改进深部模型的训练是非常有效的。但是,当训练小批量或不包含独立样本时,其有效性会降低。我们假设这是由于模型层对小批量中所有示例的依赖性,以及在训练和推理之间产生的不同激活。...
18th Jolt获奖书籍,经典的说………………
Batch Normalization: Accelerating Deep Network Training by Reducing Internal Covariate Shift
Reducing the Dimensionality of Data with Neural Networks
Reducing the dimensionality of data with neural networks.pdf
2024 MCM Problems Problem A: Resource Availability and Sex Ratios Problem B: Searching for Submersibles Problem C: Momentum in Tennis Problem C: Data ...Problem F: Reducing Illegal Wildlife Trade
Martin Fowler 的一篇文章,面向对象设计方面的
Hinton reducing the dimensionality of data with neural networks
论文+代码实现方法 论文详细 Karl Krissian,Oriented Speckle Reducing Anisotropic Diffusion,IEEE TRANS 2007
论文+代码实现方法 论文详细 Karl Krissian,Oriented Speckle Reducing Anisotropic Diffusion,IEEE TRANS 2007
用RBF降维,还可以实现分类 是HIINTON的新作
代码
作者:Hinton, GE (Hinton, G. E.); Salakhutdinov, RR (Salakhutdinov, R. R.) SCIENCE 卷: 313 期: 5786 页: 504-507 DOI: 10.1126/science.1127647 出版年: JUL 28 2006