How to import and export MySQL database into an SQL file
Or Gzip for the matter. Here’s the command to run in your UNIX-based server to import or export via an SQL file, this is useful for performing backup and restoring of a MySQL database. (I wrote a similar import and export guide for PostgreSQL.) The mysqldump utility performs just that:
Exporting using mysqldump:
For example, my username is ‘kahwee’, my password being ‘secret’ and database being ‘justrealized_db’, I would run the following to export my database to a SQL file:
And to Gzip:
Importing using mysql:
To import back, we can use the mysql utility in a similar fashion, note that the > (greater than) has change to a < (lesser than).
For example, my username is ‘kahwee’, my password being ‘secret’ and database being ‘justrealized_db’, I would run the following to import my database:
And to Ungzip:
Backing up and restoring MySQL databases in Windows
Unfortunately, you can’t use gzip here. So all those commands above with gzip can’t work. The rest, however, still works. However, mysqldump and mysql may not be set in your system environment variables. These are instructions on how to add them for Windows Vista:
Click on ‘Edit the system environment variables’, a dialog box will pop up. Click on ‘Environment Variables…’, you should be greeted with the following dialog box:
My path looks like this before I add anything:
Append your MySQL bin directory at the back of what is already there. I use XAMPP (XAMPP lite to be specific) which has its MySQL bin folder located here ‘;C:\xampplite\mysql\bin\’, so I would be appending this:
That’s all I guess, hope it is helpful for you.
Possibly related:
[...] If you’re interested how to do the same in MySQL, I wrote a guide on how to do backup and restore for MySQL. [...]
May 15
on windows you can use open source sqlyog community version – it can backup (and restore) database as sql dump. i use it a lot, and so far i had no problems with it.
May 15
thejw23: Thanks for the tip. I am looking for more of a command line solution though. Trying to automate for of my syncing.
May 16
Hi there
I am using Xammp and want to know how I can export or import the database onto my the web host server?
Thanks in advance
CHEERS
Jul 10
Hi Danny, for XAMPP, you need to add “;C:xamppmysqlbin” to your list of environment variables. This is assuming that your XAMPP is located in the C drive.
You then need to upload to your server and import using the ‘mysql’ command in your server as described above.
Jul 10
I think this technique will be more usefull if we create a php script that exports database automatically for everyday. Because we can execute shell command by php right ?
Sep 15
zülküf küçüközer: You can execute shell command by PHP indeed but if you gonna do it every day, a cron job would probably be a better idea (:
Sep 15
fuckin awesome! i never thought this was possible on windows. all that pain trying to import huge db dumps through phpmyadmin is over!!! Thanks!!!
Mar 20