Jump to content

Global Message before Database Backup

Recommended Posts

Currently MD does a DB backup every day at 4:00 server time. During this time MD becomes completely inaccessible for 2-5 minutes, depending on the day. I propose that a global message be sent 1 minute before this occurs, to inform users online at the time of the upcoming backup. The 1 minute before is to allow users the time to actually receive the message.

Link to comment
Share on other sites

So, it appears Chewett wasn't aware that the DB backup was causing an issue like this. Working under the assumption that MD is non-responsive due to the SQL server being slammed by the dump, I was instructed to look into the details and present my findings.



My proposed solution is to add the --single-transaction option to the invocation of mysqldump. The --single-transaction parameter is to ensure a single, consistent, state is backed up. It also means that every table doesn't have to be locked while the backup is occurring. It should be noted that MyISAM tables, as they don't support transactions, will be accessed in whatever state they are at the time. As hinted in the first answer to this StackOverflow question, it might be necessary to add --skip-lock-tables to the options list, however I believe it would be redundant.

It may also be worth looking into using --result-file rather than piping the console output to disk. I doubt this will have any impact on dump speed, but it's worth mentioning.



My initial response included adding the --quick option, but, upon further reading, it is enabled by default, and specifying it manually is not needed.


I was going to suggest mysqlhotcopy if MD contained solely MyISAM and ARCHIVE tables, however it was deprecated in MySQL 5.6.20 and removed in MySQL 5.7.


An initial alternative idea was to have the dump occur via a user that had a resource limitation of the number of queries a second, but, as the only query limitation available is a per-hour limit, and, as the result of too many queries is that the server will return an error, rather than waiting until the user is allowed to make more queries, this is not a viable way to limit the resource usage of the DB backup process.

Edited by I am Bored
Link to comment
Share on other sites

As Chewett wasn't aware that the backup was causing an issue like this, this might be better placed in the bugs section. If it's moved, I can do a bit of re-writing of the title and opening post's message to reflect its status as a bug rather than a feature request..

Link to comment
Share on other sites

  • 1 month later...

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Forum Statistics

    Total Topics
    Total Posts
  • Recently Browsing

    • No registered users viewing this page.
  • Upcoming Events

    No upcoming events found
  • Recent Event Reviews

  • Create New...