Importing a large MySQL database and allowing remote connections safely

Browser-based tools like phpMyAdmin are limited by upload size and execution time. Anything much over 50 MB tends to time out part-way and leave a half-imported database, which is worse than a clean failure. On a server you control, the command line has no such limits. This guide covers importing a large dump into MySQL or MariaDB on a Cloud Server, and then letting a specific outside machine connect to it without opening the database to everyone.

Part 1: import a large database

Get the dump onto the server

Copy it over SSH from your machine. rsync can resume if the connection drops, which matters for big files:

rsync -avP database.sql.gz [email protected]:/root/

Create an empty database and a user

The import does not create the database for you:

mysql -u root -p

CREATE DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;CREATE USER 'shop'@'localhost' IDENTIFIED BY 'a-long-random-password';GRANT ALL PRIVILEGES ON shop.* TO 'shop'@'localhost';FLUSH PRIVILEGES;

Run the import

mysql -u shop -p shop < /root/database.sql

A compressed dump does not need extracting first:

gunzip < /root/database.sql.gz | mysql -u shop -p shop

Nothing is printed while it runs. The prompt returning is the signal it has finished. To see progress on a big file, install pv:

apt install -y pvpv /root/database.sql.gz | gunzip | mysql -u shop -p shop

Avoid putting the password on the command line (-pPassword). It ends up in your shell history and is visible in the process list. Let MySQL prompt you instead.

Check it worked

mysql -u shop -p shop -e "SHOW TABLES;" | wc -l

Compare the table count with the source database. A short list means the import stopped early.

If the import fails part-way

• "MySQL server has gone away". A single statement exceeded the packet limit, usually a very large row. Raise max_allowed_packet under the mysqld section of your MySQL config (for example to 256M), restart MySQL and try again.
• "Access denied". The user has no privileges on that database. Recheck the GRANT.
• "Table already exists". The database was not empty. A partial import cannot be resumed: drop and recreate the database, then import again.

Part 2: allow remote connections safely

By default MySQL on Ubuntu only listens on 127.0.0.1. That is the right default: an openly reachable database is one of the most attacked services on the internet. If a separate app server, a reporting tool or your laptop needs access, open it to specific addresses only.

1. Find the client's public IP

On the machine that will connect:

curl -s https://ifconfig.me

If that connection has a dynamic IP, it will change, and you will need to update the rules below when it does.

2. Make MySQL listen on the network

Edit /etc/mysql/mysql.conf.d/mysqld.cnf (MariaDB: /etc/mysql/mariadb.conf.d/50-server.cnf):

[mysqld]bind-address = 0.0.0.0

systemctl restart mysql

3. Create a user that can only connect from that IP

CREATE USER 'report'@'198.51.100.7' IDENTIFIED BY 'another-long-random-password';GRANT SELECT ON shop.* TO 'report'@'198.51.100.7';FLUSH PRIVILEGES;

Grant only what that client needs. A reporting tool rarely needs more than SELECT. Never create a user with host '%': that lets every address on the internet attempt to log in.

4. Open port 3306 to that IP only

ufw allow from 198.51.100.7 to any port 3306 proto tcpufw status

The firewall rule and the user's host restriction protect you twice over: an attacker would have to come from the allowed IP and know the password.

5. Test from the client

mysql -h 203.0.113.10 -u report -p -e "SELECT 1;"

A 1 back means the network, the firewall, the credentials and the grant are all correct.

If the connection is still refused

• The client's IP changed. Check it again and update both the user and the firewall rule.
• MySQL is still bound to localhost. Run ss -tlnp | grep 3306 on the server; it should show 0.0.0.0:3306.
• The user has no privileges on that database. Check with SHOW GRANTS FOR 'report'@'198.51.100.7';.
• Port 3306 is blocked at the client's end. Many office networks block outbound MySQL. Test with nc -vz 203.0.113.10 3306.

An alternative: tunnel over SSH

If the client already has SSH access, you can skip opening port 3306 entirely:

ssh -N -L 3307:127.0.0.1:3306 [email protected]mysql -h 127.0.0.1 -P 3307 -u shop -p

The database stays bound to localhost, and the traffic is encrypted inside SSH.

Next steps

• Rather not run MySQL yourself? Managed Databases for MySQL, MariaDB and PostgreSQL handle patching and maintenance for you.
• Keep dump files off the server in Object Storage.
• Running a busy database on your own server? Turbo plans give it dedicated vCPU. Compare them on Pricing.