Shulou(Shulou.com)05/31 Report--

MySQL database how to achieve backup and recovery, many novices are not very clear about this, in order to help you solve this problem, the following small series will explain in detail for everyone, there are people who need this can learn, I hope you can gain something.

A. Some concepts of database backup

1. Causes of Data Loss

Program error, human error, arithmetic error, disk failure, disaster (fire, earthquake) and theft

2. Type of data backup

1) Physical and logical classification

Physical backups can be divided into:

Cold backup (offline backup): backup operation while database is down

Hot backup (online backup): A backup operation that takes place while the database is running and relies on log files for the database

Warm backup: backup operation in the state of database lock table (not writable but readable)

To put it bluntly, physical backup is a backup of physical files (such as data files, log files, etc.) of the database operating system. This type of backup is suitable for large and important databases that need to be quickly restored when problems occur.

A logical backup is a backup of logical components of a database, such as database objects such as tables. This type of backup is used to edit small amounts of data with data values or table structures, or to recreate data on different machine architectures.

2) Classified from the perspective of database backup strategy

From backup strategy can be divided into:

Complete backup: Every time a complete backup of data is made, that is, the backup of the entire database, database structure and file structure, the database at the time of backup completion is saved, which is the basis of differential backup and incremental backup. Full backup backup and restore operations are very simple and convenient, but there is a lot of duplication of data, and will take up a lot of disk space, backup time is also very long.

Differential backup: backup all files that have been modified since the last full backup. The backup time node is from the last full backup. The backup data volume will be larger and larger. When restoring data, only the last full backup and the most recent differential backup need to be restored.

Incremental backups: Only files that have been modified since the last full or incremental backup are backed up. The time of the last complete backup or the last incremental backup is a time point, and only the data changes between them are backed up, so the amount of data backed up is small, the space occupied is small, and the backup speed is fast. However, during recovery, all incremental recovery from the last complete backup to *** incremental backup is required, such as a backup data corruption in the middle, which will lead to data loss.

As shown in the figure:

3. Common backup methods

1) Physical cold backup

It needs to be carried out under the closed state of the database to ensure the integrity of the database. Generally used for non-core services, such services generally allow terminals, physical cold backup is characterized by fast speed, simple recovery operation.

This is usually achieved by packaging the database folder directly (e.g./usr/local/mysql/data)

2) special backup tools mysqldump or mysqlhotcopy

mysqldump is a common logical backup program on the client side that produces a set of SQL statements that are executed to reproduce the original database object definitions and table data. It can dump one to several MySQL databases, backup them or transfer them to a remote SQL server. Mysqldump is more generic because it can back up various tables.

Mysqlhotcopy is a Perl script originally written and contributed by Tim Bunce. Mysqlhotcopy is only used to back up MyISAM and ARCHIVE tables. It can only run on UNIX or Linux, and because it is used in a small range, I won't go into detail here.

3) Incremental backups by enabling binary logging

MySQL supports incremental backups, and binary logging must be enabled for incremental backups. Binary log files provide replication for users, restoring information needed for database changes made after the backup point was performed. If you do incremental backups, you need to flush the binary logs.

4) Backup through third-party tools

Percona XtraBackup is a free MySQL hot backup software that supports online hot backup Innodb and XtraDB. It also supports MySQL table backup, but MyISAM table backup should be carried out in the case of table lock. Because the content is too much, here will not explain in detail, if interested, you can consult the detailed introduction of the software to understand.

Complete Backup and Recovery of MySQL

1. Physical cold backup and recovery

Physical cold backups typically package database folders directly with tar command, while mysql service needs to be turned off before backups can be made.

1) Backup database

Create a/backup directory as the backup data storage path and use tar to create backup files. An entire database folder backup is a full backup. As shown below:

2) Recovery of database

Simulate a failure by moving/usr/local/mysql/data to/bak. As shown below:

Recover data from backup files. As shown below:

2. mysqldump backup and recovery

The mysqldump command allows you to export specified libraries, tables, or all libraries as SQL scripts, making it easy to use the command on different versions of MySQL servers. For example, when you need to upgrade MySQL server, you can use mysqldump command to export the original library information, and then directly import it into the upgraded MySQL server.

