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 for backup
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 for backup
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