Automatically Backup MySql

Automatically Backup MySql

#! /bin/bash

# Automated database MySQL backup

# The below command will backup one database in SQL which has been gzip.

mysqldump -h IPofMySQLMachine -u username -pPassword databaseName | gzip > /pathToBackupHome/database-`date --iso-8601`.sql.gz

# The below command will backup all databases in SQL which has been gzip.

mysqldump -h IPofMySQLMachine -u username -pPassword --all-databases| gzip > /pathToBackupHome/database-`date --iso-8601`.sql.gz

I have given examples for gzip format. You can skip gzip and pipe if you want plain sql. The first example would change to

# The below command will backup one database in SQL format.

mysqldump -h IPofMySQLMachine -u username -pPassword databaseName  > /pathToBackupHome/database-`date --iso-8601`.sql

To Automate this backup process you can execute this shell script using crontab

See mysqldump manual for more details

http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html