Valery's Mlog

Mindlog of a Freak
February 22nd, 2007 by Valery Dachev

MySQL: Reducing ibdata1

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 an initial size of 10Mb and it automatically extends. Unfortunately, InnoDB data files cannot be shrinked and that’s by design. 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, and for in Mac OS take a look at the footnotes). Guess what! It’s not set by default. However you can configure your InnoDB engine as described in Section “14.2.1 Configuring InnoDB of the MySQL 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 ? :)). You can take a look at the Section “ InnoDB File-Per-Table Tablespaces of the MySQL Reference Manual for more details.

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:


Changing Table Engines

  1. Invoke the following query on each InnoDB table:
    ALTER TABLE `table_name` ENGINE=MyISAM;
  2. Stop the MySQL server;
  3. Remove InnoDB data files;
  4. Add the innodb_file_per_table option to your my.cnf;
  5. Start the server again;
  6. Invoke the following query for those tables again:
    ALTER TABLE `table_name` ENGINE=InnoDB;

Note: Any foreign key information is lost when changing the engine to MyISAM. You should execute the following query:
SHOW CREATE TABLE `table_name`
for each of those tables, save the output 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:
    DROP TABLE db_name.tbl_name;
    DROP TABLE db_name1.tbl_name1;
    -- DROP other tables here...
  3. Stop the MySQL server;
  4. Remove InnoDB data files;
  5. Add the innodb_file_per_table option to your 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:
    SOURCE db_name.tbl_name.sql;
    SOURCE db_name1.tbl_name1.sql;
    -- SOURCE other files here...

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:
    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. Add the innodb_file_per_table option to your 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:
    SOURCE 'all-databases.sql';
  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 ! :)

P.S. If you’re using Mac OS X and you have installed MySQL using Homebrew, note that the installation does not provide a my.cnf file and the MySQL uses the default settings. However, you could create one in one of the following locations:

  • /etc/my.cnf
  • /etc/mysql/my.cnf
  • /usr/local/etc/my.cnf
  • ~/.my.cnf

You could start by using one of the templates located in /usr/local/opt/mysql/support-files/. See the related post on Cameron Spear’s blog.


