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!

SOLVED Mysqldump failure on automatic backups

Discussion in 'Database Discussion' started by janipewter, Sep 13, 2018.

  1. janipewter

    janipewter Active Member

    Joined:
    Jan 2, 2013
    Messages:
    41
    Likes Received:
    3
    Trophy Points:
    8
    cPanel Access Level:
    Root Administrator
    Recently my WHM automatic backups have been completing with errors. I get this in the email:

    The backup process completed, but 10 errors occurred.

    [2018-09-13 02:05:09 +0100] removed_1: mysqldump failed -- database may be corrupt
    [2018-09-13 02:05:21 +0100] removed_2: mysqldump failed -- database may be corrupt
    [2018-09-13 02:08:17 +0100] removed_3: mysqldump failed -- database may be corrupt
    [2018-09-13 02:08:35 +0100] removed_4: mysqldump failed -- database may be corrupt
    [2018-09-13 02:25:10 +0100] removed_5: mysqldump failed -- database may be corrupt
    [2018-09-13 02:25:23 +0100] removed_6: mysqldump failed -- database may be corrupt
    [2018-09-13 02:25:24 +0100] removed_7: mysqldump failed -- database may be corrupt
    [2018-09-13 02:25:24 +0100] removed_8: mysqldump failed -- database may be corrupt
    [2018-09-13 08:28:12 +0100] removed_9: mysqldump failed -- database may be corrupt
    [2018-09-13 08:32:55 +0100] removed_10: mysqldump failed -- database may be corrupt

    I have removed all of the names of the databases, but they are spread over multiple accounts on my server. All of the databases are in use and working perfectly. I never had this error until recently, when it suddenly started on all 10 of these databases. I have tried repairing them with phpmyadmin but it says no errors found. One of the databases was only created 2 weeks ago and is definitely not corrupt.
     
  2. sysnishit

    sysnishit Active Member

    Joined:
    Aug 13, 2018
    Messages:
    25
    Likes Received:
    3
    Trophy Points:
    3
    Location:
    Internet
    cPanel Access Level:
    Root Administrator
    Hello,

    You can use mysqlcheck -r databasename to check if databases / database tables are corrupted or not. Also, Try to backup mysql database from command line using mysqldump command

    cd /home
    mysqldump database_name > database_name.sql
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  3. janipewter

    janipewter Active Member

    Joined:
    Jan 2, 2013
    Messages:
    41
    Likes Received:
    3
    Trophy Points:
    8
    cPanel Access Level:
    Root Administrator
    Thanks for your reply synsnishit. I tried the mysqlcheck on all of the databases and they were all OK, nothing to repair. A couple of the databases showed this message for some tables:

    The storage engine for the table doesn't support repair

    But they are all still working fine.

    I tried using mysqldump to make a backup of my biggest database and it worked perfectly.

    Any other suggestions?
     
  4. cPanelLauren

    cPanelLauren Forums Analyst II Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    5,733
    Likes Received:
    436
    Trophy Points:
    233
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    Hi @janipewter

    After running mysqlcheck on the databases are you still getting an error during automated backups (regardless of whether or not errors were fixed)

    Furthermore, do you have anything in relation to these databases being noted in the mysql error logs?

    Thanks!
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  5. janipewter

    janipewter Active Member

    Joined:
    Jan 2, 2013
    Messages:
    41
    Likes Received:
    3
    Trophy Points:
    8
    cPanel Access Level:
    Root Administrator
    Won't be able to tell until the next automatic backup runs on Wednesday night. For now I am doing manual backups of the affected accounts and there are no errors with those. The databases are in tact.

    There are no mysql error logs either.
     
  6. cPanelLauren

    cPanelLauren Forums Analyst II Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    5,733
    Likes Received:
    436
    Trophy Points:
    233
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    Hello @janipewter

    You could also modify the backup schedule to set it to run or run it manually by executing the following:

    Code:
    /usr/local/cpanel/bin/backup --force
    Thanks!
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  7. janipewter

    janipewter Active Member

    Joined:
    Jan 2, 2013
    Messages:
    41
    Likes Received:
    3
    Trophy Points:
    8
    cPanel Access Level:
    Root Administrator
    OK, I started the backup manually using that command. Watching the log output as it's going I've already seen this:

    exec(/usr/bin/mysqldump,--complete-insert --quote-names --quick --single-transaction --default-character-set=utf8mb4 --events --routines --triggers --routines -- database_name_removed) exited with error: The “/usr/bin/mysqldump --complete-insert --quote-names --quick --single-transaction --default-character-set=utf8mb4 --events --routines --triggers --routines -- database_name_removed” command (process 6851) reported error number 2 when it ended. at /usr/local/cpanel/Cpanel/Pkgacct.pm line 443.
    [2018-09-18 02:31:46 +0100] database_name_removed: mysqldump: Couldn't execute 'show events': Cannot proceed because system tables used by Event Scheduler were found damaged at server start (1577)
    [2018-09-18 02:31:46 +0100] database_name_removed: mysqldump failed -- database may be corrupt
    [2018-09-18 02:31:46 +0100] (7493 bytes)
    [2018-09-18 02:31:46 +0100] Failed to dump database database_name_removed: The subprocess reported error number 2 when it ended.
    [2018-09-18 02:31:46 +0100]
    [2018-09-18 02:31:46 +0100] ERROR: Failed to dump one or more databases

    Once again I have removed the database name myself. It seems that the problem isn't fixed.
     
  8. cPanelLauren

    cPanelLauren Forums Analyst II Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    5,733
    Likes Received:
    436
    Trophy Points:
    233
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    Hi @janipewter

    I am curious about the error you're receiving:

    Code:
    mysqldump: Couldn't execute 'show events': Cannot proceed because system tables used by Event Scheduler were found damaged at server start
    Can you run the following and provide the output:

    Code:
    ls -lah /var/lib/mysql
    Thanks!
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  9. janipewter

    janipewter Active Member

    Joined:
    Jan 2, 2013
    Messages:
    41
    Likes Received:
    3
    Trophy Points:
    8
    cPanel Access Level:
    Root Administrator
    [root@srv01 ~]# ls -lah /var/lib/mysql
    total 177M
    drwxr-x--x 38 mysql mysql 4.0K Sep 9 14:42 .
    drwxr-xr-x. 24 root root 4.0K Sep 18 04:33 ..
    -rw-rw---- 1 mysql mysql 16K Sep 16 20:09 aria_log.00000001
    -rw-rw---- 1 mysql mysql 52 Sep 16 20:09 aria_log_control
    -rw-rw---- 1 mysql mysql 56 Apr 27 2017 auto.cnf
    drwx------ 2 mysql mysql 4.0K Sep 18 02:25 db_name_removed_1
    drwx------ 2 mysql mysql 4.0K Apr 27 2017 cphulkd
    -rw-rw---- 1 mysql mysql 5.2K Apr 27 2017 hlgh001.hlgh.local.err
    -rw-rw---- 1 mysql mysql 5.3K Apr 27 2017 HLGH001.HLGH.local.err
    -rw-rw---- 1 mysql mysql 76M Sep 18 23:47 ibdata1
    -rw-rw---- 1 mysql mysql 48M Sep 18 23:47 ib_logfile0
    -rw-rw---- 1 mysql mysql 48M Sep 16 17:11 ib_logfile1
    drwx------ 2 mysql mysql 4.0K Sep 18 06:24 db_name_removed_2
    drwx------ 2 mysql mysql 4.0K Sep 18 06:23 db_name_removed_3
    drwx------ 2 mysql mysql 12K Mar 16 2018 db_name_removed_4
    -rw-rw---- 1 mysql mysql 0 Apr 26 00:11 multi-master.info
    drwx--x--x 2 mysql mysql 4.0K Nov 6 2017 mysql
    srwxrwxrwx 1 mysql mysql 0 Sep 9 14:42 mysql.sock
    -rw-r--r-- 1 mysql mysql 6 Nov 6 2017 mysql_upgrade_info
    drwx------ 2 mysql mysql 4.0K Jan 11 2018 db_name_removed_5
    drwx------ 2 mysql mysql 32K Sep 26 2017 db_name_removed_6
    drwx------ 2 mysql mysql 4.0K Nov 6 2017 performance_schema
    drwx------ 2 mysql mysql 40K Oct 27 2017 db_name_removed_7
    drwx------ 2 mysql mysql 4.0K Sep 18 06:23 db_name_removed_8
    -rw-r--r-- 1 mysql mysql 2.3K Nov 6 2017 RPM_UPGRADE_HISTORY
    -rw-r--r-- 1 mysql mysql 1.1K Nov 6 2017 RPM_UPGRADE_MARKER-LAST
    drwx------ 2 mysql mysql 4.0K Feb 12 2018 db_name_removed_9
    drwx------ 2 mysql mysql 12K Sep 18 06:24 db_name_removed_10
    drwx------ 2 mysql mysql 4.0K Apr 28 2017 db_name_removed_11
    drwx------ 2 mysql mysql 4.0K Apr 28 2017 db_name_removed_12
    drwx------ 2 mysql mysql 4.0K Apr 28 2017 db_name_removed_13
    drwx------ 2 mysql mysql 4.0K Apr 28 2017 db_name_removed_14
    drwx------ 2 mysql mysql 4.0K Apr 28 2017 db_name_removed_15
    drwx------ 2 mysql mysql 4.0K Apr 28 2017 db_name_removed_16
    drwx------ 2 mysql mysql 12K Apr 28 2017 db_name_removed_17
    drwx------ 2 mysql mysql 28K Apr 28 2017 db_name_removed_18
    drwx------ 2 mysql mysql 4.0K Apr 28 2017 db_name_removed_19
    drwx------ 2 mysql mysql 4.0K Apr 28 2017 db_name_removed_20
    drwx------ 2 mysql mysql 4.0K Apr 28 2017 db_name_removed_21
    drwx------ 2 mysql mysql 4.0K Apr 28 2017 db_name_removed_22
    drwx------ 2 mysql mysql 4.0K Sep 12 03:07 db_name_removed_23
    drwx------ 2 mysql mysql 4.0K Apr 28 2017 db_name_removed_24
    drwx------ 2 mysql mysql 12K Apr 28 2017 db_name_removed_25
    drwx------ 2 mysql mysql 4.0K Apr 28 2017 db_name_removed_26
    drwx------ 2 mysql mysql 12K Apr 28 2017 db_name_removed_27
    drwx------ 2 mysql mysql 4.0K Sep 18 02:31 db_name_removed_28
    drwx------ 2 mysql mysql 4.0K Sep 1 21:00 db_name_removed_29
    drwx------ 2 mysql mysql 12K Jul 25 22:01 db_name_removed_30
    drwx------ 2 mysql mysql 4.0K Sep 18 02:31 db_name_removed_31
    -rw-rw---- 1 mysql mysql 3.8M Sep 18 20:40 server_hostname_removed.err
    -rw-rw---- 1 mysql mysql 5 Sep 9 14:42 server_hostname_removed.pid
    drwx------ 2 mysql mysql 4.0K Apr 16 18:11 .ssh
    -rw-rw---- 1 mysql mysql 24K Sep 9 14:42 tc.log
    drwx------ 2 mysql mysql 4.0K Sep 8 2017 db_name_removed_32
     
  10. eva2000

    eva2000 Well-Known Member

    Joined:
    Aug 14, 2001
    Messages:
    338
    Likes Received:
    15
    Trophy Points:
    318
    Location:
    Brisbane, Australia
    cPanel Access Level:
    Root Administrator
    Twitter:
    sounds like mysql/mariadb server was never properly upgraded if you had done mysql upgrades. Did you make sure to run mysql_upgrade after upgrading mysql/mariadb major versions ?

    what's output for these 2 commands
    Code:
    cat /var/lib/mysql/mysql_upgrade_info
    
    Code:
    mysqladmin ver
    
     
  11. cPanelLauren

    cPanelLauren Forums Analyst II Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    5,733
    Likes Received:
    436
    Trophy Points:
    233
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    Hi @janipewter

    This is what I was looking for:

    Code:
    drwx------ 2 mysql mysql 4.0K Apr 16 18:11 .ssh
    
    I'd also wager that an upgrade attempt would fail because of the directory's presence due to it being misconstrued as a database due to it's location.

    We have an open case that matches the description of what's occurring here CPANEL-21490 - /usr/local/cpanel/scripts/updatesupportauthorizations is creating empty .ssh dir in all homedirs including system users.

    This case is fixed in v76 of cPanel but until the resolution is available on your build the issue should stop if you simply remove
    Code:
    /var/lib/mysql/.ssh

    Thanks!
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    eva2000 likes this.
  12. janipewter

    janipewter Active Member

    Joined:
    Jan 2, 2013
    Messages:
    41
    Likes Received:
    3
    Trophy Points:
    8
    cPanel Access Level:
    Root Administrator
    [root@srv01 ~]# cat /var/lib/mysql/mysql_upgrade_info
    5.6.38
    [root@srv01 ~]# mysqladmin ver
    mysqladmin Ver 9.1 Distrib 10.1.36-MariaDB, for Linux on x86_64
    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

    Server version 10.1.36-MariaDB
    Protocol version 10
    Connection Localhost via UNIX socket
    UNIX socket /var/lib/mysql/mysql.sock
    Uptime: 11 days 1 hour 37 min 17 sec

    Threads: 1 Questions: 16621133 Slow queries: 0 Opens: 4193 Flush tables: 1 Open tables: 1636 Queries per second avg: 17.381


    Will try this and report back. Thanks.
     
  13. janipewter

    janipewter Active Member

    Joined:
    Jan 2, 2013
    Messages:
    41
    Likes Received:
    3
    Trophy Points:
    8
    cPanel Access Level:
    Root Administrator
    Err OK, it still seems to be broken.

    [2018-09-20 16:32:09 +0100] database_name_removed: mysqldump: Couldn't execute 'show events': Cannot proceed because system tables used by Event Scheduler were found damaged at server start (1577)
    exec(/usr/bin/mysqldump,--complete-insert --quote-names --quick --single-transaction --default-character-set=utf8mb4 --events --routines --triggers --routines -- database_name_removed) exited with error: The “/usr/bin/mysqldump --complete-insert --quote-names --quick --single-transaction --default-character-set=utf8mb4 --events --routines --triggers --routines -- database_name_removed” command (process 54547) reported error number 2 when it ended. at /usr/local/cpanel/Cpanel/Pkgacct.pm line 443.
    [2018-09-20 16:32:09 +0100] database_name_removed: mysqldump: Couldn't execute 'show events': Cannot proceed because system tables used by Event Scheduler were found damaged at server start (1577)
    [2018-09-20 16:32:09 +0100] database_name_removed: mysqldump failed -- database may be corrupt
    [2018-09-20 16:32:09 +0100] (864 bytes)
    [2018-09-20 16:32:09 +0100] Failed to dump database database_name_removed: The subprocess reported error number 2 when it ended.
    [2018-09-20 16:32:09 +0100]
    [2018-09-20 16:32:09 +0100] ERROR: Failed to dump one or more databases
    [2018-09-20 16:32:09 +0100] ...Done
    Load watching resumed due to SIGUSR2
    [2018-09-20 16:32:09 +0100] Completed “Mysql” component.
     
  14. cPanelLauren

    cPanelLauren Forums Analyst II Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    5,733
    Likes Received:
    436
    Trophy Points:
    233
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    Hi @janipewter

    I think you may have had two separate issues. The information @eva2000 provided is pointing in the right direction as well. The two should match:

    From my server:
    Code:
    cat /var/lib/mysql/mysql_upgrade_info
    10.2.17-MariaDB
    Code:
    mysqladmin --version
    mysqladmin  Ver 9.1 Distrib 10.2.17-MariaDB, for Linux on x86_64
    We have another case open for the behavior you're seeing here as well CPANEL-22320. In most cases running:
    Code:
    mysql_upgrade --force 
    was a resolution to the issue
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    eva2000 likes this.
  15. janipewter

    janipewter Active Member

    Joined:
    Jan 2, 2013
    Messages:
    41
    Likes Received:
    3
    Trophy Points:
    8
    cPanel Access Level:
    Root Administrator
    OK, have now updated to MariaDB 10.2 and my output for the versions now matches yours. Also did the
    mysql_upgrade --force.

    I will manually start the backup a bit later and see how it goes.
     
  16. cPanelLauren

    cPanelLauren Forums Analyst II Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    5,733
    Likes Received:
    436
    Trophy Points:
    233
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  17. janipewter

    janipewter Active Member

    Joined:
    Jan 2, 2013
    Messages:
    41
    Likes Received:
    3
    Trophy Points:
    8
    cPanel Access Level:
    Root Administrator
    Can confirm that this has resolved the issue. Thanks for the help everyone. Great support as always from cPanel!
     
    cPanelLauren likes this.
  18. cPanelLauren

    cPanelLauren Forums Analyst II Staff Member

    Joined:
    Nov 14, 2017
    Messages:
    5,733
    Likes Received:
    436
    Trophy Points:
    233
    Location:
    Houston
    cPanel Access Level:
    DataCenter Provider
    Hi @janipewter

    I'm glad it's resolved the issue! Thank you so much for updating here and letting us know.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
Loading...

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.
    Dismiss Notice