Fork me on GitHub
Suzf  Blog

Tag Mysql

MySQL 5.7 multi-source replication

英文原文:MySQL 5.7 multi-source replication

近日ORACLE发布几个新的功能在最新的Mysql5.7.2的版本上,由此有了此篇文章。大多数的改善是在数据库性能和复制相关的功能上,这个新版本会带给我们不可思议的效果。

在这篇文章里,我将要用一些简单的步奏来尝试了解这新的多源复制工作原理以及我们怎样进行自己的测试。需要说明的是,这还是一个开发版本,不是给生产环境 准备的。因此这篇文章是打算给那些想了解此新功能的人,看看它是如何在应用中工作的,都是在临时环境中进行相关操作。

什么是多源复制?

首先,我们需要清楚 multi-mastermulti-source 复制不是一样的. Multi-Master 复制通常是环形复制,你可以在任意主机上将数据复制给其他主机。

Mysql 单机多实例详解

应用场景
采用了数据伪分布式架构的原因,而项目启动初期又不一定有那多的用户量,为此先一组物理数据库服务器,但部署多个实例,方便后续迁移
为规避mysql对SMP架构不支持的缺陷,使用多实例绑定处理器的办法,把不同的数据库分配到不同的实例上提供数据服务
一台物理数据库服务器支撑多个数据库的数据服务,为提高mysql复制的从机的恢复效率,采用多实例部署
已经为双主复制的mysql数据库服务器架构,想部分重要业务的数据多一份异地机房的热备份,而mysql复制暂不支持多主的复制模式,且不给用户提供服务,为有效控制成本,会考虑异地机房部署一台性能超好的物理服务器,甚至外加磁盘柜的方式,为此也会部署多实例
传统游戏行业的MMO/MMORPG,以及Web Game,每一个服都对应一个数据库,而可能要做很多数据查询和数据订正的工作,为减少维护而出错的概率,也可能采用多实例部署的方式,按区的概念分配数据库

[译] Repair MySQL 5.6 GTID replication by injecting empty transactions

在前面文章我提到了两种关于如何修复 Mysql 5.6 GTID 主从数据库
我没有提到大家说熟知的方法 - ` GLOBAL SQL_SLAVE_SKIP_COUNTER = n`。原因很简单,如果你使用的是MysqlGTID,它是不工作的。
那么问题来了:

有没有简单的方法跳过这单一事务 ?
是的!注入空事务。让我们想象一下,从服务器上的复制不工作,因为下面一个错误:

Last_SQL_Error: Error 'Duplicate entry '4' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'insert into t VALUES(NULL,'salazar')'
Retrieved_Gtid_Set: 7d72f9b4-8577-11e2-a3d7-080027635ef5:1-5
Executed_Gtid_Set: 7d72f9b4-8577-11e2-a3d7-080027635ef5:1-4

这里有不同的方法可以找到失败的事务。你可以检查二进制日志,或者你也可以检查retrieved_gtid_set executed_gtid_set 从显示输出的例子中我们可以看出。此从服务器检索到1到5的交易,但只执行了1到4。这意味着交易5是导致问题的一个问题。

因为在GTID中sql_slave_skip_counter不工作,我们需要找到一种办法来忽视事务。我们可以在GTID中创建空事务以跳过它。

STOP SLAVE;
SET GTID_NEXT="7d72f9b4-8577-11e2-a3d7-080027635ef5:5";
BEGIN; COMMIT;
SET GTID_NEXT="AUTOMATIC";
START SLAVE;
[...]
Retrieved_Gtid_Set: 7d72f9b4-8577-11e2-a3d7-080027635ef5:1-5
Executed_Gtid_Set: 7d72f9b4-8577-11e2-a3d7-080027635ef5:1-5

START SLAVE 之后 检查事务5已经在它自己的二进制文件中了,这就意味着它已经执行过了。

这是一个简单的方法来跳过一些事务,但是,你应该想到主从服务器之间数据不一致。pt-table-checksum 在这里可以帮助你,它可以在Percona Toolkit for mysql 中找到。

上周我谈及了很多关于GTID的东西在多伦多 Percona Mysql 大学。 它包括MySQL 5.6 gtid 这个新功能的工作概述,可以帮助人们。这是来自该会议的幻灯片。我希望你觉得它有用:MySQL 5.6 GTID in a nutshell
 

