MySQL/migration - Revision history

Woozle at 01:56, 25 February 2026

2026-02-25T01:56:03Z

← Older revision		Revision as of 01:56, 25 February 2026
Line 4:		Line 4:
	There is a choice of tools for doing this.		There is a choice of tools for doing this.
	* [[Beekeeper Studio]], but only the paid versions		* [[Beekeeper Studio]], but only the paid versions
	* [[~~mysqldump~~]] (command-line)		* [[/CLI/]]: command-line interface
	* [[MySQL Workbench]] (GUI utility)		* [[MySQL Workbench]] (GUI utility)
	* [[PHPMyAdmin]] (web-based)		* [[PHPMyAdmin]] (web-based)
	* '''obsolete''':
	** [[MySQL Admin]] (GUI utility) - this has been replaced by [[MySQL Workbench]]
	==Related Resources==		==Related Resources==
	* [https://sourceforge.net/projects/automysqlbackup/ AutoMySQLBackup] ([https://web.archive.org/web/20081218005549/http://members.lycos.co.uk/wipe_out/automysqlbackup/ origin story])		* [https://sourceforge.net/projects/automysqlbackup/ AutoMySQLBackup] ([https://web.archive.org/web/20081218005549/http://members.lycos.co.uk/wipe_out/automysqlbackup/ origin story])

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

2024-11-04T16:33:54Z

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

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

Woozle at 16:32, 4 November 2024

2024-11-04T16:32:58Z

← Older revision		Revision as of 16:32, 4 November 2024
Line 1:		Line 1:
	==About==		==About==
	This subpage is about ~~porting~~ data ~~into and out of~~ [[MySQL]] databases.		This subpage is about migrating data between [[MySQL]] databases, i.e. backing up data to independent files, and restoring the data from those files back into a database

	There is a choice of tools for doing this.		There is a choice of tools for doing this.
	* [[~~/phpmyadmin~~]] ~~(web-based)~~		* [[Beekeeper Studio]], but only the paid versions
	* [[/mysqldump]] (command-line)		* [[mysqldump]] (command-line)
	* [[~~/mysql~~-~~admin~~]] (GUI utility) - ~~obsolete;~~ this has been replaced by MySQL Workbench		* [[MySQL Workbench]] (GUI utility)
			* [[PHPMyAdmin]] (web-based)
			* '''obsolete''':
			** [[MySQL Admin]] (GUI utility) - this has been replaced by [[MySQL Workbench]]
	==Related Resources==		==Related Resources==
	* [https://sourceforge.net/projects/automysqlbackup/ AutoMySQLBackup] ([https://web.archive.org/web/20081218005549/http://members.lycos.co.uk/wipe_out/automysqlbackup/ origin story])		* [https://sourceforge.net/projects/automysqlbackup/ AutoMySQLBackup] ([https://web.archive.org/web/20081218005549/http://members.lycos.co.uk/wipe_out/automysqlbackup/ origin story])

Woozle: Replaced content with "==About== This subpage is about porting data into and out of MySQL databases. There is a choice of tools for doing this. * /phpmyadmin (web-based) * [[/mysqldump]..."

2024-08-11T18:58:53Z

Replaced content with "==About== This subpage is about porting data into and out of MySQL databases. There is a choice of tools for doing this. * /phpmyadmin (web-based) * [[/mysqldump]..."

@@ Line 2: / Line 2: @@
 This subpage is about porting data into and out of [[MySQL]] databases.
-There are several techniques for doing this.
+There is a choice of tools for doing this.
-==Using phpmyadmin (web-based)==
+* [[/phpmyadmin]] (web-based)
-===Backing up===
+* [[/mysqldump]] (command-line)
-#Make sure you've got [http://www.phpmyadmin.net PHPMyAdmin] running and that's all happy. I won't cover this part here as it varies with your set up.
+* [[/mysql-admin]] (GUI utility) - obsolete; this has been replaced by MySQL Workbench
 ==Related Resources==
-*[http://members.lycos.co.uk/wipe_out/automysqlbackup/ AutoMySQLBackup]
+* [https://sourceforge.net/projects/automysqlbackup/ AutoMySQLBackup] ([https://web.archive.org/web/20081218005549/http://members.lycos.co.uk/wipe_out/automysqlbackup/ origin story])

Woozle: /* Using phpmyadmin (web-based) */ original version written by Aero over a decade ago, but he wanted to disappear from the site and there have been lots of edits since then anyway...

2023-09-25T15:40:00Z

Using phpmyadmin (web-based): original version written by Aero over a decade ago, but he wanted to disappear from the site and there have been lots of edits since then anyway...

← Older revision		Revision as of 15:40, 25 September 2023
Line 4:		Line 4:
	There are several techniques for doing this.		There are several techniques for doing this.
	==Using phpmyadmin (web-based)==		==Using phpmyadmin (web-based)==
	~~<font size=-2>(by [[User:Aero\|Aero]])</font>~~
	===Backing up===		===Backing up===
	#Make sure you've got [http://www.phpmyadmin.net PHPMyAdmin] running and that's all happy. I won't cover this part here as it varies with your set up.		#Make sure you've got [http://www.phpmyadmin.net PHPMyAdmin] running and that's all happy. I won't cover this part here as it varies with your set up.
Line 24:		Line 23:
	====Notes====		====Notes====
	Some installations of phpmyadmin may have an upper size-limit on imports – e.g. at l48.net it's 2 MB, which basically means I can't use it to restore any of my wikis.		Some installations of phpmyadmin may have an upper size-limit on imports – e.g. at l48.net it's 2 MB, which basically means I can't use it to restore any of my wikis.

	==Using mysql-admin (GUI utility)==		==Using mysql-admin (GUI utility)==
	===Backing up===		===Backing up===

Woozle: /* Backing Up */

2023-09-25T15:38:09Z

Backing Up

← Older revision		Revision as of 15:38, 25 September 2023
Line 40:		Line 40:
	===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:
			* 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:		From a shell command line:
	;mysqldump --tab={{arg\|~~/output/~~path}} -h{{arg\|hostname}} -u{{arg\|username}} -p{{arg\|password}} --opt {{arg\|dbname}} : ~~Saves~~ SQL code to recreate the entire database in an .sql ~~file~~ compatible with the restoring method given below.		;mysqldump --tab={{arg\|path}} -h{{arg\|hostname}} -u{{arg\|username}} -p{{arg\|password}} --opt {{arg\|dbname}}
	:* ~~If you leave off the <u>password</u> (as in the next example), it~~ will ~~prompt you for~~ one.		: This saves SQL code to recreate the entire database in .sql files compatible with the restoring method given below.
	~~:* To connect to a MySQL server on localhost~~, ~~omit -h~~{{arg\|~~hostname~~}}. ~~'''not tested'''~~		:* 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\|...}} : ~~Saves SQL code for particular~~ tables		;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.
	~~To back up all databases~~: ~~<bash>~~mysqldump --all-databases -hhost -uuser -ppassword > name_of_dump_file.sql~~</bash>~~		;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.

	~~To back up a single database~~:<~~bash~~>~~mysqldump -hhost~~ -~~uuser~~ -~~ppassword name_of_database > name_of_dump_file.sql~~</~~bash~~>		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.		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:		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 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 ~~<u>~~tablename~~</u>~~, ''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.

	===Restoring===		===Restoring===

Woozle: /* Related Resources */ error message

2017-07-04T10:39:01Z

Related Resources: error message

← Older revision		Revision as of 10:39, 4 July 2017
Line 68:		Line 68:
	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>

			==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.
	==Related Resources==		==Related Resources==
	*[http://members.lycos.co.uk/wipe_out/automysqlbackup/ AutoMySQLBackup]		*[http://members.lycos.co.uk/wipe_out/automysqlbackup/ AutoMySQLBackup]

Woozle: /* Using mysql (command-line) */ tidying

2017-04-06T13:10:54Z

Using mysql (command-line): tidying

← Older revision		Revision as of 13:10, 6 April 2017
Line 40:		Line 40:
	===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]

			From a shell command line:
			;mysqldump --tab={{arg\|/output/path}} -h{{arg\|hostname}} -u{{arg\|username}} -p{{arg\|password}} --opt {{arg\|dbname}} : Saves SQL code to recreate the entire database in an .sql file compatible with the restoring method given below.
			:* If you leave off the <u>password</u> (as in the next example), it will prompt you for one.
			:* To connect to a MySQL server on localhost, omit -h{{arg\|hostname}}. '''not tested'''
			;mysqldump --tab={{arg\|/output/path}} -h{{arg\|hostname}} -u{{arg\|username}} -p --opt {{arg\|dbname}} {{arg\|table_name_1}} {{arg\|table_name_2}} {{arg\|...}} : Saves SQL code for particular tables

	To back up all databases: <bash>mysqldump --all-databases -hhost -uuser -ppassword > name_of_dump_file.sql</bash>		To back up all databases: <bash>mysqldump --all-databases -hhost -uuser -ppassword > name_of_dump_file.sql</bash>
	~~'''The following commands seem to be obsolete, wrong, or out of context:'''~~

	~~From~~ a ~~shell command line~~:		To back up a single database:<bash>mysqldump -hhost -uuser -ppassword name_of_database > name_of_dump_file.sql</bash>
	~~;mysqldump --tab=~~<~~u>/output/path</u> -h<u>hostname</u~~> -~~u<u>username</u> -p<u>password</u>~~ --~~opt <u~~>~~dbname</u>:Saves SQL code to recreate the entire database in an~~ .sql ~~file compatible with the restoring method given below. If you leave off the <u>password~~</u> ~~(as in~~ the ~~next example), it will prompt you for~~ one~~. To connect~~ to ~~a MySQL server on localhost~~, ~~omit -h<u>hostname</u>. '''not tested'''~~
	~~;mysqldump --tab=<u>/output/path</u> -h<u>hostname</u> -u<u>username</u> -p --opt <u>dbname</u> <u>tablename</u> {{optargs\|<u>anothertablename</u> .~~.~~.}}:Saves SQL code for particular tables~~		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:		For more specific backups, the following mysql client commands are relevant:
	;SHOW CREATE DATABASE ~~<u>~~dbname~~</u>~~;: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 ~~<u>~~tablename~~</u>~~;:Returns the SQL code which could be used to recreate <u>tablename</u>, ''not'' including the data.		;SHOW CREATE TABLE {{arg\|`tablename`}}; : Returns the SQL code which could be used to recreate <u>tablename</u>, ''not'' including the data.

	===Restoring===		===Restoring===

Woozle: /* Backing Up */ correct command

2014-06-19T20:39:56Z

Backing Up: correct command

← Older revision		Revision as of 20:39, 19 June 2014
Line 40:		Line 40:
	===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]

			To back up all databases: <bash>mysqldump --all-databases -hhost -uuser -ppassword > name_of_dump_file.sql</bash>
			'''The following commands seem to be obsolete, wrong, or out of context:'''

	From a shell command line:		From a shell command line:
	;mysqldump --tab=<u>/output/path</u> -h<u>hostname</u> -u<u>username</u> -p<u>password</u> --opt <u>dbname</u>:Saves SQL code to recreate the entire database in an .sql file compatible with the restoring method given below. If you leave off the <u>password</u> (as in the next example), it will prompt you for one. To connect to a MySQL server on localhost, omit -h<u>hostname</u>. '''not tested'''		;mysqldump --tab=<u>/output/path</u> -h<u>hostname</u> -u<u>username</u> -p<u>password</u> --opt <u>dbname</u>:Saves SQL code to recreate the entire database in an .sql file compatible with the restoring method given below. If you leave off the <u>password</u> (as in the next example), it will prompt you for one. To connect to a MySQL server on localhost, omit -h<u>hostname</u>. '''not tested'''

Woozle: /* Using mysql (command-line) */ updated URL

2012-03-29T01:21:55Z

Using mysql (command-line): updated URL

← Older revision		Revision as of 01:21, 29 March 2012
Line 39:		Line 39:
	==Using mysql (command-line)==		==Using mysql (command-line)==
	===Backing Up===		===Backing Up===
	* Official MySQL documentation: [http://dev.mysql.com/doc/refman/5.0/en/backup.html ~~5.10.1~~. ~~Database Backups~~]		* Official MySQL documentation: [http://dev.mysql.com/doc/refman/5.5/en/backup-and-recovery.html Chapter 6. Backup and Recovery]
	From a shell command line:		From a shell command line:
	;mysqldump --tab=<u>/output/path</u> -h<u>hostname</u> -u<u>username</u> -p<u>password</u> --opt <u>dbname</u>:Saves SQL code to recreate the entire database in an .sql file compatible with the restoring method given below. If you leave off the <u>password</u> (as in the next example), it will prompt you for one. To connect to a MySQL server on localhost, omit -h<u>hostname</u>. '''not tested'''		;mysqldump --tab=<u>/output/path</u> -h<u>hostname</u> -u<u>username</u> -p<u>password</u> --opt <u>dbname</u>:Saves SQL code to recreate the entire database in an .sql file compatible with the restoring method given below. If you leave off the <u>password</u> (as in the next example), it will prompt you for one. To connect to a MySQL server on localhost, omit -h<u>hostname</u>. '''not tested'''