History of the simplest MySQL Data Backup and restoration tutorial (below) (), mysql Data Backup

History of the simplest MySQL Data Backup and restoration tutorial (below) (), mysql Data Backup

The third part of data backup and restoration is as follows:

Basic concepts:

BackupSave the existing data or records in another copy;

RestoreTo restore the data to the backup status.

Why do we need to back up and restore Data?

Prevent data loss;

Protect data records.

There are many data backup and restoration methods, including data table backup, single table data backup, SQL backup, and Incremental backup.

SQL backup

SQL backup: backs up SQL statements. During SQL backup, the system processes the table structure and data into corresponding SQL statements, and then executes the backup. During restoration, you only need to execute the backup SQL statement. This backup mode is mainly for the table structure.

However, MySQL does not provide SQL backup commands. If you want to perform SQL backup, you need to use MySQL connector's software mysqldump.exe, and mysqldump.exe is also a client. Therefore, you must perform authentication when operating the server.

Basic Syntax:Mysqldump.exe-hPup + database name + [Table Name 1 + [Table name 2]> Backup file directory

-HPup indicates

H: IP address or localhost;

P: port number;

U: user name;

P: password.

Because mysqldump.exe is also a client, to execute the preceding command, you must first exit the MySQL client and then execute the following command in the command line window:

mysqldump.exe -uroot -pbin.guo test class > D:/CoderLife/testMySQL/classSQL.sql

As shown in, although mysqldump provides a warning (it is insecure to enter the password in the command line), the command we entered has been successfully executed! Here, if the above command fails to be executed, it is likely that we have not configured the environment variable.

As shown in, an SQL backup of the table class has been produced under the testMySQL directory. As for the content of the SQL backup, you can open the classSQL. SQL file to view it:

As shown in, SQL backup files contain various SQL statements, such as statements used to create tables and insert data.

In additionBasic syntax, We can see that all the table names use[]If the table name is not input, the entire database is backed up by default. The execution process is the same as above, so we will not demonstrate it!

Next, we will demonstrate two ways to restore data through SQL backup files:

Method 1: Use the mysql.exe client to restore Data

Basic syntaxMysql.exe/mysql-hPup database name + [Table Name 1 + [Table name 2] <backup file directory

Run the following command in the command line window to test:

-- Log on to the MySQL client mysql-uroot-p -- enter the password and switch to the database use test; -- delete the data in the table class from class; -- exit database \ q -- restore Data mysql-uroot-pbin through SQL backup files. guo test <D:/CoderLife/testMySQL/classSQL. SQL

As shown in, all the preceding commands are successfully executed. Next, we check the restoration result,

As shown in, obviously, after the table class data is deleted, we restored the data through the SQL backup file.

Method 2: Use SQL commands to restore Data

Basic syntaxSource + backup file directory;

Run the preceding SQL statement for testing:

-- View the table class data select * from class; -- delete the table class data delete from class; -- view the table class data select * from class; -- restore the data source D through the SQL backup file: /CoderLife/testMySQL/classSQL. SQL; -- View table class data select * from class;

As shown in, it is clear that after the table class data is deleted, we use the second method to restore the data using the SQL backup file.

Through the above learning and testing, we can know the advantages and disadvantages of SQL backup:

Advantage: You can back up the table structure. disadvantage: adding additional SQL commands will waste disk space.

Incremental Backup

Incremental backup is not a backup of data or SQL, but a backup of MySQL server logs. The log content includes the history of various database operations, such as addition, deletion, modification, and query. In addition, Incremental backup is performed during a specified period of time, so the backup data is generally not duplicated and is often used for data backup in large projects. Here, we will not go into detail. As for this part, we will write a separate blog post on how to perform Incremental backup.

Tip: The content enclosed by the symbol [], indicating the option. The symbol + indicates the meaning of the connection.

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.