Hey all! Today, we shall show you some examples of master-slave replication setups.
A bit of theory first
Why do you need replication in the first place? There are at least two reasons to set it up. First off, it is your insurance that helps avoid downtime when/if your master MySQL server goes down: with replication, slave server picks up and fills for the master. Secondly, replication allows decreasing load suffered by the master server: you use it for writing only and pass read queries to slave.
The replication process
Nothing really complicated here. Master server writes binlogs that contain operations performed on the DB(s) and keeps tabs on journal records of shifts from the start to the current record. Slave connects to master, compares positions and reads changes found in the journal starting from its own position and finishing with the master’s position, and then applies the changes (commands) found to the DBs it hosts.
Master server setup
First off, we change my.cnf on the master server:
#server’s ID goes here server-id = 1 #and this is where you specify the log’s name and path to it. log_bin = /var/log/mysql/mysql-bin.log
A comment is due here: by default, master writes binlogs for all DBs, but you can change that with the help of binlog-do-db. The command tells the server to fill logs only when the specified DB is touched, and changes made to other DBs never make it to that log. You could also set logs expiration time and max size (expire_logs_days and max_binlog_size parameters).
Now, we grant a MySQL user permission to do replication:
GRANT replication slave ON *.* TO username@slave_server_ip IDENTIFIED BY "password";
replication slave - allows user to read binlogs.
slave_server_ip -ip of the server the user will connect from.
Next, we restart our MySQL server:
/etc/init.d/mysql restart
and check the master’s status:
show master status;
The reply should contain the binlog’s name and position record it has. When you run queries to DB, the position will change.
Slave server setup
my.cnf should receive the following:
#slave’s ID, must be different from master’s ID. server-id = 2 #same as the binary log,it is a list of numbered files that contain events describing changes made to the DB relay-log = /var/lib/mysql/mysql-relay-bin #index file containing names of all used relay journal files. relay-log-index = /var/lib/mysql/mysql-relay-bin.index replicate-do-db = [the DB to be replicated].
Important! When you cross DBs, i.e. use one DB but update the data in another, you don’t need to add binlog-do-db to the master’s setting. You want binlogs written for all DBs. Slave’s settings should receive replicate-wild-do-table=db_name.%, (db_name - name of the replicated DB) instead of replicate-do-db.
Restart MySQL server:
/etc/init.d/mysql restart
Switching on the replication
Now, we need to backup master’s DB while keeping the position in the binlog intact. The trick is to first block DB, make it read-only:
SET GLOBAL read_only = ON;
Check the master’s status:
show master status;
Write down File and Position values. The Position should not change now.
Next, we create a master’s dump:
mysqldump -uname -ppassword db_master_name > dump_db
The parameters are pretty obvious here:
- name - username,
- password - password (obviously),
- db_master_name - name of the DB,
- dump_db - name for the dump.
Once the dump is done, we need to make the DB writable again:
SET GLOBAL read_only = OFF;
Next, we move the dump to the slave server and restore it there:
mysql -uname -ppassword db_slave_name < dump_db
Replication setup:
CHANGE MASTER TO MASTER_HOST = “master’s ip”, MASTER_USER = "username", MASTER_PASSWORD = "password ", MASTER_LOG_FILE = "log’s name", MASTER_LOG_POS = position;
Again, everything is just what it seems here:
- master’s ip is the ip of the server that host the master,
- username is the name of the user created on the master server,
- log’s name is the File value on the master server as of the moment the dump was made,
- position is the Position value on the master server as of the aforementioned moment.
Now we start the slave:
start slave;
We can monitor the replication status:
SHOW MASTER STATUS\G SHOW SLAVE STATUS\G
Security setup for master server
bind-address in /etc/mysql/my.cnf specifies the IP address MySQL server should listen to waiting for connection.
Typically, the value is bind-address = 127.0.0.1. However, once we have the slave server set up, we need to allow connection from that server and keep local connections functioning. Bind-address does not allow to specify more than one IP address, so we comment it to allow connections from all IPs. This is a very dangerous situation, which we can remedy with the help of iptables:
#first, we allow connection from the slave’s IP iptables -I INPUT -p tcp -s ip_slave_server-a --dport 3306 -j ACCEPT #secondly, we disallow connections from all other IP addresses. iptables -I INPUT -p tcp --dport 3306 -j DROP
And that is how you do it: we now have two MySQL servers operating as master and slave, which makes the site much more resilient and even speeds up some Drupal-powered sites. One of the next pieces will cover switching between master and slave modes when the master is down. Stay tuned!