Shulou(Shulou.com)05/31 Report--

Editor to share with you how to write the sql statement of the backup database, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

The sql statement of the backup database is "mysqldump", with the syntax format "mysqldump-u user name-p database name [data table name.] > backup file name .sql"; where ">" is used to tell mysqldump to write the definition of the backup data table and data to the backup file.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

The main function of database is to save and maintain data, so backing up data is the most commonly used operation in database management. In order to prevent data loss caused by accidental database crash or hardware damage, the database system provides backup and recovery strategies.

One of the most important measures to ensure data security is to back up the database regularly. In this way, even if there is an accident, the loss will be kept to a minimum.

Database backup refers to making a copy of the database by exporting data or copying table files. When the database fails or is damaged, the backed-up database is loaded into the system so that the database can be restored from the error state to the correct state at the time of backup.

Two backup methods are provided in MySQL, namely, the mysqldump command and the mysqlhotcopy script. Because mysqlhotcopy can only be used with MyISAM tables, MySQL 5.7 removes the mysqlhotcopy script.

Use the mysqldump command to back up the database

When the mysqldump command is executed, the data in the database can be backed up as a text file. The structure and data of the datasheet are stored in the generated text file.

The syntax format for backing up a database using the mysqldump command is as follows:

Mysqldump-u username-p dbname [tbname...] > filename.sql

The above syntax parameters are described as follows:

Username: represents the user name

Dbname: indicates the name of the database to be backed up

Tbname: represents the data tables in the database that need to be backed up, and multiple data tables can be specified. When this parameter is omitted, the entire database is backed up

Right arrow ">": used to tell mysqldump to write the definition and data of the backup data table to the backup file

Filename.sql: indicates the name of the backup file, which can be preceded by an absolute path. The database is usually backed up to a file with the suffix .sql.

Note: files backed up by the mysqldump command do not necessarily require a suffix of .sql, and files backed up to other formats are also possible. For example, a file with the suffix .txt. In general, it is recommended that you back up as a file with the suffix .sql. Because files with the suffix .sql give people the first impression that they are database-related files.

Example:

Let's use the root user to back up the student table under the test database. Open a command line (cmd) window and enter the backup command and password as follows:

C:\ Windows\ system32 > mysqldump-uroot-p test student > C:\ student.sqlEnter password: *

Note: the mysqldump command must be executed under the cmd window and cannot be executed in the MySQL service.

After entering the password, MySQL backs up the student data table under the test database. Then you can view the files that have just been backed up under the specified path. Parts of the student.sql file are as follows:

-- MySQL dump 10.13 Distrib 5.7.29, for Win64 (x861464)-Host: localhost Database: test-- Server version 5.7.29Murray logpool / 40101 SET @ OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT * / / *! 40101 SET @ OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS * /;-Table structure for table `student`-- DROP TABLE IF EXISTS `student`; / *! 40101 SET @ saved_cs_client = @ @ character_set_client * /; / *! 40101 SET character_set_client = utf8 * / CREATE TABLE `student` (`id` int (4) NOT NULL, `name` varchar (20) DEFAULT NULL, `studo` int (11) DEFAULT NULL, `age` int (4) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSETroomlatin1 / / 40101 SET character_set_client = @ saved_cs_client * /;-Dumping data for table `student`-LOCK TABLES `student` WRITETransverse 40000 ALTER TABLE `student` DISABLE KEYS * / INSERT INTO `student` VALUES, (2 recorder SET TIME_ZONE=@OLD_TIME_ZONE 19), (3 recorder UNLOCK 2019-03-09 13:03:15); / *! 40000 ALTER TABLE `student` SET TIME_ZONE=@OLD_TIME_ZONE * /;

The student.sql file begins by recording the version of MySQL, the hostname of the backup, and the database name.

In the file, what starts with a "-" is a comment from the SQL language. Starting with "/ *! 40101" and so on are comments related to MySQL. 40101 is the version number of the MySQL database, which means MySQL 4.1.1. If the version of MySQL is higher than 4.1.1 when recovering data, the content between "/ *! 40101" and "* /" is executed as a SQL command. If lower than 4.1.1, the content between "/ *! 40101" and "* /" is treated as a comment. "/ *!" The contents of and "* /" will be ignored as comments in other databases, which can improve the portability of the database.

DROP statements, CREATE statements, and INSERT statements are all used during database recovery; the "DROP TABLE IF EXISTS 'student'" statement is used to determine whether there is a table named student in the database and, if so, to delete the table; the CREATE statement is used to create the student table; and the INSERT statement is used to recover all data. The backup time is recorded at the end of the file.

Note: there is no statement to create a database in the above student.sql file, so all tables and records in the student.sql file must be restored to an existing database. When you recover data, the CREATE TABLE statement creates a table in the database, and then executes the INSERT statement to insert records into the table.

After execution, you can see a file called all.sql under C:\, which stores all the database information.

The above is all the contents of the article "how to write the sql statement of backup database". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.