Need procedure for installing MySQL instead of MariaDB

Help! (Home for newbies)
1

Operating system: CentOS 8
OS version: Linux 4.18.0-240.22.1.el8_3.x86_64 #1 SMP Thu Apr 8 19:01:30 UTC 2021 x86_64 x86_64 x86_64 GNU/Linux

Is there a guide or an option that I can use to have MySQL installed instead of MariaDB?
I’ve spent so many hours getting this to work but didn’t fully succeed yet. Here’s my procedure up till now:

  • Install Virtualmin using the install.sh script
  • Remove MariaDB:
    yum remove mariadb* -y
  • Disable the mysql repos built in CentOS 8, otherwise they conflict with the installation:
    yum module disable mysql -y
  • Clear the mysql data directory at /var/lib/mysql, otherwise the daemon won’t start, giving error that it is still upgrading existing databases:
    rm -rf /var/lib/mysql/*
  • Set up mysql’s repo:
    yum install https://dev.mysql.com/get/mysql80-community-release-el8-1.noarch.rpm -y
  • Install mysql:
    yum install mysql-community-server -y
  • Start the server:
    service mysqld start
  • Secure the new installation:
    mysql_secure_installation

Now the problem at the last step is that the root password is not getting accepted:

[root@myserver ~]# mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root:
Error: Access denied for user 'root'@'localhost' (using password: YES)

[root@myserver ~]#

Using Virtualmin’s post-installation setup wizard also fails saying Warning! Incorrect MySQL password at the step of setting up a MySQL root password.

What am I doing wrong here?
Is there an easier way to have Oracle’s MySQL instead of MariaDB?

2

@vending_makina one could say this could be biased: Why MariaDB? Advantages over MySQL | MariaDB.

however if you look at bits how it works - its right…

here you can read why linux kindly shifting away from mysql: https://linuxnatives.net/2015/10-reasons-to-migrate-to-mariadb-if-still-using-mysql its an older article but still very acurate.

…lastly because its so easy and very much lightwell and more secure most of us devops dropping old documentations and does not support kindly old ways, times changed as we all did envolved. Easier way would be use mariadb if this not good way for you - perhaps you should ask on oracles devs how to replace mariadb or perhaps try ask centos devs which would be kindly step backwards like downgrade for anyone - I wish you good luck with this. Stay safe and fast, use mariadb :wink:

3

Thank you, @unborn.

I’m not against MariaDB, but these are two different (but similar) products, and my client asked specifically for MySQL because they’re using functionality that depends on it.

I’m still looking for how to resolve this apparently simple, but stubborn issue.

4

@vending_makina it should work same for you independent how did you wrote your app - it should be not noticeable by app itself that you are using maria or sql… at least on my end it does work like that.

Edit: well I am very sorry, but for me its no go back as maria provide much faster speed regards dbs upto 7GB or bigger… speed and security is very noticeable at least for me. Perhaps someone else here would be able to help you here.

5

Not sure if this helps, But webmin/virtualmin from your admin panel click on Webmin tab, scroll down to servers click it you see [ MySQL Database Server] click on it and it show you a panel of options etc.

6

To save head-scratching and hair-pulling of anyone intending to do this, here’s how it goes:

According to MySQL :: MySQL 8.0 Reference Manual :: 2.10.4 Securing the Initial MySQL Account :
Installation using RPM packages generates an initial random password, which is written to the server error log.
But no, that’s not correct. In the log the password is not output, and instead there are messages that encourage to run a command to change the root password, which does not run, of course, because the root password is not known… Ugh!

The solution is to reset the root password right after the installation by performing the following dance (as detailed at MySQL :: MySQL 8.0 Reference Manual :: B.3.3.2 How to Reset the Root Password):

(as mentioned in the OP, remove MariaDB and disable the mysql builtin repo)

yum install mysql-community-server -y
virtualmin-config-system -i MySQL
service mysqld start
sleep 3
service mysqld stop

echo "ALTER USER 'root'@'localhost' IDENTIFIED BY 'MY-ROOT-PASSWORD';">/tmp/sql-init
chmod 777 /tmp/sql-init

mysqld --user=mysql --init-file=/tmp/sql-init &
kill `cat /var/run/mysqld/mysqld.pid`

rm /tmp/sql-init
service mysqld start
ln -s /etc/systemd/system/multi-user.target.wants/mysqld.service /etc/systemd/system/mariadb.service
mysql_secure_installation

Answer: n, y, y, y, y

Now the root’s password must be set in the Webmin MySQL module.

Warning: The password gets stored in the history file if you’re working with bash, so logout then login again and execute this:
rm -f /root/.bash_history

7

I would like that but still too many unnecessary steps to just set this up eh no one know how many when issue would rise, - which will :smiley: congrats for mysql ancient 26 years code mostly unchanged from 1995… - in 2021 you know linux is older then this - yet still manage to be more newly written then mysql :smiley:
good luck there.

8

My dear @unborn, tell that to the developers of my client who have already made an application that cannot run on MariaDB, in a desperate attempt at making my life more miserable.

9

@vending_makina well first of all its your responsibility to explain your client as it is your client not mine = your bread and butter :smiley: not mine, secondly I just learn my lesson to be quieter here, also been told un-directly, which is great for me as well. good luck and bye.

10

You’re asking the wrong question, I think? It looks like you’re just having trouble resetting the root password in MySQL?

11

Never heard of that. MySQL vs. MariaDB is sort of like Exxon vs. Mobil gasoline. They’re both gasoline. Your car isn’t going to know which is which.

They may LIST MySQL as a requirement, but if you’re running MariaDB it’s not going to know it’s not on MySQL.

Or at least not in any instance I’ve ever seen before.

1 Like
12

They have diverged in minor ways. It’s possible to make something work in only one or the other, but I think you’d have to be trying.

2 Likes
13

News to me, Joe. Wow. How anal would one have to be to do something like that?

14

I can think of at least one thing that works differently in MySQL, which is the json type column.
It parses the json value as an object, and does not store it as plain text, while MariaDB does not have that data type. They might have used this or other features. I don’t know. @unborn, they asked for hosting AFTER writing the application, so I had no say in that.

And regarding asking the wrong question: Yes, eventually it came down to resetting the password. In the beginning I didn’t expect such a behaviour for generating the initial password, and then I never suspected the MySQL documentation was incorrect… I thought perhaps Virtualmin supported something that would make life easier. Damn, it was a rough day for me.

15

If Webmin module for MySQL/MariaDB cannot connect to the database, it will give you a nice prompt on UI to do it in no time (force reset the password).

The issue you were facing is expected, as MySQL/MariaDB Webmin module thought it knew the password (and it did, as it was defined and working for old MariaDB) but as you clean installed with MySQL, the root database password happened to be set to blank and as a result it didn’t work.

1 Like
16

Here’s an update for anyone who decides to go down this :volcano: rocky route:

Numerous tests and attempts were made. I’m not a total noob when it comes to Linux, webmin, and Virtualmin, but it really took plenty of effort to get to the conclusions:

+----------+---------+-----------------------------------------------------+
|    OS    |  MySQL  |    Comment                                          |
+----------+---------+-----------------------------------------------------+
| CentOS 8 | Ver 8   | Possible compatibility issues in other components***|
+----------+---------+-----------------------------------------------------+
| CentOS 8 | Ver 5.7 | MySQL 5.7 is not available for CentOS 8             |
+----------+---------+-----------------------------------------------------+
| CentOS 7 | Ver 5.7 | Removal of MariaDB* causes chroot to fail           |
+----------+---------+-----------------------------------------------------+
| CentOS 7 | Ver 5.7 | As a Remote DB Server, it randomly denies service** |
+----------+---------+-----------------------------------------------------+

* Speculation: When MariaDB gets uninstalled some components and their settings files get removed. Reinstallation of these components didn’t help. Chroot’s problem manifested by closing SSH connections immediately after connecting. (Also there seems to be a bug with chroot and Virtualmin on CentsOS 8, but that’s another thing). Other uncovered issues might also exist, but I preferred not to continue on this path.

** The remote connections to MySQL were sketchy, to say the least. Every so often, out of the blue, the connection would fail, and the error message coming from MySQL was not helpful at all, claiming that the connecting user does not have permission, although the same connection and action was just being done a minute ago and it was working fine. So frustrating!

*** Amnesia… I no longer remember accurately why it didn’t work. Possibly because it was my first attempt and I didn’t have any experience with MySQL.

So what did I do eventually you may ask…
Well, I had an old installation of Vesta Control Panel on one of the servers. Vesta comes with MySQL, and that’s what they got to get eventually.

17

Just for the record, eventually it turned out the password is generated and logged when the Service myqld is started, not during installation. This password can be found using:
grep 'temporary password' /var/log/mysqld.log

18

As far as I know, it is. What problem did you run into?

chroot as in jailkit jails? That’d mean you need to change your jail config. I can’t think of anything else that could cause it.

19

Frankly I just found this summary written in my notes, and I assumed it was correct. Now that you stimulated my cells with your question, I can’t seem to remember, but I can guess one of two possible things:

  1. The post-installation wizard failed to reset the MySQL root password, so I assumed it was incompatibility.
  2. Adding a new user to the server didn’t work because the syntax differs between ver 5 and ver 8 (, I believe).

Whatever is the case, since that I’m no longer trusting my own summary because I was less experienced than now, and much more tired, I will edit my reply above to make it clearer and more fair.

chroot as in jailkit jails? That’d mean you need to change your jail config.

Yes. This was one issue I faced after exhausting attempts, so I thought: I’m not ready for any more surprises, I need to get some sleep, so I’m out.
I have literally zero understanding and experience with chroot/jailkit, but… on the same system where I replaced MariaDB with MySQL:
One probably related thing I noticed during the preparation of the first virtual server was an error message from chroot about a repeated entry at line 180 (or was it 118?) in the settings file. Unfortunately I didn’t keep the error message, but when I looked into the settings file it was there in the openvpn section, a duplicated setting with two different values. I commented the first one and it seemed to work…
That was a CentOS 8. I checked the same file on a CentOS 7 standard installation and found the same duplicated entries, yet there was never a complaint about this.

20

That’s not in any way MySQL/MariaDB related. It’s a bug in the package on…some distros. I don’t remember details, but it’s something I still need to fix, I think.

If you don’t know anything about jails, don’t use them. They aren’t a security feature, they’re cosmetic. Some people like the feeling of security it gives them, but it is not “more secure”. The package will get fixed eventually, and you can just delete the extra line from the jail config, as well. It should not be in any way related to your decision about what to do with MySQL/MariaDB because it has nothing to do with it. (I still recommend everyone use whatever comes with your OS. There’s probably never gonna be a new feature in either worth the hassle of taking on the responsibility of maintaining a third party installation. Just use an up to date OS, and use the database the installer installs, if at all possible.)