MySql database backup script code under UbuntuServer _ MySQL

UbuntuServer MySql database backup script code: UbuntuServerUbuntu

BitsCN.com 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: {}/; spaces exist 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.

BitsCN.com