Mysql – Multiple instance of Mysql realtime backup

backupMySQL

My current mysql backup strategy is replication. I setup one or more slaves following a master. If the master failed, I still have a clean and up to date copy of the data from one of the slaves. It works without any problem. Now I started to have more master nodes to manage, each master node holds different data.

Now if I wanted to use the same backup strategy, I needed to setup at least one slave instance for each master. It's going to double the number of mysql nodes. Ideally I'm hoping to have only one server for all backups. Possible solutions I can think of are:

  1. To run multiple instances of mysql slaves on different ports on the same machine;
  2. Crontab + Rsync;

For possible solution #1, it should work, but it costs memory to running slave instances. For possible solution #2, I'm afraid when the data is being rsync'ed, the master failed, the backup might not contain a complete copy of the data. So is there any other better strategy to safely backup all master node to a single place?

Best Answer

"My current mysql backup strategy is replication." Replication cannot be a backup strategy but you can setup backups on slave! I guess you're talking about HA "if master fails then use slave"...

Anyways... MySQL 5.7 comes with Multi-source replication. You can have single slave machine replicating from two different masters.

You may write your own script to loop around CHANGE-MASTER-TO switching masters and replicate.

Idea for you:

while(true) {

change master to master1 from noted co-ordinates
monitor until sec_behind_master=0
   Note coordinates: relay_master_log_file, exec_master_log_pos

change master to master2 from noted co-ordinates
monitor until sec_behind_master=0
   Note coordinates: relay_master_log_file, exec_master_log_pos

}

Finally you can setup backup on this slave. (This is little complex than wht's written below)

You may also have individual slaves for both masters and setup backups on them. You can later push those backup to remote location / network drive/ tape / upload to s3 to retain longer.

master1--> slave1

master2--> slave2

(Consider backing up binary-logs if you want point in time restores.)

For backups you can use traditional mysqldump or mydumper/loader. If your data size is large, it'd be better to go /w physical-backups, follow settingup xtrabackup for mysql with Holland framework.

Related Question