Shulou(Shulou.com)06/01 Report--

Preface

Why do I need to back up my data?

Backup type of data

How MySQL backs up data

Issues to be considered in backup

Design an appropriate backup strategy

Actual combat exercise

Use cp for backup

Replicate BINARY LOG backups using mysqldump+

Back up data using lvm2 Snapshot

Backing up with Xtrabackup

Summary

Preface

Let's try to think about what is most important in a production environment. If the hardware of our server is broken, it can be repaired or replaced, and the software problem can be repaired or reinstalled, but what if the data is gone? This is probably the most terrifying thing. I think nothing is more important than data in a production environment. So how can we ensure that the data is not lost or can be quickly recovered after it is lost? As long as you finish reading this article, you should be able to understand the implementation of data backup and recovery in MySQL.

Why do I need to back up my data?

The preface probably explains why the data should be backed up, but we should have a specific understanding of why the data should be backed up.

In the production environment, our database may encounter a variety of accidents, resulting in data loss, which can be divided into the following categories.

Hardware failure

Software failure

natural disaster

******

Misoperation (the largest proportion)

Therefore, in order to recover data after data loss, we need to back up data regularly. The strategy of backing up data needs to be customized according to different application scenarios, and there are roughly several reference values. We can customize the data backup strategy in a specific environment according to these values.

Be able to tolerate how much data is lost

How long will it take to recover the data

Which data needs to be recovered?

Backup type of data

The backup types of data are mainly divided into the following groups according to their own characteristics.

Full backup

Partial backup

Full backup refers to backing up the entire dataset (that is, the entire database), and partial backup refers to backing up part of the dataset (for example, backing up only one table)

Some backups are divided into the following two types

Incremental backup

Differential backup

Incremental backup refers to backing up data that has changed since the last backup (incremental or complete). Features: saving space and restoring hassle

Differential backup refers to the characteristics of data that have changed since the last full backup: wasted space and easier to restore than incremental backup

Schematic diagram

Blob.png

How MySQL backs up data

There are several ways for us to back up data in MySQl

Hot backup

Warm backup

Cold backup

Hot backup means that when the database is backed up, the read and write operations of the database are not affected.

Warm backup means that when the database is backed up, the database read operation can be performed, but the write operation cannot be performed.

Cold backup means that when the database is backed up, the database cannot be read or written, that is, the database will be offline.

Different backup methods in MySQL should also consider whether the storage engine supports it or not.

MyISAM

Hot standby ×

Warm standby √

Cold standby √

InnoDB

Hot standby √

Warm standby √

Cold standby √

After considering the running state of the database when the data is backed up, we also need to consider the backup mode of the data in the MySQL database.

Physical backup

Logical backup

Physical backup is generally achieved by directly packaging and copying data files of the database through commands such as tar,cp.

Logical backup generally means exporting data from the database and saving the backup through specific tools (logical backup loses data precision)

Issues to be considered in backup

There are a few more issues we need to consider before customizing the backup strategy

What do we need to back up?

In general, the data we need to back up can be divided into the following categories

data

Binary log, InnoDB transaction log

Code (stored procedures, stored functions, triggers, event schedulers)

Server profile

Backup tool

Here we list several commonly used backup tools

Mysqldump: logical backup tool for all storage engines, supporting warm backup, full backup, partial backup, hot backup for InnoDB storage engine

Cp, tar and other archive replication tools: physical backup tool, suitable for all storage engines, cold backup, full backup, partial backup

Lvm2 snapshot: almost hot standby, backup with file system management tools

Mysqlhotcopy: a tool that doesn't live up to its name, almost cold standby, and only supports MyISAM storage engine.

Xtrabackup: a very powerful InnoDB/XtraDB hot backup tool that supports full backup and incremental backup, provided by percona

Design an appropriate backup strategy

According to different scenarios, we should make different backup strategies to back up the database. In general, there are three backup strategies.

