Dumping and restoring a rotated MySQL table

Posted on 2015-10-20

These are the next steps after rotating a MySQL table that is used for data logging.

Overview:

1. Dump the data out of the rotated table.
2. Fix the dump so it referred to the original table.
3. Restore the table into a long-term archive database.

Dump the table

I always dump the schema and data separately. The schema should be done from the original table.

The data is a straightforward mysqldump run:

mysqldump -u username \
	-p \
	--compact \
	--no-create-info \
	--skip-triggers \
	--opt \
	--result-file=BigTable_YYYYMMDD.sql \
	logdb BigTable_YYYYMMDD

Fix the dump's references to the rotated table

You can do this by hand: I found that the joe editor is able to work with big files much better than the other Linux editors I tried.

But the easiest way is with sed:

sed -i "s/BigTable_YYYYMMDD/BigTable/g" BigTable_YYYYMMDD.sql

Tags: mysql backup