1

Currently my backup strategy invloves :-

1)I have one master server and two slaves (Data approximately 700 GBs and tables are of mixed innodb and myisam).

2)I take weekly and monthly backup from slave through mysqldump.

3)In case of slave failure ,will copy data from another slave.

My question is should i change this backup strategy ?? Should i use percona xtrabackup and if yes why ??

1

700GB is too much for mysqldump. Restore will take too much time (but I would measure to know numbers). XtraBackup will be faster.

Taking backup from a slave is OK, but you have to make sure the master and slave are in sync. Use pt-table-checksum to verify that.

And what is missing in your strategy is backups verification. If you don't verify backups you don't have backups.

1

Plan A - Copy

It is quite reasonable, even with a mixture of Engines, to stop a Slave, copy mysql's directory tree to another Slave, verify that the Slaves have my.cnf files with different server_id, start both slaves.

After that, both Slaves will catch up with replication from the same spot, and be online (hopefully) pretty soon.

That process is the same as for bringing a new Slave online.

Plan B - LVM

With some planning ahead, you could have LVM set up. With that, you can take a "snapshot" of a Slave with only a minute of downtime -- even for 700GB. Then take as long as you need to copy the snapshot to the other Slave (recovered / new). LVM requires OS work at the file system level, so it is not easily retrofitted.

Copying the snapshot (to another machine) can give you a backup. Or it can give you a copy of production for use in QA/Dev/etc. The copy will be not just the 700GB, but the entire file system.

Plan C - Galera

See also Galera (PXC / MariaDB / Galera addon), which has the concept of IST ("Incremental State Transfer"), which it uses when a node comes back online after a crash. (That only works if it has not been offline "too long", else a full state transfer will be performed.)

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.