Back up MySQL using mysqlhotcopy

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

Mysqlhotcopy backs up the entire database Directory, which can be copied directly to the datadir (/var/lib/MySQL/) directory specified by mysqld, pay attention to permission issues as follows:

Shell> CP-RF db_name/var/lib/MySQL/

Shell> chown-r MYSQL: MySQL/var/lib/MySQL/(change the owner of the db_name directory to the mysqld running user)

This backup policy can only restore the database to the state of the last backup. If you want to lose as little data as possible during the crash, you should back up the database more frequently, to restore data to the state of the crash, use the master-slave replication mechanism (replication ).

TIPS:

If you do not want to write the password in shell, you can create a. My. CNF file in the root home directory so that mysqlhotcopy can read the username/password from it.

[Mysqlhotcopy]

User = root

Password = yourpassword

Then, for security reasons, CHMOD.

Chmod 600 ~ /. My. CNF

Appendix: mysqlhotcopy common parameters:

· -- Allowold if the target exists, do not give up (add a _ old suffix to rename it ).

· -- Checkpoint = db_name.tbl_name Insert the checkpoint entry in the specified db_name.tbl_name.

· --- Debug enables debugging output.

· -- Dryrun,-N reports actions without executing them.

· -- Flushlog refresh the log after all the tables are locked.

· -- After keepold is completed, the previous (renamed) targets are not deleted.

· -- Method = command COPY method (CP or SCP ).

· -- Noindices backup does not include all index files. In this way, the backup is smaller and faster. You can use myisamchk-RQ to re-build the index later.

· -- Password = password,-P password: the password used to connect to the server. Note that the password value of this option is optional, unlike other MySQLProgram.

· -- Port = port_num,-P port_num indicates the TCP/IP Port number used to connect to the local server.

· -- Quiet and-Q remain silent except when an error occurs.

· -- Regexp = expr: copy the database that matches the regular expression given by all database names.

· -- Socket = path,-s path is used to connect UNIX socket files.

· -- Suffix = Suffix of the database name copied by Str.

· -- Tmpdir = path temporary directory (instead of/tmp ).

· -- User = user_name,-u user_name: MySQL username used to connect to the server.

Mysqlhotcopy reads the [client] and [mysqlhotcopy] consumer groups from the option files. To execute mysqlhotcopy, you must be able to access the backup table files and have select and reload permissions for those tables (so that you can execute flush tables ).