MySQL Full Backup and Recovery
The main content of this paper
- Classification of database backups
- MySQL Full Backup and Recovery
- MySQL Differential Backup and Recovery
- MySQL Incremental Backup Concept
- MySQL backup recovery operation
1. Preface
With the rapid development of office automation and e-commerce, enterprises rely more and more on information systems. Database plays a major role as the core of information systems.Database backup is an important means to recover important data and prevent data loss in case of database loss.A reasonable database backup scheme should be able to recover data effectively when data is lost, taking into account the technical difficulties and efficient use of resources.
2. Concepts and Classifications of Database Backup
2.1 Importance of Data Backup
In a production environment, the security of data in a database is critical, and any loss of data can have serious consequences; the reasons for data loss are as follows:
2.2 Causes of Data Loss
- Program errors; generally less
- Artificial error; this is the most common
- The computer failed; down
- Disk failure; Storage - File system - Distributed
- Disasters; attack s or natural disasters
Therefore, we need to make as many backups as possible of the data in the database to avoid business accidents caused by data loss and to have a negative impact on the company.
Backups of databases can be categorized from different perspectives. Below we describe the specific classification of databases and their concepts.
2.3 Classification of database backups
2.3.1 Physical and Logical Backups
Physical backup: Backup of physical files (data files, log files, etc.) of the database operating system
Physical backups can also be divided into offline backups (cold backups) and online backups (hot backups)
Cold backup: when the database is closed;
Hot backup: The database is running and this backup method relies on the database's log files
Logical backup: Backup of database logical components, such as database objects such as tables
2.3.2 From the database backup strategy is divided into
- Full backup: A full backup of the data at a time
- Differential backup: Back up files that have been modified since the last full backup
- Incremental backup: Only files that have been modified since the last full or incremental backup will be backed up
Advantages and disadvantages of full backup:
- Advantages: Safety, data integrity, simple and convenient operation;
- Disadvantages: redundant backup data consumes system resources; long backup time and recovery time
Advantages and disadvantages of differential backup:
- Advantages: Save resources to a certain extent, compromise security;
- Disadvantages: Once a full backup is suspended, only the backup that differs from it is left;
Advantages and disadvantages of incremental backup:
- Advantages: No redundant data to back up;
- Disadvantages: low security; recovery troubles
Let's use a visual example to illustrate the difference between the three:
| Backup Method | Full backup | Differential backup | Incremental backup |
|---|---|---|---|
| Status at full backup | table1，table2 | table1，table2 | table1，table2 |
| Add content for the first time | Create table3 | Create table3 | Create table3 |
| Backup Content | Backup table1, 2, 3 | Backup table3 | Backup table3 |
| Second Add Content | Create table4 | Create table4 | Create table4 |
| Backup Content | Backup table1, 2, 3, 4 | Backup table3,4 | Backup table4 |
This can be summarized as follows: A full backup takes one backup of all data, a differential backup takes the first full backup, and an incremental backup takes the last (as opposed to the previous backup).
3. MySQL Full Backup
The backup methods of MySQL include full backup and incremental backup.Full backup is the basis of incremental backup.Therefore, in a production environment, both of these methods will be used, and reasonable and efficient schemes need to be developed to backup data.
3.1 Detailed concept of full backup
- A full backup is a backup of the entire database, database structure, and file structure
- A full backup saves the database at the end of the backup
- Full backup is the basis for differential and incremental backups
3.2 Full backup actual operation
There are two ways to backup a MySQL database, because the database is actually a file, you can package the database folder directly, or you can use the special backup tool mysqldump for backup operations.
3.2.1 Backup using tar packaged folder
(1) Install the xz compression format tool, which has a higher compression rate.
[root@localhost ~]# yum install -y xz Plugins loaded: fastestmirror, langpacks base | 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.com Package xz-5.2.2-1.el7.x86_64 is installed and up to date No processing required
(2) Packaging database files in a 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]# ls data-2020-01-07.tar.xz mysql-5.7.17 rh
(3) Comparing occupied space resources
[root@localhost opt]# ls -lh /usr/local/mysql/data/student/ //Total dosage 112K -rw-r-----. 1 mysql mysql 61 1 July 14:11 db.opt -rw-r-----. 1 mysql mysql 8.5K 1 July 14:14 stu_info.frm -rw-r-----. 1 mysql mysql 96K 1 July 14:14 stu_info.ibd [root@localhost opt]# ls -lh /usr/local/mysql/data/fruit/ //Total dosage 148K -rw-r-----. 1 mysql mysql 61 1 June 16:37 db.opt -rw-r-----. 1 mysql mysql 8.5K 1 June 18:30 fruit_info.frm -rw-r-----. 1 mysql mysql 129K 1 July 12:04 fruit_info.ibd [root@localhost opt]# ls -lh data-2020-01-07.tar.xz -rw-r--r--. 1 root root 1.4K 1 July 14:32 data-2020-01-07.tar.xz
(4) If the data in the original directory is lost or damaged, it can be decompressed to reply to the data in the data directory
[root@localhost opt]# cd /usr/local/mysql/data/ [root@localhost data]# ls auto.cnf ibdata1 ibtmp1 student fruit ib_logfile0 mysql sys ib_buffer_pool ib_logfile1 performance_schema [root@localhost data]# rm fruit/ -rf [root@localhost data]# rm student/ -rf [root@localhost data]# ls auto.cnf ib_logfile0 mysql usr ib_buffer_pool ib_logfile1 performance_schema ibdata1 ibtmp1 sys [root@localhost data]# tar Pjxf /opt/data-2020-01-07.tar.xz -C . [root@localhost data]# ls auto.cnf ibdata1 ibtmp1 student fruit ib_logfile0 mysql sys ib_buffer_pool ib_logfile1 performance_schema usr
3.2.2 Backup using the mysqldump tool
We know that compressing packages is not a very good option in practice because it is used to back up everything in the database, and the mysqldump tool gives you more flexibility in controlling the backup content, such as backing up specific databases and tables.
First we have a database system with two databases we created:fruit student
Where fruit has a fruit_info table 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 changed mysql> 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 names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +-----------------+ | Tables_in_fruit | +-----------------+ | fruit_info | +-----------------+ 1 row in set (0.00 sec)
(1) Make a full backup of a table using the mysqldump command in the following format:
Mysqldump-uroot-p [option] [database name] [data table name] >/backup path/backup file name (to end with a.sql suffix name)
Example:
[root@localhost ~]# mysqldump -uroot -p fruit fruit_info > /opt/fruit_bak_$(date +%F).sql Enter password: [root@localhost ~]# ls /opt/ data-2020-01-07.tar.xz fruit_bak_2020-01-07.sql mysql-5.7.17 rh
(2) Make a full backup of a single database using the mysqldump command 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).sql Enter password: [root@localhost ~]# ls /opt/ data-2020-01-07.tar.xz fruit_db_backup-2020-01-07.sql rh fruit_bak_2020-01-07.sql mysql-5.7.17
(3) Make a full backup of multiple databases using the mysqldump command in the following format:
Mysqldump-uroot-p [option] -- databases [database name list] >/backup path/backup file name (to end with a.sql suffix name)
Example:
[root@localhost ~]# mysqldump -uroot -p --databases fruit student > /opt/fruit_and_student_db_backup-$(date +%F).sql Enter password: [root@localhost ~]# ls /opt/ data-2020-01-07.tar.xz fruit_and_student_db_backup-2020-01-07.sql fruit_bak_2020-01-07.sql fruit_db_backup-2020-01-07.sql mysql-5.7.17 rh
(4) Make a full backup of all databases using the mysqldump command 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).sql Enter password: [root@localhost ~]# ls /opt/ all_db_backup-2020-01-07.sql data-2020-01-07.tar.xz fruit_and_student_db_backup-2020-01-07.sql fruit_bak_2020-01-07.sql fruit_db_backup-2020-01-07.sql mysql-5.7.17 rh
(5) Use the mysqldump command to directly back up the table structure or the entire data table in the following format:
Mysqldump-uroot-p [-d] [database name] [data table name] >/backup path/backup file name (to end with.sql suffix name)
Example:
[root@localhost ~]# mysqldump -uroot -p -d fruit fruit_info > /opt/table_structure.sql Enter password: [root@localhost ~]# ls /opt/ all_db_backup-2020-01-07.sql data-2020-01-07.tar.xz fruit_and_student_db_backup-2020-01-07.sql fruit_bak_2020-01-07.sql fruit_db_backup-2020-01-07.sql mysql-5.7.17 rh table_structure.sql [root@localhost ~]# mysqldump -uroot -p fruit fruit_info > /opt/table_structure-$(date +%F).sql Enter password: [root@localhost ~]# ls /opt/ all_db_backup-2020-01-07.sql data-2020-01-07.tar.xz fruit_and_student_db_backup-2020-01-07.sql fruit_bak_2020-01-07.sql fruit_db_backup-2020-01-07.sql mysql-5.7.17 rh table_structure-2020-01-07.sql table_structure.sql
Let's compare the contents of backup files with option-d and without
With the -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,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,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,2.50,'banana'),(2,5.50,'apple'),(3,6.00,'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:
You can see that with the -d option, only the structure of the table is backed up, and without the -d parameter, the entire data table is backed up
4. Full database recovery
The specific operations described above for a full backup of a database can be restored in several ways when data errors occur
4.1 Restore entire library operation
You can use the source and mysql commands when you need to restore the entire library
4.1.1 Source command whole library recovery
Command format: source backup script path (absolute path)
Example:
Using the example above for backing up the fruit database, simulate deleting and restoring the library
Method: Generate backup data file -- "Log on to database --" Delete database --"Use source command to restore
Operation:
[root@localhost data]# mysqldump -uroot -p student > /opt/student.sql Enter password: [root@localhost data]# ls /opt/ data-2020-01-07.tar.xz mysql-5.7.17 rh student.sql
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)
mysql> create database student
-> ;
Query OK, 1 row affected (0.00 sec)
mysql> use student;
Database changed
mysql> source /opt/student.sql
Query 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)
Query OK, 0 rows affected (0.00 sec)
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.2 MySQL command whole library recovery
Instead of logging in to the mysql database system, you can use the mysql command to recover the entire library directly.
Examples are as follows:
[root@localhost data]# mysqldump -uroot -p student > /opt/student.sql Enter password: [root@localhost data]# ls /opt/ data-2020-01-07.tar.xz rh student.sql mysql-5.7.17 student1.sql
The analog database is missing:
[root@localhost data]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 41 Server version: 5.7.17 Source distribution Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. 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 43 Server version: 5.7.17 Source distribution Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database student; Query OK, 1 row affected (0.01 sec) mysql> exit Bye [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 47 Server version: 5.7.17 Source distribution Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. 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 names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from stu_info; +----+-------+---------+ | id | name | address | +----+-------+---------+ | 1 | zhsan | bj | | 2 | wawu | nj | +----+-------+---------+ 2 rows in set (0.00 sec)
4.2.3 Summary
In mysql version 5.7, you need to create a library before you can use the source or mysql commands for full library recovery.
4.2 Restore data table operations (similar to full library restore)
4.2.1 Use the source command to restore tables
(1) Create a directory of recovery files to store backup table files:
[root@localhost opt]# mkdir abc [root@localhost opt]# cd - /usr/local/mysql/data [root@localhost data]# ls auto.cnf ibdata1 ibtmp1 student fruit ib_logfile0 mysql sys ib_buffer_pool ib_logfile1 performance_schema usr [root@localhost data]# mysqldump -uroot -p student stu_info > /opt/abc/table.sql Enter password: [root@localhost data]# ls /opt/abc/ table.sql
(2) View the contents of database data tables
[root@localhost data]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 52 Server version: 5.7.17 Source distribution Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. 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 names You can turn off this feature to get a quicker startup with -A Database changed mysql> 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 from simulation table files
mysql> drop table stu_info; Query OK, 0 rows affected (0.00 sec) mysql> show tables; Empty set (0.00 sec)
(4) Sorce command recovery data table
mysql> source /opt/abc/table.sql; Query OK, 0 rows affected (0.00 sec) ...#Omit some content Query OK, 0 rows affected (0.00 sec mysql> 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 tables
When restoring a table using the mysql command, you must specify the name of the library whose target inventory is in when the backup file contains only a backup of the table, not the statement that created the library.
Examples are as follows: The previous steps are similar to the demonstration of the source command without much explanation
[root@localhost data]# mysqldump -uroot -p student stu_info > /opt/abc/table1.sql Enter password: [root@localhost data]# ls /opt/abc/ table1.sql table.sql [root@localhost data]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 57 Server version: 5.7.17 Source distribution Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. 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 names You can turn off this feature to get a quicker startup with -A Database changed mysql> 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> exit Bye
Restore table operations:
[root@localhost data]# mysql -uroot -p student < /opt/abc/table1.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 59 Server version: 5.7.17 Source distribution Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. 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 names You can turn off this feature to get a quicker startup with -A Database changed mysql> 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 commands are basically the same when restoring tables, except in different environments. The difference between restoring tables and restoring libraries is that the restoring libraries need to create the usage libraries first, while restoring data tables does not. This is determined by the contents of the backup files.
5. MySQL database backup ideas
MySQL requires regular backups, proper backup plans or strategies, and strict compliance.In addition to full backup, turning on the logging function of MySQL server is also a top priority. Full backup with log can maximize the restore of MySQL.
The name of the backup file needs to be a unified and easy-to-understand name. It is recommended to use the name of the library in conjunction with the time to make it easier for others and themselves to use.
6. Summary of this paper
This paper mainly describes the classification of database backup and the practice of making backup and restoring data.Database backup is divided into physical (hot and cold) and logical backup from the physical and logical point of view. From the backup strategy of database, backup can be divided into full backup, differential backup and incremental backup.
mysql has a special backup of the mysqldump tool that generates a script file for SQL.The database recovery operation uses the mysql or source commands.