Shulou(Shulou.com)05/31 Report--

This article mainly introduces "mysql data backup and recovery". In the daily operation, I believe many people have doubts about mysql data backup and recovery. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "mysql data backup and recovery". Next, please follow the editor to study!

Backup

1. Regular backup work script: (date and time as the name of the compressed file, unzip is the sql script)

/ root/backup/script/backup_.sh

two。 Backup output path:

/ root/backup/mysql

3. Commands to perform scheduled tasks:

# crontab-e Open the scheduled Task Editor

# add the following (refers to the execution of backup_mysql.sh backup scripts scheduled to start at 00:00 on Sunday and Thursday)

0 0 * * 0jue 4 / root/backup/script/backup_mysql.sh

Www.2cto.com

4. If you add data to the library, you can also perform a backup manually

# Log in to 192.168.20.101 and execute the script under. / root/backup/script/backup_mysql.sh on the command line

5. File production in / root/backup/mysql/ year date time .tar.gz

The content of the backup_mysql.sh script:

# = = start shell script = =

#! / bin/bash

# define the name of the database with backup

Dbname=zhdatabase1

# define the user name and password to use when backing up the database

Dbuser=root dbpasswd=root

# path to database backup

Backuppath=/root/backup/mysql/

# the path to the storage of database backup log files

Logfile=/root/backup/log/mysql.log

Www.2cto.com

# name the database backed up with the current time.

Dumpfile=$ (date +% y%m%d%H%M)

# this function is used to back up the database

Back_db () {

# save the backup time and database name in the log

Echo "-" $(date +% Y-%m-%d%t%A%t%T) "Beginning database" ${dbname} "backup-" > > ${logfile}

# back up the database and log if there are any error messages. Default utf8 encoding

#-skip-opt (mysql service runtime backup, please add this parameter to prevent table corruption)

#-- quick-- force-- routines-- add-drop-database-- add-drop-table (stored procedures are also backed up)

/ usr/bin/mysqldump-- skip-opt-u ${dbuser}-p$ {dbpasswd}-- default-character-set=utf8-- quick-- force-- routines-- add-drop-database-- add-drop-table ${dbname} > ${backuppath} ${dumpfile} .SQL 2 > > ${logfile}

# start compressing data files

Echo $(date +% Y-%m-%d%t%A%t%T) "Beginning zip ${backuppath} ${dumpfile} .sql" > ${logfile}

# compress the backup database file library into a ZIP file, and delete the previous SQL file. If there is an error message, it will also be logged.

Tar zcvf ${dumpfile} .tar.gz ${dumpfile} .SQL & & rm ${dumpfile} .sql 2 > > ${logfile}

# save the compressed file name to the log.

Echo "backup file name:" ${dumpfile} ".tar.gz" > > ${logfile}

Echo-e "-" $(date +% Y-%m-%d%t%A%t%T) "Ending database" ${dbname} "backup-\ n" > > ${logfile}

}

Www.2cto.com

# send email

# cat ${logfile} | mutt-s "Blog database backup"-a ${dumpfile} .tar.gz 8chf@163.com}

# this function is used to delete data from 90 days ago. Note that this script deletes all files in the current directory that are older than seven days.

Rm_oldfile () {

# find the files generated 90 days ago in the current directory and delete them

Find / root/backup/mysql-type f-mtime + 90-exec rm {}\

}

# change to the directory where the database is backed up. If you do not do this, there may be errors when compressing the file

Cd ${backuppath}

# run backup data function

Back_db

# run the delete file function

Rm_oldfile

# add autorun scheduled tasks (execute scripts every Sunday, every Thursday, 00:00)

# crontab-e

# 00 00 * * 0010 04 / root/backup/script/backup_mysql.sh

# = end shell script = =

Www.2cto.com

Restore

1. Restore the backed up database

Note: the database is backed up as a compressed file of "year date time .tar.gz" and unzipped as a sql file. For example, 201206271633.tar.gz: represents 16:33 on June 27, 2012. Tar.gz

# 1 winscp logs in to the MySQL server to retrieve the last backup. Tar.gz under / root/backup/mysql/

# 2 decompression

# 3 create a new "database" using the database management tool navicat, then open it, right-click "Table" and select the pop-up menu

Select "run batch task file" to import sql file, select utf8 encoding, and execute to restore table structure data (default utf8 encoding).

# test database is a test database, so import tests at will

At this point, the study on "mysql data backup and recovery" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.