How to export mysql database to another computer?

Question

I created a database using Mysql Workbench. Now I want to export this database to my home PC.

How can I do this if the 2 PCs have no network connection?

score 1 · Accepted Answer · answered Sep 08 '13 at 11:36

1

I use mysqldump to export the database. You can use something like

mysqldump -u [username] -p [database name] > backup.sql

to store it in a file. After that you can import into another database via

mysql -u [username] -p [database name] < backup.sql

answered Sep 08 '13 at 11:36

Marc Hauptmann

There's also a GUI option for this in MySQL workbench, just connect to the Server Administration GUI, then go to Import or Export in the sidebar – Matt Sep 08 '13 at 11:56
I used Import in sidebar, but how do I save the changes to model? – alwbtc Sep 08 '13 at 12:30

score 1 · Answer 2 · answered Jan 31 '14 at 07:36

As edit was rejected posting it as an answer; hope it will be helpful. Followed to the queries give by "Marc Hauptmann" -

Few quick-tips for generic issues that can be faced while performing DB dump and restore:-

As correctly mentioned above by "Marc" it is always advised not to provide db password in command line export [if you do so, it can be easily sniffed in history or reverse-search]
If you are transferring large dump file it is advised to compress it before transferring. [it should be uncompressed before restore]
While exporting if you want to export data with 'new database name' it can also be done. [It will require new Db to be created before using it in import]
Also if we are exporting data from production servers to make sure it doesn't impact performance, export from other servers with below additional option "-h [hostname]"

mysqldump -h [hostname] -u [username] -p [database name] > backup.sql

score 0 · Answer 3 · answered Jan 25 '14 at 07:48

Using gzip is pretty painless and really shrinks these files.

mysqldump -u [uname] -p[pass] [dbname] | gzip -9 > [backupfile.sql.gz]

gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]

But man, it is 2014 - this stuff is also easy to do via a secure shell connection.

3 Answers3