Migrating a MySQL dump to a new server

Migration through a dump is the direct way to move PHP script data: export on the old server, transfer the file to the new one, import into an empty database, and edit the config. Without a dump, moving files gives you only an empty shell. Full site migration, files plus DNS: script migration.

It works the same from local MicroServer to hosting and from shared hosting to VDS. Only the way you retrieve the file changes.

Export on the source

mysqldump -u user -p --single-transaction --routines dbname > dump.sql

On shared hosting without SSH, use phpMyAdmin -> Export or a panel backup. Make sure the file downloaded fully: at the end of a dump there are usually transaction-ending lines, not a break in the middle of CREATE TABLE.

Before export, a short window without active orders or publications is desirable if you fear inconsistency. For InnoDB, --single-transaction reduces locking pain.

Backup ritual: PHP and MySQL backup.

Preparation on the new server

1. Create an empty utf8mb4 database.
2. Create a user and grant permissions on this database: CREATE/INSERT/...
3. Check that MySQL listens and is reachable from the host where PHP runs, usually localhost.

Requirements: PHP/MySQL. Locally, it is convenient to create a database here: phpMyAdmin.

Import

mysql -u user -p newdb < dump.sql

Through phpMyAdmin, use the Import tab. A large file may hit upload_max_filesize or timeouts; then use CLI or upload the dump to a directory and import from the console.

Import errors:

• Access denied: wrong login or permissions.
• Unknown collation / charset: old server versus new; usually fixed by utf8mb4 on both sides.
• Table exists: database is not empty; clear it or create a new one.
• Packet too large: raise max_allowed_packet.

Script config

Enter the new host, database name, user, and password. Old values from the previous server are the main reason for "I imported the dump, but the site is 500". Symptoms: database error, 500.

Update the site URL in settings or options tables if the domain changed. Otherwise images and redirects point to the old host.

MySQL users do not move "as is"

A database dump usually does not include server MySQL users, except in special modes. On the new server, create accounts again and grant permissions. The password in the script config is the password of the new account.

MySQL/MariaDB versions

Moving from 5.7 to 8.0 is usually fine. Moving back from 8 to very old 5.5 is painful. Check sql_mode if import complains about zero dates; old defaults often appear in dumps. Do not disable sql_mode globally without understanding the cost; it is better to fix the data.

After import - check

• The number of tables matches the source.
• Home page and account area open.
• A fresh record, such as an ad or order, can be written.
• Text did not turn into "???".

Uploaded files are transferred separately as an archive; the dump does not contain them. Permissions: chmod.

Automation

On a VDS, you can build a chain: nightly dump plus copy to another node. Do not forget file rotation and size checks. Firewall on the receiver: firewall. Web stack: Nginx + PHP-FPM.

Fresh installation without data migration: installation. If something stops halfway through migration, send a ticket with the import error text: support.

Dump compression

Large sql files are convenient to compress:

mysqldump ... | gzip > dump.sql.gz
gunzip -c dump.sql.gz | mysql -u user -p newdb

On Windows with MicroServer, you can get the dump through phpMyAdmin and compress it with 7-Zip. The important part is not to corrupt the file by ASCII ftp transfer; use binary mode.

Triggers, procedures, VIEW

If the product uses routines, mysqldump needs --routines. Without it, part of the logic disappears on the new server, and errors appear later. VIEW objects with a definer user missing on the new server also hurt; edit the definer or create a user with the same name.

Time zones and sql_mode

If order times move by three hours after migration, MySQL or PHP time_zone differs. Compare date.timezone in php.ini and the time zone in the script admin panel. It is not always a broken dump.

Strict sql_mode on MySQL 8 can reject old zero dates. Then import fails on a specific line. Options: fix the data, temporarily loosen mode for the import session, and understand the price. Do not leave a weaker mode forever without reason.

Partial migration

Sometimes only core tables are moved without logs. This makes sense for huge access_log tables. Do not accidentally cut relation tables. Compare the table list with a fresh installation of the same product: installation on an empty database shows the reference set.

Checksum checks

For important moves, compare row counts in key tables before and after, such as users, orders, or ads depending on the product. A mismatch right after import means a truncated file or import into the wrong database. Silent failure is worse than a loud one.

Migration rollback

Until DNS is switched, the old server is still the source of truth. The new one can be reloaded three times. After DNS switch, keep the old server read-only or disable writes and cron; otherwise new orders appear "nowhere". Site migration order: script migration. Backup before start: backup.

File permissions after upload: chmod. If the site returns 500 immediately after config change: 500 error, database error. Local rehearsal: MicroServer, phpMyAdmin.

Do not import a dump into a database where another project already lives "temporarily". Table prefixes may match and you will overwrite someone else's data. It is better to create a new database name and change only the target site's config. On shared hosting, prefixes like u12345_ are mandatory; do not rename the database in the panel in a way that breaks the user. Recreate grants. After migration, walk through the admin panel: counters, latest orders or ads, one image. If numbers are zero while tables have data, check whether the script connected to an empty database with a similar name. Encoding requirements: requirements. Full site migration: migration. Backup before experiments: backup. Server choice for migration: VDS or hosting. If Access denied appears again after import: database error.