What is the sql statement to back up the database?

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

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

The main function of the 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 unexpected 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 an accident occurs, the loss will be minimized.

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, thereby restoring the database from an error state to the correct state at the time of backup.

MySQL provides two backup methods, namely the mysqldump command and the mysqlhotcopy script. Since mysqlhotcopy can only be used with MyISAM tables, the mysqlhotcopy script was removed in MySQL 5.7.

Use the mysqldump command to back up the database

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

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

mysqldump -u username -p dbname [tbname ...]> filename.sql The above syntax parameters are explained as follows:

username : Indicates the user name;

dbname : Indicates the name of the database that needs to be backed up;

tbname : Indicates the data tables in the database that need to be backed up. Multiple data tables can be specified. When this parameter is omitted, the entire database will be backed up;

right arrow " > ": used to tell mysqldump to write the definition and data of the backup data table into the backup File;

filename.sql : Indicates the name of the backup file, and the absolute path can be added in front of the file name. The database is usually backed up into a file with the suffix .sql.

Note: The files backed up by the mysqldump command do not necessarily need to have the suffix .sql. Files backed up in other formats can also be backed up. For example, files with the suffix .txt. Under normal circumstances, it is recommended to back up files with the suffix .sql. Because files with the suffix .sql give people the first impression that they are files related to the database.

Example:

The following uses the root user to back up the student table under the test database. Open the command line (cmd) window and enter the backup command and password. The running process is as follows:

C:\Windows\system32>mysqldump -uroot -p test student>C:\student.sql Enter password: **** Note: the mysqldump command must be executed in the cmd window and cannot be logged in to the MySQL service.

After entering the password, MySQL will back up the student data table under the test database. Then you can view the files you just backed up under the specified path. Part of the content in the student.sql file is as follows:

-- MySQL dump 10.13 Distrib 5.7.29, for Win64 (x86_64) -- -- Host: localhost Database: test -- ------------------------------------------------------ -- Server version 5.7.29-log /*!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, `stuno` int(11) DEFAULT NULL, `age` int(4) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `student` -- LOCK TABLES `student` WRITE; /*!40000 ALTER TABLE `student` DISABLE KEYS */; INSERT INTO `student` VALUES (1,'zhangsan',23,18),(2,'lisi',24,19),(3,'wangwu',25,18),(4,'zhaoliu',26,18); /*!40000 ALTER TABLE `student` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; ...... -- Dump completed on 2019-03-09 13:03:15 student.sql The beginning of the file records the version of MySQL, the backup host name and the database name.

In the file, all comments starting with "--" are SQL language comments. Comments starting with "/*!40101" and other forms are related to MySQL. 40101 is the version number of the MySQL database, which here means MySQL 4.1.1. If the MySQL version is higher than 4.1.1 when restoring data, the content between "/*!40101" and "*/" will be executed as SQL commands. If lower than 4.1.1, the content between "/*!40101" and "*/" is treated as a comment. Contents in "/*!" and "*/" will be ignored as comments in other databases, which can improve database portability.

DROP statement, CREATE statement and INSERT statement 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. If it exists, delete it This table; the CREATE statement is used to create the student table; the INSERT statement is used to restore all data. The backup time is recorded at the end of the file.

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

After execution, you can see a file named all.sql under C:\ . This file stores information about all databases.

Related free learning recommendations: mysql video tutorial

What is the sql statement to back up the database?

Related articles