Ubuntu Server MySql database backup script code

  • 2020-05-27 07:23:17
  • OfStack

Description:

I'm going to backup the pw85 database below var/lib/mysql into home/mysql_data and save it as mysqldata_bak_2012_04_11.tar.gz in the compressed file format (2012_04_11 is the date on which the backup was executed), with only the last 7 days.

Implementation steps:

1. Create a directory to save the backup files: /home/mysql_data
cd /home # enter the directory
mkdir mysql_data # create a directory
2. Create a backup script file :/home/mysql_data/ mysql_databak.sh
cd /home/mysql_data # enters the directory
touch mysql_databak.sh # create file
nano mysql_databak.sh # edit the file and enter the following


#!/bin/sh
DUMP=/usr/bin/mysqldump #mysqldump Backup program execution path 
OUT_DIR=/home/mysql_data # Backup file storage path 
LINUX_USER=root # System user name 
DB_NAME=pw85 # The name of the database to backup 
DB_USER=root # Database account   Note: the root The user will use the backup parameters  --skip-lock-tables , otherwise you may report an error 
DB_PASS=123456 # Database password 
DAYS=7 #DAYS=7 On behalf of the delete 7 Days old backups, that is, keep only the most recent 7 Days of backup 
cd $OUT_DIR # Go to the backup repository directory 
DATE=`date +%Y_%m_%d` # Gets the current system time 
OUT_SQL="$DATE.sql" # Backup the file name of the database 
TAR_SQL="mysqldata_bak_$DATE.tar.gz" # The final saved database backup file name 
$DUMP -u$DB_USER -p$DB_PASS $DB_NAME --default-character-set=utf8 --opt -Q -R --skip-lock-tables> $OUT_SQL # The backup 
tar -czf $TAR_SQL ./$OUT_SQL # Compressed into .tar.gz format 
rm $OUT_SQL # delete .sql Format the backup file 
chown $LINUX_USER:$LINUX_USER $OUT_DIR/$TAR_SQL # Change the owner of the backup database file 
find $OUT_DIR -name "mysqldata_bak*" -type f -mtime +$DAYS -exec rm {} \; # delete 7 Days before the backup file 


(note: {} \; There is a space in the middle.)
#######################################################################################################
ctrl+o # save the configuration
ctrl + x # exit

3, modify the file properties to make it executable

chmod +x /home/mysql_data/mysql_databak.sh

4. Modify /etc/crontab

Edit the nano /etc/crontab # file and add it below
45 22 * * * root /home/mysql_data/ mysql_databak.sh # means that backups are performed at 22:45 every day
ctrl+o # save the configuration
ctrl + x # exit

5. Restart crond for Settings to take effect

service cron stop # stop
service cron start # starts
/ etc/init d/cron restart # to restart
chkconfig cron on # is set to boot

Every day you can see compressed files like mysqldata_bak_2012_04_11.tar.gz under /home/mysql_data
If you need to recover a file, just unzip it: tar-zxvf mysqldata_bak_2012_04_11.tar.gz
Then import it into the database.

At this point, the Ubuntu Server MySql database backup script is complete.


Related articles: