Ubuntu Server MySql database backup script code
- 2020-05-27 07:23:17
- OfStack
Description:
(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.
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.