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.

  • 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 Nov 23 '19 at 18:16
  • How complex is your DB structure? – Akina Nov 23 '19 at 18:17
  • See also LVM snapshots. – Rick James Nov 24 '19 at 1:55
  • @Akinam I have about 80 tables in 2 databases to backup – user2861783 Nov 25 '19 at 6:56

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:

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.

  • 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 – user2861783 Nov 24 '19 at 13:21
  • 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! – Vérace Nov 24 '19 at 14:23
  • 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? – user2861783 Nov 25 '19 at 6:14
  • 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! – Vérace Nov 25 '19 at 8:03
  • sorry I was looking at the version 8 of Percona. – user2861783 Nov 26 '19 at 10:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.