MySQL Backup and restore instructions

MySQL Automated Backup Guide.md

In the home directory make a .my.cnf file

touch ~/.my.cnf

Ensure it has your login details.

[mysqldump]
user=yourdbusername
password=yourdbpassword

Permissions of the .my.cnf must be 600.

chmod 600 .my.cnf

Make script to run this. Or put it in cron as you wish.

mysqldump -u yourdbusername yourdbname | gzip > yourdbname_db_backup_`date +%Y-%m-%d`_.sql.gz

If you use cron you will want another script to rsync or SCP the backups off site. You may also want to delete some after backed up to free up space with some scheme in mind as to not run out of disk space.

Ah with modern (non macosx?) you can get ISO standard date using date -I

Note: If you want to monitor progress bar of pipe.
pv is an awesome utility.

mysqldump -u username database | pv -cN | gzip > ~/backups/`date -I`_mysql_db_database.sql.gz

To restore

gunzip < database_backup_filename| mysql -u database_username -p database_name

Note: If on plesk or if you get an error such as
ERROR 1227 (42000) at line 403: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

Then you will need to use a super user mysql account or make your account one.

On plesk this is as simple as

plesk db
GRANT ALL PRIVILEGES ON database_name.* TO 'database_user'@'localhost';
UPDATE mysql.user SET Super_Priv='Y' WHERE user='database_user' AND host='localhost';
FLUSH PRIVILEGES;
\q

Note: You would probably want to disable Super privilege afterwards if using the same account for website.