: Most efficient method uploading a large SQL db (200mb) to server Is there an easier way to upload an sql database to the server so it can be accessed via phpmyadmin? My dev seems to do it

@Cooney921

If you have SSH access to the server, gzip the file to compress it, if it is not already compressed:

gzip thefile.sql


then copy it up to the server:

scp thefile.sql.gz yoursshuser@example.com:~/


then SSH into the server and unzip it:

gunzip thefile.sql.gz


then import it using the MySQL command line:

mysql -u yourdbuser -p yourdatabase < thefile.sql


This is the most efficient method.

I assume you're currently trying to import it using phpMyAdmin's import feature (rather than upload the file to the server first). This usually times out for large files in my experience. If you have SSH access but your internet connection really does timeout for large uploads, use rsync to upload instead, which can resume file uploads.

Vote Up Vote Down

@Ann8826881

When using MySql, I always recommend using www.mysql.com/products/workbench/. However, you do need to have some ports opened on your firewall, assuming you have one, and make sure your MySql is properly secured which can be found here: dev.mysql.com/doc/refman/5.7/en/security.html. If you do not have a firewall, then the process becomes that much easier.

Keep in mind that phpmyadmin is used by web hosts to keep MySql ports closed. It is thought to be safer using phpmyadmin, however, history has proven otherwise. Phpmyadmin over the years has have more vulnerabilities than connecting to MySql directly.

This is something you want to research.

You do not have to allow connections from the Internet full time and only turn on access on an as needed basis. As well, you can limit by domain name, IP address, and user to limit who can access the database server. This can be changed on the fly making the practice very safe.

MySql Workbench is the tool for managing, developing, and deploying data with MySql. It is really simple to use and should be a go to tool for anyone who deals with MySql where greater access is needed.

Vote Up Vote Down

@Sent6035632

You could break the huge file into smaller chunks. Just make sure you don't set the break in the middle of an SQL statement or you'll get errors.

Vote Up Vote Down