98 Responses to “MySQL: Reducing ibdata1”
  1. Thanks for the tutorial! Using the “Dump the whole db” method reduced my 20+ GB innodb1 file to less than 4GB of data.

    Best regards,

  2. I used the “Dump the whole db” method. It reduced the size of my ibdata1 file from 78GB to 35GB.

    Your tutorial was great: detailed, carefully written, and complete.

  3. Note that in the “Dump the whole db” method, the syntax is slightly off — all options should be prepended by double-dash “–” so:

    /usr/bin/mysqldump –-extended-insert –-all-databases –-add-drop-database –-disable-keys –-flush-privileges –-quick –-routines –-triggers > all-databases.sql

  4. @Greg, @Matt: Thanks for your great feedback! :)

    @Bret: Thank you for pointing out that mistake. In a matter of fact it was a HTML, browser or WordPress issue – it, for some reason, displays a long dash instead of double-dash. I replaced the double dashes with double – entities.

  5. Thanks for the tutorial! It was great! The disk usage was 73% before I dumped all the databases. Now it’s 38%.

    Unfortunately I backed up only ibdata1 file instead of complete folder and got a little bit nervous when realized that it was not enough ;-). But anyway, everything is OK and I would like to thank you for the perfect article.

    I would like to ask you to make a strict definition that one must make backup of THE DATA FOLDER and not the ibdata1. It’s written correctly in the article, but I think it will be better to somehow highlight it for such dummies like me.

    Best regards,

  6. @Nugzar: Thank you for being so kind! I’ve just emphasized the backup advice. :) Have fun!

  7. Great tutor, it’s method save my God Damnt databases…. It’s So Big.. and make me crazy when electric down… Thanks dude!!!

  8. […] I choose option 2 because I lucklly had only 40 InnoDB tables and much more using MyISAM. For details on how to apply each solution and down/up sides of each read MySQL: Reducing ibdata1. […]

  9. Daniel Serodio says

    Thanks, you helped me free some much-needed space in my /var partition.

  10. Sweet!
    This is great. I had a bit of confusion when I reinstalled the DB – there were no permissions for the ‘debian-sys-maint’ user so, at first, I couldn’t restart MySQL. GRANTed privileges for this user, using the password in /etc/mysql/debian.cnf and then everything went smoothly.

  11. Great tutorial, thanks a lot.
    Some minor comments:

    1. for the mysqldump command ––all-databases is the only necessary parameter, all others are default on (5.4)
    2. I would add “after restoring from all-databases.sql, restart the mysqld once more to see the users and permissions” or something like that
    3. If your article has no other affect just that some admins now will have a backup of their database, you have improved their life quality significantly :)

  12. @Harisanto, @Daniel Serodio: I’m glad it helped! :)
    @Luke: I guess Debian did not changed the mysql_install_db script so that they add the debian-sys-maint user and grant it the required permissions. You could alternatively restart the database using the root user and the mysqladmin tool.
    @czaby: Thank you for your comment! Yep, the most of the options are already enabled in the default my.cnf‘s but this may not be true if it was modified. So I’ve left them here just in case. :) I have added the additional punkt to the “Dump the Whole Database” instruction though. Thank you! :)

  13. Påven says

    Thanks for a great tutorial.

    Just wanted to point out that you might need to change ownership of the created databases after running ‘mysql_install_db‘.

  14. @Påven: I am sorry for the late comment approval and response. You are right about the database ownership and I have changed the tutorial accordingly. Thanks!

  15. Dustin says

    Followed the steps from method “Dump the Whole Database” on two MySQL instances (TEST and PROD) and it worked without a glitch. Thanks a lot for this tutorial!

  16. @Dustin: I am more than happy to hear that! :)

  17. Vincent Langlard says

    Apparently the path for my ubuntu is var/run/mysqld/ for finding the innodb files…

  18. Guess I should have been more careful. Everything worked like a charm up until that not-so-unimportant part where you use sudo. There’s where the story ends. The Win version has no such file.

  19. @OMFG: Well… the Windows version doesn’t need it. You should run the command (prompt) with administrative privileges.

  20. Thanks for the quick reply. What prompt do you mean? Do you mean the one given by MYSQL.EXE? At this point I should have the MySQL service turned off so I can’t run MYSQL.EXE. Can I turn the service on and run MYSQL.EXE and then enter “mysql_install_db” ?

  21. Okay. You must have meant MYSQLD.EXE, it just confused me that you said “(replace the ‘mysqld‘ with the login of the user your MySQL server runs as)”.

  22. Thanks! Finally made it work. Thank you for the help!

  23. @OMFG
    I am glad it worked! :) I really should amend this tutorial so that it works under Windows.

  24. Valery, I’ve got a server where the ibdata files have taken up all the free space. I’d like to follow ANY of your methods, but I’ve got no extra space to work with. Have you got any suggestions for how to move some of the content off the server, perhaps?

  25. Thanks Valery, I managed to follow the steps and it worked like a charm!

  26. @Jonathan Puddle I am sorry for being unable to respond earlier but I am glad my article really helped! :) In your case I suppose copying the database files to another (virtual ?) machine and performing the mention operations would help. And I guess that’s what you did, didn’t you? :)

  27. So this is good, but I don’t see how this fixes the real issue of the file growing too large. Is this just something that I need to put on the calendar every 6 months? Dumping the database and restoring it, the incurred downtime, it all sounds like a bandaid. Do I misunderstand? The only change that actually seems to be made is “Make the appropriate changes in my.cnf;”. Perhaps there is a change in there I am failing to recognize.

  28. @Zach
    The purpose of this article is to help you solve the problem of a large ibdata1. It’s MySQL AB (Oops, Oracle) who should find a solution to the problem of ibdata1 growing rapidly.

    The core problems here are this file doesn’t get reduced 1. when you delete data and/or 2. drop tables. However in the first paragraph you could find a solution to the latter problem – enable the innodb_file_per_table option.

  29. You are correct. I just missed that little bit. Thanks for the response.

  30. What is the difference between “innodb1” and “ibdata1”? In the first part of your post you talk about a file called “innodb1” and then it is not mentioned.

    Just want to make sure before I embark on this that I have the right file name.

    I’m working with a Win2008 Server if that makes any diference.


  31. Point taken. Thank you! :)

  32. So it is only ibdata1 I should be looking for?

    Thanks again

  33. @Carlos Arrien
    Yes, it is. :)

  34. […] design the InnoDB file does not shrink. The safest way to shrink this file is to take a complete backup, stop MySQL, remove the ib* files, start MySQL and restore […]

  35. […] design the InnoDB file does not shrink. The safest way to shrink this file is to take a complete backup, stop MySQL, remove the ib* files, start MySQL and restore […]

  36. Hi we have a whole lot going wrong here,

    heres the error log from the move …

    110826 13:58:41 [Note] Plugin ‘FEDERATED’ is disabled.
    C:\Program Files (x86)\MySQL\MySQL Server 5.1\bin\mysqld: Table ‘mysql.plugin’ doesn’t exist
    110826 13:58:41 [ERROR] Can’t open the mysql.plugin table. Please run mysql_upgrade to create it.
    110826 13:58:42 InnoDB: Error: cannot allocate 2147500032 bytes of
    InnoDB: memory with malloc! Total allocated memory
    InnoDB: by InnoDB 30993228 bytes. Operating system errno: 8
    InnoDB: Check if you should increase the swap file or
    InnoDB: ulimits of your operating system.
    InnoDB: On FreeBSD check you have compiled the OS with
    InnoDB: a big enough maximum process size.
    InnoDB: Note that in most 32-bit computers the process
    InnoDB: memory space is limited to 2 GB or 4 GB.
    InnoDB: We keep retrying the allocation for 60 seconds…
    InnoDB: Fatal error: cannot allocate the memory for the buffer pool
    110826 13:59:42 [ERROR] Plugin ‘InnoDB’ init function returned error.
    110826 13:59:42 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
    110826 13:59:42 [ERROR] Unknown/unsupported table type: INNODB
    110826 13:59:42 [ERROR] Aborting

  37. @Steven: It seems like you are running an older database with a newer version of MySQL. I think you should upgrade your database (probably using the mysql_upgrade script) and follow the instructions in the post. For some reason the InnoDB engine is trying to allocate a really huge amount of memory but your operating system is 32-bit and doesn’t support that.

    Please take a careful look at the error messages you get and try to resolve these issues. They are not related to what is described in this post.

  38. freddy says


  39. Hi,
    trying method #3 (drop all databases) for me this doesn’t work:

    mysqld mysql_install_db

    I tried this but got back an error:

    [[email protected] mysql]# /usr/bin/mysql_install_db –user=mysql

    FATAL ERROR: Could not find mysqld

    The following directories were searched:


    If you compiled from source, you need to run ‘make install’ to
    copy the software into the correct location ready for operation.

    If you are using a binary release, you must either be at the top
    level of the extracted archive, or pass the –basedir option
    pointing to that location.

    any ideas?!?

  40. @Gilas, I think there’s some problem with your MySQL installation as “mysql_install_db” doesn’t seem to be able to find the “mysqld” binary (most probably located in /usr/sbin). You could try re-installing the MySQL server package (make sure this doesn’t delete your data files) or even consider upgrading it to a newer version. If these doesn’t work, you could report a bug to the package developers as it seems to be a package issue.

  41. mysql_newbie says

    Hi Valery-
    Thanks for the article, this is most informative on this topic.

    I have one question: is mysql_install_db necessary? Can’t I remove ibdata1 file, start mysql and import the dump of all database including mysql db?

  42. @mysql_newbie, thank you! :)

    I suppose you are considering the “Dump the Whole Database” method. “mysql_install_db” is required in order to be able to log into the database and execute the queries in the database dump.

  43. Thanks for this tutorial but I wanted to get a little clarification before I run anything.

    first of all i will be dumping all databases on a shared server so there are quite a few.
    On step 5 of dump all databases tutorial
    1) if i see in the top process list that the user mysql is running the /usr/sbin/mysqld then i would enter mysql as the user correct?
    2) I am unclear as to the purpose of this step can you clarify it anymore?

    also when running the dump (step 1) what folder should we run this from? anything special or it doesnt really matter.

    it seems like step 7 is the one that re-imports the databases, but my question here is how does mysql know where the all-databases.sql file is located?

    thanks so much for your time and help!!!

  44. @John: Thank you for the nice words! :)

    I’d like to make it clear that MySQL itself uses the special “mysql” database to store information about any users, servers, privileges, etc. which makes it impossible for the MySQL server to operate if the “mysql” database is missing.

    In step 3 of the process the MySQL data directory is (re)moved and that’s why step 5 is required to re-initialize the “mysql” database. Otherwise you won’t be able to start the MySQL server and/or connect to it and execute any queries (the database import in particular). The username you see in the “USER” column of the “top” tool is just the username you should use in step 5 of the process.

    You can dump the database wherever you find appropriate. In the example the database is dumped in/restored from a file called “all-databases.sql” in the current working directory but feel free to replace it with any other relative or full path to a file that suits your needs.

    In step 7 it’s actually the “mysql” tool rather than the MySQL server that reads the file so it uses the current working directory as a relative path.

    I hope that helps. :)

  45. that helps a lot thanks!! and thanks for replying so fast.

    i have one more question, you say

    >>And don’t forget to stop any services using MySQL databases.

    would this include stopping apache (httpd)? this was suggested by my admins

    (along with stopping tailwatchd (this one makes sense yes) and crond ( why not)

    but stopping apache is a little more troublesome for downtime….

    any sense of how long it would take to do the whole process (i have a 2GB ibdata file) and 3GB in myisam databases.

    also do you think a 2GB ibdatafile would cause any performance issues?


  46. @John: Stopping any services that use MySQL is advised because they may change the data in the database in between the database dump and its restoration and these changes will be lost. Unfortunately you can’t get without any downtime in these solutions. Still a solution that involves database replication probably exists but it would be much more complicated and a topic for a whole new article.

    Sadly I can’t tell how long it would take to go through all these steps but your database seems to be relatively small, so I guess the whole process shouldn’t take more than 10 minutes. If you want to be really fast, you could setup a new (virtual?) machine, copy the MySQL database there and go through all the process until you reach perfection and then do it on the live machine. :)

    I believe a 2 GB ibdata1 file wouldn’t have any performance impact. I would me more concerned about possible corruption of the file that would make all the InnoDB tables really hard to recover.

  47. thanks!! I have already set the file per table setting and moved my larger innodb dbs to use that, so thanks for your advice and help and hopefully my questions help others that read in the future. perhaps mysql will get their act together to solve this issue (that they seem to call not a bug) sometime in the future.

    so would you still do the change on the concern of possible data corruption?

  48. Mike Nicewarner says

    I’m staying with the single default INNODB file, which is currently just about 100GB on a 1TB box. No space considerations.
    However, I have 10 databases in this single file, making up the 100GB, and would like to be able to break the file into 10 database files. Has anyone heard anything about a middle option between the single tablespace and per-table (even the option to use multiple files doesn’t put database tables together)?


  49. @Mike: Unfortunately I am not aware of such an option and I couldn’t find anything in the MySQL documentation and on the Internet… :(

  50. […] diese Einstellung für bestehende Tables übernommen werden soll, gibt es hier einen Workaround […]

  51. […] design the InnoDB file does not shrink. The safest way to shrink this file is to take a complete backup, stop MySQL, remove the ib* files, start MySQL and restore […]

  52. This was really helpful and helped me fix a DB I broke! Thanks.

  53. Great Job!
    This step by step works great for me!

    The only thing was (running the whole database procedure) that it takes like 19 hours to complete it….but well… we have a 84GB ibdata1 file.

    Just for the record, I ran this using the “innodb_file_per_table” on the my.cnf file which does break the files per table nicely.

    Again….thanks for this great help!!!

  54. Imagine Dimensions says

    Thanks for the write up. A few things, I did which maybe helpful to others. Flavor centos5.8
    For an updated my.cnf, I had used the default which is not a good long term solution as my innodb was ~48G, so I copied over my-large.cnf to /etc and added the innodb_file_per_table, along with a few other things taken from my original my.cnf

    Once your old data directory in my case /var/lib/mysql is removed or copied to a new location to recreate it and continue to allow everything to function properly I ran:

    mkdir -p /var/lib/mysql
    chown -R mysql.mysql /var/lib/mysql
    chcon -R -t mysqld_db_t /var/lib/mysql

    Once completed I ran the following to repopulate tables as mysql user: /usr/bin/mysql_install_db –user=mysql

    Everything was spot on. Thanks.

  55. Hi,

    thanks, that made my day …

    VPS was running out of space – then I realized that chossing innodb was not the best idea.


  56. Hi Valery,
    awesome post. I especially like the second. Dumping all databases.
    I really like the design of your blog and hey it is a cool image show you ;o)
    all the best,

  57. Hehe! I’m glad you liked it both the post and the image – a friend of mine ( made if for me.

  58. […] "After following this post to shrink a 30Gb innodb I am having problem s starting mysql. "service mysqld start" […]

  59. CodeWaggle says

    @Mike: regarding your question:
    “I have 10 databases in this single file, making up the 100GB, and would like to be able to break the file into 10 database files.”

    You can run a separate MySql instance for each database, you would use a different data directory for each one, so each would have a separate ibdata file. The main requirement for doing this is to assign a unique port and socket to each instance which can be done in a separate config file for each one.

    See “Running Multiple MySQL Instances on One Machine” in the docs:

  60. Great Tutorial Valery – and very much appreciated !.. Leaving more bread-crumbs for those that may have stumbled on certain areas for Windows MySQL55 installations.

    Window Server Experience and Trouble-Tips:

    A) Do *NOT* delete the \data\mysql or \data\performance_schema folders!! Only delete YOUR data folders. The only files one should have left in the \data folder are:

    \data\performance_schema (but this might be optional – I didn’t test if these were mandatory)

    B) Skip Step 5 entirely. It is totally irrelevant in Windows. “mysql_install_db” does *NOT* exist for Windows (lost 2hrs before I figured that one out).

    C) Instead of Step 5, just doing “net start MySQL55” (after changing your my.ini file) will cause the Win service to use the YOUR config file, and create the default files that it needs (which are the ones that mysql_install_db creates);

    D) On Windows, Step 7 did and did *NOT* work for me. Oh it all ‘ran’ perfect. Didn’t see any errors or anything. It made all the .idb files at various sizes, etc. However, in WorkBench while I did see my tables and everything looked great there were *NO* columns defined in the tables.

    MY WORKAROUND/SOLUTION: From WorkBench I dropped all my schemas that I could (MySQL complained) – so I turned off the Win service MySQL55, went to my \data folder and *WHACKED* MY folders containing everything! Then I restarted MySQL55 service, openned WorkBench and manually loaded and exected the former .sql file. This took a minute, but then everything was there just like new and everything works perfect!

    MY RESULTS: our entire data folder went from 2.6 Gigs to 17 Megs – haha – how about that for space-saving!

    Cheers and Thanks again!

  61. @Joe what do you mean *WHACKED* MY folders? Do you mean you deleted them? I’m on Win 2003 server/IIS 6. Thanks!

  62. Dhaval says

    I’m on Windows. This solution doesn’t seem to work for me. After having mysqldump’ed the whole database, I changed the my.ini to use a new empty directory. After that, MySQL server doesn’t start. It only starts if I make the original ibdata1 file available. Any suggestions would be helpful. Thanks.

  63. Steven says

    I have already break the InnoDB to single table for each file innodb_file_per_table=1.
    I backup the whole database. Drop ibdata1 and recreate it and restore back the database.

    There is each individual table files (.ibd) created.

    However I notice the ibdata1 continue to grow as well? Any idea why?

  64. @Steven: I guess you should check if there are any InnoDB tables without a corresponding .ibd file. Such tables keep using the main ibdata1 until dumped, dropped and re-created.

  65. Steven says

    All InnoDB tables have their own .ibd file. Still ibdata1 is still incresing.

  66. @Steven: I am not quite sure if there’s an easy way to check what exactly uses the ibdata1 file but you could try something like:

    strings /var/lib/mysql/ibdata1 | less

    and try to recognize the data and double-check the corresponding tables.

    Keep in mind that you can actually control the maximum size of the InnoDB data files by changing the “innodb_data_file_path” as described in Chapter of the Reference Manual.

  67. Ruel del Mundo says

    Great stuff!

    What happens if ibdata1 reaches the maximum size set in my.cnf? Will there be a problem?

  68. @Ruel del Mundo: Thank you! In fact, I haven’t checked that out. I guess an error will be issued or something. Otherwise setting a maximum size would be senseless.

  69. Trempest Humphries says

    Hi. I just joined a company as basic IT support and eventually made to take over a database that was created by the last person who got sacked. Now I find that I’m facing a problem with the database size and I’m really lost and at risk of losing my job. I really hope you could help me! Please contact me.

  70. @Trempest Humphries: This post addresses a specific issue with InnoDB tables on MySQL databases. Unfortunately, you haven’t provided any details in your comment. Please, do so, as I prefer other visitors to know how to address similar issues (if I am able to help at all).

  71. thank you so much for this post! big help!!

  72. Thanks for this guide! Worked perfectly on a box that had escaped files per table int he initial setup.

    Strangely, there’s still an issue with dashes in the original post. Here’s a corrected version (which defaults to asking for credentials too):

    mysqldump -u root -p –extended-insert –all-databases –add-drop-database –disable-keys –flush-privileges –quick –routines –triggers

  73. @Brad Web, @Andrew Berry: I’m glad the article helped solve your issues! :)
    @Andrew Berry: I think I fixed the dashes issues. WordPress seems to replace some symbols and HTML entities when not put in the appropriate HTML element (in this case – <code>).

  74. Hi Valery,
    I’m confused about “Make the appropriate changes in my.cnf” or “Make the proper changes in my.cnf”, you mean adding “innodb_file_per_table=1” in my.cnf , right ?

  75. @Jack: That’s correct. :)

  76. Thanks alot…used the whole db dump method. Worked a treat.

  77. Shrink from 39GB to 38GB :(

  78. @Innek: Chances are you missed a point in the guide. It makes no sense for this file to stay that big…

  79. single says

    Hi , I am new to mysql, and trying to shrink DB. But i dont understand what should b the contents in my.cnf, when one saying “appropriate chnages in my.cnf” . Please help. reading doc i could not understand anything :( . My DB size is 4 gb whether it should not more than 1GB.

  80. single says

    After following all steps i could not start mysql. getting err that is window could not start mysql. I tried all 3 methods n getting same err.

  81. Michel says

    Hi, please help me, i’ve tried this steps on a Ubuntu Server with Plesk, and now nothing is working anymore ! I choosed to rename the directory to mysql-backup, make another “mysql” and lauch “sudo -u rootmysql_install_db”
    It didn’t work so I decided to delete the mysql folder and rename the backup into mysql.

    But now, it tells me Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2) and all my sites are down !!

  82. @Michel, the error means that the MySQL server is most probably not running. Try to start it. If it doesn’t start, check the logs to find out why.

  83. Before we embark on this radical cure and replace the single ibdata1 with individual files, can you clarify whether these individual files all end up in the one folder ibdata1 lives, as an earlier poster (Mike) seems to suggest, or in the database specific subfolders?
    In shared server scenario with dozens of databases, all table files of all databases ending up in one place doesn’t strike me as good practice.

  84. @Matthias Otto, the option innodb_file_per_table setting instructs MySQL (and MariaDB) to put individual *.ibd files for each InnoDB table, right next to other table data files (in the directory of their database). This however doesn’t obsolete the ibdata1 file – it just reduces its usage.

  85. @single, I made a clarification to the article. The “appropriate changes in my.cnf” meant adding the “innodb_per_table” option, as described in the MySQL Reference Manual.

  86. @single, I know it’s been months since your comment but… what was the error message?

  87. Yer a saviour!

  88. Hi @VALERY DACHEV, unfortunately It does worked for me, I am using Mac 10.6.7, following are the steps i followed
    1) I removed all my databases (leaving those which you mentioned)

    2) I stopped the mysql

    3) I created a my.cnf under /etc, and the write

    i also placed my.cnf into ~/MAMP/Library and ~/MAMP/conf but does not work

    3) Then i restarted the mysql
    4) After that I load data into database from a c++ application using the DATA INFILE method

    but again there are only .frm files created and data is stored again in ibdata1 file which is increasing very fastely

    can you help me in this plz

  89. why the need to set key checks to 0 before droping the tables? (and then back to 1)
    when we import them it makes perfect sense (but not when droping)
    thanks for the tutorial

  90. @dcc, I can’t remember quite well, but I think that’s because MySQL wouldn’t allow you to drop a table that is referenced by another table. Otherwise you should analyze the foreign keys and change the order you drop databases in. That’s okay with a single database with a few tables, but once you have ~100 databases to migrate… :)

  91. noorrswati, thank you for commenting! I’ve added a footnote about Mac OS X. I’m not sure if that’s the case, but try adding the following to the my.cnf file:


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: