It is commonplace in the website industry to deal with databases that are much too large to import over a web based front end such as phpMyAdmin. So what do you do when you need to import a MySQL dump, but it is just too large for use with the import utility provided by phpMyAdmin?

To do this in Linux, you will need access to the command line on the server where your MySQL service is running; this may require you to use a Telnet/SSH utility such as PuTTY. Once you are connected to your server and have shell access you will need to move your MySQL dump file to the server if it is not there already. This can be done a few different ways, the most common of which are FTPing the file to your server (given there is ftp access on your server), or by doing a "get" (or wget) if the file is available via HTTP protocol (i.e. if the file is located on a website server where apache can access it.)

Once the file is on the local drive you are ready to import your MySQL dump into the database. But, before I show you how to do this I want to tell you what a MySQL dump file really is. A MySQL dump differs from many other data files due to the fact that it does not just contain data, as you would expect a .csv file to. Meaning that data in a MySQL dump is not delimited by any special character; instead your dump file is comprised of individual (or bulk) MySQL statements.

A typical MySQL insert statement looks like:

INSERT INTO [table_name] ([field1,field2,field3) VALUES ([data1, data2, data3]);

So essentially, if the data you are attempting to import was dumped with the individual statements switch, your dump file has an individual insert command for each record that was exported. So knowing that our file contains MySQL commands instead of just data we can see that what we really want to do is tell MySQL to run the commands within a file instead of extracting data, as would be the case if we where dealing with a .csv file.

Before we dive into the command that will tell MySQL to parse our file, we need to gather some necessary information. First, we need to know MySQL's root user login information; by default this is set to nothing, but for security purposes it is recommended that it be changed to something a little more... lengthy. Once we have this information we can open a MySQL session so we can either create a new database to house the table that will be created when MySQL runs our dump, or to retrieve the exact name of an existing database for use with our dump. To open a MySQL se ssion, run the following command:

mysql -u root

The above command tells MySQL that you wish to open a session using the root user. If your root user requires a password to log in with, then you will need to explicitly tell MySQL to prompt you for the password; which is done by running the following command:

mysql -u root -p

Once you have gained access to a MySQL session, which will be denoted by a welcome message, you are ready to deal with the database. For the purpose of staying on topic, I am going to assume you already have your database created; so to retrieve a list of databases registered with MySQL you can run the following command:

show databases;

This command will provide you with a fancy text based table which contains the names of all databases on the server. So, let’s say that the database we are using is called "it_drive_rules". Now that we have our database name and root login information we are finally ready to get this import over with.

First, you will need to close your current MySQL session, this can be done by typing "quit" into the prompt and pressing enter. Once the session has closed and you are at your shell prompt again we can begin building the import statement. For the following command I am going to assume that your MySQL dump file is located at /home/robert/dbdump.sql; however you will need to substitute my file path (and database name) with that of yours. With that squared away, run the following command:

mysql --force -p it_drive_rules < /home/robert/dbdump.sql

The --force switch here tells MySQL to skip errors and continue to parse the rest of the document, while the -p, just as before tells MySQL to prompt you for the root users password. You will need to replace "it_drive_rules" with your database name and "/home/robert/dbdump.sql" with the location of your dump file.

Essentially, this is three commands in one (ah, the power of piping); whereby the "mysql --force -p" is initiating a MySQL session but with the presence of a database name, in this case "it_drive_rules" is just the same as if we where to type the "use" command. So basically this is saying log into MySQL with the password I give you when you prompt me and immediately set my focus to the it_drive_rules table. Now, the less than sign "<" is actually a Linux rule, not a MySQL parameter.

When a less that sign is used in unison with a command it is called piping, in which the commands that are to the right of the < symbol are pushed into the commands on the left of the < symbol. So, basically what this does is log into MySQL, select a database, and send the contents of our file to MySQL, which because a MySQL dump is just a bunch of commands, MySQL runs each command that is piped to it.