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

The importance of data backup

1. In a production environment, the security of data is crucial, and any loss of data may have serious consequences.

2. The cause of data loss

Program error

Human error

Computer failure

Disk failed

Disasters (such as earthquakes) and theft

Classification of database backup

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

1. Physical backup: backup of physical files (such as data files, log files, etc.) of the database operating system

Physical backup 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, and 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: make a full backup of the data each time

2. Differential backup: back up files that have been modified since the last full backup

3. Incremental backup: only those in. Files that were modified after the last full or incremental backup will be backed up.

MySQL full backup (full backup)

1. Full backup is the backup of the whole database, database structure and file structure.

2. A full backup saves the database at the time when the backup is completed.

3. Full backup is the basis of incremental backup.

Advantages of full backup

Backup and recovery operation is simple and convenient.

Disadvantages of full backup

1. There is a lot of duplication of data.

two。 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 database folders directly, such as / usr/local/mysql/data

two。 Use the dedicated backup tool mysqldump

II. Mysqldump command

1.MySQL 's own backup tool, which is quite convenient to back up MySQL.

two。 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 /

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

MySQL incremental backup

An incremental backup is a backup of files or contents that have been added or changed since the last backup

Characteristics of incremental backup

● has no duplicate data, and the backup is small and the time is short.

Trouble with ● recovery: all incremental backups after the last full backup and full backup can be restored, and all incremental backups have to be restored one by one.

MySQL does not provide a direct incremental backup method

Incremental backup can be achieved indirectly through the binary log (binary logs) provided by MySQL

The significance of MySQL binary Log to backup

The ● binary log holds all updates or possible database updates.

The ● binary log starts to be recorded after the MySQL server is started, and a new log file is recreated after the file reaches the size set by max_ binlog_size or after the flush logs command is received

● only needs to regularly execute the flush logs method to recreate the new log, generate a sequence of binary files, and save these old logs to a safe place in time to complete an incremental backup for a period of time.

Incremental recovery of MySQL database

Reply based on location

Is to import the binary log at a certain starting time into the database, thus skipping a certain point in time where an error occurs to achieve data recovery.

Point-in-time recovery

Using point-in-time recovery, there may be both correct and wrong operations at a point in time, so we need a more accurate recovery.

Methods of incremental recovery

1. General recovery

Mysqlbinlog [--no-defaults] incremental backup files | mysql-u user name-p

two。 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

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.