Script code for MySql database backup under Ubuntu Server

Note:

Here I want to store the MySql database directory/Var/lib/mysqlThe followingPw85Back up the database/Home/mysql_dataAnd saveMysqldata_bak_2012_04_11.tar.gzCompressed file format (2012_04_11 indicates the date on which the backup was executed.).7Daily backup.

Steps:

1. Create a directory to save the backup file:/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 # enter the Directory
Touch mysql_databak.sh # create a file
Nano mysql_databak.sh # edit the file and enter the following content

Copy codeThe Code is as follows:
#! /Bin/sh
DUMP =/usr/bin/mysqldump # mysqldump backup program execution path
OUT_DIR =/home/mysql_data # backup file storage path
LINUX_USER = root # system username
DB_NAME = pw85 # Name of the database to be backed up
DB_USER = root # database account Note: Non-root users must use the backup parameter -- skip-lock-tables; otherwise, an error may occur.
DB_PASS = 123456 # Database Password
DAYS = 7 # DAYS = 7 indicates that the backup created seven DAYS ago is deleted, that is, only the backup of the last seven DAYS is retained.
Cd $ OUT_DIR # enter the backup storage directory
DATE = 'date + % Y _ % m _ % d' # obtain the current system time
OUT_ SQL = "$ DATE. SQL" # Name of the backup database file
TAR_ SQL = "mysqldata_bak_1_date.tar.gz" # Name of the final database backup file
$ DUMP-u $ DB_USER-p $ DB_PASS $ DB_NAME -- default-character-set = utf8 -- opt-Q-R -- skip-lock-tables> $ OUT_ SQL # backup
Tar-czf $ TAR_ SQL./$ OUT_ SQL compressed into .tar.gz format
Rm $ OUT_ SQL # Delete backup files in. SQL format
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 backup files 7 DAYS ago


(Note: {} \; there is a space in the middle)
######################################## ######################################## #######################
Ctrl + o # Save Configuration
Ctrl + x # exit

3. Modify file attributes to make them executable.

Chmod + x/home/mysql_data/mysql_databak.sh

4. Modify/etc/crontab

Nano/etc/crontab # edit the file and add
45 22 * root/home/mysql_data/mysql_databak.sh # indicates that backup is performed at every day.
Ctrl + o # Save Configuration
Ctrl + x # exit

5. Restart crond to make the settings take effect.

Service cron stop # stop
Service cron start # start
/Etc/init. d/cron restart # restart
Chkconfig cron on # Set to boot

Every day, you can see compressed files such as mysqldata_bak_2012_04_11.tar.gz under the/home/mysql_datadirectory.
To restore a file, extract the file tar-zxvf mysqldata_bak_2012_04_11.tar.gz.
Import the data to the database.

So far, the MySql database backup script under Ubuntu Server is complete.