Following my previous blog post, where I have discussed how to install multiple MySQL server instances, we are ready now to move on setting up replication.
What is replication?
– Replication meaning, capability that allows the databases on one server(the master) to be made available on another servers(the slaves).
MySQL supports replication capabilities that allow the databases on one server to be made available on another server instances.
MySQL replication uses a master/slave architecture:
– The server that manages the original databases is the master.
– Any server that have the copy of the original databases are slaves.
– A given master server can have many slaves, but a slave can have only a single master.
1) There are three parallel threads working, one on the server host and remaining two(IO thread and SQL Thread) on the slave host.
2) The task of the thread on the server host, is to write all the events to a file called binary log(make sure binary recording is enabled on the server host).
3) IO Thread interact with the server host and reads all the events being recorded with specific replication co-ordinate and writes it to its local file called relay log.
4) SQL thread reads all non-run events from the relay log and applies to the slave database.
Setting up Replication:
– Before starting the copying process there needs to a starting replication co-ordinates where all the databases(master and slave) have the exact clone. The easiest way to do is to dump-out the latest database state and dump in to all the slaves.
mysqldump --all-databases --master-data=2 > dump_file
Assuming that binary logging is enabled, the –master-data=2 option causes the dump file to include a comment containing a CHANGE MASTER statement that indicates the replication coordinates as of the time of the backup. These coordinates can be used later when you tell the slave where to begin replicating in the master’s binary log.
Copy the dump file to the replication slave host and load it into the MySQL server on that machine:
mysql < dump_file
– Master Configurations:
– master should be recording binary logging. It can done like(set where bin-log files reside)
SET log_bin = /var/log/mysql/mysql-bin.log
– Create a replication user on the master host and allow for replication access only.
CREATE USER `replicator`@localhost IDENTIFIED BY `password`; GRANT REPLICATION SLAVE ON *.* TO replicator@localhost;
and that’s all from master side.
– Slave Configurations:
– set the location for relay-log files. Add this line to the slaves configuration file:
relay-log = /var/lib/mysql/slave-relay.log
NB: this is optional, if we don’t set the location files gonna be stored to the default location(inside data directory)
– Linking master with the slaves:
Now we need to instruct the slave which Master to connect to. We also tell slave where to start reading on binary-log file.
CHANGE MASTER TO MASTER_HOST = 'localhost', MASTER_USER = 'replicator', MASTER_PORT = 3306, MASTER_PASSWORD = &amp;quot;password&amp;quot;, MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 120;
NB: The hostname is the host where the master server is running. The username and pass-word are those for the slave account that you set up on the master. The log file and position are the replication coordinates in the master’s binary log. (You can get these from the CHANGE MASTER statement near the beginning of the dump file.)
We can checkout a list of replication slaves currently registered with the master with this command:
SHOW SLAVE HOSTS;
And we are done. The slave should connect to the master and begin replicating updates that the master sends to it.
NB: remember we have multiple MySQL instances we can setup replication in our local host to test. 🙂