原文: https://www.percona.com/blog/2013/03/26/repair-mysql-5-6-gtid-replication-by-injecting-empty-transactions/

 

How-to Use LVM Snapshot To Backup MySQL

如果你Mysql的数据存储在LVM逻辑卷上,那么使用 LVM Snapshot 是对Mysql数据取得一个时间点的完全备份的一个低影响行为的一个方法。唯一的困难是,必须保证数据文件处于`安全`的状态(即所有数据已经写到硬盘文件,而不是仅仅存在于内存中);

为什么基于快照备份Mysql是一个好的选择?

大部分场景接近热备份 您可以在应用程序运行时执行这种类型的备份。无需关闭服务,只需设置只读或是类似的操作。

支持所有本地磁盘存储引擎 它适用于MyISAM和Innodb和BDB,它也适用于Solid,PrimeXT和Falcon存储引擎。

低开销 因为它只是文件副本 因此对服务器的开销是最小的

快速备份

易于对数据进行压缩归档备份 将其备份到磁带,FTP或任何网络备份软件;它很容易,因为你只需要复制文件。

快速恢复 恢复时间与将数据恢复和标准MySQL崩溃恢复一样快,并且可以进一步减少。

免费没有额外的商业工具,Innodb热备份需要执行备份。

不过这里也有些不足之处

显然易见这里需要快照的兼容性

可能会需要 root 权限

很难预测停机时间我提到这个解决方案通常是热备份,但糟糕的是,很难估计它是什么时候,它是不是 - FLUSH TABLES WITH READ LOCK 可能需要相当长的时间在具有长查询的系统上完成。

多个卷上的数据的问题 如果您在单独的设备或仅跨越多个卷的数据库上有日志,则会遇到麻烦,因为您无法在所有数据库中获得一致的快照。 一些系统可能能够对许多卷执行原子快照。

下面是是使用LVM Snapshot备份的基本过程

1. 打开多个终端会话,一个用于登陆Mysql 执行相关命令; 一个用于生成LVM 快照

2. 在终端会话1中,连接Mysql 设置读锁;所以不会再有新的数据写入。但是不要使用 `mysqladmin` 执行操作,并确保您的数据库会话保持打开,否则读取锁将在客户端断开连接时立即删除。

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;    # 重建 Mysql Slave

3. 在另一个终端会话中,创建 Mysql 数据卷的 LVM 快照。在本例中,我们假设 数据目录为 /var/lib/mysql LVM 逻辑卷名称为 /dev/vg0/mysql. 确保快照的空间足够大,以便在执行备份时有足够的空间容纳新数据进入数据库。如果空间给的太小,快照会失效备份也会终止。

lvcreate -L8G -s -n mysql-backup /dev/vg0/mysql

4. 回到之前打开Mysql 连接会话的终端,释放读锁;这样正常的数据操作就恢复了

UNLOCK TABLES;

5. 挂载快照到方便的地方

mkdir -p /mnt/mysql-backup
mount -o nouuid -t xfs /dev/vg0/mysql-backup /mnt/mysql-backup

如果你现在查看 /mnt mysql-backup 的内容,你应该看到一个 /var/lib/mysql 的副本,就像创建快照时的一样。

6. 使用您选择的方法将整个目录复制到您选择的位置。 例如

tar -C /mnt -czf ~/mysql-backup.tar.gz mysql-backup

或者启动一个新的Mysql实例,将数据导出

7. 一旦完成备份, 卸载 并移除快照

umount /mnt/mysql-backup
lvremove -f /dev/vg0/mysql-backup

如上所述,在创建快照时,数据库连接必须保持打开状态,以便保持锁定。编写脚本的唯一方法是使用支持数据库连接的语言。 这里是一个perl的例子。

#!/usr/bin/perl

use DBI;

# Connect to the local database
$dbh = DBI->connect('DBI:mysql:host=localhost;database=mysql', 'root', 'password') || die;

# Flush and lock tables to prepare for LVM snapshot
$dbh->do('FLUSH TABLES WITH READ LOCK;');

# Create LVM snapshot volume
system('lvcreate -L8G -s -n mysqlbackup /dev/vg0/mysql');

# Release table lock
$dbh->do('UNLOCK TABLES;');

# Disconnect from database
$dbh->disconnect();

相关链接

[0] https://dev.mysql.com/doc/refman/5.7/en/backup-methods.html

[1] https://www.percona.com/blog/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup/

[2] https://www.badllama.com/content/mysql-backups-using-lvm-snapshots

 

