Back Up and Restore a MySQL Database: phpMyAdmin, mysqldump and Large Files

Most websites keep their important data in a MySQL or MariaDB database: orders, users, posts, settings. Files can often be re-uploaded. A lost database usually cannot be rebuilt. This guide covers the two common ways to back up and restore a database, phpMyAdmin and mysqldump, then the errors you hit with large files, and how to automate backups with cron.

Which tool to use

• phpMyAdmin works in the browser and needs no SSH. It is fine for small and medium databases, as a rule of thumb up to a few tens of megabytes. Above that, uploads and PHP time limits start to get in the way.

• mysqldump runs on the command line. It handles large databases, can be compressed on the fly, and can be scheduled. If you have SSH access, prefer it.

Both produce the same thing: a .sql file of SQL statements that recreate the tables and data.

Back up with phpMyAdmin

1. Open phpMyAdmin and select the database in the left panel.

2. Open the Export tab.

3. Choose Custom as the export method. Keep the format as SQL.

4. Under Output, set compression to gzipped. SQL text compresses very well, so the file gets much smaller.

5. Under object creation options, enable Add DROP TABLE if you plan to restore over an existing database.

6. Click Export and keep the downloaded file somewhere other than the server.

Restore with phpMyAdmin

1. Select the target database (create an empty one first if needed).

2. Open the Import tab and choose your .sql or .sql.gz file. phpMyAdmin reads gzip files directly.

3. Keep "Allow the interruption of an import" enabled. If the import stops because of a time limit, you can import the same file again and phpMyAdmin continues from where it stopped.

4. Click Import.

The Import page shows a maximum upload size, for example "Max: 50MiB". That limit comes from PHP, not from MySQL: upload_max_filesize and post_max_size, plus max_execution_time for long imports. On shared hosting you may be able to raise these in your control panel's PHP settings. If the file is still too large, compress it, split it, or switch to mysqldump over SSH.

Back up with mysqldump

A solid default command:

mysqldump -u dbuser -p \
  --single-transaction --quick \
  --routines --triggers \
  --default-character-set=utf8mb4 \
  dbname > dbname.sql

What the options do:

• --single-transaction takes a consistent snapshot of InnoDB tables without locking them, so the site keeps working during the backup. It does not help MyISAM tables, which still need locks to be consistent.

• --quick streams rows one at a time instead of loading whole tables into memory. It is on by default in modern versions, but stating it does no harm.

• --routines --triggers include stored procedures, functions and triggers. Without them, an application that depends on them breaks after restore.

• --default-character-set=utf8mb4 avoids broken characters. If Arabic text comes back as something like ال, a character set mismatch during dump or import is the usual cause.

Compress the dump as it is written:

mysqldump -u dbuser -p --single-transaction --quick --routines --triggers \
  --default-character-set=utf8mb4 dbname | gzip > dbname-$(date +%F).sql.gz

If you dump as a user without the PROCESS privilege, which is common on shared hosting, MySQL 8 may stop with:

mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

Add --no-tablespaces to the command. Most applications do not need tablespace information in the dump.

Restore with mysql

# create an empty database first (skip if it already exists)
mysql -u root -p -e "CREATE DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

# plain .sql file
mysql -u dbuser -p --default-character-set=utf8mb4 dbname < dbname.sql

# compressed .sql.gz file
gunzip < dbname-2026-10-25.sql.gz | mysql -u dbuser -p --default-character-set=utf8mb4 dbname

The restore replaces tables that exist in the dump. Restoring into the wrong database is a common and painful mistake, so check the database name before you press Enter.

Large files: what goes wrong and how to fix it

Long imports and dropped SSH sessions

A multi-gigabyte import can take a long time. If your SSH connection drops, the import stops. Run it inside tmux or screen, and use pv to see progress:

sudo apt install pv tmux
tmux new -s restore
pv dbname.sql.gz | gunzip | mysql -u dbuser -p --default-character-set=utf8mb4 dbname

