MySQL migration tips

Introduction

So, i decided to migrate the MySQL database in my homelab to a dedicated server. Yes, it adds one more OS for maintenance, but it simplifies other things for me (startup dependencies, hardware resources distribution...).

In theory, MySQL migration is a simple process:

Install and start the new database.
Stop the applications.
Backup the database.
Restore the backup to the new database.
Reconfigure the applications to use the new database and enjoy ;-)

Most of the steps went without a problem, except for some small issues that i want do elaborate. 🔝

Post-installation scripts failed

So... I spun-up a new VM, installed Ubuntu 22.04, installed the latest updates, webmin and did some basic housekeeping. Added a new disk, did the LVM stuff, created a filesystem and mounted it at /var/lib/mysql, to serve as a datafile location. Next step... install.

root@unidb:~# apt install mysql-server
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following packages were automatically installed and are no longer required:
  libflashrom1 libftdi1-2
Use 'apt autoremove' to remove them.
The following additional packages will be installed:
  libcgi-fast-perl libcgi-pm-perl libclone-perl libencode-locale-perl libevent-pthreads-2.1-7 libfcgi-bin
  libfcgi-perl libfcgi0ldbl libhtml-parser-perl libhtml-tagset-perl libhtml-template-perl libhttp-date-perl
  libhttp-message-perl libio-html-perl liblwp-mediatypes-perl libmecab2 libprotobuf-lite23 libtimedate-perl
  liburi-perl mecab-ipadic mecab-ipadic-utf8 mecab-utils mysql-client-8.0 mysql-client-core-8.0 mysql-common
  mysql-server-8.0 mysql-server-core-8.0
Suggested packages:
  libdata-dump-perl libipc-sharedcache-perl libbusiness-isbn-perl libwww-perl mailx tinyca
The following NEW packages will be installed:
  libcgi-fast-perl libcgi-pm-perl libclone-perl libencode-locale-perl libevent-pthreads-2.1-7 libfcgi-bin
  libfcgi-perl libfcgi0ldbl libhtml-parser-perl libhtml-tagset-perl libhtml-template-perl libhttp-date-perl
  libhttp-message-perl libio-html-perl liblwp-mediatypes-perl libmecab2 libprotobuf-lite23 libtimedate-perl
  liburi-perl mecab-ipadic mecab-ipadic-utf8 mecab-utils mysql-client-8.0 mysql-client-core-8.0 mysql-common
  mysql-server mysql-server-8.0 mysql-server-core-8.0
0 upgraded, 28 newly installed, 0 to remove and 0 not upgraded.
Need to get 29.6 MB of archives.
After this operation, 243 MB of additional disk space will be used.
Do you want to continue? [Y/n]
Get:1 http://mk.archive.ubuntu.com/ubuntu jammy/main amd64 mysql-common all 5.8+1.0.8 [7,212 B]

... Standard apt installation output ...

update-alternatives: using /etc/mysql/mysql.cnf to provide /etc/mysql/my.cnf (my.cnf) in auto mode
Renaming removed key_buffer and myisam-recover options (if present)
Specified filename /var/lib/mysql/ibdata1 does not exist.
mysqld will log errors to /var/log/mysql/error.log
2023-10-22T23:46:40.890693Z 0 [ERROR] [MY-010946] [Server] Failed to start mysqld daemon. Check mysqld error log.
Warning: Unable to start the server.
Created symlink /etc/systemd/system/multi-user.target.wants/mysql.service → /lib/systemd/system/mysql.service.
Job for mysql.service failed because the control process exited with error code.
See "systemctl status mysql.service" and "journalctl -xeu mysql.service" for details.
invoke-rc.d: initscript mysql, action "start" failed.
● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: activating (auto-restart) (Result: exit-code) since Sun 2023-10-22 23:46:42 UTC; 6ms ago
    Process: 2223 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=1/FAILURE)
        CPU: 12ms

Oct 22 23:46:42 unidb systemd[1]: mysql.service: Control process exited, code=exited, status=1/FAILURE
Oct 22 23:46:42 unidb systemd[1]: mysql.service: Failed with result 'exit-code'.
Oct 22 23:46:42 unidb systemd[1]: Failed to start MySQL Community Server.
dpkg: error processing package mysql-server-8.0 (--configure):
 installed mysql-server-8.0 package post-installation script subprocess returned error exit status 1