How-to Load CSV data into mysql use Python

逗号分隔值(Comma-Separated Values,CSV,有时也称为字符分隔值,因为分隔字符也可以不是逗号),其文件以纯文本形式存储表格数据(数字和文本)。纯文本意味着该文件是一个字符序列,不含必须像二进制数字那样被解读的数据。CSV文件由任意数目的记录组成,记录间以某种换行符分隔;每条记录由字段组成,字段间的分隔符是其它字符或字符串,最常见的是逗号或制表符。通常,所有记录都有完全相同的字段序列。

CSV文件格式的通用标准并不存在,但是在RFC 4180中有基础性的描述。使用的字符编码同样没有被指定,但是7-bitASCII是最基本的通用编码。

 

日常工作中总是需要将采集的数据保存到数据库中对以往数据的对比。下面以MySQL数据为例说明。

 

#!/usr/bin/env python
# -*- coding:utf-8 -*-

import csv
import MySQLdb

conn = MySQLdb.connect(host='localhost',
                       user='root',
                       passwd='',
                       db='test')
cur = conn.cursor()

cur.execute('DROP TABLE IF EXISTS `test_csv`;')
create_table = '''
  CREATE TABLE `test_csv` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col1` varchar(50) NOT NULL,
  `col2` varchar(30) DEFAULT NULL,
  `col3` varchar(30) DEFAULT NULL,
  `col4` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) 
'''

cur.execute(create_table)

csv_data = csv.reader(file('test.csv'))
for row in csv_data:
    #print row
    cur.execute('INSERT INTO test_csv(col1, col2, col3, col4)'
                'VALUES("%s", "%s", "%s", "%s")',
                row)

# close the connection to the database.
conn.commit()
cur.close()
conn.close()
print "Done"

执行结果

mysql>  select * from test_csv;
+----+------+-------+--------+--------+
| id | col1 | col2  | col3   | col4   |
+----+------+-------+--------+--------+
|  1 | '1'  | 'UE1' | '6295' | '1648' |
|  2 | '2'  | 'UE9' | '9805' | '4542' |
|  3 | '3'  | 'MQ2' | 'NONE' | 'NONE' |
|  4 | '4'  | 'BD8' | 'NONE' | 'NONE' |
|  5 | '5'  | '908' | '1548' | '1099' |
|  6 | '6'  | 'dle' | '1548' | '1098' |
|  7 | '7'  | '808' | '1548' | '1099' |
|  8 | '8'  | '108' | '1548' | '1098' |
|  9 | '9'  | 'B08' | '1548' | '1098' |
+----+------+-------+--------+--------+
9 rows in set (0.00 sec)

源CSV文件

^_^[14:36:16][[email protected] ~]#cat test.csv 
1,UE1,6295,1648
2,UE9,9805,4542
3,MQ2,NONE,NONE
4,BD8,NONE,NONE
5,908,1548,1099
6,dle,1548,1098
7,808,1548,1099
8,108,1548,1098
9,B08,1548,1098

How-to use MySQL-python module

对于数据库操作,和 TCP/IP 的三次握手异曲同工之妙,建立连接,执行操作,断开连接。当然这就需要建立连接的工具

Python连接mysql的方案有oursql、PyMySQL、 myconnpy、MySQL Connector 等,不过本篇说的确是另外一个类库MySQLdb,MySQLdb 是用于Python链接Mysql数据库的接口,它实现了 Python 数据库 API 规范 V2.0,基于 MySQL C API 上建立的。

Reference

Package MySQL-python

MySQLdb User's Guide

安装

yum install Mysql-python -y

pip install Mysql-python

源码解压缩进入主目录执行 python setup.py install

使用

1. 数据库的连接

MySQLdb提供了connect方法用来和数据库建立连接,接收数个参数,返回连接对象:
conn=MySQLdb.connect(host="hostname",user="username",passwd="password",db="dbname",charset="utf8")

比较常用的参数包括:
host:数据库主机名.默认是用本地主机
user:数据库登陆名.默认是当前用户
passwd:数据库登陆的秘密.默认为空
db:要使用的数据库名.没有默认值
port:MySQL服务使用的TCP端口.默认是3306
charset:数据库编码
然后,这个连接对象也提供了对事务操作的支持,标准的方法:
commit() 提交
rollback() 回滚

#!/usr/bin/env python
# -*- coding=utf-8 -*-

