Tag Archives: replication

04 Dec

Fixing inconsistency on MySQL Slave of Galera Cluster

Checksum is a standard practice among DBAs to verify the data consistency across replicated nodes. In this post we’re going to review the syncing options for an inconsistent MySQL slave of Galera cluster node.

Here we’re assuming a setup of regular replication to a MySQL instance from one of the Galera cluster nodes.
PXC-slave-checksum Read More

04 Dec

MySQL master master replication monitor with php code

For monitoring replication we know a lot of tools and codes – but this one is different because I wrote it ;)
Well this is fairly simple php code for monitoring a master master replication setup.

It requires a single shared login id available on both MySQL servers.

It will display in tabular format following details:

  • File: Present binary log
  • Position: Binary log position
  • Io run: Slave IO Thread Running status
  • Sql run: SQL Thread Running status
  • ErrorNum: Error number
  • ErrorMeg: Error message

You can easily stop / start each server’s slave (STOP SLAVE / START SLAVE) with a single click.
It also does provide detailed information of global status variables on same page.
It is auto refreshes by default every 2 seconds to provide updated status of both MySQL Servers in Replication.

In code you just need to adjust following parameters as per requirement:

#host-name & ports for replication servers
$slave = “localhost”;
$master = “localhost”;
$slaveport=3306;
$masterport=3307;


#Refresh rate
$refreshRate=2;


#considered a common username password for accessing both servers#
$username=”root”;
$password=”kedar”;

You can download code here: mysql-replication-monitor.php

MySQL Replication Monitor Using PHP:

mysql-replication-monitor-php

11 Nov

Monitor multiple mysql replication using php: Updated

Monitoring a replication is an important aspect. As replication includes multiple nodes, it is essential to track activity and status across all mysql servers involved in replication.

To monitor replication we know commands like:

Show slave status;

Show master status;

Refer: http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-replication.html

But when it comes to non-gui interface, it becomes little tedious.

With some efforts, I managed to make a php script to monitor mysql servers.

It can:

  • Get per second status of slaves.
  • Start / Stop slave from any of the mysql servers in replication.
  • Display error number along with description in case of error in replication.
  • Shows detailed information of both the server’s global status variables.

Well there is no fancy UI, my bad, but it works for me. I’ll try to make it better, may be you can suggest.

Code:

[ Download here: monitor-replication.txt] ( ** Check Updated Monitoring Script. )

<?php

#Resets warning messages

error_reporting(E_ALL ^ E_NOTICE);

#Function to display slave status.

function slavestatus($host,$username,$password,$slaveport=3306)

{

$con=mysql_connect(“$host:$slaveport”,$username,$password)

or die(“can’t connect server”);

$result=mysql_query(“show slave status”);

$timeResult=mysql_query(“select now()”);

$time=mysql_result($timeResult,0,0);

while($status = mysql_fetch_array($result))

{

$file=$status[5];

$position=$status[6];

$sql_run=$status[10];

$io_run=$status[11];

$errorNum=$status[18];

$errorMeg=$status[19];

}

print “<tr align=center>

<td> $time </td>

<td> $host : $slaveport </td>

<td> $file </td>

<td> $position </td>

<td> $sql_run </td>

<td> $io_run </td>

<td> $errorNum </td>

<td> $errorMeg </td>

<td> <form name=’form’ action=’#’ method=’POST’>

<input type=’submit’ name=’stop’  id=’stop’  value=\”STOP $host:$slaveport\”>

<input type=’submit’ name=’start’  id=’start’  value=\”START $host:$slaveport\”>

</form> </td>

<td> <a href=’?detail=$host:$slaveport’> detailed </a></td>

</tr>”;

}

#Function for starting and stopping mysql server.

function start_stop($machine,$username,$password,$task=’no’)

{

if($task==’no’) exit;

$con=mysql_connect($machine,$username,$password) or die(“can’t connect server”);

$sql= $task . ” slave”;

$result=mysql_query($sql);

$_POST = array();

$task=”no”;

$VAR=$_SERVER[“REQUEST_URI”];

#Refreshes page, updates server status in table.

header(‘refresh: 1; url=’.$VAR);

}

#Function for showing detailed status

function get_status($machine,$username,$password)

{

$hostname = $_SERVER[‘SCRIPT_NAME’];

echo “<center><a href=\”$hostname\”>Reset</a></center>”;

if($task==’no’) exit;

$con=mysql_connect($machine,$username,$password) or die(“can’t connect server”);

$sql=”show global status”;

echo “<table border=1 align=center><tr><th>Variable</th><th>Value</th></tr>”;

$res=mysql_query($sql);

while($row = mysql_fetch_assoc($res))

{

echo ‘<tr align=center><td>’.$row[‘Variable_name’].

‘</td><td>’.$row[‘Value’].'</td></tr>';

}

echo “</table>”;

#$_POST = array();

$task=”no”;

}

