6

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.

3 Answers 3

3

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
  • Is there absolutely no way around this? The original file may be 160 GB big, but only a small portion of that is used. Can't I construct a new ibdata1 using the data from the original?
    – Halfgaar
    Nov 11, 2013 at 22:13
  • BTW, you said: "It is possible you could suffer data loss by trying to delete it manually without getting rid of all your existing databases first." That sounds uncertain. It's safe to give the 'you have to do it' answer, but do you have real data about what this ibdata1 file holds for DB's created with innodb_file_per_table?
    – Halfgaar
    Nov 11, 2013 at 22:25
  • @Halfgaar There is no safe, vendor-supported way around a full database system dump and restore. All the MySQL system tables are stored in the shared tablespace, and if you trash those your MySQL instance is toast. If you elect to perform a procedure which is not supported by the vendor (MySQL) that's your choice, but I'm certainly not going to advise you to do so. Rule number zero is you don't take risks with production data - you ALWAYS take the safe option...
    – voretaq7
    Nov 11, 2013 at 23:05
0

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

3
  • I'm not sure transportable tablespaces will solve any problems here (there will still need to be an outage while the data is copied from the old server instance to the new one, no?
    – voretaq7
    Nov 11, 2013 at 23:02
  • 1
    There will need to be an outage, but not anywhere near the length of a dump/restore. Just long enough to restart and let MySQL recreate the InnoDB related files, then import it. I know of no way that one could remove/shrink the ibdata1 file without at least a restart.
    – user143703
    Nov 11, 2013 at 23:17
  • Ubuntu 12.04 has MySQL 5.5.32 :(
    – Halfgaar
    Nov 12, 2013 at 6:35
0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .