Archive for October, 2009

How to configure master and slave replication in mysql server

Thursday, October 8th, 2009

All the contents of this article has taken from ” High Performance Mysql Server” For the simplicity I just added some extra visualize picture from my running server . For this reason the values(i.e time, log position) between pics and the book does not match.

Primary Server : 192.168.1.2

Slave Server : 192.168.1.3

In picture I used, Primary server(NODE2) Slave (Beaver)

Step -1

Create replication account on each server,Example user repl:

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*
-> TO repl@’192.168.0.%’ IDENTIFIED BY ‘p4ssword’;

Configure Master Server:

Edit my.cnf (/etc/my.cnf- For default mysql server setup) and insert bellow lines:

log_bin = mysql-bin
server_id = 10

Now restart mysql server ( service mysqld restart)

Now if you go to /var/lib/mysql/

you should see some files like bellow picture:

Log files created(Red circle)

Log files created(Red circle)

To verify that the binary log file is created on the master, run
SHOW MASTER STATUS as shown in bellow picture

Verifying if binary log is created.

Verifying if binary log is created.

Configure Slave Server:

Edit my.cnf and add bellow lines

log_bin = mysql-bin
server_id = 2
relay_log = mysql-relay-bin
log_slave_updates = 1

Now Restart Mysql server.

Starting the Slave:

Log into mysql server and execute the bellow commands

mysql> CHANGE MASTER TO MASTER_HOST=’server1′,
-> MASTER_USER=’repl’,
-> MASTER_PASSWORD=’p4ssword’,
-> MASTER_LOG_FILE=’mysql-bin.000001′,
-> MASTER_LOG_POS=0;

The MASTER_LOG_POS parameter is set to 0 because this is the beginning of the log.
After you run this, you should be able to inspect the output of SHOW SLAVE STATUS and
see that the slave’s settings are correct:

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: server1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 4
Relay_Log_File: mysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
…omitted…
Seconds_Behind_Master: NULL

Salve Server output should be something like this

Salve Server output should be something like this

The Slave_IO_State, Slave_IO_Running, and Slave_SQL_Running columns show that
the slave processes are not running,also notice that the log position
is 4 instead of 0. That’s because 0 isn’t really a log position; it just means “at the
start of the log file.” MySQL knows that the first event is really at position 4.*

To start replication, run the following command:
mysql> START SLAVE;

This command should produce no errors or output. Now inspect SHOW SLAVE STATUS
again:
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: server1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 164
Relay_Log_File: mysql-relay-bin.000001
Relay_Log_Pos: 164
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
…omitted…
Seconds_Behind_Master: 0

Slave is awitting for master

Slave is awiting for master

Notice that the slave I/O and SQL threads are both running, and Seconds_Behind_
Master is no longer NULL. The I/
O thread is waiting for an event from the master, which means it has fetched all of the
master’s binary logs. The log positions have incremented, which means some events
have been fetched and executed (your results will vary). If you make a change on the
master, you should see the various file and position settings .

You will also be able to see the replication threads in the process list on both the master
and the slave. On the master, you should see a connection created by the slave’s I/O
thread:
mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 55
User: repl
Host: slave1.webcluster_1:54813
db: NULL
Command: Binlog Dump
Time: 610237
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL

On the Master:

Server has sent all bin logs to slave

Server has sent all bin logs to slave

On the Slave :

On the slave, you should see two threads. One is the I/O thread, and the other is the
SQL thread:
mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 1
User: system user
Host:
db: NULL
Command: Connect
Time: 611116
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 33
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL

Figure:

Slave is Updating binlog

Slave is Updating binlog

The sample output we’ve shown comes from servers that have been running for a
long time, which is why the I/O thread’s Time column on the master and slave has a
large value. The SQL thread has been idle for 33 seconds on the slave, which means
no events have been replayed for 33 seconds.

Pictures in Big size: