MySQL (a): Mysqldump, LVM2, Xtrabackup respectively based on three ways to achieve backup recovery

three ways to restore MySQL backup based on mysqldump, LVM2, Xtrabackup, respectively

First, the use of mysqldump implementation of logical backup operations

1. In/etc/my.cnf, add the following to open the binary log

Innodb-file-per-table = On

Skip-name-resolve=on

Log_bin=/var/log/maradb/binlog

2. Backup data, lock table, binary log to do scrolling, change MASTER to set to 2 Let statement be commented

[Email protected] ~]# mysqldump-uroot-hlocalhost zrs--lock-tables--flush-logs--master-data=2 >/tmp/zrs.1.sql

View Log storage location, 2, that is, the log scrolling

[Email protected] ~]# ls/var/log/mariadb

binlog.000001 binlog.000002 Binlog.index Mariadb.log

Add a database structure that displays the creation and use of the database--databases

[Email protected] ~]# mysqldump-uroot-hlocalhost--databases zrs--lock-tables--flush-logs--master-data=2 >/tmp/z Rs.2.sql

Backing up the entire server's database--all-databases

[Email protected] ~]# mysqldump-uroot-hlocalhost--all-databases--lock-tables--flush-logs--master-data=2 >/tmp/ Myserver.1.sql

3. Analog failure

Process crashes

[Email protected] ~]# killall mysqld mysqld_safe

All library files are missing

[Email protected] ~]# rm-rf/var/lib/mysql/*

4. Restoring a backup

Start the service first

[Email protected] ~]# systemctl start mariadb

To view the library files that have been built for repair

[Email protected] ~]# ls/var/lib/mysql/

aria_log.00000001 ibdata1 ib_logfile1 mysql.sock test

Aria_log_control IB_LOGFILE0 MySQL Performance_schema

Because the process of recovery also generates a command record, resulting in data changes, forcing it to not log into the binary

[[email protected] ~]# MySQL

Only the current session can be set

MariaDB [(None)]> set @ @session. Sql_log_bin=off;

Recovering data

MariaDB [(none)]> Source/tmp/myserver.1.sql

And then turn on the binary record.

MariaDB [zrs]> SET @ @session. Sql_log_bin=on;

5. After recovering the data, you need to delete the backup, and then back up again, the backup data is used only once.

--------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------

Second, the use of lvm2 to achieve physical backup operation

Warm backup (almost hot standby)

1. Create a partition first

[Email protected] ~]# FDISK/DEV/SDA

Command (enter M for help): N

Partition Type:

P Primary (2 primary, 0 extended, 2 free)

E Extended

Select (default P):

Using Default Response P

Partition code (3,4, default 3):

Start sector (109684736-167772159, default = 109684736):

The default value of 109684736 will be used

Last sector, + sector or +size{k,m,g} (109684736-167772159, default = 167772159): +20g

Partition 3 is set to Linux type and size is set to GiB

Command (enter M for help): t

Partition code (1-3, default 3): 3

Hex code (input L list all code): 8e

Changed the type of Partition "Linux" to "Linux LVM"

Command (enter M for help): W

Partprobe, please.

[Email protected] ~]# PARTPROBE/DEV/SDA

See if the partition you just created exists

[Email protected] ~]# cat/proc/partitions

Do physical volumes

[Email protected] ~]# Pvcreate/dev/sda3

Physical volume "/dev/sda3" successfully created.

Make a roll group

[Email protected] ~]# vgcreate Datavg/dev/sda3

Volume Group "Datavg" successfully created

Create a logical volume, specify a name

[Email protected] ~]# lvcreate-l 8g-n mydata datavg

Logical volume "MyData" created.

View

[[email protected] ~]# LVS

LV VG Attr lsize Pool Origin data% meta% Move Log cpy%sync Convert

Root Centos-wi-ao----50.00g

Swap Centos-wi-ao----2.00g

MyData datavg-wi-a-----8.00g

[Email protected] ~]# ls/dev/mapper/

Centos-root Centos-swap Control Datavg-mydata

[Email protected] ~]# ls/dev/datavg/

MyData

Formatting

[Email protected] ~]# mke2fs-b 2048-t ext4/dev/datavg/mydata

[Email protected] ~]# Blkid/dev/datavg/mydata

/dev/datavg/mydata:uuid= "95e9edc9-257a-49c6-b44b-235f18af371d" type= "Ext4"

Create a Directory

[Email protected] ~]# Mkdir-pv/data/mydata

Mount this LVM to the created directory, and in the configuration file, add the following

[Email protected] ~]# Vim/etc/fstab

Uuid= "95e9edc9-257a-49c6-b44b-235f18af371d"/data/mydata ext4 defaults,acl 0 0

View

[Email protected] ~]# mount-a

[Ro[email protected] ~]# DF

File system 1k-block already used% mount point available

/dev/mapper/centos-root 52403200 4552636 47850564 9%/

