MySQL

Replication

===============================================================

1) Before setting up a replication, it could be important to have a clear idea on the why you are setting up a MySQL replication.

The most common one are:
1) Backup purpose
2) Scalability
3) Geographic Distribution
4) High Availability
(More see http://dev.mysql.com/doc/refman/5.1/en/replication-solutions.html)

So stop, relax think and decide which one is yours because it will make the difference.

2) One of the most common question clients asks is HOW the Replication works?

----------------------------------------------------------------------------

There are two main concepts to keep in mind:

1) in MySQL the replication is asynchronous

2) the replication is based on the binlog

The Replication is based on the work of 3 (note please 3 not 2) threads:

1) BinlogDump thread

2) Slave I/O thread

3) Slave SQL thread

What they are used for?

The Binlog dump thread. The master creates a thread to send the binary log contents to the slave. This thread can be identified in the output of SHOW PROCESSLIST on the master as the Binlog Dump thread.

The binlog dump thread acquires a lock on the master's binary log for reading each event that is to be sent to the slave. As soon as the event has been read, the lock is released, even before the event is sent to the slave.

Slave I/O thread. When a START SLAVE statement is issued on a slave server, the slave creates an I/O thread, which connects to the master and asks it to send the updates recorded in its binary logs.

The slave I/O thread reads the updates that the master' Binlog Dump thread sends and copies them to local files — known as relay logs - in the slave's data directory.

The state of this thread is shown as Slave_IO_running in the output of SHOW SLAVE STATUS or as Slave_running in the output of SHOW STATUS.

Slave SQL thread. The slave creates this thread to read the relay logs that were written by the slave I/O thread. The slave SQL thread is also used to execute the updates contained in the relay logs.

Please Note that MySQL create a Binlog Dump thread for each connected slave. This could lead to a increment of load on the Master specially if is a very busy master in write.

Because this it is good practics to do not attach too many slaves to the same Master. In order to bypass the limitation it is possible to attach a slave to another slave doing the replication "on cascade".

Something like:

   M
  /  \

      S1       S2
      /   \

S1.1   S1.2

The other question very often rose, and related to this is... how long the replication takes to propagate the data across all slaves.

A possible formula but quite far to be perfect could be:

PT = (ND + QT) * NL

Where

PT = Propagation Time

ND = Network time delay

QT = Query Execution Time

NL = Number of Level

This formula take in account the need of the replication to wait for each slaves to be fully propagate, but also that it is not serialized but that could be executed in parallel.

3) What needs to be replicated?

----------------------------------------------------------------------

Last but not least point is what is really needed to be replicated?

There are two main points to keep in account,

• the binlog is not only used by the replication
• filtering could be applied at slave level.

It is possible to do not write statements for a specific Database using binlog-ignore-db=db_name, but this will imply that all the changes will not be save in the binlog, which imply that we will not have a way to recover the time "delta".

It is quite obvious that there *must* be a very good reason to use this approach.

So for me it is much better to go for filtering at slave level, also if this implies a bigger amount of traffic at network level.

4) Master and Slave where and how synch the data?

Well if we are going to implement the Replication on a new Environment then no problem because it will start from scratch, but what if we have to do it on a Master with an existing data set?

The easy answer is to load a valid backup in the Slave(s).

But how to take a valid backup? (read other article on how to take backups on MySQL)

----------------------------------------------------------------------

In MySQL a valid backup, that respect the principle of internal consistency and that could be re conducted to a Binlog file and position, needs to take in account the different behavior of the different engines.

So any time we have to take a backup we must think on how to handle the different engines.

See also http://dev.mysql.com/doc/refman/5.1/en/backup-and-recovery.html

And it is always a good practice to perform at regular interval a "restore check" to analyze the backup procedure validity.

Briefly we can say the following:

For MyIsam:

1) Use FLUSH TABLES WITH READ LOCK (1)

2) use one of the following

a) copy files

b) Export (with SELECT ... INTO OUTFILE 'file_name')

c) mysqldump with --master-data (2)

Notes

1. FLUSH TABLES WITH READ LOCK acquires a global read lock and not table locks, so it is not subject to the same behavior as LOCK TABLES and UNLOCK TABLES with respect to table locking and implicit commits.
   
   
2. The --master-data option automatically turns off --lock-tables. It also turns on --lock-all-tables, unless --single-transaction also is specified, in which case, a global read lock is acquired only for a short time at the beginning of the dump (see the description for --single-transaction). In all cases, any action on logs happens at the exact moment of the dump.

For InnoDB(1), PBXT, NDB

