Introduction

I've worked with clients that, for one reason or another, needed to change their database platform.

This ask has increased with the move to DXPCloud and MySQL database.

I'm not sure there's a well defined plan for how to migrate a Liferay database, so I thought I'd blog about it.

Now, if you know anything about me, you know I'm a planner and I create (sometimes intricate) processes to complete a task, and this is no different.

It is important to understand why this process is so detailed...

So yes, I put a great deal of time and effort into defining this process because I want to have the strongest foundation possible for the Liferay environment going forward.

Any misstep will be hard to recover from in the future; if you complete a migration, let the environment run for a week and then realize something didn't migrate correctly, well at that point you're in big trouble. You have a weeks worth of data and changes that might be difficult or impossible to discard, and something from the old DB that needs to be moved over... It can be a real disaster.

So sure, this process is detailed and thorough, but hopefully the reasons for that are clear now, so let's get started.

Oh, one more thing - Let's not do this in production first, yeah? Test out your migration plan in the lower lanes, it will give you an opportunity to evaluate the plan and get it working to your satisfaction before you attempt it in your production environment.

Preparation

There's a couple of things to do before the DB migration...

Prune the Database

The first thing you should consider is cleaning up your database. Any cruft you have in the database is cruft you have to move and cruft that will be in your new database.

Eliminating the cruft will help to speed up the actual migration process, too.

Liferay has a page to help with data pruning here: https://learn.liferay.com/dxp/latest/en/installation-and-upgrades/upgrading-liferay/upgrade-stability-and-performance/database-pruning-for-faster-upgrades.html

Alternatively, you might want to try my DB Pruner to help automate some of this effort: https://web.liferay.com/marketplace/-/mp/application/167037532

Download the DB Driver

If moving to a commercial database or even to use a later driver on an open source database, you may need to download the JDBC driver for Liferay use.

In versions up to 7.3, the driver jar file would go in Tomcat's lib/ext (or equivalent on other platforms). For 7.4+, the driver jar would go to the ROOT/WEB-INF/shielded-container-lib directory (unless you're using JNDI, in which case you use Tomcat's lib directory).

Create the Target Database

Whatever database you're migrating to, you'll need to create the Liferay database and credentials so Liferay will be able to connect.

A few items to remember:

Create a database that uses (at least) UTF-8 as the character set. If you plan on supporting emoticons, use UTF-16.

Create a user for Liferay to connect as. This user needs to be able to create, alter and delete tables and indexes. The easiest path is often to create a special database for Liferay tables and create a user that has DBA privileges but only on that special database; use different connections for other business tables and databases. This way Liferay will have the ability to do what it needs on its own database, but you limit access to the business data to only what Liferay requires.

Follow the target database guidance with respect to setting up tablespaces, datafiles, and database engines.

Create the Tables

So this may seem weird, but the next step is actually to configure your Liferay environment by pointing it at the new database and then starting it so Liferay can create all of the tables.

In some of the migration tools that I'll be covering in the next section will not say something like this is necessary. The tools will make decent guesses about what a column type should be in the target database given the column type in the source database.

Most of the time the tool likely would make the right guess about what the new column type would be. But me, I'm like "why risk it?"

Seriously, Liferay knows what the tables should be called in the target database, how they should be capitalized, etc., so let Liferay create the tables for you.

Afterwards you are free to truncate the tables to make them ready to receive the migrated data, but the types and sizes and what not will be exactly what Liferay expects to find.

Understand Capitalization Concerns

One issue that consistently trips people up are the internal database capitalization rules.

MySQL is probably the worst here as they have different capitalization rules depending upon which platform is hosting the database. In Windows, tables are case insensitive, in Linux tables are case sensitive, and on MacOS they might be case sensitive depending upon which filesystem you're using.

The point here is that you need to know what the capitalization is in the source database as well as in the target database as there may be a need to "massage" the process with a little bit of remapping.

So going from MySQL on a Windows host to MySQL on a Linux host, you could face problems because a statement like CREATE OR REPLACE TABLE user_ would update an existing User_ table Liferay generated, but it would be a completely new table on the Linux side, resulting in a User_ table and a user_ table and bad outcomes will stem from that.

In any route you take to migrate the data, be sure to take capitalization into account so your data ends up in the right table for Liferay to use.