Copy database files directly with cp,tar

Mysqldump+ replication BIN LOGS

Lvm2 Snapshot + replication BIN LOGS

Xtrabackup

The above solutions are aimed at different scenarios.

If the amount of data is small, you can use the first method to copy the database file directly.

If the amount of data is OK, you can use the second way, first use mysqldump to make a full backup of the database, and then back up BINARY LOG regularly to achieve the effect of incremental backup.

If the amount of data is mediocre and does not unduly affect the operation of the business, you can use the third way to back up the data files using the snapshot of lvm2, and then back up BINARY LOG regularly to achieve the effect of incremental backup.

If the amount of data is large and does not unduly affect the operation of the business, you can use the fourth way, after using xtrabackup for full backup, use xtrabackup for incremental backup or differential backup regularly.

Actual combat exercise

Use cp for backup

We are using a version of mysql-5.1 installed using yum, using a dataset of an employee database found on the network

View the information of the database

Mysql > SHOW DATABASES; # to view the current database, our database is employees

+-+

| | Database |

+-+

| | information_schema |

| | employees |

| | mysql |

| | test |

+-+

4 rows in set (0.00 sec)

Mysql > USE employees

Database changed

Mysql > SHOW TABLES; # View the tables in the current library

+-+

| | Tables_in_employees |

+-+

| | departments |

| | dept_emp |

| | dept_manager |

| | employees |

| | salaries |

| | titles |

+-+

6 rows in set (0.00 sec)

Mysql > SELECT COUNT () FROM employees; # due to space, let's just look at the number of lines in employees, which is 300024

+-+

| | COUNT () |

+-+

| | 300024 |

+-+

1 row in set (0.05sec)

Impose a read lock on the database

Mysql > FLUSH TABLES WITH READ LOCK; # imposes read locks on all tables

Query OK, 0 rows affected (0.00 sec)

Backup data files

[root@node1 ~] # mkdir / backup # create a folder to store backup database files

[root@node1 ~] # cp-a / var/lib/mysql/* / backup # copy source data files with reserved permissions

[root@node1 ~] # ls / backup # View the files in the directory

Employees ibdata1 ib_logfile0 ib_logfile1 mysql mysql.sock test

Simulated data loss and recovery

[root@node1 ~] # rm-rf / var/lib/mysql/* # Delete all files in the database

[root@node1 ~] # service mysqld restart # restart MySQL. If it is compiled and installed, it should not be started. If rpm is installed, the database will be reinitialized.

Mysql > SHOW DATABASES; # because we are installed by rpm, connect to MySQL to check, and find that the data is missing!

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | test |

+-+

3 rows in set (0.00 sec)

[root@node1] # rm-rf / var/lib/mysql/ # this step may not be done

[root@node1 ~] # cp-a / backup/ / var/lib/mysql/ # copy the backed up data files back

[root@node1 ~] # service mysqld restart # restart MySQL

# reconnect the data and view

Mysql > SHOW DATABASES; # database has been restored

+-+

| | Database |

+-+

| | information_schema |

| | employees |

| | mysql |

| | test |

+-+

4 rows in set (0.00 sec)

Mysql > USE employees

The number of rows in the mysql > SELECT COUNT () FROM employees; # table has not changed.

+-+

| | COUNT () |

+-+

| | 300024 |

+-+

1 row in set (0.06 sec)

# # complete

Replicate BINARY LOG backups using mysqldump+

We are using a version of mysql-5.1 installed using yum, using a dataset of an employee database found on the network

We make a full backup through mysqldump, then modify the data in the table, and then restore the binary log through binary log. You need to add log_bin=on to the mysql configuration file to enable it.

Introduction to mysqldump command

Mysqldump is a client-side logical backup tool that can generate a SQL statement that reproduces the creation of the original database and table, supports all storage engines, and supports hot backup for InnoDB

Introduction of official documents

# basic syntax format

