Mysqldump failure on automatic backups
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.
-
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 0 -
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? 0 -
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! 0 -
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. 0 -
Hello @janipewter You could also modify the backup schedule to set it to run or run it manually by executing the following: /usr/local/cpanel/bin/backup --force
Thanks!0 -
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. 0 -
Hi @janipewter I am curious about the error you're receiving: 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:ls -lah /var/lib/mysql
Thanks!0 -
[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 0 -
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)
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 commandscat /var/lib/mysql/mysql_upgrade_info
mysqladmin ver0 -
Hi @janipewter This is what I was looking for: 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/var/lib/mysql/.ssh
Thanks!0 -
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
cat /var/lib/mysql/mysql_upgrade_info
mysqladmin ver
[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.381Hi @janipewter This is what I was looking for:
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/var/lib/mysql/.ssh
Thanks!
Will try this and report back. Thanks.0 -
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. 0 -
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: cat /var/lib/mysql/mysql_upgrade_info 10.2.17-MariaDB
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:mysql_upgrade --force
was a resolution to the issue0 -
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. 0 -
Hi @janipewter Ok, that sounds good. Thanks! 0 -
Can confirm that this has resolved the issue. Thanks for the help everyone. Great support as always from cPanel! 0 -
Hi @janipewter I'm glad it's resolved the issue! Thank you so much for updating here and letting us know. 0
Please sign in to leave a comment.
Comments
17 comments