Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

Backup all MySQL Databases?

Discussion in 'Data Protection' started by keat63, Feb 27, 2019.

Tags:
  1. keat63

    keat63 Well-Known Member

    Joined:
    Nov 20, 2014
    Messages:
    1,209
    Likes Received:
    77
    Trophy Points:
    28
    cPanel Access Level:
    Root Administrator
    I'm about to update MYSQL at the weekend.
    Is there a way I can perform a backup of just the databases.

    I can't do this as an export through PHP MyAdmin, as I suffer timeouts or memory space issues.
     
    #1 keat63, Feb 27, 2019
  2. rpvw

    rpvw Well-Known Member

    Joined:
    Jul 18, 2013
    Messages:
    1,088
    Likes Received:
    441
    Trophy Points:
    113
    Location:
    Spain
    cPanel Access Level:
    Root Administrator
    Backup all MySQL Databases to individual files
    Code:
    for I in $(mysql -e 'show databases' -s --skip-column-names); do mysqldump $I | gzip > "$I.sql.gz"; done
    Backup everything to one file
    Code:
    mysqldump --all-databases > all_databases.sql
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #2 rpvw, Feb 27, 2019
  3. GOT

    GOT Get Proactive! PartnerNOC

    Joined:
    Apr 8, 2003
    Messages:
    1,372
    Likes Received:
    154
    Trophy Points:
    193
    Location:
    Chesapeake, VA
    cPanel Access Level:
    DataCenter Provider
    The previous reply is a good way to go A alternative if you think you may need to roll back the mysql version is to make a copy of the mysql data files To do this properly you have to take several steps.

    Go into service manager and uncheck monitor for mysql

    service stop mysql

    cp -rav /var/lib/mysql /var/lib/mysql.bak

    service mysql start

    Then you can perform the upgrade.

    Rolling it back is a whole different process but this would allow for a speedy roll back if it was required.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #3 GOT, Feb 27, 2019
    rpvw likes this.
  4. keat63

    keat63 Well-Known Member

    Joined:
    Nov 20, 2014
    Messages:
    1,209
    Likes Received:
    77
    Trophy Points:
    28
    cPanel Access Level:
    Root Administrator
    Is it really this simple or am I being nieve.

    If I FTP off the server var/lib/mysql
    Perform the upgrade.

    If anything goes wrong, roll back to MYSQL 5.6 and ftp the folder back up. ?

    I just did a dummy run on the ftp and it only took 5 minutes.
     
    #4 keat63, Feb 27, 2019
  5. GOT

    GOT Get Proactive! PartnerNOC

    Joined:
    Apr 8, 2003
    Messages:
    1,372
    Likes Received:
    154
    Trophy Points:
    193
    Location:
    Chesapeake, VA
    cPanel Access Level:
    DataCenter Provider
    The key here is that when you copy or download the files whichever it is mysql has to not be running
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #5 GOT, Feb 27, 2019
  6. keat63

    keat63 Well-Known Member

    Joined:
    Nov 20, 2014
    Messages:
    1,209
    Likes Received:
    77
    Trophy Points:
    28
    cPanel Access Level:
    Root Administrator
    And no doubt I'll have to shutdown apache to stop new data being added by web site users.
     
    #6 keat63, Feb 27, 2019
  7. GOT

    GOT Get Proactive! PartnerNOC

    Joined:
    Apr 8, 2003
    Messages:
    1,372
    Likes Received:
    154
    Trophy Points:
    193
    Location:
    Chesapeake, VA
    cPanel Access Level:
    DataCenter Provider
    No you just need to have the mysql service off.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #7 GOT, Feb 27, 2019
  8. keat63

    keat63 Well-Known Member

    Joined:
    Nov 20, 2014
    Messages:
    1,209
    Likes Received:
    77
    Trophy Points:
    28
    cPanel Access Level:
    Root Administrator
    doh... of course.
     
    #8 keat63, Feb 27, 2019
  9. cPanelMichael

    cPanelMichael Technical Support Community Manager Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    47,009
    Likes Received:
    2,123
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    Hello @keat63,

    One aspect of the upgrade to keep in-mind is that downgrading back to MySQL version 5.6 isn't a supported action if the MySQL upgrade succeeds (if the upgrade fails then MySQL 5.6 will remain installed).

    Thank you.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #9 cPanelMichael, Feb 27, 2019
  10. keat63

    keat63 Well-Known Member

    Joined:
    Nov 20, 2014
    Messages:
    1,209
    Likes Received:
    77
    Trophy Points:
    28
    cPanel Access Level:
    Root Administrator
    Thats reassuring and good to know.
     
    #10 keat63, Feb 28, 2019
  11. keat63

    keat63 Well-Known Member

    Joined:
    Nov 20, 2014
    Messages:
    1,209
    Likes Received:
    77
    Trophy Points:
    28
    cPanel Access Level:
    Root Administrator
    The update from MySQL 5.6 to 5.7 appears to have gone without a hitch (finger crossed), and went a lot quicker than I expected.
    Thanks for the info.

    One thing I picked up on though.
    After the update, I tried to stop the MySql service to update my.cnf (to disable strict mode), but the command 'service mysql stop' comes back with an error saying unrecognised service.

    Any ideas ?
     
    #11 keat63, Mar 1, 2019
  12. Tiberiu

    Tiberiu Registered

    Joined:
    Oct 18, 2016
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    Bistrita
    cPanel Access Level:
    Root Administrator
    Dont stop Cpanel services by running service xxx stop, instead you should run /scripts/restartsrv_service --stop.
    Dont forget do disable mysql from tailwatch from WHM > service manager otherwise it will restart your service if it detects its down.
     
    #12 Tiberiu, Mar 1, 2019
  13. keat63

    keat63 Well-Known Member

    Joined:
    Nov 20, 2014
    Messages:
    1,209
    Likes Received:
    77
    Trophy Points:
    28
    cPanel Access Level:
    Root Administrator
    Prior to the update, I disabled tailwatch, then via terminal I ran 'service mysql stop'
    This echo'd back that the service had stopped.
    I took my backups, then retstarted mysql before running the update process.

    The update appears to have gone without a hitch.
    Next I modified 'my.cnf' to disable strict mode, but this time, I was unable to stop the mysql service using 'service mysql stop'
    Instead I restarted my sql via the whm gui.

    Just wondered why it would stop when at 5.6 but not when at 5.7.
     
    #13 keat63, Mar 4, 2019
  14. cPanelMichael

    cPanelMichael Technical Support Community Manager Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    47,009
    Likes Received:
    2,123
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    Hello @keat63,

    MySQL version 5.7 comes with native support for systemd:

    MySQL :: MySQL 5.7 Reference Manual :: 2.5.10 Managing MySQL Server with systemd

    You should always restart the service using /scripts/restartsrv_mysql, however if for some reason it doesn't work and you need to restart the service directly, use the following command (on CentOS 7):

    Code:
    systemctl restart mysqld
    The service command will automatically redirect to the corresponding systemctl command, but as of MySQL 5.7 you need to enter "mysqld" as the service name instead of "mysql".

    Thank you.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #14 cPanelMichael, Mar 4, 2019
