How to move a database to a new server

Moving a database to a new server is exporting the data, moving that export somewhere new, and importing it into an empty database on the other end. The mechanics are the same whether the destination is a new hosting account, a new server on the same account, or a server you have set up yourself, and none of it requires the two servers to talk to each other directly.

Step 1: export the database

If you have shell access, mysqldump is the most reliable way to export a database of any size:

mysqldump -u username -p database_name > database_name.sql

You will be prompted for the password rather than typing it on the command line. If the database uses a specific character set you want to preserve exactly, add it explicitly:

mysqldump -u username -p --default-character-set=utf8mb4 database_name > database_name.sql

Without shell access, phpMyAdmin's Export tab does the same job: select the database, choose the "Quick" export method with the SQL format, and download the file. For a database too large for phpMyAdmin to export comfortably in the browser, see importing a database too large for phpMyAdmin, which covers the same size problem from the import side.

Step 2: create a matching database and user on the new server

On the destination, create a new, empty database and a user attached to it with full privileges, the same as you would for a fresh install — see creating a MySQL database and user if you need the full steps. The name does not have to match the old one exactly, but using the same name avoids having to update it inside application code later, beyond the connection configuration itself.

Step 3: transfer the exported file

Move the .sql file from wherever you exported it to somewhere you can reach from the new server, typically by downloading it and then uploading it through FTP, SFTP, or your control panel's file manager. For a very large export, compressing it first with gzip reduces both the transfer time and the space it takes up while sitting on either server.

Step 4: import it on the new server

With shell access, import the file into the empty database you created:

mysql -u username -p database_name < database_name.sql

Without shell access, phpMyAdmin's Import tab accepts the same file: select the empty database, choose the file, and run the import. Importing a database too large for phpMyAdmin covers the alternatives if the file is too large for the browser-based import to handle in one go.

Step 5: update your application's connection details

Once the import finishes, update your application's configuration to point at the new database: host, database name, username and password all need to match what actually exists on the new server, which may differ from the old values even if you tried to keep them consistent. Connecting an application to MySQL covers exactly which values are needed and where they usually go.

Step 6: test before you rely on it

Load the application against the new database and check the things most likely to reveal a problem: logging in, saving a change, and any page that displays content with accents, symbols or emoji, since a character set mismatch between the old and new database tends to show up there first. Do not remove the old database until you have tested the new one thoroughly and are confident it is working correctly on its own.

Keeping downtime to a minimum

For a site that cannot afford much downtime, do the export and import while the old site is still live and serving traffic, test the new database against a temporary copy of the application, and only switch the live application over to the new database once you have confirmed it works. Any changes made to the old database between your export and the actual cutover will not be present in the new one, so keep that window as short as you reasonably can, and take a final export immediately before switching over if the gap matters.

What tends to go wrong

Two problems account for most of the trouble people run into with an otherwise straightforward move. The first is a character set mismatch: an export taken without specifying the character set explicitly can be imported successfully and still display accented characters or emoji incorrectly, because the assumption MySQL made about the encoding during export did not match the assumption it made during import. Specifying --default-character-set on both the export and the import removes the ambiguity rather than leaving MySQL to guess. The second is an import that appears to finish without error but is silently incomplete, usually because a size or timeout limit was hit partway through. Checking the row counts of your largest tables against the old database, rather than only checking that the import command exited without a visible error, catches this before it becomes a problem someone notices in production.

Old server clean-up

Once the new database has been running successfully for a reasonable period, decide what to do with the old one rather than leaving it in place indefinitely. Keeping an old copy around for a short while as a fallback is sensible, but a forgotten database left behind on an old account is both a small ongoing resource cost and, if it still holds real data, a copy of that data sitting somewhere you have stopped paying attention to. Deleting a database without breaking a site covers doing this safely once you are confident the migration is complete and nothing still depends on the old copy.

If you are moving the database as part of a wider site migration rather than on its own, migrating just the database covers where this fits alongside moving files and DNS, and contact us if you would like a hand with any part of the move.