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
