Today, I was given a database dump that needed to be uploaded to an active server. More specifically, a 750 MB sql dump (8 columns of data and 11.5 million rows). When databases reach this size, it isn’t practical to use phpmyadmin’s convenient GUI for importing. It simply takes far too long. For situations such as this, a command line approach is a much better way to import a database.

In this post I’m going to cover how this is achieved remotely. Take a look at this post if you’re looking for command line tips on a WAMP/LAMP/XAMPP local installation.

Import a Database with SSH

The first step is to establish your remote connection. This is typically achieved through SSH (that’s another day’s tutorial). After your connection is made, transfer your .sql file to your server and place it in a convenient folder. Make it convenient, because the next step is to navigate to said folder through the command line. This is achieved using conventional cd (change directory) commands, as shown. The last step is to simply run the following command.

mysql -p -u username database_name < file.sql

If you happen to have a password associated with the username that has access to the database (you should), you’ll be prompted to enter the password and it’ll process the command straight away. Do note, that unlike demonstrated on the local WAMP installation, the SSH line will give you no visual feedback that it is processing the file other than the fact it won’t pull up a new command line.

SSH Terminal showing the commands to import a sql file.

For reference, the ~750 MB file took my server about a minute and a half to import. Just for fun, I gave up on my phpmyadmin comparison after about 15 minutes.