scheduled database backups not working

Discussion in 'General' started by nhybgtvfr, Feb 14, 2018.

  1. nhybgtvfr

    nhybgtvfr Member

    hi,
    I'm seeing problems with the database backups on a multi-server setup with a central db server.
    the database backup script appears to be working fine, but the actual backup files don't exist.
    this is only happening for databases on a central db server. for clients with a vps, where the db is on the same server as the website, it all seems to be working fine.

    an example failed backup from last night, has the correct entry in the control panels database web_backup table:
    | 17504 | 2 | 22 | mysql | sqlgz | 1518566417 | db_c1x2kkuk_slp_2018-02-14_00-00.sql.gz | 78865 |
    and in the central database servers web_backup table:
    | 8568 | 2 | 22 | mysql | sqlgz | 1518566417 | db_c1x2kkuk_slp_2018-02-14_00-00.sql.gz | 78865 |

    but the backup folder only contains the website files:
    [email protected]:/var/backup/web22# ls -l
    total 507620
    -rwxr-x--- 1 root root 46996383 Mar 22 2016 web22_2016-03-22_00-33.tar.gz
    ...
    -rwxr-x--- 1 root root 47279858 Feb 13 00:27 web22_2018-02-13_00-27.tar.gz
    -rwxr-x--- 1 root root 47279858 Feb 14 00:26 web22_2018-02-14_00-26.tar.gz

    I can run the database backup script manually without any apparent errors: (removed some identical lines to reduce post length)
    php /usr/local/ispconfig/server/cron_debug.php --cronjob=500-backup.inc.php
    Warning: Using a password on the command line interface can be insecure.
    ...
    Warning: Using a password on the command line interface can be insecure.
    Warning: Using a password on the command line interface can be insecure.
    finished.

    after this, the database entries are updated,
    in the control panel database:
    | 17535 | 2 | 22 | mysql | sqlgz | 1518606880 | db_c1x2kkuk_slp_2018-02-14_11-14.sql.gz | 78866 |
    in the database server database:
    | 8582 | 2 | 22 | mysql | sqlgz | 1518606880 | db_c1x2kkuk_slp_2018-02-14_11-14.sql.gz | 78866 |

    and the backup file now exists, so the script does have access to the folder:
    [email protected]:/var/backup/web22# ls -ltr
    total 507700
    -rwxr-x--- 1 root root 46996383 Mar 22 2016 web22_2016-03-22_00-33.tar.gz
    ....
    -rwxr-x--- 1 root root 47279858 Feb 13 00:27 web22_2018-02-13_00-27.tar.gz
    -rwxr-x--- 1 root root 47279858 Feb 14 00:26 web22_2018-02-14_00-26.tar.gz
    -rwxr-x--- 1 root root 78866 Feb 14 11:14 db_c1x2kkuk_slp_2018-02-14_11-14.sql.gz

    this problem occurs with every database on this server with a backup scheduled. manually running the script will successfully create a backup file, the scheduled backup will not create a single database backup file.

    a vps server with MySQL running on it, running the same scheduled backup, to the same shared, mounted drive is working fine:
    [email protected]:/var/backup/web5# ls -ltr
    total 3872192
    -rwxr-x--- 1 root root 763355743 Feb 10 00:01 web5_2018-02-10_00-00.tar.gz
    -rwxr-x--- 1 root root 27366226 Feb 10 00:02 db_c3kooddirect_2018-02-10_00-01.sql.gz
    -rwxr-x--- 1 root root 762846455 Feb 11 00:01 web5_2018-02-11_00-00.tar.gz
    -rwxr-x--- 1 root root 28218943 Feb 11 00:02 db_c3kooddirect_2018-02-11_00-01.sql.gz
    -rwxr-x--- 1 root root 760728606 Feb 12 00:01 web5_2018-02-12_00-00.tar.gz
    -rwxr-x--- 1 root root 29354616 Feb 12 00:02 db_c3kooddirect_2018-02-12_00-01.sql.gz
    -rwxr-x--- 1 root root 763747019 Feb 13 00:02 web5_2018-02-13_00-00.tar.gz
    -rwxr-x--- 1 root root 31013074 Feb 13 00:02 db_c3kooddirect_2018-02-13_00-02.sql.gz
    -rwxr-x--- 1 root root 765705633 Feb 14 00:01 web5_2018-02-14_00-00.tar.gz
    -rwxr-x--- 1 root root 32751605 Feb 14 00:02 db_c3kooddirect_2018-02-14_00-01.sql.gz


    what could be causing this? I've compared the configuration on working and non-working servers and nothing looks different. all file/folder permissions are identical. no errors are being generated, at least none that I can find.

    thanks
    lee.
     
  2. nhybgtvfr

    nhybgtvfr Member

    more information after rechecking the servers this morning.

    after checking the folder that should contain the backups, I noticed the database backup created by manually running the debug cron script was gone:

    -rwxr-x--- 1 root root 47279858 Feb 12 00:27 web22_2018-02-12_00-27.tar.gz
    -rwxr-x--- 1 root root 47279858 Feb 13 00:27 web22_2018-02-13_00-27.tar.gz
    -rwxr-x--- 1 root root 47279858 Feb 14 00:26 web22_2018-02-14_00-26.tar.gz
    -rwxr-x--- 1 root root 47279858 Feb 15 00:28 web22_2018-02-15_00-28.tar.gz

    although the correct entries are in each servers database, including the scheduled overnight db backup:

    control panel:
    +-----------+-----------+------------------+-------------+-------------+------------+-----------------------------------------+----------+
    | backup_id | server_id | parent_domain_id | backup_type | backup_mode | tstamp | filename | filesize |
    +-----------+-----------+------------------+-------------+-------------+------------+-----------------------------------------+----------+
    | 17498 | 6 | 22 | web | rootgz | 1518481646 | web22_2018-02-13_00-27.tar.gz | 47279858 |
    | 17529 | 6 | 22 | web | rootgz | 1518567981 | web22_2018-02-14_00-26.tar.gz | 47279858 |
    | 17535 | 2 | 22 | mysql | sqlgz | 1518606880 | db_c1x2kkuk_slp_2018-02-14_11-14.sql.gz | 78866 |
    | 17549 | 2 | 22 | mysql | sqlgz | 1518652820 | db_c1x2kkuk_slp_2018-02-15_00-00.sql.gz | 78865 |
    | 17574 | 6 | 22 | web | rootgz | 1518654487 | web22_2018-02-15_00-28.tar.gz | 47279858 |
    +-----------+-----------+------------------+-------------+-------------+------------+-----------------------------------------+----------+

    database server:
    +-----------+-----------+------------------+-------------+-------------+------------+-----------------------------------------+----------+
    | backup_id | server_id | parent_domain_id | backup_type | backup_mode | tstamp | filename | filesize |
    +-----------+-----------+------------------+-------------+-------------+------------+-----------------------------------------+----------+
    | 8582 | 2 | 22 | mysql | sqlgz | 1518606880 | db_c1x2kkuk_slp_2018-02-14_11-14.sql.gz | 78866 |
    | 8596 | 2 | 22 | mysql | sqlgz | 1518652820 | db_c1x2kkuk_slp_2018-02-15_00-00.sql.gz | 78865 |
    +-----------+-----------+------------------+-------------+-------------+------------+-----------------------------------------+----------+

    webserver:
    +-----------+-----------+------------------+-------------+-------------+------------+-------------------------------+----------+
    | backup_id | server_id | parent_domain_id | backup_type | backup_mode | tstamp | filename | filesize |
    +-----------+-----------+------------------+-------------+-------------+------------+-------------------------------+----------+
    | 6664 | 6 | 22 | web | rootgz | 1518481646 | web22_2018-02-13_00-27.tar.gz | 47279858 |
    | 6674 | 6 | 22 | web | rootgz | 1518567981 | web22_2018-02-14_00-26.tar.gz | 47279858 |
    | 6684 | 6 | 22 | web | rootgz | 1518654487 | web22_2018-02-15_00-28.tar.gz | 47279858 |
    +-----------+-----------+------------------+-------------+-------------+------------+-------------------------------+----------+

    noting this, I've now tried running the cron_dedug backup script on both the db server and the webserver.
    the db server, again creates the backup file without any problems.
    then running the same backup on the webserver, it runs through creating each website backup, I can see it create the website backup for web22, it moves on to creating the backup file for the next website. whilst it is continuing with the website backups, the database backup file still exists. there's some cleanup running after it's created the backup file for the last website file, it's at this point it deletes all the database backup files.

    it shouldn't be doing that. Admittedly, I haven't examined the scripts themselves yet (just about to look at them), but I don't understand why it would delete them, but the same script run on the database server isn't deleting the website backup files.
     
  3. till

    till Super Moderator Staff Member ISPConfig Developer

    Ensure that each server uses it's own backup dir, so do not store the backups of multiple servers in the same directory. If you have a central storage server for backups, then each server must have it's own backup directory there.
     
  4. florian030

    florian030 ISPConfig Developer ISPConfig Developer

    Or just remove the garbage-collection from the backup-class
     

Share This Page