MariaDB 10 Guide.

Wow, thank you for this tutorial, it’s awesome!

Hopefully your how too may inspire others to post their how too

Question. After upgrading mariadb to 10, interworx cannot stop and start because its trying to use systemd mysqld.service which doesn’t exist.
How can we map this to mysql.service instead? I tried to find the systemd config for mysql.service to setup symlink but couldnt find that either. Any ideas much appreciated.

Thanks,

Very good guide, thanks!

After all, is MariaDB good and worth the effort to setup?! I have heard many good and bad things about it.

Hi rasa

Changing from MySQL to mariadb is entirely your choice.

If it helps decide, IW on Centos 7 use mariadb and we also use mariadb on centos 7, running latest IW-CP

Many thanks

John

I just wanted to check back in on this. What is InterWorx stance on upgrading our servers to MariaDB? Is there any official repos from Iworx or would following this guide still be the best path?

Hi Justin

I hope your well and had a lovely Christmas break

I would just follow the thread to upgrade to mariaDB

We have updated our centos 7 servers and all appears to work lovely. Just make sure you make backups first

Hope that helps a little

Many thanks

John

Thanks John, looking back through this thread I believe all my test almost exactly 2 years ago worked fine, I just never needed to upgrade, but now I’m hitting the point where some applications need it. Do you know if there is an easy downgrade path? If I run into issues. Can I just easily downgrade by using yum?

I think just to be safe I will move over some of my larger / complex sites onto a test server and do the upgrade again (like I did 2 years ago) and just make sure it’s all good.

Glad to hear it’s working well for you!

Hi Justin

Many thanks

I do not think you would be able to yum downgrade as the original had been removed.

So I?m thinking you would need to remove mariaDB first and then install MySQL most likely from IW repos

Sorry if I am wrong though

Many thanks

John

Hello,

Have anyone tried this guide on CentOS 6.x ? (changing the packages/ dirs from el7 to el6) … Or any guide out there for how to upgrade MySQL 5.5 to MySQL 5.6/ MariaDB 10 ?

Thanks

Paulo

Hi Paulo

Many thanks

Yes I have upgraded centos 6 to use mariaDB and it works lovely.

This is a good guide but please make sure you make all backups first - just in case anything goes wrong

We have also upgraded centos 7 to mariaDB and IW in centos 7 uses mariaDB

I believe many IW uses have also updated to mariaDB

Please could you update your post with how you got on

Many thanks

John

(CentOS 6.10 amd64)

Just the FULL log of the installation… looking good…


[root@server ~]# cat /home/interworx/iworx.ini | grep rootdsn=
rootdsn="mysql://iworx:?????????@unix(/var/lib/mysql/mysql.sock)/mysql"

[root@server ~]# mysqldump -u iworx -p --add-drop-table --routines --events --all-databases --force > data-for-upgrade.sql
Enter password:

[root@server ~]# yum update -y

[root@server ~]# mv /etc/my.cnf /etc/my.cnf.bak

[root@server ~]# service mysqld stop

[root@server ~]# yum clean all

[root@server ~]# vi /etc/yum.repos.d/MariaDB.repo
[root@server ~]# cat /etc/yum.repos.d/MariaDB.repo
# MariaDB 10.3 CentOS repository list - created 2019-06-18 00:00 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.3/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

[root@server ~]# yum update -y
...
Resolving Dependencies
--> Running transaction check
.....
--> Finished Dependency Resolution

Dependencies Resolved
-
Installing:
 MariaDB-client                 x86_64          10.3.16-1.el6                 mariadb           54 M
     replacing  mysql.x86_64 5.5.62-1.el6.remi
 MariaDB-compat                 x86_64          10.3.16-1.el6                 mariadb          4.0 M
     replacing  mysql-libs.x86_64 5.5.62-1.el6.remi
 MariaDB-devel                  x86_64          10.3.16-1.el6                 mariadb          7.7 M
     replacing  mysql-devel.x86_64 5.5.62-1.el6.remi
 MariaDB-server                 x86_64          10.3.16-1.el6                 mariadb          123 M
     replacing  mysql-server.x86_64 5.5.62-1.el6.remi
