0

I have a MySQL master-master replication setup with a slave for each master(only one master used for read/writes at a time) on Ubuntu server. Wondering what would be the best way to schedule backup of replication databases with mysqldump.

I have following clarifications because of which could not proceed further.

  1. Scheduling mysqldump backup on masters safe for replication?
  2. Connecting masters with GUI applications(workbench) for database manipulations(read, writes.. by developers) is safe?

Any inputs are welcome.

John Gardeniers
  • 27,458
  • 12
  • 55
  • 109
user53864
  • 1,723
  • 11
  • 37
  • 66

1 Answers1

1

The most common way is to perform backups on a slave. The process is simple enough. Stop replication, take your backup and restart the replication. You may find it worthwhile setting up another slave just for this purpose.

John Gardeniers
  • 27,458
  • 12
  • 55
  • 109
  • What happens if I directly issue mysqldump on Master servers?. And how about allowing developers for making changes to databases manually with the tools?. – user53864 Sep 09 '12 at 01:03
  • For the first part, you can expect performance issue on that master, which for most people would be undesirable. For the second part, You will have performance issues of databases and/or tables are modified but otherwise they're just queries, nothing else, so they should be applied on whatever master best suits your requirements. The tools used to create those queries is irrelevant. – John Gardeniers Sep 09 '12 at 03:52
  • I was just wondering if it's safe if master servers are directly accessed for database changes. If it's not safe then not the M/M replication, the whole meaning of database would go wrong. This, I might have understood from your comment. Somehow we should access the masters for database changes apart from application connection and the developers make changes to the db before deploying web apps. Thanks! – user53864 Sep 09 '12 at 04:54
  • Coming to replication database backup, feeling a bit inconvenience as it should be done manually. If the slave is stopped, the master would have written something in the meanwhile and the whole databases should be dumped again from master to slave and then start the slave from the same master position. Any more tips? – user53864 Sep 09 '12 at 05:00