Database backup and recovery of Linux operation and maintenance learning

Today brings you a tutorial on database backup and recovery, when our hands are cheap or do not know what causes the database crash, or even delete the database, you can not run it? So it is necessary for us to do daily backups and related post-disaster reconstruction.

So what are the ways to back up? One, file backup, is very stupid to all of the files are copied to our prepared all kinds of storage devices, but this method lacks timeliness, need to cold standby, is to stop the service, slowly copy, and only function with a small amount of data backup, too many words can not fit, functional thieves weak, two, logical backup, Re-export data in the database and other backup, the advantage is that the editor is simple, easy to restore, the disadvantage is that the backup is large data, backup is slow, so we have launched a full backup and differential backup and incremental backup, full backup as the name implies is the database completely back down, But we can combine it with differential backups and incremental backups, where the backup data is the newly added and modified data after the last full backup, the incremental backup is the data that was added and modified after the last backup, and some of the small partners are blindfolded. Then give you a picture can be very good to reflect the three ways to backup the difference.

650) this.width=650; "src=" Https://s3.51cto.com/oss/201711/16/00624eba8083efe2c0c8fe9ad48b63e3.png "title=" Uc20171116095813.png "width=" "height=" 387 "border=" 0 "hspace=" 0 "vspace=" 0 "style=" WIDTH:600PX;HEIGHT:387PX; "alt = "00624eba8083efe2c0c8fe9ad48b63e3.png"/>

In general our full backup and incremental backup, is similar to the stair structure, a first-level backup, when we recover, of course, is also a first-level recovery, while the differential backup is not, it and the full backup, is relative to the full backup, when the day of data loss, Use full backup directly and backup of the previous day to quickly find our data.

Let's experiment with it today. Full backup + incremental backup.

Full backup We are using the Mysqldump tool, incremental backup we are using binary log method implementation, then how to do?

Lab Environment: CENTOS7 virtual machine, installation complete MySQL

The first step: prepare to back up the directory, change the owner, belong to the group

Mkdir/backup

Chown mysql:mysql/backup/

Step two: Prepare to back up the database and table

Mysql

Create Database Qiangge;

Use Qiangge;

Create Tabel Zao (id int not null,name char (20));

Step three: Make a full backup

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

#--all-databases: All databases

#--lock-all-tables: Lock All tables (in order to maintain data consistency)

#--flush-log: Flush binary files from memory to hard disk

#--master-data=2: Exported statement chage Master to is commented

Fourth step: Inserting data into the table

Mysql

Use Qiangge;

INSERT into Zao values ("Jerry"), ("Tom"), ((), ' Dog '), ("Cat");

Fifth step: Make incremental backup, back up the binary log

Vim/etc/my.cnf

[Mysqld]

Log-bin=bl

Systemctl Restart MARIADB

Mysqlbinlog--start-position=num1--stop-position=num2/var/lib/mysql/bl.000002 >/backup/' Date +%F-%T '-bl.sql

#NMU1, NUM2 are looking at bl.000002, the specific method is Mysqlbinlog bl.000002, if bl.000002 did not, then look for Bl.000001,at NUM1 beginning, sandwiched between SQL statements, commit/*!*/ The end of which is End_log_pos NUM2.

Sixth step: Continue inserting data, delete database without backup, simulate misoperation

Mysql

Use Qiangge;

INSERT into Zao values (, ' Mile Fly '), (' USAF ');

Drop database Qiangge;

Seventh Step: Data recovery, because finally we have no backup to delete the database, so we first need to protect the last binary log, see the position value before the delete operation: Mysqlbinlog binary file.

Eighth step: Back up the binary log of the last operation

Mysqlbinlog--start-position=num3--stop-position=nmu4 binary log >/backup/' date +%f-%t '-bl2.sql

Nineth Step: Import the previously backed up database

MySQL </backup/full backup log

MySQL </backup/binary log

MySQL </backup/the last backup

Tenth step: Review the recovery of the database

Mysql

SELECT * from Qiangge.zao;

Complete. We can see that we have successfully completed the recovery of the database.

