Replication

 

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;

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s