MySQL database backup plan

Question

I have a MySQL database that is about 17GB in size. Right now we don't have a backup for this and I have been tasked to look into backing it up automatically.

I am a software developer myself, not an expert database engineer.

After some research I found out that MySQL has something called MySQL Replication, which will automatically create a backup database on different server.

My concern about this is that if someone accidentally drops a table, or deletes a record, the same will happen on the slave database as well - so that would not serve as a backup.

I need suggestions on what can be done with our MySQL database so that it can be backed up automatically.

• Akina: After some research I found out that MySQL has something called MySQL Replication, which will automatically create a backup database on different server. :ROFL: Replication initiation needs in restoring full DB backup on slave server.
• Akina: How complex is your DB structure?
• Rick James: See also LVM snapshots.
• user2861783: @Akinam I have about 80 tables in 2 databases to backup

Answer 1

Note that mysqlbackup is not a hot backup solution. For databases the size of your one, there really is no debate; see the related Q & A Mysql backup strategies?

As Shlomi Noach (GitHub's MySQL guy) says in his answer, Percona's XtraBackup is your go-to choice. It is also worth reading MySQL Backup and Restore Best Practices by Krzysztof Ksiazek.

By the way, no backup strategy on earth will protect you from devs/DBAs deleting records/dropping tables, that's why you should be using incremental backups; maybe LVM snapshots or flush logs. However, incremental backups are also performed by XtraBackup. See also Example Backup and Recovery Strategy in the MySQL reference manual.

On a more general note, broad approaches to this issue are discussed in:

• Database Backup and Recovery Best Practices by Ali Navid Akhtar, Jeff Buchholtz, Michael Ryan, and Kumar Setty
• Backup and Recovery Best Practices by Omelchenko Alexander

In any case, on a production system, devs (or DBAs for that matter) should not be wandering through the filesystem and/or database deleting stuff ad libitum. Any and all changes on production should have been performed at least twice on test/UAT or similar systems before being implemented in prod! This is best practice.

• user2861783: Thank you for the information. It’s glad to be a member of such a great community. Will XtraBackup serve my purpose and do incremental backups? Also is it easy to implement for a non database expert like me ? Thanks
• Vérace: I have always found Percona's documentation to be excellent, and their lists are very helpful as well! There's always here also! If you found my answer good, you could always mark it as correct and/or helpful. Glad your first experience here has been positive!
• user2861783: I was reading the documentation for XtraBackup and it says that it doesnot support MYSQL versions less then 8.0. The version i am running right now is MySQL Version 5.6.43. What is your advise on this?
• Vérace: I was surprised about 5.6, so I checked. Percona is saying that 2.4 (latest version) supports MySQL the way back to version 5.1!
• user2861783: sorry I was looking at the version 8 of Percona.