Database Migration (MySQL / MariaDB)
This guide explains how to securely export MySQL and MariaDB databases using mysqldump and import them on a new server — individual databases or all at once.
Prerequisites: SSH access to both servers. MySQL or MariaDB must be installed and configured on the target server. The MySQL version on the target should be equal to or higher than the source.
Step 1: List databases and users
Get an overview of all databases and users before exporting.
Terminal
mysql -u root -p -e "SHOW DATABASES;"
mysql -u root -p -e "SELECT user, host FROM mysql.user;"
Step 2: Export databases
Export all databases or individual ones using mysqldump.
Export all databases at once
Terminal
mysqldump -u root -p --all-databases --single-transaction --routines --triggers > /root/all-databases.sql
Export a single database
Terminal
mysqldump -u root -p --single-transaction --routines --triggers databasename > /root/databasename.sql
| Flag | Purpose |
|---|---|
| --single-transaction | Exports consistently without locking tables — important for live applications |
| --routines | Includes stored procedures and functions |
| --triggers | Includes triggers |
Step 3: Transfer the export file to the target server
Copy the SQL file securely to the target server using scp.
Terminal
scp /root/all-databases.sql root@TARGET_SERVER_IP:/root/
Step 4: Import databases on the target server
Import the exported SQL file on the target server.
Terminal
mysql -u root -p < /root/all-databases.sql
For a single database, create it first then import:
Terminal
mysql -u root -p -e "CREATE DATABASE databasename;"
mysql -u root -p databasename < /root/databasename.sql
Step 5: Verify the import
Confirm all databases and tables were transferred correctly.
Terminal
mysql -u root -p -e "SHOW DATABASES;"
mysql -u root -p databasename -e "SHOW TABLES;"
Also verify that your applications on the target server can connect to the databases correctly before updating DNS records.
Further Documentation
For all mysqldump options, refer to the official MySQL documentation.