UbuntuServer MySQL database backup script

Note: here I want to back up the pw85 database under the/var/lib/MySql database directory to the compressed file format of/home/snapshot (2012_04_11 refers to the date on which the backup was executed ), at last, only the backups of the last 7 days are retained. Implementation steps: 1. Create a directory for saving the backup file:/home/mysql_datacd/home

Note:

Here I want to back up the pw85 database under the/var/lib/MySql database directory to the compressed file format of/home/snapshot (2012_04_11 refers to the date on which the backup was executed ), at last, only the backups of the last 7 days are retained.

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

######################################## ######################################## #######################

#! /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 the backup file seven 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.