Backing up MySQL

[halfmoonforever]

Hi guys,

Was wondering if you could share how you backup your production MySQL servers (I don't have a master-slave setup yet).

I'm more experienced with MSSQL, and on that, you can do incremental backups with log files etc on the hour, then have full backups happen once a day so you can replay the logs if something happens and maybe only lose an hour worth of work when restoring from backup.

Does MySQL have something similar? Or is a full DB backup the only way to really ensure consistency when doing disaster recovery?

I'd maybe like to use BorgBackup as a method to store these backups offsite as it has the ability to mount directly to a backup and restore from there if need be.

Thanks for any advice

 

[lroachl]

What os?

 

[lroachl]

If it's linux, get the latest version Percona XtraBackup. 2.3 has a bug in it doing incremental backups. works like a charm

https://www.percona.com/downloads/XtraBackup/
https://www.percona.com/doc/percona-xtrabackup/2.3/installation/apt_repo.html
https://www.percona.com/doc/percona...ackupex/incremental_backups_innobackupex.html

 

[WAslayer]

If it's linux, get the latest version Percona XtraBackup. 2.3 has a bug in it doing incremental backups. works like a charm

https://www.percona.com/downloads/XtraBackup/
https://www.percona.com/doc/percona-xtrabackup/2.3/installation/apt_repo.html
https://www.percona.com/doc/percona...ackupex/incremental_backups_innobackupex.html

+1 restores or also much faster on larger db's..

 

[halfmoonforever]

I always run linux when working with MySQL or PHP

I'm running Debian 8 with MariaDB 10.1.12, I read that 10.2 doesn't work with XtraBackup, and I'm afraid if Debian releases 10.2 upgrade, that it would break my backup solution

 

[reactor_sa]

I just use it for kodi, to backup mysql I close the DBA and stop the service then backup the folder containing the database files.

 

[koeksGHT]

https://www.digitalocean.com/community/tutorials/how-to-backup-mysql-databases-on-an-ubuntu-vps

 

[Nod]

I just use it for kodi, to backup mysql I close the DBA and stop the service then backup the folder containing the database files.

Why not just use "mysqldump"? No need to stop the DB if you use it.

 

[itareanlnotani]

I use automysqlbackup on my web server boxes. Its setup to run daily, although I could run it more often if needed.

Does daily,weekly, and monthly rotations, so you get a nice selection of recovery db's to get at if needed.

I then rsync the dbbackup folder to elsewhere daily.

 

[envo]

I use automysqlbackup on my web server boxes. Its setup to run daily, although I could run it more often if needed.

Does daily,weekly, and monthly rotations, so you get a nice selection of recovery db's to get at if needed.

I then rsync the dbbackup folder to elsewhere daily.

So those, I assume, aren't incremental. What would happen if you have a 5GB+ db size to backup?

 

[itareanlnotani]

I don't do incrementals as I don't have clients with large DB's, and total space for DB over multiple servers is only about 200G with aggregated server daily's /monthly's, but it does also handle that.

Howto here - https://www.howtoforge.com/tutorial/mysql-incremental-backup-point-in-time-recovery/

 

[envo]

I don't do incrementals as I don't have clients with large DB's, and total space for DB over multiple servers is only about 200G with aggregated server daily's /monthly's, but it does also handle that.

Howto here - https://www.howtoforge.com/tutorial/mysql-incremental-backup-point-in-time-recovery/

Cool. Nice to know.

Does this need the DB to be offline at any point?

 

[itareanlnotani]

Cool. Nice to know.

Does this need the DB to be offline at any point?

Nope.

 

[halfmoonforever]

Cool thanks guys. Think I'll go with lsheed_cn option and use BorgBackup to store them on a different drive