A simple method to implement scheduled backup of MySQL database in Linux

Here are the detailed steps:

1. Check the disk space:

[root@localhost backup]# df -h
File system capacity used available used% Mount point /dev/mapper/centos-root 17G 2.7G 15G 16% /
devtmpfs 476M 0 476M 0% /dev
tmpfs 488M 0 488M 0% /dev/shm
tmpfs 488M 7.7M 480M 2% /run
tmpfs 488M 0 488M 0% /sys/fs/cgroup
/dev/sda1 1014M 130M 885M 13% /boot
tmpfs 98M 0 98M 0% /run/user/0
[root@localhost backup]#

Select a suitable disk to store the backup files

2. Create a backup directory:

cd /home
mkdir backup
cd backup

3. Create a backup shell script:

Create a backup script in the created directory (vi bkDatabaseName.sh)

#!/bin/bash
mysqldump -uroot -proot rtak > /data/backup/rtak_$(date +%Y%m%d_%H%M%S).sql
mysqldump -uroot -proot rtak | gzip > /data/backup/rtak_$(date +%Y%m%d_%H%M%S).sql.gz

Note:

bkDatabaseName.sh Replace with an interesting name

You can choose between sql backup and gz backup, or full backup

Username and password need to be replaced

4. Add executable permissions:

chmod u+x bkDatabaseName.sh

Test whether the file can be executed normally (./bkDatabaseName.sh)

Note: (1) If the error mysqldump: command not found is displayed, execute

ln -fs /usr/local/mysql/bin/mysqldump /usr/bin (/usr/local/mysql is the path where mysql is installed)

(2) If there is a warning (Warning: Using a password on the command line interface can be insecure.), you can ignore it.

(3) Check whether the backup SQL file is normal and whether it can be imported into the database normally

5. Add scheduled tasks

Confirm whether crontab is installed:

If the crontab command reports command not found, it means that it is not installed.

Execute the command:

crontab -e

Enter the following and save:

*/* * 1 * * /data/backup/bkDatabaseName.sh

/* * 1 * * / Several * represent minute, hour, date, month, and day of the week when the backup operation is performed.

For example: perform backup every minute /1 * * * * / (tested)

Perform backup every day at 3:00 AM /00 3 * * * / (not tested)

6. Stop the backup operation

When scheduled backup is not required, perform this operation and the normal process will be completed at step 5.

crontab -r

Note: Clean up expired SQL backups in time to prevent the disk from filling up