Menu
I maintain big MySQL database. I need to backup it every night, but the DB is active all the time. There are queries from users. Now I just disable the website and then do a backup, but this is very bad as the service is disabled and users don't like this.
What is a good way to backup the data if data is changed during the backup?
What is best practice for this?
266
Identifying the backup and recovery requirements, setting up an automated backup schedule, and backup restoration testing are some of the best database backup practices you need to implement for successful backup and restore process in SQL Server. This article discusses five SQL Server database backup best practices worth exploring. 1.
If the service level is 15 minutes, then backups need to be done at least every 15 minutes. Again, for BLI backups, a 15-minute window is reasonable. The only negative to a high number of BLI backups is that there is a limit in most software applications as to how many BLI backups can exist prior to them impacting backup and recovery performance.
File or filegroup backup strategy can be used if the databases to be backed up are very large databases (VLDBs) that are partitioned among multiple files.
1 Develop a comprehensive backup plan. 2 Perform effective backup management. 3 Perform periodic databases restore testing. 4 Have backup and recovery SLAs drafted and communicated to all stakeholders. 5 Have the disaster recovery plan (DRP) database portion drafted and documented. More items...
I've implemented this scheme using a read-only replication slave of my database server.
MySQL Database Replication is pretty easy to set up and monitor. You can set it up to get all changes made to your production database, then take it off-line nightly to make a backup.
The Replication Slave server can be brought up as read-only to ensure that no changes can be made to it directly.
There are other ways of doing this that don't require the replication slave, but in my experience that was a pretty solid way of solving this problem.
Here's a link to the docs on MySQL Replication.
136
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
