My (SQL) WorkLog: August 2009

Friday, August 07, 2009

MySQL - Restoring a single table from nightly backup (mysqldump generated file)

Very often we get the requests from customer to restore a "tiny" table from the nightly backup into the development/staging environment. This request need to be done on urgent basis as its just a matter of a one table and customer don't want to restore entire database of 10GB so the restoration might take less than 5 min - this is how most of the customer/managers thinks and they are abs right as why should it take more time for a single table?. This can be addressed by playing with the privileges and with the help of parameters available to 'mysql' command line utility

Steps to restore single table from dump file

# Create a user & GRANT him all rights on the table which need to be restored
# Start importing as show below

mysql -uRestUser -pSecret --force --one-database DB_NAME < /path/to/dumpfile.sql

ResetUser - "user" created for restoring single table

--one-database - Ignore statements except those for the default database named on the command line

--force - Parameter would force the import to continue even if an SQL error occurs( mainly due to the privileges as the new user don't have the privilege on other tables)