[CentOS 7] Upgrade Mysql 5.5 to 10.2

20 posts / 0 new

Topic locked

#1 Thu, 08/10/2017 - 00:50

Teranode

[CentOS 7] Upgrade Mysql 5.5 to 10.2

I'm currently utilizing: Nginx + PHP-FPM (7.1.8) + Memcahce + Redis + MariaDb 10.2.7

First before you do anything make a backup of your current database: mysqldump -u root -p --all-databases > database-backup.sql

Next stop and uninstall the current mariadb service:

# service mysql stop

$ yum remove mysql* mariadb*
$ yum clean all

Also make a backup of your etc/my.cnf file before you do this. (you can always get the default one from /usr/share/mysql)

Create a file in /etc/yum.repos.d/MariaDB.repo then copy and paste the code below inside it.

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.2/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Save then install mariadb 10.2: $ yum install MariaDB-server MariaDB-client

then start the mysql service: # service mysql start

then run and upgrade # mysql_upgrade -u root -p

restore your my.cnf you made a backup of or use one of the examples in the directory i mentioned above. (if you happen to use the example, remove the innodb_additional_mem_pool_size line as it's no longer being used)

Your mysql service has now been upgraded to 10.2 without issue. The Mysql area will be able to read the config file.

Now you may have noticed that during the uninstall that postfix was also uninstalled along with mariadb 5.5. This indeed happened however luckily it created a backup for the main, virtual and master configuration before uninstallation.

go to your Webmin CP -> Servers -> Postfix Mail Server It will say that it's not installed, click the install button for it to reinstall now go back to "Postfix Mail Server" settings and go to "Edit Config Files"

in the dropdown go to main.rpmsave, virtual.rpmsave and master.rpmsave and copy/paste each one to their respective default file.

If you also installed dkim-milter, it would have also created a backup file of dkim-fliter.conf.rpmsave so just apply the same thing there (i assume there's not in house editor so you'll have to restore via ssh)

#2 Wed, 09/27/2017 - 09:12

Eskes

Les isue is this.

nano /etc/yum.repos.d/MariaDB.repo

Post

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.2/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Save

yum update

reboot

#3 Tue, 10/10/2017 - 09:43

Eskes

You migt get problems. the cehck mysql config i webmin. Config modul. Check that it is service mysql start and stop and not service mariadb

#4 Wed, 06/27/2018 - 23:18

oracast

Stupid question. I am seeing the comment from Eskes about simply adding the new repo and doing a yum update but on every other site it talks about uninstalling first and then installing and running upgrade. Any reason why I am reading this and there are no comments here? I have existing databases but Magento 2 requires MariaDB 10 instead of 5.5 on Centos7 so it seems to be a necessary upgrade but I want to do this correctly and efficiently. Thoughts?

#5 Mon, 02/18/2019 - 09:31

Bassem

please can you explain this point :

"in the dropdown go to main.rpmsave, virtual.rpmsave and master.rpmsave and copy/paste each one to their respective default file."

also i don't have this files but i have others like them >> main.cf.rpmsave, virtual and master.cf.rpmsave

#6 Fri, 05/24/2019 - 17:43

Gashumba

I have tried over and over to upgrade to MariaDB 10.3 but everything I have tried ends up with errors. Although it seems to be working, MySQL throws up errors when I try to create new databases after the upgrade. If anyone has a working procedure, please share it.

#7 Fri, 05/24/2019 - 18:23

oracast

This works for us....

Update required to get MariaDB 10.3 instead of MariaDB 5.5. Need to backup DBs and will have a little bit of downtime (15 mins tops)

ensure the system is up to date

yum update

create a backup of the current databases

mysqldump -u root -p --all-databases > all-databases.sql

create a mariadb yum repo config

cat > /etc/yum.repos.d/MariaDB10.repo << EOF

MariaDB 10.3 CentOS repository list - 2018-07-06 http://mariadb.org/mariadb/repositories/

[mariadb] name = MariaDB baseurl = http://yum.mariadb.org/10.3/centos7-amd64 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1 EOF

remove the current mariadb install

yum remove mariadb-server mariadb mariadb-libs yum clean all

re-install mariadb against the current config

yum -y install MariaDB-server MariaDB-client

ensure the mariandb service is started

systemctl enable mariadb systemctl start mariadb

reinstall packages removed with mariadb !!!NOTE – must be mysqlnd or will have library conflicts

yum install perl-DBD-MySQL php-mysqlnd postfix postgrey

rebuild the databases

mysql_upgrade --force -u root –p

Go to Postfix server and start the server, also ensure it’s turned on at reboot by setting and starting the server in the Bootup and Shutdown menu under System in Webmin.

#8 Fri, 08/02/2019 - 09:51 (Reply to #7)

applejack

Thanks this worked for me but I updated to MariaDB 10.0 as from what I read you should update to one version at a time. It also depends on what your current version of your database is i.e. if you're changing from say Centos 6 using MySQL 5.5 or 5.6 etc.

Also for postgrey ensure it’s turned on at reboot by setting and starting the server in the Bootup and Shutdown menu under System in Webmin.

P.S. When I updated to MariaDB 10.2 or 10.3 I got errors when trying to create a new database via Virtualmin or a new user. This was on Centos 6.

MySQL database failed! : mysql::execute_sql_logged failed : SQL insert into user (host, user, ssl_type, ssl_cipher, x509_issuer, x509_subject) values ('localhost', 'domain_owner', '', '', '', '') failed : Field 'authentication_string' doesn't have a default value at /usr/libexec/webmin/web-lib-funcs.pl line 1476.

Failed to save user : SQL set password for 'user_test'@'localhost' = '19N-)8^-gTp-vB4' failed : Password hash should be a 41-digit hexadecimal number

#9 Fri, 05/24/2019 - 19:47

Gashumba

I will give that a try tomorrow and provide an update. Thank you!

#10 Sun, 05/26/2019 - 11:21

Gashumba

I was able to upgrade to version 10.3 and everything is working fine. However, I got the following error when trying to upgrade the databases

root@myvserver.com:~# mysql_upgrade --force -u root –p
        Version check failed. Got the following error when calling the 'mysql' command line client
        ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
        FATAL ERROR: Upgrade failed

Also, I am not able to run systemctl start mysql nor systemctl enable mysql form command line. But then again, In Virtualmin everything seems to be running just fine, although this is due to not having any active databases on this server as all my clients are still running on the apache server. I was able to create a new vhost with a database but do not know if i will run into any issues.

If anyone can see red flags please do share.

Thanks again for your help!

#11 Sun, 05/26/2019 - 15:30

oracast

If you don't have any databases on that server you are likely ok but the error 1045 is saying that you entered the wrong password for your root user in the mariadb server. I would personally try to run that command again.

For the systemctl start mysql command, I dont use that but does it work in virtualmin or webmin?

#12 Mon, 05/27/2019 - 20:08

Gashumba

I ended up using systemctl start mariadb instead. I also, had a problem with mysql_upgrade --force -u root –p but I used this instead mysql_upgrade=FORCE and that seemed to work. However, SMTP still not working.

#13 Thu, 07/18/2019 - 06:26

h2ojunkie

I've tried every suggestion in this topic, and no matter what i do I can't get postfix to start after ugprading mariaDB.

Has anyone been able to get the system fully function with the oracast instructions? (or any of them for that matter)

#14 Fri, 09/13/2019 - 03:57

chill_Surf

Has anyone found a workaround on updating mysql 5.5 to mySQL 10? The update works and my system uses MySQL 10.2.27 but I also experience some strange issues when I try that method.

Whats the best way to update? is there any update to this after a year?

Can andreycheck let us know if this is the correct way to upgrade to mySQL 10.2 without damaging my virtualmin installation?

#15 Sat, 10/12/2019 - 16:59

andreychek

I unfortunately don't have good solid advice on upgrading to MariaDB versions not included with your distro... we've been seeing a lot of problems with upgrade attempts lately.

I think my suggestion is that, if possible, wait until the next Virtualmin release, where we'll be including better support for MariaDB 10+.

You'd still need to use caution upgrading to package versions not provided by your distro, but at least the support should be better at that point.

-Eric

#16 Sat, 10/12/2019 - 17:23 (Reply to #15)

Gashumba

With minimum requirements for WordPress 5 requiring MariaDB 10.x, when can we expect Virtualmin to support latest version of LEMP stack.

Also, any word on upgrading Virtualmin to support CentOS 8?

#17 Sat, 10/12/2019 - 17:33

andreychek

Howdy,

WordPress works just fine with MySQL 5.x, and their system requirements page states the following:

Note: If you are in a legacy environment where you only have older PHP or MySQL versions, WordPress also works with PHP 5.6.20+ and MySQL 5.0+

Their concern is that those software versions have reached their end of life -- but that's not the case on CentOS, where RHEL/CentOS continue to provide updates for them.

Virtualmin supports MariaDB 10.0-10.2, for distro's that ship with those package versions.

The issue we've been noticing is when upgrading to MariaDB versions being provided by third parties can lead to some troubles.

Now, we'll be making updates to try and reduce those troubles, though there's always a risk when using third party repositories.

We expect the next Virtualmin version we release to offer support for newer MariaDB versions, which should put us in a good place for Debian 10 and CentOS 8 support. Hopefully that will all happen around the same time.

-Eric

#18 Sat, 10/12/2019 - 17:58 (Reply to #17)

Gashumba

I understand that WordPress 5 still works with MySQL databases but they have plans of abandoning anything older than MySQL 5.6 or MariaDB 10.1 soon.

I have used YUM Repo to install MariaDB 10.2 and it worked just fine with no issues. However, I am having issues with third party mail clients after the last update of Virtualmin. I have no idea if this is related to it but I have not yet found a solution.

I am really looking forward to CentOS 8/ Virtualmin eagerly! Thank you for all your hardwork, guys!

#19 Mon, 10/14/2019 - 16:11

oracast

Hi andreychek,

You mentioned that you have seen lots of issues with the config we had setup and I would love to hear more about what these look like. I know another user that will be using 10.x pretty quickly on CentOS 7 but would like to warn him of some potential issues. Our DB servers are generally on different VMs than the websites so that may be why we have been relatively ok but I will try to work through this with another guy and post our results. It's been a while since we have touched this and we appreciate everything your team has done over many years so if we can add value we will try.

#20 Tue, 10/29/2019 - 16:11

jmunjr

Three questions below:

I found this guide to upgrade from Mariadb 5.5 to 10.1 to 10.3 on Centos 7 with provisions for Virtualmin:

https://vander.host/knowledgebase/databases/how-to-upgrade-for-mariadb-5...

Has anyone followed this guide closely. If so, what were your results?

Is there a method to reverse the upgrade and go back to normal, keeping 5.5, or are we "screwed" once it breaks, if it breaks?

Andrey, do you have a rough ETA for the next release with better MariaDB support?

Thank you.

Topic locked