1) Backup database

When you export data using mysqldump command, it will be displayed directly in the terminal by default. To save it to a file, you need to combine the shell's ">" redirection output operation. The command format is as follows:

Common options: -u: Specify username;-p: Specify password

Example: Export the user table in the mysql library as mysql-user.sql, export the entire auth library as auth.sql, and export all libraries as all-data.sql files. As shown below:

2) View backup files

SQL scripts exported through the mysqldump tool are text files where "/*... */ "Partial or behavior comment information that begins with"--". Use grep, less, cat and other text tools to view the script content. As shown below (see auth.sql file):

3) Recovery of database

SQL backup scripts exported using the mysqldump command are imported at restore time using the mysql command. The command format is: "mysql [options] [library name] [table name] < /backup path/backup file name."

When the backup file contains only a copy of the table and no statements for the library you created, the library name must be specified when you perform the import operation, and the target library must exist.

Example: Import tables from the backup file mysql-user.sql into the test library. As shown below:

If the backup file already includes complete library information, you do not need to specify a library name for the import operation. As shown below:

Incremental Backup and Recovery of MySQL

1. Overview of incremental backups

1) Characteristics of incremental recovery

Different from full backup, incremental backup has no duplicate data, the amount of backup is small, and the time is short; however, its recovery is troublesome, requiring the last full backup and all incremental backups after the full backup to be restored, and all incremental backups must be restored one by one. MySQL does not provide direct incremental backup methods, but can indirectly realize incremental backup through binary logs provided by MySQL.

2) MySQL binary log significance for backup

Binary logs hold all operations that update or potentially update the database. Binary log starts recording after starting MySQL server, and re-creates new log files after the file reaches the *** value set by binary log or receives flush logs command, generates binary file sequence, and saves these logs to safe storage location in time to complete incremental backup of a period of time. Use the max_binlog_size configuration item to set the *** value of the binary log file, which automatically creates a new binary file if the binary file size exceeds max_binlog_size.

To perform incremental backup of MySQL, first turn on the binary log function. There are many ways to turn on the binary log function of MySQL. The most common way is to add the prefix "log-bin=/file path/file name" under mysql of MySQL configuration file, such as log-bin=/usr/local/mysql/mysql-bin, and then restart MySQL service. You can view the binary log file under the specified path (as shown below). By default, binary log files have a six-digit extension, such as mysql-bin.000001.

2. incremental recovery

When maintaining a database, data loss may occur for a variety of reasons, such as: perceived SQL statement corruption, system failure before the next full backup, and data failure in the master database in the database master-slave architecture. Therefore, there are three commonly used incremental recovery methods: general recovery, location-based recovery, and point-in-time recovery.

1) General recovery: restore all binary log contents of backup

Command Format:

"mysqlbinlog [--no-defaults] incremental backup files| mysql -u username-p"

2) Location-based recovery: Database administrators may have both wrong and correct operations at the same point in time when operating the database. Location-based recovery can be more accurate.

Command Format:

Format 1: Restore data to specified location

"mysqlbinlog --stop-position ='operation id'binary log| mysql -u username-p password "

Format 2: Recovery starts from specified location

"mysqlbinlog --start-position ='operation id'binary log| mysql -u username-p password "

3) point-in-time recovery: skip a point in time when an error occurred to achieve data recovery, and point-in-time recovery can be divided into three cases

A. Recovery from the beginning of the log to a point in time

Command Format:

"mysqlbinlog [--no-defaults] --stop-datetime='yy| mysql -u username-p password "

B. Recovery from a point in time to the end of the log

Command Format:

"mysqlbinlog [--no-defaults] --start-datetime='yy| mysql -u username-p password "

C. Recovery from a point in time to a point in time

Command Format:

"mysqlbinlog [--no-defaults] --start-datetime ='yy| mysql -u username-p password "

Did reading the above help you? If you still want to have further understanding of related knowledge or read more related articles, please pay attention to the industry information channel, thank you for your support.

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.