System/MySQL/Replication - Revision history

Eadam: /* Slave initialization commands */

2014-03-09T22:19:32Z

Slave initialization commands

Eadam: /* Init and start a slave */

2012-12-31T16:35:12Z

Init and start a slave

Eadam: /* Configuration */

2012-11-10T09:34:44Z

Configuration

Eadam: /* Init and start a slave */

2012-11-08T20:20:39Z

Init and start a slave

Eadam at 07:30, 21 September 2012

2012-09-21T07:30:47Z

Eadam: /* Auto instanciation script of slave */

2012-05-18T12:31:54Z

Auto instanciation script of slave

Eadam at 12:31, 18 May 2012

2012-05-18T12:31:21Z

Eadam: /* Nagios check */

2012-05-09T11:14:35Z

Nagios check

Eadam at 11:08, 9 May 2012

2012-05-09T11:08:42Z

Eadam: /* Restarting after a failed replicated statement */

2011-12-19T11:25:26Z

Restarting after a failed replicated statement

@@ Line 37: / Line 37: @@
 == Slave initialization commands ==
-  CHANGE MASTER TO
+  CHANGE MASTER TO MASTER_HOST = 'x.x.x.x', MASTER_USER = 'repl', MASTER_PASSWORD = 'passwd', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 259, MASTER_SSL = 0;
 == Repairing after a failed replicated statement ==

@@ Line 24: / Line 24: @@
 First, on the master, dump the data _with_ the --master-data and --single-transaction parameters:
-  mysqldump -R -u root -p --master-data --single-transaction --databases db1 db2 > dump.sql
+  mysqldump -u root -p -R --master-data --single-transaction --databases db1 db2 > dump.sql
 Then source it on the slave and start slave:

← Older revision		Revision as of 09:34, 10 November 2012
Line 1:		Line 1:
	== Configuration ==		== Configuration ==
		+
		+	For a more general mysql configuration, see [[System/MySQL]]

	Edit /etc/my.cnf to have the following options (change server-id if necessary):		Edit /etc/my.cnf to have the following options (change server-id if necessary):

@@ Line 22: / Line 22: @@
 First, on the master, dump the data _with_ the --master-data and --single-transaction parameters:
-  mysqldump -u root -p --master-data --single-transaction --databases db1 db2 > dump.sql
+  mysqldump -R -u root -p --master-data --single-transaction --databases db1 db2 > dump.sql
 Then source it on the slave and start slave:

@@ Line 78: / Line 78: @@
 echo "<CHANGEMASTER>, MASTER_HOST='$SOURCE_HOST', MASTER_USER='$REPL_USER', MASTER_PASSWORD='$REPL_PASS', MASTER_SSL = 0;" | sed "s/<CHANGEMASTER>/$CHGMASTER/g"
 </pre>
 = Nagios check =

@@ Line 69: / Line 69: @@
 SOURCE_HOST=<source>
 ssh deploy@$SOURCE_HOST "time mysqldump -u $SOURCE_USER -p$SOURCE_PASS -A --master-data | gzip > /tmp/fulldump.sql.gz"
 scp deploy@$SOURCE_HOST:/tmp/fulldump.sql.gz /var/tmp/
 ssh deploy@$SOURCE_HOST 'rm /tmp/fulldump.sql.gz'
@@ Line 79: / Line 76: @@
 CHGMASTER=$(gunzip -c /var/tmp/fulldump.sql.gz | head -n 40 | grep 'CHANGE MASTER' | sed 's/;//g')
 echo "<CHANGEMASTER>, MASTER_HOST='$SOURCE_HOST', MASTER_USER='$REPL_USER', MASTER_PASSWORD='$REPL_PASS', MASTER_SSL = 0;" | sed "s/<CHANGEMASTER>/$CHGMASTER/g"
 </pre>

@@ Line 50: / Line 50: @@
   START SLAVE;
   SHOW SLAVE STATUS;
 = Nagios check =

@@ Line 69: / Line 69: @@
 REPLPASSWD=''
-REPL2=fastprod.com
+REPL2=<remote host>
 REPL2USERNAME=repl
 REPL2PASSWD=''
@@ Line 76: / Line 76: @@
 WARNING_VALUE=1000
