It looks like our database backups are not running right. When I run zcat db.sql.gz | tail, it seems to end somewhere in the middle of the SQL dump.
To see what’s going on I wanted to look at the backuplogs in /var/webmin/modules/virtual-server/backuplogs, but the most recent files there are from years ago. /etc/webmin/mysql/backup.plis still running every night, per webmin → servers → mariadb server → database backups.
Is there somewhere I need to turn on logging? Or are they not written because it’s running out of space while trying to make the backup on that same partition?
The database is about 62 GB, there is 72 GB left on /home, which it seems to use (~/.virtualmin-src). Is it first writing the dump to disk and then compressing it to the backup target directory (sits on a different partition)?
Also is there a reason the timestamp of the backuplogs files seems off?
On another server it is still logged, but it will show as last modified: 00:16, while the backup process is scheduled for 00:30, and the db archive is indeed timestamped 00:31.
I at least figured out why they’re not being backed up right, now that I told it to email me backup status (that’s a useful thing!). Apparently some views are outdated and causing an error, so I’ll try taking those out. This post suggests that using --ignore_table only works for a single table/view. Are there any updates on that?
I did some research i think cron jobs are logged in /var/log/syslog also. But when it comes to centos 7 its logged in /var/log/cron and i installed centos 7 and tested it and it worked perfectly. All logs are coming. Check rsyslog configuration files in /etc/rsyslog.conf and /etc/rsyslog.d/ to see if there are specific rules directing cron logs to another file.
Yes, cron is logging fine to /var/log/cron, showing Nov 10 00:30:01 horafinita CROND[12094]: (root) CMD (/etc/webmin/mysql/backup.pl --all)
However, I’m more interested in the logs that used to be written to /var/webmin/modules/virtual-server/backuplogs, which would contain more useful information such as
Dumping MySQL database db_prod ..
.. dump failed! cat: write error: No space left on device
mysqldump: Got errno 32 on write
Creating incremental TAR file of home directory ..
.. backup of dir failed : Failed to open /home/prod/.virtualmin-src for writing : No space left on device
The above example is from the file (1607295739-8447-1.out) in there dated December 7, 2020. I’d like to know what setting to change or config to fix to get webmin to write these logs again.
I thought you are using only webmin. I don’t use virtualmin, i prefer using webmin as its more simple and gives more control, atleast for me. You can try editing mysql config file and adding these to get log.
log_error: Specifies the error log file.
slow_query_log: Enables or disables the slow query log.
general_log: Enables or disables the general query log.
Remember it will not log mysqldump. Its for MariaDB’s internal server logs, such as error logs, slow query logs, and general query logs.
If you want to backup a database of that big size its recommended to use ssh.
This command will work and do the job. Backing up a database of 62GB size i dont think its a good idea to use virtualmin GUI when you are also low on disk space. Better use mysqldump command i provided as it will also log the error which is necessory because database is too big.
A successful email delivery to popular email provider like Gmail and outlook is not possible all the time because there are 50% chances that the assigned IP is either in their spam database. I personally own 7 vps from different provider and none of my vps email comes to my email. It’s simply blocked or gets bounced back.
It’s better the see MTA logs from there I think you can also see the email that webmin tried to send and read it from there. Or simply configure webmin to use SMTP services like sendinblue which has free plan.
@sanjeevone Ah I see, there is both a webmin part and a virtualmin part in my web interface; the database backups are configured in the webmin part.
Tweaking mysql configuration is unlikely to affect mysqldump behaviour as you state? The cause was found and fixed (for now), my question is still regarding the backup logs that webmin would write, and no longer does - although having them emailed works for now (our domain registrar and email provider also hosts our VPSs so they arrive fine).
This command will work and do the job. Backing up a database of 62GB size i dont think its a good idea to use virtualmin GUI
Only the configuration is done through the web interface, which simply writes to the root user’s crontab, so it’s similar to running it from an SSH session. Besides I wouldn’t want to do this manually every night after midnight
Your command can be improved wrt disk space by piping it through gzip before writing it to disk (also you can’t have a space between -p and the password)
