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

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...)

  • 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 '13 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 '13 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 '13 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

  • 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 '13 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. – yoonix Nov 11 '13 at 23:17
  • Ubuntu 12.04 has MySQL 5.5.32 :( – Halfgaar Nov 12 '13 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.