Setting up libcgi-pm-perl (4.54-1) ...
Setting up libhtml-template-perl (2.97-1.1) ...
dpkg: dependency problems prevent configuration of mysql-server:
 mysql-server depends on mysql-server-8.0; however:
  Package mysql-server-8.0 is not configured yet.

dpkg: error processing package mysql-server (--configure):
 dependency problems - leaving unconfigured
Setting up libcgi-fast-perl (1:2.15-1) ...
Processing triggers for man-db (2.10.2-1) ...
No apport report written because the error message indicates its a followup error from a previous failure.
                                                                                                          Processing triggers for libc-bin (2.35-0ubuntu3.4) ...
Errors were encountered while processing:
 mysql-server-8.0
 mysql-server
needrestart is being skipped since dpkg has failed
E: Sub-process /usr/bin/dpkg returned an error code (1)
root@unidb:~#

WTF??? MySQL did install, but initial configuration failed. The problem was that, during initial configuration, MySQL requires that the datafile directory is empty. If it is not, the process will fail. Remember how i said before that i wanted the datafile directory to be on a different filesystem? Well, mkfs, when creating a filesystem, creates one lost+found directory that is used by fsck. A solution to the problem would be to completely empty /var/lib/mysql (deleting lost+found and all the leftovers from the unsuccessful initial configuration) or not to use the root of the filesystem for MySQL datafiles. Once this has been sorted out, re-started the initial configuration, and the new database was up and running.

root@unidb:/var/lib/mysql# dpkg --configure -a
Setting up mysql-server-8.0 (8.0.34-0ubuntu0.22.04.1) ...
Renaming removed key_buffer and myisam-recover options (if present)
mysqld will log errors to /var/log/mysql/error.log
mysqld is running as pid 2460
Setting up mysql-server (8.0.34-0ubuntu0.22.04.1) ...
root@unidb:/var/lib/mysql#

From here, i continued to migrate the data using backups and restores. 🔝

Some tips for easier migration

Once the database was up and running, the rest of the migration process went OK.

Tip #1: Be sure to backup and restore the database named mysql. This is where the users and privileges are. If you opt not to do this, the users and grants need to be created manually.

Tip #2: By default, mysqld only listens to localhost. Edit the /etc/mysql/mysql.conf.d/mysqld.cnf configuration file and make sure mysqld listens to other IPs, as well. Alternatively, you can bind it to 0.0.0.0 to make it listen to all IPs and interfaces. Be sure to restart the service after the change and update the local firewall rules, if needed.

Tip #3: If you had an application on the old DB server accessing the database locally, be sure to add remote access privileges to the user. Fire up your favourite sql tool and run the following queries:

update db set Host='X.X.X.X' where Db='yourDB';
update user set Host='X.X.X.X' where user='youruser';

You should replace 'X.X.X.X' with the IP of your application server and 'yourDB' and 'youruser' with the database and the user in question. Alternatively, you can replace the Ip with '%' to allow access from all IPs.

Personally, i use Webmin on all of my servers and used the MySQL server administration module to make the change. Whatever floats your boat.

Tip #4: Don't forget to change the DB connection parameter in the applications to use the new IP (or hostname) of the database.

🔝

Debian/Ubuntu system tools would not run

Now that the database us up, data migrated, and applications were all running against the new database, it was time to setup some regular backups. I used automysqlbackup in the past, so i intended to use it here, as well. It installs via apt on Ubuntu and is easy to configure. Installed it, copied the configuration from the old server and i was ready to test the process...

root@unidb:~# automysqlbackup
ERROR 1045 (28000): Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)
root@unidb:~#

... and the test failed. :-(

It didn't behave like this when i configured it on the original server, so what's the matter now? Well... is has to do with the way Debian based Linux distributions, like Ubuntu, are organized in the context of MySQL tools, including automysqlbackup. That and my "Tip #1" above ;-)

Apparently, there is a file named /etc/mysql/debian.cnf, that contains some credentials to be used by these tools, and these credentials are provisioned during the initial system configuration. Now, remember the "Tip #1" above. We migrated the mysql database with all the users and privileges, including one debian-sys-maint user, used by automysqlbackup. As the credentials for this user are unique to each installation, the ones in the configuration file did not match the ones in the database. To fix this, just fire up your favourite SQL utility and run:

alter user 'debian-sys-maint'@'localhost' identified by 'the_password_from_debian.cnf';

... where you should replace 'the_password_from_debian.cnf' with whatever is in the password field in /etc/mysql/debian.cnf (in single quotes).

That's all for now. This post might get updated if i stumble upon more migration related issues in the future. 🔝