[CentOS 7] Upgrade Mysql 5.5 to 10.2

14 posts / 0 new
Last post
#1 Thu, 08/10/2017 - 00:50

[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.

name = MariaDB
baseurl = http://yum.mariadb.org/10.2/centos7-amd64

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)

Wed, 09/27/2017 - 09:12

Les isue is this.

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


name = MariaDB
baseurl = http://yum.mariadb.org/10.2/centos7-amd64


yum update


Tue, 10/10/2017 - 09:43

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

Wed, 06/27/2018 - 23:18

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?

Mon, 02/18/2019 - 09:31

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

Fri, 05/24/2019 - 17:43

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.

Fri, 05/24/2019 - 18:23

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.
Fri, 08/02/2019 - 09:51 (Reply to #7)

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

Fri, 05/24/2019 - 19:47

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

Sun, 05/26/2019 - 11:21

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!

Sun, 05/26/2019 - 15:30

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?

Mon, 05/27/2019 - 20:08

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.

Thu, 07/18/2019 - 06:26

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)

Fri, 09/13/2019 - 03:57

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?