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:
--no-data
In case of the error “Mysqldump error 1044: Access denied for database when using LOCK TABLES” after mysqldump add:
--skip-opt
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/backups.sh > /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`
wget https://github.com/TrinityCore/TrinityCore/archive/4.3.4.zip
wget https://github.com/TrinityCore/TrinityCore/archive/master.zip
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