Implement a variety of ways to back up MySQL

First, Mysqldump+binlog Backup and recovery

This method is a full backup that enables incremental backups by backing up the binary logs. The steps are as follows:

① Creating a backup directory

650) this.width=650; "src=" Https://s4.51cto.com/oss/201711/16/4f0365844b7e4b0c7bb3647c6eaaf271.png "title=" 1.png " alt= "4f0365844b7e4b0c7bb3647c6eaaf271.png"/>

② create a backed up database and table.

650) this.width=650; "src=" Https://s3.51cto.com/oss/201711/16/d5b6dd2febeabf647d74aa19c6cd6cd4.png "title=" 1.png " alt= "D5b6dd2febeabf647d74aa19c6cd6cd4.png"/>

③ backing up the database

650) this.width=650; "src=" Https://s3.51cto.com/oss/201711/16/f2bd277161531edcbe0feab46e789613.png "title=" 1.png " alt= "F2bd277161531edcbe0feab46e789613.png"/>

④ inserts data into the table.

650) this.width=650; "src=" Https://s3.51cto.com/oss/201711/16/336d63080d808583db59335fad5d0e09.png "title=" 1.png " alt= "336d63080d808583db59335fad5d0e09.png"/>

⑤ make incremental backups, and back up the binary logs. "Remember to turn on the binary log"

MARIADB configuration file

650) this.width=650; "src=" Https://s2.51cto.com/oss/201711/16/d4ed430d875419a5c387d1c4c1a525bb.png "title=" 1.png " alt= "D4ed430d875419a5c387d1c4c1a525bb.png"/>

650) this.width=650; "src=" Https://s5.51cto.com/oss/201711/16/5965fe94f45c7c9805624388f593c0c0.png "title=" 1.png " alt= "5965fe94f45c7c9805624388f593c0c0.png"/>

⑥ incremental backup, backup binary log

viewing binary logs

650) this.width=650; "src=" Https://s4.51cto.com/oss/201711/16/7ca1aae0a4acef78c8fbba69393d7bac.png "title=" 1.png " alt= "7ca1aae0a4acef78c8fbba69393d7bac.png"/>

Add another piece of data to back up the binary log

650) this.width=650; "src=" Https://s5.51cto.com/oss/201711/16/1c6d6f4bf3bbf85c542c093a2b6cb900.png "title=" 1.png " alt= "1c6d6f4bf3bbf85c542c093a2b6cb900.png"/>⑦ continue to insert data, no backup, simulated mistakenly deleted database.

650) this.width=650; "src=" Https://s4.51cto.com/oss/201711/16/e65fe8224bb3dd1935773bafb93b390b.png "title=" 1.png " alt= "E65fe8224bb3dd1935773bafb93b390b.png"/>

Back up the last operational binary log

To view the command for the last action log:

mysqlbinlog/var/lib/mysql/log-bin.0000002 (Note: Yellow represents the name of the custom binary log)

650) this.width=650; "src=" Https://s3.51cto.com/oss/201711/16/f8ec660b01137ea6da8688eb2f710bda.png "title=" 1.png " alt= "F8ec660b01137ea6da8688eb2f710bda.png"/>

650) this.width=650; "src=" Https://s2.51cto.com/oss/201711/16/fe87063855eba03ebc4cce5cf28dabaa.png "title=" 2.png " alt= "Fe87063855eba03ebc4cce5cf28dabaa.png"/>⑧ all backups before importing

650) this.width=650; "src=" Https://s3.51cto.com/oss/201711/16/9b662b53b20892bebe421dddb0156dbf.png "title=" 1.png " alt= "9b662b53b20892bebe421dddb0156dbf.png"/>

⑨ view database and data.

650) this.width=650; "src=" Https://s1.51cto.com/oss/201711/16/7961ff1ef08a8bd09a23d916ecc10b23.png "title=" 1.png " alt= "7961ff1ef08a8bd09a23d916ecc10b23.png"/>

Restore success.

Second, Xtrabackup mode backup

For InnoDB: Hot standby, full backup and incremental backup supported

For MyISAM: Win Bei, only full backup is supported

Characteristics:

(1) Fast and reliable backup process

(2) The backup process does not interrupt the executing transaction

(3) Ability to save disk space and traffic based on functions such as compression

(4) Automatically implement backup inspection

(5) Fast restore speed

Implementation steps:

① installation package.

Yum Install Xtrabackup

② for a full backup.

Command: Innobackupex--user=root/app/backup/, and Modify permissions.

650) this.width=650; "src=" Https://s4.51cto.com/oss/201711/16/52c050916cd37bbd3a9c3d3952ea66d8.png "title=" 1.png " alt= "52c050916cd37bbd3a9c3d3952ea66d8.png"/>

Modify the MARIADB configuration file to modify the directory path.

650) this.width=650; "src=" Https://s4.51cto.com/oss/201711/16/0cc9c1043a0b5e6b26bf8cad68e3d3be.png "title=" 1.png " alt= "0cc9c1043a0b5e6b26bf8cad68e3d3be.png"/>