...

...

...

/dev/mapper/datavg-mydata 8190760 12308 7750830 1%/data/mydata

Change the owner group of the Directory

[Email protected] ~]# chown-r mysql.mysql/data/*

Modifying a configuration file

[Email protected] ~]# VIM/ETC/MY.CNF

[Mysqld]

Datadir=/data/mydata

Socket=/data/mydata/mysql.sock

2. Analog failure

Process crashes

[Email protected] ~]# killall mysqld mysqld_safe

All library files are missing

[Email protected] ~]# rm-rf/var/lib/mysql/*

3. Restoring a Backup

Start the service first

[Email protected] ~]# systemctl start mariadb

When you change the sock file, enter MySQL will prompt the error, you can set the configuration file as follows.

[[email protected] ~]# MySQL

ERROR 2002 (HY000): Can ' t connect to local MySQL server through socket '/var/lib/mysql/mysql.sock ' (2)

[[email protected] ~]# vim. my.cnf

[Client]

socket= '/data/mydata/mysql.sock '

Turn off binary logging, restore backups, and turn on binary logging.

MariaDB [(None)]> set @ @session. Sql_log_bin=off;

MariaDB [(none)]> source/tmp/myserver.1.sql;

MariaDB [zrs]> SET @ @session. Sql_log_bin=on;

To test, first create a table, insert the data

MariaDB [zrs]> CREATE table if not exists tbl5 (UID tinyint unsigned NOT NULL auto_increment primary key,username Varch AR (+), age tinyint unsigned);

MariaDB [zrs]> INSERT INTO TBL5 (username,age) VALUES (' Zhang san ', +, (' Li si ', 27);

Add a read lock to the table

MariaDB [zrs]> flush tables with read lock;

To create a snapshot volume

[Email protected] ~]# lvcreate-l 5g-s-P r-n data_backup/dev/datavg/mydata

Using default Stripesize 64.00 KiB.

Logical volume "Data_backup" created.

Release the lock immediately.

MariaDB [zrs]> unlock tables;

Create a Directory

[Email protected] ~]# Mkdir-pv/mnt/backup

Mount

[Email protected] ~]# Mount/dev/datavg/data_backup/mnt/backup

View

[Email protected] ~]# ls/mnt/backup/

aria_log.00000001 ibdata1 ib_logfile1 mysql.sock test

Aria_log_control ib_logfile0 MySQL Performance_schema zrs

Create a backup directory

[Email protected] ~]# Mkdir/tmp/backup

Back up all files to this directory

[Email protected] ~]# Cd/mnt/backup

[Email protected] backup]# Cp-a */tmp/backup/

Delete unwanted files

[Email protected] backup]# rm-f/tmp/backup/mysql.sock

Log the following binary values for later recovery of data

MariaDB [zrs]> Show Master status;

+---------------+----------+--------------+------------------+

| File | Position | binlog_do_db | binlog_ignore_db |

+---------------+----------+--------------+------------------+

|    binlog.000012 |         759 |            | |

+---------------+----------+--------------+------------------+

4. Analog failure

[Email protected] ~]# killall mysqld mysqld_safe

Mount and delete a snapshot volume

[Email protected] ~]# umount/mnt/backup/

[Email protected] ~]# Lvremove/data/datavg/data_backup

Delete Files in directory

[Email protected] ~]# rm-rf/data/mydata/*

5. Start a physical backup restore below

[Email protected] ~]# cd/tmp/backup/

[Email protected] backup]# Cp-a */data/mydata/

Start the service

[Email protected] backup]# systemctl start mariadb

[[email protected] backup]# MySQL

MariaDB [(None)]> use ZRS;

MariaDB [zrs]> SELECT * from TBL5;

+-----+-----------+------+

| UID | Username | Age |

+-----+-----------+------+

| 1 |   Zhang san| 30 |

| 2 |   Li Si | 27 |

+-----+-----------+------+

found that the new data was not recovered

Using binary log Recovery

[Email protected] ~]# Mysqlbinlog--start-position=759/var/log/mariadb/binlog.000012 >/tmp/lvm.sql

Turn off the binary logging feature

MariaDB [zrs]> SET @ @session. Sql_log_bin=off;

Recovering data using binary logs

MariaDB [zrs]> \. /tmp/lvm.sql

Turn on the binary recording function

MariaDB [zrs]> SET @ @session. Sql_log_bin=on;

--------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------

Third, the use of xtrabackup to achieve physical backup operation

1. Installing the Software

Yum Install Percona-xtrabackup

2.

Create a backup directory

[Email protected] ~]# Mkdir/data/backup

Change the genus Owner

[[email protected] ~]# chown MySQL. /data/backup/

Full backup

[Email protected] ~]# Innobackupex--user=root--host=localhost--socket=/data/mydata/mysql.sock/data/backup/

View, create a directory with a time name

[Email protected] ~]# ls/data/backup/

