Backup and recovery of MySQL data

In the rapid development of science and technology today, each of us live in the big Data age, the more data we have, the more we can understand the needs of consumers, but also have better prospects for development, so data becomes very important. Then the backup of the data is indispensable. Today we're going to talk about backup and recovery of MySQL data.

Data backup by Business division can be divided into: full backup, incremental backup, differential backup.

1. Full backup

The so-called full backup is the data and the data structure of the whole database is backed up. The advantage of this backup method is that it is intuitive and easy to understand. And when a data loss disaster occurs, the lost data can be recovered as long as the backup file before the disaster is used. However, it also has shortcomings: first, because the system is fully backed up every day, there is a large number of duplicates in the backup data. This duplication of data takes up a lot of space, which means more cost to the user, and second, because the amount of data that needs to be backed up is quite large, so the backup takes a long time. For those organizations that are busy and have a limited backup window, choosing this backup strategy is no doubt unwise.

2. Incremental backup (incrementalbackup)

Is that the data that is backed up every time is just the amount of data that was added and modified since the last backup. The advantages of this backup are obvious: there is no duplication of backup data, which saves space and shortens backup time. But its disadvantage is that recovering data is cumbersome when a disaster occurs. For example, if the system fails in the morning of Thursday and a large number of data is lost, the system now needs to be restored to the Wednesday night state. At this point, the administrator needs to find out the Monday full backup data for system recovery, then find the Tuesday data to recover the Tuesday data, and then find the Wednesday data to restore the Wednesday data. Obviously this is much more troublesome than the first strategy. In addition, the reliability of this backup is poor. In this kind of backup, the relationship between the backup data is like a chain, one ring, and any one of the backup data problems will cause the whole chain out of line.

3. Differential Backup (differentialbackup)

The data that is backed up each time is the newly added and modified data relative to the last full backup. The administrator first makes a full system backup in Monday, and then in the next few days, the administrator then backs up all data (new or modified) from Monday to tape on the same day.

650) this.width=650; "src=" Https://s4.51cto.com/oss/201711/16/b139e6c6f93de5816966e61b50aa3001.png "title=" 6.png " Style= "width:500px;height:350px", "alt=" B139e6c6f93de5816966e61b50aa3001.png "width=" vspace= "0" hspace= "0" height= "border=" 0 "/>

Divided by way: can be divided into hot spare, Win Bei, cold

Hot Backup ( Hot backup) refers to direct backups in the database run and has no effect on the running database.

Cold Backup ( Cold backup) is the backup that is made when the database is stopped, which is the simplest, and generally only requires copying the relevant database physical files.

Warm Backup ( Warm Backup backups are also performed while the database is running, but they affect the operation of the current database, such as adding a global read lock to ensure the consistency of the backup data.

mysqldump: logical Backup tool for all storage engines, available for Win Bei, full backup, partial backup, support for InnoDB storage engine, CP, Tar File System Tools: Physical Backup tool for all storage engines, for cold standby, full backup, partial backup;

Mysqldump+binlog

Syntax format for commands

mysqldump [OPTIONS] database [tables]: Backing up a single library, or one or more tables specified by a library

mysqldump [OPTIONS]--databases [OPTIONS]DB1 [DB2 DB3 ...] : Backing up one or more libraries

mysqldump [OPTIONS]--all-databases[options]: Backing up all libraries

Mysqldump+binlog

Other options

-X,--lock-all-tables: Lock All Tables

-L,--lock-tables: Locked Table for backup

--single-transaction: Start a large single transaction to implement backup

-C,--compress: Compressed transmission

-E,--events: Backing up the event scheduler for the specified library

-R,--routines: Backup stored procedures and storage functions

--triggers: Backup Trigger

--MASTER-DATA={0|1|2}

0: Do not record

1: Record change MASTER to statement; This statement is not commented

2: Record as comment statement

-F,--flush-logs: Execute flushlogs command after locking table

Combat training

1. Prepare backup directory

Mkdir-pv/backup/binlog

2. Prepare backup database and table

650) this.width=650; "src=" Https://s3.51cto.com/oss/201711/16/bc57a7ae5f9e1ad39daaa4adca9ac05f.png "title=" 2.png " Style= "width:500px;height:115px", "alt=" Bc57a7ae5f9e1ad39daaa4adca9ac05f.png "width=" vspace= "0" hspace= "0" height= "border=" 0 "/>

