How to upgrade MySQL 5.5 to 5.6/5.7 or MariaDB 5.5 to 10.x on Linux?

Follow

Comments

245 comments

  • Avatar
    Peter Debik

    MariaDB 10.2 is not yet officially supported. It is not causing issues in the panel, but the database repair tool claims that some date/timestamp fields are misconfigured, and it is unable to repair them. It would be good to add a check in the installation routines of Plesk to prohibit an installation on MariaDB 10.2. I only "accidentally" installed it on a 10.2 and found out about the wrong version in a recent support ticket.

    0
    Comment actions Permalink
  • Avatar
    Bruno Schrotter (Edited )

    Hello Ivan!

    I have Plesk Onyx Version 17.8.11 Update #19 on a CentOS Linux 7.5.1804 (Core) with a MySQL (Maria DB I belief) ver.5.5.60. on a dedicated server.

    I have a script that requires MySQL ver. 5.6, and error flags with [No MySQL available] when I tried to install it just to see.

    The script developer tells me to move to a server with Cpanel, and that is out of the question, as I have used Plesk since version 7 or 8 I believe. I cannot get a refund either, as this is not a script failure, and I'm not going to write off $800 either, and there's no other script with what I need anyway.

    I have currently 2 servers with Plesk licenses, and I can experiment with one not so important.
    However, my time is on a tremendous squeeze, so I would like to start out with something that works without trying to experiment with version after version.

    Should I try to install the latest MariaDB 10.3, or is it possible to install a MySQL 5.6 as required, and a better option. I would appreciate your opinion. Thank you kindly!

    0
    Comment actions Permalink
  • Avatar
    Richard Mountain

    I've followed the Centos 6 guide, everything seem to install but it's still running MySql 5.5.57 

    I've tried service mysqld restart

    But this doesn't seem to do anything.

    0
    Comment actions Permalink
  • Avatar
    Julian Bonpland Mignaquy

    Hi Nerque that is correct, you may skip this part:

    rpm -q --whatprovides mariadb-server

    rpm -e --nodeps `rpm -q --whatprovides mariadb-server`

    Since you are already running mariadb you do not need to remove mysql.

    0
    Comment actions Permalink
  • Avatar
    Victor Fournier

    Hi @Nikita Nikushkin,

    Thank you for your answer and your help, I didn't notice that it was not yet supported in Plesk. Therefore, it's not possible to make the upgrade for MySQL 5.5 to 5.7 for client side until the full adoption of the 10.3 version ?

    If I downgrade to the 10.2, will it be possible ? The update from 5.5 to 5.7 is automatic or some commands should be run ? I didn't find any clue on the MariaDB website.

     

    Best Regards

    0
    Comment actions Permalink
  • Avatar
    Necro (Edited )

    Ok thanks Ivan!!

    It already seems that Plesk is running much faster on MariaDB 10.3 than on previous 5.5 👍

    Do we need to secure MariaDB? I was reading a tutorial (https://linuxize.com/post/install-mariadb-on-centos-7/) that recommended to run the mysql_secure_installation script which will perform several security related tasks like set up the root user password, remove the anonymous user, restrict root user access to the local machine, and remove the test database.

    Or does Plesk takes care of this already?

    Thanks again!

    0
    Comment actions Permalink
  • Avatar
    Artyom Baranov

    @Domenico,

    Hello,

    In such case, it is still possible to get support from Plesk directly. Please check the article How to get support directly from Plesk?

    Plesk support subscription will allow you to submit a request if you decide to continue with MySQL upgrade.

    0
    Comment actions Permalink
  • Avatar
    Ivan Postnikov

    Hello Miguel Ángel

    This option by itself shouldn't cause the issue.

    If it's still actual, submit a support request.

    0
    Comment actions Permalink
  • Avatar
    Thomas Hellwig

    Hi there,

    my PLESK backend is showing MariaDB 10.5.9 but my server is running 10.5.10. I updatet from 10.5.9 and performed this 2 commands:

    #plesk bin service_node --update local
    # plesk sbin packagemng -sdf

    But it's still the old version in plesk backend...
    0
    Comment actions Permalink
  • Avatar
    Bulat Tsydenov

    @Peter It would be great if you provide your suggestion on our Plesk User Voice. Thank you!

    0
    Comment actions Permalink
  • Avatar
    WeHoDo (Edited )

    Hi,

    I have tried to run the commands on Centos 7 on a test VPS.
    I get no errors, but in the GUI it stills show 5.5.60 as database server version.

    In the CLI I see this:
    mysql Ver 15.1 Distrib 10.2.23-MariaDB,

     

    Did I missed something?

     

    ==

    Never mind, now its shows the 10.2.23 version.
    Probaly some updates didnt renew directly.

     

    Will try to run on the live server.

    0
    Comment actions Permalink
  • Avatar
    Alejandro Stendelis

    Hi I get this error, how I can fix it??

    CentOS 6.8

    mysql Ver 14.14 Distrib 5.5.30, for Linux (x86_64) using readline 5.1

     

     

    [root@casadimaco ~]# yum update mysql
    Loaded plugins: fastestmirror
    Setting up Update Process
    Loading mirror speeds from cached hostfile
    * base: mirror.fileplanet.com
    * extras: mirrors.oit.uci.edu
    * updates: mirrors.usc.edu
    Resolving Dependencies
    --> Running transaction check
    ---> Package mysql.x86_64 0:5.5.30-1.el6.remi will be obsoleted
    --> Processing Dependency: mysql(x86-64) = 5.5.30-1.el6.remi for package: mysql-bench-5.5.30-1.el6.r emi.x86_64
    --> Processing Dependency: mysql(x86-64) = 5.5.30-1.el6.remi for package: mysql-server-5.5.30-1.el6. remi.x86_64
    --> Processing Dependency: mysql(x86-64) = 5.5.30-1.el6.remi for package: mysql-devel-5.5.30-1.el6.r emi.x86_64
    ---> Package mysql-community-client.x86_64 0:5.7.25-1.el6 will be obsoleting
    --> Processing Dependency: mysql-community-libs(x86-64) >= 5.7.9 for package: mysql-community-client -5.7.25-1.el6.x86_64
    --> Running transaction check
    ---> Package mysql-bench.x86_64 0:5.5.30-1.el6.remi will be obsoleted
    ---> Package mysql-community-devel.x86_64 0:5.7.25-1.el6 will be obsoleting
    ---> Package mysql-community-libs.x86_64 0:5.7.25-1.el6 will be obsoleting
    --> Processing Dependency: mysql-community-common(x86-64) >= 5.7.9 for package: mysql-community-libs -5.7.25-1.el6.x86_64
    ---> Package mysql-community-server.x86_64 0:5.7.25-1.el6 will be obsoleting
    --> Processing Dependency: libnuma.so.1(libnuma_1.2)(64bit) for package: mysql-community-server-5.7. 25-1.el6.x86_64
    --> Processing Dependency: libnuma.so.1(libnuma_1.1)(64bit) for package: mysql-community-server-5.7. 25-1.el6.x86_64
    --> Processing Dependency: libnuma.so.1()(64bit) for package: mysql-community-server-5.7.25-1.el6.x8 6_64
    ---> Package mysql-devel.x86_64 0:5.5.30-1.el6.remi will be obsoleted
    ---> Package mysql-libs.x86_64 0:5.5.30-1.el6.remi will be obsoleted
    --> Processing Dependency: libmysqlclient.so.18()(64bit) for package: php-mysql-5.4.13-1.el6.remi.x8 6_64
    --> Processing Dependency: libmysqlclient.so.18(libmysqlclient_16)(64bit) for package: php-mysql-5.4 .13-1.el6.remi.x86_64
    --> Processing Dependency: libmysqlclient.so.18(libmysqlclient_18)(64bit) for package: php-mysql-5.4 .13-1.el6.remi.x86_64
    ---> Package mysql-server.x86_64 0:5.5.30-1.el6.remi will be obsoleted
    --> Running transaction check
    ---> Package mysql-community-common.x86_64 0:5.7.25-1.el6 will be installed
    ---> Package mysql-libs.x86_64 0:5.5.30-1.el6.remi will be obsoleted
    --> Processing Dependency: libmysqlclient.so.18()(64bit) for package: php-mysql-5.4.13-1.el6.remi.x8 6_64
    --> Processing Dependency: libmysqlclient.so.18(libmysqlclient_16)(64bit) for package: php-mysql-5.4 .13-1.el6.remi.x86_64
    --> Processing Dependency: libmysqlclient.so.18(libmysqlclient_18)(64bit) for package: php-mysql-5.4 .13-1.el6.remi.x86_64
    ---> Package numactl.x86_64 0:2.0.9-2.el6 will be installed
    --> Finished Dependency Resolution
    Error: Package: php-mysql-5.4.13-1.el6.remi.x86_64 (installed)
    Requires: libmysqlclient.so.18()(64bit)
    Removing: mysql-libs-5.5.30-1.el6.remi.x86_64 (installed)
    libmysqlclient.so.18()(64bit)
    Obsoleted By: mysql-community-libs-5.7.25-1.el6.x86_64 (mysql57-community)
    Not found
    Available: mysql-libs-5.5.28-3.el6.remi.x86_64 (mt_dv_extras)
    libmysqlclient.so.18()(64bit)
    Available: mysql-libs-5.5.29-1.el6.remi.x86_64 (mt_dv_extras)
    libmysqlclient.so.18()(64bit)
    Available: mysql-libs-5.1.73-8.el6_8.i686 (base)
    Not found
    Error: Package: php-mysql-5.4.13-1.el6.remi.x86_64 (installed)
    Requires: libmysqlclient.so.18(libmysqlclient_18)(64bit)
    Removing: mysql-libs-5.5.30-1.el6.remi.x86_64 (installed)
    libmysqlclient.so.18(libmysqlclient_18)(64bit)
    Obsoleted By: mysql-community-libs-5.7.25-1.el6.x86_64 (mysql57-community)
    Not found
    Available: mysql-libs-5.5.28-3.el6.remi.x86_64 (mt_dv_extras)
    libmysqlclient.so.18(libmysqlclient_18)(64bit)
    Available: mysql-libs-5.5.29-1.el6.remi.x86_64 (mt_dv_extras)
    libmysqlclient.so.18(libmysqlclient_18)(64bit)
    Available: mysql-libs-5.1.73-8.el6_8.i686 (base)
    Not found
    Error: Package: php-mysql-5.4.13-1.el6.remi.x86_64 (installed)
    Requires: libmysqlclient.so.18(libmysqlclient_16)(64bit)
    Removing: mysql-libs-5.5.30-1.el6.remi.x86_64 (installed)
    libmysqlclient.so.18(libmysqlclient_16)(64bit)
    Obsoleted By: mysql-community-libs-5.7.25-1.el6.x86_64 (mysql57-community)
    Not found
    Available: mysql-libs-5.5.28-3.el6.remi.x86_64 (mt_dv_extras)
    libmysqlclient.so.18(libmysqlclient_16)(64bit)
    Available: mysql-libs-5.5.29-1.el6.remi.x86_64 (mt_dv_extras)
    libmysqlclient.so.18(libmysqlclient_16)(64bit)
    Available: mysql-libs-5.1.73-8.el6_8.i686 (base)
    Not found
    You could try using --skip-broken to work around the problem
    You could try running: rpm -Va --nofiles --nodigest

    0
    Comment actions Permalink
  • Avatar
    Ivan Postnikov

    @Stefan, use the instruction from this article.

    Please note that it is highly recommended to create a full server backup before applying resolution steps.

    The solution is applied on your own risk.

    0
    Comment actions Permalink
  • Avatar
    Steve Yates

    re: the CentOS 7 instructions:  "Note: After an upgrade, there may appear 'mysql' init script. It should be removed to avoid conflicts with MariaDB service. The same commands can be used to fix already existing conflict"

    ...it is my experience that running "yum update" and having it install updates to MariaDB 10.2 recreates the file /etc/init.d/mysql.  At the next boot the "mysql" service (which is MariaDB) will start, and any scripts that try to stop and start mariadb may fail since mariadb is "not running" even though it is under the other service name.  In other words /etc/init.d/mysql would need to be deleted after installing updates.

    0
    Comment actions Permalink
  • Avatar
    Ivan Postnikov

    Hello @Alexander,

    This article is devoted to your issue: https://support.plesk.com/hc/en-us/articles/360010401659-MariaDB-does-not-start-after-update-on-November-the-5th-Assertion-failure-in-file?source=search

    The issue is caused by the last MariaDB update and confirmed as MariaDB bug MDEV-20987

    This issue affects the following versions of the MariaDB SQL Server:

    • 10.1.42
    • 10.2.28
    • 10.3.19
    • 10.4.9 (Not supported)
    0
    Comment actions Permalink
  • Avatar
    Nikita Nikushkin

    Hi @Victor Fournier,

    > Thank you for your answer and your help, I didn't notice that it was not yet supported in Plesk. Therefore, it's not possible to make the upgrade for MySQL 5.5 to 5.7 for client side until the full adoption of the 10.3 version ?

    MySQL upgrade from 5.5 to 5.6/5.7 is possible by using steps from this article, as well as MariaDB upgrade from 5.5 to 10.0/10.1/10.2

    However, it is not possible to upgrade MariaDB to MySQL or vice versa as they are different products

    > If I downgrade to the 10.2, will it be possible ? The update from 5.5 to 5.7 is automatic or some commands should be run ? I didn't find any clue on the MariaDB website.

    MariaDB downgrade is not supported be Plesk and this procedure can break Plesk working capacity

    Also, the official MariaDB website tells that downgrading MariaDB is not supported as well:

    "Downgrading MariaDB is not supported. The only reliable way to downgrade is to restore from a full backup made before upgrading, and start the old version of MariaDB.

    Some people have reported successfully downgrading, but there are many possible things that can go wrong, and downgrading is not tested in any way by the MariaDB developers."

    In this article, it was strongly recommended to create a server snapshot/full server backup.

    So, if you have one, restore the server from it and perform the upgrade of MariaDB to the supported version one more time. It is the best way

    If you have no server backup, you can try to do the following at your own risk:

    1. Create a full server backup/snapshot
    2. Stop MariaDB service
    3. Remove MariaDB packages
    4. Disable MariaDB 10.3 repository
    5. Install the same MariaDB version as it was before the upgrade procedure
    6. Stop MariaDB service
    7. Free up the current "/var/lib/mysql" folder and move databases dumps to it. By default, they have to located in the "/var/lib/mysql_backup" folder
    8. Start MariaDB service
    9. Perform the upgrade of MariaDB again to the supported version by Plesk if it is required

    0
    Comment actions Permalink
  • Avatar
    Jan Bludau

    @Ivan: the mysql-upgrade guide is complette, everythink worked as aspected :-)

    to be more update2date is quite nice, more performance is also quite nice =D

    on the plesk gui for install i see that i could install a "MySQL Server" why you don't support updating it?

    the guide works as aspected, fully automatical, without a user input. You give an notice that you will do an backup and everything is cool :-)

    without exakt that line: "rpm -e --nodeps `rpm -q --whatprovides mysql-server`" you will destroy the plesk installation *smile*

    0
    Comment actions Permalink
  • Avatar
    Nikita Nikushkin (Edited )

    Hi @Victor Fournier,

    Based on the provided information:

    # mysql -V

    mysql Ver 15.1 Distrib 10.3.14-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

    you performed an upgrade to MariaDB 10.3

    As far as I can see, the local MySQL server was upgraded successfully 

    Keep also in mind that Plesk supports MariaDB 10.3 in upcoming Plesk 17.9 only:

    MariaDB 10.3 support in Plesk

    The same information is available in the Notes section in this article:

    MariaDB 10.3 is not currently supported. Support for this version will be added in the upcoming release Plesk Onyx 17.9
    0
    Comment actions Permalink
  • Avatar
    Hopjanj (Edited )

    Hello,

    After upgrading to MariaDB 10.2 I have mysql warning in my log.

    ...
    2019-10-25 9:08:08 139900448986880 [Warning] InnoDB: Cannot add field `external_id` in table `psa`.`clients` because after adding it, the row size is 8714 which is greater than maximum allowed size (8126) for a record on index leaf page.
    2019-10-25 9:08:14 139900448782080 [Warning] InnoDB: Cannot add field `page_wrapper_template` in table `admin_d_db`.`IPB_cms_pages` because after adding it, the row size is 8858 which is greater than maximum allowed size (8126) for a record on index leaf page.
    2019-10-25 9:08:22 139900448986880 [Warning] InnoDB: Cannot add field `page_wrapper_template` in table `admin_db`.`IPB_cms_pages` because after adding it, the row size is 8858 which is greater than maximum allowed size (8126) for a record on index leaf page.
    2019-10-25 9:08:27 139900448782080 [Warning] InnoDB: Cannot add field `category_last_title` in table `admin_db`.`IPB_cms_database_categories` because after adding it, the row size is 8766 which is greater than maximum allowed size (8126) for a record on index leaf page.
    2019-10-25 9:08:46 139900448782080 [Warning] InnoDB: Cannot add field `country` in table `admin_whmc1`.`tblclients` because after adding it, the row size is 8690 which is greater than maximum allowed size (8126) for a record on index leaf page.
    2019-10-25 9:10:01 139900449396480 [Warning] InnoDB: Cannot add field `record_edit_member_name` in table `admin_d_db`.`IPB_cms_custom_database_1` because after adding it, the row size is 8152 which is greater than maximum allowed size (8126) for a record on index leaf page.
    2019-10-25 9:13:36 139900449396480 [Warning] InnoDB: Cannot add field `video_poster` in table `admin_d_db`.`IPB_videos_videos` because after adding it, the row size is 8754 which is greater than maximum allowed size (8126) for a record on index leaf page.
    2019-10-25 9:17:02 139900447553280 [Warning] InnoDB: Cannot add field `month` in table `psa`.`ScheduledTasks` because after adding it, the row size is 8567 which is greater than maximum allowed size (8126) for a record on index leaf page.

    Should I edit my.cnf?
    If so, what are the optimum values?

    OS: Ubuntu 18.04.3 LTS
    Plesk Obsidian 18.0.20

    Thank You for Answer

     

     

    0
    Comment actions Permalink
  • Avatar
    Jan Bludau (Edited )

    Hello Plesk Support Team,

    could you change this line in your documentation:

    # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqldump -u admin --all-databases --routines --triggers > /tmp/all-databases.sql

    to

    # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqldump -u admin --verbose --all-databases --routines --triggers > /tmp/all-databases.sql

    it shows what is happening. Better output better for large Databases doesn't seems to be frozen.

    0
    Comment actions Permalink
  • Avatar
    Maxim Krasikov

    Hi @Christopher-James Sheppard,

    There is no solution to roll-back MySQL to the previous version. In case of the upgrade failure, it is necessary to find the cause of the failure and finish the upgrade.

    Please note that it is strongly recommended to create a server snapshot before initializing MySQL upgrade just in case.

    Also, if you prefer that the upgrade is performed by Plesk professionals, I suggest you contact our Professional Services Team and discuss this task with them: https://www.plesk.com/professional-services/ . In this case, it will be performed on a paid basis.

    0
    Comment actions Permalink
  • Avatar
    Konstantin Annikov

    @Keith, 

    Thank you for the input. I have added this information to article. 

    0
    Comment actions Permalink
  • Avatar
    Mitchell van Bijleveld

    Why don't you offer a Graphical User Interface for this?

    0
    Comment actions Permalink
  • Avatar
    Ivan Postnikov

    Hello Rashid Qureshi,

    Most probably, you haven't executed this command at the end:

    # plesk sbin packagemng -sdf

     

    0
    Comment actions Permalink
  • Avatar
    Andreu

    Hello,
    I am trying to upgrade to mariadb 10.4 from Plesk Obsidian 18.0.30 on debian 9 and after adding the command # curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version=mariadb-10.4 get the following error: curl: (23) Failed writing body (395 != 1369)
    What could this be due to?

    Thank you

    0
    Comment actions Permalink
  • Avatar
    Markus

    Thanks a lot for answering, Anton Maslov. It's too late. I already performed the upgrade, seems to be successful. I performed the steps from the tutorial above. Apart from the install notice "Fatal error..." everything is running fine on 10.5.5. I had the same problem by upgrading from 10.2 to 10.3. So, if nothing bad will happen I'll run it until I change my server next year after three/four years.

    Greets

    0
    Comment actions Permalink
  • Avatar
    Necro (Edited )

    Hi there!

    Thanks for providing this, i used to update MariaDB 5.5.65 on CentOS 7 to MariaDB 10.3, latest stable supported by Plesk.

    Everything went smoothly and no errors so far, however i didn't understand what you mean on the last step:

    Note: After an upgrade, there may appear 'mysql' init script. It should be removed to avoid conflicts with MariaDB service. The same commands can be used to fix already existing conflict.

    What is " 'mysql' init script", is it an error message that appears on the SSH window, on Plesk, on an error log?

    Should i run those final steps anyway?

    systemctl stop mysql; killall mysqld # to stop the incorrect service if it is started
    # rm /etc/init.d/mysql && systemctl daemon-reload # to remove the incorrect service script and reload systemctl configuration
    # systemctl start mariadb # to start MariaDB if not started
    # systemctl enable mariadb # to make sure that MariaDB will start after the server reboot automatically

    Thanks!

    0
    Comment actions Permalink
  • Avatar
    Nikita Nikushkin

    Hi @WeHoDo,

    Glad to hear that the issue was resolved!

    Usually, the changes are reflected in Plesk immediately. Maybe the website page was cached and, thus, showed the incorrect info regarding the MariaDB version

    0
    Comment actions Permalink
  • Avatar
    Domenico

    @Alisa,

    thanks for your kind reply, I tried several times to reinstall plesk, in yum.log I see different Erased: plesk ... I made the commands you indicated, part of the installation but crashes with this error:


    Checking for the group 'psaserv'...
    Trying to add supplementary group 'psaserv' for user 'psaadm'... already there
    Checking for the group 'psacln'...
    Group 'psacln' already exists

    Checking that /usr/local/psa/bin/chrootsh registered as login shell...
    /usr/local/psa/bin/chrootsh already registered as a login shell


    ===> Performing safe prep-install database actions

    Trying to start service mysql... active
    done
    Trying to establish test connection... ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES)
    ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES)

    failed

    ERROR while trying to establish test connection
    Check the error reason(see log file: /var/log/plesk/install/plesk_17.8.11_installation.log), fix and try again

    ***** problem report *****
    ERROR while trying to establish test connection
    Check the error reason(see log file: /var/log/plesk/install/plesk_17.8.11_installation.log), fix and try again
    sh: /etc/init.d/psa: No such file or directory

    ERROR: An error occurred during performing of installation PREP actions (see log for details). Installation was rolled back.
    Warning! Not all packages were installed.
    Please check if your system services are operable.
    Please resolve this issue and try to install the packages again.
    If you cannot resolve the issue on your own, contact product technical support for assistance.

    0
    Comment actions Permalink
  • Avatar
    Kingsley Felix

    I am on debian 9 and i wish to upgrade mariadb to 10.3

    0
    Comment actions Permalink

Please sign in to leave a comment.

Have more questions? Submit a request