(You must log in or sign up to post here.)
Loading...
Similar Threads - Backup MySQL Databases
  1. VJIADIS

    mysql databases (only) backup

    VJIADIS, Jan 18, 2018, in forum: Data Protection
    Replies:
    1
    Views:
    808
    cPanelMichael
    Jan 19, 2018
  2. 2Pro4u

    Would a backup of the cPanel partition be enough if mysql upgrade goes wrong?

    2Pro4u, Apr 15, 2019, in forum: Database Discussion
    Replies:
    1
    Views:
    165
    cPanelMichael
    Apr 17, 2019
  3. cosmin

    Additional MySQL backup during the day

    cosmin, Mar 13, 2019, in forum: Database Discussion
    Replies:
    1
    Views:
    182
    cPanelLauren
    Mar 13, 2019
  4. greektranslator

    Upgrade MariaDB to 10.3: backup without mysqldump?

    greektranslator, Jan 16, 2019, in forum: Database Discussion
    Replies:
    7
    Views:
    837
    cPanelMichael
    Mar 15, 2019
  5. MHD_MUS

    cPanel mysql backup Error 1045

    MHD_MUS, Dec 7, 2018, in forum: Database Discussion
    Replies:
    3
    Views:
    334
    cPanelLauren
    Dec 10, 2018

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Accept Learn More...
    Dismiss Notice