Migrating databases (MySQL/MariaDB).

Most CMS migrations are 90% files + 10% database. The database is what stores your content, users, settings — losing it means losing everything. Here's how to move it cleanly.

Method 1: phpMyAdmin export/import (easiest)

Best for databases under 100 MB.

On the old host

1. cPanel (or DirectAdmin / Plesk) → phpMyAdmin
2. Click your database in the left sidebar
3. Export tab → leave defaults → Go
4. Download the .sql file

On Rivervo

1. cPanel → MySQL Databases → create a new database (note the name) and a user, assign user to DB with all privileges
2. cPanel → phpMyAdmin → click new database → Import tab → upload the .sql file → Go

Done. Update your app's config to point at the new DB credentials.

Method 2: mysqldump via SSH (best for medium-large DBs)

Bigger DBs hit phpMyAdmin's upload limit (256 MB usually). SSH method has no such limit.

On old host (if SSH access)

mysqldump -u olduser -p --single-transaction --quick --lock-tables=false oldwp > backup.sql

--single-transaction keeps the site working during dump (no locking).

Transfer to new host

Either via scp:

scp backup.sql newuser@newhost.com:/home/newuser/

Or via web upload (cPanel File Manager) if you can't SSH between hosts.

On Rivervo (SSH)

mysql -u newuser -p newwp < backup.sql

For big files, watch progress with pv:

pv backup.sql | mysql -u newuser -p newwp

Method 3: direct host-to-host (fastest for huge DBs)

If both hosts have SSH and outbound network access:

# From new host:
ssh olduser@oldhost.com 'mysqldump --single-transaction olddb' | mysql -u newuser -p newdb

Streams directly from old → new without intermediate file. Limited only by network speed.

For 50+ GB databases, consider compression in transit:

ssh olduser@oldhost.com 'mysqldump --single-transaction olddb | gzip' | gunzip | mysql -u newuser -p newdb

Connection settings — what to update

After import, your app's config file needs the new credentials.

WordPress (wp-config.php):

define('DB_NAME',     'newuser_newwp');
define('DB_USER',     'newuser_dbuser');
define('DB_PASSWORD', 'newpass');
define('DB_HOST',     'localhost');
define('DB_CHARSET',  'utf8mb4');
define('DB_COLLATE',  '');
 
$table_prefix = 'wp_';   // must match the prefix from old DB

Laravel (.env):

DB_HOST=localhost
DB_DATABASE=newuser_newwp
DB_USERNAME=newuser_dbuser
DB_PASSWORD=newpass

The exact field names vary by app, but the pattern is the same.

Verifying the import

USE newdb;
SHOW TABLES;
SELECT COUNT(*) FROM wp_posts;   # or whatever big table you have

Compare counts to the old DB. If they match, import succeeded.

Common errors

"Access denied for user". New user doesn't have permissions on the new DB. Re-add user → DB association in cPanel → MySQL Databases.

"Got error 28 from storage engine". Disk full on import. Free space on the host or upgrade plan.

"Unknown collation: 'utf8mb4_0900_ai_ci'". Old DB used MySQL 8 collation, new host on MariaDB doesn't support it. Run search-replace on the SQL file:

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

Then re-import.

"Table 'wp_X' doesn't exist". Foreign keys imported in wrong order. mysqldump should handle this with --add-drop-table (default). Re-export with that option set.

What to update after move

• DB credentials in app config (above)
• Site URL if changed (search-replace https://oldhost.com → https://newhost.com in DB)
• Permalinks (WordPress: Settings → Permalinks → Save, no changes — regenerates rules)
• Search index (rebuild if you used external search like Elasticsearch)