Detailed explanation of mysql backup and recovery

Preface:

The previous articles introduced the usage of various MySQL statement syntax and user rights related knowledge. This article will mainly explain the knowledge related to MySQL database data backup and recovery, focusing on logical backup, and introducing the use of the mysqldump tool 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 certain directory. Physical backups can be restored quickly but take up a lot of space. In MySQL, you can use the xtrabackup tool to perform physical backups.
Logical backup: Use tools to export database objects and summarize them into backup files. Logical backups are slower to restore, but they take up less space and are more flexible. The commonly used logical backup tool in MySQL is mysqldump.

1. Back up all databases

If you want 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.sqlmysqldump -uroot -pxxxxxx -A > /tmp/all_database.sql

2. Back up some databases

Sometimes we need to back up only certain databases. In this case, we can use the --databases or -B parameter, which is followed by the database name, with multiple databases separated by spaces.

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

3. Back up some tables

Usually we also have the need to back up some tables, for example, to make a backup before changing the table, then we can do it like this:

Only back up the test_tb table 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 in 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? This is when you can use the --where option. where is followed by the conditions that need to be met. For example, if we only need data in the tb1 table whose create_time is greater than 2019-08-01, we can export it like this:

mysqldump -uroot -pxxxxxx testdb tb1 --where=" create_time >= '2019-08-01 00:00:00' " > /tmp/tb1.sql

5. Exclude certain tables from export

If we want to back up a database, but some tables have a large amount of data or are not closely related to the business, we can consider excluding these tables. Similarly, the option --ignore-table can accomplish this function.

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

6. Back up only the structure or only the data

To back up only the structure, you can use the --no-data option, abbreviated as -d; to back up only the data, you can use the --no-create-info option, abbreviated as -t.

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

7. The backup includes stored procedures, functions, and events

By default, mysqldump backups do not include stored procedures, custom functions, and events. We can use the --routines or -R option to back up stored procedures and functions, and the --events or -E parameter to back up events. For example, we want to back up the entire testdb database, including stored procedures and events:

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

8. Backup in the form of transactions

If we want to ensure data consistency and reduce table locks during the dump process, we can use the --single-transaction option. This option is very useful for InnoDB data tables and will not lock the table.

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

9. Full backup and recovery

If we have yesterday's full backup and want to restore it now, we can do this:

mysql -uroot -pxxxxxx < /tmp/all_database.sql

10. Restore a single database from a full backup

There may be such a requirement, for example, we only want to restore a certain library, but we have the backup of the entire instance. At this time, we wonder whether we can separate the backup of a single library from the full backup. The answer is yes. The following simple shell can help you:

sed -n '/^-- Current Database: `testdb`/,/^-- Current Database: `/p' all_databases.sql > testdb.sql

After the separation is completed, we can import testdb.sql to restore the single database

11. Restore a single table from a single database backup

This requirement is quite common. After all, single database or full recovery involves a lot of business and the recovery time is relatively long. For example, if we know which table has been misoperated, we can use single table recovery to recover it. For example, we now have a backup of the entire testdb database, but due to an error in the tb1 table, we need to restore this table separately. Then we can do this

cat testdb.sql | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `tb1`/!d;q' > /tmp/tb1_jiegou.sqlcat testdb.sql | grep --ignore-case 'insert into `tb1`' > /tmp/tb1_data.sql

Use shell syntax to separate the statements for creating tables and inserting data, and then export them one by one to complete the recovery.

Summarize:

This article provides backup and recovery methods in different scenarios. There may be more complex scenarios in production, which require you to be flexible.

The above is a detailed explanation of MySQL backup and recovery. For more information about MySQL backup and recovery, please pay attention to other related articles on 123WORDPRESS.COM!