How to Import Large MySQL/MariaDB Databases to Your TierHive SQL Offload Service

If you're trying to upload a large database backup through the web interface and hitting upload size limits, here's how to import it using the command line instead.

What You Need

  • Your TierHive Hourly VPS, any plan that is big enough for your database file will do, it can be a fresh hourly VPS just for this purpose if you want. - SQL Offload Service credentials (Sql server IP Address, username, password, database name)
  • Your database dump file (.sql or .sql.gz)

Upload and Import

First, upload your database dump to your NAT VPS using SFTP. You can use any SFTP client (FileZilla, WinSCP, Cyberduck) with the SSH credentials from your TierHive control panel.

Once uploaded, SSH into your NAT VPS and run: 

mysql -h your-sql-server-ip -u your-username -p your-database --skip-ssl < /path/to/backup.sql

For compressed dumps: 

gunzip < backup.sql.gz | mysql -h your-sql-server-ip -u your-username -p your-database --skip-ssl

The import will run in your terminal. For large databases, this can take several minutes. You'll see your command prompt return when it's complete.

Common Issues

If you see max allowed packet errors during import, the dump contains queries larger than the server accepts,

You may need to drop the partial import and start over. To avoid this with very large imports, run the command inside a screen session. (usually large BLOB/TEXT fields). Re-export your database with a smaller packet size: 

mysqldump --max_allowed_packet=16M -u user -p database > backup.sql

Clean Up

After the import completes successfully, delete the dump file from your VPS to free up disk space.