Project

General

Profile

Support #828

Migrating Databases to Another Server

Added by Daniel Curtis over 8 years ago. Updated about 8 years ago.

Status:
Closed
Priority:
Normal
Assignee:
Daniel Curtis
Category:
Database Server
Target version:
Debian
Start date:
07/26/2016
Due date:
% Done:

100%

Estimated time:
2.00 h
Spent time:
4.00 h

Description

I recently needed to move two database services, PostgreSQL and MariaDB, from a Debian server on a VM to a FreeBSD jail. Since the database server on the VM was the database server for a few web services, I wanted as little downtime as possible.

This is a guide on the process by which I migrated the two database services to the new server.

localdb.example.com - current Debian VM database server
remotedb.example.com - new FreeBSD jail database server

Prepare the New Server

Start by installing and setting up the two database services in the new server.

Install PostgreSQL

  • Install and setup PostgreSQL
    • NOTE: Make sure to test connecting to the remote server:
      psql -h remotedb.example.com -U pgsql template1
    • NOTE: Make sure to have the following set in the pg_hba.conf file to allow passwordless connections by the admin to the database socket:
      # Database administrative login by Unix domain socket
local   all             postgres                                trust

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

Install MariaDB

  • Install and setup MariaDB
    • NOTE: Make sure when setting up the MariaDB server to Allow remote root login during mysql_secure_installation. This will be disabled later.
    • NOTE: Make sure to add the root database user on the remote server:
      CREATE USER 'root'@'localdb.example.com' IDENTIFIED BY 'SuperSecretPassword';

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localdb.example.com';

FLUSH PRIVILEGES;

quit
    • NOTE: Make sure to test connecting to the remote server:
      mysql -h remotedb.example.com -uroot -p

Migrate the Databases

Migrate PostgreSQL

Online Migration

  • Migrate the local PostgreSQL databases, localdb.example.com, to the new remote server, remotedb.example.com:
    pg_dumpall -h localhost -U postgres --clean | PGPASSWORD="SuperSecretPassword" psql -h remotedb.example.com -U pgsql -w -d template1

Offline Migration

  • Dump the databases to the postgresql-backup.sql file:
    pg_dumpall -h localhost -U postgres --clean -f postgresql-backup.sql
  • Move the postgresql-backup.sql file to the new server:
    scp postgresql-backup.sql remotedb.example.com:
  • Login to the remotedb.example.com server and import the databases:
    psql -h remotedb.example.com -U pgsql -W -d template1 < postgresql-backup.sql

Migrate MariaDB

Online Migration

  • Migrate the local MariaDB databases, localdb.example.com to the new remote server, remotedb.example.com:
    mysqldump --all-databases -uroot -pSuperSecretPassword | mysql -hremotedb.example.com -uroot -pSuperSecretPassword

Offline Migration

  • Dump the databases to the mariadb-backup.sql file:
    mysqldump --all-databases --host db.example.com --user user -p > mariadb-backup.sql
  • Move the mariadb-backup.sql file to the new server:
    scp mariadb-backup.sql remotedb.example.com:
  • Login to the remotedb.example.com server and import the databases:
    mysql --user root -p < mariadb-backup.sql

Resources

Also available in: Atom PDF