Easy-to-use mysql backup tool _ MySQL

Source: Internet
Author: User
Tags mysql backup perl script
I used mysqldump for backup, which is easy to store as text files, but it is slow. Of course, the fastest backup method is to copy the data directory directly. But in general, MySQL services must be shut down before you can do it. otherwise, when you copy the data, there will be people reading and writing tables, which will be too much trouble. This friend introduced me to use mysqlhotcopy. Mysql management tools

I used mysqldump for backup, which is easy to store as text files, but it is slow. Of course, the fastest backup method is to copy the data directory directly. But in general, MySQL services must be shut down before you can do it. otherwise, when you copy the data, there will be people reading and writing tables, which will be too much trouble. This friend introduced me to use mysqlhotcopy. This is equivalent to the above, but he can perform hot backup. His backup is very fast. I tested a 2.8G mysql and his backup time was completed within 3 minutes.

Below is its introduction.

Mysqlhotcopy is a Perl script originally written and provided by Tim Bunce. It uses lock tables, flush tables, and cp or scp to quickly back up databases. It is the fastest way to back up a database or a single table, but it can only run on the machine where the database directory is located. Mysqlhotcopy is only used to back up MyISAM. It runs in Unix and NetWare

For usage instructions, see the following script. add it to crotab.

#! /Bin/sh
# Name: mysqlbackup. sh
# PS: MySQL DataBase Backup, Use mysqlhotcopy script.
# Last Modify: 2008-06-12
# Define variables. modify the variables as needed
# Define the directory where the script is located
ScriptsDir = 'pwd'

# Database Data Directory
DataDir =/var/lib/mysql

# Data backup directory
TmpBackupDir =/tmp/mysqlblackup
BackupDir =/backup/mysql

# Username and password used to back up the database
MysqlUser = root
MysqlPWD = 'You password'

# If the temporary backup directory exists, clear it. if it does not exist, create it.
If [[-e $ tmpBackupDir]; then
Rm-rf $ tmpBackupDir /*
Else
Mkdir $ tmpBackupDir
Fi

# Create a backup directory if it does not exist
If [[! -E $ backupDir]; then
Mkdir $ backupDir
Fi

# Obtain the database backup list, where you can filter databases that do not want to be backed up
For databases in 'find $ dataDir-type d | \
Sed-e "s/\/var \/lib \/mysql \ //" | \
Sed-e "s/test //" '; do
If [[$ databases = ""]; then
Continue
Else

# Backing up a database
/Usr/bin/mysqlhotcopy -- user = $ mysqlUser -- password = $ mysqlPWD-q "$ databases" $ tmpBackupDir
DateTime = 'date "+ % Y. % m. % d % H: % M: % S "'
Echo "$ dateTime Database: $ databases backup success! "> MySQLBackup. log
Fi
Done


# Compressing backup files
Date = 'date-I'
Cd $ tmpBackupDir
Tar czf $ backupDir/mysql-‑date.tar.gz ./

# End completed


Add to crontab to set to run 5 times a week
0 0 ** 5/backup/blackup. sh


Note: restore the database to the backup status

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.