Centos 7 remove MariaDB and replace with MySQL

Author
Trevor
Created
Updated
Reading time
4 min
Views
781
Categories: centos

Why

A number of reasons, but the one that was the trigger for me was:

MariaDB does not have JSON data type

MySQL version 5.7 does have JSON data type.

System info

The server I tested this process on has the following details:

  • uname -a: Linux s14.iotau.io 3.10.0-862.3.3.el7.x86_64 #1 SMP Fri Jun 15 04:15:27 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
  • mysql –version: Ver 15.1 Distrib 5.5.56-MariaDB, for Linux (x86_64) using readline 5.1
  • postconf -d | grep mail_version: mail_version = 2.10.1 milter_macro_v = $mail_name $mail_version (why relevant will see later)
  • yum list installed | grep phpMyAdmin: phpMyAdmin.noarch 4.8.2-1.el7.remi @remi

Important

This process removes all traces of MariaDB including removes any databases and users. You must back them up before you start.

Postfix issue

I don’t believe it to be a big drama at least for me, however postfix is dependent on a single library that is part of the MariaDB installation. Removing MariaDB using yum will also remove postfix which this document works around. We just need to make a copy of /usr/lib64/libmysqlclient.so.18 and put it out of the way for now so we can put it back later.

The one issue I can see is postfix will no longer automatically update with yum update due to what it thinks is a missing dependency.

Perl-DBD-MySQL issue

I have not checked what perl-DBD-MySQL is dependent on within the MariaDB code. I don’t use Perl (at the moment).

Remove MariaDB

Have you backed up your databases and recorded user/password for each?

Have you made a copy of /usr/lib64/libmysqlclient.so.18?

Yes to both, your ready to start.

systemctl stop mariadb.service
systemctl disable mariadb.service
systemctl stop postfix

Check that you backed up your data. Good idea to restore the databases to a local mysql or mariadb server on your laptop.

NOTE: Next step removes for dependencies: perl-DBD-MySQL and postfix. Skip to alternate step is not ok:

yum remove mariadb.x86_64 mariadb-libs.x86_64 mariadb-server.x86_64

OR to keep dependencies for now

rpm -e --nodeps "mariadb-libs-5.5.56-2.el7.x86_64"
rpm -e --nodeps "mariadb-server-5.5.56-2.el7.x86_64"
# Expect to see "warning: /var/log/mariadb/mariadb.log saved as /var/log/mariadb/mariadb.log.rpmsave"
rpm -e --nodeps "mariadb-5.5.56-2.el7.x86_64"

and continue post package removal

rm -f /var/log/mariadb
rm -f /var/log/mariadb/mariadb.log.rpmsave
rm -rf /var/lib/mysql
rm -rf /usr/lib64/mysql
rm -rf /usr/share/mysql

Remove phpMyAdmin

yum remove phpMyAdmin.noarch

Install MySQL 5.7

Note, at time of writing, MySQL 8 still had issues with tools like phpMyAdmin and the change in it’s password mechanisms hence sticking to version 5.7 for now.

yum localinstall https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
yum install mysql-community-server

This will install the following packages:

  • mysql-community-server x86_64 5.7.23-1.el7 mysql57-community
  • mysql-community-client x86_64 5.7.23-1.el7 mysql57-community
  • mysql-community-common x86_64 5.7.23-1.el7 mysql57-community
  • mysql-community-libs  x86_64 5.7.23-1.el7 mysql57-community

Now start and enable the service:

systemctl start mysqld.service
systemctl enable mysqld.service

Get the temporary root password

grep 'A temporary password is generated for root@localhost' /var/log/mysqld.log |tail -1

The password is at the very end of the line:

2018-08-20T07:53:40.590219Z 1 [Note] A temporary password is generated for root@localhost: e88,Jtm&*n;>

Secure the server

mysql_secure_installation

Set a new root password – must be at least 8 chars, have a number, an upper case letter, a lower case letter and a special character eg _ or # or other.

After repeating the password, it will ask you if you want to change the password, say N because it’s already changed.

Then four y’s to continue (unless you want something different)

Now open /etc/my.cnf and at the bottom of the file add:

