Large MYSQL DB on single file I have a large MYSQL innodb database (115GB) running on single file mode in MySQL server.

I NEED to move this to file per table mode to allow me to optimize and reduce the overall DB size.

Im looking at various options to do this, but my problem falls in there only being a small window of downtime (roughly 5 hours).


Setup a clone of the server as a slave. Set the slave up with file_per_table, take a mysqldump from the main DB, run in the slave and have this replicating.


I will then look to fail over to the slave.


The other option is the usual mysqldump, drop DB and then import.


My concern is around the time to take the mysqldump and the quality of the dump being such a large size. I have BLOB data in the DB also.

Can anyone offer advise on a good approach?

Thanks
If you're concerned about the downtime mysqldump might cause you can look into hot backups. For example percona-xtrabackup.

Here is what I would do:


Create a slave with xtrabackup (set master_info_repository to
table if you have MySQL 5.6.2+)   
Have it replicated and let it catch up   
Stop the replication: stop slave 
(optional) Make a backup of master info file if you have MySQL older than 5.6.2
Make a note of the position with show slave status
Make the mysqldump
Destroy the database and all the files in mysql_datadir and remove all the relay logs
Restore the database with mysql_install_db and execute the dump (make sure you have file_per_table settings)
(only on &lt;5.6.2) If necessary restore replication (if it was in table than the dump contains the information so no need)


Then you can do the switchover and you'll have only a couple of seconds of downtime.