February 1, 2021

How to import a large database using 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.

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.

 

Then follow the steps below:

  1. Upload the SQL file that you exported to the root folder(public_html for ex.) via the cPanel file manager or an FTP client.
  2. If the database does not exist, please create a database, a user with a strong password, and give the user full privileges to the database. Save the username and password in the text file.
  3. Log into the server through the Terminal from cPanel.
  4. 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.)

  5. 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.

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

Also, if you need help in support your WordPress website 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.

About the author

Ivan is a CodaStudio lead wordpress developer. He also manages a web design company at Elivan Studio. He likes to sneak in a few games of World of Tanks, and is a huge Harry Potter movie fan.

Subscribe to our newsletter


Privacy Preference Center

Necessary

These cookies are required to provide necessary site functionalities and features.

wordpress_[hash], wordpress_logged_in_[hash], wp-settings-{time}-[UID], wordpress_test_cookie, wp_woocommerce_session_, wpe-auth, woocommerce_cart_hash, woocommerce_items_in_cart, gdpr[consent_types], __distillery, gdpr[allowed_cookies], time, muxData, fs_uid, ajs_user_id, _gid, __cfduid, intercom-id-x51ganuz, ajs_group_id, ajs_anonymous_id, _ga, __jid, cp-impression-added-forcp_id_, disqus_unique, nelioab_was_in, nelioab_env, nelioab_userid, mp_a36067b00a263cce0299cfd960e26ecf_mixpanel, _ga, intercom-lou-x51ganuz, __stripe_mid

Third Party

Learn more about Types of Cookies used by Google: https://policies.google.com/technologies/types

Learn more about Google Analytics Cookies: https://developers.google.com/analytics/devguides/collection/analyticsjs/cookie-usage

Learn more about Facebook Cookies: https://www.facebook.com/policies/cookies/

Learn more about Intercom Cookies: https://www.intercom.com/terms-and-policies#cookie-policy

NID,1P_JAR,DV, OTZ, SID, APISID, SSID, HSID, SAPISID, OGPC, AID, SIDCC, HSID, IDE, _ym_uid, __sonar, DSID, FR, dpr, datr, c_user, xs, wd, act, sb, presence, disqus_unique, hubspotutk, G_ENABLED_IDPS, __hstc, btIdentify, _bti, __utma, __utmz, __hssrc, sessionid, _ga, _gid, intercom-id-x2byp8hg, __simplex, _sp_id., __hstc, optimizelyEndUserId, _ga, _gid, hubspotutk, __ar_v4, __hssrc, wistia-http2-push-disabled, intercom-id-, intercom-session-, gtm_id, messenger_install_identifier, _ga, product_store_identifier, intercom-lou-, _hp2_id., _drip_client_, fs_uid, amplitude_idsegment.com, ajs_user_id, mkjs_group_id, mkjs_user_id, _ga, _ga, seg_xid_ts, seg_xid_fd, __ar_v4, seg_xid, ajs_anonymous_id, AID, _ga, optimizelyEndUserId, fs_uid
NID,1P_JAR,DV, OTZ, SID, APISID, SSID, HSID, SAPISID, OGPC, AID, SIDCC, HSID
IDE, _ym_uid, __sonar, DSID
FR, dpr, datr, c_user, xs, wd, act, sb, presence
disqus_unique, hubspotutk, G_ENABLED_IDPS, __hstc, btIdentify, _bti, __utma, __utmz, __hssrc, sessionid, _ga, _gid, intercom-id-x2byp8hg
__simplex, _sp_id., __hstc, optimizelyEndUserId, _ga, _gid, hubspotutk, __ar_v4, __hssrc, wistia-http2-push-disabled
intercom-id-, intercom-session-, gtm_id, messenger_install_identifier, _ga, product_store_identifier, intercom-lou-, _hp2_id.
_drip_client_, fs_uid, amplitude_idsegment.com, ajs_user_id, mkjs_group_id, mkjs_user_id, _ga, _ga, seg_xid_ts, seg_xid_fd, __ar_v4, seg_xid, ajs_anonymous_id
AID
_ga, optimizelyEndUserId, fs_uid