import MySQLdb
 
try:
    conn=MySQLdb.connect(host='localhost',user='root',passwd='',port=3306)
    
    # 使用cursor()方法获取操作游标
    cur=conn.cursor()

    # 选择数据库
    conn.select_db('test')

    # 使用execute方法执行SQL语句
    cur.execute("SELECT VERSION()")

    # 使用 fetchone() 方法获取一条数据库。
    data = cur.fetchone()
    print "Database version : %s " % data
    
    # 关闭连接
    conn.commit()
    cur.close()
    conn.close()
 
except MySQLdb.Error,e:
     print "Mysql Error %d: %s" % (e.args[0], e.args[1])

执行结果

Database version : 5.1.73

2. cursor方法执行与返回值

cursor方法提供两类操作:
1.执行命令
2.接收返回值
cursor用来执行命令的方法

#用来执行存储过程,接收的参数为存储过程名和参数列表,返回值为受影响的行数
callproc(self, procname, args)
#执行单条sql语句,接收的参数为sql语句本身和使用的参数列表,返回值为受影响的行数
execute(self, query, args)
#执行单挑sql语句,但是重复执行参数列表里的参数,返回值为受影响的行数
executemany(self, query, args)
#移动到下一个结果集
nextset(self)
cursor用来接收返回值的方法
#接收全部的返回结果行.
fetchall(self)
#接收size条返回结果行.如果size的值大于返回的结果行的数量,则会返回cursor.arraysize条数据
fetchmany(self, size=None)
#返回一条结果行
fetchone(self)
#移动指针到某一行.如果mode='relative',则表示从当前所在行移动value条,如果mode='absolute',则表示从结果集的第一行移动value条
scroll(self, value, mode='relative')
#这是一个只读属性,并返回执行execute()方法后影响的行数
rowcount

3.  数据库操作

a.创建表

#!/usr/bin/env python
# -*- coding=utf-8 -*-

import MySQLdb

 
try:
    conn=MySQLdb.connect(host='localhost',user='root',passwd='',port=3306)
    
    # 使用cursor()方法获取操作游标

    cur=conn.cursor()
    # 选择数据库
    conn.select_db('test')


    # 如果数据表已经存在使用 execute() 方法删除表。
    cur.execute("DROP TABLE IF EXISTS stu_info")

    # 创建数据表SQL语句
    sql = """CREATE TABLE stu_info (
             `id` int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
             `name` CHAR(20) NOT NULL,
             `age` INT,
             `sex` CHAR(6))"""
    cur.execute(sql)

    conn.commit()
    cur.close()
    conn.close()
 
except MySQLdb.Error,e:
     print "Mysql Error %d: %s" % (e.args[0], e.args[1])

b. 插入数据

 添加单行记录

#!/usr/bin/env python
# -*- coding=utf-8 -*-

import MySQLdb
 
# 创建连接
conn=MySQLdb.connect(host='localhost',user='root',passwd='',port=3306)
# 使用cursor()方法获取操作游标

cur=conn.cursor()
# 选择数据库
conn.select_db('test')

# 插入一条记录
sql = "insert into stu_info(name,age,sex) values(%s,%s,%s)"
cur.execute(sql,('Lisa',18,'female'))

conn.commit()
cur.close()
conn.close()

添加多行记录

#!/usr/bin/env python
# -*- coding=utf-8 -*-

import MySQLdb

# 创建连接
conn = MySQLdb.connect(host='localhost', user='root', passwd='', port=3306)
# 使用cursor()方法获取操作游标

cur = conn.cursor()
# 选择数据库
conn.select_db('test')

# 插入多条记录
sql = "insert into stu_info(name, age, sex) values(%s, %s, %s)"
mutl_line = [
    ('jack', 20, 'male'),
    ('Danny', 19, 'female'),
    ]
cur.executemany(sql, mutl_line)

conn.commit()
cur.close()
conn.close()

executemany()方法可以一次插入多条值,执行单挑sql语句,但是重复执行参数列表里的参数,返回值为受影响的行数。

c. 查询数据

#!/usr/bin/env python
# -*- coding=utf-8 -*-

import MySQLdb

# 创建连接
conn = MySQLdb.connect(host='localhost', user='root', passwd='', port=3306)
# 使用cursor()方法获取操作游标

cur = conn.cursor()
# 选择数据库
conn.select_db('test')

