MySQL Backup and restore series one: Backup type

One, MySQL backup type

1. Follow the MySQL server status

• Cold Offline backup, read and write operations are aborted

• warm can only perform read operations

• Hot Read and write operations are not affected

2, according to data consistency

• Consistent

• Inconsistent

3. Follow the backup data format

• logical Backup SQL statements to perform backup SQL statements to reproduce the database data at the time of recovery

• physical File system level directly copy data files, but the real backup time is not the CP so simple

4. Data storage method

• Full Backup

• Increamenta Incremental backup

• Differential Differential backup

Definition of differential backup: Backs up data that has changed since the last full backup .

Incremental backup definition: Backs up data that has changed since the last backup , including full, incremental, and differential backups.

Second, MySQL Backup tool

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/40/C4/wKiom1PPXZvCj5iJAAIb5TLCNys024.jpg "title=" 9.png " alt= "Wkiom1ppxzvcj5ijaaib5tlcnys024.jpg"/>

I mainly follow logical backup or physical backup into two categories to explain:

1. Logical Backup

• Mysqldump

  Mysqldump is the most commonly used logical backup method, which uses the SQL-level backup mechanism to lead the data table into SQL script files.

  All WARM support all engines, MyISAM engine is logic Win Bei, can be locked manually

  INNODB hot logic, only for INODB engines

• SELECT into OUTFILE

  For SQL-Level table backups, the logical

  
  

2. Physical Backup

• Ibbackup

  INNODBHot for INNODB engine, hot standby, physical backup

  AllWARM other engines, Win Bei

  Ibbackup Business Tool MyISAM is a warm backup, InnoDB is a hot backup, backup and restore speed are very fast, this software its license per Server version is $5000.

• Xtrabackup

  Xtrabackup Open Source Tool MyISAM is a warm backup, InnoDB is a hot backup and a replacement tool for ibbackup business tools.

• Snapshot (LVM,ZFS)

  Allalmost physical hot

  Hot standby, support for all local disk-based storage engines, fast backups, low overhead, easy to maintain integrity, fast recovery, and more.

• Almost hot backup, support for all engines, snapshot-based (LVM,ZFS) physical backups, very fast. Only affects data for a few seconds. However, the process of creating the snapshot itself affects the use of the database online, so the backup speed is faster, the recovery speed is faster, there is no elastic space, and the limitations of LVM: Multiple logical volumes can not be backed up at the same time, so the data files and transaction logs and other files must be placed on the same LVM. ZFS is very good to back up between multiple logical volumes.

• Mysqlhotcopy

  Only MyISAMMOSTLY COLD(lvm,snapshot)

  Mysqlhotcopy is a Perl program that is the lock tables, flush tables, and CP or SCP to quickly back up the database. It is the quickest 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 file, data file, index file) resides.

  Physical Backup tool, but only support MyISAM engine, basically belongs to the category of cold standby, physical backup, faster.

  Mysqldump can back up various types of data tables, but mysqlhotcopy is only suitable for backing up MyISAM and ISAM data tables. So before using mysqlhotcopy, you have to confirm that your data sheet is not available for other storage engines (storage engines).

• TAR,CP Cold standby

  Using a direct copy of the database file to package the backup, you need to pay attention to the following steps: Lock table, Backup, Solution table. Recovery is also very simple, directly copied to the previous database file storage directory. Note: For InnoDB engine tables, you also need to back up the log files, which is the ib_logfile* file. Because when the InnoDB table is damaged, you can rely on these log files to recover. CP Cold backup, support all engines, copy command, can only implement cold standby, physical backup. With the archive tool, the CP command, which backs up, the backup speed is fast, the restore speed is almost the fastest, but the flexibility is very low, can cross the system, but the cross-platform ability is poor.

• Use MySQL master-slave replication

  MySQL replication means that the DDL and DML operations of the primary database are routed through the binaries (bin-log) to the slave server and then re-executing the logs from the server, keeping the data synchronized from the server and the primary server.

Iii. Backup and Recovery strategies

Backup/Restore Policy:

1. Do a regular MySQL backup and consider the recovery time that the system can afford.

2. Make sure that MySQL opens Log-bin, with Binarylog,mysql to do full recovery when necessary, or point-in-time recovery, or location-based recovery.

3. Always do backup recovery testing to make sure that the backups are valid and can be recovered.

Because the MySQL table is saved as a file, it is easy to back up. To maintain the consistency of the backup, perform a lock TABLES operation on the related table and perform a flush TABLES on the table.

You only need to read the lock so that when you copy the files in the database directory, other customers are allowed to continue querying the table. The Flush tables statement is required to ensure that all active index pages are written to the hard disk before starting the backup.

Strategy one: Direct copy of database files (file system Backup tool CP) (for small databases, is the most reliable)

if the server is running, the same restrictions apply to copying files, and you should use the same locking protocol to "calm down" the server. When you have completed the backup, you need to restart the server (if it is turned off) or release the lock on the table (if you let the server run). To copy a database from one machine to another using a direct copy file, just copy the file to the appropriate data directory on the other server host. To make sure that the file is in myiasm format or that the two machines have the same hardware structure, your database has strange content on another host. You should also ensure that the servers on the other machine do not access them while you are installing the database tables.

Strategy two: mysqldump backup database (full backup + add backup, relatively slow, suitable for small database) (MyISAM is warm backup, InnoDB is hot backup)

Mysqldump is a SQL-level backup mechanism that guides data tables into SQL script files and is relatively appropriate for upgrading between different MySQL versions, which is also the most commonly used backup method. Mysqldump is slower than direct copy. For systems with moderate levels of traffic, the backup strategy can be as follows: The first full backup, one incremental backup per day, and a full backup once a week, so repeat. For important and busy systems, you may need to have a full backup every day, incremental backups hourly, or even more frequently. in order not to affect the online business, to achieve an on-line backup, and can be incremental backup, the best way is to use the master-slave replication mechanism (replication), on the slave machine to do the backup.

Strategy three: LVs snapshot from a physical point of view to achieve almost hot standby full backup, with binary log backup to achieve incremental backup, fast fit to compare annoying busy database

• The data file is to be on a logical volume;

• The volume group that contains this logical volume must have sufficient space to use the snapshot volume;

• Data files and transaction logs are to be on the same logical volume;

Strategy four: Xtrabackup backup database for full hot and incremental hot backup (MyISAM is warm backup, InnoDB is hot backup)

Description: Xtrabackup is a tool for data backup of InnoDB, supporting online hot backup (no impact on data read and write during backup), a good alternative to commercial backup tools InnoDB hotbackup or Ibbackup.

Xtrabackup has two main tools: Xtrabackup, Innobackupex

• innobackupex is a reference to the Innoback script of InnoDB Hotbackup. Innobackupex is a Perl script package that encapsulates xtrabackup. The main purpose is to back up the InnoDB and MyISAM engine tables at the same time, but a read lock is required to handle the MyISAM. and added some options to use. As Slave-info can record the backup after recovery as slave need some information, according to this information, can easily use Backup to redo slave.

Characteristics:

• The backup process is fast and reliable;

• The backup process does not interrupt the transaction being performed;

• Can save disk space and traffic based on functions such as compression;

• Automatic implementation of backup inspection;

• Fast restore speed;

Strategy five: Master-slave replication (replication) for real-time database backup (common in cluster)

Reference:

http://freeloda.blog.51cto.com/2033581/1254883

This article is from the "Share Your Knowledge" blog, so be sure to keep this source http://skypegnu1.blog.51cto.com/8991766/1501442

MySQL Backup and restore series one: Backup type