If you disconnect, reconnect and run tmux attach -t restore.

"MySQL server has gone away"

ERROR 2006 (HY000) at line 1234: MySQL server has gone away
ERROR 1153 (08S01) at line 1234: Got a packet bigger than 'max_allowed_packet' bytes

A single statement in the dump, usually a large multi-row INSERT or a big BLOB, is larger than the server accepts. Raise max_allowed_packet:

# client side, for this import only
mysql -u dbuser -p --max-allowed-packet=256M dbname < dbname.sql

# server side, in the [mysqld] section of my.cnf, then restart MySQL/MariaDB
max_allowed_packet = 256M

256M is a common working value, not a magic number. On shared hosting you cannot change the server setting, so ask support or import through phpMyAdmin in smaller parts.

Access denied because of DEFINER

ERROR 1227 (42000) at line 87: Access denied; you need (at least one of) the SUPER or SET_USER_ID privilege(s) for this operation

Views, triggers and procedures in the dump carry a DEFINER clause naming the original user, for example root. Your account on the new server cannot create objects owned by another user. Remove the clauses before importing:

sed -E 's/DEFINER=[^ ]+ //g' dbname.sql > dbname-clean.sql

Unknown collation when moving from MySQL 8 to MariaDB

ERROR 1273 (HY000) at line 25: Unknown collation: 'utf8mb4_0900_ai_ci'

MySQL 8 uses collations that MariaDB does not know. Replace them in the dump:

sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' dbname.sql

Automate backups with cron

Do not put the password on the command line, where it can show up in the process list and shell history. Store it in ~/.my.cnf for the user that runs the backup (for root, /root/.my.cnf):

[client]
user=dbuser
password=change-this-password

chmod 600 ~/.my.cnf

mysqldump and mysql read the [client] section automatically. Then create /usr/local/bin/mysql-backup.sh:

#!/bin/bash
set -euo pipefail

BACKUP_DIR=/var/backups/mysql
DB=dbname
KEEP_DAYS=14

mkdir -p "$BACKUP_DIR"
mysqldump --single-transaction --quick --routines --triggers \
  --default-character-set=utf8mb4 "$DB" \
  | gzip > "$BACKUP_DIR/$DB-$(date +%F-%H%M).sql.gz"

find "$BACKUP_DIR" -name "$DB-*.sql.gz" -mtime +"$KEEP_DAYS" -delete

set -o pipefail makes the script fail when mysqldump fails, even though gzip is the last command in the pipe. The find line keeps the last 14 days, so the disk does not fill up. Make the script executable and schedule it:

sudo chmod 700 /usr/local/bin/mysql-backup.sh
sudo crontab -e

30 2 * * * /usr/local/bin/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1

That runs every night at 02:30. One detail: if you put date +%F directly inside a crontab line, escape each percent sign as \%. In a separate script, as here, you do not need to.

Keep a copy off the server, and test it

A backup on the same server is lost along with the server. Copy the files to another machine or storage service on a schedule, for example with rsync over SSH.

Then test a restore into a separate database from time to time:

mysql -e "CREATE DATABASE restore_test;"
gunzip < /var/backups/mysql/dbname-2026-10-25-0230.sql.gz | mysql restore_test
mysql -e "SELECT COUNT(*) FROM restore_test.orders;"
mysql -e "DROP DATABASE restore_test;"

Replace orders with a table you know. If the count looks right, the backup works. A backup that has never been restored is untested.

As a rule of thumb, once a database reaches tens of gigabytes, dumps and restores become slow. At that point, physical backup tools such as mariadb-backup or Percona XtraBackup are worth a look.

Run it on IM HOST

IM HOST Shared Hosting runs on NVMe storage with free SSL and free daily backups for the whole subscription, so you have a copy to fall back on alongside your own exports. On a Linux VPS you get full root access to tune max_allowed_packet and schedule your own dumps, and daily backup is available as an optional add-on. Moving from another host? Our free migration service moves files, databases and email without downtime, and our engineers are available 24/7.