Webmasters Stack Exchange is a question and answer site for pro webmasters. It only takes a minute to sign up.
Sign up to join this community
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 so easily, however if i try my internet connection usually times out due to large uploads etc.
Possibly a way to do it through FTP, which can resume/pause uploads etc?
When using MySql, I always recommend using https://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: https://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.
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.
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.
