In Linux, the MySQL database is automatically backed up and uploaded to the remote FTP server and the specified _ MySQL

In Linux, the MySQL database is automatically backed up and uploaded to the remote FTP server and the Shell script bitsCN.com before the specified date is deleted.

In Linux, the MySQL database is automatically backed up and uploaded to the remote FTP server and the Shell backup script before the specified date is deleted.

Note:

1. backup MySQL database storage directory/var/lib/mysql vos3000db database to/home/snapshot compressed file format (2012_06_30 refers to the date on which the backup is executed ), last, only the backups of the last 7 days are retained.

2. Upload The backup file in/home/mysql_data to the remote FTP server and keep the backup files for the last seven days.

3. FTP server: 192.168.21.139 Port: 21 account: admin Password: 123456 mysql_data is the directory for storing backup files (this directory must be created on the FTP server in advance)

Steps:

1. install ftp Commands

Apt-get install ftp # install ftp

2. create a directory to save the backup file:/home/mysql_data

Cd/home # enter the Directory

Mkdir mysql_data # Create a directory

3. 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 = vos3000db # Name of the database to be backed up

DB_USER = root # Database username

DB_PASS = 521521 # 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 = "vos3000db_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-zcvf $ 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 "vos3000db_bak *"-type f-mtime + $ DAYS-exec rm {}/; # Delete the backup file seven DAYS ago (note :{}/; there is a space in the middle)

Deldate = 'date-d-7day + % Y _ % m _ % d' # delete backup of ftp server space seven days ago

Ftp-n <

Open 192.168.15.5 21

User sam 521521

Binary # set binary transmission

Cd mysql_data # enter the ftp directory

LCD/home/mysql_data # List local directories

Prompt

Mput vos3000db_bak_1_date.tar.gz vos3000db_bak_1_date.tar.gz # upload files in the directory

Mdelete vos3000db_bak_?deldate.tar.gz vos3000db_bak_?deldate.tar.gz # Delete the backup of the ftp space seven days ago

Lose

Bye!

Ctrl + o # save configuration

Ctrl + x # exit

4. Modify file attributes to make them executable.

Chmod + x/home/mysql_data/mysql_databak.sh

5. modify/etc/crontab

Nano/etc/crontab # Add below

45 22 * root/home/mysql_data/mysql_databak.sh # indicates that backup is performed at every day.

6. 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 # install chkconfig (apt-get install chkconfig) before starting chkconfig)

You can see compressed files such as vos3000db_bak_2012_06_30.tar.gz in the/home/mysql_datadirectory.

To restore a file, extract the file tar-zxvf vos3000db_bak_2012_06_30.tar.gz.

Import the data to the database.

So far, Debian automatically backs up the MySQL database and uploads it to a remote FTP server and deletes the Shell backup script before the specified date.

BitsCN.com