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

MySQL full backup and recovery

The main content of this article

Classification of database backup MySQL full backup and recovery MySQL differential backup and recovery MySQL incremental backup concept MySQL backup and recovery operation 1

​ with the rapid development of office automation and e-commerce, enterprises are more and more dependent on the information system. Database plays a major role as the core of the information system. Database backup is an important means to recover important data in time and prevent data loss when the database is lost. A reasonable database backup scheme should be able to recover data effectively when data is lost. at the same time, it is necessary to consider the difficulty of technical implementation and effective use of resources.

II. The concept and classification of database backup 2.1 the importance of data backup

​ in a production environment, the security of data in the database is critical, and any loss of data may have serious consequences. The reasons for data loss are as follows:

2.2 causes of data loss: program errors; generally fewer human errors; most computer failures; disk downtime failures; storage-file system-distributed disasters; attack or natural disasters

​ therefore, we need to backup the data in the database as much as possible, so as to avoid business accidents caused by data loss and have a negative impact on the company.

Backups of ​ databases can be classified from different angles. Let's introduce the specific classification of databases and their concepts.

2.3 Classification of database backup 2.3.1 physical backup and logical backup

Physical backup: backup of physical files (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: occurs when the database is shut down

​ hot backup: the database is running, and this backup method depends on the log files of the database

Logical backup: backup of database logical components (such as tables, etc.)

2.3.2 from the database backup strategy is divided into full backup: full backup of data each time differential backup: backup of those files that have been modified since the last full backup: only those files that have been modified after the last full backup or incremental backup will be backed up the advantages and disadvantages of full backup: advantages: security, data integrity; simple and convenient operation Disadvantages: backup data redundancy, consumption of system resources; backup time and recovery time long difference backup advantages and disadvantages: advantages: saving resources to a certain extent, security is more eclectic; disadvantages: as soon as the full backup is failed, there are only different backups; advantages and disadvantages of incremental backup: no redundant data to be backed up; disadvantages: low security; recovery trouble

Let's use a more vivid example to illustrate the difference between the three:

Backup mode full backup differential backup incremental backup status table1,table2table1,table2table1,table2 first add content create table3 backup content backup table1,2,3 backup table3 backup table3 second time add content create table4 backup content backup table1,2,3,4 backup table3,4 backup table4

​ can sum up as follows: a full backup backs up all data once, the reference object for a differential backup is the first full backup, and the reference object for an incremental backup is the most recent (the last backup relative to the previous backup).

3. MySQL full backup

The main backup methods of ​ MySQL are full backup and incremental backup. Full backup is the basis of incremental backup. Therefore, in the production environment, these two methods will be used, it is necessary to develop a reasonable and efficient plan to achieve the purpose of backing up data.

3.1 detailed understanding of the concept of full backup full backup is the backup of the entire database, database structure and file structure, full backup saves the complete backup of the database at the time of backup is the basis of differential backup and incremental backup 3.2 full backup actual operation

​ MySQL database can be backed up in two ways, because the database is actually a file, you can package the database folder directly, or use the special backup tool mysqldump for backup operation.

3.2.1 backup using tar packaged folders

(1) install the xz compression format tool, which has a high compression ratio.

[root@localhost ~] # yum install-y xz loaded plug-in: fastestmirror Langpacksbase | 3.6 kB 00:00 extras | 2.9 kB 00:00 updates | 2.9 kB 00:00 Loading mirror speeds from cached hostfile * base: mirrors.aliyun.com * extras: mirrors.aliyun.com * updates: mirrors.aliyun. The com package xz-5.2.2-1.el7.x86_64 is installed and is the latest version without any processing

(2) Packaging the database files in the database

[root@localhost opt] # tar Pjcf / opt/data-$ (date +% F). Tar.xz / usr/local/mysql/data/fruit / usr/local/mysql/data/student/ [root@localhost opt] # lsdata-2020-01-07.tar.xz mysql-5.7.17 rh

(3) compare the space resources occupied

[root@localhost opt] # ls-lh / usr/local/mysql/data/student/ total dosage 112K Murray / usr/local/mysql/data/student/. 1 mysql mysql 61 January 7 14:11 db.opt-rw-r-. 1 mysql mysql 8.5K January 7 14:14 stu_info.frm-rw-r-. 1 mysql mysql 96K January 7 14:14 stu_ in fo.ibd [root @ localhost opt] # ls-lh / usr/local/mysql/data/fruit/ total usage 148K Murray RW RFT. 1 mysql mysql 61 January 6 16:37 db.opt-rw-r-. 1 mysql mysql 8.5K January 6 18:30 fruit_info.frm-rw-r-. 1 mysql mysql 129K January 7 12:04 fruit_ in fo.ibd [root @ localhost opt] # ls-lh data-2020-01-07.tar.xz-rw-r--r--. 1 root root 1.4K January 7 14:32 data-2020-01-07.tar.xz

(4) if the data in the original directory is lost or damaged, you can decompress it and recover the data in the data directory.

[root@localhost opt] # cd / usr/local/mysql/data/ [root@localhost data] # lsauto.cnf ibdata1 ibtmp1 studentfruit ib_logfile0 mysql sysib_buffer_pool ib_logfile1 performance_ schemas [root @ localhost data] # rm fruit/-rf [root@localhost data] # rm student/-rf [root@localhost data] # lsauto.cnf ib_logfile0 mysql usrib_ Buffer_pool ib_logfile1 performance_schemaibdata1 ibtmp1 sys [root@localhost data] # tar Pjxf / opt/data-2020-01-07.tar.xz-C. [root@localhost data] # lsauto.cnf ibdata1 ibtmp1 studentfruit ib_logfile0 mysql sysib_buffer_pool ib_logfile1 performance_schema usr3.2.2 uses the mysqldump tool to back up

​ We know that using compressed packages is not a very good choice in practice, because it backs up all the contents of the database, and mysqldump tools can control the contents of the backup more flexibly, such as backing up specific databases and specific tables.

​ first we have the following database system, in which there are two databases created by ourselves: fruit student

There is fruit_ info table in fruit and stu_ info table in student

Mysql > show databases;+-+ | Database | +-+ | information_schema | | fruit | | mysql | | performance_schema | | student | | sys | +-+ 6 rows in set (0.00 sec) mysql > use student Database changedmysql > show tables;+-+ | Tables_in_student | +-+ | stu_info | +-+ 1 row in set (0.00 sec) mysql > use fruit; Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > show tables +-+ | Tables_in_fruit | +-+ | fruit_info | +-+ 1 row in set (0.00 sec)

(1) use the mysqldump command to make a full backup of a table in the following format:

Mysqldump-uroot-p [option] [database name] [Datasheet name] > / backup path / backup file name (to end with .sql suffix)

Example:

[root@localhost ~] # mysqldump-uroot-p fruit fruit_info > / opt/fruit_bak_$ (date +% F). SqlEnter password: [root@localhost ~] # ls / opt/data-2020-01-07.tar.xz fruit_bak_2020-01-07.sql mysql-5.7.17 rh

(2) use the mysqldump command to make a full backup of a single database in the following format:

Mysqldump-uroot-p [option] [database name] > / backup path / backup file name (to end with .sql suffix name)

Example:

[root@localhost ~] # mysqldump-uroot-p fruit > / opt/fruit_db_backup-$ (date +% F). SqlEnter password: [root@localhost ~] # ls / opt/data-2020-01-07.tar.xz fruit_db_backup-2020-01-07.sql rhfruit_bak_2020-01-07.sql mysql-5.7.17

(3) use the mysqldump command to make a full backup of multiple databases in the following format:

Mysqldump-uroot-p [option]-- databases [list of database names] > / backup path / backup file name (to end with .sql suffix)

Example:

[root@localhost] # mysqldump-uroot-p-- databases fruit student > / opt/fruit_and_student_db_backup-$ (date +% F). SqlEnter password: [root@localhost ~] # ls / opt/data-2020-01-07.tar.xzfruit_and_student_db_backup-2020-01-07.sqlfruit_bak_2020-01-07.sqlfruit_db_backup-2020-01-07.sqlmysql-5.7.17rh

(4) use the mysqldump command to make a full backup of all databases in the following format:

Mysqldump-uroot-p [option]-- all-databases > / backup path / backup file name (to end with .sql suffix name)

Example:

[root@localhost] # mysqldump-uroot-p-- all-databases > / opt/all_db_backup-$ (date +% F). SqlEnter password: [root@localhost ~] # ls / opt/all_db_backup-2020-01-07.sqldata-2020-01-07.tar.xzfruit_and_student_db_backup-2020-01-07.sqlfruit_bak_2020-01-07.sqlfruit_db_backup-2020-01-07.sqlmysql-5.7.17rh

(5) use the mysqldump command to directly back up the table structure or the entire data table. The command format is as follows:

Mysqldump-uroot-p [- d] [database name] [datasheet name] > / backup path / backup file name (to end with .sql suffix)

Example:

[root@localhost ~] # mysqldump-uroot-p-d fruit fruit_info > / opt/table_structure.sqlEnter password: [root@localhost ~] # ls / opt/all_db_backup-2020-01-07.sqldata-2020-01-07.tar.xzfruit_and_student_db_backup-2020-01-07.sqlfruit_bak_2020-01-07.sqlfruit_db_backup-2020-01-07.sqlmysql 07.sqlfruit_db_backup-2020 5.7.17rhtable_ structure.sql [root @ localhost ~] # mysqldump -uroot-p fruit fruit_info > / opt/table_structure-$ (date +% F). SqlEnter password: [root@localhost ~] # ls / opt/all_db_backup-2020-01-07.sqldata-2020-01-07.tar.xzfruit_and_student_db_backup-2020-01-07.sqlfruit_bak_2020-01-07.sqlfruit_db_backup-2020-01-07.sqlmysql-5.7.17rhtable_structure-2020-01-07.sqltable_structure.sql

Let's compare the contents of backup files with and without the option-d.

There is a-d option:

[root@localhost ~] # cat / opt/table_structure.sql

-- MySQL dump 10.13 Distrib 5.7.17, for Linux (x86 / 64)

-- Host: localhost Database: fruit

-- Server version 5.7.17

/! 40101 SET @ OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /

/! 40101 SET @ OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /

/! 40101 SET @ OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /

/! 40101 SET NAMES utf8 /

/! 40103 SET @ OLD_TIME_ZONE=@@TIME_ZONE /

/! 40103 SET TIME_ZONE='+00:00' /

/ 40014 SET @ OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 /

/ 40014 SET @ OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 /

/ 40101 SET @ OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' /

/ 40111 SET @ OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 /

--

-- Table structure for table fruit_info

DROP TABLE IF EXISTS fruit_info

/! 40101 SET @ saved_cs_client = @ @ character_set_client /

/! 40101 SET character_set_client = utf8 /

CREATE TABLE fruit_info (

Id int (4) NOT NULL

Price decimal (3 dint 2) NOT NULL

Newtype varchar (6) DEFAULT NULL

UNIQUE KEY id_index_new (id)

UNIQUE KEY type_index (newtype)

KEY id_index (id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

/! 40101 SET character_set_client = @ saved_cs_client /

/! 40103 SET TIME_ZONE=@OLD_TIME_ZONE /

/! 40101 SET SQL_MODE=@OLD_SQL_MODE /

/! 40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS /

/! 40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS /

/! 40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /

/! 40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /

/! 40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION /

/! 40111 SET SQL_NOTES=@OLD_SQL_NOTES /

-- Dump completed on 2020-01-07 14:59:25

There is no-d option:

[root@localhost] # cat / opt/table_structure-2020-01-07.sql

-- MySQL dump 10.13 Distrib 5.7.17, for Linux (x86 / 64)

-- Host: localhost Database: fruit

-- Server version 5.7.17

/! 40101 SET @ OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /

/! 40101 SET @ OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /

/! 40101 SET @ OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /

/! 40101 SET NAMES utf8 /

/! 40103 SET @ OLD_TIME_ZONE=@@TIME_ZONE /

/! 40103 SET TIME_ZONE='+00:00' /

/ 40014 SET @ OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 /

/ 40014 SET @ OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 /

/ 40101 SET @ OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' /

/ 40111 SET @ OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 /

--

-- Table structure for table fruit_info

DROP TABLE IF EXISTS fruit_info

/! 40101 SET @ saved_cs_client = @ @ character_set_client /

/! 40101 SET character_set_client = utf8 /

CREATE TABLE fruit_info (

Id int (4) NOT NULL

Price decimal (3 dint 2) NOT NULL

Newtype varchar (6) DEFAULT NULL

UNIQUE KEY id_index_new (id)

UNIQUE KEY type_index (newtype)

KEY id_index (id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

/! 40101 SET character_set_client = @ saved_cs_client /

--

-- Dumping data for table fruit_info

LOCK TABLES fruit_info WRITE

/! 40000 ALTER TABLE fruit_info DISABLE KEYS /

INSERT INTO fruit_info VALUES (1 meme 2.50 pound banana'), (2 recorder 5.50 pound apple'), (3 recorder 6.00 dagger peach')

/! 40000 ALTER TABLE fruit_info ENABLE KEYS /

UNLOCK TABLES

/! 40103 SET TIME_ZONE=@OLD_TIME_ZONE /

/! 40101 SET SQL_MODE=@OLD_SQL_MODE /

/! 40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS /

/! 40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS /

/! 40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /

/! 40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /

/! 40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION /

/! 40111 SET SQL_NOTES=@OLD_SQL_NOTES /

-- Dump completed on 2020-01-07 15:01:04

Conclusion:

​ can see that the structure of the table is backed up with the-d option, while the entire data table is backed up without the-d parameter.

IV. Full recovery of database

​ described the specific operation of database full backup above, so when there is an error in the data, you can restore it in the following ways

4.1 resume the operation of the whole library

When you need to restore the entire library, you can use the source command and the mysql command

4.1.1source command to restore the whole library

Command format: source backup script path (absolute path)

Example:

Use the above example of backing up the fruit database to simulate deleting the library and performing a restore operation

Method: generate backup data files-- "log in to the database--" delete the database-- restore using the source command

Specific operations:

[root@localhost data] # mysqldump-uroot-p student > / opt/student.sqlEnter password: [root@localhost data] # ls / opt/data-2020-01-07.tar.xz mysql-5.7.17 rh student.sqlmysql > drop database student;Query OK, 1 row affected (0.00 sec) mysql > show databases +-+ | Database | +-+ | information_schema | | fruit | | mysql | | performance_schema | | sys | | usr | +-+ 6 rows in set (0.00 sec) mysql > create database student-> Query OK, 1 row affected (0.00 sec) mysql > use student;Database changedmysql > source / opt/student.sqlQuery OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec)... # omit some content Query OK, 0 rows affected (0.00 sec) mysql > mysql > show tables +-+ | Tables_in_student | +-+ | stu_info | +-+ 1 row in set (0.00 sec) 4.1.2mysql command to restore the whole library

You don't need to log in to the mysql database system, and you can use the mysql command to restore the entire library directly.

Examples are as follows:

[root@localhost data] # mysqldump-uroot-p student > / opt/student.sqlEnter password: [root@localhost data] # ls / opt/data-2020-01-07.tar.xz rh student.sqlmysql-5.7.17 student1.sql

Simulation database loss:

[root@localhost data] # mysql-uroot-p Enter password: Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 41Server version: 5.7.17 Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql > show databases +-+ | Database | +-+ | information_schema | | fruit | | mysql | | performance_schema | | student | | sys | | usr | +-+ 7 Rows in set (0.00 sec) mysql > drop database student Query OK, 1 row affected (0.00 sec) mysql > show databases +-+ | Database | +-+ | information_schema | | fruit | | mysql | | performance_schema | | sys | | usr | +-+ 6 rows in set (0.00 sec)

Full library recovery:

[root@localhost data] # mysql-uroot-p

< /opt/student.sql Enter password: ERROR 1046 (3D000) at line 22: No database selected[root@localhost data]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 43Server version: 5.7.17 Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

Create database student;Query OK, 1 row affected (0.01sec) mysql > exitBye [root@localhost data] # mysql-uroot-p student

< /opt/student.sql Enter password: [root@localhost data]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 47Server version: 5.7.17 Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

Show databases +-+ | Database | +-+ | information_schema | | fruit | | mysql | | performance_schema | | student | | sys | | usr | +-+ 7 Rows in set (0.00 sec) mysql > use student Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > select * from stu_info +-+ | id | name | address | +-+ | 1 | zhsan | bj | | 2 | wawu | nj | +-+ 2 rows in set (0.00 sec) 4.2.3 Summary

When ​ performs a full library recovery, you need to create a library in the MySQL5.7 version before you can use the source or mysql command to restore the entire library.

4.2 restore data table operations (similar to entire library recovery) 4.2.1 restore tables using the source command

(1) create a recovery file directory to store backup table files:

[root@localhost opt] # mkdir abc [root@localhost opt] # cd-/ usr/local/mysql/data [root@localhost data] # lsauto.cnf ibdata1 ibtmp1 studentfruit ib_logfile0 mysql sysib_buffer_pool ib_logfile1 performance_schema usr [root@localhost data] # mysqldump-uroot-p student stu_info > / opt/abc/table.sqlEnter password: [root@localhost data] # ls / opt/abc/table.sql

(2) View the contents of the database table

[root@localhost data] # mysql-uroot-pEnter password: Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 52Server version: 5.7.17 Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c' to clear the current input statement.mysql > use student;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > show tables +-+ | Tables_in_student | +-+ | stu_info | +-+ 1 row in set (0.00 sec) mysql > select * from stu_info +-+ | id | name | address | +-+ | 1 | zhsan | bj | | 2 | wawu | nj | +-+ 2 rows in set (0.00 sec)

(3) data loss of simulation table file

Mysql > drop table stu_info;Query OK, 0 rows affected (0.00 sec) mysql > show tables;Empty set (0.00 sec)

(4) source command to restore the data table

Mysql > source / opt/abc/table.sql;Query OK, 0 rows affected (0.00 sec)... # omit part of Query OK, 0 rows affected (0 secmysql > select * from stu_info) +-+ | id | name | address | +-+ | 1 | zhsan | bj | | 2 | wawu | nj | +-+ 2 rows in set (0.00 sec) 4.2.2 use the mysql command to restore the table

When ​ uses the mysql command to restore the operation of the table, when the backup file contains only the backup of the table, but not the statement to create the library, the library name must be specified and the target library exists.

An example of ​ is as follows: the previous steps are similar to the demonstration of the source command, so they are not explained too much.

[root@localhost data] # mysqldump-uroot-p student stu_info > / opt/abc/table1.sqlEnter password: [root@localhost data] # ls / opt/abc/table1.sql table.sql [root@localhost data] # mysql-uroot-pEnter password: Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 57Server version: 5.7.17 Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c' to clear the current input statement.mysql > use student;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > select * from stu_info +-+ | id | name | address | +-+ | 1 | zhsan | bj | | 2 | wawu | nj | +-+ 2 rows in set (0.00 sec) mysql > drop table stu_info;Query OK, 0 rows affected (0.01 sec) mysql > exitBye

Restore table operations:

[root@localhost data] # mysql-uroot-p student

< /opt/abc/table1.sql Enter password: [root@localhost data]# mysql -uroot -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 59Server version: 5.7.17 Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

Use student;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > select * from stu_info +-+ | id | name | address | +-+ | 1 | zhsan | bj | | 2 | wawu | nj | +-+ 2 rows in set (0.00 sec) 4.2.3 Summary

The operation of the ​ command when restoring the table is basically the same, but the usage environment is different. The difference between the recovery table and the recovery library is that the recovery library needs to create the usage library first, while the recovery data table is not needed, which is determined by the contents of the backup file.

Fifth, the idea of MySQL database backup.

​ MySQL requires regular backups, appropriate backup plans or policies, and strict compliance. In addition to full backup, enabling the log function of the MySQL server is also a top priority. Full backup with logs can maximize the restore of MySQL.

The name of the ​ backup file needs to be unified and easy to understand. It is recommended to use the naming method of the library name with time, which is convenient for others and yourself.

VI. Summary of this article

​ this article mainly talks about the classification of database backup and the practical operation of making backup and restoring data. From the physical and logical point of view, database backup is divided into physical (hot and cold backup) and logical backup; from the point of view of database backup strategy, backup can be divided into full backup, differential backup and incremental backup.

There is a special mysqldump tool backup in ​ MySQL, which generates a script file for SQL. The database recovery operation uses the mysql or source command.

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.