[BlueOnyx:20867] Server Backup issue

[Michael Aronoff]

I have been having a problem where the Server Backup package will hang while
backing up large databases.

 

What happens is that I don't get a backup email and when I check running
processes the mysqldump hung during the backup so the rest of the raq-backup
script never completes. Sometime I have gone a few days before I noticed and
found 3 hung.

 

I found the solution myself but I wanted to ask Michael Stauber if he could
consider adding it to the Server Backup package either as default or as a
checkbox option.

 

The problem seems to occur when backing up large InnoDB tables. I have
gotten the mysqldump to complete successfully by adding two options to the
file at /usr/sbin/raqbackup.sh

 

I add the following to the mysqldump command "--flush-logs" and "-q"

so the whole options bit looks like "--all-databases --flush-logs
--add-locks -Q -q -f -u"

 

The --flush-logs force any logs to be written out. This simply makes sure
that the InnoDB state is all caught up from what I understand. It introduces
a delay but it has not been bad.

 

The -q causes the dump to read large tables one row at a time rather than
reading and buffering the whole table. Can solve problems on reading large
tables.

 

The other thing I have done, in order to ensure I get good backups is to
include the /home/msql folder as an additional folder in the backup, but in
order for that to work MariaDB (or MySQL) must be stopped while the folder
is backed up. In order to do that I create two script files at:

/home/solarspeed/solbackup/server_backup/plugins/pretardirs.sh

/home/solarspeed/solbackup/server_backup/plugins/posttardirs.sh

 

Each of those does what you would expect from the names. In the pre file I
put a command to stop the database server and a short sleep so the database
daemon stops. Then the regular folder backup runs and in the post file I
simply restart the database server again.

 

This method causes a tiny bit of downtime but by copying the current state
of /home/mysql I am sure to get a workable backup. It might be nice to
include the option of a database backup with through a dump file or folder
backup within the Server Backup package itself.

 

I hope this helps someone else.

 

Thanks,

 

______________________________
M Aronoff Out 

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.blueonyx.it/pipermail/blueonyx/attachments/20170410/ef4f46ca/attachment.html>