Installing for dependencies:
 MariaDB-common                 x86_64          10.3.16-1.el6                 mariadb          162 k
 boost-program-options          x86_64          1.41.0-28.el6                 base             108 k
 galera                         x86_64          25.3.26-1.rhel6.el6           mariadb          8.8 M

Transaction Summary
-
Install       7 Package(s)

Total download size: 197 M
Downloading Packages:
Setting up and reading Presto delta metadata
Processing delta metadata
Package(s) data still to download: 197 M
(1/7): MariaDB-10.3.16-centos6-x86_64-client.rpm                              |  54 MB     00:50    
(2/7): MariaDB-10.3.16-centos6-x86_64-common.rpm                              | 162 kB     00:01    
(3/7): MariaDB-10.3.16-centos6-x86_64-compat.rpm                              | 4.0 MB     00:04    
(4/7): MariaDB-10.3.16-centos6-x86_64-devel.rpm                               | 7.7 MB     00:07    
(5/7): MariaDB-10.3.16-centos6-x86_64-server.rpm                              | 123 MB     02:00    
(6/7): boost-program-options-1.41.0-28.el6.x86_64.rpm                         | 108 kB     00:00    
(7/7): galera-25.3.26-1.rhel6.el6.x86_64.rpm                                  | 8.8 MB     00:09    
-
Total                                                                1.0 MB/s | 197 MB     03:13    
warning: rpmts_HdrFromFdno: Header V4 DSA/SHA1 Signature, key ID 1bb943db: NOKEY
Retrieving key from https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
Importing GPG key 0x1BB943DB:
 Userid: "MariaDB Package Signing Key <package-signing-key@mariadb.org>"
 From  : https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing : MariaDB-compat-10.3.16-1.el6.x86_64                                              1/11
  Installing : MariaDB-common-10.3.16-1.el6.x86_64                                              2/11
  Installing : MariaDB-client-10.3.16-1.el6.x86_64                                              3/11
  Installing : boost-program-options-1.41.0-28.el6.x86_64                                       4/11
  Installing : galera-25.3.26-1.rhel6.el6.x86_64                                                5/11

*
error: %pre(MariaDB-server-10.3.16-1.el6.x86_64) scriptlet failed, exit status 1
Error in PREIN scriptlet in rpm package MariaDB-server-10.3.16-1.el6.x86_64
error:   install: %pre scriptlet failed (2), skipping MariaDB-server-10.3.16-1.el6
  Installing : MariaDB-devel-10.3.16-1.el6.x86_64                                               7/11
  Erasing    : mysql-devel-5.5.62-1.el6.remi.x86_64                                             8/11
  Erasing    : mysql-5.5.62-1.el6.remi.x86_64                                                   9/11
  Erasing    : mysql-libs-5.5.62-1.el6.remi.x86_64                                             10/11
  Verifying  : galera-25.3.26-1.rhel6.el6.x86_64                                                1/11
  Verifying  : MariaDB-common-10.3.16-1.el6.x86_64                                              2/11
  Verifying  : MariaDB-devel-10.3.16-1.el6.x86_64                                               3/11
  Verifying  : MariaDB-client-10.3.16-1.el6.x86_64                                              4/11
  Verifying  : MariaDB-compat-10.3.16-1.el6.x86_64                                              5/11
  Verifying  : boost-program-options-1.41.0-28.el6.x86_64                                       6/11
  Verifying  : MariaDB-server-10.3.16-1.el6.x86_64                                              7/11
  Verifying  : mysql-devel-5.5.62-1.el6.remi.x86_64                                             8/11
mysql-server-5.5.62-1.el6.remi.x86_64 was supposed to be removed but is not!
  Verifying  : mysql-server-5.5.62-1.el6.remi.x86_64                                            9/11
  Verifying  : mysql-5.5.62-1.el6.remi.x86_64                                                  10/11
  Verifying  : mysql-libs-5.5.62-1.el6.remi.x86_64                                             11/11

Installed:
  MariaDB-client.x86_64 0:10.3.16-1.el6             MariaDB-compat.x86_64 0:10.3.16-1.el6            
  MariaDB-devel.x86_64 0:10.3.16-1.el6            

