Backup script example

I will write below a simple example of backing up mysql databases using mysqldump.

Create an executable file and write the line to it:

mysqldump -u USER -h localhost -pPASSWORD DATABASE | gzip -c > /home/backups/mysqldump/databasename_`date +%Y-%m-%d`.sql.gz

In order to exclude some tables when dumping a database, you need to add:

--ignore-table=database.table --ignore-table=database.table

To dump the structure without data itself:


In case of the error “Mysqldump error 1044: Access denied for database when using LOCK TABLES” after mysqldump add:


Mysql user assign rights:

GRANT SELECT, LOCK TABLES, SHOW VIEW ON *.* TO 'user'@'localhost' IDENTIFIED BY 'password';

Add a link to the new file in /etc/crontab (the scheduler will execute it every day at 4 am):

0 4 * * * user /home/backups/ > /dev/null 2>&1

You can also add backup directories to the script, for example:

tar -cvjf /home/backups/etc/`date +%Y-%m-%d`_etc.tar.bz2 /etc/
tar -cvjf /home/backups/www/`date +%Y-%m-%d`_www.tar.bz2 /var/www/

You can also regularly clone the github repository, for example the Trinity Core repository:

mkdir /home/backups/trinitycore/`date +%Y-%m-%d`
cd /home/backups/trinitycore/`date +%Y-%m-%d`

How to delete old backups automatically, so as not to overflow the disk I wrote in this article – Script to delete old files

See also my articles:
Import and export MySQL databases
Connecting to MySQL without entering a password

Leave a comment

Leave a Reply