How to set up a MySQL database server on Ubuntu for Drupal to WordPress migrations

Content Management System (CMS) migration projects involve moving data between databases with different schemas. Table names, field types and constraints often don’t match up, leading to a number of frustrating errors. This is especially the case with highly complex Drupal to WordPress migrations that use custom scripts to extract, transform and load the data. These projects can seem like you’re constantly hitting roadblocks throughout. I’ve found from experience that running the migration in an appropriate development environment can reduce a great deal of effort. In this guide, I will describe how to set up a MySQL database server on Ubuntu for Drupal to WordPress migrations.

Table of contents

Why MySQL and Ubuntu?

The first thing to address is why MySQL and Ubuntu? How about MariaDB? How about Arch Linux, Mac OS or Windows? Yes! Any platform that runs Drupal and WordPress will work for your migration environment so you can use whatever you prefer. Personally, I have a fondness for OpenBSD but it’s not a practical platform for a CMS migration. OpenBSD’s niche user-base means you’ll spend much longer installing necessary tools and troubleshooting errors.

There are all sorts of tutorials covering MySQL on Ubuntu. This means you’re more likely to quickly find a solution from a web search when you hit a problem. Furthermore, if you need a software utility or program to help you get the job done, it will probably be available through apt, dpkg, snap or tasksel. Use whatever you like but for now, MySQL on Ubuntu is my recommended platform for Drupal to WordPress migrations. I expect this will be the case for some time to come. These projects are complex and time-consuming enough without making the job more difficult.

Installing MySQL on Ubuntu for CMS migrations

There are many detailed tutorials for installing MySQL on Ubuntu. DigitalOcean’s How To Install MySQL on Ubuntu 20.04 is a good one and writing another won’t add much value. My guide will therefore only give a brief overview of the MySQL server installation steps. Instead, I will focus on the configuration areas specifically related avoiding problems on a CMS migration project.

You may wonder why the migration environment should be much different from a live server. Migration projects require you to do things that aren’t supported by the CMS platform. You’re therefore likely to encounter weird errors that aren’t normally found when running standard Drupal or WordPress.

Set up your Ubuntu LAMP migration platform

The main source of unusual errors is almost certainly because you’re migrating on a setup suited to a live website. Live server configurations are more restrictive than you need for a migration project. You can therefore save yourself a huge headache by rolling your own local migration environment. It might take a little longer to get started but you’ll save time by avoiding lots of unnecessary troubleshooting.

I must highlight that this will be a local migration environment and should not be accessible from the public internet. The normal security considerations with running a live content management system don’t apply when you’re working locally. By all means follow basic security measures mentioned the various tutorials for setting up Ubuntu and MySQL. Nevertheless, a highly secure setup is counterproductive for these projects and you can avoid trouble by being a little more permissive.

Go ahead and install Ubuntu Desktop. Since this will be a development environment, you’ll want the Desktop environment rather than the more lightweight server version. Of course, you’ll still need to install a web and database server. Follow these instructions for installing LAMP stack but skip the step of installing MariaDB. As mentioned above, we’ll be using MySQL.

WARNING: Installing MariaDB over MySQL or vice versa on Ubuntu 20.04 may lead to all sorts of problems starting up the database server with errors like the following:

Failed to start mysqld.service: Unit mysqld.service not found.

The last time I did this, none of solutions mentioned online for purging the installation worked. I spent most of a day trying to fix the problem. In the end, I realised it was quicker to start again and rebuild the machine from scratch. This is a big reason why I decided to stick with MySQL as standard for my projects.

Install MySQL

You can read a more detailed tutorial on installing MySQL on Ubuntu but here’s an overview.

  1. Update the package index on your server: sudo apt update
  2. Install MySQL server: sudo apt install mysql-server
  3. Secure MySQL: sudo /usr/bin/mysql_secure_installation

The mysql_secure_installation script doesn’t cause problems for migrations so it’s worth running.

Create an admin user:

CREATE USER 'user'@'localhost' IDENTIFIED BY 'userpassword';
GRANT ALL PRIVILEGES ON *.* to 'user'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
quit;

