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.
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
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.
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.
First, make sure that the hosting provider supports SSH Access. You can check it from your cPanel Account here:
If it does, then you must export the database in .sql format. It can not be compressed in a .zip or .tar.gz file.
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.
If the database does not exist, please create a database. To do this just access the necessary section from your Cpanel Dashboard:
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.
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.
mysql -p -u user_name database_name < file.sql
(replace ‘user_name’, ‘database_name’, and ‘file.sql’ with the actual name.)
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.
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:
That’s it. In case you have any questions, please feel free to ask here in comments.
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.