MySQL backup and recovery policies

MySQL backup and recovery policies

It is important to back up your database when database tables are lost or damaged. In the event of a system crash, you must be able to restore as much data as possible from your table to the State at the time of the crash. This document describes how to back up and restore the MyISAM table.

Backup policy 1: directly copy database files (not recommended)

Backup policy 2: Use mysqlhotcopy to back up the database (full backup, suitable for backup of small databases)

Backup Policy 3: Use mysqldump to back up the database (full + Incremental backup, suitable for medium-sized database backup)

Backup policy 4: Use the master-slave replication mechanism (for real-time database backup)

Backup policy 1. Directly copy database files

Directly copying data files is the most direct, fast, and convenient, but the disadvantage is that Incremental backup is basically not supported. To ensure data consistency, run the following SQL statement before backing up the file:

Flush tables with read lock;

That is, the data in the memory is refreshed to the disk, and the data table is locked to ensure that no new data is written during the copy process. In this way, the backup data recovery is also very simple, just copy it back to the original database directory.

Backup policy 2. Use mysqlhotcopy to back up a database

Mysqlhotcopy is a PERL program originally written 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 file (including the data table definition file, data file, and index file) is located, in addition, mysqlhotcopy can only be used to back up MyISAM tables.

This backup policy is suitable for backup of small databases with a small amount of data. You can use the mysqlhotcopy program to perform a full backup every day.

Backup policy layout:

(1) install the DBD-mysql perl module and support connecting the mysqlhotcopy script to the 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 and execute the backup script every day.

Shell> crontab-e

0 3 ***/root/MySQLBackup/mysqlbackup. sh>/dev/null 2> & 1

Run the backup script at every day.

Note:

#! /Bin/sh

# Name: mysqlbackup. sh

# PS: MySQL DataBase Backup, Use mysqlhotcopy script.

# Write by: I. Stone

# Last Modify: 2007-11-15

#

# Define variables. Modify the variables as needed

# Define the directory where the script is located

ScriptsDir = 'pwd'

# Database Data Directory

DataDir =/usr/local/mysql/data/

# Data Backup Directory

TmpBackupDir =/tmp/tmpbackup/

BackupDir =/tmp/mysqlbackup/

# Username and password used to back up the database

MysqlUser = root

MysqlPWD = 111111

# Define eMail addresses

EMail = alter@somode.com

# 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

# Clearing MySQLBackup. log

If [[-s MySQLBackup. log]; then