Shell > mysqldump [options] db_name [tbl_name...] Manual CRATE DATABASES is required for recovery

Shell > mysqldump [options]-- databases db_name... Recovery does not require manual creation of the database

Shell > mysqldump [options]-- all-databases recovery does not require manual database creation

Other options:

-E,-- events: backup event scheduler

-R,-- routines: backup stored procedures and stored functions

-- triggers: trigger for backing up tables;-- skip-triggers

-- master-date [= value]

1: recorded as CHANGE MASTER TO statements, statements are not commented

2: CHANGE MASTER TO statements recorded as comments

Based on binary restore, you can only restore in full library.

-- flush-logs: log scrolling is performed after the log roll locking table is completed

View the information of the database

Mysql > SHOW DATABASES; # to view the current database, our database is employees

+-+

| | Database |

+-+

| | information_schema |

| | employees |

| | mysql |

| | test |

+-+

4 rows in set (0.00 sec)

Mysql > USE employees

Database changed

Mysql > SHOW TABLES; # View the tables in the current library

+-+

| | Tables_in_employees |

+-+

| | departments |

| | dept_emp |

| | dept_manager |

| | employees |

| | salaries |

| | titles |

+-+

6 rows in set (0.00 sec)

Mysql > SELECT COUNT () FROM employees; # due to space, let's just look at the number of lines in employees, which is 300024

+-+

| | COUNT () |

+-+

| | 300024 |

+-+

1 row in set (0.05sec)

Back up the database using mysqldump

[root@node1 ~] # mysql-e 'SHOW MASTER STATUS' # View the status of the current binary file and record the number of position

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000003 | 106 | |

+-+

[root@node1 ~] # mysqldump-- all-databases-- lock-all-tables > backup.sql # backup the database to the backup.sql file

Mysql > CREATE DATABASE TEST1; # create a database

Query OK, 1 row affected (0.00 sec)

Mysql > SHOW MASTER STATUS; # write down the current position

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000003 | 191 | |

+-+

1 row in set (0.00 sec)

[root@node1 ~] # cp / var/lib/mysql/mysql-bin.000003 / root # backup binaries

[root@node1 ~] # service mysqld stop # stop MySQL

[root@node1 ~] # rm-rf / var/lib/mysql/* # Delete all data files

[root@node1 ~] # service mysqld start # starts MySQL. If it is compiled and installed, it should not be started (need to be reinitialized). If rpm is installed, the database will be reinitialized.

Mysql > SHOW DATABASES; # check the database, data is missing!

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | test |

+-+

3 rows in set (0.00 sec)

Mysql > SET sql_log_bin=OFF; # close the binary log for the time being

Query OK, 0 rows affected (0.00 sec)

Mysql > source backup.sql # recovery time, depending on the database time

Mysql > SET sql_log_bin=ON; turn on binary log

Mysql > SHOW DATABASES; # database recovery, but missing TEST1

+-+

| | Database |

+-+

| | information_schema |

| | employees |

| | mysql |

| | test |

+-+

4 rows in set (0.00 sec)

[root@node1 ~] # mysqlbinlog-- start-position=106-- stop-position=191 mysql-bin.000003 | mysql employees # recover data through binary log increments

Mysql > SHOW DATABASES; # now TEST1 appears!

+-+

| | Database |

+-+

| | information_schema |

| | TEST1 |

| | employees |

| | mysql |

| | test |

+-+

5 rows in set (0.00 sec)

# complete

Back up data using lvm2 Snapshot

Let's review the knowledge of lvm2-snapshot before we do the experiment.

LVM snapshot is simply to save the metadata of all files in the source partition of the snapshot at a point in time. If the source file does not change, then the corresponding file accessing the snapshot volume points directly to the source file of the source partition. If the source file changes, the corresponding file in the snapshot volume will not change. Snapshot volumes are mainly used for auxiliary backup files. Here is only a brief introduction, click to view the detailed introduction

Deploy the lvm environment