1. using one transaction (or single transaction if you prefer)
2. use one of the following methods:
   a) Export (with SELECT ... INTO OUTFILE 'file_name')
   b) mysqldump with --master-data and --single-transaction
   c) For cluster ONLY use START BACKUP (http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-backup-using-management-client.html)

All the above (excluding mysqldump which is storing the info inside) should also store the information for the binary log file and position.

Transforming all this into a step by step procedure:

1) Create a user that will be used by the slaves for connecting to the master as follow:

GRANT REPLICATION SLAVE ON *.* TO 'replica'@'' IDENTIFIED BY 'repl';

Please note that it is good practice to use an internal set of IP instead public ones when setting up the replication.

2) Modify the my.cnf(my.ini) on both Master and Slave(s):

a) first thing to do is to modify the server-id such that any server in the Replication will have a unique identifier

b) If you are going to use multilevel Replication add log-slave-updates = 1 on the Slaves or you will not have the data propagated correctly.

c) In the unlikely case you have taken the fool decision of excluding a Database from the binlog. Add on the master binlog-ignore-db=

d) In the case you need to filter at slave level the replication by Database or Table. Add the command (one for each, Database or table) replicate-do/ignore-db=db_name and --replicate-do/ignore-table=db_name.tbl_name

3) Load the master dataset on the slave(s) taken from the valid backup previously taken

4) Register the Master on the Slave with the command:

CHANGE MASTER to MASTER_HOST='', MASTER_PORT=3306,MASTER_USER='replica',MASTER_PASSWORD='repl', MASTER_LOG_FILE='', MASTER_LOG_POS=xxxx;

5) Start the slave(s)

6) Check the Replication Status with SHOW SLAVE STATUS\G

7) Test the replication creating on the master a database, a table and filling it with some data. Then check the latest Slave of each chain to see if the data is replicated correctly.

Setting up Master - Master replication (with one node at time acting as the ACTIVE node)
MySQL 5.1 or above
===========================================================================================================

If you want to filter the replication you have two different ways to do that, at master (by inclusion or exclusion), at slave (by inclusion or exclusion).

Parameter to use for filtering the binary log at MASTER:

binlog-do-db = DB_to_replicate

binlog-ignore-db = DB_to_IGNORE

To specify more than one database to ignore, use this option multiple times, once for each database.

When using this way of filtering, it is advisable to use binlog_format = ROW, this to ensure that the replication will act consistently, and will ignore the USE or dbname.tablename clausole.

Parameter to use for filtering the binary log at SLAVE:

replicate-do-db=db_name

replicate-ignore-db=db_name

replicate-do-table=db_name.tbl_name

replicate-ignore-table=db_name.tbl_name

replicate-wild-do-table=db_name.tbl_name

replicate-wild-ignore-table=db_name.tbl_name

Assuming two MySQL sites site:

Site_A IP (192.168.0.1)

Site_B IP (192.168.0.2)

Steps:

1) First of all be sure both machine will have correct GRANTS/User for the replication

Execute on Site_A

GRANT REPLICATION SLAVE ON *.* TO 'replica'@'192.168.0.2' IDENTIFIED BY 'repl';

Execute on Site_B

GRANT REPLICATION SLAVE ON *.* TO 'replica'@'192.168.0.1' IDENTIFIED BY 'repl';

2) check/modify the configuration files:

Site_A (active):

----------------

Setting (or checking) the UNIQUE id for the server:

server-id = XX (i.e. 100)

Activate the binary log:

log-bin = /var/log/mysql/mysql-bin.log

binlog_format = MIXED;

Set MySQL to generate different values for the auto increment (Site_A/Site_B)

auto_increment_increment = 2

auto_increment_offset = 1 #for Master

Write the setting for future use but keep it commented, it will be then possible to set it at command line from mysql client, and then modify it also in the configuration file if needed.

#read_only=1

Site_B (read only/standby):

----------------

Setting (or checking) the UNIQUE id for the server:

server-id = YY (i.e. 200)

Activate the binary log:

log-bin = /var/log/mysql/mysql-bin.log

binlog_format = MIXED;

Set MySQL to generate different values for the auto increment (Site_A/Site_B)

auto_increment_increment = 2

auto_increment_offset = 2 #for FailOverMaster

Set the server as READ ONLY

read_only=1

Custom steps (on both sites):

-----------------------------

log-slave-update Add this parameter in the case you have additional SLAVES attached for scale-out purpose on one or both Site.

slave-load-tmpdir = file_name The name of the directory where the slave creates temporary files for replicating LOAD DATA INFILE statements.

Final look could be:

Site_A:

server-id = 100

binlog-do-db = DB_to_replicate