Now create a user and database for your migration project:

CREATE USER 'projectuser'@'localhost' IDENTIFIED BY 'password';

CREATE DATABASE project_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
GRANT ALL ON project_db.* TO 'projectuser'@'localhost';

You can give this user more restrictive permissions but GRANT ALL avoids errors when running scripts and SQL queries on the database. Only the migration team should have access and you won’t need it after the migration so why not make your life easier?

Setting the MySQL server SQL mode

It’s possible that you won’t be familiar with SQL modes unless you’ve done some database administration work. For our purposes, SQL modes do two things:

  • change the types of queries you can run on your MySQL server;
  • change the validation checks when altering the data.

I’ve found that specific SQL modes need to be set for Drupal to WordPress projects. You may find that all sorts of strange errors appear if the correct modes aren’t set. The following sections show you two ways to set your MySQL server’s SQL mode.

Option 1: Setting the global sql_mode in the database

  1. Login in to database as an admin user.
  2. View the current sql-modes using SELECT @@GLOBAL.sql_mode; and make a copy if necessary.
  3. Copy the current modes (add or delete modes as needed) and paste in next step.
  4. Add ALLOW_INVALID_DATES and removes both NO_ZERO_DATE, NO_ZERO_IN_DATE by setting the sql-modes with
    SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; (WARNING: check the modes correspond with your setup.)
  5. Restart server:
    sudo systemctl start mysql

Option 2: setting the sql_mode in the MySQL configuration file

Locating the MySQL configuration file

The my.cnf configuration file isn’t always found in the same place. It’s specific to the Linux distribution and server configuration but can normally found in one of the following locations:

/etc/my.cnf
/etc/mysql/my.cnf
echo/my.cnf
[datadir]/my.cnf
~/.my.cnf

If you can’t find your MySQL configuration file, you can try running locate my.cnf or mysqladmin --help. The latter will show something like the following in the output:

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

Also keep in mind that it’s possible to use !include directives to include other option files and !includedir to search specific directories for option files. Under Ubuntu, there may be a file /etc/mysql/my.cnf with !includedir directives to search /etc/mysql/conf.d/ and /etc/mysql/mysql.conf.d/

Editing the MySQL configuration file

If the MySQL server finds more than one configuration file, it will load each one in turn. The values override each other and it can be difficult to know which takes priority. Furthermore, the –defaults-file parameter can also override all configurations. Keep things simple and have only one file and place it the directory that makes sense to you.

Before editing the my.cnf, first log in to MySQL with an administrator user and run the SELECT @@GLOBAL.sql_mode query to see the values used in your setup.

Setting the MySQL database server sql mode on Ubuntu for Drupal to WordPress migrations
Running the SELECT @@GLOBAL.sql_mode query on MySQL Workbench

Next, open the configuration file, look for the section [mysqld] and edit the line starting with:
sql_mode = ...

Add the line if it’s not there. Adjust the exact modes to match your project’s needs so take a look at the list of SQL modes to see which may apply. I’ve found the following works well:
sql_mode = "STRICT_TRANS_TABLES,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

Finally, restart the MySQL server. On Ubuntu this will probably be with

sudo systemctl start mysql

If you are logged in to the MySQL server, you may also need to disconnect your client and reconnect for the changes to take effect for your session.

Potential errors

Here are some potential errors that you may come across during a CMS migration project. I usually find them when running a Drupal to WordPress migration on a freshly built development environment.

mysqldump access denied when trying to dump tablespaces

Migrations involve dumping and importing databases and this process is straightforward on a mature development environment. However you may receive an ‘Access denied’ error out of the blue when dumping your MySQL database:

mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

If you see this, perhaps you were working on an environment that was a little too ‘mature’ (in other words, obsolete!) and you have recently upgraded your installation. The updates for MySQL 5.7.31 and MySQL 8.0.21 in July 2020 introduced an incompatible change that produces this error.

Read my separate article, How to fix the mysqldump access denied process privilege error, for more information and instructions on how you can try solving this.

ERROR 1067 (42000) Invalid default value

Drupal nodes store the date as a Unix timestamp in an int (e.g. 1623427200) field whereas WordPress stores dates as datetime (e.g. 2021-06-11 16:00:00). There may be a conversion error in your migration script or the source date could simply be zero for some reason. Normally your MySQL server mode will be set to NO_ZERO_DATE, NO_ZERO_IN_DATE so trying to insert a zero date will give you the error:

ERROR 1067 (42000) Invalid default value

You can fix this by replacing NO_ZERO_DATE, NO_ZERO_IN_DATE with set to ALLOW_INVALID_DATES in your global SQL mode.

Expression #1 of SELECT list is not in GROUP BY clause

You run an SQL query and get the rather cryptic error:

Expression #1 of SELECT list is not in GROUP BY clause and contains
nonaggregated column 'database.table.pid' which is not functionally dependent
on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

What’s wrong? MySQL has a only_full_group_by mode which, when enabled, strictly applies ANSI SQL rules when using GROUP BY. Fix this by reworking your script or removing the ONLY_FULL_GROUP_BY SQL mode.

Error Code: 2013. Lost connection to MySQL server

This often happens when it an SQL query takes too long to return data. The connection between your MySQL client and database server times out so the connection gets dropped. For ideas on how to try solving this, read my separate article, How to fix Error Code 2013 Lost connection to MySQL server.

Conclusion

Setting up a MySQL database server for Drupal or WordPress is a familiar task for web developers and site administrators. However, CMS migrations have quirks that can cause obscure and baffling errors. In this guide I’ve shown you some little tricks that may save time and annoyance. While there’s no way to provide an exhaustive list of solutions to all the MySQL problems you’ll encounter, I hope to have pointed you in the right direction.

If you have a site migration project and would like to hire me, please ask for a quote for my consulting service.

CMS migration consulting

All content · Custom content types · SEO · Plugins

Migrating content from a site and need a specialist? Please contact me for a quotation. Whether you’re a media agency who needs a database expert or a site owner looking for advice, I’ll save you time and ensure accurate content exports.

Get a quote

How to fix the mysqldump access denied process privilege error

How to fix the mysqldump process privilege error after applying a recent MySQL update.

You may receive a new ‘Access denied’ error when trying to dump your MySQL database:

mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

You were able to export database before so what caused this? Here’s the answer: updates for MySQL 5.7.31 and MySQL 8.0.21 in July 2020 introduced an incompatible change:

Incompatible Change: Access to the INFORMATION_SCHEMA.FILES table now requires the PROCESS privilege.

