This document offers some tips on how to migrate your Drupal website from a database running on the same server as your web server, to a physically separate database server.

Migrating your database

The following steps allow you to migrate your database from one server to another, however they do require some down time to prevent any data being lost while you copy the database from your old database server to your new database server. There are tricks for minimizing downtime if working with a very large database using MySQL's binary logs, but this is not covered in depth in this document. If copying a smaller database, it is generally quickest and certainly simpler to follow the directions below.

Be sure to fully review all steps before following this process, as your website will be off-line during the migration. It is a good idea to practice all steps on a non-production server a few times if you've never done this before, to get familiar with all commands.

1. Put your Drupal website off-line
   Sign in to your website as an administrator, and follow the menus to “Administer >> Site configuration >> Site maintenance”. On that page, click “Off-line”, and enter a custom message into the “Site off-line message” text box, then click “Save configuration”. At this point, only users with “administer site configuration” permissions will be able to browse your website, everyone else will see your maintenance message.

2. Backup your database
   Text backups are a simple storage-engine-agnostic method of transferring a database from one server to another. MySQL provides the mysqldump utility for performing this action. The utility supports numerous options, those that we recommend follow.

   • --user=user_name, -u user_name
     This option is used to specify a username when connecting to the database.

   • --password[=password], -p[password]
     This option is used to specify a password when connecting to the database. We use the -p[password] option for automated scripts, otherwise we are prompted for a password.

   • --host=host_name, -h host_name
     This options is used to specify a hostname when connecting to a remote database, only necessary if you are performing the backup from a different server than the existing database process is running on.

   • --add-drop-table
     This will add a DROP TABLE statement before each CREATE TABLE statement, useful if trying to restore over an existing table.

   • --add-locks
     This will surround each table dump with LOCK TABLES and UNLOCK TABLES statements, useful as it results in faster inserts when the dump is reloaded.

   • --comments, -i
     Enabled by default, this option writes additional information in the dump file such as program version, server version, and host.

   • --compress, -C
     This option will compress all information sent between the client and the server minimizing the traffic sent across the network. You only need to use this option if you are performing the backup from a different server than the existing database process is running on.

   • --extended-insert, -e
     This option results in a smaller dump file and a speeds up inserts when the file is reloaded by using multiple-row INSERT syntax that include several VALUES lists.

   • --no-autocommit
     This option can improve performance when reloading the dump by enclosing the INSERT statements for each dumped table within SET AUTOCOMMIT=0 and COMMIT statements.

   • --result-file=file, -r file
     This option is used to direct output to a given file.

   • --quick, -q
     This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out.

   So, for example, backup your database with the following command:

   mysqldump -u USERNAME -p -h HOSTNAME --add-drop-table --add-locks -i -C -e --no-autocommit -q -r OUTPUT.FILE DATABASE_NAME
   

3. Load your backup onto your new database server

   1. Move your database backup onto your new database server. If you set up database permissions to allow it, you can actually make your backup of your old server from your new server, using the “-h” option detailed above.

   2. Be sure your new database exists in your server. Create it if necessary with “mysqladmin -u USERNAME -p create DATABASE_NAME”.

   3. Load your backup into the new database:

      $ mysql -u USERNAME -p < DATABASE_NAME

4. Reconfigure settings.php to point to your new database
   On your webserver, modify the $db_url line from settings.php, reconfiguring your web server to talk to your new database server.

5. Put your website back on-line
   Return to the “Site maintenance” page and put the website back on-line.

6. Test that your website is fully functional.
   Be sure to test all user roles, including accessing the website as a normal user and as an anonymous visitor.