Project

General

Profile

Support #828

Migrating Databases to Another Server

Added by Daniel Curtis about 4 years ago. Updated almost 4 years ago.

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

100%

Estimated time:
2.00 h
Spent time:

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

History

#1 Updated by Daniel Curtis about 4 years ago

  • Description updated (diff)

#2 Updated by Daniel Curtis about 4 years ago

  • % Done changed from 0 to 30
  • Status changed from New to In Progress
  • Description updated (diff)

#3 Updated by Daniel Curtis about 4 years ago

  • Description updated (diff)
  • % Done changed from 30 to 50

#4 Updated by Daniel Curtis about 4 years ago

  • % Done changed from 50 to 70
  • Description updated (diff)

#5 Updated by Daniel Curtis about 4 years ago

  • Description updated (diff)

#6 Updated by Daniel Curtis about 4 years ago

  • % Done changed from 70 to 80
  • Description updated (diff)

#7 Updated by Daniel Curtis about 4 years ago

  • Status changed from In Progress to Resolved
  • % Done changed from 80 to 100
  • Description updated (diff)

#8 Updated by Daniel Curtis almost 4 years ago

  • Status changed from Resolved to Closed

Also available in: Atom PDF