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.
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
This process removes all traces of MariaDB including removes any databases and users. You must back them up before you start.
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.
I have not checked what perl-DBD-MySQL is dependent on within the MariaDB code. I don’t use Perl (at the moment).
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
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
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
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.
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>
<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
Test phpMyAdmin by going to your phpMyAdmin URL.
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: /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: /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: /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:
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.