2016年6月

开启binlog，且binlog-format=row

新建一个表

[sql]CREATE TABLE student (id int(10) unsigned NOT NULL AUTO_INCREMENT,name varchar(10) NOT NULL DEFAULT '', class int(10), score varchar(10) NOT NULL DEFAULT '', PRIMARY KEY (id));[/sql]

插入测试数据：
insert into student(name,class,score) values('a',1,56),('b',1,61),('c',2,78),('d',2,45),('e',3,76),('f',3,89),('g',4,43),('h',4,90);
模拟update忘加where条件
update student set score='failure';
mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS bin-log.000039 | grep -B 15 'failure'

- 阅读剩余部分 -

开启binlog，且binlog-format=row

新建一个表
CREATE TABLE `student` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`name` varchar(10) NOT NULL DEFAULT '',   `class` int(10), `score` varchar(10) NOT NULL DEFAULT '', PRIMARY KEY (`id`));
插入测试数据
insert into student(name,class,score) values('a',1,56),('b',1,61),('c',2,78),('d',2,45),('e',3,76),('f',3,89),('g',4,43),('h',4,90);
模拟删除
mysql> delete from student;
Query OK, 8 rows affected (0.00 sec)
 
mysql> select * from student;
Empty set (0.00 sec)

- 阅读剩余部分 -

#!/bin/bash
 
########################################################################
##
##此脚本是用来自动处理同步报错的，默认跳过10次。
##
##只有Last_SQL_Error:Could not execute Delete_rows event on table ...;Can't find record ## in ...;Error_code:1032;handler error HA_ERR_KEY_NOT_FOUND;the event's master log ## bin-log.000001.end_log_pos ...
##这种情况才跳过，其他的情况自行处理，以免丢失数据。
##
#########################################################################
 
export LANG=zh_CN
. /root/.bash_profile
v_dir=/usr/local/mysql/bin/
v_user=root
v_password=
v_log=/home/logs/
v_time=10
 
if [ -d "${v_log}" ];then
    echo "${v_log} has existed before."
else
    mkdir ${v_log}
fi
 
echo "" > ${v_log}/slave_status.log
echo "" > ${v_log}/slave_status_error.log
count=1
while true
do
    Seconds_Behind_Master=$(${v_dir}mysql -u${v_user} -p${v_password} -e "show slave status\G;" | awk -F':' '/Seconds_Behind_Master/{print $2}')
    if [ "$Seconds_Behind_Master" != "NULL" ];then
        echo "slave is ok!"
        ${v_dir}mysql -u${v_user} -p${v_password} -e "show slave status\G;" >> ${v_log}/slave_status.log
        break
    else
        echo "" >> ${v_log}/slave_status_error.log
        date >> ${v_log}/slave_status_error.log
        echo "" >> ${v_log}/slave_status_error.log
 
        ${v_dir}mysql -u${v_user} -p${v_password} -e "show slave status\G;" >> ${v_log}/slave_status_error.log
        ${v_dir}mysql -u${v_user} -p${v_password} -e "show slave status\G;" | egrep 'Delete_rows' > /dev/null 2>&1
        if [ $? = 0 ];then
            ${v_dir}/mysql -u${v_user} -p${v_password} -e "stop slave;set global sql_slave_skip_counter=1;start slave;"
        else
            ${v_dir}mysql -u${v_user} -p${v_password} -e "show slave status\G;" | grep 'Last_SQL_Error'
            break
        fi
 
        let count++
 
        if [ $count -gt "${v_times}" ];then
            break
        else
            ${v_dir}mysql -u${v_user} -p${v_password} -e "show slave status\G;" >> ${v_log}/slave_status_error.log
            sleep 2
            continue
        fi
    fi
done

主从数据不一致时，slave上已经有该条记录，但我们又在master上插入了同一条记录，此时就会报错，报错信息如下：

Last_SQL_Error:Could not execute Write_rows event on table ...;
Duplicater entry '2' for key 'PRIMARY',
Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY;
the event's master log bin-log.000006, end_log_pos 924

解决方法：在slave上使用命令“desc abc.student;”先查看一下表结构，如下所示：

mysql> desc abc.student;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10)      | NO   |     |         |                |
| class | int(10)          | YES  |     | NULL    |                |
| score | varchar(10)      | NO   |     |         |                |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

得到主键的字段名id，接着删除重复的主键，命令如下：

delete from student where id=2;

开启主从复制：

start slave;

show slave status\G;

主从数据不一致时，master上已经有该条记录，但slave上没有这条记录，之后若在master上又更新了这条记录，此时就会报错，报错信息如下：

Last_SQL_Error: Could not execure Update_rows event on table abc.student; Can't find record in 'student', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log bin-log.000006, end_log_pos 794

解决方法：在master上，用mysqlbinlog分析一下出错的binlog日志在干什么，如下所示：

mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS bin-log.000006 | grep -A '10' 794
# at 794
#160323 17:10:36 server id 1  end_log_pos 8515214 CRC32 0x90b5f50c     Update_rows: table id 151 flags: STMT_END_F
### UPDATE `abc`.`student`
### WHERE
###   @1=8 /* INT meta=0 nullable=0 is_null=0 */
###   @2='h' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
###   @3=4 /* INT meta=0 nullable=1 is_null=0 */
###   @4='failure' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
### SET
###   @1=8 /* INT meta=0 nullable=0 is_null=0 */
###   @2='h' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
###   @3=4 /* INT meta=0 nullable=1 is_null=0 */
###   @4='90' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */

- 阅读剩余部分 -

  1. 1
  2. 2
  3. 3
  4. 4
  5. 5