# 获取记录条数
rec_count = cur.execute("select * from stu_info")
print "There have %s records" % rec_count

#打印表中的数据
#rows = cur.fetchmany(rec_count)
rows = cur.fetchall()
for row in rows:
    print row

conn.commit()
cur.close()
conn.close()

执行结果

There have 3 records
(1L, 'Lisa', 18L, 'female')
(2L, 'jack', 20L, 'male')
(3L, 'Danny', 19L, 'female')

上面的代码,用来将所有的结果取出,不过打印的时候是每行一个元祖打印,现在我们使用方法,取出其中的单个数据:

import MySQLdb
 
# 创建连接
conn=MySQLdb.connect(host='localhost',user='root',passwd='',port=3306)
# 使用cursor()方法获取操作游标

cur=conn.cursor()
# 选择数据库
conn.select_db('test')

# 执行那个查询,这里用的是select语句
cur.execute("select * from stu_info")

# 使用cur.rowcount获取结果集的条数
numrows = int(cur.rowcount)
for i in range(numrows):
  row = cur.fetchone()
  print str(row[0]) + "," + row[1] + "," + str(row[2]) + "," + row[3]

conn.commit()
cur.close()
conn.close()

执行结果

1,Lisa,18,female
2,jack,20,male
3,Danny,19,female

 

使用字典cursor取得结果集(可以使用表字段名字访问值)

import MySQLdb
 
# 创建连接
conn=MySQLdb.connect(host='localhost',user='root',passwd='',port=3306)
# 使用cursor()方法获取操作游标

cur=conn.cursor()
# 选择数据库
conn.select_db('test')

# 获取连接上的字典cursor,注意获取的方法,
# 每一个cursor其实都是cursor的子类
cur = conn.cursor(MySQLdb.cursors.DictCursor)

# 执行语句不变
cur.execute("SELECT * FROM stu_info")

# 获取数据方法不变
rows = cur.fetchall()

# 遍历数据也不变(比上一个更直接一点)
for row in rows:
    # 这里,可以使用键值对的方法,由键名字来获取数据
    print "%s %s %s" % (str(row["id"]), row["name"], str(row["age"]))


conn.commit()
cur.close()
conn.close()

执行结果:

1 Lisa 18
2 jack 20
3 Danny 19

使用Prepared statements执行查询

import MySQLdb
 
# 创建连接
conn=MySQLdb.connect(host='localhost',user='root',passwd='',port=3306)
# 使用cursor()方法获取操作游标

cur=conn.cursor()
# 选择数据库
conn.select_db('test')


# 我们看到,这里可以通过写一个可以组装的sql语句来进行
cur.execute("UPDATE stu_info SET Name = %s WHERE Id = %s",
    ("cherry", "3"))
# 使用cur.rowcount获取影响了多少行
print "Number of rows updated: %d" % cur.rowcount


conn.commit()
cur.close()
conn.close()

执行结果

:<EOF>
Number of rows updated: 1

In [16]:

Transaction - 事务

事务机制可以确保数据一致性。
事务应该具有4个属性:原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。
① 原子性(atomicity)。一个事务是一个不可分割的工作单位,事务中包括的诸操作要么都做,要么都不做。
② 一致性(consistency)。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
③ 隔离性(isolation)。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰。
④ 持久性(durability)。持续性也称永久性(permanence),指一个事务一旦提交,它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

Python DB API 2.0 的事务提供了两个方法 commit 或 rollback。
对于支持事务的数据库, 在Python数据库编程中,当游标建立之时,就自动开始了一个隐形的数据库事务。commit()方法游标的所有更新操作,rollback()方法回滚当前游标的所有操作。每一个方法都开始了一个新的事务。

import MySQLdb

try:
    # 创建连接
    conn = MySQLdb.connect(
        host='localhost', user='root', passwd='', port=3306)
    # 使用cursor()方法获取操作游标

    cur = conn.cursor()
    # 选择数据库
    conn.select_db('test')

    # 如果某个数据库支持事务,会自动开启
    # 这里用的是MYSQL,所以会自动开启事务(若是MYISM引擎则不会)
    cur.execute("UPDATE stu_info SET name = %s WHERE id = %s",
                   ("tonny", "1"))
    cur.execute("UPDATE stu_infos SET name = %s WHERE id = %s",
                   ("jim", "2"))

    # 事务的特性1、原子性的手动提交
    conn.commit()

    cur.close()
    conn.close()

