How to import a large MySQL dump into your onion service

How to import a large MySQL dump into your onion service

A dump bigger than a couple of hundred megabytes cannot be imported through phpMyAdmin in the browser — that form is capped at 200 MB — and a gigabyte over Tor would take the best part of an hour anyway. The dependable route is to upload the compressed dump over SFTP and pipe it into the MySQL client over SSH, inside the container, where nothing has to travel through the browser.

Step 1 — Get a dump of the old database

Whatever produced the database, you want a plain .sql file (or a .sql.gz), not a binary backup. On the old server:

mysqldump --single-transaction --quick --routines --triggers \
          -u root -p database_name | gzip > database_name.sql.gz

--single-transaction keeps the table consistent without locking it for the whole dump, and --quick streams rows instead of buffering them — both matter when the database is large and still in use. If the old database is hosted here, export it from phpMyAdmin instead (see "How to back up and restore your database with phpMyAdmin"): export works server-side and has no upload limit.

Step 2 — Upload the dump

Connect over SFTP as described in How to connect to your onion service over SFTP and upload database_name.sql.gz. Put it in www (the web root, where your shell starts) or in backup; either way, keep it out of a directory Apache serves if the file is sensitive, or delete it as soon as the import finished.

Compressed is not optional here: at the 1 MiB/s limit of the container, a 1 GB dump is ~17 minutes of pure transfer, and gzip typically cuts it to a third.

Step 3 — Import it over SSH

Open the shell as described in How to connect to your onion service with SSH and run one command. The four values come from the Database tab of the portal: hostname db (not localhost), port 3306, user root, and its password.

ls -lh database_name.sql.gz

gunzip -c database_name.sql.gz | \
  LD_PRELOAD= mysql --skip-ssl -h db -u root -p'THE-ROOT-PASSWORD' database_name

For an uncompressed dump, use input redirection instead — the restricted shell allows <:

LD_PRELOAD= mysql --skip-ssl -h db -u root -p'THE-ROOT-PASSWORD' database_name < database_name.sql

Why those two odd-looking options are there

They are the difference between "it just works" and two error messages that look like server problems:

• LD_PRELOAD= — the SSH shell of a service is wrapped in torsocks ("Tor mode activated. Every command will be torified for this shell."), which sends all DNS traffic through Tor. The database is a container on the same host, so its name cannot be resolved through Tor and you get ERROR 2005 (HY000): Unknown server host 'db'. Clearing the variable for that single command restores normal local name resolution.
• --skip-ssl — the client in the container is MariaDB's mysql, the server is MySQL 8 with a self-signed certificate. Without the flag the client refuses the connection with TLS/SSL error: Certificate verification failure. The connection never leaves the host, so plain is fine.

Note the password form: -p'…' with no space. Written as -p … MySQL reads the space as "no password" and prompts, which a non-interactive command cannot answer.

Step 4 — Progress, and how long it takes

There is no pv in the container, so the import is quiet until it finishes. Two things explain the wait: the container is limited to 1 MiB/s of disk read, and gunzip competes for the same 0.5 CPU as your website. A 1 GB dump is therefore a 20–40 minute job — run it in a terminal you can leave open, and do not restart it because it "looks stuck".

Step 5 — Verify the import

LD_PRELOAD= mysql --skip-ssl -h db -u root -p'THE-ROOT-PASSWORD' database_name \
  -e 'show tables; select count(*) from your_table;'

Then load the site and check that the application reads the data. If the table count is right but the app shows nothing, the application is connected to a different database — compare with the DB_DATABASE in its configuration (.env for Laravel, wp-config.php for WordPress) with the name you imported into.

If it stops half way

• ERROR 2006 (HY000): MySQL server has gone away — a single statement was larger than max_allowed_packet. Raise it as root for the session (SET GLOBAL max_allowed_packet=268435456;) and import again into an empty database.
• The import dies at "No space left on device" — the dump plus the imported tables did not fit. Extract the dump, import table by table, or increase the disk plan first (see "How to increase disk space for your onion service").
• Duplicated rows after a second attempt — the dump was imported twice. Import into an empty database: create it with CREATE DATABASE name_import; GRANT ALL ON name_import.* TO 'root'@'%';, or drop and recreate the tables.

Related articles

• How to back up and restore your database with phpMyAdmin
• How to connect to your onion service over SFTP
• How to connect to your onion service with SSH
• How to upload a large application to your onion service