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

This article will mainly explain the relevant knowledge of MySQL database data backup and recovery, mainly focus on logical backup, and introduce the use of mysqldump tools and recovery methods.

Here is a brief introduction to the concepts of physical backup and logical backup:

Physical backup: back up data files and dump database physical files to a directory. The recovery speed of physical backup is relatively fast, but it takes up a lot of space, so xtrabackup tool can be used for physical backup in MySQL.

Logical backup: the database objects are exported by tools and summarized into the backup file. Logical backup recovery is slow, but takes up less space and is more flexible. The logical backup tool commonly used in MySQL is mysqldump.

1. Back up all databases

To back up the entire instance with mysqldump, you can use the-- all-databases or-A parameter:

Mysqldump-uroot-pxxxxxx-all-databases > / tmp/all_database.sql mysqldump-uroot-pxxxxxx-A > / tmp/all_database.sql

two。 Back up part of the database

Sometimes we encounter the need to back up only certain libraries, so we can use the-databases or-B parameter, which is followed by the database name, and multiple databases are separated by spaces.

Mysqldump-uroot-pxxxxxx-- databases testdb1 testdb2 > / tmp/testdb.sql mysqldump-uroot-pxxxxxx-B testdb1 testdb2 > / tmp/testdb.sql

3. Backup part of the table

Usually we also have the need to back up some tables, such as making a backup before the table changes, so we can do this:

# only back up test_ TB tables in the testdb library mysqldump-uroot-pxxxxxx testdb test_tb > / tmp/test_tb.sql # backup multiple tables mysqldump-uroot-pxxxxxx testdb tb1 tb2 tb3 > / tmp/tb.sql

4. Back up part of the data of a single table

Sometimes a table has a large amount of data, and we only need part of the data, so what should we do? At this point you can use the-- where option. Where is followed by conditions that need to be met. For example, if we only need data with create_time greater than 2019-08-01 in the tb1 table, we can export it as follows:

Mysqldump-uroot-pxxxxxx testdb tb1-- where= "create_time > = '2019-08-01 00lv 0000'" > / tmp/tb1.sql

5. Exclude some table exports

If we want to back up a library, but some tables have a large amount of data or are not relevant to the business, we can consider excluding these tables at this time. Again, the option-- ignore-table can do this.

Mysqldump-uroot-pxxxxxx testdb-- ignore-table=testdb.tb1 > / tmp/testdb.sql

6. Back up structure only or data only

You can use the-- no-data abbreviated to-d option only for backup structures; the-- no-create-info abbreviated to-t option can be used for backup data only.

Mysqldump-uroot-pxxxxxx testdb-- no-data > / tmp/testdb_jiegou.sql mysqldump-uroot-pxxxxxx testdb-- no-create-info > / tmp/testdb_data.sql

7. The backup contains stored procedure functions, events

Mysqldump backups do not contain stored procedures, custom functions, and events by default. We can use the-- routines or-R options to back up stored procedures and functions, and the-- events or-E parameters to back up events. For example, we want to back up the entire testdb library, including stored procedures and events:

Mysqldump-uroot-pxxxxxx-R-E-- databases testdb > / tmp/testdb.sql

8. Backup in the form of transaction

If we want to ensure data consistency and reduce locking tables during dump, we can use the-- single-transaction option, which is useful for InnoDB data tables and does not lock tables. Add Q group: 478052716 free (Java framework materials, video materials, BATJ interview materials)

Mysqldump-uroot-pxxxxxx-single-transaction-- databases testdb > / tmp/testdb.sql

9. Full backup recovery

If we now have a full backup from yesterday and want to restore the whole now, we can do this:

Mysql-uroot-pxxxxxx

< /tmp/all_database.sql 10.从全量备份中恢复单库 可能有这样的需求，比如说我们只想恢复某一个库，但是我们有的是整个实例的备份，这个时候我们想到能不能从全量备份中分离出单个库的备份，答案是可以的，下面这个简单的shell可以帮到你哦： sed -n '/^-- Current Database: `testdb`/,/^-- Current Database: `/p' all_databases.sql >

After testdb.sql # separation is completed, we can import testdb.sql to restore a single library.

11. Restore a single table from a single library backup

This requirement is still quite common. After all, there are many businesses involved in single database or full recovery, and the recovery time is relatively long. For example, if we know which table has been misoperated, we can restore it in the way of single table recovery. For example, now we have a backup of the entire testdb database, but we need to restore this table separately due to the misoperation of the tb1 table, so we can do this:

| cat testdb.sql | sed-e'/. / {HtterabildCREATE TABLE `tb1` /! dpoliq' > / tmp/tb1_jiegou.sql cat testdb.sql | grep-- ignore-case 'insert into `tb1`' > / tmp/tb1_data.sql # separates the statements that create the table and inserts the data using shell syntax, and then exports them sequentially to complete the recovery.

Summary:

This article gives the backup and recovery methods in different scenarios, and there may be more complex scenarios in production, which require everyone to adapt flexibly.

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.