-iSlave_1=`mysql -h $REPL2 -u $REPL2USERNAME -p$REPL2PASSWD -e "show slave status" | grep bin | cut -f7`
+Slave_1=`mysql -h $REPL2 -u $REPL2USERNAME -p$REPL2PASSWD -e "show slave status" | grep bin | cut -f7`
-iMaster=`mysql -u $REPLUSERNAME -p$REPLPASSWD -e "show master status" | grep bin | cut -f2`
+Master_1=`mysql -u $REPLUSERNAME -p$REPLPASSWD -e "show master status" | grep bin | cut -f2`
-iDiff_1=`expr $iMaster - $iSlave_1`
+Diff_1=`expr $Master_1 - $Slave_1`
-iSlave_2=`mysql -u $REPLUSERNAME -p$REPLPASSWD -e "show slave status" | grep bin | cut -f7`
+Slave_2=`mysql -u $REPLUSERNAME -p$REPLPASSWD -e "show slave status" | grep bin | cut -f7`
-iMaster_2=`mysql -h $REPL2 -u $REPL2USERNAME -p$REPL2PASSWD -e "show master status" | grep bin | cut -f2`
+Master_2=`mysql -h $REPL2 -u $REPL2USERNAME -p$REPL2PASSWD -e "show master status" | grep bin | cut -f2`
-iDiff_2=`expr $iMaster_2 - $iSlave_2`
+Diff_2=`expr $Master_2 - $Slave_2`
-echo "Master1 (local) Log Position:"$iMaster" Slave1 Log Position:"$iSlave_1 " Diff1:"$iDiff_1" Master2 ("$REPL2") Log Position:"$iMaster_2" Slave2 Log Position:"$iSlave_2" Diff2:"$iDiff_2
+echo "Master1 (local):"$Master_1" Slave1:"$Slave_1" ["$Diff_1"] Master2 ("$REPL2"):"$Master_2" Slave2:"$Slave_2" ["$Diff_2"]"
-if [ $iDiff_1 -gt $CRITICAL_VALUE ]; then
+if [ $Diff_1 -gt $CRITICAL_VALUE ]; then
          exit $STATE_CRITICAL
-elif [ $iDiff_1 -gt $WARNING_VALUE ]; then
+elif [ $Diff_1 -gt $WARNING_VALUE ]; then
          exit $STATE_WARNING
-elif [ $iDiff_2 -gt $CRITICAL_VALUE ]; then
+elif [ $Diff_2 -gt $CRITICAL_VALUE ]; then
          exit $STATE_CRITICAL
-elif [ $iDiff_2 -gt $WARNING_VALUE ]; then
+elif [ $Diff_2 -gt $WARNING_VALUE ]; then
          exit $STATE_WARNING
 else

← Older revision		Revision as of 11:08, 9 May 2012
Line 50:		Line 50:
	START SLAVE;		START SLAVE;
	SHOW SLAVE STATUS;		SHOW SLAVE STATUS;
		+
		+	= Nagios check =
		+
		+	Check for replication lag by connecting to master1 and master2
		+
		+	<pre>
		+	#!/bin/sh
		+
		+	# Nagios alert status
		+	STATE_OK=0
		+	STATE_WARNING=1
		+	STATE_CRITICAL=2
		+	STATE_UNKNOWN=3
		+	STATE_DEPENDENT=4
		+
		+	# Script Variables
		+	REPLUSERNAME=repl
		+	REPLPASSWD=''
		+
		+	REPL2=fastprod.com
		+	REPL2USERNAME=repl
		+	REPL2PASSWD=''
		+
		+	CRITICAL_VALUE=3000
		+	WARNING_VALUE=1000
		+
		+	iSlave_1=`mysql -h $REPL2 -u $REPL2USERNAME -p$REPL2PASSWD -e "show slave status" \| grep bin \| cut -f7`
		+	iMaster=`mysql -u $REPLUSERNAME -p$REPLPASSWD -e "show master status" \| grep bin \| cut -f2`
		+	iDiff_1=`expr $iMaster - $iSlave_1`
		+
		+	iSlave_2=`mysql -u $REPLUSERNAME -p$REPLPASSWD -e "show slave status" \| grep bin \| cut -f7`
		+	iMaster_2=`mysql -h $REPL2 -u $REPL2USERNAME -p$REPL2PASSWD -e "show master status" \| grep bin \| cut -f2`
		+	iDiff_2=`expr $iMaster_2 - $iSlave_2`
		+	echo "Master1 (local) Log Position:"$iMaster" Slave1 Log Position:"$iSlave_1 " Diff1:"$iDiff_1" Master2 ("$REPL2") Log Position:"$iMaster_2" Slave2 Log Position:"$iSlave_2" Diff2:"$iDiff_2
		+
		+	if [ $iDiff_1 -gt $CRITICAL_VALUE ]; then
		+	exit $STATE_CRITICAL
		+	elif [ $iDiff_1 -gt $WARNING_VALUE ]; then
		+	exit $STATE_WARNING
		+	elif [ $iDiff_2 -gt $CRITICAL_VALUE ]; then
		+	exit $STATE_CRITICAL
		+	elif [ $iDiff_2 -gt $WARNING_VALUE ]; then
		+	exit $STATE_WARNING
		+	else
		+	exit $STATE_OK
		+	fi
		+	</pre>