except MySQLdb.Error, e:
    # 如果出现了错误,那么可以回滚,就是上面的三条语句要么执行,要么都不执行 [ 存储引擎支持事物 ]
    # 如存储引擎不只是事务[MyISM], 则只提交成功的结果
    conn.rollback()
    print "Error %d: %s" % (e.args[0], e.args[1])

执行结果

Error 1146: Table 'test.stu_infos' doesn't exist

 

未完待续  ... ....

 

Python MySQLdb test for select count(*) = zero

I use SELECT COUNT(*) FROM db WHERE <expression> to see if a set of records is null. So:

>>> cnt = c.fetchone()
>>> print cnt
(0L,)

My question is: how do you test for this condition?
I have a number of other ways to accomplish this. Is something like the following possible?

if cnt==(0L,):
    # do something

fetchone returns a row, which is a sequence of columns.
If you want to get the first value in a sequence, you use [0].

You could instead compare the row to (0,), as you're suggesting. But as far as I know neither the general DB-API nor the specific MySQLdb library guarantee what kind of sequence a row is; it could be a list, or a custom sequence class. So, relying on the fact that it's a tuple is probably not a good idea. And, since it's just as easy to not do so, why not be safe and portable?

So:

count_row = c.fetchone()
count = count_row[0]
if count == 0:
    do_something()

Or, putting it together in one line:

if c.fetchone()[0] == 0:
    do_something()

source: stackoverflow

How-to find MySQL process list and to kill those processes

有些时候由于MySQL 表和查询设计的不够好,常常以为一个SQL是这个数据库卡住亦或是查询变得灰常的慢。

那么我们该如何查到查询列表并杀死它们呢?

 

Here I go with solution.

  1. Login to DB.
  2. run command show full processlist;
  3. Here you will get the process id with status and Query it self which causing the problem of hanging database.
  4. Now select the process id and run command KILL <pid>;
  5. Now that point you kill that process.

Sometime that is not enough to kill each process manually. So, for that we've to go with some sort of trick. here I go with that trick:

  1. Login to MySql
  2. run that query Select concat('KILL ',id,';') from information_schema.processlist where user='user';
  3. This will print all the process with KILL command.
  4. Copy all the query result, manipulate them and remove pipe | sign and paste all again into the query console. HIT ENTER. BooM its done.

相关连接

[0] https://dev.mysql.com/doc/refman/5.7/en/kill.html

[1] https://stackoverflow.com/questions/44192418/how-to-find-mysql-process-list-and-to-kill-those-processes

[2] https://stackoverflow.com/questions/1903838/how-do-i-kill-all-the-processes-in-mysql-show-processlist

[Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.

问题描述

发现网站反应比较慢 , 顺便查看了一下mysql的错误日志,发现产生了很多warning的日志:
150527 21:15:28 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO 'xxx'

问题原因

查了下原因,xxx 这个表上有2个唯一键。则使用INSERT ... ON DUPLICATE KEY UPDATE ,且当前数据库binlog_format是statement格式,这种sql语句就会报unsafe。

查了下手册

INSERT ... ON DUPLICATE KEY UPDATE statements on tables with multiple primary or unique keys.When executed against a table that contains more than one primary or unique key, this statement is considered unsafe, being sensitive to the order in which the storage engine checks the keys, which is not deterministic, and on which the choice of rows updated by the MySQL Server depends.

INSERT ... ON DUPLICATE KEY UPDATEstatement against a table having more than one unique or primary key is marked as unsafe for statement-based replication beginning with MySQL 5.6.6. (Bug #11765650, Bug #58637)

link : http://dev.mysql.com/doc/refman/5.6/en/replication-rbr-safe-unsafe.html
binlog format : http://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#sysvar_binlog_format

看官方解释,是server层把数据传给innodb引擎,innodb引擎检查key值比较敏感造成的。

问题解决
两种办法:
1.修改binlog_format格式为mixed;
登陆mysql,执行 set global binlog_format=MIXED; FLUSH LOGS;

注意:
如果是 master->slave 结构的数据库架构。并且 slave上开启了 log_slave_updates。那么在master上修改完binlog格式,开启了log_slave_updates的从库,会同布中断。
所以,需要先在 slave上,设置 binlog_format=mixed,之后再在master上设置。slave报错信息如下:
Last_SQL_Errno: 1666
Last_SQL_Error: Error executing row event: 'Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.'

2.不要使用这类sql;