Backup a database

It's way too difficult to restore a database using the /var/lib/mysql files. It's better to use mysqldump. Assume your database is named mydbname.

mysqldump -u root -p mydbname > mydbname.sql

This will dump out the SQL needed to recreate the database.

Normally you want this as a cron job so it runs daily. Google how to make a cron job. Put the command into a script. You also want to gzip it because it's quite big and repetitive. The below is an example of a useful script. It saves the files with dates in the names so you can revert/restore the database from any date. Note however it embeds your password into the file, this is probably a bad idea™ but you can find a better solution yourself. One way is to create an authenticated user, let them run mysql without password (saved password), and set them run the script, ie use the username parameter in cron.

#!/bin/sh

uniquedate=`date +"%Y%m%d_%H%M%S-%s"`

backuppath="/var/log/mysqldumps"

[ ! -d $backuppath ] && mkdir -p $backuppath || :

chown 0.0 -R $backuppath

chmod 0700 $backuppath

thisfile=mydbname".sql.gz"

mysqldump -u root -h localhost -p"yourpassword" mydbname | gzip > $backuppath/mydbname/$thisfile

To restore from backup just gunzip the gzipped file (mydbname.sql) and use mysql like so:

mysql -u root -p mydbname < mydbname.sql

Note that this will overwrite existing SQL or change existing so... make sure you want to do this first.