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

The following mainly brings you MySQL to achieve full-volume, incremental backup and recovery methods, I hope these contents can bring you practical use, which is also the main purpose of this article that I edit MySQL to achieve full-volume, incremental backup and recovery methods. All right, don't talk too much nonsense, let's just read the following.

The importance of data backup 1. In the production environment, the security of data is very important. Any loss of data may have serious consequences. 2. Causes of data loss: program errors, human errors, computer failures, disk failures, disasters (such as earthquakes, etc.) and classification of stolen database backups

1. From a physical and logical point of view, backup can be divided into:

1. Physical backup: physical backup of physical files (such as data files, log files, etc.) of the database operating system can be divided into offline backup (cold backup) and online backup (hot backup):-cold backup: when the database is closed-hot backup: the database is running This backup method depends on the log files of the database 2. Logical backup: backup of database logical components (such as tables and other database objects)

2. From the point of view of the backup strategy of the database, backup can be divided into:

1. Full backup: complete backup of data every time. 2. Differential backup: back up those files that have been modified since the last full backup. 3. Incremental backup: only those in. The files modified after the last full backup or incremental backup will be backed up. Note: differential backup and incremental backup complement each other MySQL full backup (full backup) 1, full backup is the backup of the entire database, database structure and file structure backup 2, full backup saves the database at the time when the backup is completed. 3. Full backup is the basis of incremental backup the advantages of full backup and recovery operation is simple and convenient full backup 1. There are a lot of duplicates in the data. Take up a lot of backup space 3. Long backup and recovery time mysqldump backup library

1. MySQL database can be backed up in a variety of ways

1. Package the database folder directly, such as / usr/local/mysql/data2. Use the dedicated backup tool mysqldump

II. Mysqldump command

1.MySQL comes with backup tools, which is quite convenient for MySQL backup 2. Through this command tool, you can export specified libraries, tables, or all libraries as SQL scripts, and you can restore data when you need to restore.

Third, make a full backup of a single database using the mysqldump command

Mysqldump-u user name-p [password] [option] [database name] > / backup path / backup file name

4. Make a full backup of multiple libraries using the mysqldump command

Mysqldump-u username-p [password] [option]-- databases library name 1 [library name 2]. > / backup path / backup file name

Fifth, use the mysqldump command to make a full backup of all libraries

Mysqldump-u user name-p [password] [option]-- all-databases > / backup path / backup file name mysqldump backup table in the actual production environment, there is a maintenance operation for a specific table, and mysqldump also plays an important role.

1. The operation of using mysqldump backup table

Mysqldump-u user name-p [password] [option] Database name Table name > / backup path / backup File name

two。 Operation of backing up table structure using mysqldump

Mysqldump-u user name-p [password] [option]-d database name table name > / backup path / backup file name restore database 1. SQL backup script exported using the mysqldump command can be imported during data recovery using the following methods: source command # run mysql command in database mode # run in Linux mode

1. Steps to restore a database using source

● logs in to MySQL database ● to perform source backup sq | the path to the script (absolute path) MySQL [(none)] > source / backup/all-data.sql#source command restore

two。 Use the mysql command to restore data

Mysql-u user name-p [password] / opt/ bak. Overview of txtMySQL Database incremental recovery General recovery

Location-based recovery is to import a binary log at a starting time into the database, thus skipping an error point in time to achieve data recovery based on a point-in-time recovery using a point-in-time recovery. there may be both correct and wrong operations in a point in time, so we need a more accurate recovery method incremental recovery method 1. Generally restore mysqlbinlog [--no-defaults] incremental backup files | mysql-u user name-p2. Location-based recovery

Restore data to a specified location

Mysqlbinlog-- stop-position= operation 'id' 1 log | mysql-u username-p password

Restore data from the specified location

Mysqlbinlog-- start-position= operation 'id' binary log | mysql-u username-p password 3. Point-in-time based recovery

Recovery from the beginning of the log to a certain point in time

Mysqlbinlog [--no-defaults]-- stop-datetime=' year-month-day hour: minute: second 'binary log | mysql-u username-p password

Recovery from a certain point in time to the end of the log

Mysqlbinlog [--no defaults]-- start-datetime=' year-month-day hour: minute: second 'binary log | mysql-u username-p password

Recovery from a certain point in time to a certain point in time

Mysqlbinlog [--no defaults]-- start-datetime=' year-month-day hour: minute: second'- stop-datetime=' year-month-day hour: minute: second 'binary log | mysql-u username-p password

For the above MySQL to achieve full, incremental backup and recovery methods, we do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.