Announcement

Collapse
No announcement yet.

MariaDB 10 Guide.

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • bdacus01
    started a topic MariaDB 10 Guide.

    MariaDB 10 Guide.

    All:

    I could not find a solid guide on how to upgrade Interworx 5.X Mysql to MariaDB 10.x. So a few caviots MariaDB 10 is not supported currently by Interworx OR Me for that matter. BACKUP YOUR SYSTEM AND DB'S. I warned you. Try this on a test box. Dont have a test box...well buckle your seet belt here we go. I will attach the guide and copy and paste it here. Feel free to improve of change it. Please post improvement and success here..



    #Interworx 5.x #Mysql to MariaBD 10.x guide
    #CENTOS 7.x
    #Brent Dacus 2016


    #NOTES Depending on your installed server you may need to substitute maria for mysql or vise versa.




    #BACKUP YOUR DB's NOW...


    #Lets do a on the system update. You should be good here but if your Naughty well here we go.


    yum update -y


    #need to backup config will need later.


    mv /etc/my.cnf /etc/my.cnf.bak


    #stop server


    systemctl stop mysql OR systemctl stop mariadb




    yum remove mariadb mariadb-server mariadb-libs mariadb-devel


    #It should not tell you it will NOT remove dependancies!!! found it might want to
    #remove net-snmp and php-snmp. I let it remove them and then reinstalled.


    #Option: You could use:


    rpm -e mariadb mariadb-server mariadb-libs mariadb-devel --nodeps


    yum clean all


    #Add new repo for MariaDB 10. you can get this on the repos generator here.
    #Go her in your browser an follow the prompts
    https://downloads.mariadb.org/mariad...#mirror=osuosl


    #You should get
    #start copy paste here
    # MariaDB 10.0 CentOS repository list - created 2016-08-13 17:06 UTC
    # http://downloads.mariadb.org/mariadb/repositories/
    [mariadb]
    name = MariaDB
    baseurl = http://yum.mariadb.org/10.0/centos7-amd64
    gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
    gpgcheck=1
    #End copy paste here.


    #from cd /etc/yum.repos.d


    nano mariadb10.repo


    #Paste in the above repo info and save.


    #Dont be tempted to run the Install command on the MariaDB website yet..


    #This will update and existing items after adding the new repo.


    yum update -y


    #Now run. Almost there!!!


    yum install MariaDB-client.x86_64 MariaDB-server.x86_64 MariaDB-common.x86_64 MariaDB-devel.x86_64 MariaDB-shared.x86_64


    #Under Installed you should see only entries like "10.1.16-1.el7.centos"


    yum list maria*


    #Now run
    systemctl start mariadb


    #or


    systemctl start mysql


    #either should work


    #Now we need to upgrade the mysql system db


    mysql_upgrade --verbose -uroot -p


    #Enter your Mysql root password when asked.




    systemctl restart mariadb


    systemctl status mariadb


    #In "status" Shouldn't se any errors


    #just to be sure we are all enabled


    systemctl enable mariadb


    #Replace the New my.cnf with the Interworks one. We will backup the new one as well.
    #Should be fine but you might want compare them.


    mv /etc/my.cnf /etc/my.cnf.mdb10


    mv /etc/my.cnf.bak /etc/my.cnf


    systemctl restart mariadb


    systemctl status mariadb


    #WHEW YOU did it!!!


    #login to Nodeworx and check Overview and PhpMyAdmin


    #In Overview you will see the wrong Version listes as 5.5.5


    #In PhpMyAdmin you should the correct Server version.
    Attached Files
    Last edited by bdacus01; 08-18-2016, 09:20 PM. Reason: updated

  • paulo
    replied
    (CentOS 6.10 amd64)

    Just the FULL log of the installation... looking good...

    Code:
    [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)
    Last edited by paulo; 06-18-2019, 05:52 AM.

    Leave a comment:


  • d2d4j
    replied
    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

    Leave a comment:


  • paulo
    replied
    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

    Leave a comment:


  • d2d4j
    replied
    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

    Leave a comment:


  • Justec
    replied
    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!

    Leave a comment:


  • d2d4j
    replied
    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

    Leave a comment:


  • Justec
    replied
    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?

    Leave a comment:


  • d2d4j
    replied
    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

    Leave a comment:


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

    Leave a comment:


  • WebHostingUK
    replied
    Very good guide, thanks!

    Leave a comment:


  • javapipe
    replied
    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,

    Leave a comment:


  • bestellen
    replied
    Hopefully your how too may inspire others to post their how too

    Leave a comment:


  • carewa
    replied
    Wow, thank you for this tutorial, it's awesome!

    Leave a comment:


  • d2d4j
    replied
    Hi Justin

    Many thanks

    I'm sure it will go well for you, as it appears to for others who have posted but appreciate how you got on in an update

    Many thanks

    John

    Leave a comment:

Working...
X