MySQL regular backup is an important task, but manual operation is too cumbersome, but also difficult to avoid omissions, use the following method to let the system regularly back up data. Use the system crontab to execute the backup file regularly, save the backup result by date, and achieve the purpose of backup.
1. Create a backup folder
#cd/bak
#mkdir Mysqldata
2. Write Run script
#vi/usr/sbin/bakmysql.sh
Note: If you use Nano to edit this code, add ' && ' or '; ' at the end of each line Connector, or the end character of the resulting file name is garbled
Code:
#!/bin/bash
# Name:bakmysql.sh
# This was a shellscript for Auto DB backup and Delete old Backup
#
Backupdir=/bak/mysqlbak
Time= ' Date +%y%m%d%h '
Mysql_bin_dir/mysqldump-u User-ppassword dataname1 | gzip > $backupdir/name1$time.sql.gz
Mysql_bin_dir/mysqldump-u User-ppassword dataname2 | gzip > $backupdir/name2$time.sql.gz
#
Find $backupdir-name "name_*.sql.gz"-type f-mtime +5-exec rm {} \; >/dev/null 2>&1
Save exit
Description
Time= ' Date +%y%m%d%h ' in the code can also be written as Time= "$ (date +"%y%m%d$h ")"
where the ' symbol is the symbol above the TAB key, not the ' left ' sign of enter, and a space after date.
The bin path of the Mysql_bin_dir:mysql;
Dataname: Database name;
User: Database username;
Password: User password;
Name: custom backup file prefix identification.
-type f means finding a normal type of file, and F represents a normal file.
-mtime +5 The file changes time to find the file, +5 indicates that the file change time is 5 days ago, or if-mmin +5 means that the file change time is now 5 minutes ago.
-exec rm {} \; Represents executing a shell command, followed by the EXEC option with the command or script to execute, followed by a pair of {}, a space and a \, and finally a semicolon.
/dev/ Null 2>&1 REDIRECT the standard error to standard output and then drop it under/dev/null. In layman's words, all standard output and standard errors are thrown into the trash, where the & means that the command executes in the background.
3, add Execute permission for script
# chmod +x/usr/sbin/bakmysql.sh
4, modify the/etc/crontab (in centOS5 test feasible) or crontab-e
Check if there are no crond installed, if not, install first
Yum-y Install Vixie-cron
Yum-y Install Crontabs
#vi/etc/crontab
In the last line, add:
XX 3 * * * root/usr/sbin/bakmysql.sh
Indicates that backups are performed 3:00 every day
Note: The crontab configuration file format is as follows:
Time-sharing Weekly command
Redhat Method:
Redhat's crontab takes 4 directories per time (/etc/cron.hourly: hourly;/etc/cron.daily: every day;/etc/cron.weekly: Weekly;/etc/ Cron.monthly: Monthly) The way the script comes out of the running.
Just copy the script you just edited to the appropriate directory in the Redhat.
5. Restart Crontab
#/etc/rc.d/init.d/crond Restart orService Crond Restart
Complete.
6. Restore Data Backup files:
Non-compressed backup file recovery:
#mysql-u root-p dataname < Name2008010103.sql
To restore directly from a compressed file:
#gzip < name2008010103.sql.gz | Mysql-u root-p Dataname
Or:
# zcat name2008010103.sql.gz | mysql-u root-p
Go-let Linux regularly back up the MySQL database every day and delete the backup files five days ago