Dependency Installed:
  MariaDB-common.x86_64 0:10.3.16-1.el6         boost-program-options.x86_64 0:1.41.0-28.el6        
  galera.x86_64 0:25.3.26-1.rhel6.el6          

Replaced:
  mysql.x86_64 0:5.5.62-1.el6.remi                 mysql-devel.x86_64 0:5.5.62-1.el6.remi          
  mysql-libs.x86_64 0:5.5.62-1.el6.remi          

Failed:
  MariaDB-server.x86_64 0:10.3.16-1.el6            mysql-server.x86_64 0:5.5.62-1.el6.remi          

Complete!

[root@server ~]# yum install MariaDB-client.x86_64 MariaDB-server.x86_64 MariaDB-common.x86_64 MariaDB-devel.x86_64 MariaDB-shared.x86_64
Package MariaDB-client-10.3.16-1.el6.x86_64 already installed and latest version
Package MariaDB-common-10.3.16-1.el6.x86_64 already installed and latest version
Package MariaDB-devel-10.3.16-1.el6.x86_64 already installed and latest version
Resolving Dependencies
--> Running transaction check
---> Package MariaDB-server.x86_64 0:10.3.16-1.el6 will be obsoleting
---> Package MariaDB-shared.x86_64 0:10.3.16-1.el6 will be installed
---> Package mysql-server.x86_64 0:5.5.62-1.el6.remi will be obsoleted
--> Finished Dependency Resolution

Dependencies Resolved

=
 Package                    Arch               Version                     Repository           Size
=
Installing:
 MariaDB-server             x86_64             10.3.16-1.el6               mariadb             123 M
     replacing  mysql-server.x86_64 5.5.62-1.el6.remi
 MariaDB-shared             x86_64             10.3.16-1.el6               mariadb             362 k

Transaction Summary
=
Install       2 Package(s)

Total download size: 123 M
Is this ok [y/N]: y
Downloading Packages:
Setting up and reading Presto delta metadata
Processing delta metadata
Package(s) data still to download: 123 M
(1/2): MariaDB-10.3.16-centos6-x86_64-server.rpm                              | 123 MB     01:53    
(2/2): MariaDB-10.3.16-centos6-x86_64-shared.rpm                              | 362 kB     00:00    
-
Total                                                                1.1 MB/s | 123 MB     01:54    
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing : MariaDB-shared-10.3.16-1.el6.x86_64                                               1/3

*
A MySQL or MariaDB server package (mysql-server-5.5.62-1.el6.remi.x86_64) is installed.

The current MariaDB server package is provided by a different
vendor (Remi Collet) than MariaDB Foundation.  Some files may be installed
to different locations, including log files and the service
startup script in /etc/init.d/.

Upgrading directly from MySQL 5.5 to MariaDB 10.3 may not
be safe in all cases.  A manual dump and restore using mysqldump is
recommended.  It is important to review the MariaDB manual's Upgrading
section for version-specific incompatibilities.

A manual upgrade is required.

- Ensure that you have a complete, working backup of your data and my.cnf
  files
- Shut down the MySQL server cleanly
- Remove the existing MySQL packages.  Usually this command will
  list the packages you should remove:
  rpm -qa | grep -i '^mysql-'

  You may choose to use 'rpm --nodeps -ev <package-name>' to remove
  the package which contains the mysqlclient shared library.  The
  library will be reinstalled by the MariaDB-shared package.
- Install the new MariaDB packages supplied by MariaDB Foundation
- Ensure that the MariaDB server is started
- Run the 'mysql_upgrade' program

This is a brief description of the upgrade process.  Important details
can be found in the MariaDB manual, in the Upgrading section.
*
error: %pre(MariaDB-server-10.3.16-1.el6.x86_64) scriptlet failed, exit status 1
Error in PREIN scriptlet in rpm package MariaDB-server-10.3.16-1.el6.x86_64
error:   install: %pre scriptlet failed (2), skipping MariaDB-server-10.3.16-1.el6
  Verifying  : MariaDB-shared-10.3.16-1.el6.x86_64                                               1/3
  Verifying  : MariaDB-server-10.3.16-1.el6.x86_64                                               2/3