#validate-password=FORCE_PLUS_PERMANENT
#validate_password_length=10
#validate_password_mixed_case_count=1
#validate_password_number_count=1
#validate_password_policy=MEDIUM

The above are the default setting for password checking. To change any, un-comment and change the value. I don’t agree with the other method of removing the plugin all together.

Once you have configured your password checking, run:

systemctl restart mysqld.service

Install phpMyAdmin

rpm -Uvh https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
yum -y install phpmyadmin

Now configure phpMyAdmin so you can access it but also change the url to access it so it’s not attacked.

Open /etc/httpd/conf.d/phpMyAdmin.conf

At the top you will see:

Alias /phpMyAdmin /usr/share/phpMyAdmin
Alias /phpmyadmin /usr/share/phpMyAdmin

Change the two bolded phpMyAdmin’s to something different. As an example change to:

Alias /fredsqladmin /usr/share/phpMyAdmin
Alias /fredSqlAdmin /usr/share/phpMyAdmin

The url to access phpMyAdmin will be https://your.server.com/fredsqladmin or https://your.server.com/fredSqlAdmin

Next part is giving access from the internet. I have a fixed ip address so I allow my ip address only which means a person needs to know the address plus be on my ip address plus know the password. A good level of security. However if your ip address changes a lot or want to be able to access from multiple places you can change to allow all.

First option – fixed ip.

Google ‘what is my ip’ to get your ip address.

Two blocks identical:

<IfModule mod_authz_core.c>
  # Apache 2.4
  Require ip 127.0.0.1
  Require ip ::1
</IfModule>
<IfModule !mod_authz_core.c>
  # Apache 2.2
  Order Deny,Allow
  Deny from All
  Allow from 127.0.0.1
  Allow from ::1
</IfModule>

Add below the ‘Allow from ::1’:

Allow from 192.168.0.24

and ‘Require ip ::1’:

Require ip 192.168.0.24

Changing the ip address to your ip address. Note you can add multiple ip addresses on separate lines.

Second option – allow from any address:

Change the two blocks

<IfModule mod_authz_core.c>
# Apache 2.4
#Require ip 127.0.0.1
#Require ip ::1
Require all granted
</IfModule>
<IfModule !mod_authz_core.c>
# Apache 2.2
Order Deny,Allow
Deny from All
Allow from 127.0.0.1
Allow from ::1
</IfModule>

to

<IfModule mod_authz_core.c>
  # Apache 2.4
  #Require ip 127.0.0.1
  #Require ip ::1
  Require all granted
</IfModule>
<IfModule !mod_authz_core.c>
  # Apache 2.2
  Order Deny,Allow
  Deny from All
  #Allow from 127.0.0.1
  #Allow from ::1
  Allow from all
</IfModule>

Restart apache using

apachectl restart

Test phpMyAdmin by going to your phpMyAdmin URL.

Fix postfix.

Postfix’s is unhappy producing the following errors because it’s missing the libmysqlclient.so.18 library:

Aug 20 18:18:43 dovetail.iotsd.io aliasesdb[32408]: /usr/sbin/postconf: error while loading shared libraries: libmysqlclient.so.18: cannot open shared object file: No such file or directory
Aug 20 18:18:43 dovetail.iotsd.io aliasesdb[32408]: /usr/bin/newaliases: error while loading shared libraries: libmysqlclient.so.18: cannot open shared object file: No such file or directory
Aug 20 18:18:43 dovetail.iotsd.io postfix[32413]: /usr/sbin/postfix: error while loading shared libraries: libmysqlclient.so.18: cannot open shared object file: No such file or directory

It expects to find /usr/lib64/libmysqlclient.so.18 so copy it from where you saved your copy.

Restart postfix with:

systemctl restart postfix

As suggested earlier, postfix updates are likely to be problems with automatic yum updates so we tell yum to not update postfix by opening /etc/yum.conf and adding:

exclude=postfix* perl-DBD-MySQL*

I follow the instructions at https://www.netadmintools.com/art491.html to update my postfix.

You now have a CentOS 7 server with MySQL and postfix still works.

 

Related posts