MySQL Replication/Backup

MySQLtransactional-replication

I'm a new DBA working with a MySQL(5.6.19) instance I have running on a Mac Pro, I'd like to back it up with the Percona tools, but there isn't a distro for Mac, so my plan is to create a copy of the database on a linux box, make it a slave to the one on the Mac and then use the Percona tool to back it up from there (this will be on a local network). This Linux box will also be running the full system backup software (Bacula) we're setting up. I've looked into how to set this up and I think I get most of it, but I'd love advice on the following issues:

1) Bacula uses MySQL for part of its back end, if I set up the slave server on that machine will Bacula still be able to use MySQL as its back end? (More generally, can there be other schema on a MySQL instance that is running in slave mode?). Or do I need to configure a separate server instance as here: http://dev.mysql.com/doc/refman/5.6/en/multiple-servers.html ? (or are there better ways that I'm missing?)

2) I can do a mysqldump of the current database, but it will be rather slow as I have a few very large files, will there be a problem transferring the files directly as http://dev.mysql.com/doc/refman/5.6/en/replication-howto-rawdata.html from Mac to Linux?

Thanks everyone! Any responses to either would be much appreciated!

David

Best Answer

There seems that some people used to patch the source code of xtrabackup to make it usable in OSX:

http://sourceforge.net/projects/xtrabackup-osx/

But expect some problems, as some of the functionalities depend on a linux system (but you can try to give it a shot). The other alternative is MySQL Enterprise backup, which has full Mac compatibility.

1) You can run extra databases on the slave, within the same instance, without problem. There is no compatibility problem, it is just that there is some operational overhead on exceptional cases. Let me elaborate– You cannot setup the slave as read-only, which can increase the danger of slave-drifting by writing to the wrong database by accident. If the master fails, you have to separate the databases. And as you are writing to the binary log of the slave, it would be more difficult to use it for rolling forward a backup or using gtid replication (you cannot just switch master and slave roles, as it would try to replay all slave transaction). But in general, nothing will break more than usual, I use several setups like this where I use the slave as the backup source and the monitoring database.

2) Yes, you can clone it easily just by transferring the files. You may have some compatibility issues with the different case handling (Mac is not case sensitive, Linux is, which can affect db and table names), but that can be fixed in configuration in most cases. Aside from that, and the usual "moving the files around" issues (permissions, locations, configuration, etc.), you should be ok for moving the files, that should be endianness and OS independent for the most case. Please remember to completely shutdown the server before trying that, or you will end up with a corrupted set of files.