Home / server / Questions / 554233
Accepted
Halfgaar
Asked: 2013-11-12 12:10:28 +0800 CST

Remove MySQL ibdata1 without dumping and restoring existing proper databases

  • 772

My MySQL server contains two 100+ GB big databases. One was created with innodb_file_per_table and one wasn't. The one that wasn't, has been dumped, ready to be reloaded. However, the ibdata1 file is still huge and I don't have enough free space. Normal advice in this situation is to dump and remove each database, stop MySQL, then remove ibdata1 and the transaction logs, and then reload the databases.

My specific question is: can I leave databases that were created with innodb_file_per_table alone? Or will they be destroyed when I remove ibdata1, even though all their files are separate?

I can't afford to take this database off-line to dump and reload it. And because it's already properly made with separate files per table, it would feel pretty useless.

mysql

3 Answers

  1. Best Answer

    My specific question is: can I leave databases that were created with innodb_file_per_table alone? Or will they be destroyed when I remove ibdata1, even though all their files are separate?

    The monolithic ibdata1 contains some system-wide data. It is possible you could suffer data loss by trying to delete it manually without getting rid of all your existing databases first.
    (There are ways to recover from such a situation, but why put yourself in that position? Follow the instructions you've been given and dump ALL your databases, rebuild your MySQL installation, and reload them. It's the safe choice.)

    I can't afford to take this database off-line to dump and reload it.

    You're going to have to afford it. It's just one outage - schedule it and make it happen.
    Our resident MySQL expert confirms that this is the only (safe) way to shrink ibdata1 and I defer to his expertise in these matters.

    (I would also suggest reading his other posts, linked from that answer, before you schedule the outage window. You may as well clean up as many potential problems as you can since you're going to have to take an outage window...)

    • 3

  2. If you are running MySQL 5.6 you can take advantage of a new feature called transportable tablespaces.

    If you aren't running 5.6, you can use the innobackupex tool from Percona Xtrabackup.. This requires using XtraDB (Percona's modified InnoDB engine, part of Percona Server). Importing and Exporting Individual Tables

    If you aren't running 5.6, and can't run Percona Server

    • 0

  3. if a db needs to be up, due to selects are crucial, and you have fast storage (such as iops volume on ec2), one way to minimize downtime is to stop mysql, rsync over data dir to fast volume, and then bring back up master in read-only.

    then you have time to dump/import and so on, while your old master servers up select qrys. once dump/import are done, and second box is ready to take writes, just cut over to it in your app layer code.

    if your master needs write at all times, you cant use this. if you have dedicated slaves, you can lock master, fixup slave and promote to master.

    • 0