We have 2 servers, server1 is the known good and server2 is the one that is out of synchronisation.
Make sure nothing is actively querying server2. You can do this by turning on full query logging and tailing the log file for any queries.
mysql> SHOW GLOBAL VARIABLES like 'general_log%' ;
+------------------+-------------------------------+
| Variable_name | Value |
+------------------+-------------------------------+
| general_log | OFF |
| general_log_file | /var/lib/mysql/server2.log |
+------------------+-------------------------------+
2 rows in set (0.00 sec)
mysql> SET GLOBAL general_log = 'ON';
[root@server2 ~]# tail -f /var/lib/mysql/server2.log
Stop the slave replication threads on both servers:
mysql> STOP SLAVE;
mysql> RESET SLAVE;
Leave the slave threads stopped, if you let them run then you may lose your good copy.
Make a mysql dump of you database on server1. I reset the master as well because that cleans up any replication logs that may be laying around, you may not want to do this if you use them for anything other than replication.
mysql> RESET MASTER;
[root@server1 ~]# mysqldump -p --all-databases --master-data > /var/lib/mysql/dbdump-`hostname -s`-`date +%Y%m%dT%H%M%S`.sql
Copy the dump file to server2:
rsync -a --progress /var/lib/mysql/dbdump-server1-20110616T153454.sql server2:/var/lib/mysql/
On server2 import the database dump and start the slave.
[root@server2 ~]#mysql -p < /var/lib/mysql/dbdump-server1-20110616T153454.sql
mysql> START SLAVE;
mysql> SHOW SLAVE STATUSG
Reset the master on server2 so that we don’t replicate any of the import and then get what point the master is up to.
mysql> RESET MASTER;
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 106 | | |
+------------------+----------+--------------+------------------+
Change server1 to be at the same point in replication.
mysql> CHANGE MASTER TO MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 106;
mysql> SHOW SLAVE STATUS\G
mysql> START SLAVE;
Check that Both are now replicating correctly.
Make sure you have turned off logging on server 2 or you will fill up the disks quite rapidly.
mysql> SET GLOBAL general_log = 'ON';
Small script to run a instance of mysql in my home directory using the binary provided by the OS. Maybe some day I will make it more usable but for the time being it suits my needs.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
| #!/bin/bash
MYSQL_HOME=$HOME
MYSQL_USER=$USER
MYSQL_PORT=13306
ACTION=$1
if [ $ACTION == 'init' ]; then
echo "initalising new mysql installation at $MYSQL_HOME/var/lib/mysql"
rm -rf $MYSQL_HOME/var/lib/mysql
mkdir -p $MYSQL_HOME/var/lib/mysql $MYSQL_HOME/mysql/data $HOME/var/lib/mysql/tmp
mysql_install_db --user=$MYSQL_USER --datadir=$MYSQL_HOME/var/lib/mysql/data/
cat < $HOME/.my.cnf
[mysqld]
user=$MYSQL_USER
datadir=$MYSQL_HOME/var/lib/mysql/data
log-error=mysqld.log
pid-file=mysqld.pid
socket=$MYSQL_HOME/var/lib/mysql/tmp/mysql.sock
port=$MYSQL_PORT
[client]
user=$MYSQL_USER
socket=$MYSQL_HOME/var/lib/mysql/tmp/mysql.sock
port=$MYSQL_PORT
[safe_mysqld]
log-error=mysqld.log
pid-file=mysqld.pid
port=$MYSQL_PORT
EOT
echo " Done"
elif [ $ACTION == 'start' ];then
echo -n "Starting mysqld as $MYSQL_USER on $MYSQL_PORT"
nohup mysqld_safe &>/dev/null &
echo " Done"
elif [ $ACTION == 'stop' ];then
PID=$(cat $MYSQL_HOME/var/lib/mysql/data/mysqld.pid)
echo -n "Stopping mysql instance [$PID]"
kill `cat $MYSQL_HOME/var/lib/mysql/data/mysqld.pid`
echo " Done"
else
echo "Unknown command '$1'"
fi |
UPDATED SCRIPT:
I decided to update the script and make it less hacky
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
| #!/bin/bash
MYSQL_HOME=$HOME
MYSQL_USER=$USER
MYSQL_PORT=13306
function init {
echo "initalising new mysql installation at $MYSQL_HOME/var/lib/mysql"
rm -rf $MYSQL_HOME/var/lib/mysql
mkdir -p $MYSQL_HOME/var/lib/mysql $MYSQL_HOME/mysql/data $HOME/var/lib/mysql/tmp
mysql_install_db --user=$MYSQL_USER --datadir=$MYSQL_HOME/var/lib/mysql/data/
cat <<EOT > $HOME/.my.cnf
[mysqld]
user=$MYSQL_USER
datadir=$MYSQL_HOME/var/lib/mysql/data
log-error=mysqld.log
pid-file=mysqld.pid
socket=$MYSQL_HOME/var/lib/mysql/tmp/mysql.sock
port=$MYSQL_PORT
innodb_file_per_table
[client]
user=$MYSQL_USER
socket=$MYSQL_HOME/var/lib/mysql/tmp/mysql.sock
port=$MYSQL_PORT
[safe_mysqld]
log-error=mysqld.log
pid-file=mysqld.pid
port=$MYSQL_PORT
EOT
echo " Done"
}
function start {
echo -n "Starting mysqld as $MYSQL_USER on $MYSQL_PORT"
nohup mysqld_safe &>/dev/null &
echo " Done"
}
function stop {
if [ -e $MYSQL_HOME/var/lib/mysql/data/mysqld.pid ]; then
PID=$(cat $MYSQL_HOME/var/lib/mysql/data/mysqld.pid)
echo -n "Stopping mysql instance [$PID]"
kill $PID
echo " Done"
else
echo "mysqld.pid file doesnt exist"
fi
}
function usage {
echo "Unknown command '$1'"
}
function status {
if [ -e $MYSQL_HOME/var/lib/mysql/data/mysqld.pid ]; then
PID=$(cat $MYSQL_HOME/var/lib/mysql/data/mysqld.pid)
ps -p $PID &>/dev/null
RTN=$?
else
RTN=-1
fi
if [ $RTN -eq 0 ]; then
echo "Mysql Running, pid:$PID"
elif [ $RTN -eq 1 ]; then
echo "mysql stopped but pidfile exists"
elif [ $RTN -eq -1 ]; then
echo "Stopped"
fi
}
case "$1" in
start)
start
;;
stop)
stop
;;
status)
status
;;
restart)
stop
start
;;
init)
init
;;
*)
echo $"Usage: $0 {start|stop|status|restart|init}"
exit 1
esac |