Large MYSQL DB on single file

Question

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

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

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

Answer 1

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:

1. Create a slave with xtrabackup (set master_info_repository to table if you have MySQL 5.6.2+)
2. Have it replicated and let it catch up
3. Stop the replication: stop slave
4. (optional) Make a backup of master info file if you have MySQL older than 5.6.2
5. Make a note of the position with show slave status
6. Make the mysqldump
7. Destroy the database and all the files in mysql_datadir and remove all the relay logs
8. Restore the database with mysql_install_db and execute the dump (make sure you have file_per_table settings)
9. (only on <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.

Answer 2

If you use mysqldump, you can do the following

• Assure that no DDL occurs (no ALTER TABLE, no DROP TABLE) during the mysqldump
• Use --single-transaction to make sure all tables have the same point-in-time

You can arrange a mysqldump to push the data directly into the slave without saving a dump file. Just pipe the mysqldump straight into the slave. You must preset the replication pieces beforehand.

Create a bash script called LoadSlave.sh with these lines

MYSQL_MAS=10.20.30.40
MYSQL_SLV=10.20.30.50
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYSQL_CONN_MAS="-h${MYSQL_MAS} -u${MYSQL_CONN}"
MYSQL_CONN_SLV="-h${MYSQL_SLV} -u${MYSQL_CONN}"
REPL_USER=replicator
REPL_PASS=whateverthereplicatorpasswordis
DATABASE_TO_DUMP=mydb
MYSQLDUMP_OPTIONS="--single-transaction"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --master-data=1"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines --triggers"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --add-drop-database"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} -B ${DATABASE_TO_DUMP}"

SQL="STOP SLAVE;"
SQL="${SQL} CHANGE MASTER TO master_host='${MYSQL_MAS},master_port=3306,"
SQL="${SQL} master_user='${REPL_USER},master_password='${REPL_PASS}',"
SQL="${SQL} master_log_file='blank',master_log_pos=4;"

mysql ${MYSQL_CONN_SLV} -ANe"${SQL}"
mysqldump ${MYSQL_CONN_MAS} ${MYSQLDUMP_OPTIONS} | mysql ${MYSQL_CONN_SLV}
mysql ${MYSQL_CONN_SLV} -ANe"START SLAVE;"

Then, run the following

chmod +x LoadSlave.sh
nohup ./LoadSlave.sh &

This will set up replication mechanism, transmit the mysqldump through the network straight to the slave, drop the database on the slave, create the database on the slave, load the data into the slave, and start replication.

Please test this out on Dev/Staging Servers

As highlighted in Károly Nagy's answer, you could use percona-xtrabackup. Just keep in mind the major difference between mysqldump and percona-xtrabackup

• If you start mysqldump --single-transaction, at midnight and the backup takes 30 min, the backup's point-in-time is the start time of the mysqldump, 12:00 AM.
• If you start percona-xtrabackup, at midnight and the backup takes 30 min, the backup's point-in-time is the completion time of the backup, 12:30 AM.