This change affects users of the mysqldump command, which accesses tablespace information in the FILES table, and thus now requires the PROCESS privilege as well. Users who do not need to dump tablespace information can work around this requirement by invoking mysqldump with the --no-tablespaces option. (Bug #30350829)

This error appears when running mysqldump directly from the command line, exporting the database using a client like MySQL Workbench or if you’re managing the WordPress database through WP-CLI’s export command.

mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

In my case, I encountered the problem when running a routine Python script for a Drupal to WordPress migration client. My script uses WP-CLI to export a database dump file and deploy it to a remote server.

Solutions for fixing the mysqldump process privilege error

The mysqldump command requires at least the following privilege assigned to the user:

  • SELECT privilege for dumped tables
  • SHOW VIEW for dumped views
  • TRIGGER for dumped triggers
  • LOCK TABLES if you don’t use the --single-transaction option
  • PROCESS if you don’t use the --no-tablespaces option

The last PROCESS privilege is new as of MySQL 5.7.31 and MySQL 8.0.21 and may be the root source of your problem. You can solve the mysqldump process privilege error in two ways:

  1. Updating the privileges for your database user.
  2. Runing mysqldump with the --no-tablespaces option.

Solution 1: Update the user privileges

Granting the PROCESS privilege for the user is perhaps the simplest option for fixing the mysqldump process privilege error. Keep in mind that this option presents security issues. You should therefore really only use this option for your own local development server installation.

To grant the PROCESS privilege, log in as an administrator user and run the following query:

GRANT PROCESS ON *.* TO [email protected];

Note that PROCESS is a global level privilege. It can’t apply to individual databases. Global privileges are either administrative or apply to all databases on your MySQL server. Trying to grant them on individual databases deplays the following error:

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

To grant the privilege to all databases you must use the ON *.* ... syntax.

Solution 2: Use the --no-tablespaces option

If you cannot assign global level privileges to your user, for example, when doing so presents unacceptable security issues, you must specify the --no-tablespaces option when dumping your database.

mysqldump --no-tablespaces -u user -ppass dbname > db_backup_file.sql

What are MySQL tablespaces?

We are usually only concerned with logical database objects when working with databases. However, the data must be physically stored somewhere. This is where tablespaces come in. Tablespaces are physical datafiles stored in the host file system holding data for one or more tables and indexes.

The diagram below provides a handy illustration. It’s from the Oracle Concepts documentation, Introduction to Tablespaces, Datafiles, and Control Files and refers to Oracle databases. Nevertheless, it may help you understand how tablespaces relate to logical database objects and datafiles.

Oracle documentation: Introduction to Tablespaces, Datafiles, and Control Files
Diagram source: Oracle Concepts documentation

You can therefore use the --no-tablespaces option if you don’t need to dump tablespace information. This may be the case for routine database dumps, for example when exporting databases for WordPress migrations.

About the access mysqldump denied PROCESS privilege error

mysqldump accesses tablespace information in the FILES table. Prior to MySQL 5.7.31 and 8.0.21, your user could run mysqldump without the PROCESS privilege. However, users running mysqldump after the update need PROCESS privileges to access the INFORMATION_SCHEMA.FILES table. Running mysqldump without PROCESS privilege ends up giving you an Access denied error.

Be careful with the PROCESS privilege

According to the MySQL documentation, the PROCESS privilege controls access to information about statements being executed by sessions.

It is a server administration privilege and should not be given to all users. This is because it may show text from currently executing queries. Any user with the PROCESS privilege may therefore see queries issued by others. Here’s the danger: these queries, such as UPDATE user SET password=PASSWORD, may show secrets.

For more information, see General Security Issues and the MySQL Access Privilege System from the O’Reilly MySQL Reference Manual.

How to fix https version of a site redirecting to the wrong domain

Some hosting providers restrict customers to a single SSL/TLS certificate per socket. (In simple terms, a socket is the combination of IP address and port number.) Since Apache listens to port 80 for non-SSL connections and port 443 for SSL connections on the same IP address, customers usually need a separate IP address for each certificate.

At the same time, you can configure Apache for multiple domains to share a single IP address using virtual hosts. Each virtual host gets its own port and Apache listens to this port, redirecting connections to the appropriate domain.

The combination of the above behaviours can sometimes cause complications when you install a single SSL Certificate on a shared IP address. Secure connections to port 443 of an IP address will be directed to the virtual host and domain assigned to that port. Thus, if you try to make a secure connection to a domain on a shared IP address, Apache will create a socket to the actual domain listening to port 443. Depending on your configuration, this domain may be a default virtual host or one that is explicitly set to listen to port 443.

The possible solutions depend on the types of configurations supported by your hosting provider. These include:

  1. Moving each domain with SSL certificates to its own IP address.
  2. Use Server Name Indication (SNI) to define separate SSL virtual hosts.
  3. Creating a default virtual host in your SSL file that does nothing but redirect to non-SSL connection.
  4. Installing a self-signed certificate on each domain name on that IP address.
  5. Making a different SSL host the primary certificate for the IP address.

Resources

How To Configure SSH Key-Based Authentication on FreeBSD for Mac OS X and Panic’s Transmit FTP Client

This is a guide for setting up an Apple Mac OS X workstation with SSH key-based authentication to a remote FreeBSD server. I won’t go into any detail about these protocols or try to make a case for using them. If you’re reading this, you probably already have a basic grounding on SSH, SFTP and the implications of SSH key-based authentication. My goal is to outline the steps needed so you can start using key-based authentication on your Mac.

I. Intended audience

The instructions here are aimed at Mac OS X based web developers with at least a moderate level of systems administration knowledge. Most likely you host websites for your clients or employer on *nix servers controlled through a command line interface. You generally work on multiple servers per project. Repeatedly entering secure long random passwords is becoming a hassle. If this sounds familiar, this guide is for you.

I’m on a Mac OS X workstation, currently Yosemite, using the Mac Terminal App to connect via SSH and Panic’s Transmit FTP client to transfer files. The server instructions here are for FreeBSD but you should still find the information useful if you run a Linux-based web server. I expect that you’re familiar with your own environment and have a preferred way of doing things so won’t list every command needed.

The prerequisites for this guide are that you:

  1. already have your server set up to SSH and SFTP with password authentication;
  2. have an account on the server that you use for day-to-day web development;
  3. also have root access to the server.

II. Setting up your Mac workstation and server for SSH Key-Based Authentication

We’ll need to do two main things to get everything working:

  1. Set up your server to accept the key files instead of a password.
  2. Configure your Mac OS X workstation to use SSH key files.

We’ll be moving back-and-forth between the them so for clarity, open up two terminal windows: one will be used for configuring the server, which I’ll refer to as your server terminal and another for configuring your Mac workstation, which I’ll call the Mac terminal.

A. Configure the server

First make sure your server is configured to accept key-based authentication. On the server terminal, open a secure shell to the server as you normally would. Edit the configuration file for your OpenSSH Daemon at /etc/ssh/sshd_config.

Look for the following lines and uncomment or add them:

RSAAuthentication yes
PubkeyAuthentication yes
AuthorizedKeysFile .ssh/authorized_keys
ChallengeResponseAuthentication no

You may need to return to this file later but for now, save it and restart sshd. On FreeBSD, it’s the command:

# /etc/rc.d/sshd reload

B. Generate the key files on your Mac

Now you need to generate your private and public SSH key files. The private key file will remain on your Mac and you’ll place a copy of the public key file on the server. To generate the key files, switch to your Mac terminal and run:

$ ssh-keygen -t rsa

The -t flag specifies that you’re creating an RSA key.

You’ll be asked to enter a file name for the key. The default for key files on Mac OS X is /Users/username/.ssh/id_rsa.pub. While you can simply select the default, it might be a good idea to create a key file specifically for each project. That way, if your Mac workstation is ever compromised, you minimise the risk of access to servers used for past projects where your account has been inadvertently left active.

ssh-keygen will also ask if you want to set a password for the key. If you set a password, you’ll have to enter it every time you try to authenticate with the key file. 1

The result should be two new key files in your .ssh directory:

  • The public key: /Users/username/.ssh/yourkeyfile_rsa.pub
  • The private key: /Users/username/.ssh/yourkeyfile_rsa

If you use a service like GitHub you may also see your GitHub key files already in that directory.

C. Copy the public key to the server

Transfer your public key to the .ssh directory of the server account you use to do your day-to-day web development. There are two ways to do this on a standard-install Mac OS X workstation. You can follow these steps:

  1. Go to the server terminal and cd into the account’s home directory.
  2. Create a .ssh directory (~/.ssh/).
  3. Switch back to the Mac’s terminal and copy the public key text using the command:
    $ pbcopy < ~/.ssh/yourkeyfile_rsa.pub
  4. Move back to the server terminal and create an authorized_keys file within the .ssh directory.
  5. Paste the public key text into your authorized_keys file. (Remember that the pbpaste command won’t work on a non-Mac operating system.)

Alternatively, you can run the following command on your Mac’s terminal:

cat ~/.ssh/yourkeyfile_rsa.pub | ssh [email protected]_host “mkdir -p ~/.ssh && cat >> ~/.ssh/authorized_keys”

However it’s done, you should end up with a file ~/.ssh/authorized_keys for the developer account on the server.

It’s worth making this clear so there’s no confusion: you are giving the public key, yourkeyfile_rsa.pub, to the developer account that you’d previously been using to connect using a password. You should not be giving it to the root account. Make sure you put the authorized_keys file in the developer account’s home folder. Also make sure you don’t accidentally copy and paste the private key, yourkeyfile_rsa.

D. Test authentication to the remote server over SSH

Make sure everything worked by checking if you can connect to the server without having to supply a password. Log out of your existing connection on the server terminal and try connecting again:

ssh -i ~/.ssh/yourkeyfile_rsa [email protected]

The -i flag should be the path to the private key file we generated in step B. Also, if you set a key password, you will be asked to supply it before you can access the key.

You should see your server motd and terminal prompt after pressing enter.

E. Optional: disable password authentication

Since you’ve gone to the trouble of setting up SSH key-based authentication, you may want to disable password authentication after you’ve successfully testing the connection. Uncomment or add the following lines in /etc/ssh/sshd_config:

PasswordAuthentication no

Finally, restart sshd:

# /etc/rc.d/sshd reload

Make absolutely sure that everything works before disabling password authentication, otherwise you may find yourself without any way to remotely connect to your server.2 Unscheduled trips to the data centre are no fun. (Can you tell I’m writing from experience?)

F. Optional: use a ssh config file

An option to save yourself some typing is to put the server and key details into an ssh config file. On your Mac terminal, create a config file at /Users/username/.ssh/config with the following:

Host serveralias
    HostName host
    User your_username
    PubKeyAuthentication yes
    IdentityFile ~/.ssh/yourkeyfile_rsa

This will let you login just by typing:

ssh serveralias

III. Setting up the Transmit FTP client for SSH Key-Based Authentication

After configuring SSH key-based authentication on your server, the SFTP service will start rejecting your login attempts unless you supply your key. Setting up Panic’s Transmit FTP client with key-based authentication is simple but it can be a little buggy.

  1. In Transmit, delete your old connection details and add new connection settings 3
  2. Leave the password blank
  3. Either:
    • From the menu, select Favourites > Import SSH Key File
    • Or click the key picker, which is the key icon to the right of the password box
  4. Import your private key file yourkeyfile_rsa

Connect with these new settings and the SFTP server will grant you access.

Panic Transmit FTP client ssh key picker
Panic Transmit FTP client ssh key picker

If you try this process with a password-encrypted key, Transmit will give you the error: “The file is not in a supported format.” This is a bug and you’ll have to apply a workaround. Panic’s customer support sent me the following instructions:

If the private key is passphrase encrypted (as yours is), or if it lives in a directory other than ~/.ssh/, there are a few extra steps needed to get up and running.

Instead of using the key picker, edit your ~/.ssh/config file and add the following:

Host yourserver.com
IdentityFile /Users/yourusername/.ssh/id_rsa

Then set the site’s Password field to your key’s passphrase and try connecting.

Alternatively, you can try this method:

  1. First, add the keyfile and password to your keychain:
    ssh-add -K path/to/.ssh/yourkeyfile_rsa
  2. Now add the connection details to Transmit but do not enter the password or set a key file.
  3. Transmit will connect using the credentials in you keychain

In addition to being able to conveniently log in to your various servers, you will now also be able to use Transmit for your regular web development file transfers.

IV. Notes

  1. It’s tempting to think that setting a password for your key negates the whole point of this exercise. After all, if you have to enter a password to authenticate with the key file, you might as well keep the existing model of password authentication to the server, right? Not quite and here’s why: the ssh key is a cryptographically secure way of authenticating to your server and is exactly what you want for something accessible to the open internet. While manually typing in the key itself every time you want to open a connection isn’t practical, SSH key-based authentication handles key exchange automatically.

    The key password protects against a different threat. It prevents unauthorised use of the key itself. This has a lower threat profile because it’s stored on your own private workstation. It therefore can be something that’s easier to remember and type.

  2. Remember to backup your keys and store the backups in a secure location. If you lose your keys and have also disabled password authentication, you won’t be able to access the server without physical access or help from your hosting technical support.

  3. I’ve found that editing a saved connection sometimes doesn’t work. This might be a bug in Transmit. Deleting the old connection and creating a new one seems to always work.