Importing and Exporting MySQL Databases

From OCS Support Wiki

There are three ways to import and export data in MySQL. The first way is graphically via phpMyAdmin. This method has its limitations, particularly in that uploading large files through the browser generally doesn't work well. The second method is to use an SSH tunnel for MySQL and use a 3rd party tool to perform the import. The third option, and the one we will cover in this guide, is to use the MySQL command line client via SSH (see Windows and Linux instructions on establishing an SSH connection) to import the data into your database.

Importing Data

Before you import data into a MySQL database, you will need to create the database and a MySQL username for it. You can find instructions for doing this for cPanel and Webmin. Once you have the MySQL database as well as the MySQL username and password information, you're ready to proceed.

You will first need to upload the SQL dump file of your database to your account via FTP. These files typically have a .sql extension, although this is not necessary. Inside they contain the SQL statements necessary for recreating the tables and reinserting the rows of data that was in your database. We strongly recommend you upload this file to your home directory (the initial directory you see when you login via FTP) and not a directory like public_html. If you put it in public_html, it could be visible to the public.

Once the file is uploaded, establish an SSH connection to your account and run:

mysql -u mysqluser -p mysqldb < dbfile.sql

In this example, replace mysqluser with your MySQL username. Replace mysqldb with the name of the MySQL database you created. Replace dbfile.sql with the name of your MySQL dump file. You will be prompted for the password to the MySQL user you are trying to connect with. Once you have successfully entered it, the data will be imported. If you get an error on the password, check that you have created a MySQL user with access to the database you're trying to use. If you get an error while the data is being imported, its possible there is either data already in the database or there is an error in your dump file.

Exporting Data

Exporting your data is in some ways easier than importing it. To do so, establish an SSH connection to your account and run:

mysqldump -u mysqluser -p mysqldb > dbfile.sql

In this example, replace mysqluser with the MySQL username, mysqldb with the name of the database to back up, and dbfile.sql with the name of the file you want to create.

You'll be prompted for your password to the MySQL user you specified. Once you have successfully entered that, a file will be created with your data in it. You can then use FTP to download this file. If you execute the command above as soon as you get connected via SSH, it will be dumped into your home dir. The public can't see this file (since its not in public_html), but we recommend you delete it after you've downloaded it if you're not going to be using it on the server again.