Details of backup and restore of MySQL database

Time:2021-10-25

Database backup is very important, especially when data files are damaged, MySQL service errors, system kernel crashes, computer hardware is damaged or data is accidentally deleted, data backup can quickly solve all the above problems. Next, Liang Xu Xiaobian willMysql databaseBackup and restore in detail, I hope it will be helpful to you.

Linux命令

Many backup schemes are provided in MySQL database, mainly including logical backup, physical backup, full backup and incremental backup. Readers can choose a suitable way to back up data according to their own needs.

  1. Physical backup
    Physical backup refers to the direct replication of directories and files containing database contents. This backup method is suitable for the production environment that backs up important large-scale data and requires rapid restore.

A typical physical backup is to copy some or all directories of MySQL database. Physical backup can also back up relevant configuration files. However, the use of physical backup requires Mysql to be closed or the database to be locked to prevent changing the sent data during the backup process.

There are two physical backup methods: using mysqlbackup to back up InnoDB data and mysqlhotcopy to back up MyISAM data.

  1. Logical backup
    Logical backup refers to the storage of descriptive information representing database structure and data content. For example, save sol statements that create data structures and add data content.

Logical backup is applicable to the backup and restore of a small amount of data. It needs to query MySQL server to obtain data structure and content information, and convert these information into logical format, so it is relatively slow compared with physical backup.

Note that logical backup will not back up logs, configuration files and other data that do not belong to the database content. The advantage of logical backup is that it can be realized at the service level, database level or data table level. Because it is stored in logical format, this backup has nothing to do with the system and hardware.

  1. Full backup
    Full backup mainly backs up all data at a certain time.

  2. Incremental backup
    Incremental backup refers to backing up only data that has changed in a certain period of time. Full backup can be completed through physical or logical backup tools, but incremental backup requires MySQL binary logs to record data changes through logs, so as to realize incremental differential backup.

Use mysqldump to back up all databases. By default, the tool will export SQL statement information to standard output. You can save the output to a file through redirection. The command is as follows:

[[email protected] abc]# mysqldump –all-databases > bak.sql

Back up the specified database. The command is as follows:

[ [email protected] Abc]# mysqldump — all databases database 1 database 2 database 3 > bak.sql

When only one database is backed up, — databases can be omitted, and the command is as follows:

[ [email protected] Abc]# mysqldump Database > bak.sql
[ [email protected] Abc]# mysqldump — Databases > bak.sql

Note that the difference is that if the — databases option is not used, the backup output information will not contain the create database or use statement. For data files backed up without the — databases option, if the database does not exist in the later data restore operation, the database must be created first.

Use the MySQL command to read the backup file and realize the data restore function. The command is as follows:

[[email protected] abc]# mysql < bak.sql
[ [email protected] Abc]# MySQL database < bak.sql

The above isLiangxu tutorial networkShare the details of MySQL database backup and restore for all friends.

This article is composed of blog one article multi posting platformOpenWriterelease!

Recommended Today

Swift advanced (XV) extension

The extension in swift is somewhat similar to the category in OC Extension can beenumeration、structural morphology、class、agreementAdd new features□ you can add methods, calculation attributes, subscripts, (convenient) initializers, nested types, protocols, etc What extensions can’t do:□ original functions cannot be overwritten□ you cannot add storage attributes or add attribute observers to existing attributes□ cannot add parent […]