When working with large databases, it can be challenging to import them efficiently. One effective way to import a large database is through SSH access. In this short tutorial we will show, how to import large database using SSH Command Line. Most of the people who use the shared hosting, have ever tried to import large databases from PhpMyAdmin, that weight more than 100Mb, and most of them met various difficulties during this process. On this way may appear some errors, like:

You probably tried to upload too large file. Please refer to documentation for ways to workaround this limit.

or

No data to import. Either no filename was sent or the filesize exceeded the maximum allowed size. See FAQ 1.16.

Unfortunately, all shared servers have a limit of 50MB or so for database upload from PhpMyAdmin, which in most cases is integrated in the cPanel. It is a global setting on the server that cannot be changed and not all the time this issue can be fixed  just by editing php.ini file and increasing upload_max_filesize and max_execution_time

Sure, it is possible to contact hosting support team and ask them for help, but if you can’t wait or the hosting doesn’t answer, and you want to import the database quickly, without any errors, then yo can follow up our guide.

Using SSH can be a great way to manage the database and files on the website. By using some simple commands you can import a large MySQL database into a recently created database on your hosting.

What is SSH?

SSH (Secure Shell) is a network protocol used for secure remote access and control of computer systems. It provides a secure way to connect to a remote computer over an unsecured network such as the Internet.

SSH works by encrypting data sent between the client and the server, making it difficult for anyone to intercept or read the data. It also provides authentication mechanisms to ensure that only authorized users can access the system.

SSH is commonly used by system administrators, developers, and network engineers to remotely manage and configure servers and other network devices. It can also be used for secure file transfer and tunneling of other network protocols such as HTTP and FTP.

Prepare the database before importing using SSH

First, make sure that the hosting provider supports SSH Access. You can check it from your cPanel Account here:

 

Step 1: Export the database

If it does, then you must export the database in .sql format. It can not be compressed in a .zip or .tar.gz file.

 

Step 2: Upload the database to the server

Once you have downloaded the database, upload the SQL file that you exported to the root folder(public_html for ex.) via the cPanel File Manager or an FTP client, this will save you the trouble of finding the correct path to the file.

Step 3: Create new database and assign user

If the database does not exist, please create a database. To do this just access the necessary section from your Cpanel Dashboard:

Create New Database from Cpanel Account

Find the “Databases” section on your Cpanel Account and click on “MySQL Databases”

Create New Database from MySQL Databases Section

Create New Database using “New Database:” input field

Add the new User for Database

Add the new User for Database

Make sure to generate a strong password, and give the user full privileges to the database.

Note: Don’t forget to save the username and password in the text file before proceeding to the next steps, you will need it for the final step.

Step 4: Assign user and delegate privileges to the newly created database

So after you have created a new user you need to assign the newly created database to that user:

Then add “All Privileges” for the new user to that database:

Note: Of course you can set an existing user, just make sure you remember the old password. Otherwise, you will need to reset it 🙂

Now everything is ready for importing the database using SSH.

Import the large database from Terminal using Cpanel or Command line using SSH

Importing the Database using Cpanel Terminal:

  1. Log into the server through the Terminal from cPanel.
  2. Type in this command: 
    mysql -p -u user_name database_name < file.sql

    (replace ‘user_name’, ‘database_name’, and ‘file.sql’ with the actual name.)

  3. The system will ask for the database user password, and then the database will be imported. Note: the password is invisible!

Note: We recommend using the database user with the database user’s password and not the cPanel main user and password. The -p flag will cause the MySQL to prompt for your account’s password. Do not put passwords in the command line in the plain text, and let the system to prompt for a password. This is for your own security.

Importing the Database using Command line from your OS – for advanced users:

If your hosting provider provides you with SSH access then you can import the database using the command line from your OS, for this you need to:

    1. Open the terminal(Windows, iOS, Linux).
    2. Connect to your hosting using your server login and shared IP.
      Example: ssh [email protected]. BTW You can find your server login and shared IP on your Cpanel Dashboard for example:
    3. Follow the step 2 and 3 from Importing the Database using Cpanel Terminal Guide above.

That’s it. In case you have any questions, please feel free to ask here in comments.

Conclusion

Importing a large database can be a daunting task, but using SSH access can make the process more manageable. By compressing the database, uploading it to the server, extracting it, and importing it, you can efficiently import a large database. Just remember to monitor the progress to ensure that everything is running smoothly.

Also, if you need help with importing your WordPress Database using SSH from professionals, you can choose one of our WordPress Care Plans. Our CodaStudio WordPress support team will take care of everything on your website, from small design changes to custom functionality.

Subscribe to our newsletter