Shell script + crontab for automatic MySQL data backup and compression

When there is more and more data in the database, how important is data backup. Next we will use shell scripts + crontab scheduled tasks to back up MySQL data (supporting multiple databases ), I will not explain the usage of shell and crontab too much here. If you are not familiar with it, you can refer to relevant materials for further study.

#! /Bin/bash
      
# Name of the database to be backed up. Multiple databases are separated by spaces, for example, database1 database2 database2)
Databases = (mydatebase)
      
# Directory of the backup file to be saved
Basepath = '/home/backup_mysql'
      
If [! -D "$ basepath"]; then
Mkdir-p "$ basepath"
Fi
      
# Circulating databases array
For db in $ {databases [*]}
Do
# Backup database generation SQL file
/Bin/nice-n 19/usr/bin/mysqldump-uroot -- default-character-set = utf8-p123456 -- database $ db> $ basepath $ db-$ (date + % Y % m % d ). SQL
      
# Compressing the generated SQL file
/Bin/nice-n 19 tar zPcf $ basepath $ db-$ (date using policyymm1_d1_. SQL .tar.gz $ basepath $ db-$ (date + % Y % m % d). SQL
      
# Deleting backup data 7 days ago
Find $ basepath-mtime + 7-name "*. SQL .tar.gz"-exec rm-rf {}\;
Done
      
# Delete the generated SQL file
Rm-rf $ basepath/*. SQL
To support remote server backup, you can change the mysqldump parameter in row 17th to-p123456 followed by-h 192.168.1.123 remote ip.

After the shell script is written, it is saved in the specified location, for example, in/home/backup_mysql/back_mysql.sh. Now we start to run it regularly. At this time, we can use crontab in Linux, to implement scheduled execution at every night:

1
0 23 *** sh/home/backup_mysql/back_mysql.sh
The backup effect is as follows: automatically deletes the backup that was created seven days ago and retains only the data for the past seven days. The advantage is that it can save disk space:

-Rw-r -- 1 root 161994773 July 16 03:40 mydatebase-20160716. SQL .tar.gz

-Rw-r -- 1 root 162069976 July 17 03:40 mydatebase-20160717. SQL .tar.gz

-Rw-r -- 1 root 162137600 July 18 03:40 mydatebase-20160718. SQL .tar.gz

-Rw-r -- 1 root 164006629 July 19 03:41 mydatebase-20160719. SQL .tar.gz

-Rw-r -- 1 root 164911525 July 20 03:41 mydatebase-20160720. SQL .tar.gz

-Rw-r -- 1 root 166129583 July 21 03:41 mydatebase-20160721. SQL .tar.gz

-Rw-r -- 1 root 167313026 July 22 03:42 mydatebase-20160722. SQL .tar.gz