Complete MySQL/MariaDB data backup and data recovery

The MariaDB database management system is a branch of MySQL. It is mainly maintained by the open-source community and licensed by GPL. One of the reasons for developing this branch is that Oracle has the potential risk of shutting down the source of MySQL after purchasing MySQL. Therefore, the community uses the branch method to avoid this risk. Data is no longer important to us, so how can we

The MariaDB database management system is a branch of MySQL. It is mainly maintained by the open-source community and licensed by GPL. One of the reasons for developing this branch is that Oracle has the potential risk of shutting down the source of MySQL after purchasing MySQL. Therefore, the community uses the branch method to avoid this risk. Data is no longer important to us, so how can we

The MariaDB database management system is a branch of MySQL. It is mainly maintained by the open-source community and licensed by GPL. One of the reasons for developing this branch is that Oracle has the potential risk of shutting down the source of MySQL after purchasing MySQL. Therefore, the community uses the branch method to avoid this risk.

Data is no longer important to us. How can we ensure the security of data as much as possible? What should we do when our data is lost, therefore, data backup is too important for our data security.

Data is no longer familiar to us, but it is also most common. We are exposed to various types of data every day, and the data records our common business information, therefore, data is very important for us. If such important data is lost, will it be impossible for us to do the relevant business, this should be a very troublesome issue. How can we protect the security of our data? We need to back up our data.

How to perform Backup Recovery: backup and recovery are the top priority in the work environment. Why is backup and recovery required:

1. Disaster Recovery is generally implemented, such as natural disasters.

2. audit can be performed, such as what a data is like in the past.

3. For example, whether a new data storage method in the Business architecture is accessible to the business

The purpose of backup is to recover data. If the backup data cannot be recovered when it is used, it is necessary to perform a recovery test on the backup data. In addition, we need to determine the sequence for testing.

Backup Type: there are many types

Based on whether the database server is online during Backup:

Cold backup: cold backup. The server must be offline, which means that read and write operations cannot be performed. This is the safest backup method and the most unreliable one.

Warm backup: warm backup, which applies a shared lock globally and is readable only. A non-writable backup is called a warm backup.

Hot backup: hot backup, database offline, read/write operations can be performed

When InnoDB records data, a serial number is assigned to the Data. Therefore, the snapshot is automatically accelerated Based on the MVCC (Multi-version Concurrency Control) mechanism during Backup, each time a transaction is started, a quick snapshot of the current set is created, and then each serial number is recorded for it based on the MVCC mechanism. During the backup, only the data before the serial number or serial number is backed up, future operations will not be backed up. If the isolation level of the transaction is not very high, it will not affect the read/write operations of the transaction, the data backed up in this way must be consistent at the time point. Therefore, to complete hot backup, it is usually completed by the transaction-based storage engine.

Classify data based on the data set during Backup:

Full backup: full backup refers to the data of the entire database and the entire database of the current dataset.

Partial backup: only one copy of a table or a table is backed up. Sometimes it is necessary to back up a single table.

Based on the backup interface (back up data files directly or export data through the mysql server)

Physical backup: the backup method for directly copying (archiving) data files; the cross-platform capability is not as good as logical backup, physucal backup. It is better to use big data sets.

Logical backup: stores the files in the database as text files. The commonly used tools are mysqldump and logical backup, which is not suitable for large data volumes. Recovery is slow and takes up a lot of space

Whether to back up the entire data or only the changed data during Backup:

Full backup: full backup is the same as full backup in the backup data set. It is also used to back up the entire database.

Incremental backup: incremental backup. incremental backup is used to back up the changed data after the last full backup. For example, backup is performed on Monday, backup is performed on Tuesday, and backup is performed on Wednesday, in this way, Incremental backup is accumulated every day. Saves space.

Differential backup: differential backup. For example, if a backup is performed on Monday, the backup is performed on Monday and Tuesday by Tuesday, and the backup is performed on Monday, Tuesday and Wednesday by Wednesday, this is called differential backup. Recovery is easier.

Backup policy: Considerations

1. Select the backup mode and the method based on the needs of our production environment;

2. It is reasonable to select the minimum access time for backup;

3. recovery costs: Recovery duration;

4. Backup Cost: Considering the lock time, backup duration, and backup load;

Backup object: What do we need to back up for backup?

1. Backing up data in the database is the most important;

2. MySQL configuration file, which is also the backup object

3. MySQL Code also needs to be backed up: stored procedures, storage functions, and triggers

4. OS-related configuration files, such as the crontab configuration plan and related scripts

5. In the scenario of master-slave replication, copy-related information should also be backed up.

6. To ensure data reliability, binary log files must be backed up.

Common backup tools:

Mysqldump: the logical backup tool is a single-thread backup tool. Therefore, when a backup is performed on a server, it can only start one CPU to start one thread for backup, with poor performance.

For InnoDB hot backup, for MyISAM, only warm backup and for Aria are allowed, and the backup and recovery process is slow;

Mysqldumper: multi-threaded mysqldump, which can be performed on multiple databases or tables at the same time to improve performance;

Mysqldump and mysqldumper are both logical backup tools. Generally, it is difficult to implement differential or Incremental backup. Only full backup can be performed, but partial backup can be performed, for example, you can back up only one table;

When using cold backup: cp, which needs to be backed up based on the lvm-snapshot logical volume snapshot, is similar to the hot backup tool, because the global lock is requested first, and then the snapshot is created, after creating a snapshot, release the global lock. Then, use tools such as cp and tar for physical backup (because the copied source data file), so the data backup and recovery speed is fast, disadvantages: it is difficult to implement Incremental backup, and it takes some time to request a global lock, especially on busy servers.

SELECT clause into outfile '/path/to/somefile'; Save the selected clause to a file. It is a part of backup tool and does not define the backup relationship, only the data in the table is backed up, but this is also a logical backup tool, which is faster than mysqldump and cannot implement Incremental backup.

Load adta infile '/path/from/somefile'; indicates where to read the data for restoration;

Innobase: Provides the commercial backup tool Innobackup to support Incremental backup for InnoDB hot backup. However, MyISAM does not support Incremental backup and can only implement full backup, which is a physical backup, fast.

Xtrabackup: an open-source backup tool provided by Percona, featuring physical backup and high speed;

Mysqlhostcopy: almost cold standby, bragging tool, not applicable;

Mysqldump: A common backup tool and a logical backup tool for small data backup. Generally, small data under 5 GB is backed up. You can use text for secondary processing; equivalent to MySQL client Tool

Format: mysqldump [options] [db_name [tbl_name...]

This tool is used to back up a single database: mysqldump [option] db_name

If the target database does not exist during restoration, you must manually create