Exporting a Large Remote MySQL database and importing it to Local

If you are using PhpMyAdmin for the database export for the remote database, and if your database is too huge then you may face the timeout issue.

I faced the similar issue when I was looking to replicate a remote database on my local. I was looking to export and import it via phpMyAdmin. Did not work.

Here is what worked.

#1. SSH into your remote server.

#2. Run the following command to export the database.

mysqldump -P 3310 -h localhost -u remote_user -p  remote_dbname > filename_to_export.sql

Replace the username and database name with your actual remote db parameters, Once you execute this command it will ask for the database password and will create the export database file in the same directory from where you execute this command.

#3. Once you have exported the database file, The next task is to get this file to your local machine. We will use FTP for this.

Login to your cPanel of remote server and choose option File Manager

File Manager option cPanel

Go to FTP File Manager and Navigate to directory where you have exported the database file. If you have a huge file, its a good idea to compress the file before downloading.

Right Click on the sql file which you are looking to bring to your local and choose Compress to zip format.

#4. Once Compressed, Right click on the File in File Manager and Choose Download. The file will be downloaded to your local machine.

#5. To import the sql file in your local database, Uncompress the sql file, Navigate to your Terminal / Command Prompt and run the following command

mysql -u root -p local_db_name < /Path/to/sqlfile/file_exported.sql

That’s it ! Now you have your huge remote database replicated to local.

 

Related Articles

If you like our content, please consider buying me a coffee.

Thank you for your support!

Buy me a coffeeBuy me a coffee

Leave a reply:

Your email address will not be published.

Site Footer