MySQL Backup and Recovery Strategy detailed tutorial

It is important to back up your database if your database tables are missing or corrupted. If a system crash occurs, you will want to be able to recover as few data from your table as possible to the state at which the crash occurred. This paper mainly on the MyISAM table to do backup recovery.

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.

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