I'm doing replication for one server. Its not the first time I do configure replication between two MySQL database nodes, however since I haven't done it for a few years, my "know how" has mostly vanished so I had some troubles in setting it up. Once I followed some steps to configure replication I had to check if the two MASTER / Slave MySQL db nodes communicate properly. Hence I decided to drop a short post on that just in case if someone has to do the same or if I myself forget how I did it so I can check later on:
1. Check if MASTER MySQL server node is configured properly
The standard way to check a MySQL master node status info is with:
mysql> show master status;
+——————+———-+———————————————————+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+———————————————————+——————+
| mysql-bin.000007 | 106 | database1,database2,database3 | |
+——————+———-+———————————————————+——————+
1 row in set (0.00 sec)
By putting G some extra status info is provided:
mysql> show master statusG;
*************************** 1. row ***************************
File: mysql-bin.000007
Position: 106
Binlog_Do_DB: database1,database2,database3
Binlog_Ignore_DB:
1 row in set (0.00 sec)ERROR:
No query specified
2. Check if Slave MySQL node is configured properly
To check status of the slave the cmd is:
mysql> show slave status;
The command returns an output like:
mysql> show slave status;+———————————-+————-+————-+————-+—————+——————+———————+————————-+—————+———————–+——————+——————-+——————————————————-+———————+——————–+————————+————————-+—————————–+————+————+————–+———————+—————–+—————–+—————-+—————+——————–+——————–+——————–+—————–+——————-+—————-+———————–+——————————-+—————+—————+—————-+—————-+| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error |+———————————-+————-+————-+————-+—————+——————+———————+————————-+—————+———————–+——————+——————-+——————————————————-+———————+——————–+————————+————————-+—————————–+————+————+————–+———————+—————–+—————–+—————-+—————+——————–+——————–+——————–+—————–+——————-+—————-+———————–+——————————-+—————+—————+—————-+—————-+| Waiting for master to send event | HOST_NAME.COM | slave_user | 3306 | 10 | mysql-bin.000007 | 106 | mysqld-relay-bin.000002 | 251 | mysql-bin.000007 | Yes | Yes | database1,database2,database3 | | | | | | 0 | | 0 | 106 | 407 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | |+———————————-+————-+————-+————-+—————+——————+———————+————————-+—————+———————–+——————+——————-+——————————————————-+———————+——————–+————————+————————-+—————————–+————+————+————–+———————+—————–+—————–+—————-+—————+——————–+——————–+——————–+—————–+——————-+—————-+———————–+——————————-+—————+—————+—————-+—————-+
As you can see the output is not too readable, as there are too many columns and data to be displayed and this doesn't fit neither a text console nor a graphical terminal emulator.
To get more readable (more verbose) status for the SQL SLAVE, its better to use command:
mysql> show slave statusG;
Here is a sample returned output:
mysql> show slave statusG;*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: HOST_NAME.COM Master_User: slave_user Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mysql-bin.000007 Read_Master_Log_Pos: 106 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 251 Relay_Master_Log_File: mysql-bin.000007 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: database1,database2,database3 Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 106 Relay_Log_Space: 407 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: 1 row in set (0.00 sec)ERROR: No query specified
If show master status or shwo slave status commands didn't reveal replication issue, one needs to stare at the mysql log for more info.
More helpful Articles
Tags: Auto, Cipher, cmd, condition, configure, Connect, Draft, event, exec, host, info, key, Master, master node, master slave, mysql server, name, Path, quot, Replicate, replication, Retry, server node, show, slave status, someone, Space, SQL, time, User
Mozilla/5.0 (Windows NT 6.1; WOW64; rv:11.0) Gecko/20100101 Firefox/11.0
Hello,
If your are interrested, here is the script I've done a few years ago to monitor the replication into my monitoring system
#!/usr/bin/perl -w
use strict;
use DBI;
# Script to test MySQL replication by TRD 08/01/2008
if (scalar(@ARGV)==3) {
#Variables
my $dbServer="DBI:mysql:database=syslog;host=$ARGV[0]";
my $dbUser=$ARGV[1];
my $dbPwd=$ARGV[2];
#Connect to DB
my $db=DBI->connect($dbServer,$dbUser,$dbPwd) || exit 2;
my $rs=$db->prepare('show slave status');
View CommentView Comment$rs->execute();
my $dr=$rs->fetchrow_hashref();
my $Slave_IO_Running=$dr->{'Slave_IO_Running'};
my $Slave_SQL_Running=$dr->{'Slave_SQL_Running'};
$rs->finish();
$db->disconnect();
if (($Slave_IO_Running eq 'Yes') && ($Slave_SQL_Running eq 'Yes')) {
exit 0;
}
else {
exit 2;
}
}
else {
print "Missing parameter\n";
exit 1;
}
Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.3) Gecko/20100401 Firefox/3.6.3
Hi Thibs,
Long time no hear you. This is nice script.
Only I need to change the name of the monitored database, cause right now I got error;
# perl monitor-replication.pl localhost root password
DBI connect(‘database=syslog;host=localhost’,’root’,…) failed: Unknown database ‘syslog’ at monitor-replication.pl line 15
I’ve made minor modifications to your script and added some text to clear up the script is written by you as well as a link to your site http://qmailrocks.thibs.com for further reference.
The script is here: http://www.pc-freak.net/bshscr/not-mine/monitor-replication.pl
As it was recently Easter the passed sunday and we have the habit to great each other with the paschal Greeting. I would like to great you with our paschal Greeting Jesus is Risen!
Best
View CommentView CommentGeorgi
Mozilla/5.0 (Windows NT 6.1; WOW64; rv:11.0) Gecko/20100101 Firefox/11.0
Thanks for all ! Great andholy Easter to you !
View CommentView CommentMozilla/5.0 (Windows NT 6.1; WOW64; rv:11.0) Gecko/20100101 Firefox/11.0
Thanks for all ! Great and holy Easter to you !
View CommentView CommentFor the script, I can adapt it o allow database name beeing passed as parameter.
Note that according to my experience, it's useless to check replication for all replicated database .. one is enough to check the whole replication status