默认情况下,mysql5.1/5.5的复制功能是异步的,异步复制可以提供最佳的性能,主库把binlog日志发送给从库,这一动作就结束了,并不会验证是否接受完毕,但这同时也带来了很高的风险,这就意味着当主服务器发送故障时,有可能从机没有接受到主机发过来的binlog日志,会造成主服务器/从服务器的数据不一致,甚至在恢复时会造成数据丢失。

为了解决这个问题,mysql5.5引入了一种半同步复制模式,该模式可以确保从服务器接受完主服务器发送的binlog日志并写入自己的中继日志relay log里,然后会给主服务器一个反馈,告诉对方已经接收完毕,这时主库线程才返回当前session告知操作完成。当出现超市情况时,源主服务器会暂时切换到异步复制模式,知道至少有一台设置为半同步模式的从服务器及时收到信息为止。

mysql半同步复制

- 阅读剩余部分 -

开启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;