1

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.

Vérace
  • 26,808
  • 7
  • 61
  • 75
user2861783
  • 13
  • 2

1 Answers1

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:

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.

Vérace
  • 26,808
  • 7
  • 61
  • 75
  • 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 06:14
  • I was surprised about 5.6, so I checked. Percona is saying that 2.4 (latest version) [supports MySQL](https://www.percona.com/doc/percona-xtrabackup/2.4/index.html) the way back to version 5.1! – Vérace Nov 25 '19 at 08:03
  • sorry I was looking at the version 8 of Percona. – user2861783 Nov 26 '19 at 10:07