mysqldump

About

mysqldump is a command line interface (CLI) utility provided with MySQL. The command is also provided by MariaDB, where it is a link to an executable called mariadb-dump.

Its entire purpose in life is exporting data. (For importing data, see MySQL/migration/CLI/import.)

Commands

The exact commands to use depend on whether you're exporting from a local or remote database.

Local DB

Official MySQL documentation: Chapter 6. Backup and Recovery

For all of these examples:

• If you leave off the <password>, it will prompt you for one.
• To connect to a MySQL server on localhost, omit -h<hostname>. not tested
• see /remote for backing up from a remote server without storing the backup file remotely

From a shell command line:

mysqldump --tab=<path> -h<hostname> -u<username> -p<password> --opt <dbname>

This saves SQL code to recreate the entire database in .sql files compatible with the restoring method given below.

• This will create one .sql file per table, in the folder specified by <path> (which must already exist).

mysqldump --tab=</output/path> -h<hostname> -u<username> -p --opt <dbname> <table_name_1> <table_name_2> <...>

This only dumps tables.

mysqldump -h<hostname> -u<username> --opt <dbname> > <filespec>

This dumps the entire database to a single file named <filespec> (which will be created).

mysqldump --all-databases -hhost -uuser -ppassword > name_of_dump_file.sql

This backs up all databases to a single file.

Questions:

• Not sure what the --opt flag does.
• I don't know if there's a way to dump all dbs to individual files.

To copy the dump file from one server to another, rsync generally works well.

For more specific backups, the following mysql client commands are relevant:

SHOW CREATE DATABASE <dbname>;

Returns the SQL code which could be used to recreate the database, not including the contents.

SHOW CREATE TABLE <tablename>;

Returns the SQL code which could be used to recreate<tablename>, not including the data.

Remote DB

To backup data from a remote server onto the local machine, via direct connection:

mysqldump -h <remote_host> -u <username> -p <database_name> | gzip > <local_backup_file>.sql.gz

To backup data from a remote server onto the local machine via an ssh tunnel:

First, set up the tunnel:

ssh -L 3307:localhost:3306 <remote username>@<remote hostname>

That will open a ssh terminal session, as well as creating the tunnel. In a separate terminal session, run this:

mysqldump -P 3307 -h 127.0.0.1 -u <username> -p <database_name> > <local_backup_file>.sql

To close the tunnel, exit the ssh session in the first terminal window.

Links

• 2009-01-06 PHP, MySQL and SSH Tunneling (Port Forwarding)
  • Although this article is quite old, it still seems accurate as of December 2024.
  • ...specifically including the fact that PHP's ssh2 library does not appear to support using a different port locally than remotely (which can cause conflicts).
    • ...although actually, I think maybe it is doable by setting up the "context" resource properly. To Be Investigated. 2026-02-24
  • That said, I've consistently had trouble trying to open tunnels with this methodology.
    • -- Woozle 2024-12-04

Notes

• Error message: "The MySQL server is running with the --secure-file-priv option so it cannot execute this statement"
  • Meaning: MySQL has been configured to only allow imports or exports from within a specified folder.
  • Solution: Check the secure_file_priv setting (can be found in MySQL Workbench under Server -> Status and System Variables -> System Variables tab; current Ubuntu default is "/var/lib/mysql-files/") to see where you should be putting your import/export files so as not to trigger this error. ...or you can stop the server, change the setting, restart, and try again.