mysqldump

MySQL database backup tool

Supplementary instructions

mysqldump command is a backup tool in the mysql database. It is used to export the database in the MySQL server in the standard sql language and save it to a file.

grammar

mysqldump(options)

Options

--add-drop-table: Add a statement to delete a database table before each statement to create a database table;
--add-locks: Lock the database table when backing up the database table;
--all-databases: Back up all databases on the MySQL server;
--comments: add comment information;
--compact: compression mode, produces less output;
--complete-insert: Output the completed insert statement;
--databases: Specify the databases to be backed up;
--default-character-set: Specify the default character set;
--force: continue the backup operation when an error occurs;
--host: Specify the server to back up the database;
--lock-tables: lock all database tables before backup;
--no-create-db: prohibit generating create database statements;
--no-create-info: Disable generating database table statements;
--password: Password for connecting to the MySQL server;
--port: The port number of the MySQL server;
--user: Username to connect to the MySQL server.
--skip-lock-tables: Export tables without locking them

Example

Export the entire database

mysqldump -u username -p database name > exported file name
mysqldump -u linuxde -p smgp_apps_linuxde > linuxde.sql

Export a table

mysqldump -u username -p database name table name > exported file name
mysqldump -u linuxde -p smgp_apps_linuxde users > linuxde_users.sql

Export a database structure

mysqldump -u linuxde -p -d --add-drop-table smgp_apps_linuxde > linuxde_db.sql

-d has no data, --add-drop-table adds a drop table before each create statement

problem solved

Lock table failed

mysqldump: Got error: 1044: "Access denied for user 'appuser'@'1%' to database 'tc_mall'" when doing LOCK TABLES

You can use --skip-lock-tables to skip the lock table process during the data export phase.