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.


  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