MySQL Backup and restore detailed

Related reading:

MySQL backup and restore specific implementation http://www.linuxidc.com/Linux/2012-12/76257.htm

Three ways to summarize MySQL backup and recovery http://www.linuxidc.com/Linux/2012-12/75428.htm

MySQL backup restore (view, stored procedure) http://www.linuxidc.com/Linux/2012-01/52665.htm

One, MySQL backup type

1. Hot backup, warm backup, cold backup (based on server status)

• Hot backup: Read, write not affected;

• Warm backup: Only read operation can be performed;

• Cold backup: Offline backup, read and write operations are aborted;

2. Physical Backup and logical backup (from object to sub)

• Physical Backup: Copy data files;

• Logical backup: Export data to a text file;

3. Full backup, incremental backup, differential backup (from data collection)

• Full backup: Back up all data;

• Incremental backup: Backs up only data that has changed since the last full or incremental backup;

• Differential backup: Backs up only data that has changed since the last full backup;

4. Advantages of logical Backup:

• Two types of backups are dependent on different storage engines at the backup speed

• Physical backups are very fast to restore. But the minimum strength of a physical backup can only be achieved by table

• Logical backups are usually kept in a purely ASCII structure, so we can use a text processing tool to handle

• Logical backups have very strong compatibility, while physical backups are very demanding for versions

• Logical backups are also guaranteed to keep the data secure

5. Disadvantages of logical Backup:

• Logical backups have additional pressure on the RDBMS, while bare backups are stress-free

• The result of a logical backup may be larger than the source file. So many people are compressing the contents of the backup

• Logical backups may lose precision information for floating-point numbers

Note that differential backups are more space-intensive than incremental backups, but are easier to restore! But we usually use incremental backup !

Second, what does MySQL backup back up?

The following sections are typically backed up:

1. Data files

2. log files (such as transaction logs, binary logs)

3. Stored procedures, stored functions, triggers

4. configuration file (very important, each configuration file to be backed up)

5. Scripts used to implement database backup, Croutab of the database itself, etc...

Third, MySQL Backup tool

Common backup tools:

1.Mysql comes with Backup tool

• Mysqldump logical Backup tool, support all engines, MyISAM engine is Win Bei, InnoDB engine is hot standby, backup speed medium speed, restore speed is very very slow, but in the implementation of the restore, there is a lot of room to operate. It has good elasticity.

• mysqlhotcopy Physical Backup tool, but only support MyISAM engine , basically belongs to the category of cold standby, physical backup, speed is relatively fast.

2. File System Backup tool

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

  LVM is almost a hot backup, supporting all engines, snapshot-based (LVM,ZFS) physical backups, very fast, almost hot standby. 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 relatively fast, there is no elastic space, and the limitations of LVM: The multiple logical volumes can not be backed up at the same time , So various files, such as data files and transaction logs, must be placed on the same LVM. ZFS is very good to back up between multiple logical volumes .

3. Other tools

• 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 Open Source Tool MyISAM is a warm backup, InnoDB is a hot backup and a replacement tool for ibbackup business tools.

Iv. MySQL backup strategy

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

When you use the direct backup method, you must ensure that the table is not in use. If the server changes it when you are copying a table, the copy loses its meaning. The best way to ensure your copy integrity is to shut down the server, copy the files, and then restart the server. If you do not want to shut down the server, you need to lock the server while performing the table check. 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.

2. Strategy Two: mysqldump backup database (full backup + increase 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.

3. 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 speed for comparison of annoying busy database

Premise:

• 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;

Steps:

(1). Open session, apply read lock, lock all tables;

12	mysql> FLUSH TABLES WITH READ LOCK;mysql> FLUSH LOGS;

(2). Save the binary log file and related location information through another terminal;

1	mysql -uroot -p -e ‘SHOW MASTER STATUS\G‘> /path/to/master.info

(3). Create a Snapshot volume

1	lvcreate -L # -s -p r -n LV_NAME /path/to/source_lv

(4). Release lock

1	mysql> UNLOCK TABLES;

(5). Mount the snapshot volume, backup

12

mountcp

(6). Delete the snapshot volume;

Or use the ready-made integrated command tool Mylvmbackup (can integrate the above command set, automatically complete the backup)

mylvmbackup --user=dba--password=xxx--mycnf=/etc/my.cnf--VGNAME=TESTVG--LVNAME=TESTLV--backuptype=tar- -lvsize=100m--backupdir=/var/lib/backup

4. Strategy Four: Xtrabackup backup database, to achieve full hot and incremental hot backup (MyISAM is warm backup, InnoDB is a hot backup), because some data at the beginning of the design, the data directory is not stored on the LVM, so can not be backed up with LVM, Use Xtrabackup instead to back up the database

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

• Xtrabackup can only back up InnoDB and xtradb two data tables, not MYISAM data tables.

• 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;

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

6. Summary

A stand-alone backup is a combination of a full backup (all database files) + incremental backup (backup binary log)!

The backup in the cluster is a combination of full backup (all database files) + incremental backup (backup binary log) + master-slave copy (replication)!

The data will be fully backed up to the current date of the directory name in/root/mybackup/xtrabackup/, Xtrabackup will back up all the InnoDB tables, MyISAM tables simply copy the table structure files, and MyISAM, MERGE, CSV and archive tables, and also backs up files related to triggers and database configuration information. In addition to saving the data also generated some xtrabackup required data files, detailed below:

• Xtrabackup_checkpoints the backup type (such as full or incremental), the backup state (such as whether it is already in the prepared state), and the LSN (log sequence number) range information, each InnoDB page (typically 16k size) contains a log sequence number, the LSN. LSN is the system version number of the entire database system, and each page-related LSN can indicate how the page has changed recently.

• Xtrabackup_binlog_info the binary log file currently being used by the MySQL server and the location of the binary log event up to the moment the backup was made.

• Xtrabackup_binary the Xtrabackup executable file used in the backup.

• BACKUP-MY.CNF The configuration option information used by the backup command.

• Xtrabackup_logfile record standard output information xtrabackup_logfile

17. Summary

If the database fails after an incremental backup, we need to recover from the full backup plus all incremental backups up to now and the last incremental backup to the current binary log .

18. Notes

Separate backup:

1	innobackupex --user=root --password=123456 --defaults-file=/etc/my.cnf --database=test/root/mybackup

Backup and package compression:

1	innobackupex --user=root --password=123456 --defaults-file=/etc/my.cnf --database=test--stream=tar/root/mybackup/ | gzip> /root/mybackup/testdb.tar.gz

With time stamp:

1	innobackupex --user=root --password=123456 --defaults-file=/etc/my.cnf --database=test--stream=tar/root/mybackup/ | gzip > /root/mybackup/`date+%F`_testdb.tar.gz

Backup information output redirected to file:

1	innobackupex --user=root --password=123456 --defaults-file=/etc/my.cnf --database=test--stream=tar/root/mybackup/ 2>/root/mybackup/test.log | gzip1>/root/mybackup/test.tar.gz

Description

1234567

--stream #指定流的格式，目前只支持tar--database=test#单独对test数据库做备份 ，若是不添加此参数那就那就是对全库做2>/root/mybackup/test.log #输出信息写入日志中1>/root/mybackup/test.tar.gz #打包压缩存储到该文件中解压 tar-izxvf 要加-i参数，官方解释 innobackupex: You must use -i (--ignore-zeros) option forextraction of the tarstream.在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。

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

Transferred from: http://www.linuxidc.com/Linux/2013-07/87990.htm

MySQL Backup and restore detailed