echo ‘<html>';

if(!isset($_GET[‘detail’]))

{

#Auto refreshing page for continuous status

#content=”3;” page refreshes after 3 seconds

echo ‘<meta http-equiv=”refresh” content=”3;”>';

}

echo'<body>

<h1><font face=”verdana” size=10><small>

MySQL Replication Monitor</small></font></h1>';

#host-name & ports for replication servers

$slave = “localhost”;

$master = “localhost”;

$slaveport=3306;

$masterport=3307;

#considered a common username password for accessing both servers

$username=”root”;

$password=”kedar”;

print “<table border=1><tr>

<th> time </th>

<th> host : port </th>

<th> file </th>

<th> position </th>

<th> io run </th>

<th> sql run </th>

<th> errorNum </th>

<th> errorMeg </th>

<th> Stop / Start </th>

<th> Extra </th>

</tr>”;

slavestatus($slave,$username,$password,$slaveport);

slavestatus($master,$username,$password,$masterport);

print “</table>”;

if (isset($_POST[‘start’])) {

list($task,$machine)=split(” “,$_POST[‘start’]);

start_stop($machine,$username,$password,$task);

}

if (isset($_POST[‘stop’])) {

list($task,$machine)=split(” “,$_POST[‘stop’]);

start_stop($machine,$username,$password,$task);

}

if (isset($_GET[‘detail’])) {

echo “<br><center><b>Showing global status for: “.

$_GET[‘detail’] . “</b></center><br>”;

get_status($_GET[‘detail’],$username,$password);

}

echo ‘<div align=right><font face=”verdana” color=”gray” size=”2px”>

Kedar.</div></div></body>

</html>';

?>

Update: Above script with little changes for monitoring more mysql servers using php script with easiest possible configuration.
Download: MySQL-replication-monitoring-PHP-Script.  ( ** Check Updated Monitoring Script. )

The script give reads server configurations from a config file ‘replmon_conf.txt’ and thus facilitate to monitor simultaneously any number of mysql database servers.

You have to specify connection parameters in replmon_conf.txt as follows:
hostname,username,password,port

To monitor more than one mysql database servers you just need to add one more line of connection parameters.
Hope this helps.

Update 2:  Tookout some time and modified the script for monitoring mysql replication using php.

Download mysql monitor using php: mysql replication monitor php

04 Nov

Quick Multi MySQL Server Installation with Master-Master Replication on Same Windows Box

This article is a brief step-by-step tutorial on the subject task which is divided into the following main parts:

  • Installing two MySQL Server Instances
  • Setting up master-slave replication
  • Setting up slave-master replication

Let’s begin covering those points…

Installing Two MySQL Server Instances on Windows:

1. Install MySQL:

  • Download MySQL Binaries from dev.mysql.com.
  • Install normal MSI Package of mysql. (I used: mysql-essential-5.0.83-win32).

* Following MSI Installation is quite easy, proceed and finish the installation.
This server will be installed on default 3306 port with all windows default settings.

2. Register/Install another MySQL instance:

To have two servers we make sure to have separate values of port, datadir etc. Please check the configuration file for according changes.

Create a duplicate of /etc/my.cnf as /etc/my2.cnf and make according changes.

mysqld-nt.exe –install “MySQL1″ –defaults-file=”C:\Program Files\MySQL\MySQL Server 5.0\my2.ini”

This will register the mysql1 service, and thus we’ll end up having two mysql instances on windows.

3. Start Both MySQL Servers:

net start mysql
net start mysql1

4. Check connecting client from command prompt:

mysql -uroot -p –port=3306
mysql -uroot -p –port=3307

* It will prompt for password, on success you will end up logging in both mysql servers.

Setting up Master-Master Replication on Windows – Single machine:

Setting up Master-Master Replication mainly consists of getting both servers to perform Master and Slave Replication Roles.

For replication I have used database named ‘master’ having table named ‘test’ with following structure:

