Introduction to commonly used backup and restoration tools of mariadb

I. Significance of backup from the perspective of data security, Database Server disks are all RAID, and Mariadb itself also has master-slave and other disaster recovery mechanisms, but they cannot completely replace backup. Disaster Tolerance and high availability can help us effectively cope

I. Significance of backup from the perspective of data security, Database Server disks are all RAID, and Mariadb itself also has master-slave and other disaster recovery mechanisms, but they cannot completely replace backup. Disaster Tolerance and high availability can help us effectively cope

Ii. Backup Type

1. Whether the database server is online based on the backup:

2. Based on the backup data set:

4. Back up the entire data or only the changed data during Backup:

5. Backup policy:

6. Backup object

Iii. Common backup tools

Mariadb itself provides us with mysqldump and mysqlbinlog backup tools, and percona also provides us with powerful Xtrabackup, coupled with open-source mydumper, there are also master-slave synchronization-based delayed backup, slave database cold backup, and backup based on file system snapshots. In fact, these methods can be properly matched to meet our needs. The backup itself is for recovery, so the backup method that enables us to quickly and accurately recover after a fault is the most suitable for us. Of course, it can save money and save time, that's perfect. Below we will compare several backup tools, discuss their respective application scenarios, and briefly describe how to use them.

1. mysqldump

(1) Advantages and Disadvantages of mysqldump

Mysqldump is the simplest logical backup method (working in a single thread ). When backing up a myisam table, if you want to obtain consistent data, You need to lock the table, which is simple and crude. When backing up the innodb table, add the-master-data = 2-single-transaction option to record the binlog-pos point at the start of the transaction, then mvcc (Multi-version concurrency control) is used to obtain consistent data. Because it is a large transaction, a lot of undo will be generated in the database with a large volume of writes and updates, performance is significantly affected, so use it with caution.

Advantage: it is simple and can be used for single-Table backup, especially when the table structure is fully exported. Different storage engines can be backed up (InnoDB hot backup, MyISAM warm backup, and Aria warm backup)

Disadvantages: simple and crude, single-thread, slow backup and recovery, does not support differential or Incremental backup, if you want to perform differential or Incremental backup, you must combine the binlog Log File

Mydumper is an enhanced version of mysqldump. Compared with mysqldump:

Built-in compression is supported, which saves 2-4 times the storage space.

Supports parallel backup and recovery, so the speed is much faster than mysqldump. However, because it is a logical backup, it is still not very fast. If you want to perform differential or Incremental backup, you must combine the binlog Log File

Some backup tools

SELECT clause into outfile '/path/to/somefile'

Load data infile '/path/from/somefile'

The relationship definition is not backed up, and only the data in the table is backed up;

The logical backup tool is faster than mysqldump.

(2) Introduction and simple use of mysqldump commands

① Command Introduction

Mysqldump [options] [db_name [tbl_name...]

Back up a single database:

Mysqldump [options] db_name

Recovery: if the target database does not exist, you must manually create it in advance.

Options description

-- All-databases: backs up all databases

-- Databases db1 db2...: backs up multiple specified databases.

Note: lock before backup

-- Lock-all-tables: requests to lock all tables and then back up the data, enabling warm backup for MyISAM, InnoDB, and Aria.

-- Single-transaction: supports hot backup for the InnoDB Storage engine;

Backup code:

-- Events: Backup event scheduler code

-- Routines: Backup stored procedures and storage functions

-- Triggers: Backup trigger

Rolling logs during Backup:

-- Flush-logs: Scroll logs before backup and after the lock is requested;

Synchronization position during replication:

-- Master-data = [0 | 1 | 2]

0: no record

1: The record is a change master statement

2: record the change master statement as a comment

Note:

Back up data using mysqldump

Request lock: -- lock-all-tables or -- singe-transaction for innodb hot backup;

Rolling log: -- flush-logs

Select the database to be backed up: -- databases

Record the binary log file and location: -- master-data =

Restore

Suggestion: Disable binary logs and other user connections;

Suggested backup policy: Based on mysqldump

Backup part

Mysqldump + binary log file;

Restore

Full backup + events in various binary log files to the moment, MySQL configuration files and MySQL-related OS configuration files should be backed up directly after each modification;

②. Instance

The requirements are as follows:

All databases must be backed up automatically every Sunday morning;

③ Solution (this method is not unique)

Backup phase

Step 1: log on to the database remotely and check the existing database in advance.

It can be seen that in addition to the hellodb database, other databases are built-in systems. Let's take a look at the tables in hellodb and the location of the current binlog records.

Because the current database only has one user database, we care about the storage engine of the table to determine the method used for backup. (Apply for a lock or a single transaction)

View the table status and find that all the tables in hellodb are MyISAM storage engines, you can apply for a global lock to back up the data.

# Mysqldump-uroot-p -- all-databases -- lock-all-tables -- flush-logs -- master-data = 2>/tmp/all. SQL

At this time, the database is fully backed up (this method is only applicable to the case where the data volume is not large, and a relatively concurrent write request is not selected for a particularly large amount of time or midnight backup. If the data size is very large, this method will not be used as a reference ).

If we create a new table or insert data in the original database after this, the database is deleted by some misoperation. How will it be restored?

It can be seen that a table is added to the original hellodb database. In order not to demonstrate the restoration effect, I will delete hellodb and see how to restore it.

Now hellodb has been deleted to see how to restore it.

Recovery Section

Step 1: Take the server offline, export the binlog currently in use, and restore the original full backup

View the start position of the binlog in the full backup.

# Vim/tmp/all. SQL -- CHANGE MASTER TO MASTER_LOG_FILE = 'mysql-bin.000002 ', MASTER_LOG_POS = 365;