Importing large MySQL databases can be a challenge, especially when the size of the database exceeds the upload limits set by PHPMyAdmin. When this happens, the most efficient way to import the database is through the command line interface (CLI) using SSH or a terminal. This method avoids the limitations of PHPMyAdmin and offers a more reliable approach to managing large databases.

In this tutorial, we’ll walk you through how to import a large MySQL database on servers running CWP (Control Web Panel), VestaCP, or cPanel. We’ll also explain how to handle database backups, especially when they are archived in formats like .zip or .gzip.

Why Use the Command Line?

While it might seem convenient to use a graphical interface like PHPMyAdmin, there are several reasons why importing a database via the command line is preferable:

• No Upload Limits: PHPMyAdmin has upload size limits that can prevent you from importing large databases. Using the command line bypasses these limits.
• Less Risk of Corrupted Tables: Importing through the command line minimizes the chances of data corruption, ensuring a smoother process.
• Faster Process: The command line method is typically faster, especially for very large databases.

For these reasons, importing databases via the MySQL command line is the preferred method.

Steps to Import a MySQL Database via Command Line

Let’s break down the process of importing a database using the command line.

1. Basic Command to Import a Database

Assume your database name is blawp_db, and your backup file is named wordpress_cms.sql, which is uploaded to the /home directory. You can import the database with the following command:

mysql -u username -p blawp_db < /home/wordpress_cms.sql

Here, replace username with your MySQL username and blawp_db with the name of your database.

2. Importing an Archived Database

If your database backup is archived (e.g., .zip or .gzip), you will need to extract it before importing. Below are the commands to extract and import databases from common archive formats.

A. For .zip Archives

If your database backup is a .zip file (e.g., database.sql.zip), use the following command to extract it:

unzip /home/database.sql.zip -d /home/

Once the file is extracted, you can import it using the basic MySQL import command mentioned earlier.

B. For .gzip Archives

If your database backup is a .gzip file (e.g., database.sql.gz), you will need to extract it first using the gunzip command. Use the following command:

gunzip /home/database.sql.gz

After the extraction, you can import the .sql file as usual.

3. Installing gunzip if Needed

In some cases, the gunzip command might not be available on your server. If that happens, you can install it based on your server’s operating system.

For RHEL/CentOS:

yum install gzip

For Debian/Ubuntu:

apt-get install gzip

Once installed, you can proceed with extracting .gzip archives and importing the database.

Conclusion

Importing large MySQL databases via the command line is an effective and reliable method for server administrators. Whether you’re using CWP, VestaCP, or cPanel, the steps above will guide you through the process of importing databases, even when they are large or archived in formats like .zip or .gzip. By using the command line, you can avoid the limitations of PHPMyAdmin, reduce the risk of errors, and speed up the import process.