Failed to make backup of database because mysqldump failed

Discussion in 'ISPConfig 3 Priority Support' started by rezz, Oct 31, 2023.

Tags:
  1. rezz

    rezz New Member HowtoForge Supporter

    Hello,
    I recently migrated a server using the migration tool and then days later I needed to move the updated DB over to the new host so I dumped all db's using mysqldump and then imported the dump on the new host.

    There were some issues (mainly needed to update passwords used by ispconfig) but I have since corrected them.
    The only problem that is lingering is that automated and manual db backups are failing.
    I've confirmed the correct credentials exist on /usr/local/ispconfig/server/lib/mysql_clientdb.conf.
    I have also done a force update using the update script.
    I've looked at any file I could find which contains a sql password and it doesnt seem to be the issue...
    running mysqldump via cli also works without issue...

    Does anyone have an idea as to why db backups might be failing? Thanks for your help!
     
    rezz, Oct 31, 2023
    #1
  2. Th0m

    Th0m ISPConfig Developer Staff Member ISPConfig Developer

    It would have been better to let the migration tool take care of copying over the database, it can also resync data after migration has been done... For future migrations ;)

    Do you have any errors in your log regarding the failure of the backups?
     
    Th0m, Oct 31, 2023
    #2
    rezz likes this.
  3. rezz

    rezz New Member HowtoForge Supporter

    oh, I didn't realize it could resync! Thanks for letting me know, I actually plan on migrating a different instance as well.

    The only error shown in ispconfig log is
    Code:
    Failed to make backup of database dbname, because mysqldump failed
    I've looked for mysql logs in /var/log/mysql/ but there are none in there.
     
    rezz, Oct 31, 2023
    #3
  4. Th0m

    Th0m ISPConfig Developer Staff Member ISPConfig Developer

    Th0m, Oct 31, 2023
    #4
    rezz likes this.
  5. rezz

    rezz New Member HowtoForge Supporter

    Just tried that and then tried to manually do the backup but I'm still getting the same error.
    Not sure if this helps, but web backup works fine, DB is the only one affected.
     
    rezz, Oct 31, 2023
    #5
  6. Th0m

    Th0m ISPConfig Developer Staff Member ISPConfig Developer

    Can you check if you can login with the username and password in /usr/local/ispconfig/server/lib/config.inc.php?
     
    Th0m, Oct 31, 2023
    #6
  7. rezz

    rezz New Member HowtoForge Supporter

    Yup, I can log in using the credentials in the "//** Database" section.
    db dbispconfig
    user ispconfig
     
    rezz, Oct 31, 2023
    #7
  8. till

    till Super Moderator Staff Member ISPConfig Developer

    Please test if you can login to MySQL with the details from /usr/local/ispconfig/server/lib/mysql_clientdb.conf using localhost as well as 127.0.0.1. Maybe one of them fails:

    mysql -h localhost -u root -p

    and

    mysql -h 127.0.0.1 -u root -p

    with the password from /usr/local/ispconfig/server/lib/mysql_clientdb.conf should both work.
     
    till, Nov 1, 2023
    #8
  9. rezz

    rezz New Member HowtoForge Supporter

    Hey Till,
    Just tested both of those and they both logged me in just fine (used password from /usr/local/ispconfig/server/lib/mysql_clientdb.conf)
     
    rezz, Nov 1, 2023
    #9
  10. till

    till Super Moderator Staff Member ISPConfig Developer

    Please edit the file /usr/local/ispconfig/server/lib/classes/backup.inc.php, before doing this, please make a backup copy. Replace line 1835:

    Code:
    $app->log('Failed to make backup of database ' . $rec['database_name'] . ', because mysqldump failed', LOGLEVEL_ERROR);
    with:

    Code:
    $app->log("mysqldump -h $clientdb_host -u $clientdb_user -p$clientdb_password -c --add-drop-table --create-options --quick --max_allowed_packet=512M " . $mysqldump_routines . " --result-file=".$db_backup_dir . '/' . $db_backup_file." $db_name", LOGLEVEL_ERROR);
    Then do a backup, you should get the whole command instead of the generic failed error message then in the log. Just to be aware, the log will contain the mysql root password then. The command that is run will be still a little bit different then because the parameters get correctly quoted in the real code.
     
    till, Nov 1, 2023
    #10
  11. rezz

    rezz New Member HowtoForge Supporter

    Here's the output
    Code:
    mysqldump -h localhost -u root -pDBPASS -c --add-drop-table --create-options --quick --max_allowed_packet=512M --routines --result-file=/var/backup/web3/db_DBNAME_2023-11-01_12-39.sql DBNAME
    Should I try to run this in cli?
     
    rezz, Nov 1, 2023
    #11
  12. till

    till Super Moderator Staff Member ISPConfig Developer

    Yes, please run it in cli to see what the error message is.
     
    till, Nov 1, 2023
    #12
  13. rezz

    rezz New Member HowtoForge Supporter

    So the password contains ) and it appears it does not like this.
    Code:
    bash: syntax error near unexpected token `)'
    Odd because I didn't have this issue with the old server

    I'll change the password and will try again. Will report back, thank you for your help @till @Th0m !
     
    Last edited: Nov 1, 2023
    rezz, Nov 1, 2023
    #13
  14. till

    till Super Moderator Staff Member ISPConfig Developer

    This should not be a problem. As I mentioned, the parameters of the executed command are quoted, so the password gets put in single quotes. You can try to mimic it like this:

    Code:
    mysqldump -h localhost -u root -p'DBPASS' -c --add-drop-table --create-options --quick --max_allowed_packet=512M --routines --result-file=/var/backup/web3/db_DBNAME_2023-11-01_12-39.sql DBNAME
     
    till, Nov 1, 2023
    #14
  15. rezz

    rezz New Member HowtoForge Supporter

    This is the exact output that I got though, password was not in single quotes. When I ran it as it was outputted, I received an error about the parenthesis
     
    rezz, Nov 1, 2023
    #15
  16. till

    till Super Moderator Staff Member ISPConfig Developer

    That's what I explained in post #10: "The command that is run will be still a little bit different then because the parameters get correctly quoted in the real code."
     
    till, Nov 1, 2023
    #16
  17. rezz

    rezz New Member HowtoForge Supporter

    Apologies, I missed that line!

    OK I see the actual error is
    Code:
    Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'DBNAME'': Cannot load from mysql.proc. The table is probably corrupted (1728)
    I will try to run
    Code:
    mysql_upgrade -u root -p
    later tonight to try and correct it
     
    Last edited: Nov 1, 2023
    rezz, Nov 1, 2023
    #17
  18. till

    till Super Moderator Staff Member ISPConfig Developer

    You probably restored mysql 'mysql' database of an older version. Try to upgrade it with:

    Code:
    mysql_upgrade -u root -p
     
    till, Nov 1, 2023
    #18
    Th0m and rezz like this.
  19. rezz

    rezz New Member HowtoForge Supporter

    I had to do
    Code:
    mysql_upgrade -u root -p --force
    since it mentioned "This installation of MariaDB is already upgraded to 10.6.12-MariaDB" but it worked after I forced, thank you guys so much for your help!
     
    rezz, Nov 2, 2023
    #19
    jerocobo, Th0m and till like this.

Share This Page