Export and import a MySQL/MariaDB database on a Linux VPS

Moving a website, restoring a backup or cloning a staging environment all come down to the same two operations: dumping a database to a file and loading it back. On a VPS Linux Infrawire you can do both in a few commands, without any web interface. This guide covers consistent dumps, compression, large files, character encoding and direct server-to-server transfer.

📋 Prerequisites

• A Linux VPS running Ubuntu 24.04 or Debian 12 with root SSH access (the IP and the root password are available in your customer area)
• MySQL or MariaDB installed, or at least the client (apt install mariadb-client)
• A database user with SELECT, SHOW VIEW, TRIGGER, LOCK TABLES and EVENT privileges on the database to dump
• Enough free disk space: check with df -h

1. Export a database with mysqldump

For InnoDB tables (the default engine), --single-transaction produces a consistent snapshot without locking the tables, so your site keeps running during the dump.

Bash
1mysqldump -u USER -p --single-transaction --quick DBNAME > DBNAME.sql

On recent MariaDB releases the tool is also available as mariadb-dump; the options are identical.

2. Include routines, triggers and events

Triggers are dumped by default, but stored procedures, functions and scheduled events must be requested explicitly. Forcing the character set avoids surprises with accents and emojis.

Bash
1mysqldump -u USER -p --single-transaction --routines --triggers --events \
2  --default-character-set=utf8mb4 DBNAME > DBNAME.sql

Useful variants:

Bash
1# Structure only, no rows
2mysqldump -u USER -p --no-data DBNAME > schema.sql
3# Several databases, with CREATE DATABASE statements
4mysqldump -u USER -p --single-transaction --databases DB1 DB2 > two-dbs.sql
5# Every database on the server
6mysqldump -u root -p --single-transaction --routines --events --all-databases > all.sql

3. Compress the dump with gzip

SQL text compresses very well. Pipe the output straight into gzip so the uncompressed file never touches the disk.

Bash
1mysqldump -u USER -p --single-transaction --routines --events DBNAME | gzip > DBNAME_$(date +%F).sql.gz

To avoid typing the password (and keeping it out of ps output), store it in ~/.my.cnf:

INI
[client]
user=USER
password=YOUR_PASSWORD

Bash
1chmod 600 ~/.my.cnf

4. Import a dump

Create the target database first, with a modern character set, then load the file.

Bash
1mysql -u USER -p -e 'CREATE DATABASE IF NOT EXISTS DBNAME CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'
2mysql -u USER -p --default-character-set=utf8mb4 DBNAME < DBNAME.sql
3# From a compressed file
4gunzip -c DBNAME.sql.gz | mysql -u USER -p --default-character-set=utf8mb4 DBNAME

Warning: by default the dump contains DROP TABLE IF EXISTS, so importing into an existing database replaces the tables of the same name. Back up the destination first.

5. Handle large files

For multi-gigabyte dumps, run the import inside tmux so an SSH disconnection does not interrupt it, and use pv to see the progress.

Bash
1apt install -y tmux pv
2tmux new -s import
3pv DBNAME.sql.gz | gunzip | mysql -u USER -p --max-allowed-packet=512M DBNAME

Detach with Ctrl+B then D, and reattach later with tmux attach -t import.

6. Transfer directly to another server over SSH

No intermediate file is needed: the dump travels through an encrypted SSH pipe and is loaded on arrival. Credentials are read from ~/.my.cnf (or the local socket as root) on each side.

Bash
1mysqldump --single-transaction --routines --events DBNAME | gzip \
2  | ssh root@NEW_VPS_IP 'gunzip | mysql DBNAME'

The database DBNAME must already exist on the destination server.

✅ Verify the result

Bash
1gzip -t DBNAME.sql.gz && echo 'archive OK'
2mysql -e 'SHOW TABLES;' DBNAME
3mysql -e 'SELECT COUNT(*) FROM TABLENAME;' DBNAME
4mysql -e "SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name='DBNAME';"

Compare row counts of a few key tables between source and destination.

🔧 Troubleshooting

ERROR 1045 Access denied : wrong user or password, or the user is not allowed from this host. Test with mysql -u USER -p first.

ERROR 2006 MySQL server has gone away : a row or query exceeds max_allowed_packet. Raise it on the server in the [mysqld] section of the configuration and restart the service, or pass --max-allowed-packet=512M to the client.

Garbled accents after import : the file was read with the wrong charset. Dump with --default-character-set=utf8mb4 and import with the same option.

ERROR 1227 or DEFINER errors : the dump references a user that does not exist on the destination. Strip the clause while importing:

Bash
1sed -E 's/DEFINER=`[^`]+`@`[^`]+`//g' DBNAME.sql | mysql -u USER -p DBNAME

---

🎉 Your databases are now easy to back up, restore and migrate between servers!