How to Import Large MySQL Database Files

Last Updated March 28, 2022 · Web Development

MySQL is one of the most popular database systems in use today. It’s found in many popular open-source projects, like WordPress, and is frequently used in private software applications as well. Developers around the world work with and write programs that feature MySQL as a key part of their infrastructure. Unfortunately, MySQL can quickly grow massive in size and become difficult to manage. One of the most common difficulties developers run into is very large backup files. In fact, trying to import large MySQL database files can be an issue regardless of the development environment.

Thankfully, there is an easy way to handle this problem. We’re going to discuss the best way to import large MySQL database files. First, before we tackle the how, we will briefly discuss the why. It’s important to know why some MySQL database files quickly balloon in size.

Why Do MySQL Database Files Get So Large?

There are many reasons why your MySQL database files can grow to an unwieldy size.

Sometimes the answer is simple: your database contains quite a lot of data. In these instances, there’s not much that can be done to avoid growing file sizes. Our recommendation, in this scenario, is to consider breaking your backups into chunks. Logically dividing one large backup into multiple, smaller backups is often an easy way to manage large SQL dump files.

On the other hand, even small MySQL databases can generate large backup files. One common reason, often seen in older versions of MySQL, is not using extended inserts. In short, an extended insert combines multiple-row inserts into a single command. This helps trim down file size. With extended insert disabled, each row of each table will be its own insert command in your file. It doesn’t take much for even the smallest databases to grow into huge backup files when each row gets its own line in the file.

Extended inserts are enabled by default with modern versions of MySQL. If you are using a newer version of MySQL and still not getting backups with extended inserts check the mysqldump command you are running. If the --skip-extended-insert option is being used you’ll want to remove it.

What do you do if you are using extended inserts and your MySQL backup files are still very large? In that case, it’s highly unlikely your preferred MySQL desktop application or web-based application (like phpMyAdmin) will work. Instead, it’s time to use the command line.

Import Large MySQL Database Files with the Command Line

The best way to import large MySQL database files is via the command line. Specifically, you’ll want to use one of two methods. First, you can import your MySQL file straight into the database with a single command or you can use the source command for more useful output.

Traditionally, you import a file into a MySQL database with the following command:

mysql -h hostname -u username -p databasename < filename

Simply replace the hostname, username, databasename, and filename text with the appropriate text particular to your setup. If done correctly, MySQL will prompt you for the password of the username you entered and the import will begin. When the import is complete the prompt will appear to let you know things have finished. A downside of this approach, other than its limited output, is that occasionally it can choke on very large files. This method is normally reliable but it can fail often enough that another way might be better for you.

Alternatively, if you would like more output during the import and more assured results, you can use the source command. Simply log into MySQL on the command line using your username and password. Once in, you’ll see the standard MySQL prompt:

mysql>

Next, tell MySQL what database you want to use:

mysql> user databasename;

Finally, use source to tell MySQL what file to import:

mysql> source filename;

As in the previous example, replace databasename and filename with whatever is the appropriate text for your situation. In the end, once source begins to run the output to the screen will tell you as each query in your import file is successfully completed. This way you can get some form of real-time feedback that your import is, in fact, properly running.

Don’t Let Large Import Files Ruing Your MySQL Backup Strategy

Large backup files can be quite a hindrance to anyone’s database backup plan. Of course, nothing is so problematic that you should avoid making backups entirely. Simply using the command line techniques we’ve laid out can help you overcome any difficulties. You should be able to successfully import your data no matter the size of your MySQL exports.