www.mjhall.org

IT training resources & rants

View My GitHub Profile

MariaDB bulk database migration

I recently upgraded a development laptop to CentOS 7 and built a spare backup laptop running the same operating system. After manually creating a dozen MariaDB databases and users and importing their data from dump files onto the first laptop, I wasn’t interested in repeating that process again on the second laptop.

The following procedure describes how I migrated an entire MariaDB RDBMS installation in one go without recreating any databases or users or dumping and re-importing any files. The same procedure would of course work for migrating similar data between CentOS 7 servers.

Note that this data migration was between two machines with identical freshly installed operating systems. In cases where the operating systems, distributions or version were different things might not go so smoothly. One concern would be that global database configuration parameters on both machines were compatible.

Original Database Server

1: Create a directory to hold the data being migrated:

# mkdir mariadbdata

2: Stop the MariaDB/MySQL server:

# systemctl stop mariadb

3: Copy contents of /var/lib/mysql to the directory just created:

# cp -r /var/lib/mysql/* mariadbdata

4: Restart the MariaDB/MySQL server again:

# systemctl start mariadb

5: Compress the data:

# tar -czvf mariadbdata.tar.gz mariadbdata

6: Copy the compressed file to new server.

New Database Server

1: Install MariaDB but don’t start it yet.

2: Uncompress data file:

# tar -xzvf mariadbdata.tar.gz

3: Move contents of data directory to /var/lib/mysql:

# mv mariadbdata/* /var/lib/data

4: Change ownership of all files in /var/lib/mysql to mysql user:

# chown -R mysql.mysql /var/lib/mysql/*

5: Restore correct SELinux security contexts:

# restorecon -R /var/lib/mysql/

6: Enable and start MariaDB:

# systemctl enable mariadb
# systemctl start mariadb

That’s it! As mentioned above, this went so smoothly probably because the operating systems and installed software at either end were identical.