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:
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
|–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:
- Back up all databases as follows:
[[email protected] ~]# mysqldump -u root -p –all-databases > all database sql
- Back up the user packet under the MySQL database, as shown below:
[[email protected] ~]# mysqldump -u root -p myaql user > user_table
- Use all_ database_ The SQL database backup file restores the database as follows:
[[email protected] ~]# mysql -u root -p myaql < all-database_sql
- Use user_ Restore the data table from the table database backup file, as shown below:
[[email protected] ~]# mysql -u root -p myaql < user_table
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!