Mysql – Best way to backup/restore a very large DB (~2TB)

MySQLmysqldump

The ibdata1 file has increased to ~2TB and leaves only a few GBs free space in the same disk (this is because I forgot to turn on innodb_file_per_table, before it was too late)

From what I have read in What is the best way to reduce the size of ibdata in mysql? the only way to reduce ibdata1 is to:

backup -> delete ibdata1 -> restore database.

Now, since the ibdata1 is so large, what's the best way to do it?
And how much time will it take?

I have two other free disks of 2TB size available which can be used for backup.

My thought is as follows: 

mysqldump current database into disk-x  
restore from disk-x to disk-y  
delete original ibdata1 in disk-z

Is this good way and how long will it take?

Best Answer

You may need to think further ahead -- When reloading, will you have enough space?

Setting innodb_file_per_table does not necessarily provide any extra space.

When you do SHOW TABLE STATUS;, the "Data_free" will indicate how much free space there is in ibdata1. How big is that? If it is small, then something more needs to be done.

Is it possible in that OS to "extend" your current drive with one of those spare drives? That is can you, with an OS command and without dumping, extend the current "drive" to 4TB? If so, that is is probably worth doing to get started.

If you do backup, it will need to be to some other drive, either on the same server or on another server. (That is, do the dump across the 'network'.)

Related Question