Create database master;
Use master;
CREATE TABLE `temp` (
`id` int(10) NOT NULL auto_increment,
PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Here we’ve two MySQL server instances, namely mysql and mysql1.

We will setup Master-Slave Replication from mysql1 to mysql. Later we will setup Master-Slave Replication from mysql to mysql1.
For your reference please check the appendix section for sample configuration files along with below mentioned instructions.

1. Setting Master Slave “mysql1 –> mysql”:

Considering:

mysql1 @ port: 3307 as Master
mysql @ port: 3306 as Slave

– On mysql1(master): Add following line to my.ini:

binlog-do-db=master

– We require a ‘Replication Slave’ privileged user on Master for slave to connect:

mysql> Grant replication slave on *.* to ‘replication1’@’localhost’ identified by ‘slave1′;

On mysql(slave):
– Add following line to my.ini:

#Conn. Pmtr for slave:

master-host = localhost
master-user = replication1
master-password = slave1
master-port = 3307

*It’s not a good idea to add such parameters in configuration file; but this is just learning purpose. Use CHANGE MASTER& command.

– Start Slave Server:

net start mysql

– On Slave Client:

mysql>Start Slave;

** Check for working Master-Slave Replication.**
** Stop both MySQL Servers **

2. Setting Slave Master “mysql1 <– mysql” :


Considering:

mysql1 @ port: 3307 as Slave
mysql @ port: 3306 as Master

– On mysql(master) Add following line to my.cnf:

binlog-do-db=master

– Start Master Server:

net start mysql1

– We require a ‘Replication Slave’ privileged user:

mysql> Grant replication slave on *.* to ‘replication2’@’localhost’ identified by ‘slave2′;

– On mysql1 (slave):
Add following line to my.cnf:

#Conn. Pmtr for slave:

master-host = localhost
master-user = replication2
master-password = slave2
master-port = 3306

– Start Slave Server:

net start mysql

– On Slave client:

mysql>Start Slave;

** Check for working Master-Slave **
** Check for working Master-Master **

And that’s it.  We now have two different instances of MySQL server running with a Master-Master replication scheme.  Hopefully, you found this tutorial helpful.  Please see examples of the final configuration files in the appendix below along with references for more information regarding this topic.

Appendix: Configuration Files for master-master mysql setup:

Following is the my.ini [configuration] file for first MySQL Server Instance:

##my.ini for mysql (Regularly installed):
[client]
port=3306
[mysqld]
server-id=2
port=3306
# Replication
log-bin=binlog
#For this as master:
binlog-do-db=master
binlog-ignore-db=mysql
binlog-ignore-db=test
#Conn. Pmtr for slave:
master-host = localhost
master-user = replication1
master-password = slave1
master-port = 3307
basedir=”C:/Program Files/MySQL/MySQL Server 5.0/”
datadir=”C:/Program Files/MySQL/MySQL Server 5.0/Data/”
auto_increment_increment=1
auto_increment_offset=1

Following is the my2.ini [configuration] file for second MySQL instance named “mysql1″:

##my2.ini for Mysql1:
[client]
port=3307
[mysqld]
server-id=1
port=3307
# Replication
log-bin=binlog
#For this as master:
binlog-do-db=master
binlog-ignore-db=mysql
binlog-ignore-db=test
#Conn. Pmtr for slave:
master-host = localhost
master-user = replication2
master-password = slave2
master-port = 3306
basedir=”C:/Program Files/MySQL/MySQL Server 5.0/”
datadir=”C:/Program Files/MySQL/MySQL Server 5.0/Data1/”
auto_increment_increment=1
auto_increment_offset=2

References:

  1. http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html
  2. http://dev.mysql.com/doc/refman/5.0/en/multiple-windows-servers.html
  3. http://dev.mysql.com/doc/refman/5.0/en/change-master-to.html
  4. http://dev.mysql.com/doc/refman/5.0/en/replication-options-master.html#sysvar_auto_increment_increment
04 Nov

Replication slave lag monitoring using heartbeat and windows batch scripts

“Show Slave Status” command has a last column “Seconds_Behind_Master”, which gives us idea about how much time slave is lagging behind master. It is an important to be considered parameter in monitoring and maintaining replication.

This article explains us a way to monitor replication slave lag time. It also includes a sample batch scripts to automate the monitoring process, makes it easy to understand.

Whats wrong with “Seconds_Behind_Master”:

Show Slave Status command; does shows us Seconds_Behind_Master.

Now Documentation says: The field measures the time difference in seconds between the slave SQL thread and the slave I/O thread. If the network is slow, this is not a good approximation; the slave SQL thread may quite often be caught up with the slow-reading slave I/O thread, so Seconds_Behind_Master often shows a value of 0, even if the I/O thread is late compared to the master. In other words, this column is useful only for fast networks.

Now this has been discussed at a lot of places for a lot of times, the solution is to periodically insert a row into a “heartbeat” table on the master server. Let it get replicated on slave and check the “heartbeat”s on the slave. This will surely explain you replication time and the slave behind master.

So whats the idea?

Consider descriptions of following two mysql functions:

current_timestamp() : The function when used in a query replicates its output same across the slaves. Thus even if the query is executed at a later time on slave, the value stored would be same as what is there in the master.

sysdate() : The behavior of this function is different from the above. It stores the current output of the function at the slave when executed. Thus the output produced could be different from what was produced at the master.

Considering behavior, if we have both values inserted on master server; we will get it replicated on slave. But for sysdate() function, the time will be of slave’s and that will help us calculating the slave lag comparing with current_timestamp() value.

Implementing Slave Behind Master Monitoring:

I’ve tried to implement replication slave lag monitoring through “heartbeat” method.

Here for making an example I’ve considered MySQL Server with port 3307 as Master and Server with port 3306 (Default) as Slave for monitoring Slave Lag.

Create table on Master MySQL Server:

1:
2:
3:
4:
5:
create table heartbeat
(
  master_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  slave_time TIMESTAMP NOT NULL
) ENGINE=MyISAM;

Periodical value insertion – Generate Heartbeat:

1:
insert into heartbeat(slave_time) values(SYSDATE());

One more case is, when slave is intentionally kept behind master. We can surely add up that time and make according insertion.

1:
2:
3:
Insert Into heartbeat(slave_time)
Values( from_unixtime((unix_timestamp(sysdate()) + time_to_sec('HH:MM:SS'))) ;

Here, HH:MM:SS is slave’s known time of lagging behind master.

We have following queries to monitor slave-lag:

First query will tell us the time difference of slave and master query execution time. Which is actually caused by the functions explained above.

1:
2:
3:
4:
5:
Select master_time, timediff(slave_time, master_time)
From heartbeat
Where DATE(master_time) = DATE(NOW())
Order By master_time;

This second query will tell us the time difference of last query execution time from replication to now.

1:
select timediff(NOW(), max(master_time)) from heartbeat;

Batch Scripts For Windows:

To understand above setup and making it little automated, I wrote couple of batch scripts.

Following batch script will auto insert one row into heartbeat table on per minute basis on Master.

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
##insert-master.bat
:begin
@echo off
set StartTime=%TIME%
set StartMin=%StartTime:~3,2%
:start
set EndTime=%TIME%
set EndMin=%EndTime:~3,2%
set /a Hour_Diff=StartMin - EndMin >nul
if %Hour_Diff%==0 (
goto start )
echo Beating...
mysql -uroot -pXXXX -P3307 -e "use master;insert into heartbeat(slave_time) values(SYSDATE());"
goto begin
pause

Similar batch created to monitor the lag time on Slave MySQL Server.

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:




19:
20:
21:
22:
23:
##monitor-slave.bat
@echo off
echo Observing slave delay - per minute basis
echo SBM -  Slave Behind Master / Last Query Executed.
echo ETD - Execution Time Difference
echo ----------------------------------
echo SBM  -  ETD - Slave Status
echo ----------------------------------
:begin
set StartTime=%TIME%
set StartMin=%StartTime:~3,2%
:start
set EndTime=%TIME%
set EndMin=%EndTime:~3,2%
set /a Hour_Diff=StartMin - EndMin >nul
if %Hour_Diff%==0 (
         goto start )
FOR /F "skip=1 tokens=*" %%I IN ('mysql -uroot -pXXXX -e
"use master;select TIME_TO_SEC(timediff(NOW(), max(master_time)) )
last_query_time, second(timediff(max(slave_time), max(master_time)))
master_slave_difference from heartbeat where DATE(master_time) = DATE(NOW())
order by master_time desc;"') DO set var=%%I%
@echo %var%
FOR /F "tokens=11 delims='|        '" %%G IN ('mysql -uroot -pXXXX -e "show slave status;"') do @echo                  %%G
FOR /F "tokens=12 delims='|        '" %%G IN ('mysql -uroot -pXXXX -e "show slave status;"') do @echo                  %%G
goto begin
pause

PS: Set passwords accordingly.

Using sample batch scripts we will be able to understand the replication lag.
ETD, the Execution Time Difference from master to slave shows us how long slave takes to replicate once master has executed the statement. By query we can understand it is difference between current_timestamp() and sysdate().
SBM, Slave Behind Master, points us the time in seconds last query executed on slave from master.
The monitoring script will also tell you the IO and SQL thread status after each minute.

-- Kedar Vaijanapurkar --