The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

upgrade mysql

Discussion in 'Database Discussions' started by arjanvr, Apr 4, 2017.

  1. arjanvr

    arjanvr Well-Known Member

    Joined:
    Dec 13, 2013
    Messages:
    101
    Likes Received:
    1
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Hello,

    I need to upgrade mysql as it's currently on 5.5.54 while wordpress advises 5.5.6. Now i found that you can upgrade though software -> upgrade mysql. I have a few questions before i do that.

    1. I understand its the best way to upgrade through software but how do i make a backup of the current mysql files and where will it be stored?
    2. Should something go wrong, how do i restore that backup?
    3. My mysql is on a seperate SSD drive for improved speed. Will this cause issues that it's not on the same HDD as the rest of the files, when upgrading, or wil it make no difference?
    4. I see i can also click to upgrade to mariadb 10. Can i simply do that with one click, can this cause issues or will it work smooth and would you recommend it?

    Thank you
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    37,021
    Likes Received:
    1,276
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Hello @arjanvr,

    You can use the mysqldump utility to backup all databases to the location of your preference on the system (e.g. /backup). Here's a third-party URL where this is discussed:

    Export and Import all MySQL databases at one time

    You could import a backup of the databases per the instructions in the URL above.

    This should not make a difference.

    Yes, you would use the same steps when upgrading to MariaDB. MariaDB 10.0 is recommended.

    Thank you.
     
  3. arjanvr

    arjanvr Well-Known Member

    Joined:
    Dec 13, 2013
    Messages:
    101
    Likes Received:
    1
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Thank you. Since you say mariadb is prefered, is it now supported by cpanel. Andere is it really as simple as upgrading mysql? Will it keep the made changes
     
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    37,021
    Likes Received:
    1,276
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Hello,

    Yes, MariaDB is supported. You should be able to simply use the MySQL or MariaDB Upgrade option in WHM without manual intervention.

    Thank you.
     
  5. arjanvr

    arjanvr Well-Known Member

    Joined:
    Dec 13, 2013
    Messages:
    101
    Likes Received:
    1
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    "You can use the mysqldump utility to backup all databases to the location of your preference on the system (e.g. /backup). Here's a third-party URL where this is discussed:

    Export and Import all MySQL databases at one time"

    So what i gather this is the command is enough to back it up
    mysqldump -u root -p --all-databases > alldb.sql

    and this to restore?
    mysql -u root -p < alldb.sql

    That leaves me with one problem, it asks for a password when i want to back it up.. and it does not accept root password?
     
  6. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    37,021
    Likes Received:
    1,276
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    You can exclude the "-p" and "-u" flags if you are logged in via SSH as root. EX:

    Code:
    mysqldump --all-databases > /path/to/alldb.sql
    Thank you.
     
  7. arjanvr

    arjanvr Well-Known Member

    Joined:
    Dec 13, 2013
    Messages:
    101
    Likes Received:
    1
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Sorry about my lack of knowledge about this subject but i did a search on alldb.sql using the command

    find / -xdev -name alldb.sql

    and it shows the file in /root
    However, the file is 0 in size so it's empty and created today. Now to clearify that what i think you meant is correct.

    The command you mention
    mysqldump --all-databases > /path/to/alldb.sql
    I thought i was replacing /path/to/alldb.sql is, but i think what you mean is that is the path where the file alldb.sql will be created?

    Just to be sure. I rater not mess up as we have to update production vps server(s)
     
  8. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    37,021
    Likes Received:
    1,276
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Hello,

    Yes, that's the path and file name that's created when running the command. You'd replace it with the path and name of your preference.

    Thank you.
     
  9. arjanvr

    arjanvr Well-Known Member

    Joined:
    Dec 13, 2013
    Messages:
    101
    Likes Received:
    1
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    On one vps where i upgraded my mariadb it went fine but on my main vps it immidiately get this error when attempting to make a backup?

    [~]# mysqldump --all-databases > /root/alldb.sql
    mysqldump: Got error: 1102: Incorrect database name '#mysql50#.cpanel' when selecting the database
    [~]#

    any clues?
     
  10. arjanvr

    arjanvr Well-Known Member

    Joined:
    Dec 13, 2013
    Messages:
    101
    Likes Received:
    1
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Found the answer here
    Incorrect database name

    "It was a hidden .cpanel dir in /var/lib/mysql
    Deleting the hidden forlder fix my issue"
     
  11. arjanvr

    arjanvr Well-Known Member

    Joined:
    Dec 13, 2013
    Messages:
    101
    Likes Received:
    1
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Is this going to be an issue?

    -- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
     
  12. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    37,021
    Likes Received:
    1,276
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Hello,

    You could add the "events" flag to the command. EX:

    Code:
    mysqldump --all-databases --events > /path/to/alldb.sql
    Thank you.
     
Loading...

Share This Page