3. make a full backup

Mysqldump--all-databases--lock-all-tables--flush-log--master-data=2 >/backup/' data +%f_%t '-all.sql

View Backup data: ls/backup/

4. inserting data into the table

650) this.width=650; "src=" Https://s3.51cto.com/oss/201711/16/2eb91fa00ce5b29eff10ffc76540d4c7.png "title=" 4.png " Style= "width:500px;height:311px", "alt=" 2eb91fa00ce5b29eff10ffc76540d4c7.png "width=" vspace= "0" hspace= "0" height= "311" border= "0"/>

5, make incremental backup, backup binary log

Mysqlbinlog--start-position=245--stop-position=440/var/log/mariadb/mariadb_bin.000004 >/backup/' Data +%F_%T '- All.sql

6, continue to insert data, in the case of no backup to delete the database, analog error operation

650) this.width=650; "src=" Https://s4.51cto.com/oss/201711/16/1b94610649cfb49cdebd73a1a262fd79.png "title=" 5.png " Style= "width:500px;height:143px", "alt=" 1b94610649cfb49cdebd73a1a262fd79.png "width=" vspace= "0" hspace= "0" height= "143" border= "0"/>

7, data recovery, because finally we do not have a backup to delete the database, so we first need to protect the last binary log, see the position value before the delete operation

mysqlbinlog/mydata/data/mysql-bin.000015

8 , the last operation of the binary log backup

Mysqlbinlog--stop-position=630/var/lib/mysql/mysql_bin.000004 >/backup/binlog/binlog-' Date +%F_%T '. sql

To view the log of backups: ls/backup/binlog/

9 . All backups prior to import

MySQL </backup/2017.....-all.sql full backup

MySQL </backup/2017... sql incremental backup

MySQL </backup/2017 .... backup before SQL error operation

view databases and data

LVM2 Snapshot +binlog

LVM Snapshot simply means that the snapshot source partition a point in time all the file metadata to save, if the source file is not changed, then the corresponding file access to the snapshot volume directly points to the source partition source file, if the source file changes, the corresponding file in the snapshot volume will not change. Snapshot volumes are primarily used for secondary backup files.

Experiment

1 . Add the hard drive and divide the disk type into LVM type

After adding the hard drive in the settings, to write it to the kernel, you can use echo '---'/sys/class/scsi_host/host0/scan

2 .partx-a/dev/sdb make the kernel recognize the new disk

3 .pvcreate/dev/sdb1 Adding physical volumes

4 .vgcreatemyvg/dev/sdb1 Add volume group

5 . lvcreate-nmydata-l 5G MYVG Add Logical Volume

6 .mkfs.ext4/dev/mapper/myvg-mydata formatted Logical volume

7 , mount mount/dev/mapper/myvg-mydata/lvm_data use , the mount point, if not, should be created in advance

8 , Modify The Mysql configuration, so that the data file on the logical volume datadir=/lvm_data

Modified files: vim/etc/my.cnf

9 ,servicemysqld Restart start Mysql Services

Create a database to operate

One ,mysql>flush TABLES with READ LOCK; # Lock table

Lvcreate-l1g-n mydata-snap-p r-s/dev/mapper/myvg-mydata # Create snapshot volume logicalvolume "Mydata-snap" Crea Re ".

Mysql>unlock TABLES; # Unlock all Tables

mount/dev/myvg/mydata-snap/lvm_snap/ # Mount snap

Tar Cvf/tmp/mysqlback.tar. /* # Pack Physical Backups

umount/lvm_snap/ # Uninstall snap

LVREMOVEMYVG mydata-snap # Delete snap

Delete mysql data rm-rf/lvm_data/*

Extract recover deleted data tar Xvf/tmp/mysqlback.tar.

Verify that the database data is restored correctly

650) this.width=650; "src=" Http://img.baidu.com/hi/babycat/C_0019.gif "alt=" C_0019.gif "/>                                                                                                                                                                                                                                                                                                                       

This article is from the "13162732" blog, please be sure to keep this source http://13172732.blog.51cto.com/13162732/1983291

Backup and recovery of MySQL data