Usage of Linux mysqldump command


Mysqldump is a database logical backup program. It is usually used to back up or restore one or more MySQL databases. In addition, it can also transfer the databases to other MySQL servers. Next, Liang Xu Xiaobian willLinux mysqldump commandI hope it will be helpful to you.


When using mysldump to back up database tables, the account must have select permission, show view permission is used to back up views, and trigger permission is used to back up triggers.

Note that other command options may require more permissions to complete.

Because mysqldump needs to rebuild SQL statements to realize the backup function, the speed of database backup and restore operations with large amount of data is relatively slow, so mysqldump is not suitable for big data backup. When you open the mysqldump backup file, the content of the backup file is the SQL language reproduction of the database. For the backup and restore of big data, physical backup is usually selected, that is, copying data files directly, so as to realize rapid data restore.

Using mysqldump, you can back up the data tables in the database, the entire database, and all databases in the MySQL system. For database files backed up by MySQL dump tool, you can use MySQL command tool to restore data.

Note that when backing up the entire database, you cannot use the name of the data table after the database.

The syntax format of mysqldump command is as follows:

[ [email protected] ~]#Mysqldump [options] DB_ name [table_name]
[ [email protected] ~]#Mysqldump [options] – databases DB_ name …
[ [email protected] ~]#Mysqldump [options] – all databases

Common options in mysqldump can be written to the configuration file through [mysqldump] and [Client]. The common options and descriptions of mysqldump command are shown in Table 1.

Table 1 common options and descriptions of mysqldump command

Options explain
–add-drop-database Add and delete SQL statements of the same database in the backup file
–add-drop-table Add and delete SQL statements of the same data table in the backup file
–add-drop-trigger Add and delete SQL statements of the same trigger in the backup file
–add-locks Add table locking and unlocking SQL statements before and after backing up the data table
–all-databases Back up data tables in all databases
–apply-slave-statements Add a stop slave statement before the change master
–bind-address=ip_address Use the specified network interface to connect to the MySQL server
–comments Add notes for backup files
–create-options Include all MySQL features in the CREATE TABLE statement
–databases Back up the specified database
–debug Create debugging log
–default-character-set=charsename Set default character set
–host,-h Set the host to which you want to connect
–ignore-table Set data tables that do not need to be backed up. This option can be used multiple times
–lock-all-tables Set a global lock to lock all data tables to ensure the integrity of backup data
–no-create-db,-n Export data without creating a database
–no-create-info Export data without creating a data table
–no-date The data content is not backed up. It is used to back up the table structure
–password,-p You can also connect to the server with a password
–port=port_num Connect to the server using the specified port number
–replace Replace the insert statement with the replace statement

The usage of mysqldump tool is as follows:

  1. Back up all databases as follows:

[[email protected] ~]# mysqldump -u root -p –all-databases > all database sql
Enter password:

  1. Back up the user packet under the MySQL database, as shown below:

[[email protected] ~]# mysqldump -u root -p myaql user > user_table
Enter password:

  1. Use all_ database_ The SQL database backup file restores the database as follows:

[[email protected] ~]# mysql -u root -p myaql < all-database_sql
Enter password:

  1. Use user_ Restore the data table from the table database backup file, as shown below:

[[email protected] ~]# mysql -u root -p myaql < user_table
Enter password:

Note that all backup and restore operations must enter the password after entering the command.

The above isLiangxu tutorial networkShare the usage of Linux mysqldump command for all friends.

This article is composed of blog one article multi posting platformOpenWriterelease!

Recommended Today

OC basis

IOS development interview essential skills chart.png What are objects and what are the objects in OC? An object is an instance of a class; Is an instance created through a class, which is generally called an instance object; Common objects in OC include instance objects, class objects, and metaclass objects; What is a class? What […]