MySQL: Exporting and Importing a database

Working on a presentation I am giving, I needed to export my existing Sguil Database to my laptop. I have years of data in this database, so it provides a good basis for demonstrating some basic reporting principles in BIRT. So I needed to export the data to my local system.

This is actually a very easy thing to accomplish. On the source system, I run the following command:

mysqldump -u -p sguildb > sguildb.sql

Where -u is a switch to accept the database username to use, -p will prompt for a password, and the last entry is the database to export, in this case the sguildb database. Now, I have a large script that will rebuild the Sguil database on my development system in my user directory.

I log into the target system, and copy the script using the following command:

scp @:sguildb.sql

This will use the secure copy command to copy the sguildb.sql file from my source system to the local target system.

In order to import, I run the following command:

cat sguildb.sql | mysql -p -D sguildb

Where -p will prompt for a password, and -D is the database switch. I do not use the username switch since I am using my local user account name to login to the database.