Regular backup of linux mysql

Keywords: MySQL crontab Database mysqldump SQL

The project needs to back up the database regularly. Here are your own operation notes

1. Check the disk space


# df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/vda1        40G  3.6G   34G  10% /
tmpfs            16G     0   16G   0% /dev/shm

This is my current situation of linux server. I don't know why it's like this (I'm half connected, but I can't help it)

Based on the above information, I put the backup file in the / dev directory

2. Create backup directory


cd /dev
mkdir backup
cd backup

3. Create backup shell command


Enter the following

1 #!/bin/bash
2 mysqldump -uusername -ppassword -hmysqlIp DatabaseName > /dev/backup/DatabaseName_$(date +%Y%m%d_%H%M%S).sql

Compress backup

mysqldump -uusername -ppassword -hmysqlIp DatabaseName | gzip > /dev/backup/DatabaseName_$(date +%Y%m%d_%H%M%S).sql.gz

be careful:
Replace username with the actual user name;

Replace the password with the actual password;

Replace mysql IP with mysql IP;

Replace DatabaseName with the actual database name;

4. Add executable permission

chmod u+x

After adding the executable permission, execute it first to see if the script has any errors and can be used normally;


5. Add Scheduled Tasks

1 # crontab
2 -bash: crontab: command not found

If the crontab command is executed and the command is not found, it indicates that it is not installed;

To install crontab, refer to:

Add scheduled task

crontab -e
*/1 * * * * /home/backup/

It means to execute every minute. Note: for cron expression, it is recommended to Baidu

6. Test whether the task is performed

 1 # tail -f /var/log/cron
 2 Dec 21 17:33:10 iZ2318jid47Z run-parts(/etc/cron.daily)[22449]: finished makewhatis.cron
 3 Dec 21 17:33:10 iZ2318jid47Z run-parts(/etc/cron.daily)[15753]: starting mlocate.cron
 4 Dec 21 17:33:10 iZ2318jid47Z run-parts(/etc/cron.daily)[22460]: finished mlocate.cron
 5 Dec 21 17:33:10 iZ2318jid47Z run-parts(/etc/cron.daily)[15753]: starting prelink
 6 Dec 21 17:33:27 iZ2318jid47Z run-parts(/etc/cron.daily)[23799]: finished prelink
 7 Dec 21 17:33:27 iZ2318jid47Z run-parts(/etc/cron.daily)[15753]: starting readahead.cron
 8 Dec 21 17:33:27 iZ2318jid47Z run-parts(/etc/cron.daily)[23811]: finished readahead.cron
 9 Dec 21 17:33:27 iZ2318jid47Z run-parts(/etc/cron.daily)[15753]: starting tmpwatch
10 Dec 21 17:33:27 iZ2318jid47Z run-parts(/etc/cron.daily)[23849]: finished tmpwatch
11 Dec 21 17:33:27 iZ2318jid47Z anacron[15719]: Job `cron.daily' terminated

Or go to the directory where you store the backup files to see if there are backup files generated;

Posted by joozt on Sat, 02 May 2020 07:56:12 -0700