Mysql Replication (Master-Slave)

Mysql Replication (Master - Slave):

Master Server    : 192.168.0.109
Slave Server    : 192.168.0.201

Master Node:
1. Backup the cnf file and add below entries on cnf file

key_buffer = 128m
myisam_sort_buffer_size = 32m
query_cache_size= 32M
sort_buffer_size = 128M
server-id = 1 
log-slow-queries = /applog/mysqld-slow.log
long_query_time = 2
log-bin = /applog/mysql-bin


2. Create Replication user on Master Node

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO  repl@"192.168.0.201" IDENTIFIED BY 'repl';

Verify user and grants

select host,user from user;
MASTER >show grants for repl@"192.168.0.201";
+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for repl@192.168.0.201                                                                                                                   |
+-------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'192.168.0.201' IDENTIFIED BY PASSWORD '*A424E797037BF97C19A2E88CF7891C5C2038C039' |
+-------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MASTER >


3.Restart mysql

sudo /etc/init.d/mysql restart


4.Backup Master site directory.

block the tables for updates, record bin log position as root user

mysql –u root –p mysql

MASTER>FLUSH TABLES WITH READ LOCK;

record bin log position
MASTER >show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000021 |      107 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)


backup mysql directory

tar -cvf /backup/mysql_bkp.tar /u04/

unlock the tables

MASTER >UNLOCK TABLES;


transfer mysql_bkp.tar backup from master to slave node

scp mysql_bkp.tar mysql@192.168.0.201:/tmp/.


SLAVE Node:

1. Stop mysql

sudo /etc/init.d/mysql stop

2. delete old mysql files
$rm –rf mysql
$tar xvf mysql_bkp.tar

3.Remove  bin log files and bin log index files
$cd /u04/mysql/data
$rm db1-bin.*

4.Add below entry on my.cnf file

server-id = 2

5.start mysql

sudo /etc/init.d/mysql start

6.Issue below statement to add master

CHANGE MASTER TO  MASTER_HOST='192.168.0.109',  MASTER_USER='repl',  MASTER_PASSWORD='repl', MASTER_LOG_FILE='master-bin.000021', MASTER_LOG_POS=107;


7. Start slave

mysql> start slave;


*Check Master staus

mysql>SHOW MASTER STATUS \G
mysql>SHOW MASTER LOGS;

*Check Slave Status

mysql> SHOW SLAVE STATUS \G
If we will see below status means slave is running fine

Slave_IO_Running: Yes
Slave_SQL_Running: Yes,


Test:

create table emp (name char(10),age integer(2));
insert into emp(name,age) values('Tommy',20);


MASTER >use test;
Database changed
MASTER >create table emp (name char(10),age integer(2));
Query OK, 0 rows affected (0.27 sec)

MASTER >insert into emp(name,age) values('Tommy',20);
Query OK, 1 row affected (0.01 sec)

MASTER >

Verify on Slave site

SLAVE >use test;
Database changed
SLAVE >show tables;
+----------------+
| Tables_in_test |
+----------------+
| customers      |
| emp            |
+----------------+
2 rows in set (0.00 sec)

SLAVE >select * from emp;
+-------+------+
| name  | age  |
+-------+------+
| Tommy |   20 |
+-------+------+
1 row in set (0.00 sec)



Notes:

Using MySQL 5.0 and back
SHOW VARIABLES LIKE 'Slave_running';

Using MySQL 5.1+/5.5
select COUNT(1) SlaveThreads
from information_schema.processlist
where user = 'system user';


show slave status;

How To Repair MySQL Replication

SHOW SLAVE STATUS \G

STOP SLAVE;

 simply skip the invalid SQL query:

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

 If you'd like to skip two queries, you'd use SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 2; instead and so on.



mysql> START SLAVE;

Check Slave status

mysql> SHOW SLAVE STATUS \G

mysql> SHOW SLAVE STATUS \G