mysql slave servers 问题

(root@db-slave:)[(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.20.81
Master_User: replicator
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.006103
Read_Master_Log_Pos: 267469562
Relay_Log_File: slave-relay.000670
Relay_Log_Pos: 384033426
Relay_Master_Log_File: master-bin.006098
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1053
Last_Error: Query partially completed on the master (error on master: 1053) and was aborted. There is a chance that your master is inconsistent at this point. If
Skip_Counter: 0
Exec_Master_Log_Pos: 384033288
Relay_Log_Space: 5637368787
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: NULL
1 row in set (0.01 sec)

[ERROR] Slave SQL: Query partially completed on the master (error on master: 1053) and was aborted. There is a chance that your master is inconsistent at this point. If you are sure that your master is ok, run this query manually on the slave and then restart the slave with SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

因此我：
slave stop;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
START SLAVE;
跳过一个错的SQL

++++++++++++++++++++++++++++++++
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = N
This statement skips the next N events from the master. This is useful for recovering from replication stops caused by a statement.

This statement is valid only when the slave thread is not running. Otherwise, it produces an error.
++++++++++++++++++++++++++++++++

找的些资料
1、若需要修改my.cnf，必须先删除/var目录下的master.info文件，因为mysql会先读此文件

2、双机备份时，必须使用标准的mysql 命令(在主机上)，如：从别处拷贝数据库文件到A机上/var目录下，在A上，该数据库是能使用的，但是在B机上，没有显示，并且B机器会报错，但是使用 mysqldump命令把数据库从A机导出，在B机上导入，这样才可避免一些不必要的错误

3、测试：
最后重新启动两台机器的mysql.查看状态 及试
①查看master的状态
SHOW MASTER STATUS;
Position不应为0
②查看slave的状态
show slave status;
Slave_IO_Running | Slave_SQL_Running这两个字段 应为YES|YES.
Slave_IO_Running | Slave_SQL_Running这两个字段 应为YES|YES.
show processlist;
会有两条记录与同步有关state为Has read all relay log; waiting for the slave I/O thread to update it
和s Waiting for master to send event .
③错误日志
MySQL安装目录\data\Hostname.err
④CHANGE MASTER TO
如果A的Slave未启动,Slave_IO_Running为No.可能会是B的master的信息有变化,查看B SHOW MASTER STATUS;记录下File,Position字段.假设为’mysql_binary_log.000004′,98 ;在A下执行:Stop Slave;
CHANGE MASTER TOMASTER_LOG_FILE = ‘mysql_binary_log.000004′,MASTER_LOG_POS = 98 ;
Start Slave;
⑤SET GLOBAL SQL_SLAVE_SKIP_COUNTER = n;
如果A的Slave_SQL_Running为No.
Err文件中记录:Slave: Error ‘Duplicate entry ‘1′ for key 1′ on query….可能是master未向slave同步成功,但slave中已经有了记录。造成的冲突.可以在A上执行
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = n;跳过几步，再restart salve;
⑥stop slave
reset slave
start slave
可以清除一些日志。

Chang master to的使用方法如下：
mysql> CHANGE MASTER TO
->MASTER_HOST=’IP’,
->MASTER_USER=’BACKUP’,
->MASTER_PASSWORD=’123456′,
->MASTER_PORT=3306,
->MASTER_LOG_FILE=’master2-bin.001′,
->MASTER_LOG_POS=4,
->MASTER_CONNECT_RETRY=10;

这是在网上发现的一个多台server 跳的perl代码，刚好最近我也在学perl就摘录下来了，原网址是：http://hi.baidu.com/smallfish_xy/blog/item/62944b1e59462c6af724e449.html

#!/usr/bin/perl
use strict;
use warnings;

# get slave status
sub get_status {
my ($ip, $usr, $pass) = @_;
my $info = mysql -u$usr -p$pass -h$ip -e ’show slave status\\G;’;
if (($info =~ /Slave_IO_Running: Yes/) && ($info =~ /Slave_SQL_Running: No/)) {return 1;} else {return 0;}
}
# mysql slave skip
sub slaveskip {
my ($ip, $usr, $pass) = @_;
print “slave error **\n”;
system(”mysql -u$usr -p$pass -h$ip -e ’slave stop;’”);
system(”mysql -u$usr -p$pass -h$ip -e ’set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;’”);
system(”mysql -u$usr -p$pass -h$ip -e ’slave start;’”);
}

my @hosts = qw/
192.168.0.101:root:tt1234
192.168.0.102:root:tt1234
192.168.0.103:root:tt1234
/;
foreach (@hosts) {
my ($ip, $usr, $pass) = split ‘:’;
print “// —– $ip\n”;
my $count = 1;
while ($count < 100000) {
my $status = get_status($ip, $usr, $pass);
print “i: $count status: $status\n”;
last if $status == 0;
slaveskip($ip, $usr, $pass);
select(undef, undef, undef, 0.1);
$count++;
}
print “\n”;
}

exit;