Notes on MySQL backup and recovery policies (1)

•  
  MySQL backup and recovery policies
  11:06:05 big medium and small labels: knowledge/exploration MySQL backup and recovery MySQL backup policy MySQL recovery policy when the database table is lost or damaged, it is very important to back up your database. 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)

   

  Script: Click to download the script

   

  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. 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

  CAT/dev/null> mysqlbackup. Log

  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 // USR // local // MySQL // data //" |/

  Sed-e "s/test //" '; do

  If [[$ databases = ""]; then

  Continue

  Else

  # Backing up a 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

  # Compressing backup files

  Date = 'date-I'

  CD $ tmpbackupdir

  Tar CZF $ backupdir/mysql-‑date.tar.gz ./

  # Send email notification

  If [[-s mysqlbackup. Log]; then

  Cat mysqlbackup. log | mail-s "MySQL backup" $ email

  Fi

  # Use the smbclientmv. Sh script to upload the database backup to the backup server

  # $ Scriptsdir/smbclientmv. Sh

   

  Smbclientmv. Sh Annotation

  #! /Bin/sh

  # Name: smbclientmv. Sh

  # Ps: Move the data to backup server.

  # Write by: I. Stone

  # Last modify: 2007-11-15

  #

  # Defining variables

  # Backup Server Name

  Backupserver = "backupservername"

  # Shared folder name

  Backupshare = "sharename"

  # Backup server access username and password

  Backupuser = "smbuser"

  Backuppw = "smbpassword"

  # Define the 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-rjdate.tar.gz"

  # Delete temp files

  Rm-F $ backupdir/mysql-1_date.tar.gz

  (3) restore the database to the backup status
  Mysqlhotcopy backs up the entire database Directory, which can be copied directly to the datadir (/usr/local/MySQL/data/) directory specified by mysqld during use, pay attention to permission issues as follows:
  Shell> CP-RF db_name/usr/local/MySQL/data/
  Shell> chown-r MYSQL: MySQL/usr/local/MySQL/data/(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 ).