Introduction

One way replication can be used is to increase both robustness and speed. For robustness you can have two systems and can switch to the backup if you have problems with the master. The extra speed is achieved by sending a part of the non-updating queries to the replica server. Of course this only works if non-updating queries dominate, but that is the normal case.

Starting in Version 3.23.15, MySQL supports one-way replication internally. One server acts as the master, while the other acts as the slave. Note that one server could play the roles of master in one pair and slave in the other. The master server keeps a binary log of updates and an index file to binary logs to keep track of log rotation. The slave, upon connecting, informs the master where it left off since the last successfully propagated update, catches up on the updates, and then blocks and waits for the master to notify it of the new updates.

Note that if you are replicating a database, all updates to this database should be done through the master!

Another benefit of using replication is that one can get live backups of the system by doing a backup on a slave instead of doing it on the master.

Why Replicate?

- Need a hot spare live (live backup)
- Make backup easier
- Load balancing (scaling)

Topology : Master/Slave Replication

Server Configuration :

Server Name : Database1
Description : Master Database Server
Database Engine : MySQL 3.23.X Max Server & Client
IP Server : 10.10.10.1

Server Name : Database2
Description : Slave Database Server
Database Engine : MySQL 3.23.X Max Server & Client
IP Server : 10.10.10.2

Replication Setup

1 Configure replication account on the master

mysql> GRANT FILE ON *.* TO repl@”10.10.10.2″ IDENTIFIED BY ‘YourPassword’;

mysql> flush privileges;

If you are using MyISAM tables, flush all the tables and block write queries by executing FLUSH TABLES WITH READ LOCK command.

mysql> FLUSH TABLES WITH READ LOCK;

2 Enable binary log on the master configuration

#vi /etc/my.cnf

[client]

port = 3306

socket = /tmp/mysql.sock

[mysqld]

port = 3306

socket = /tmp/mysql.sock

server-id = 1

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

3 Snapshot data on master and copy to the slave

$ mysqldump -u root –p –A –-add-drop-table –opt > datasnap.sql

copy ‘datasnap.sql’ to slave server using ftp, in import the data to slave.

$ mysql –u root –p < datasnap.sql

4 Reset log on the master

mysql> FLUSH LOGS;

or

mysql> RESET MASTER;

5 Setup replication option on slave

#vi /etc/my.cnf

[client]

port=3306

socket=/var/lib/mysql/mysql.sock

[mysqld]

port=3306

socket=/var/lib/mysql/mysql.sock

server-id=2

master-host = 10.10.10.1

master-user = repl

master-password = YourPassword

master-port = 3306

master-info-file = master.info

master-connect-retry

log-slave-updates

replicate-rewrite-db = old_db->new_db

slave-skip-errors = error_list

6 restart slave

mysql> RESET SLAVE;

7 check the Replication log

MASTER:

mysql> show master status;

+—————–+———-+————–+——————+

| File | Position | Binlog_do_db | Binlog_ignore_db |

+—————–+———-+————–+——————+

| replication.log | 72 | | |

+—————–+———-+————–+——————+

1 row in set (0.00 sec)

SLAVE:

mysql> show slave status;

+—————+—————+—————+—————–+—————–+———–+——————+

| Master_Host | Master_User | Master_Port | Connect_retry | Log_File | Pos | Slave_Running |

+—————+—————+—————+—————–+—————–+———–+——————+

| 10.10.105.11 | repl | 3306 | 0 | replication.log | 72 | Yes |

+—————+—————+—————+—————–+—————–+————+—————–+

—————- —+————————-+————+————-+—————-+

Replicate_do_db | Replicate_ignore_db | Last_errno | Last_error | Skip_counter |

——————–+————————-+————+————-+—————-+

| | 0 | | 0 |

——————–+————————-+————+————-+—————-+

How to Monitoring?

• Heartbeat for performance

– Create a table to hold timestamps

– Add records on the master

– Read them on the slave

– Compare and compute the delay

• Errors

– Watch error log on slave

- Or run a SHOW SLAVE STATUS, fixing Slave_Running is ‘Yes’ if didn’t see Last_error info.