The simplest MySQL data backup and restore tutorial in history (Part 2) (Part 37)

Data backup and restore part 3, details are as follows

Basic concepts:

Backup , save another copy of the current data or records;

Restore : restore the data to the state at the time of backup.

Why do we need to back up and restore data?

Prevent data loss;

Protect data records.

There are many ways to back up and restore data, which can be divided into: data table backup, single table data backup, SQL backup and incremental backup.

SQL backup

SQL backup backs up SQL statements. When performing SQL backup, the system will process the table structure and data, convert them into corresponding SQL statements, and then execute the backup. When restoring, you only need to execute the backed-up SQL statements. This backup method is mainly for the table structure.

However, MySQL does not provide SQL backup instructions. If we want to perform SQL backup, we need to use the software mysqldump.exe provided by MySQL. Moreover, mysqldump.exe is also a client, so authentication must be performed when operating the server.

Basic syntax: mysqldump.exe -hPup + database name + [table name 1 + [table name 2]] > backup file directory

Among them, -hPup represents

h: IP or localhost;

P: port number;

u: user name;

p: password.

Since mysqldump.exe is also a client, to execute the above command, we need to exit the MySQL client first, 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 the figure above, although mysqldump gave a warning (it is not safe to enter a password in the command line), the command we entered has been successfully executed! Here, if the above command fails to execute successfully, it is likely that we have not configured the environment variables.

As shown in the figure above, in the testMySQL directory, a SQL backup of the table class has been generated. As for what the content of the SQL backup is, we can open the classSQL.sql file to view it:

As shown in the figure above, the file backed up by SQL contains various SQL statements, such as statements for creating tables and inserting data.

In addition, in the basic syntax for executing SQL backup given above, we can see that the table names are enclosed in [] , which indicates optional items. If the table name is not entered, the entire database will be backed up by default. The execution process is the same as above, so we will not demonstrate it!

Next, we will demonstrate how to restore data from SQL backup files in two ways:

Method 1 : Use mysql.exe client to restore data

Basic syntax mysql.exe/mysql -hPup database name + database name + [table name 1 + [table name 2]] < backup file directory

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

-- Log in to the MySQL client mysql -uroot -p

-- Enter the password and switch to database use test;

-- Delete the data in the class table delete from class;

-- Exit database\q

-- Restore data through SQL backup file mysql -uroot -pbin.guo test < D:/CoderLife/testMySQL/classSQL.sql

As shown in the figure above, all the above commands were executed successfully. Next, we check the restoration results.

As shown in the figure above, after the data in the table class was deleted, we restored the data through the SQL backup file.

Method 2 : Use SQL commands to restore data

Basic syntax source + backup file directory;

Execute the above SQL statement to test:

-- View the class table data select * from class;

-- Delete table class data delete from class;

-- View the class table data select * from class;

-- Restore data through SQL backup file source D:/CoderLife/testMySQL/classSQL.sql;

-- View the class table data select * from class;

As shown in the figure above, after the data in the table class was deleted, we restored the data using the second method through the SQL backup file.

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

Advantages: table structure can be backed up; Disadvantages: adding additional SQL commands will waste disk space.

Incremental backup

Incremental backup does not back up data or SQL, but backs up the log of the MySQL server. The log content includes the historical records of various operations we have on the database, such as addition, deletion, modification and query. In addition, incremental backup is performed at a specified time period, so the backed up data generally will not be duplicated, and is often used for data backup of large projects. We will not introduce it in detail here. As for this part, we will write a separate blog post on how to perform incremental backup in the future.

Tips : The content enclosed by the symbol [] indicates optional items; the symbol + means connection.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.