log-bin = /var/log/mysql/mysql-bin.log

binlog_format = MIXED;

auto_increment_increment = 2

auto_increment_offset = 1

#read_only=1

log-slave-update

slave-load-tmpdir = /tmp

Site_B:

server-id = 200

binlog-do-db = DB_to_replicate

log-bin = /var/log/mysql/mysql-bin.log

binlog_format = MIXED;

auto_increment_increment = 2

auto_increment_offset = 2

read_only=1

log-slave-update

slave-load-tmpdir = /tmp

3) Taking a valid Snapshot of the dataset on a new Environment ther is no problem because it will start from scratch, but what if we have to do it on a Master with an existing data set?

The easy answer is to load a valid backup in the Slave(s).

But how to take a valid backup? (read Above) ;-)

----------------------------------------------------------------------

4) Setting up the Replication on slaves

On Site_A

---------

CHANGE MASTER to MASTER_HOST='192.168.0.2', MASTER_PORT=3306,MASTER_USER='replica',MASTER_PASSWORD='repl', MASTER_LOG_FILE='FILENAME', MASTER_LOG_POS=xxxx;

Master Log file and Master log position can be taken from the OTHER SLAVE/MASTER with SHOW MASTER STATUS:

So connect to SITE_B and execute SHOW MASTER STATUS

+---------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+---------------+----------+--------------+------------------+

| binlog.000002 | 106 | | |

+---------------+----------+--------------+------------------+

1 row in set (0.00 sec)

Check that the settings are taken correctly :

SHOW SLAVE STATUS\G

Start the slave:

START SLAVE;

Check the slave status again and see if it is running correctly:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

On Site_B

---------

CHANGE MASTER to MASTER_HOST='192.168.0.1', MASTER_PORT=3306,MASTER_USER='replica',MASTER_PASSWORD='repl', MASTER_LOG_FILE='FILENAME', MASTER_LOG_POS=xxxx;

Master Log file and Master log position can be taken from:

the dump like HEAD -n50:

-- Position to start replication or point-in-time recovery from

-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000004', MASTER_LOG_POS=552;

-- Table structure for table 'columns_priv'

or from the master with SHOW MASTER STATUS:

+---------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+---------------+----------+--------------+------------------+

| binlog.000006 | 106 | | |

+---------------+----------+--------------+------------------+

1 row in set (0.00 sec)

Check that the settings are taken correctly :

SHOW SLAVE STATUS\G

Start the slave:

START SLAVE;

Check the slave status again and see if it is running correctly:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

5) Do final check to be sure about data propagation

On Site_A

---------

from mysql console:

CREATE DATABASE rep_test:

create table tb1(a int, b char(3);

Use rep_test;

show tables;

On Site_B

---------

Show databases;

Use rep_test;

If all went fine both sites will have database and table.

At this point check IF also from Site_B the actions are replicated.

On Site_B

---------

Remove READ ONLY:

SET GLOBAL read_only=0;

Drop database rep_test;

Show databases;

SET GLOBAL read_only=1;

On Site_A

---------

Show databases;

Database rep_test should not be there anymore.

Summary of the actions to perform:

==================================

1) Check/Assign GRANTS

2) Modify configuration files on boths sites

3) Take snapshot if requested

4) Register Master on SLAVE(S)

5) Do final check.

Setting up Master - Master replication (with both nodes acting as the ACTIVE node)
MySQL 5.1 or above

===========================================================================================================           
If you want to have MASTER-MASTER
Follow the steps above only keep commented the :
read_only=1
Also on the Site_B and that's it.

HOW to monitor the REPLICATION STATUS?

==================================================================

It is possible to write a very simple custom script which will check the replication from the SHOW SLAVE STATUS.

Some of them are also on my site.

Or I can write some for you just This email address is being protected from spambots. You need JavaScript enabled to view it. me

Or you can use the MAATKIT which has two very useful little tools:

mk-table-checksum - Perform an online replication consistency check, or checksum MySQL tables efficiently on one or many servers. (http://www.maatkit.org/doc/mk-table-checksum.html)

mk-table-sync - Synchronize MySQL tables efficiently. (http://www.maatkit.org/doc/mk-table-sync.html)

BUT what is the meaning of the MySQL SHOW SLAVE STATUS output?

==================================================================

It is not good to re-invent the wheel or to rewrite something already written and clear.

So go here for details: http://dev.mysql.com/doc/refman/5.1/en/show-slave-status.html

======================================================

HELP!!!!!! My replication is broken....

======================================================

This email address is being protected from spambots. You need JavaScript enabled to view it. we will be happy to help. ;-)

{joscommentenable}