mysql-server-5.5.62-1.el6.remi.x86_64 was supposed to be removed but is not!
  Verifying  : mysql-server-5.5.62-1.el6.remi.x86_64                                             3/3

Installed:
  MariaDB-shared.x86_64 0:10.3.16-1.el6                                                              

Failed:
  MariaDB-server.x86_64 0:10.3.16-1.el6            mysql-server.x86_64 0:5.5.62-1.el6.remi          

Complete!

[root@server ~]# rpm -qa | grep -i '^mysql-'
mysql-iworx-4.0.21-12.rhe6x.iworx.x86_64
mysql-server-5.5.62-1.el6.remi.x86_64

[root@server ~]# rpm --nodeps -ev mysql-server
warning: /var/log/mysqld.log saved as /var/log/mysqld.log.rpmsave

[root@server ~]# rpm -qa | grep -i '^mysql-'
mysql-iworx-4.0.21-12.rhe6x.iworx.x86_64

[root@server ~]# yum install MariaDB-server.x86_64
Loaded plugins: fastestmirror, presto
Setting up Install Process
Loading mirror speeds from cached hostfile
 * base: mirror.fccn.pt
 * epel: mirror.nl.leaseweb.net
 * extras: ftp.cica.es
 * ius: mirrors.kernel.org
 * remi-php56: remi.mirrors.cu.be
 * remi-safe: remi.mirrors.cu.be
 * updates: ftp.cica.es
Resolving Dependencies
--> Running transaction check
---> Package MariaDB-server.x86_64 0:10.3.16-1.el6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=
 Package                    Arch               Version                     Repository           Size
=
Installing:
 MariaDB-server             x86_64             10.3.16-1.el6               mariadb             123 M

Transaction Summary
=
Install       1 Package(s)

Total download size: 123 M
Installed size: 514 M
Is this ok [y/N]: y
Downloading Packages:
Setting up and reading Presto delta metadata
Processing delta metadata
Package(s) data still to download: 123 M
MariaDB-10.3.16-centos6-x86_64-server.rpm                                     | 123 MB     02:13    
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Warning: RPMDB altered outside of yum.
  Installing : MariaDB-server-10.3.16-1.el6.x86_64                                               1/1
  Verifying  : MariaDB-server-10.3.16-1.el6.x86_64                                               1/1

Installed:
  MariaDB-server.x86_64 0:10.3.16-1.el6                                                              

Complete!

[root@server ~]# service mysql start
Starting MariaDB.190617 20:27:47 mysqld_safe Logging to '/var/lib/mysql/server.frontflash.pt.err'.
190617 20:27:47 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
...                                                        [  OK  ]

[root@server ~]# chkconfig mysql on

[root@server ~]# mysql_upgrade --verbose -uroot -p
Enter password:
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
MySQL upgrade detected
Phase 1/7: Checking and upgrading mysql database
Processing databases
mysql
...(mysql db's list)...OK
Phase 7/7: Running 'FLUSH PRIVILEGES'
OK

[root@server ~]# mv /etc/my.cnf /etc/my.cnf.mdb10

[root@server ~]# mv /etc/my.cnf.bak /etc/my.cnf.bak.mysql55

[root@server ~]# cp /etc/my.cnf.bak.mysql55 /etc/my.cnf

[root@server ~]# service mysql restart
Shutting down MariaDB..                                    [  OK  ]
Starting MariaDB.190617 20:36:15 mysqld_safe Logging to '/var/log/mysqld.log'.
190617 20:36:15 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
                                                           [  OK  ]


Final Notes:

  • checked Nodeworx > MySQL Server Overview, and it recognizes the MariaDB engine and version
  • checked Nodeworx > MySQL Server PhpMyAdmin, and it recognizes the MariaDB engine and version
  • The only error until now is on “Nodeworx > Overview” when changing “Start on boot-up” to “On” it does not does nothing and an error occours

(i will check the system further and update this post in case there is something that could be improved)