mysqldump

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

Note that the following instructions apply to a very old version, and details may have changed since then.

Backing Up

Local

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

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.

Restoring

If you are restoring to a different database name (as might happen if you were moving the db to a new server), you will need to edit the .sql file to use the appropriate database name in the line which begins "CREATE DATABASE" and the "USE" line following it. If you know the database has already been created, you can just delete the CREATE DATABASE line.

You might also want to verify that the mysql utility connects successfully before you try the import:

mysql -hserver -uusername -ppassword

If that works, then the full command to create a database from a .sql file is:

mysql -hserver -uusername -ppassword < path/to/backup/file.sql

Note that the path is relative to the system on which you are running the mysql utility, not the system running the mysql server (mysqld). Note also that this technique doesn't tell you anything until it finishes processing the .sql file. You can also specify the database in which the SQL commands should be run:

mysql -hserver -uusername -ppassword -Ddbname < path/to/backup/file.sql

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).
  • 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.