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
  • 30,923
  • 9
  • 73
  • 85
user2861783
  • 13
  • 3

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
  • 30,923
  • 9
  • 73
  • 85