Add a hard disk; here we directly implement the hot plug of the SCSI hard disk. First, add a hard disk to the virtual machine without rebooting.

[root@node1 ~] # ls / dev/sd* # only have the following hard drives, but if we don't reboot, we can let the system recognize the newly added hard drives.

/ dev/sda / dev/sda1 / dev/sda2

[root@node1 ~] # echo'--- > / sys/class/scsi_host/host0/scan

[root@node1 ~] # echo'--- > / sys/class/scsi_host/host1/scan

[root@node1 ~] # echo'--- > / sys/class/scsi_host/host2/scan

[root@node1] # ls / dev/sd* # look! Sdb identified it.

/ dev/sda / dev/sda1 / dev/sda2 / dev/sdb

[root@node1 ~] # fdisk / dev/sdb # partition

Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel

Building a new DOS disklabel with disk identifier 0xd353d192.

Changes will remain in memory only, until you decide to write them.

After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w (rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to

Switch off the mode (command 'c') and change display units to

Sectors (command'u').

Command (m for help): n

Command action

E extended

P primary partition (1-4)

P

Partition number (1-4): 1

First cylinder (1-2610, default 1):

Using default value 1

Last cylinder, + cylinders or + size {KMagne Mpeng} (1-2610, default 2610): + 15G

Command (m for help): t

Selected partition 1

Hex code (type L to list codes): 8e

Changed system type of partition 1 to 8e (Linux LVM)

Command (m for help): W

The partition table has been altered!

Calling ioctl () to re-read partition table.

Syncing disks.

You have new mail in / var/spool/mail/root

[root@node1] # partx-a / dev/sdb

BLKPG: Device or resource busy

Error adding partition 1

# # creating logical Volume

[root@node1 ~] # pvcreate / dev/sdb1

Physical volume "/ dev/sdb1" successfully created

[root@node1 ~] # vgcreate myvg / dev/sdb1

Volume group "myvg" successfully created

[root@node1] # lvcreate-n mydata-L 5G myvg

Logical volume "mydata" created.

[root@node1 ~] # mkfs.ext4 / dev/mapper/myvg-mydata # format

[root@node1 ~] # mkdir / lvm_data

[root@node1 ~] # mount / dev/mapper/myvg-mydata / lvm_data # Mount to / lvm_data

[root@node1 ~] # vim / etc/my.cnf # modify the datadir of the mysql configuration file as follows

Datadir=/lvm_data

[root@node1 ~] # service mysqld restart # restart MySQL

# re-import employees database # skip #

View the information of the database

Mysql > SHOW DATABASES; # to view the current database, our database is employees

+-+

| | Database |

+-+

| | information_schema |

| | employees |

| | mysql |

| | test |

+-+

4 rows in set (0.00 sec)

Mysql > USE employees

Database changed

Mysql > SHOW TABLES; # View the tables in the current library

+-+

| | Tables_in_employees |

+-+

| | departments |

| | dept_emp |

| | dept_manager |

| | employees |

| | salaries |

| | titles |

+-+

6 rows in set (0.00 sec)

Mysql > SELECT COUNT () FROM employees; # due to space, let's just look at the number of lines in employees, which is 300024

+-+

| | COUNT () |

+-+

| | 300024 |

+-+

1 row in set (0.05sec)

Create a snapshot volume and back up

Mysql > FLUSH TABLES WITH READ LOCK; # Lock all tables

Query OK, 0 rows affected (0.00 sec)

[root@node1 lvm_data] # lvcreate-L 1G-n mydata-snap-p r-s / dev/mapper/myvg-mydata # create a snapshot volume

Logical volume "mydata-snap" created.

Mysql > UNLOCK TABLES; # unlock all tables

Query OK, 0 rows affected (0.00 sec)

[root@node1 lvm_data] # mkdir / lvm_snap # create a folder

[root@node1 lvm_data] # mount / dev/myvg/mydata-snap / lvm_snap/ # Mount snap

Mount: block device / dev/mapper/myvg-mydata--snap is write-protected, mounting read-only

[root@node1 lvm_data] # cd / lvm_snap/

[root@node1 lvm_snap] # ls

Employees ibdata1 ib_logfile0 ib_logfile1 mysql mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 mysql-bin.index test

[root@node1 lvm_snap] # tar cf / tmp/mysqlback.tar * # package the file to / tmp/mysqlback.tar

[root@node1 ~] # umount / lvm_snap/ # Uninstall snap

[root@node1 ~] # lvremove myvg mydata-snap # Delete snap

Recover data

[root@node1 lvm_snap] # rm-rf / lvm_data/*

[root@node1 ~] # service mysqld start # starts MySQL. If it is compiled and installed, it should not be started (need to be reinitialized). If rpm is installed, the database will be reinitialized.

Mysql > SHOW DATABASES; # check the database, data is missing!

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | test |

+-+

3 rows in set (0.00 sec)

[root@node1 ~] # cd / lvm_data/

[root@node1 lvm_data] # rm-rf * # Delete all files

[root@node1 lvm_data] # tar xf / tmp/mysqlback.tar # extract the backup database to this folder

[root@node1 lvm_data] # ls # View the current file

Employees ibdata1 ib_logfile0 ib_logfile1 mysql mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 mysql-bin.index test

Mysql > SHOW DATABASES; # data is restored

+-+

| | Database |

+-+

| | information_schema |

| | employees |

| | mysql |

| | test |

+-+

4 rows in set (0.00 sec)

# # complete

Backing up with Xtrabackup

In order to demonstrate better, we use the version of mariadb-5.5 this time, using xtrabackup to use InnoDB can make the most of it, and each table of InnoDB must use a separate tablespace, we need to add innodb_file_per_table = ON to the configuration file to open it.

Download and install xtrabackup

We install it here through wget percona's official rpm package.

[root@node1 ~] # wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.4/binary/redhat/6/x86_64/percona-xtrabackup-2.3.4-1.el6.x86_64.rpm

[root@node1 ~] # yum localinstall percona-xtrabackup-2.3.4-1.el6.x86_64.rpm # requires EPEL source

Xtrabackup introduction

Xtrabackup is a mysql database backup tool provided by percona. According to officials, it is the only open source tool in the world that can provide hot backup for innodb and xtradb databases. Features:

The backup process is fast and reliable

The backup process does not interrupt the transaction that is in progress

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

Automatic backup verification

Fast reduction speed

Extracted from Brother Ma's document

Full backup based on xtrabackup

We use xtrabackup's front-end configuration tool innobackupex to achieve a full backup of the database.

When using innobackupex backup, xtrabackup is called to back up all InnoDB tables, copying all files related to table structure definition (.frm), and MyISAM, MERGE, CSV, and ARCHIVE tables, as well as files related to triggers and database configuration file information, which are saved to a directory named by time.

Backup process

[root@node1 ~] # mkdir / extrabackup # create backup directory

[root@node1 ~] # innobackupex-- user=root / extrabackup/ # backup data

# prompt complete to indicate success *

[root@node1 ~] # ls / extrabackup/ # see backup directory

2016-04-27 07-30-48

In general, after the backup is complete, the data cannot be used for restore operations because the backed up data may contain transactions that have not yet been committed or transactions that have been committed but have not been synchronized to the data file. Therefore, the data files are still inconsistent at this time, so we need to "prepare" a full backup.

[root@node1 ~] # innobackupex-- apply-log / extrabackup/2016-04-27upload 07-30-48 / # specify the directory of backup files

# in general, the end of the following three lines represents success *

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 369661462

160427 07:40:11 completed OK!

[root@node1] # cd / extrabackup/2016-04-277-30-48 /

[root@node1 2016-04-27007-30-48] # ls-hl # View backup files

Total 31M

-rw-r- 1 root root 386 Apr 27 07:30 backup-my.cnf

Drwx- 2 root root 4.0K Apr 27 07:30 employees

-rw-r- 1 root root 18m Apr 27 07:40 ibdata1

-rw-r--r-- 1 root root 5.0M Apr 27 07:40 ib_logfile0

-rw-r--r-- 1 root root 5.0M Apr 27 07:40 ib_logfile1

Drwx- 2 root root 4.0K Apr 27 07:30 mysql

Drwx- 2 root root 4.0K Apr 27 07:30 performance_schema

Drwx- 2 root root 4.0K Apr 27 07:30 test

-rw-r- 1 root root 27 Apr 27 07:30 xtrabackup_binlog_info

-rw-r--r-- 1 root root 29 Apr 27 07:40 xtrabackup_binlog_pos_innodb

-rw-r- 1 root root 117 Apr 27 07:40 xtrabackup_checkpoints

-rw-r- 1 root root 470 Apr 27 07:30 xtrabackup_info

-rw-r- 1 root root 2.0M Apr 27 07:40 xtrabackup_logfile

Recover data

[root@node1 ~] # rm-rf / data/* # Delete data files

* you can restore without starting the database *

[root@node1] # innobackupex-- copy-back / extrabackup/2016-04-27073048 / # restore data and remember how to use it

# We are compiling and installing mariadb here, so we need to do something #

[root@node1 data] # killall mysqld

[root@node1] # chown-R mysql:mysql. / *

[root@node1 ~] # ll / data/ # data recovery

Total 28704

-rw-rw---- 1 mysql mysql 16384 Apr 27 07:43 aria_log.00000001

-rw-rw---- 1 mysql mysql 52 Apr 27 07:43 aria_log_control

-rw-rw---- 1 mysql mysql 18874368 Apr 27 07:43 ibdata1

-rw-rw---- 1 mysql mysql 5242880 Apr 27 07:43 ib_logfile0

-rw-rw---- 1 mysql mysql 5242880 Apr 27 07:43 ib_logfile1

-rw-rw---- 1 mysql mysql 264 Apr 27 07:43 mysql-bin.000001

-rw-rw---- 1 mysql mysql 19 Apr 27 07:43 mysql-bin.index

-rw-r- 1 mysql mysql 2166 Apr 27 07:43 node1.anyisalin.com.err

[root@node1 data] # service mysqld restart

MySQL server PID file could not be found! [FAILED]

Starting MySQL.. [OK]

MariaDB [(none)] > SHOW DATABASES; # View the database, and it has been restored

+-+

| | Database |

+-+

| | information_schema |

| | employees |

| | mysql |

| | performance_schema |

| | test |

+-+

5 rows in set (0.00 sec

Incremental backup

# create two databases for testing #

MariaDB [(none)] > CREATE DATABASE TEST1

Query OK, 1 row affected (0.00 sec)

MariaDB [(none)] > CREATE DATABASE TEST2

Query OK, 1 row affected (0.00 sec)

[root@node1] # innobackupex-- incremental / extrabackup/-- incremental-basedir=/extrabackup/2016-04-277-30-48 /

[root@node1] # ls / extrabackup/2016-04-270757-22 / # View backup files

Total 96

-rw-r- 1 root root 386 Apr 27 07:57 backup-my.cnf

Drwx- 2 root root 4096 Apr 27 07:57 employees

-rw-r- 1 root root 49152 Apr 27 07:57 ibdata1.delta

-rw-r- 1 root root 44 Apr 27 07:57 ibdata1.meta

Drwx- 2 root root 4096 Apr 27 07:57 mysql

Drwx- 2 root root 4096 Apr 27 07:57 performance_schema

Drwx- 2 root root 4096 Apr 27 07:57 test

Drwx- 2 root root 4096 Apr 27 07:57 TEST1

Drwx- 2 root root 4096 Apr 27 07:57 TEST2

-rw-r- 1 root root 21 Apr 27 07:57 xtrabackup_binlog_info

-rw-r- 1 root root 123 Apr 27 07:57 xtrabackup_checkpoints

-rw-r- 1 root root 530 Apr 27 07:57 xtrabackup_info

-rw-r- 1 root root 2560 Apr 27 07:57 xtrabackup_logfile

BASEDIR refers to the directory where the full backup is located, and after the execution of this command, the innobackupex command creates a new time-named directory in the / extrabackup directory to hold all incremental backup data. In addition, when you perform an incremental backup again after an incremental backup, its-- incremental-basedir should point to the directory where the last incremental backup was located.

It is important to note that incremental backups can only be applied to InnoDB or XtraDB tables, and for MyISAM tables, incremental backups are actually performed as full backups.

Organize incremental backups

[root@node1] # innobackupex-- apply-log-- redo-only / extrabackup/2016-04-277-30-48 /

[root@node1] # innobackupex-- apply-log-- redo-only / extrabackup/2016-04-279 07-30-48 /-incremental-dir=/extrabackup/2016-04-27 7-5

7-22 /

Recover data

[root@node1 ~] # rm-rf / data/* # Delete data

[root@node1] # innobackupex-- copy-back / extrabackup/2016-04-270730-48 / # after collating incremental backups, you can restore them directly through full backups.

[root@node1] # chown-R mysql.mysql / data/

[root@node1 ~] # ls / data/-l

Total 28732

-rw-rw---- 1 mysql mysql 8192 Apr 27 08:05 aria_log.00000001

-rw-rw---- 1 mysql mysql 52 Apr 27 08:05 aria_log_control

Drwx- 2 mysql mysql 4096 Apr 27 08:05 employees

-rw-r- 1 mysql mysql 18874368 Apr 27 08:05 ibdata1

-rw-r- 1 mysql mysql 5242880 Apr 27 08:05 ib_logfile0

-rw-r- 1 mysql mysql 5242880 Apr 27 08:05 ib_logfile1

Drwx- 2 mysql mysql 4096 Apr 27 08:05 mysql

-rw-rw---- 1 mysql mysql 245 Apr 27 08:05 mysql-bin.000001

-rw-rw---- 1 mysql mysql 19 Apr 27 08:05 mysql-bin.index

-rw-r- 1 mysql mysql 1812 Apr 27 08:05 node1.anyisalin.com.err

-rw-rw---- 1 mysql mysql 5 Apr 27 08:05 node1.anyisalin.com.pid

Drwx- 2 mysql mysql 4096 Apr 27 08:05 performance_schema

Drwx- 2 mysql mysql 4096 Apr 27 08:05 test

Drwx- 2 mysql mysql 4096 Apr 27 08:05 TEST1

Drwx- 2 mysql mysql 4096 Apr 27 08:05 TEST2

-rw-r- 1 mysql mysql 29 Apr 27 08:05 xtrabackup_binlog_pos_innodb

-rw-r- 1 mysql mysql 530 Apr 27 08:05 xtrabackup_info

MariaDB [(none)] > SHOW DATABASES; # data restore

+-+

| | Database |

+-+

| | information_schema |

| | TEST1 |

| | TEST2 |

| | employees |

| | mysql |

| | performance_schema |

| | test |

+-+

7 rows in set (0.00 sec)

# there are still many powerful functions about xtrabackup that have not been described. If you are interested, you can read the official documents.

Summary

Backup method backup speed recovery speed convenience function is generally used for

Cp is generally fast, low flexibility, very weak a small amount of data backup

Mysqldump is slow and slow, but can ignore the difference of storage engine. General backup of small and medium-sized data

Lvm2 snapshots are generally fast, support almost hot backup, and are generally fast for small and medium-sized data backups.

Xtrabackup implements innodb hot backup faster and faster, and requires powerful and large-scale backup to the storage engine.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.