Friday, May 16, 2008

Restoring MySQL databases CLI trick

It is very easy to dump and restore a database using mysql and mysqldump CLI utilities, just

# backup
mysqldump --single-transaction mydb > dump.sql
mysql mydb < dump.sql

and you are all. Unfortunately, if your database is several gigabytes and takes a long time to restore you might want to have some sort of output, to indicate where in the process your backup or restore is. For backup you just add -v flag to your mysqldump command and it will throw out some information about which table it is backing up. What about restore? While it is definitely possible to just go and check what table is being restored (mysqldump dumps tables in alphabetical order), I came up with a little clever trick to make the restore progress obvious and similar to mysqldump. Just add perl.

cat dump.sql | perl -ne '/Table structure for table \`(.*?)\`/ && do {chomp($t=`date`); print STDERR $t . " loading $1\n";}; print' | mysql mydb

1 comment:

  1. You’ll see advert outcomes based mostly on factors like relevancy, and the quantity sellers ppc. Etsy is not supporting older versions 1xbet korea of your internet browser in order to to} positive that|be positive that} user knowledge stays safe. Gold Club™ use state-of-the-art design processes and solely the most effective supplies to ensure an extended lifespan and high quality appears.