Centos 7 remove MariaDB and replace with MySQL

    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.