Then, our second experiment is based on a small tool Xtrabackup, which has a lot of advantages such as simpler command and backup speed, fast restore, reliable backup, save disk and so on, also can automatically implement backup inspection.

First, we need to install a package, yum source can

Yum Install percona-xtrabackup

Then create a new directory to store the backup data specifically

Mkdir/backup

1 Full backup

Innobackupex--user=root/backup/

2 Adding data

Mysql

Create Database Qiangge;

Use Qiangge;

CREATE table six (ID int,name char (20));

INSERT into six values (1, ' haha '), (2, ' hehe ');

3 Incremental Backup

Innobackupex--incremental/backup/--incremental-basedir=/backup/2017-11-16_23-55-11/

4 Deleting a database

Mysql

Drop database Qiangge;

5 Preparation before data recovery

Why do you have to prepare it? Because the data that is backed up may contain transactions that have not yet been committed or that have been committed but have not been synchronized to the data file. Therefore, the data file still handles the inconsistent state, so the committed transaction is replayed, merged into a full backup, and the transaction is not committed for rollback.

Innobackupex--apply-log--redo-only/backup/2017-11-16_23-55-11

Innobackupex--apply-log--redo-only/backup/2017-11-16_23-55-11--incremental-dir=/backup/2017-11-16_23-57-12

#一定要用绝对路径, here is the pit, the author at that time cut to the directory, can be complete, thought can use relative path, the result of the half-afternoon error

6 recovery phase, data recovery

Innobackupex--copy-back/backup/2017-11-16_23-55-11

At this point it will remind us that/var/lib/mysql is non-empty, why is it? Because the directory is the database data, it will directly restore all the data to the directory, we directly delete this directory.

rm-rf/var/lib/mysql/*

Execute the command again and find that there is still a problem, we change the owner of the directory, the owning group

Chown-r Mysql.mysql/var/lib/mysql

Systemctl Restart MySQL

Then success, into a glance, the database is completely OK!

Finally, we're going to have another lvm2. The combination of snapshots and binlog is the data backup and recovery used in conjunction with logical volume snapshots and binary logs.

1 First add a hard disk as a logical volume

2 Adding a physical volume

Pvcreate/dev/sdb1

3 Adding a volume group

Vgcreate MYVG/DEV/SDB1

4 Adding logical volumes

Lvcreate-n mydata-l 5G MYVG

5 Formatting logical Volumes

Mkfs.ext4/dev/mapper/myvg-mydata

6 Mounting Logical volumes

Mount/dev/mapper/myvg-mydata/lvm_data

7 Modify the MySQL configuration file to make the data file Datadir=/lvm_data on the logical volume

8 Restart MARIADB Service

9 Creating a database to operate

Mysql

Create Database Qiangge;

Use Qiangge;

CREATE table six (ID int,name char (20));

INSERT into six values (1, ' Six '), (2, ' sixsix ');

10 Locking the table

Mysql

Flush tables read lock;

11 Creating a Snapshot Volume

Lvcreate-l 1g-n mydata-snap-p r-s/dev/mapper/myvgmydata

12 Unlocking All Tables

Mysql

Unlock tables;

13 Mounting a Snapshot volume

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

14 Packaging physical Backups

Tar Cvf/tmp/mysqlback.tar./*

15 Uninstalling Snap

umount/lvm_snap/

16 Deleting Snap

Lvremove MYVG Mydata-snap

17 Deleting a database

rm-rf/lvm_data/*

18 Extracting the recovery database

Tar-xvf/tmp/mysqlback.tar./

19 Verifying that data is restored

The above three ways to cope with the daily management of backup recovery, then the specific difference between them? The author sums up a bit

650) this.width=650; "src=" Https://s3.51cto.com/oss/201711/16/f5bc8b2b86ab2a5edebf3ee25d0efbad.png "title=" Uc20171116164848.png "width=" "height=" "border=" 0 "hspace=" 0 "vspace=" 0 "style=" width:700px;height:115px; "alt = "F5bc8b2b86ab2a5edebf3ee25d0efbad.png"/>

Database backup and recovery of Linux operation and maintenance learning