2018-01-15_16-48-43

In order to make an incremental backup, modify the table contents as follows

MariaDB [zrs]> INSERT INTO TBL5 (username,age) values (' Qi Qi ', 55);

MariaDB [zrs]> Delete from tbl5 where UID between 1 and 2;

Make an incremental backup that indicates which full backup is based on

[Email protected] ~]# Innobackupex--user=root--socket=/data/mydata/mysql.sock--incremental/data/backup/-- incremental-basedir=/data/backup/2018-01-15_16-48-43

View, incremental backup already generated

[Email protected] ~]# ls/data/backup/

2018-01-15_16-48-43 2018-01-15_17-10-47

How to differentiate, view the configuration file

[Email protected] ~]# cat/data/backup/2018-01-15_16-48-43/xtrabackup_checkpoints

Backup_type = full-backuped

FROM_LSN = 0

TO_LSN = 1611662

LAST_LSN = 1611662

Compact = 0

Recover_binlog_info = 0

[Email protected] ~]# cat/data/backup/2018-01-15_17-10-47/xtrabackup_checkpoints

Backup_type = Incremental

FROM_LSN = 1611662

TO_LSN = 1613212

LAST_LSN = 1613212

Compact = 0

Recover_binlog_info = 0

In order to do an incremental backup again, modify the table contents as follows

MariaDB [zrs]> CREATE table if not exists TBL7 (UID tinyint unsigned NOT NULL auto_increment primary key,username VARC Har (+), age tinyint unsigned);

MariaDB [zrs]> INSERT INTO TBL7 (username,age) VALUES (' Name11 ', 98), (' Name22 ', 99);

Make the increment again based on the second incremental backup

[Email protected] ~]# Innobackupex--user=root--socket=/data/mydata/mysql.sock--incremental/data/backup/-- incremental-basedir=/data/backup/2018-01-15_17-10-47

[Email protected] ~]# ls/data/backup/

2018-01-15_16-48-43 2018-01-15_17-10-47 2018-01-15_17-38-09

Delete data

MariaDB [zrs]> Delete from TBL7 where UID between 1 and 2;

Note: If you have additional data modifications after the last backup result set, you also need to rely on the contents of the binary log after the last backup operation to achieve a complete recovery of the data.

3. Analog failure

[Email protected] ~]# killall mysqld mysqld_safe

[Email protected] ~]# Cd/data

[Email protected] data]# RM-RF mydata/*

4. Recovering data

Preparatory work

[Email protected] ~]# Innobackupex--apply-log--redo-only/data/backup/2018-01-15_16-48-43/

[Email protected] ~]# Innobackupex--apply-log--redo-only/data/backup/2018-01-15_16-48-43/--incremental-dir=/data /backup/2018-01-15_17-10-47

[Email protected] ~]# Innobackupex--apply-log/data/backup/2018-01-15_16-48-43/--incremental-dir=/data/backup/ 2018-01-15_17-38-09

Resume work

[Email protected] ~]# Innobackupex--copy-back/data/backup/2018-01-15_16-48-43/

View

[Email protected] ~]# ls/data/mydata/

Ibdata1 Performance_schema Xtrabackup_binlog_pos_innodb ZRS

MySQL Test xtrabackup_info

Because two data is deleted after the second incremental backup, you need to recover with Binlog

[Email protected] ~]# Cat/data/backup/2018-01-15_17-38-09/xtrabackup_binlog_info

binlog.0000131123

Save

[Email protected] ~]# Mysqlbinlog--start-position=1123/var/log/mariadb/binlog.000013 >/tmp/last.sql

Modify Permissions

[[email protected] ~]# chown MySQL. -r/data/mydata/*

Restart Service

[Email protected] data]# systemctl start mariadb

View, the contents of the table are also

MariaDB [zrs]> SELECT * from TBL7;

+-----+----------+------+

| UID | Username | Age |

+-----+----------+------+

| 1 |  Name11 | 98 |

| 2 |  Name22 | 99 |

+-----+----------+------+

Turn off the binary logging feature

MariaDB [zrs]> SET @ @session. Sql_log_bin=off;

Recovery

MariaDB [zrs]> \. /tmp/last.sql

Turn on the binary logging feature

MariaDB [zrs]> SET @ @session. Sql_log_bin=on;

View, the contents of the table are gone.

MariaDB [zrs]> SELECT * from TBL7;

Empty Set (0.01 sec)

--------------------------------------------------------------------------------------------------------------- ---------------------------

Summarize:

Backup and recovery for MySQL:

Mysqldump: Logical backup + Win Bei | Hot spare + binlog

LVM2: Physical backup + Win Bei (almost hot standby) + Binlog

Xtrabackup: Physical backup + Win Bei | hot standby + (full + incremental) + Binlog

Offsite Disaster Preparedness

MySQL (a): Mysqldump, LVM2, Xtrabackup respectively based on three ways to achieve backup recovery