A Master-Slave-Slave Replication in Production
Two new slave servers are added to the Production System. The master server is ‘Alpha’ and the new slave servers are ‘Beta’ and ‘Gamma’. The environment is Red Hat Enterprise Linux. If the binary logging is not already enabled, we need to enable it on the master server by editing the “my.cnf” as shown below. If it is already enabled, find out the current location of the binary logging by using the command: show master status.
1. Configure the Master Server: Alpha
Add the following lines in my.cnf:
[mysqld]
log-bin = alpha-bin
server-id = 1
Restart the server:
# /etc/init.d/mysql restart
See the master status:
mysql> show master status;
+————————–+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+————————–+———-+————–+——————+
|alpha-bin.000028 | 98 | | |
+————————–+———-+————–+——————+
1 row in set (0.00 sec)
Create a Replication user:
mysql> create user ‘replicuser’@’alpha.localdomain.com’ identified by ‘replicpass’;
mysql> GRANT REPLICATION SLAVE ON *.* TO replicuser@”%” IDENTIFIED BY ‘replicpass’;
It is important to note the position of the master log (binary log) exactly shown above. This position is given in the slave servers to point to the location to start the replication. If the position is not specified correctly, the slave server will simply fail to start replication.
2. Backup the Master Server
It is safe to take a cold backup on the server by shutting down MySQL on the server and using mysqldump, as shown below:
mysqldump –u root –p –all-databases > backup.sql
It is not online backup. So –single-transaction, –flush-logs options on mysqldump are not required. It may take hours to complete, based on the size of your databases. Once it is completed, copy the dump file from Alpha to Beta and Gamma:
# scp backup.sql mysqldba@beta.localdomain.com:/var/lib/mysql
# scp backup.sql mysqldba@gamma.localdomain.com:/var/lib/mysql
where ‘mysqldba’ is a Linux user on the slave servers and ‘localdomain’ is your domain in which alpha, beta and gamma are servers.
Restore the databases on Beta and Gamma as shown below:
# mysql –u root –p < backup.sql
The restoration used to fail many times because of some inconsistency in the table structure. If it encounters such an error, mysql will not continue and you have not other option but to repeat it all from the beginning. In case it fails during restoration, you must delete the databases already created and then restore it. Safely you may issue a forced restoration too:
# mysql –u root –p –force < backup.sql
Once the dumpfile is created on the master server, you may start MySQL on the the master server. If the dumpfile is very large in size, you may want to create different dumpfiles instead of one single large one. I have created three separate dumpfiles by grouping together some large tables from a database like this:
mysqldump -u root -p production hist0 hist1 hist2 hist31 hist32 hist33 hist34 hist41 hist42 hist43 hist44 hist51 hist52 hist53 hist54 hist61 hist62 hist63 hist64 hist71 hist72 hist73 hist74 > productionone.sql
mysqldump -u root -p production chist0 chist1 chist2 chist31 chist32 chist33 chist34 chist41 chist42 chist43 chist44 chist51 chist52 chist53 chist54 chist61 chist62 chist63 chist64 chist71 chist72 chist73 chist74 > productiontwo.sql
mysqldump -u root -p production –ignore-table=production.hist0 –ignore-table=production.hist1 –ignore-table=production.hist2 –ignore-table=production.hist31 –ignore-table= hist32 –ignore-table=hist33 –ignore-table=hist34 –ignore-table=hist41 –ignore-table=hist42 –ignore-table=hist43 –ignore-table=hist44 –ignore-table=hist51 –ignore-table=hist52 –ignore-table=hist53 –ignore-table=hist54 –ignore-table=hist61 –ignore-table=hist62 –ignore-table=hist63 –ignore-table=hist64 –ignore-table=hist71 –ignore-table=hist72 –ignore-table=hist73 –ignore-table=hist74 –ignore-table=chist0 –ignore-table=chist1 –ignore-table=chist2 –ignore-table=chist31 –ignore-table=chist32 –ignore-table=chist33 –ignore-table=chist34 –ignore-table=chist41 –ignore-table=chist42 –ignore-table=chist43 –ignore-table=chist44 –ignore-table=chist51 –ignore-table=chist52 –ignore-table=chist53 –ignore-table=chist54 –ignore-table=chist61 –ignore-table=chist62 –ignore-table=chist63 –ignore-table=chist64 –ignore-table=chist71 –ignore-table=chist72 –ignore-table=chist73 –ignore-table=chist74 > productionthree.sql
3. Configure the Slave Server: Beta
Add the following lines in my.cnf:
[mysqld]
server-id = 2
Restart the server:
# /etc/init.d/mysql restart
Run the following:
mysql> CHANGE MASTER TO
MASTER_HOST = ‘alpha.localdomain.com’,
MASTER_USER = ‘replicuser’,
MASTER_PASSWORD = ‘replicpass’,
MASTER_LOG_FILE=’alpha-bin.000028’,
MASTER_LOG_POS=98;
mysql> start slave;
mysql> show slave status \G
4. Configure the Slave Server: Gamma
Add the following lines in my.cnf:
[mysqld]
server-id = 3
Restart the server:
# /etc/init.d/mysql restart
Run the following:
mysql> CHANGE MASTER TO
MASTER_HOST = ‘alpha.localdomain.com’,
MASTER_USER = ‘replicuser’,
MASTER_PASSWORD = ‘replicpass’,
MASTER_LOG_FILE=’alpha-bin.000028’,
MASTER_LOG_POS=98;
mysql> start slave;
mysql> show slave status \G
We do not want the users to modify the tables in the databases on the slave servers. That means we need to make the databases “read-only”. Also, we do not want the tables in “mysql” database to be replicated to the slave servers. The following two lines are added to the “my.cnf” file on both slave servers.
–read-only
–replicate-ignore-db=mysql
But in most cases, users want some database to write their intermediate or temporary tables. We can create a database called “sandbox” on each server. Here the users must be given permissions to create tables. Also those users must have the global privilege of ’super’ to do so.
Errors can occur on the slave servers if a query was aborted on the master server or a table does not exist on the slave which originally exist on the master. The replication stops by showing the following error. We can choose to execute the query on the slave server, or ignore it if you are sure that this statement can be skipped. At MySQL prompt on the slave server, set global sql_slave_skip_counter=1 and then start slave.
Relay_Master_Log_File: alpha-bin.000028
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB: mysql,mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1053
Last_Error: Query partially completed on the master (error on master: 1053) and was aborted. There is a chance that your master is inconsistent at this point. If you are sure that your master is ok, run this query manually on the slave and then restart the slave with SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; . Query: ‘update liq_all, arbitration.claim_form SET liq_all.arb_date = replace(insertdate,’-',”) WHERE db_num=camref’
Skip_Counter: 0
SQL statements will cease being run on the slave (via replication) until the above error is resolved either by executing the query or by skipping the statement. On MySQL prompt, run the following:
mysql> set global sql_slave_skip_counter=1;
mysql> start slave;
