Summary of MySQL database backup and restore method

Backup strategy One: Copy database files directly (not recommended)
Backup strategy Two: Use Mysqlhotcopy backup Database (full backup, suitable for small database backup)
Backup strategy Three: Use mysqldump Backup Database (full + incremental backup, suitable for medium database backup)
Backup strategy Four: Use master-slave replication mechanism (replication) (real-time database backup)

Backup strategy First, direct copy of database files

Direct copy of data files is the most direct, fast, and convenient, but the disadvantage is that the incremental backup is basically not possible. To ensure data consistency, you need to execute the following SQL statement before backing up the file:
FLUSH TABLES with READ LOCK;
That is, the data in memory is flushed to disk, while the data table is locked to ensure that no new data is written in the copy process. This method back up the data recovery is also very simple, directly copied back to the original database directory can be

Backup strategy Two, use Mysqlhotcopy to back up the database
Mysqlhotcopy is a PERL program that was originally written by Tim Bunce. It uses LOCK tables, FLUSH tables, and CP or SCP to quickly back up databases. It is the quickest way to back up a database or a single table, but it can only run on the same machine as the database file (including the datasheet definition file, data file, index file), and mysqlhotcopy can only be used to back up the MyISAM table.
This backup strategy is suitable for small database backup, the amount of data is not large, you can use the Mysqlhotcopy program to do a full backup every day.
Backup Strategy Placement:
(1), install Dbd-mysql Perl module, support Mysqlhotcopy script to connect to MySQL database.
Shell> TAR-XZVF dbd-mysql-4.005.tar.gz
Shell> CD dbd-mysql-4.005
Shell> unset LANG
Shell> Perl makefile.pl-mysql_config=/usr/local/mysql/bin/mysql_config-testuser=root-testpassword=userpwd
Shell> make
Shell> make Test
shell> make Install
(2), set the Crontab task, execute the backup script every day
Shell> CRONTAB-E
0 3 * * */root/mysqlbackup/mysqlbackup.sh >/dev/null 2>&1
A backup script is executed 3:00 every day.

MYSQLBACKUP.SH Note:
#!/bin/sh
# Name:mysqlbackup.sh
# ps:mysql DataBase backup,use mysqlhotcopy script.
# Write By:i.stone
# Last Modify:2007-11-15
#
# define variables, please modify them as appropriate
# define the directory where the script is located
Scriptsdir= ' pwd '
# Data Directory for database
datadir=/usr/local/mysql/data/
# Data Backup Directory
tmpbackupdir=/tmp/tmpbackup/
backupdir=/tmp/mysqlbackup/
# User name and password used to back up the database
Mysqluser=root
mysqlpwd=111111
# define Email Address
Email=alter@somode.com

# If the temporary backup directory exists, empty it, and create it if it does not exist
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

# empty MySQLBackup.log
if [[s MySQLBackup.log]]; Then
Cat/dev/null >mysqlbackup.log
Fi

# Get a list of database backups, where you can filter the databases you don't want to back up
For databases in ' find $dataDir-type D | ＼
Sed-e "s/\/usr\/local\/mysql\/data\///" | ＼
Sed-e "s/test//"; Todo

if [[$databases = = ' "]]; Then
Continue
Else
# BACKUP Database
/usr/local/mysql/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

# Compress backup Files
Date= ' Date-i '
CD $tmpBackupDir
Tar czf $backupDir/mysql-$date. tar.gz./

# Send mail notification
if [[s MySQLBackup.log]]; Then
Cat MySQLBackup.log | Mail-s "MySQL Backup" $eMail
Fi

# use smbclientmv.sh script to upload database backup to backup server
# $scriptsDir/smbclientmv.sh

Smbclientmv.sh notes
#!/bin/sh
# Name:smbclientmv.sh
# Ps:move The data to Backup Server.
# Write By:i.stone
# Last Modify:2007-11-15
#
# define Variables
# Backup Server name
Backupserver= "Backupservername"
# shared folder name
Backupshare= "sharename"
# Access user name and password for the backup server
Backupuser= "Smbuser"
backuppw= "Smbpassword"
# define Backup Directory
Backupdir=/tmp/mysqlbackup
Date= ' Date-i '

# move the ' data to Backupserver
Smbclient//$BackupServer/$BackupShare \
$BackupPW-d0-w workgroup-u $BackupUser \
-C "Put $BackupDir/mysql-$date. tar.gz\
mysql-$date. tar.gz "

# Delete Temp Files
Rm-f $BackupDir/mysql-$date. tar.gz
(3), restore the state of the database to the time of backup
Mysqlhotcopy Backup is the entire database directory, use can be directly copied to the mysqld specified DataDir (here is/usr/local/mysql/data/) directory, but also attention to the issue of permissions, the following example:
Shell> CP-RF db_name/usr/local/mysql/data/
Shell> chown-r mysql:mysql/usr/local/mysql/data/(convert db_name directory owner to Mysqld run user)
This set of backup policies can only restore the state of the database to the last backup, so that the data lost in the crash should be backed up as infrequently as possible, and the master-slave replication mechanism (replication) is used to restore the data to the state of the crash.

The backup and restore method of MySQL under Win32 system

Backup: Enter "cmd" in "Run", use "Cd/program files/mysql/mysql Server 5.0/bin" to enter the Bin folder, enter "Mysqldump-u user name-P databasename > Exportfilename "Export the database to a file, such as Mysqldump-u root-p voice>voice.sql, and then enter a password to start exporting MySQL data and implementing backup operations."

Restore: Enter the MySQL command line Client, enter the password, enter the "mysql>", enter the command "show databases;", carriage return, see what some database; Create the database you want to restore, type create Voice; ", carriage return; switch to the newly established database, type" use voice; ", enter the data, enter" source voice.sql; ", carriage return, start the import, reappear" mysql> "and restore success without prompting for an error.

Second, Linux under the MySQL backup and restore methods:

Backup: [root@localhost ~]# cd/var/lib/mysql (go to MySQL library directory, adjust directory according to your MySQL installation)
[Root@localhost mysql]# mysqldump-u root-p voice>voice.sql, enter the password.
Restore: There are two ways to choose:

The first method: [root@localhost ~]# mysql-u root-p carriage return, enter the password, enter the MySQL console "mysql>", restore with 1.2.

The second method:

[Root@localhost ~]# cd/var/lib/mysql (access to MySQL library directory, adjust directory according to your MySQL installation situation)
[Root@localhost mysql]# mysql-u root-p voice<voice.sql, enter the password.