1

Status
I currently run a LAMP server on Ubuntu 20.04, that has a MySQL server containing several Drupal and Wordpress databases, along with a PHPMYADMIN db.

Request
Is there some super smart MySQL database expert that is willing to publish a "definitive guide" on how to migrate working databases from a LAMP (MySQL-Server) to a LAMP (MariaDB-Server), CONTAINING ALL STEPS starting from

1.) "Step 1" = EXPORT desired working databases,
2.) to completely removing MySQL server,# If necessary, I know Mariadb is a dropin
3.) to installing to MariaDB-server,
4.) and the "Last Step" = IMPORT desired working databases?

I have been searching online for days and can online find "piecemeal" tutorials. I think that MariaDB gains marketshare, such a tutorial would be valuable to Ubuntu 18.04 / 20.04 users. If such a guide already already exists, please forward :-)

3

A "definitive guide" will be hard to write for every situation as every server is different. Versions have to be taken into account as well as database engines and the like. That said, the basics would look like this:

1 — Export all databases (with procedures & triggers intact)

The first — and most important thing — is that you first export all of your databases. There are some that you will not need going forward, though, so let's export just the ones you care about:

mysqldump -u {user} -p --events --routines --triggers wordpress > wordpress-export.sql
mysqldump -u {user} -p --events --routines --triggers drupal > drupal-export.sql
mysqldump -u {user} -p --events --routines --triggers phpmyadmin > phpmyadmin-export.sql

Be sure to change {user} to the user account with the highest privileges in your database, otherwise you may have an incomplete export. Be sure to also change the name of the databases to their proper names, and the export name to something unique.

I would not recommend using the --all-databases flag here as that will include three MySQL-specific tables that should not be installed against your soon-to-be-created MariaDB installation.

2 — Prepare a List of Database Accounts

When you build the new database, it will be important to ensure that all of your important user accounts exist. We can query the database to see what the user account names are and, more importantly, we can query the database to build the CREATE statements that we will need later in step 5 of this guide.

Log into your MySQL installation and run this query:

SELECT CONCAT('CREATE USER ''', `User`, '''@''', `Host`, ''' IDENTIFIED BY ''CorrectHorseBatteryStaple'';') as `username`
  FROM mysql.user
 WHERE `User` NOT LIKE 'mysql.%' and `User` NOT LIKE 'slave%'
   and `User` NOT IN ('root', 'debian-sys-maint')
 ORDER BY `username`;

This will output something like:

CREATE USER 'wordpress'@'localhost' IDENTIFIED BY 'CorrectHorseBatteryStaple';
CREATE USER 'drupal'@'localhost' IDENTIFIED BY 'CorrectHorseBatteryStaple';
CREATE USER 'admin'@'%' IDENTIFIED BY 'CorrectHorseBatteryStaple';

Save this list to a file somewhere, as you'll need it shortly.

3 — Completely Eliminate MySQL from the Server

If you are 100% sure that you have everything you need out of the MySQL database, now is the time to scrub it from the server.

  1. Ensure MySQL is not running:
    sudo systemctl stop mysql
  2. Completely Remove MySQL:
sudo apt purge mysql-server \ 
               mysql-client \ 
               mysql-common \ 
               mysql-server-core-* \ 
               mysql-client-core-*
  1. Eliminate the MySQL configuration, data, and log directories:
sudo rm -Rf /etc/mysql /var/lib/mysql /var/log/mysql
  1. Ensure everything is kosher in apt:
sudo apt autoremove
sudo apt autoclean

4 — Install MariaDB Server

Next comes the MariaDB installation. I won't step through all of the different configuration options that you might want to have installed, but will point out a couple that may make your life a bit easier. First, though, let's get it installed:

sudo apt install mariadb-server
sudo mysql_secure_installation

While MariaDB is installed on your server, most of the tools you'll be using will be aimed at MySQL. This is why the second command is mysql_secure_installation rather than something that is more appropriately named.

When you run mysql_secure_installation you'll be asked these questions:

  1. Enter current password for root (enter for none): ⇠ Hit the Enter key
  2. Set root password? [Y/n] ⇠ Choose Y
  3. Enter password ⇠ Enter a good one, then enter it again when asked to confirm.

Now, because you cannot sign in as root without sudo, you will likely want to create your account with all of the admin privileges that you may need.

  1. Log into MariaDB:
    sudo mysql -u root -p
  2. Enter the password you defined previously.
  3. Create your account:
    CREATE USER 'admin'@'localhost' IDENTIFIED BY 'CorrectHorseBatteryStaple';
    Note: Be sure to change admin to whatever you'd like your account to be named, and CorrectHorseBatteryStaple to a lesser-known password that you will remember.
  4. Grant all privileges with the ability to do ... everything:
    GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;
  5. Flush the privileges:
    FLUSH PRIVILEGES;Very Important

5 — Recreate the User accounts

Using the list you prepared earlier, recreate the various user accounts required by WordPress, Drupal, and (possibly) phpMyAdmin.

CREATE USER 'wordpress'@'localhost' IDENTIFIED BY 'CorrectHorseBatteryStaple';
CREATE USER 'drupal'@'localhost' IDENTIFIED BY 'CorrectHorseBatteryStaple';

Note: Be sure to set the user names and passwords to ones you actually want.

6 — Recreate your databases

For each database that you need, run this in MariaDB:

CREATE DATABASE wordpress CHARACTER SET = utf8 COLLATE = utf8_general_ci;

Of course, change wordpress to the actual name of the database that you would like to create.

7 — Import all databases into the new DBs

mysql -u {user} -p wordpress < wordpress-export.sql

Do this for each of the databases that was exported, replacing the database name and SQL file with the correct ones. If you make a mistake and import a file into the wrong database, don't worry, you can "fix" it like this:

  1. Connect to MariaDB:
    mysql -u admin -p
  2. Type in your password
  3. Drop the database you accidentally imported into:
    DROP DATABASE whoopsie;
  4. Recreate the database:
    CREATE DATABASE whoopsie CHARACTER SET = utf8 COLLATE = utf8_general_ci;
  5. Re-import the correct file.

Now we get to set the permissions for the accounts we created earlier:

GRANT ALL PRIVILEGES ON wordpress.* TO 'wordpress'@'localhost';

Again, do this for each account, making sure they have access to the correct databases. Once done, flush the privileges to ensure they're active:

FLUSH PRIVILEGES;

Now, at this stage, you can double-check that the database names, account names, and passwords for all of your websites are correct. If everything is good, restart your web server and give it a test.

More Important Notes:

One thing I will recommend, TEST THIS BEFOREHAND. I have seen a lot of MySQL⇢MariaDB migrations go sideways, then people call me in at crazy rates to fix the problems. Test, test, test. Some things in MySQL do not transparently work in MariaDB. You do not want to be mid-migration and discover that a table needs a collation change or an import file is broken.

If you have experience with Virtual Machines, these are ideal for testing migrations with.

6
  • You skipped instructions for removing MySQl and Installing MariaDB on Ubuntu 18.04/20.04.. Will gladly change my vote if you can add those instructions, which are the "meat" of my request.. But thx I do appreciate these tips. – yupthatguy Jan 18 at 10:13
  • @yupthatguy not sure how I missed those bits. Was probably due to a lack of coffee. "Definitive Guide" updated. Thanks for the nudge 👍🏻 – user1091774 Jan 18 at 13:21
  • excellent post.. give me a few hours test this and I change up the vote, need to runout for a bit. – yupthatguy Jan 18 at 13:31
  • I have the dbs exported and due to my naming routine, I know how to import my db's, without a reference list of names.. but I couldn't follow your section about generating a list of list of users. I was not able to follow your instructions to generate that list. You may want to "dumb-down" that section a little. If someone with less knowledge of mysql than me (if that is even possible) runs across this, they likely wouldn't be able to follow that section as well. As for the rest of your post, kudos. super clear. I thx you and your coffee supplier :-) – yupthatguy Jan 18 at 15:40
  • Updated. Section 2 is now all about exporting a list of user accounts as a complete CREATE USER statement to reduce the amount of work required when building the new database. – user1091774 Jan 18 at 15:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.