Restart the database to view the contents of the database.

650) this.width=650; "src=" Https://s2.51cto.com/oss/201711/16/499cfe1e39bd30f5e5b181d119623d47.png "title=" 1.png " alt= "499cfe1e39bd30f5e5b181d119623d47.png"/>

③ Implementing Incremental backups

Adds data to the backup table of the test database.

650) this.width=650; "src=" Https://s1.51cto.com/oss/201711/16/cb80d0702662264df569612145a5551b.png "title=" 1.png " alt= "Cb80d0702662264df569612145a5551b.png"/>

Command: Innobackupex--incremental/app/backup/--incremental-basedir=/app/backup/2017-11-16_15-49-35 "Try to write absolute path"

650) this.width=650; "src=" Https://s1.51cto.com/oss/201711/16/25d3e3a0ca623c414551bbe1c808e63d.png "title=" 1.png " alt= "25d3e3a0ca623c414551bbe1c808e63d.png"/>

④ for data recovery

Data Recovery readiness:

Command: Innobackupex--apply-log--redo-only/app/backup/2017-11-16_16-42-25/

650) this.width=650; "src=" Https://s3.51cto.com/oss/201711/16/cf707e4097d293f2c6f8e163b49f6a0b.png "title=" 1.png " alt= "Cf707e4097d293f2c6f8e163b49f6a0b.png"/>

Simulates a corrupted database.

Command: Mv/var/lib/mysql/var/lib/mysql.bak

Create MySQL mkdir MySQL

CD mysql/execution command: Innobackupex--copy-back/app/backup/2017-11-16_16-45-10

Restart the database for viewing

650) this.width=650; "src=" Https://s1.51cto.com/oss/201711/16/3610cc1be731cf140faf4607e8b894d6.png "title=" 1.png " alt= "3610cc1be731cf140faf4607e8b894d6.png"/>

Third, the implementation of backup based on LVM snapshot +binlog.

① adding a piece of hard disk

650) this.width=650; "src=" Https://s4.51cto.com/oss/201711/16/6b3216f359f382605cc9ad4c20ac7665.png "title=" 1.png " alt= "6b3216f359f382605cc9ad4c20ac7665.png"/>

② is partitioned and formats the LVM format.

650) this.width=650; "src=" Https://s2.51cto.com/oss/201711/16/77c431a956a9645cf44a84ab9355c709.png "title=" 1.png " alt= "77c431a956a9645cf44a84ab9355c709.png"/>

③ Create a logical volume group and mount it.

650) this.width=650; "src=" Https://s2.51cto.com/oss/201711/18/52a62190077c395a7026fca92414e692.png "title=" 1.png " alt= "52a62190077c395a7026fca92414e692.png"/>

④ move the database file, modify the database configuration file, and reboot.

650) this.width=650; "src=" Https://s2.51cto.com/oss/201711/18/84c0bd944a378b5f7d5bc9c1163c26b7.png "title=" 1.png " alt= "84c0bd944a378b5f7d5bc9c1163c26b7.png"/>

Modify Permissions: Chown-r mysql:mysql/mnt/lvs_snap/

⑤ Create a test database and lock the database.

650) this.width=650; "src=" Https://s5.51cto.com/oss/201711/21/12d738cc3933e0e84e57ca189cc12cfc.png "title=" 1.png " alt= "12d738cc3933e0e84e57ca189cc12cfc.png"/>

⑥ a snapshot of the database and unlocks all tables.

650) this.width=650; "src=" Https://s4.51cto.com/oss/201711/21/7d4e721adb302dc0e279b81b3b5bc699.png "title=" 1.png " alt= "7d4e721adb302dc0e279b81b3b5bc699.png"/>

⑦ mount the snapshot that was created and package its contents.

Mkdir/mnt/test_snap-p

mount/dev/mysql/test-snap/mnt/test_sanp/

Tar cvf/app/mysqlbackup.tar/mnt/tset_snap/

650) this.width=650; "src=" Https://s1.51cto.com/oss/201711/21/c8b9aa54910edf43d64c631925d999e6.png "title=" 1.png " alt= "C8b9aa54910edf43d64c631925d999e6.png"/>⑧ cancel mount, delete snapshot.

Umount/mnt/test_snap

Rm-rf/dev/mysql/test-snap

650) this.width=650; "src=" Https://s4.51cto.com/oss/201711/21/7b2ece8dce1ba7fead82a798062face1.png "title=" 1.png " alt= "7b2ece8dce1ba7fead82a798062face1.png"/>

⑨ Delete the database and unzip the compressed package.

rm-rf/mnt/lvm_snap/*

Tar xvf/app/mysqlbackup.tar/mnt/lvm_snap/

650) this.width=650; "src=" Https://s5.51cto.com/oss/201711/21/c08231d5d3c99a55058457d59426aee9.png "title=" 1.png " alt= "C08231d5d3c99a55058457d59426aee9.png"/>

⑩ Restart the service to verify data recovery.

Implement a variety of ways to back up MySQL