![]() Record the value of the Position field, since we’ll need it later on the slave. | mysql-bin.000001 | 16242 | | mysql information_schema performance_schema | | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | We do this by locking the MASTER to prevent write actions: mysql Now we need another sync to put the slave in a valid state. rsync -Sa -progress -delete -exclude=mastername* -exclude= -exclude= /var/lib/mysql now synchronized the bulk of the master data to the slave. You should experiment a bit with what you should and shouldn’t exclude. For example, if you don’t want to overwrite the users on your slave, you’ll have to exclude the ‘mysql’ directory. Depending on your setup, there are a few files you’ll have to exclude. For this you’ll need to have root ssh access enabled. ![]() Now we rsync the MASTER binary data to the SLAVE. Add the ‘skip-slave-start’ setting to the section: vi /etc/mysql/my.cnf We need this so we can modify the master log pos after starting the slave. On the SLAVE, stop MySQL: sudo /etc/init.d/mysql stopĬonfigure the SLAVE not to automatically start replication on startup. Meanwhile, we record the master log position for the slave.ĭo note that this will only work if your master and slave database are the architecture and run the same MySQL version. The master will only need to be write-locked for a short amount of time. This only needs to synchronize the new data since the last sync, so it should be fast. That’s why, after the initial bulk sync, we set a write lock on the master and perform another sync. However, this leaves the slave in a potentially corrupt state, since we might have copied the master data in the middle of a transaction. Since we first do a sync to the slave without a write lock, the master can keep receiving writes. On the slave, we change the master log position and start replication again.Set a write lock on the master, record the master log position and do another rsync from the master to the slave.Rsync the binary files from the master to the slave while the master is running.Roughly speaking, we’ll be doing the following steps: You can skip the step where they perform a mysqldump, since that’s what this article is replacing with rsync in the first place. If not, please see this guide on how to configure MySQL. In this article I’m going to assume you’ve already correctly configured the master and slave for replication. If the slave was already set up and has become corrupt for whatever reason, rsync will ensure we won’t have to copy all the data again that’s already on the slave.There is a small window (seconds, usually) during which writes are temporary blocked. Very fast setup of a slave by avoiding having to load a logical dump into MySQL.My friend Cris suggest a much faster method using rsync. Unfortunately, loading data with mysqldump can be very slow. Most online tutorials for setting up a slave replication database involve dumping and loading the database on the master with mysqldump. Very fast MySQL slave setup with zero downtime using rsync
0 Comments
Leave a Reply. |