networked day to day technical issues

25Feb/120

mysql backups using mysqldump

I keep encountering all sort of bad attempts or at least not optimal attempts at doing Mysql full db backups. While it looks like a trivial task using the mysqldump tool, there are several things one needs to take into account:

  • if you backup all databases into a file (--all-databases) then when you will need to restore only one database from the backup you will be in trouble as in order to restore it you need to restore all databases on a staging server and afterward dump just the needed one or remove all of the rest of databases but with the second approach you still have the "mysql" database changed; or use some tool which can extract from a full dbdump just the needed one (it's basically a text file so you could scrip around it). Update: you can use mysql (mysql -D db_name -o < dump_file.sql) to restore a particular db from a dump done with --all-databases , just take care to have db_name created before attempting the restore
  • if you backup separately each database to it's own dump file then you will quickly learn that you should have also backed up the usernames and passwords which are allowed to access/modify the database
  • if you run a cron script each night which creates the dump(s) and overwrites the previous night's backup file(s) then you may learn it the hard way that a 0 bytes dump or incomplete dump will leave you not only without today's backup but also possibly without yesterday's valid backup (in case it wasn't bad too). So in this case the advice is keep more then tonight's and/or yesterday's backup. I generally keep at least 7 of them if done nightly as generally by the time someone realizes they need a backup a day might have passed. Also it is recommended to have a real backup infrastructure in place (with the associated retention policy)
Filed under: Linux, mysql Continue reading