One case that stands out... A client migrated their Windows MySQL database over to Linux, then they fired up Liferay pointed at the new database expecting to see all of their existing content still there, but found that they had a clean, empty Liferay database! Since Windows is not case sensitive, all of the tables were in the Linux database as lower case; when Liferay was looking for the Release table, it didn't find one (there was the release table, but that failed the lookup) and so Liferay created all new tables. All of their tables were effectively duplicated; lowercase tables from the source database and mixed case tables from the Liferay startup.

You can use the lower_case_table_names property in MySQL to control how the tables will be named, but you could also take care to map old names to new names and avoid having to track and manage that property separately.

Migrate the Data

Note that this section is titled Migrate the Data and not Migrate the Database... Because of our last prep step in the last section, the tables and indexes and everything have all been created, so the only thing left to do is to migrate the data.

There are a bunch of different strategies for you to try here, let's review the possibilities...

Manual Data Migration

This one is likely the most labor intensive.

Basically for your source tables, you want the source database native tools to export the data as SQL INSERT INTO statements.

With these files, you can use the target database native tools to run the SQL and insert the data into the new Liferay database.

This is obviously the most tedious as it gets repeated across all of the source tables, but it can be an effective way to handle the table renaming issue from the previous section... Just use an editor to do a global search and replace of the lowercase name to the mixed case Liferay uses and you'll have the right outcome.

SQLines Data

Tooling can help avoid the manual migration effort. One open source tool to consider is SQLines. It supports multiple different flavors of open source and commercial databases. Since we let Liferay create all of the target tables, we really only need to move the data using SQLines Data which is the command line tool for migrating data from the source to the target database.

To deal with possible table name mapping (for the capitalization issue discussed before), the SQLines Data tool has built in support for table name mapping.

It has plenty of other features to help you move data from the source database to the target database.

MySQL Workbench

If you are moving to MySQL or MariaDB, MySQL Workbench can help with the migration.

While the tool does allow for table name mapping, it can be difficult to process mass table renames prior to starting the migration.

So this tool is best used when you're doing a simple data migration w/o table name remapping, but if remapping is necessary I'd consider another tool instead.

SymmetricDS

The open source tool I've had the most success with is SymmetricDS. It is written in Java and based on a fork of an old and long since deprecated Apache database project, but it is alive and well. Since it is written in Java it is cross-platform and can run on any host you want.

SymmetricDS has documentation specifically to support data migration.

Using this method, there is normally a two step process, the first is to export the data to a file and the second is to import the data into the target database. Since a file is holding the data, you can easily complete the search/replace in the file to change table names for case sensitivity issues.

Target Database Native Tools

Many target databases will come with their own tools to support migrating onto their database. Oracle's SQL Workbench, for example, can help migrate from other vendors into an Oracle database.

Features and capabilities and even availability though will vary depending upon the platform that you're moving to, so your own mileage may vary here.

Try, Try Again

The rule in selecting a tool from this list: Try others until you find one that works.

Not all tools are going to be right for every situation. Some may handle your data better than others. Some will be able to handle the inserts without hitting constraint problems that cause problems on others.

Give them a try until you find one that gives you a clean migration and passes the testing phase.

Conclusion

Well, it's the conclusion of the blog, but not the conclusion of the process.

You've just completed your data migration onto the database that Liferay created for you. If you completed the database pruning earlier you have a slim database ready to go.

But is it ready to go?

Maybe, maybe not. You still want to complete a round of testing. Make sure your articles are in the new database, all of your users and permissions, all of the documents, workflow, etc. Do a complete test of your environment to ensure it actually is ready to go.

Hope this helps with your Liferay Database Migration needs!

DXP Cloud Conclusion

Hey, so although this blog is generally about Liferay database migration, more often these days it is being driven by a move to DXP Cloud.

Effectively everything I've blogged about here still absolutely applies, you're just going to be doing this work on a local MySQL database, not the one in the cloud.

Complete the work to migrate onto MySQL, and complete all of the testing to verify your MySQL local database is acting exactly as you expect.

When you're done with testing, you'll take a dump of the local MySQL database and will use that to populate your DXP Cloud database.

When you get to this point and want help loading your dump to your DXP Cloud environment, the DXP Cloud support team will be happy to help you with that.