mysqldump - Revision history

Woozle at 18:24, 1 March 2026

2026-03-01T18:24:41Z

← Older revision		Revision as of 18:24, 1 March 2026
Line 2:		Line 2:
	[[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]].		[[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]].		Its entire purpose in life is exporting data. (For importing data, see [[MySQL/migration/CLI]]{{l/pfx\|MySQL/migration/CLI\|/import}}.)

	==Commands==		==Commands==

Woozle at 01:55, 25 February 2026

2026-02-25T01:55:22Z

← Older revision		Revision as of 01:55, 25 February 2026
Line 1:		Line 1:
	[[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]].		==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]].

	~~Note that the following instructions apply to a very old version~~, ~~and details may have changed since then~~.		Its entire purpose in life is exporting data. (For importing data, see [[MySQL/migration/CLI/import]].
	==~~Backing Up~~==
	===Local===		==Commands==
			The exact commands to use depend on whether you're exporting from a local or remote database.
			===Local DB===
	'''Official MySQL documentation''': [http://dev.mysql.com/doc/refman/5.5/en/backup-and-recovery.html Chapter 6. Backup and Recovery]		'''Official MySQL documentation''': [http://dev.mysql.com/doc/refman/5.5/en/backup-and-recovery.html Chapter 6. Backup and Recovery]

Line 31:		Line 34:
	;SHOW CREATE DATABASE {{arg\|dbname}}; : Returns the SQL code which could be used to recreate the database, ''not'' including the contents.		;SHOW CREATE DATABASE {{arg\|dbname}}; : Returns the SQL code which could be used to recreate the database, ''not'' including the contents.
	;SHOW CREATE TABLE {{arg\|tablename}}; : Returns the SQL code which could be used to recreate{{arg\|tablename}}, ''not'' including the data.		;SHOW CREATE TABLE {{arg\|tablename}}; : Returns the SQL code which could be used to recreate{{arg\|tablename}}, ''not'' including the data.
	===Remote===		===Remote DB===
	To backup data from a remote server onto the local machine, via direct connection:		To backup data from a remote server onto the local machine, via direct connection:
	mysqldump -h {{arg\|remote_host}} -u {{arg\|username}} -p {{arg\|database_name}} \| gzip > {{arg\|local_backup_file}}.sql.gz		mysqldump -h {{arg\|remote_host}} -u {{arg\|username}} -p {{arg\|database_name}} \| gzip > {{arg\|local_backup_file}}.sql.gz
Line 44:		Line 47:

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

	~~==Restoring==~~
	~~'''Official MySQL documentation''': [https://dev.mysql.com/doc/refman/8.4/en/recovery-from-backups.html 9.3.2 Using Backups for Recovery]~~

	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 -h<u>server</u> -u<u>username</u> -p<u>password</u>~~
	~~If that works, then the full command to create a database from a .sql file is:~~
	~~mysql -h<u>server</u> -u<u>username</u> -p<u>password</u> < <u>path/to/backup/file.sql</u>~~
	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 -h<u>server</u> -u<u>username</u> -p<u>password</u> -D<u>dbname</u> < <u>path/to/backup/file.sql</u>~~

	==Links==		==Links==
	* '''2009-01-06''' [https://blog.rjmetrics.com/2009/01/06/php-mysql-and-ssh-tunneling-port-forwarding/ PHP, MySQL and SSH Tunneling (Port Forwarding)]		* '''2009-01-06''' [https://blog.rjmetrics.com/2009/01/06/php-mysql-and-ssh-tunneling-port-forwarding/ PHP, MySQL and SSH Tunneling (Port Forwarding)]
	** Although this article is quite old, it still seems accurate as of December 2024.		** 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).		** ...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.		** That said, I've consistently had trouble trying to open tunnels with this methodology.
	*** -- {{woozle}} 2024-12-04		*** -- {{woozle}} 2024-12-04

Woozle: /* Restoring */

2025-02-13T00:57:47Z

Restoring

← Older revision		Revision as of 00:57, 13 February 2025
Line 46:		Line 46:

	==Restoring==		==Restoring==
			'''Official MySQL documentation''': [https://dev.mysql.com/doc/refman/8.4/en/recovery-from-backups.html 9.3.2 Using Backups for Recovery]

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

Line 55:		Line 57:
	You can also specify the database in which the SQL commands should be run:		You can also specify the database in which the SQL commands should be run:
	mysql -h<u>server</u> -u<u>username</u> -p<u>password</u> -D<u>dbname</u> < <u>path/to/backup/file.sql</u>		mysql -h<u>server</u> -u<u>username</u> -p<u>password</u> -D<u>dbname</u> < <u>path/to/backup/file.sql</u>

	==Links==		==Links==
	* '''2009-01-06''' [https://blog.rjmetrics.com/2009/01/06/php-mysql-and-ssh-tunneling-port-forwarding/ PHP, MySQL and SSH Tunneling (Port Forwarding)]		* '''2009-01-06''' [https://blog.rjmetrics.com/2009/01/06/php-mysql-and-ssh-tunneling-port-forwarding/ PHP, MySQL and SSH Tunneling (Port Forwarding)]

Woozle at 13:29, 4 December 2024

2024-12-04T13:29:41Z

← Older revision		Revision as of 13:29, 4 December 2024
Line 55:		Line 55:
	You can also specify the database in which the SQL commands should be run:		You can also specify the database in which the SQL commands should be run:
	mysql -h<u>server</u> -u<u>username</u> -p<u>password</u> -D<u>dbname</u> < <u>path/to/backup/file.sql</u>		mysql -h<u>server</u> -u<u>username</u> -p<u>password</u> -D<u>dbname</u> < <u>path/to/backup/file.sql</u>
			==Links==
			* '''2009-01-06''' [https://blog.rjmetrics.com/2009/01/06/php-mysql-and-ssh-tunneling-port-forwarding/ 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==		==Notes==
	* '''Error message''': "The MySQL server is running with the --secure-file-priv option so it cannot execute this statement"		* '''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.		** '''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.		** '''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.

Woozle: /* Remote */

2024-11-04T17:35:48Z

Remote

← Older revision		Revision as of 17:35, 4 November 2024
Line 36:		Line 36:

	To backup data from a remote server onto the local machine via an ssh tunnel:		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 {{arg\|remote username}}@{{arg\|remote hostname}}		ssh -L 3307:localhost:3306 {{arg\|remote username}}@{{arg\|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 {{arg\|username}} -p {{arg\|database_name}} > {{arg\|local_backup_file}}.sql		mysqldump -P 3307 -h 127.0.0.1 -u {{arg\|username}} -p {{arg\|database_name}} > {{arg\|local_backup_file}}.sql

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

	==Restoring==		==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.		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.

Woozle at 17:22, 4 November 2024

2024-11-04T17:22:40Z

← Older revision		Revision as of 17:22, 4 November 2024
Line 1:		Line 1:
	[[mysqldump]] is a [[command line interface]] (CLI) utility provided ~~as part of the~~ MySQL ~~package~~.		[[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.		Note that the following instructions apply to a very old version, and details may have changed since then.
	==Backing Up==		==Backing Up==
			===Local===
	'''Official MySQL documentation''': [http://dev.mysql.com/doc/refman/5.5/en/backup-and-recovery.html Chapter 6. Backup and Recovery]		'''Official MySQL documentation''': [http://dev.mysql.com/doc/refman/5.5/en/backup-and-recovery.html Chapter 6. Backup and Recovery]

Line 30:		Line 31:
	;SHOW CREATE DATABASE {{arg\|dbname}}; : Returns the SQL code which could be used to recreate the database, ''not'' including the contents.		;SHOW CREATE DATABASE {{arg\|dbname}}; : Returns the SQL code which could be used to recreate the database, ''not'' including the contents.
	;SHOW CREATE TABLE {{arg\|tablename}}; : Returns the SQL code which could be used to recreate{{arg\|tablename}}, ''not'' including the data.		;SHOW CREATE TABLE {{arg\|tablename}}; : Returns the SQL code which could be used to recreate{{arg\|tablename}}, ''not'' including the data.
			===Remote===
			To backup data from a remote server onto the local machine, via direct connection:
			mysqldump -h {{arg\|remote_host}} -u {{arg\|username}} -p {{arg\|database_name}} \| gzip > {{arg\|local_backup_file}}.sql.gz

			To backup data from a remote server onto the local machine via an ssh tunnel:
			ssh -L 3307:localhost:3306 {{arg\|remote username}}@{{arg\|remote hostname}}
			mysqldump -P 3307 -h 127.0.0.1 -u {{arg\|username}} -p {{arg\|database_name}} > {{arg\|local_backup_file}}.sql
	==Restoring==		==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.		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.

Woozle at 17:06, 4 November 2024

2024-11-04T17:06:27Z

← Older revision		Revision as of 17:06, 4 November 2024
Line 1:		Line 1:
	[[mysqldump]] is a [[command-line interface]] utility provided as part of the MySQL package.		[[mysqldump]] is a [[command line interface]] (CLI) utility provided as part of the MySQL package.

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

Woozle: Woozle moved page MySQL/porting/data/mysqldump to mysqldump without leaving a redirect: reorganizing

2024-11-04T16:22:04Z

Woozle moved page MySQL/porting/data/mysqldump to mysqldump without leaving a redirect: reorganizing

← Older revision	Revision as of 16:22, 4 November 2024
(No difference)

Woozle at 19:24, 11 August 2024

2024-08-11T19:24:36Z

← Older revision		Revision as of 19:24, 11 August 2024
Line 3:		Line 3:
	Note that the following instructions apply to a very old version, and details may have changed since then.		Note that the following instructions apply to a very old version, and details may have changed since then.
	==Backing Up==		==Backing Up==
	* Official MySQL documentation: [http://dev.mysql.com/doc/refman/5.5/en/backup-and-recovery.html Chapter 6. Backup and Recovery]		'''Official MySQL documentation''': [http://dev.mysql.com/doc/refman/5.5/en/backup-and-recovery.html Chapter 6. Backup and Recovery]

	For all of these examples:		For all of these examples:
	* If you leave off the {{arg\|password}}, it will prompt you for one.		* If you leave off the {{arg\|password}}, it will prompt you for one.
	* To connect to a MySQL server on localhost, omit -h{{arg\|hostname}}. '''not tested'''		* To connect to a MySQL server on localhost, omit -h{{arg\|hostname}}. '''not tested'''
			* see [[/remote]] for backing up from a remote server without storing the backup file remotely

	From a shell command line:		From a shell command line:

Woozle: Created page with "mysqldump is a command-line interface utility provided as part of the MySQL package. Note that the following instructions apply to a very old version, and details may..."

2024-08-11T18:53:22Z

Created page with "mysqldump is a command-line interface utility provided as part of the MySQL package. Note that the following instructions apply to a very old version, and details may..."

New page

[[mysqldump]] is a [[command-line interface]] utility provided as part of the MySQL package.

Note that the following instructions apply to a very old version, and details may have changed since then.
==Backing Up==
* Official MySQL documentation: [http://dev.mysql.com/doc/refman/5.5/en/backup-and-recovery.html Chapter 6. Backup and Recovery]

For all of these examples:
* If you leave off the {{arg|password}}, it will prompt you for one.
* To connect to a MySQL server on localhost, omit -h{{arg|hostname}}. '''not tested'''

From a shell command line:
;mysqldump --tab={{arg|path}} -h{{arg|hostname}} -u{{arg|username}} -p{{arg|password}} --opt {{arg|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 {{arg|path}} (which must already exist).
;mysqldump --tab={{arg|/output/path}} -h{{arg|hostname}} -u{{arg|username}} -p --opt {{arg|dbname}} {{arg|table_name_1}} {{arg|table_name_2}} {{arg|...}}
: This only dumps tables.
;mysqldump -h{{arg|hostname}} -u{{arg|username}} --opt {{arg|dbname}} > {{arg|filespec}}
: This dumps the entire database to a single file named {{arg|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 <code>--opt</code> 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 {{arg|dbname}}; : Returns the SQL code which could be used to recreate the database, ''not'' including the contents.
;SHOW CREATE TABLE {{arg|tablename}}; : Returns the SQL code which could be used to recreate{{arg|tablename}}, ''not'' including the data.
==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

==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.