MySQL

MySQL

mysql 5.6单实例下有两万多个库导致启动和备份都很慢,有加速的方法吗?

MySQLyejr 回复了问题 • 2 人关注 • 1 个回复 • 86 次浏览 • 4 天前 • 来自相关话题

mysql-router

回复

MySQLA673 发起了问题 • 1 人关注 • 0 个回复 • 37 次浏览 • 5 天前 • 来自相关话题

数据库5.6升级到5.7后乱码

MySQLzhagyilig 回复了问题 • 2 人关注 • 5 个回复 • 108 次浏览 • 5 天前 • 来自相关话题

kingshard这个中间件怎么样?

回复

MySQLFan 发起了问题 • 1 人关注 • 0 个回复 • 31 次浏览 • 2018-05-12 11:10 • 来自相关话题

pt-table-checksum检测不出主从差异处理

MySQLUest 发表了文章 • 0 个评论 • 82 次浏览 • 2018-05-10 10:18 • 来自相关话题

根据课堂上吴老师和各位同学提供的思路整理而成~
最近几版pt-table-checksum在binlog_format='row',且主从存在差异数据时,却检测不出主从差异。原因就是处理过程中主库没有SET binlog_format = 'STATEMENT',导致下面两个核心语句不是以statement格式记录,从库不会进行CRC32相关运算,主从永远一致~# 查看pt版本
[root@ZST2 ~]# /usr/local/bin/pt-table-checksum --version
pt-table-checksum 3.0.4
[root@ZST2 ~]#
# pt-table-checksum 3.0.4检测不出差异数据
[root@ZST2 ~]# /usr/local/bin/pt-table-checksum --nocheck-binlog-format --nocheck-replication-filters --recursion-method=hosts --replicate=replcrash.checksums --databases=replcrash --tables=py_user,py_user_innodb --host=192.168.85.132 --port=3306 --user=mydba --password=mysql5721
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
05-08T09:41:15 0 0 8 1 0 0.257 replcrash.py_user
05-08T09:41:16 0 0 67740 5 0 1.056 replcrash.py_user_innodb

# 两个核心语句
REPLACE INTO `replcrash`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'replcrash', 'py_user', '1', NULL, NULL, NULL, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `uid`, convert(`name` using utf8mb4), `add_time`, convert(`server_id` using utf8mb4), CONCAT(ISNULL(`name`), ISNULL(`add_time`), ISNULL(`server_id`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `replcrash`.`py_user` /*checksum table*/
UPDATE `replcrash`.`checksums` SET chunk_time = '0.004092', master_crc = '5abbd632', master_cnt = '8' WHERE db = 'replcrash' AND tbl = 'py_user' AND chunk = '1'方法一:修改代码# pt-table-checksum 3.0.4 只需注释掉第9335行和9364行,这层逻辑应该应用于任何情况下,因此不需要使用if判断
[root@ZST2 ~]# vim /usr/local/bin/pt-table-checksum
...
9335 #if ( $o->get('check-binlog-format') ) {
9336 # https://bugs.launchpad.net/per ... 19352
9337 # The tool shouldn't blindly attempt to change binlog_format;
9338 # instead, it should check if it's already set to STATEMENT.
9339 # This is becase starting with MySQL 5.1.29, changing the format
9340 # requires a SUPER user.
9341 if ( VersionParser->new($dbh) >= '5.1.5' ) {
9342 $sql = 'SELECT @@binlog_format';
9343 PTDEBUG && _d($dbh, $sql);
9344 my ($original_binlog_format) = $dbh->selectrow_array($sql);
9345 PTDEBUG && _d('Original binlog_format:', $original_binlog_format);
9346 if ( $original_binlog_format !~ /STATEMENT/i ) {
9347 $sql = q{/*!50108 SET @@binlog_format := 'STATEMENT'*/};
9348 eval {
9349 PTDEBUG && _d($dbh, $sql);
9350 $dbh->do($sql);
9351 };
9352 if ( $EVAL_ERROR ) {
9353 die "Failed to $sql: $EVAL_ERROR\n"
9354 . "This tool requires binlog_format=STATEMENT, "
9355 . "but the current binlog_format is set to "
9356 ."$original_binlog_format and an error occurred while "
9357 . "attempting to change it. If running MySQL 5.1.29 or newer, "
9358 . "setting binlog_format requires the SUPER privilege. "
9359 . "You will need to manually set binlog_format to 'STATEMENT' "
9360 . "before running this tool.\n";
9361 }
9362 }
9363 }
9364 #}
...

# 修改后检测出主从不一致
[root@ZST2 ~]# /usr/local/bin/pt-table-checksum --nocheck-binlog-format --nocheck-replication-filters --recursion-method=hosts --replicate=replcrash.checksums --databases=replcrash --tables=py_user,py_user_innodb --host=192.168.85.132 --port=3306 --user=mydba --password=mysql5721
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
05-08T09:51:32 0 1 8 1 0 0.030 replcrash.py_user
05-08T09:51:33 0 0 67740 5 0 0.561 replcrash.py_user_innodb
[root@ZST2 ~]# 方法二、命令行带上--set-vars参考
详细说明参考:pt-table-checksum not detecting diffs# pt-table-checksum 3.0.4不修改代码,带上--set-vars参数检测出差异数据
[root@ZST2 ~]# /usr/local/bin/pt-table-checksum --nocheck-binlog-format --nocheck-replication-filters --recursion-method=hosts --replicate=replcrash.checksums --databases=replcrash --tables=py_user,py_user_innodb --host=192.168.85.132 --port=3306 --user=mydba --password=mysql5721 --set-vars binlog_format='statement'
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
05-10T09:25:01 0 1 11 1 0 0.024 replcrash.py_user
05-10T09:25:01 0 0 67740 5 0 0.695 replcrash.py_user_innodb
[root@ZST2 ~]#善于思考,善于搜索,不要期盼别人把结果原封不动地送到嘴里~.~
吐槽一下,本来想将代码中的关键字加粗,编辑时显示正常,一发布就乱套了 查看全部
根据课堂上吴老师和各位同学提供的思路整理而成~
最近几版pt-table-checksum在binlog_format='row',且主从存在差异数据时,却检测不出主从差异。原因就是处理过程中主库没有SET binlog_format = 'STATEMENT',导致下面两个核心语句不是以statement格式记录,从库不会进行CRC32相关运算,主从永远一致~
# 查看pt版本
[root@ZST2 ~]# /usr/local/bin/pt-table-checksum --version
pt-table-checksum 3.0.4
[root@ZST2 ~]#
# pt-table-checksum 3.0.4检测不出差异数据
[root@ZST2 ~]# /usr/local/bin/pt-table-checksum --nocheck-binlog-format --nocheck-replication-filters --recursion-method=hosts --replicate=replcrash.checksums --databases=replcrash --tables=py_user,py_user_innodb --host=192.168.85.132 --port=3306 --user=mydba --password=mysql5721
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
05-08T09:41:15 0 0 8 1 0 0.257 replcrash.py_user
05-08T09:41:16 0 0 67740 5 0 1.056 replcrash.py_user_innodb

# 两个核心语句
REPLACE INTO `replcrash`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'replcrash', 'py_user', '1', NULL, NULL, NULL, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `uid`, convert(`name` using utf8mb4), `add_time`, convert(`server_id` using utf8mb4), CONCAT(ISNULL(`name`), ISNULL(`add_time`), ISNULL(`server_id`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `replcrash`.`py_user` /*checksum table*/
UPDATE `replcrash`.`checksums` SET chunk_time = '0.004092', master_crc = '5abbd632', master_cnt = '8' WHERE db = 'replcrash' AND tbl = 'py_user' AND chunk = '1'
方法一:修改代码
# pt-table-checksum 3.0.4 只需注释掉第9335行和9364行,这层逻辑应该应用于任何情况下,因此不需要使用if判断
[root@ZST2 ~]# vim /usr/local/bin/pt-table-checksum
...
9335 #if ( $o->get('check-binlog-format') ) {
9336 # https://bugs.launchpad.net/per ... 19352
9337 # The tool shouldn't blindly attempt to change binlog_format;
9338 # instead, it should check if it's already set to STATEMENT.
9339 # This is becase starting with MySQL 5.1.29, changing the format
9340 # requires a SUPER user.
9341 if ( VersionParser->new($dbh) >= '5.1.5' ) {
9342 $sql = 'SELECT @@binlog_format';
9343 PTDEBUG && _d($dbh, $sql);
9344 my ($original_binlog_format) = $dbh->selectrow_array($sql);
9345 PTDEBUG && _d('Original binlog_format:', $original_binlog_format);
9346 if ( $original_binlog_format !~ /STATEMENT/i ) {
9347 $sql = q{/*!50108 SET @@binlog_format := 'STATEMENT'*/};
9348 eval {
9349 PTDEBUG && _d($dbh, $sql);
9350 $dbh->do($sql);
9351 };
9352 if ( $EVAL_ERROR ) {
9353 die "Failed to $sql: $EVAL_ERROR\n"
9354 . "This tool requires binlog_format=STATEMENT, "
9355 . "but the current binlog_format is set to "
9356 ."$original_binlog_format and an error occurred while "
9357 . "attempting to change it. If running MySQL 5.1.29 or newer, "
9358 . "setting binlog_format requires the SUPER privilege. "
9359 . "You will need to manually set binlog_format to 'STATEMENT' "
9360 . "before running this tool.\n";
9361 }
9362 }
9363 }
9364 #}
...

# 修改后检测出主从不一致
[root@ZST2 ~]# /usr/local/bin/pt-table-checksum --nocheck-binlog-format --nocheck-replication-filters --recursion-method=hosts --replicate=replcrash.checksums --databases=replcrash --tables=py_user,py_user_innodb --host=192.168.85.132 --port=3306 --user=mydba --password=mysql5721
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
05-08T09:51:32 0 1 8 1 0 0.030 replcrash.py_user
05-08T09:51:33 0 0 67740 5 0 0.561 replcrash.py_user_innodb
[root@ZST2 ~]#
方法二、命令行带上--set-vars参考
详细说明参考:pt-table-checksum not detecting diffs
# pt-table-checksum 3.0.4不修改代码,带上--set-vars参数检测出差异数据
[root@ZST2 ~]# /usr/local/bin/pt-table-checksum --nocheck-binlog-format --nocheck-replication-filters --recursion-method=hosts --replicate=replcrash.checksums --databases=replcrash --tables=py_user,py_user_innodb --host=192.168.85.132 --port=3306 --user=mydba --password=mysql5721 --set-vars binlog_format='statement'
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
05-10T09:25:01 0 1 11 1 0 0.024 replcrash.py_user
05-10T09:25:01 0 0 67740 5 0 0.695 replcrash.py_user_innodb
[root@ZST2 ~]#
善于思考,善于搜索,不要期盼别人把结果原封不动地送到嘴里~.~
吐槽一下,本来想将代码中的关键字加粗,编辑时显示正常,一发布就乱套了

一个SQL问题

MySQLqidan3500 回复了问题 • 2 人关注 • 2 个回复 • 166 次浏览 • 2018-04-24 14:26 • 来自相关话题

关于fpmmm与pmm的监控数据不一致的问题

MySQLarron 回复了问题 • 3 人关注 • 2 个回复 • 107 次浏览 • 2018-04-24 14:14 • 来自相关话题

MYSQL 8.0 认证变化

回复

MySQLwubx 回复了问题 • 1 人关注 • 1 个回复 • 112 次浏览 • 2018-04-23 17:01 • 来自相关话题

sql_mode 客户端软件登陆和本地登录值不同

MySQLwubx 回复了问题 • 2 人关注 • 1 个回复 • 125 次浏览 • 2018-04-21 11:52 • 来自相关话题

关于jdbc 和java 连接池的问题

MySQLzhiquan 回复了问题 • 3 人关注 • 2 个回复 • 401 次浏览 • 2018-04-08 13:30 • 来自相关话题

条新动态, 点击查看
youge

youge 回答了问题 • 2016-12-27 11:25 • 5 个回复 不感兴趣

死锁原因求教

赞同来自:

首先,你session 1 的delete一个不存在的记录会产生一个gap lock 在 (2,1000)之间
其次,你session 2的delete也会产生同样的gap lock
但是两个gap lock是相容的
最后你insert,需要x lock与之前... 显示全部 »
首先,你session 1 的delete一个不存在的记录会产生一个gap lock 在 (2,1000)之间
其次,你session 2的delete也会产生同样的gap lock
但是两个gap lock是相容的
最后你insert,需要x lock与之前的gap lock不兼容,所以会死锁。
 
我这边也可以提问MySQL相关问题
http://www.yougemysqldba.com/
 

innodb_rollback_on_timeout参数对锁的影响

MySQLhanchangyue 发表了文章 • 3 个评论 • 1308 次浏览 • 2016-12-21 10:51 • 来自相关话题

前提提要:
MySQL版本:5.6.21-log MySQL Community Server (GPL)
innodb_rollback_on_timeout是啥作用?
答:事务B在锁等待超时后是回滚事务内所有的statement还是最后一条语句;
0表示rollback最后一条语句,默认值;有点坑
1表示回滚事务B内所有的statements;
此参数是只读参数,需在my.cnf中配置,并且重启生效;
注意:回滚statements后不自动commit或rollback事务;坑表结构:mysql> show create table t12\G;
*************************** 1. row ***************************
Table: t12
Create Table: CREATE TABLE `t12` (
`a` int(10) unsigned NOT NULL AUTO_INCREMENT,
`b` varchar(766) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1
row in set (0.00 sec)实验一:
innodb_rollback_on_timeout开启的情况下,锁等待超时后,该事务所持有锁会一并释放;参数配置:
mysql> show variables like 'innodb_rollback_on_timeout';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_rollback_on_timeout | ON |
+----------------------------+-------+
row in set (0.00 sec)

mysql> show variables like 'tx_iso%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
row in set (0.00 sec)

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
row in set (0.00 sec)过程:


实验二:innodb_rollback_on_timeout关闭的情况下,锁等待超时后事务持有的锁不会释放;参数配置:
mysql> show variables like 'innodb_rollback_on_timeout';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_rollback_on_timeout | OFF |
+----------------------------+-------+
row in set (0.00 sec)

mysql> show variables like 'tx_iso%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
row in set (0.00 sec)

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
row in set (0.00 sec)过程:


总结:1、关闭innodb_rollback_on_timeout后,一旦以begin;start transaction;等语句开启一个事务,当锁等待超时后,该事务请求的锁将不释放,直到事务提交或回滚或会话超时;

所以autocommit参数建议设置成ON,只要程序没有显示开启事务,就可以避免上述锁未释放问题。

2、开启innodb_rollback_on_timeout后,一旦锁等待超时,是事务内sql将全部回滚,且释放之前请求的锁。

3、当autocommit=on,只要不显示开启事务,将不存在上面2个问题,即锁的问题和回滚的问题。
  查看全部
前提提要:
MySQL版本:5.6.21-log MySQL Community Server (GPL)
 
innodb_rollback_on_timeout是啥作用?
答:事务B在锁等待超时后是回滚事务内所有的statement还是最后一条语句;
0表示rollback最后一条语句,默认值;有点坑
1表示回滚事务B内所有的statements;
此参数是只读参数,需在my.cnf中配置,并且重启生效;
注意:回滚statements后不自动commit或rollback事务;坑
表结构:
mysql> show create table t12\G;
*************************** 1. row ***************************
Table: t12
Create Table: CREATE TABLE `t12` (
`a` int(10) unsigned NOT NULL AUTO_INCREMENT,
`b` varchar(766) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1
row in set (0.00 sec)
实验一:
innodb_rollback_on_timeout开启的情况下,锁等待超时后,该事务所持有锁会一并释放;
参数配置:
mysql> show variables like 'innodb_rollback_on_timeout';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_rollback_on_timeout | ON |
+----------------------------+-------+
row in set (0.00 sec)

mysql> show variables like 'tx_iso%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
row in set (0.00 sec)

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
row in set (0.00 sec)
过程:


实验二:innodb_rollback_on_timeout关闭的情况下,锁等待超时后事务持有的锁不会释放;
参数配置:
mysql> show variables like 'innodb_rollback_on_timeout';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_rollback_on_timeout | OFF |
+----------------------------+-------+
row in set (0.00 sec)

mysql> show variables like 'tx_iso%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
row in set (0.00 sec)

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
row in set (0.00 sec)
过程:


总结:1、关闭innodb_rollback_on_timeout后,一旦以begin;start transaction;等语句开启一个事务,当锁等待超时后,该事务请求的锁将不释放,直到事务提交或回滚或会话超时;

所以autocommit参数建议设置成ON,只要程序没有显示开启事务,就可以避免上述锁未释放问题。

2、开启innodb_rollback_on_timeout后,一旦锁等待超时,是事务内sql将全部回滚,且释放之前请求的锁。

3、当autocommit=on,只要不显示开启事务,将不存在上面2个问题,即锁的问题和回滚的问题。
 

mysql 误删,误更新数据恢复 脚本,基于row格式binlog

MySQLearl86 发表了文章 • 4 个评论 • 945 次浏览 • 2016-01-18 14:54 • 来自相关话题

mysql 误删,误更新数据恢复 脚本,基于row格式binlog
恢复时需要指定-s columnsize(字段行数) -t update/delete(恢复类型)
update语句需要手动更新colunmlist的值(字段列表)
python脚本如下:
 #!/usr/bin/python
#coding=utf-8
#Author:earl86
#version 1.0
#the script for 'delete from xxxtable;' and 'update xxxtable set yyycol='',xxxcol='';'
#mysqlbinlog -v --base64-output=decode-rows --start-position=6908 --stop-position=8900 mysql-bin.000001 >mysql-bin.000001.sql
#how to use:python sqlconvert.py -f mysql-bin.000001.sql -s columnsize -t update/delete
#need to modify the value of colunmlist
#update sql need customed by yourself


import os
import string
import sys
import argparse

reload(sys)
sys.setdefaultencoding('utf8')

parser = argparse.ArgumentParser()
parser.add_argument("-f", action="store", dest='sqlfile', help="input the sql file dir and file name", required=True)
parser.add_argument("-s", action="store", dest='colnum', help="input the Number of columns", required=True)
parser.add_argument("-t", action="store", dest='sqltype', help="input the convert sql type.update or delete", required=True)
args = parser.parse_args()



def updatereadlines():
    result = list()
    with open(args.sqlfile, 'r') as f:
        f = f.readlines()
        for line in f:
            if line.startswith('### '):
                if line.startswith('### UPDATE '):
                    #print result
                    convertupdatesql(result)
                    result =
                result.append(line.strip('\n').replace('### ',' '))
        #print result
        convertupdatesql(result)

def updatereadline():
    result = list()
    f = open(args.sqlfile,'r')
    for line in open(args.sqlfile):  
        line = f.readline()  
        if line.startswith('### '):
            if line.startswith('### UPDATE '):
                #print result
                convertupdatesql(result)
                result =
            result.append(line.strip('\n').replace('### ',' '))  
    #print result
    convertupdatesql(result)
    f.close()

def convertupdatesql(result):
     if (len(result) > 0):
         sql = result[0] + result[int(args.colnum)+2] + result[8].replace('@7','local_backup_status') + result[1] + result[2].replace('@1','id') + ';' 
         #print sql
         open('result-update.sql', 'a').write(sql+'\n')  

def deletereadlines(colunmlist):
    result = list()
    with open(args.sqlfile, 'r') as f:
        f = f.readlines()
        for line in f:
            if line.startswith('### '):
                if line.startswith('### DELETE '):
                    #print result
                    convertdeletesql(result,colunmlist)
                    result =
                result.append(line.strip('\n').replace('### ',' '))
        #print result
        convertdeletesql(result,colunmlist)

def deletereadline(colunmlist):
    result = list()
    f = open(args.sqlfile,'r')
    for line in open(args.sqlfile):
        line = f.readline()
        if line.startswith('### '):
            if line.startswith('### DELETE '):
                #print result
                convertdeletesql(result,colunmlist)
                result =
            result.append(line.strip('\n').replace('### ',' '))
    #print result
    convertdeletesql(result,colunmlist)
    f.close()

def convertdeletesql(result,colunmlist):
     if (len(result) > 0):
         sql =  result[0].replace('DELETE FROM','INSERT INTO') + ' SET'
         for i in range(2,int(args.colnum)+2):
             if (i == int(args.colnum)+1):
                 sql = sql + result.replace('@' + str(i-1),colunmlist[i-2]) + ';'
             else:
                 sql = sql + result[i].replace('@' + str(i-1),colunmlist[i-2]) + ','
         #print sql
         open('result-delete.sql', 'a').write(sql+'\n')


    
if __name__ == '__main__':
    colunmlist =('id','backup_time','host_name','service_name','file_name','file_md5','local_backup_status','remote_backup_status','backup_user')
    if ('delete' == args.sqltype):
        try:
            os.remove('result-delete.sql')
        except:
            pass 
        deletereadline(colunmlist)
    elif ('update' == args.sqltype): 
        try:
            os.remove('result-update.sql')
        except:
            pass
        updatereadline()[/i]
  查看全部
mysql 误删,误更新数据恢复 脚本,基于row格式binlog
恢复时需要指定-s columnsize(字段行数) -t update/delete(恢复类型)
update语句需要手动更新colunmlist的值(字段列表)
python脚本如下:
 #!/usr/bin/python
#coding=utf-8
#Author:earl86
#version 1.0
#the script for 'delete from xxxtable;' and 'update xxxtable set yyycol='',xxxcol='';'
#mysqlbinlog -v --base64-output=decode-rows --start-position=6908 --stop-position=8900 mysql-bin.000001 >mysql-bin.000001.sql
#how to use:python sqlconvert.py -f mysql-bin.000001.sql -s columnsize -t update/delete
#need to modify the value of colunmlist
#update sql need customed by yourself


import os
import string
import sys
import argparse

reload(sys)
sys.setdefaultencoding('utf8')

parser = argparse.ArgumentParser()
parser.add_argument("-f", action="store", dest='sqlfile', help="input the sql file dir and file name", required=True)
parser.add_argument("-s", action="store", dest='colnum', help="input the Number of columns", required=True)
parser.add_argument("-t", action="store", dest='sqltype', help="input the convert sql type.update or delete", required=True)
args = parser.parse_args()



def updatereadlines():
    result = list()
    with open(args.sqlfile, 'r') as f:
        f = f.readlines()
        for line in f:
            if line.startswith('### '):
                if line.startswith('### UPDATE '):
                    #print result
                    convertupdatesql(result)
                    result =
                result.append(line.strip('\n').replace('### ',' '))
        #print result
        convertupdatesql(result)

def updatereadline():
    result = list()
    f = open(args.sqlfile,'r')
    for line in open(args.sqlfile):  
        line = f.readline()  
        if line.startswith('### '):
            if line.startswith('### UPDATE '):
                #print result
                convertupdatesql(result)
                result =
            result.append(line.strip('\n').replace('### ',' '))  
    #print result
    convertupdatesql(result)
    f.close()

def convertupdatesql(result):
     if (len(result) > 0):
         sql = result[0] + result[int(args.colnum)+2] + result[8].replace('@7','local_backup_status') + result[1] + result[2].replace('@1','id') + ';' 
         #print sql
         open('result-update.sql', 'a').write(sql+'\n')  

def deletereadlines(colunmlist):
    result = list()
    with open(args.sqlfile, 'r') as f:
        f = f.readlines()
        for line in f:
            if line.startswith('### '):
                if line.startswith('### DELETE '):
                    #print result
                    convertdeletesql(result,colunmlist)
                    result =
                result.append(line.strip('\n').replace('### ',' '))
        #print result
        convertdeletesql(result,colunmlist)

def deletereadline(colunmlist):
    result = list()
    f = open(args.sqlfile,'r')
    for line in open(args.sqlfile):
        line = f.readline()
        if line.startswith('### '):
            if line.startswith('### DELETE '):
                #print result
                convertdeletesql(result,colunmlist)
                result =
            result.append(line.strip('\n').replace('### ',' '))
    #print result
    convertdeletesql(result,colunmlist)
    f.close()

def convertdeletesql(result,colunmlist):
     if (len(result) > 0):
         sql =  result[0].replace('DELETE FROM','INSERT INTO') + ' SET'
         for i in range(2,int(args.colnum)+2):
             if (i == int(args.colnum)+1):
                 sql = sql + result.replace('@' + str(i-1),colunmlist[i-2]) + ';'
             else:
                 sql = sql + result[i].replace('@' + str(i-1),colunmlist[i-2]) + ','
         #print sql
         open('result-delete.sql', 'a').write(sql+'\n')


    
if __name__ == '__main__':
    colunmlist =('id','backup_time','host_name','service_name','file_name','file_md5','local_backup_status','remote_backup_status','backup_user')
    if ('delete' == args.sqltype):
        try:
            os.remove('result-delete.sql')
        except:
            pass 
        deletereadline(colunmlist)
    elif ('update' == args.sqltype): 
        try:
            os.remove('result-update.sql')
        except:
            pass
        updatereadline()
[/i]
 

MySQL优化索引-----定位需要优化的索引( 我的SQL支持5.6,5.7,mariadb。另下面附了姜总原文中的SQL支持5.6外的环境)

MySQLhuzilin 发表了文章 • 17 个评论 • 1489 次浏览 • 2016-01-18 10:33 • 来自相关话题

前些日子在姜承尧姜总那里看到他公众号分享了个索引优化,他给了个SQL说这个SQL5.6不好使,让大家来写一个5.6能使用的,然后给了一些信息。然后我就写了下面的sql,姜总说这个SQL在SELECTIVITY是0.1左右的都需要关注了。

下面这个SQL后来请吴总帮忙看了下,说是没有问题,给了个意见,然后我按照吴总的建议修改了下现在只输出SELECTIVITY<=0.4的行。

SELECT t.TABLE_SCHEMA, t.TABLE_NAME, INDEX_NAME, s.CARDINALITY, t.TABLE_ROWS
    , s.CARDINALITY / t.TABLE_ROWS AS SELECTIVITY
FROM information_schema.TABLES t, (SELECT t1.database_name, t1.table_name, t1.index_name, t2.stat_value AS CARDINALITY
    FROM (SELECT database_name, table_name, index_name, MAX(substring(stat_name, 11)) AS max_stat_name
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name, index_name
        ) t1, mysql.innodb_index_stats t2, (SELECT database_name, table_name, group_concat(index_name) AS all_indexs
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name
        ) t3
    WHERE t2.database_name = t1.database_name
        AND t2.table_name = t1.table_name
        AND t2.index_name = t1.index_name
        AND t2.stat_name LIKE 'n_diff_pfx%'
        AND t2.database_name = t3.database_name
        AND t2.table_name = t3.table_name
        AND t3.all_indexs REGEXP '^PRIMARY,'
        AND t2.index_name != 'PRIMARY'
        AND substring(t2.stat_name, 11) = t1.max_stat_name - 1
    UNION ALL
    SELECT t1.database_name, t1.table_name, t1.index_name, t2.stat_value AS CARDINALITY
    FROM (SELECT database_name, table_name, index_name, MAX(substring(stat_name, 11)) AS max_stat_name
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name, index_name
        ) t1, mysql.innodb_index_stats t2, (SELECT database_name, table_name, group_concat(index_name) AS all_indexs
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name
        ) t3
    WHERE t2.database_name = t1.database_name
        AND t2.table_name = t1.table_name
        AND t2.index_name = t1.index_name
        AND t2.stat_name LIKE 'n_diff_pfx%'
        AND t2.database_name = t3.database_name
        AND t2.table_name = t3.table_name
        AND t3.all_indexs REGEXP '^PRIMARY'
        AND t2.index_name = 'PRIMARY'
        AND substring(t2.stat_name, 11) = t1.max_stat_name
    UNION ALL
    SELECT t1.database_name, t1.table_name, t1.index_name, t2.stat_value AS CARDINALITY
    FROM (SELECT database_name, table_name, index_name, MAX(substring(stat_name, 11)) AS max_stat_name
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name, index_name
        ) t1, mysql.innodb_index_stats t2, (SELECT database_name, table_name, group_concat(index_name) AS all_indexs
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name
        ) t3
    WHERE t2.database_name = t1.database_name
        AND t2.table_name = t1.table_name
        AND t2.index_name = t1.index_name
        AND t2.stat_name LIKE 'n_diff_pfx%'
        AND t2.database_name = t3.database_name
        AND t2.table_name = t3.table_name
        AND t3.all_indexs NOT REGEXP '^PRIMARY'
        AND t2.index_name != 'PRIMARY'
        AND substring(t2.stat_name, 11) = t1.max_stat_name
    ) s
WHERE t.table_schema = s.database_name
    AND t.table_name = s.table_name
    AND t.table_rows != 0
    AND t.table_schema NOT IN ('mysql', 'performance_schema', 'information_schema')
HAVING SELECTIVITY <=0.4
ORDER BY SELECTIVITY ;

里面的3个子查询分别是1)表有主键 当前匹配的索引不是主键 2)表有主键 当前匹配索引是主键 3)当前表没有主键
 
这里再贴下姜总的SQL吧,姜总说这个SQL在5.6中因为information_schema.STATISTICS统计的信息有BUG索引无法得出准确的结果。但是支持其他的版本。
SELECT 
     t.TABLE_SCHEMA,t.TABLE_NAME,INDEX_NAME, CARDINALITY, 
    TABLE_ROWS, CARDINALITY/TABLE_ROWS AS SELECTIVITY
FROM
    information_schema.TABLES t,
 (
  SELECT table_schema,table_name,index_name,cardinality
  FROM information_schema.STATISTICS 
  WHERE (table_schema,table_name,index_name,seq_in_index) IN (
  SELECT table_schema,table_name,index_name,MAX(seq_in_index)
  FROM information_schema.STATISTICS
  GROUP BY table_schema , table_name , index_name )
 ) s
WHERE
    t.table_schema = s.table_schema 
        AND t.table_name = s.table_name AND t.table_rows != 0
        AND t.table_schema NOT IN ( 'mysql','performance_schema','information_schema') 
ORDER BY SELECTIVITY;

----------------------------------------------------------------
关于会有SELECTIVITY列大于1的情况
我是这么理解的 information_schema.TABLES这个表不是实时跟新,这个表相对更新慢,如果行数少,比如查出在某一个时刻基数是5对应当时的行数是5, 但是如果information_schema.TABLES收集的那个时间点,有4条还未插入,就可能会导致计算的结果是5。我做过尝试,当看到超过1的行的时候,稍后再执行,那一行一般都会变成另一个数值。
如果理解有误请勘正,或者还有其他的理解希望大家也能分享讨论下。共同提高。 查看全部
前些日子在姜承尧姜总那里看到他公众号分享了个索引优化,他给了个SQL说这个SQL5.6不好使,让大家来写一个5.6能使用的,然后给了一些信息。然后我就写了下面的sql,姜总说这个SQL在SELECTIVITY是0.1左右的都需要关注了。

下面这个SQL后来请吴总帮忙看了下,说是没有问题,给了个意见,然后我按照吴总的建议修改了下现在只输出SELECTIVITY<=0.4的行。

SELECT t.TABLE_SCHEMA, t.TABLE_NAME, INDEX_NAME, s.CARDINALITY, t.TABLE_ROWS
    , s.CARDINALITY / t.TABLE_ROWS AS SELECTIVITY
FROM information_schema.TABLES t, (SELECT t1.database_name, t1.table_name, t1.index_name, t2.stat_value AS CARDINALITY
    FROM (SELECT database_name, table_name, index_name, MAX(substring(stat_name, 11)) AS max_stat_name
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name, index_name
        ) t1, mysql.innodb_index_stats t2, (SELECT database_name, table_name, group_concat(index_name) AS all_indexs
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name
        ) t3
    WHERE t2.database_name = t1.database_name
        AND t2.table_name = t1.table_name
        AND t2.index_name = t1.index_name
        AND t2.stat_name LIKE 'n_diff_pfx%'
        AND t2.database_name = t3.database_name
        AND t2.table_name = t3.table_name
        AND t3.all_indexs REGEXP '^PRIMARY,'
        AND t2.index_name != 'PRIMARY'
        AND substring(t2.stat_name, 11) = t1.max_stat_name - 1
    UNION ALL
    SELECT t1.database_name, t1.table_name, t1.index_name, t2.stat_value AS CARDINALITY
    FROM (SELECT database_name, table_name, index_name, MAX(substring(stat_name, 11)) AS max_stat_name
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name, index_name
        ) t1, mysql.innodb_index_stats t2, (SELECT database_name, table_name, group_concat(index_name) AS all_indexs
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name
        ) t3
    WHERE t2.database_name = t1.database_name
        AND t2.table_name = t1.table_name
        AND t2.index_name = t1.index_name
        AND t2.stat_name LIKE 'n_diff_pfx%'
        AND t2.database_name = t3.database_name
        AND t2.table_name = t3.table_name
        AND t3.all_indexs REGEXP '^PRIMARY'
        AND t2.index_name = 'PRIMARY'
        AND substring(t2.stat_name, 11) = t1.max_stat_name
    UNION ALL
    SELECT t1.database_name, t1.table_name, t1.index_name, t2.stat_value AS CARDINALITY
    FROM (SELECT database_name, table_name, index_name, MAX(substring(stat_name, 11)) AS max_stat_name
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name, index_name
        ) t1, mysql.innodb_index_stats t2, (SELECT database_name, table_name, group_concat(index_name) AS all_indexs
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name
        ) t3
    WHERE t2.database_name = t1.database_name
        AND t2.table_name = t1.table_name
        AND t2.index_name = t1.index_name
        AND t2.stat_name LIKE 'n_diff_pfx%'
        AND t2.database_name = t3.database_name
        AND t2.table_name = t3.table_name
        AND t3.all_indexs NOT REGEXP '^PRIMARY'
        AND t2.index_name != 'PRIMARY'
        AND substring(t2.stat_name, 11) = t1.max_stat_name
    ) s
WHERE t.table_schema = s.database_name
    AND t.table_name = s.table_name
    AND t.table_rows != 0
    AND t.table_schema NOT IN ('mysql', 'performance_schema', 'information_schema')
HAVING SELECTIVITY <=0.4
ORDER BY SELECTIVITY ;

里面的3个子查询分别是1)表有主键 当前匹配的索引不是主键 2)表有主键 当前匹配索引是主键 3)当前表没有主键
 
这里再贴下姜总的SQL吧,姜总说这个SQL在5.6中因为information_schema.STATISTICS统计的信息有BUG索引无法得出准确的结果。但是支持其他的版本。
SELECT 
     t.TABLE_SCHEMA,t.TABLE_NAME,INDEX_NAME, CARDINALITY, 
    TABLE_ROWS, CARDINALITY/TABLE_ROWS AS SELECTIVITY
FROM
    information_schema.TABLES t,
 (
  SELECT table_schema,table_name,index_name,cardinality
  FROM information_schema.STATISTICS 
  WHERE (table_schema,table_name,index_name,seq_in_index) IN (
  SELECT table_schema,table_name,index_name,MAX(seq_in_index)
  FROM information_schema.STATISTICS
  GROUP BY table_schema , table_name , index_name )
 ) s
WHERE
    t.table_schema = s.table_schema 
        AND t.table_name = s.table_name AND t.table_rows != 0
        AND t.table_schema NOT IN ( 'mysql','performance_schema','information_schema') 
ORDER BY SELECTIVITY;

----------------------------------------------------------------
关于会有SELECTIVITY列大于1的情况
我是这么理解的 information_schema.TABLES这个表不是实时跟新,这个表相对更新慢,如果行数少,比如查出在某一个时刻基数是5对应当时的行数是5, 但是如果information_schema.TABLES收集的那个时间点,有4条还未插入,就可能会导致计算的结果是5。我做过尝试,当看到超过1的行的时候,稍后再执行,那一行一般都会变成另一个数值。
如果理解有误请勘正,或者还有其他的理解希望大家也能分享讨论下。共同提高。

开发人员应该知道的索引原理

MySQLFred 发表了文章 • 4 个评论 • 971 次浏览 • 2015-12-30 15:55 • 来自相关话题

作者:黄湘龙
架构师交流群(312254004)
欢迎非商业转载,商业使用请联系我 
 

索引是有效使用数据库的基础,但你的数据量很小的时候,或许通过扫描整表来存取数据的性能还能接受,但当数据量极大时,当访问量极大时,就一定需要通过索引的辅助才能有效地存取数据。一般索引建立的好坏是性能好坏的成功关键。

1.InnoDb数据与索引存储细节

使用InnoDb作为数据引擎的Mysql和有聚集索引的SqlServer的数据存储结构有点类似,虽然在物理层面,他们都存储在Page上,但在逻辑上面,我们可以把数据分为三块:数据区域,索引区域,主键区域,他们通过主键的值作为关联,配合工作。默认配置下,一个Page的大小为16K。

一个表数据空间中的索引数据区域中有很多索引,每一个索引都是一颗B+Tree,在索引的B+Tree中索引的值作为B+Tree的节点的Key,数据主键作为节点的Value。

在InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点数据域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主键索引。这种索引也叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没有),如果没有显式指定,则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。

 表数据都以Row的形式放在一个一个大小为16K的Page中,在每个数据Page中有都页头信息和一行一行的数据。其中页头信息中主要放置的是这一页数据中的所有主键值和其对应的OFFSET,便于通过主键能迅速找到其对应的数据位置。






2.索引优化检索的原理

索引是数据库的灵魂,如果没有索引,数据库也就是一堆文本文件,存在的意义并不大。索引能让数据库成几何倍数地提高检索效率。使用Innodb作为数据引擎的Mysql数据库的索引分为聚集索引(也就是主键)和普通索引。上节我们已经讲解了这两种索引的存储结构,现在我们仔细讲解下索引是如何工作的。

聚集索引和普通索引都有可能由多个字段组成,我们又称这种索引为复合索引,1.2.3将为大家解析这种索引的性能情况.

2.1聚集索引
从上节我们知晓,Innodb的所有数据是按照聚集索引排序的,聚集索引这种存储方式使得按主键的搜索十分高效,如果我们SQL语句的选择条件中有聚集索引,数据库会优先使用聚集索引来进行检索工作。
数据库引擎根据条件中的主键的值,迅速在B+Tree中找到主键对应的叶节点,然后把叶节点所在的Page数据库读取到内存,返回给用户,如上图绿色线条的流向。下面我们来运行一条SQL,从数据库的执行情况分析一下:

select * from UP_User where userId = 10000094;

......

# Query_time: 0.000399  Lock_time: 0.000101  Rows_sent: 1  Rows_examined: 1  Rows_affected: 0

# Bytes_sent: 803  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0

# InnoDB_trx_id: 1D4D

# QC_Hit: No  Full_scan: No  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No

# Filesort: No  Filesort_on_disk: No  Merge_passes: 0

#   InnoDB_IO_r_ops: 0  InnoDB_IO_r_bytes: 0  InnoDB_IO_r_wait: 0.000000

#   InnoDB_rec_lock_wait: 0.000000  InnoDB_queue_wait: 0.000000

#   InnoDB_pages_distinct: 2

SET timestamp=1451104535;

select * from UP_User where userId = 10000094;



我们可以看到,数据库读从磁盘取了两个Page就把809Bytes的数据提取出来返回给客户端。
下面我们试一下,如果试一下选取条件没有包含主键和索引的情况:

select * from `UP_User` where bigPortrait = '5F29E883BFA8903B';

# Query_time: 0.002869 Lock_time: 0.000094 Rows_sent: 1 Rows_examined: 1816 Rows_affected: 0

# Bytes_sent: 792 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0

# QC_Hit: No Full_scan: Yes Full_join: No Tmp_table: No Tmp_table_on_disk: No

# InnoDB_pages_distinct: 25


可以看到如果使用主键作为检索条件,检索时间花了0.3ms,只读取了两个Page,而不使用主键作为检索条件,检索时间花了2.8ms,读取了25个Page,全局扫描才把这条记录给找出来。这还是一个只有1000多行的表,如果更大的数据量,对比更加强烈。

 对于这两个Page,一个是主键B+Tree的数据,一个是10000094这条数据所在的数据页。


2.2普通索引
我们用普通索引作为检索条件来搜索数据需要检索两遍索引:首先检索普通索引获得主键,然后用主键到主索引中检索获得记录。如上图红色线条的流向。
下面我用一个例子来看看数据库的表现:

 select * from UP_User where userName = 'fred';# Query_time: 0.000400 Lock_time: 0.000101 Rows_sent: 1 Rows_examined: 1 Rows_affected: 0

# Bytes_sent: 803 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0

# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No

# InnoDB_pages_distinct: 4


 我们可以看到数据库用了0.4ms来检索这条数据,读取了4个page,比用主键作为检索条件多用了0.1ms,多读取了两个Page,而这两个Page就是userName这个普通索引的B+Tree所在的数据页。


2.3复合索引

聚集索引和普通索引都有可能由多个字段组成,多个字段组成的索引的叶节点的Key由多个字段的值按照顺序拼接而成。这种索引的存储结构是这样的,首先按照第一个字段建立一棵B+Tree,叶节点的Key是第一个字段的值,叶节点的value又是一棵小的B+Tree,逐级递减。对于这样的索引,用排在第一的字段作为检索条件才有效提高检索效率。排在后面的字段只能在排在他前面的字段都在检索条件中的时候才能起辅助效果。下面我们用例子来说明这种情况。

我们在UP_User表上建立一个用来测试的复合索引,建立在 (`nickname`,`regTime`)两个字段上,下面我们测试下检索性能:

 select * from UP_User where nickName='fredlong'; 
# Query_time: 0.000443 Lock_time: 0.000101 Rows_sent: 1 Rows_examined: 1 Rows_affected: 0
# Bytes_sent: 778 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 4 


我们看到索引起作用了,和普通索引的效果一样都用了0.43ms,读取了四个Page就完成了任务。

 select * from UP_User where regTime = '2015-04-27 09:53:02'; 
# Query_time: 0.007076 Lock_time: 0.000286 Rows_sent: 1 Rows_examined: 1816 Rows_affected: 0 
# Bytes_sent: 803 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: Yes Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 26 


从这次选择的执行情况来看,虽然regTime在刚才建立的复合索引中,还是做了全局扫描。因为这个复合索引排在regTime字段前面的nickname字段没有出现在选择条件中,所以这个索引我们没用用到。
那么我们什么情况下会用到复合索引呢。我一般在两种情况下会用到:
 需要复合索引来排重的时候。 用索引的第一个字段选取出来的结果不够精准,需要第二个字段做进一步的性能优化。

 我基本上没有建立过三个以上的字段做复合索引,如果出现这种情况,我觉的你的表设计可能出现了大而全的问题,需要在表设计层面调优,而不是通过增加复杂的索引调优。所有复杂的东西都是大概率有问题的。

3.批量选择的效率
我们业务经常这样的诉求,选择某个用户发送的所有消息,这个帖子所有回复内容,所有昨天注册的用户的UserId等。这样的诉求需要从数据库中取出一批数据,而不是一条数据,数据库对这种请求的处理逻辑稍微复杂一点,下面我们来分析一下各种情况。
3.1根据主键批量检索数据
我们有一张表PW_Like,专门存储Feed的所有赞(Like),该表使用feedId和userId做联合主键,其中feedId为排在第一位的字段。该表一共有19个Page的数据。下面我们选取feedId为11593所有的赞:

 select * from PW_Like where feedId = 11593; 
# Query_time: 0.000478 Lock_time: 0.000084 Rows_sent: 58 Rows_examined: 58 Rows_affected: 0 
# Bytes_sent: 865 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No
# InnoDB_pages_distinct: 2 


我们花了0.47ms取出了58条数据,但一共只读取了2个Page的数据,其中一个Page还是这个表的主键。说明这58条数据都存储在同一个Page中。这种情况,对于数据库来说,是效率最高的情况。

3.2根据普通索引批量检索数据
还是刚才那个表,我们除了主键以外,还在userId上建立了索引,因为有时候需要查询某个用户点过的赞。那么我们来看看只通过索引,不通过主键来检索批量数据时候,数据库的效率情况。

 select * from PW_Like where userId = 80000402; 
# Query_time: 0.002892 Lock_time: 0.000062 Rows_sent: 27 Rows_examined: 27 Rows_affected: 0 
# Bytes_sent: 399 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No
# InnoDB_pages_distinct: 15 


我们可以看到的结果是,虽然我们只取出了27条数据,但是我们读取了15个数据Page,花了2.8毫秒,虽然没有进行全局扫描,但基本上也把一般的数据块读取出来了。因为PW_Like中的数据由于是按照feedId物理排序的,所以这27条数据分别分布在13个Page中(有两个Page是索引和主键),所以数据库需要把这13个Page全部从磁盘中读取出来,哪怕某一个Page(16K)上只有一条数据(15Bytes),也需要把这个数据Page读出来才能取出所有的目标Row。

通过普通索引来检索批量数据,效率明显比通过主键来检索要低得多。因为数据是分散的,所以需要分散地读取数据Page进行拼接才能完成任务。但是索引对主键而言还是非常有必要的补充,比如上面这个例子,当用户量达到100万的时候,检索某一个用户点的所有的赞的成本也只是大概读取15个Page,花2ms左右。

3.2检索一段时间范围内的数据
选取一定范围内的数据是我们经常要遇到的问题。在海量数据的表中检索一定范围内的数据,很容易引起性能问题。我们遇到的最常见的需求有以下几种:

选取一段时间内注册的用户信息

这种时候,时间肯定不会是用户表的主键,如果直接用时间作为选择条件来检索,效率会非常差,如何解决这种问题呢?我采取的办法是,把注册时间作为用户表的索引,每次先把需要检索的时间的两端的userId都差出来,然后用这两个userId做选择条件做第三次查询就是我们想要的数据了。这三次检索我们只需要读取大约10个Page就能解决问题。这种方法看起来很麻烦,但是是表的数据量达到亿级的时候的唯一解决方案。
选取一段时间内的日志
日志表是我们最常见的表,如何设计好是经常聊到的话题。日志表之所以不好设计是因为大家都希望用时间作为主键,这样检索一段时间内的日志将非常方便。但是用时间作为主键有个非常大的弊端,当日志插入速度很快的时候,就会出现因为主键重复而引起冲突。
对于这种情况,我一般把日志生成时间和一个自增的Id作为日志表的联合主键,把时间作为第一个字段,这样即避免了日志插入过快引起的主键唯一性冲突,又能便捷地根据时间做检索工作,非常方便。下面是这种日志表的检索的例子,大家可以看到性能非常好。还有就是日志表最好是每天一个表,这样能更便利地管理和检索。

select * from log_test where logTime > "2015-12-27 11:53:05" and logTime < "2015-12-27 12:03:05";
# Query_time: 0.001158 Lock_time: 0.000084 Rows_sent: 599 Rows_examined: 599 Rows_affected: 0 
# Bytes_sent: 4347 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 3


4.批量选择的效率
我们在批量检索数据的时候,对选择的结果在大多数情况下,都需要数据是排好序的。排序的性能也是日常需要注意到的,下面我们分三种情况来分析下数据库是如何排序的。
对于ORDER BY 主键这种情况,数据库是非常乐于见到的,基本上不会有额外性能的损耗,因为数据本来就是按照主键顺序存储的,取出来直接返回即可。
下面的所有关于排序的例子是在UP_MessageHistory表上做的实验,这个表一共有195个page,35417行数据,主键建立在字段id上,在sendUserId和destUserId上都建立了索引。
首先我们先做一个没有排序的检索:

 select * from `CU`.`UP_MessageHistory` where sendUserId = 92; 
# Query_time: 0.016135 Lock_time: 0.000084 Rows_sent: 3572 Rows_examined: 3572 Rows_affected: 0 
# Bytes_sent: 95600 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 125  


然后我们在相同的条件下对id进行排序: 

 select * from `CU`.`UP_MessageHistory` where sendUserId = 92 order by id; 
# Query_time: 0.016259 Lock_time: 0.000086 Rows_sent: 3572 Rows_examined: 3572 Rows_affected: 0 
# Bytes_sent: 95600 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 125 

 
从上面的数据可以看出,性能和没有加order by差不多,基本没有额外的性能损耗。接下来我们对索引进行排序:

 select * from `CU`.`UP_MessageHistory` where sendUserId = 92 order by destUserId; 
# Query_time: 0.018107 Lock_time: 0.000083 Rows_sent: 3572 Rows_examined: 7144 Rows_affected: 0 
# Bytes_sent: 103123 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No
# InnoDB_pages_distinct: 125 



接下来我们用普通字符串字段做排序再看看:

 select * from `CU`.`UP_MessageHistory` where sendUserId = 92 order by content; 
# Query_time: 0.023611 Lock_time: 0.000085 Rows_sent: 3572 Rows_examined: 7144 Rows_affected: 0 
# Bytes_sent: 105214 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 125 



然后我们再用普通的数字类型字段排序看看情况:

 select * from `CU`.`UP_MessageHistory` where sendUserId = 92 order by sentTime; 
# Query_time: 0.018522 Lock_time: 0.000107 Rows_sent: 3572 Rows_examined: 7144 Rows_affected: 0 
# Bytes_sent: 95709 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 # QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 125 


 针对以上的实验结果,我们可以得出以下结论:
针对主键做排序操作不会有性能损耗;针对不在选择条件中的索引字段做排序操作,索引不会起优化排序的作用;针对数值类型字段排序会比针对字符串类型字段排序的效率要高很多。

下面我们再研究下用选择条件中的索引字段排序,数据库是否会优化排序算法,我们任然用UP_User表来研究。

 select * from UP_User where score > 10000; 
# Query_time: 0.001470 Lock_time: 0.000130 Rows_sent: 122 Rows_examined: 122 Rows_affected: 0 
# Bytes_sent: 9559 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 17 


然后我们用选择条件中的索引字段做排序:

 select * from UP_User where score > 10000 order by score 
# Query_time: 0.001407 Lock_time: 0.000087 Rows_sent: 122 Rows_examined: 122 Rows_affected: 0 
# Bytes_sent: 9559 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No# InnoDB_pages_distinct: 17 



然后我们用选择条件中的非索引数值字段做排序:

 select * from UP_User where score > 10000 ORDER BY `securityQuestion` 
# Query_time: 0.002017 Lock_time: 0.000104 Rows_sent: 122 Rows_examined: 244 Rows_affected: 0 
# Bytes_sent: 9657 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 17 


从上面三条查询语句的执行时间来分析,使用索引字段排序和不排序花的时间差不多,比使用普通字段排序花的时间少一些,因此我们可以得出第四条结论:

针对在选择条件中的索引字段做排序操作,索引会起优化排序的作用。

5.索引维护

在前面我们可以看到所有的主键和索引都是排好序的,那么排序这件事情就需要销号资源,每次有新的数据插入,或者老的数据的数值发生变更,排序就需要调整,这里面是需要损耗性能的,下面我们分析一下。

自增型字段作为主键时,数据库对主键的维护成本非常低:
每次新增加的值都是一个最大的值,追加到最后即可,其他数据不需要挪动;这种数据一般不做修改。






  
使用业务型字段作为主键时,主键维护成本会比较高。每次生成的新数据都有可能需要挪动其他数据的位置。
 





 
 
因为innodb主键和数据是在放在一块的,每次挪动主键,也需要挪动数据,维护的成本会比较搞,对于需要频繁写入的表,不建议使用业务字段作为主键的。
由于主键是所有索引的叶节点的值,也是数据排序的依据,如果主键的值被修改,那么需要修改所有相关索引,并且需要修改整个主键B+Tree的排序,损耗会非常大。避免频繁更新主键可以避免以上提到的问题。

 update UP_User set userId = 100000945 where userId = 10000094; 
# Query_time: 0.010916 Lock_time: 0.000201 Rows_sent: 0 Rows_examined: 1 Rows_affected: 1 
# Bytes_sent: 59 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 11 从上面的数据可以看出来,索然SQL语句只修改了一条数据,却影响了11个Page。
 

 

相对于主键而已,索引就轻很多,它的叶节点的值是主键,很轻,维护起来成本比较低。但也不建议为一个表建立过多索引。维护一个索引成本低,维护8个就不一定低了,这种事需要均衡地对待。

 
6.索引设计原则

索引其实是一把双刃剑,用好了事半功倍,没用好,事倍功半。

主键的字段无特殊情况,一定要使用数值类型的,排序时占用计算资源少,存储时占用空间也少。若主键的字段值很大,则整个数据表的各种索引也会变得没有效率,因为所有的索引的叶节点的值都是主键。

并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引,如一表中有字段 sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。 查看全部
作者:黄湘龙
架构师交流群(312254004)
欢迎非商业转载,商业使用请联系我 
 

索引是有效使用数据库的基础,但你的数据量很小的时候,或许通过扫描整表来存取数据的性能还能接受,但当数据量极大时,当访问量极大时,就一定需要通过索引的辅助才能有效地存取数据。一般索引建立的好坏是性能好坏的成功关键。

1.InnoDb数据与索引存储细节

使用InnoDb作为数据引擎的Mysql和有聚集索引的SqlServer的数据存储结构有点类似,虽然在物理层面,他们都存储在Page上,但在逻辑上面,我们可以把数据分为三块:数据区域,索引区域,主键区域,他们通过主键的值作为关联,配合工作。默认配置下,一个Page的大小为16K。

一个表数据空间中的索引数据区域中有很多索引,每一个索引都是一颗B+Tree,在索引的B+Tree中索引的值作为B+Tree的节点的Key,数据主键作为节点的Value。

在InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点数据域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主键索引。这种索引也叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没有),如果没有显式指定,则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。

 表数据都以Row的形式放在一个一个大小为16K的Page中,在每个数据Page中有都页头信息和一行一行的数据。其中页头信息中主要放置的是这一页数据中的所有主键值和其对应的OFFSET,便于通过主键能迅速找到其对应的数据位置。

存储结构.jpg


2.索引优化检索的原理

索引是数据库的灵魂,如果没有索引,数据库也就是一堆文本文件,存在的意义并不大。索引能让数据库成几何倍数地提高检索效率。使用Innodb作为数据引擎的Mysql数据库的索引分为聚集索引(也就是主键)和普通索引。上节我们已经讲解了这两种索引的存储结构,现在我们仔细讲解下索引是如何工作的。

聚集索引和普通索引都有可能由多个字段组成,我们又称这种索引为复合索引,1.2.3将为大家解析这种索引的性能情况.

2.1聚集索引
从上节我们知晓,Innodb的所有数据是按照聚集索引排序的,聚集索引这种存储方式使得按主键的搜索十分高效,如果我们SQL语句的选择条件中有聚集索引,数据库会优先使用聚集索引来进行检索工作。
数据库引擎根据条件中的主键的值,迅速在B+Tree中找到主键对应的叶节点,然后把叶节点所在的Page数据库读取到内存,返回给用户,如上图绿色线条的流向。下面我们来运行一条SQL,从数据库的执行情况分析一下:


select * from UP_User where userId = 10000094;

......

# Query_time: 0.000399  Lock_time: 0.000101  Rows_sent: 1  Rows_examined: 1  Rows_affected: 0

# Bytes_sent: 803  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0

# InnoDB_trx_id: 1D4D

# QC_Hit: No  Full_scan: No  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No

# Filesort: No  Filesort_on_disk: No  Merge_passes: 0

#   InnoDB_IO_r_ops: 0  InnoDB_IO_r_bytes: 0  InnoDB_IO_r_wait: 0.000000

#   InnoDB_rec_lock_wait: 0.000000  InnoDB_queue_wait: 0.000000

#   InnoDB_pages_distinct: 2

SET timestamp=1451104535;

select * from UP_User where userId = 10000094;




我们可以看到,数据库读从磁盘取了两个Page就把809Bytes的数据提取出来返回给客户端。
下面我们试一下,如果试一下选取条件没有包含主键和索引的情况:


select * from `UP_User` where bigPortrait = '5F29E883BFA8903B';

# Query_time: 0.002869 Lock_time: 0.000094 Rows_sent: 1 Rows_examined: 1816 Rows_affected: 0

# Bytes_sent: 792 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0

# QC_Hit: No Full_scan: Yes Full_join: No Tmp_table: No Tmp_table_on_disk: No

# InnoDB_pages_distinct: 25



可以看到如果使用主键作为检索条件,检索时间花了0.3ms,只读取了两个Page,而不使用主键作为检索条件,检索时间花了2.8ms,读取了25个Page,全局扫描才把这条记录给找出来。这还是一个只有1000多行的表,如果更大的数据量,对比更加强烈。

 对于这两个Page,一个是主键B+Tree的数据,一个是10000094这条数据所在的数据页。


2.2普通索引
我们用普通索引作为检索条件来搜索数据需要检索两遍索引:首先检索普通索引获得主键,然后用主键到主索引中检索获得记录。如上图红色线条的流向。
下面我用一个例子来看看数据库的表现:


 select * from UP_User where userName = 'fred';# Query_time: 0.000400 Lock_time: 0.000101 Rows_sent: 1 Rows_examined: 1 Rows_affected: 0

# Bytes_sent: 803 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0

# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No

# InnoDB_pages_distinct: 4



 我们可以看到数据库用了0.4ms来检索这条数据,读取了4个page,比用主键作为检索条件多用了0.1ms,多读取了两个Page,而这两个Page就是userName这个普通索引的B+Tree所在的数据页。


2.3复合索引

聚集索引和普通索引都有可能由多个字段组成,多个字段组成的索引的叶节点的Key由多个字段的值按照顺序拼接而成。这种索引的存储结构是这样的,首先按照第一个字段建立一棵B+Tree,叶节点的Key是第一个字段的值,叶节点的value又是一棵小的B+Tree,逐级递减。对于这样的索引,用排在第一的字段作为检索条件才有效提高检索效率。排在后面的字段只能在排在他前面的字段都在检索条件中的时候才能起辅助效果。下面我们用例子来说明这种情况。

我们在UP_User表上建立一个用来测试的复合索引,建立在 (`nickname`,`regTime`)两个字段上,下面我们测试下检索性能:


 select * from UP_User where nickName='fredlong'; 
# Query_time: 0.000443 Lock_time: 0.000101 Rows_sent: 1 Rows_examined: 1 Rows_affected: 0
# Bytes_sent: 778 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 4 



我们看到索引起作用了,和普通索引的效果一样都用了0.43ms,读取了四个Page就完成了任务。


 select * from UP_User where regTime = '2015-04-27 09:53:02'; 
# Query_time: 0.007076 Lock_time: 0.000286 Rows_sent: 1 Rows_examined: 1816 Rows_affected: 0 
# Bytes_sent: 803 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: Yes Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 26 



从这次选择的执行情况来看,虽然regTime在刚才建立的复合索引中,还是做了全局扫描。因为这个复合索引排在regTime字段前面的nickname字段没有出现在选择条件中,所以这个索引我们没用用到。
那么我们什么情况下会用到复合索引呢。我一般在两种情况下会用到:
  1.  需要复合索引来排重的时候。
  2.  用索引的第一个字段选取出来的结果不够精准,需要第二个字段做进一步的性能优化。


 我基本上没有建立过三个以上的字段做复合索引,如果出现这种情况,我觉的你的表设计可能出现了大而全的问题,需要在表设计层面调优,而不是通过增加复杂的索引调优。所有复杂的东西都是大概率有问题的。

3.批量选择的效率
我们业务经常这样的诉求,选择某个用户发送的所有消息,这个帖子所有回复内容,所有昨天注册的用户的UserId等。这样的诉求需要从数据库中取出一批数据,而不是一条数据,数据库对这种请求的处理逻辑稍微复杂一点,下面我们来分析一下各种情况。
3.1根据主键批量检索数据
我们有一张表PW_Like,专门存储Feed的所有赞(Like),该表使用feedId和userId做联合主键,其中feedId为排在第一位的字段。该表一共有19个Page的数据。下面我们选取feedId为11593所有的赞:


 select * from PW_Like where feedId = 11593; 
# Query_time: 0.000478 Lock_time: 0.000084 Rows_sent: 58 Rows_examined: 58 Rows_affected: 0 
# Bytes_sent: 865 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No
# InnoDB_pages_distinct: 2 



我们花了0.47ms取出了58条数据,但一共只读取了2个Page的数据,其中一个Page还是这个表的主键。说明这58条数据都存储在同一个Page中。这种情况,对于数据库来说,是效率最高的情况。

3.2根据普通索引批量检索数据
还是刚才那个表,我们除了主键以外,还在userId上建立了索引,因为有时候需要查询某个用户点过的赞。那么我们来看看只通过索引,不通过主键来检索批量数据时候,数据库的效率情况。


 select * from PW_Like where userId = 80000402; 
# Query_time: 0.002892 Lock_time: 0.000062 Rows_sent: 27 Rows_examined: 27 Rows_affected: 0 
# Bytes_sent: 399 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No
# InnoDB_pages_distinct: 15 



我们可以看到的结果是,虽然我们只取出了27条数据,但是我们读取了15个数据Page,花了2.8毫秒,虽然没有进行全局扫描,但基本上也把一般的数据块读取出来了。因为PW_Like中的数据由于是按照feedId物理排序的,所以这27条数据分别分布在13个Page中(有两个Page是索引和主键),所以数据库需要把这13个Page全部从磁盘中读取出来,哪怕某一个Page(16K)上只有一条数据(15Bytes),也需要把这个数据Page读出来才能取出所有的目标Row。

通过普通索引来检索批量数据,效率明显比通过主键来检索要低得多。因为数据是分散的,所以需要分散地读取数据Page进行拼接才能完成任务。但是索引对主键而言还是非常有必要的补充,比如上面这个例子,当用户量达到100万的时候,检索某一个用户点的所有的赞的成本也只是大概读取15个Page,花2ms左右。

3.2检索一段时间范围内的数据
选取一定范围内的数据是我们经常要遇到的问题。在海量数据的表中检索一定范围内的数据,很容易引起性能问题。我们遇到的最常见的需求有以下几种:

选取一段时间内注册的用户信息

这种时候,时间肯定不会是用户表的主键,如果直接用时间作为选择条件来检索,效率会非常差,如何解决这种问题呢?我采取的办法是,把注册时间作为用户表的索引,每次先把需要检索的时间的两端的userId都差出来,然后用这两个userId做选择条件做第三次查询就是我们想要的数据了。这三次检索我们只需要读取大约10个Page就能解决问题。这种方法看起来很麻烦,但是是表的数据量达到亿级的时候的唯一解决方案。
选取一段时间内的日志
日志表是我们最常见的表,如何设计好是经常聊到的话题。日志表之所以不好设计是因为大家都希望用时间作为主键,这样检索一段时间内的日志将非常方便。但是用时间作为主键有个非常大的弊端,当日志插入速度很快的时候,就会出现因为主键重复而引起冲突。
对于这种情况,我一般把日志生成时间和一个自增的Id作为日志表的联合主键,把时间作为第一个字段,这样即避免了日志插入过快引起的主键唯一性冲突,又能便捷地根据时间做检索工作,非常方便。下面是这种日志表的检索的例子,大家可以看到性能非常好。还有就是日志表最好是每天一个表,这样能更便利地管理和检索。


select * from log_test where logTime > "2015-12-27 11:53:05" and logTime < "2015-12-27 12:03:05";
# Query_time: 0.001158 Lock_time: 0.000084 Rows_sent: 599 Rows_examined: 599 Rows_affected: 0 
# Bytes_sent: 4347 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 3



4.批量选择的效率
我们在批量检索数据的时候,对选择的结果在大多数情况下,都需要数据是排好序的。排序的性能也是日常需要注意到的,下面我们分三种情况来分析下数据库是如何排序的。
对于ORDER BY 主键这种情况,数据库是非常乐于见到的,基本上不会有额外性能的损耗,因为数据本来就是按照主键顺序存储的,取出来直接返回即可。
下面的所有关于排序的例子是在UP_MessageHistory表上做的实验,这个表一共有195个page,35417行数据,主键建立在字段id上,在sendUserId和destUserId上都建立了索引。
首先我们先做一个没有排序的检索:


 select * from `CU`.`UP_MessageHistory` where sendUserId = 92; 
# Query_time: 0.016135 Lock_time: 0.000084 Rows_sent: 3572 Rows_examined: 3572 Rows_affected: 0 
# Bytes_sent: 95600 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 125  



然后我们在相同的条件下对id进行排序: 


 select * from `CU`.`UP_MessageHistory` where sendUserId = 92 order by id; 
# Query_time: 0.016259 Lock_time: 0.000086 Rows_sent: 3572 Rows_examined: 3572 Rows_affected: 0 
# Bytes_sent: 95600 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 125 


 
从上面的数据可以看出,性能和没有加order by差不多,基本没有额外的性能损耗。接下来我们对索引进行排序:


 select * from `CU`.`UP_MessageHistory` where sendUserId = 92 order by destUserId; 
# Query_time: 0.018107 Lock_time: 0.000083 Rows_sent: 3572 Rows_examined: 7144 Rows_affected: 0 
# Bytes_sent: 103123 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No
# InnoDB_pages_distinct: 125 




接下来我们用普通字符串字段做排序再看看:


 select * from `CU`.`UP_MessageHistory` where sendUserId = 92 order by content; 
# Query_time: 0.023611 Lock_time: 0.000085 Rows_sent: 3572 Rows_examined: 7144 Rows_affected: 0 
# Bytes_sent: 105214 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 125 




然后我们再用普通的数字类型字段排序看看情况:


 select * from `CU`.`UP_MessageHistory` where sendUserId = 92 order by sentTime; 
# Query_time: 0.018522 Lock_time: 0.000107 Rows_sent: 3572 Rows_examined: 7144 Rows_affected: 0 
# Bytes_sent: 95709 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 # QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 125 



 针对以上的实验结果,我们可以得出以下结论:
  1. 针对主键做排序操作不会有性能损耗;
  2. 针对不在选择条件中的索引字段做排序操作,索引不会起优化排序的作用;
  3. 针对数值类型字段排序会比针对字符串类型字段排序的效率要高很多。


下面我们再研究下用选择条件中的索引字段排序,数据库是否会优化排序算法,我们任然用UP_User表来研究。


 select * from UP_User where score > 10000; 
# Query_time: 0.001470 Lock_time: 0.000130 Rows_sent: 122 Rows_examined: 122 Rows_affected: 0 
# Bytes_sent: 9559 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 17 



然后我们用选择条件中的索引字段做排序:


 select * from UP_User where score > 10000 order by score 
# Query_time: 0.001407 Lock_time: 0.000087 Rows_sent: 122 Rows_examined: 122 Rows_affected: 0 
# Bytes_sent: 9559 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No# InnoDB_pages_distinct: 17 




然后我们用选择条件中的非索引数值字段做排序:


 select * from UP_User where score > 10000 ORDER BY `securityQuestion` 
# Query_time: 0.002017 Lock_time: 0.000104 Rows_sent: 122 Rows_examined: 244 Rows_affected: 0 
# Bytes_sent: 9657 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 17 



从上面三条查询语句的执行时间来分析,使用索引字段排序和不排序花的时间差不多,比使用普通字段排序花的时间少一些,因此我们可以得出第四条结论:

针对在选择条件中的索引字段做排序操作,索引会起优化排序的作用。

5.索引维护

在前面我们可以看到所有的主键和索引都是排好序的,那么排序这件事情就需要销号资源,每次有新的数据插入,或者老的数据的数值发生变更,排序就需要调整,这里面是需要损耗性能的,下面我们分析一下。

自增型字段作为主键时,数据库对主键的维护成本非常低:
  1. 每次新增加的值都是一个最大的值,追加到最后即可,其他数据不需要挪动;
  2. 这种数据一般不做修改。



聚集索引索引维护.jpg

  
使用业务型字段作为主键时,主键维护成本会比较高。每次生成的新数据都有可能需要挪动其他数据的位置。
 

聚集索引索引维护2.jpg

 
 
因为innodb主键和数据是在放在一块的,每次挪动主键,也需要挪动数据,维护的成本会比较搞,对于需要频繁写入的表,不建议使用业务字段作为主键的。
由于主键是所有索引的叶节点的值,也是数据排序的依据,如果主键的值被修改,那么需要修改所有相关索引,并且需要修改整个主键B+Tree的排序,损耗会非常大。避免频繁更新主键可以避免以上提到的问题。


 update UP_User set userId = 100000945 where userId = 10000094; 
# Query_time: 0.010916 Lock_time: 0.000201 Rows_sent: 0 Rows_examined: 1 Rows_affected: 1 
# Bytes_sent: 59 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 11 从上面的数据可以看出来,索然SQL语句只修改了一条数据,却影响了11个Page。
 


 

相对于主键而已,索引就轻很多,它的叶节点的值是主键,很轻,维护起来成本比较低。但也不建议为一个表建立过多索引。维护一个索引成本低,维护8个就不一定低了,这种事需要均衡地对待。

 
6.索引设计原则

索引其实是一把双刃剑,用好了事半功倍,没用好,事倍功半。

主键的字段无特殊情况,一定要使用数值类型的,排序时占用计算资源少,存储时占用空间也少。若主键的字段值很大,则整个数据表的各种索引也会变得没有效率,因为所有的索引的叶节点的值都是主键。

并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引,如一表中有字段 sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。

MySQL 5.7 传统复制到GTID在线切换

MySQLwubx 发表了文章 • 2 个评论 • 751 次浏览 • 2015-12-29 10:13 • 来自相关话题

作者:吴炳锡 来源:http://wubx.net/ 联系方式: wubingxi#163.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

前题:
     1. 要求MySQL 5.7.6及以后版本。
    2. 所有组中成员gtid_mode 为off状态。


实现步骤:
1. 所有的Server执行 
  set @@global.enforce_gtid_consistency = warn;
    
   特别注意: 这一步是关建的一步使用不能出现警告。

2.所有的server上执行:
    set @@global.enforce_gtid_consistency = on;

3.所有的Server上执行(不关心最先最后,但要执行完):
    set @@global.gtid_mode = off_permissive;

4. 执行:
    set @@global.gtid_mode=on_permissive;

    实质在这一步骤生的日志都是带GTID的日志了,这个步骤号称是不关心任何节点,但从实管理上推荐在slave上先执行,然后再去master上执行。


5. 确认传统的binlog复制完毕,该值为0
    show status like 'ongoing_anonymous_transaction_count';

  需要所有的节点都确认为0.

6.  所有节点进行判断 show status like 'ongoing_anonymous_transaction_count'; 为零
    所有的节点也可以执行一下: flush logs; 用于切换一下日志。

7.  所有的节点启用gtid_mode
    set @@global.gtid_mode=on

8.  把gtid_mode = on相关配置写入配置文件
    gtid_mode=on
    enforce_gtid_consistency=on

9. 启用Gtid的自动查找节点复制:
stop slave;
change master to master_auto_position=1;
start slave;

完毕。Good Luck。
     查看全部
作者:吴炳锡 来源:http://wubx.net/ 联系方式: wubingxi#163.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

前题:
     1. 要求MySQL 5.7.6及以后版本。
    2. 所有组中成员gtid_mode 为off状态。


实现步骤:
1. 所有的Server执行 
  set @@global.enforce_gtid_consistency = warn;
    
   特别注意: 这一步是关建的一步使用不能出现警告。

2.所有的server上执行:
    set @@global.enforce_gtid_consistency = on;

3.所有的Server上执行(不关心最先最后,但要执行完):
    set @@global.gtid_mode = off_permissive;

4. 执行:
    set @@global.gtid_mode=on_permissive;

    实质在这一步骤生的日志都是带GTID的日志了,这个步骤号称是不关心任何节点,但从实管理上推荐在slave上先执行,然后再去master上执行。


5. 确认传统的binlog复制完毕,该值为0
    show status like 'ongoing_anonymous_transaction_count';

  需要所有的节点都确认为0.

6.  所有节点进行判断 show status like 'ongoing_anonymous_transaction_count'; 为零
    所有的节点也可以执行一下: flush logs; 用于切换一下日志。

7.  所有的节点启用gtid_mode
    set @@global.gtid_mode=on

8.  把gtid_mode = on相关配置写入配置文件
    gtid_mode=on
    enforce_gtid_consistency=on

9. 启用Gtid的自动查找节点复制:
stop slave;
change master to master_auto_position=1;
start slave;

完毕。Good Luck。
    


3分钟搞定MySQL5.7安装

MySQLzhangdh 发表了文章 • 3 个评论 • 862 次浏览 • 2015-12-22 19:49 • 来自相关话题

1.下载MySQL5.7二进制版本mkdir /opt/mysql
cd /opt/mysql
wget [url]http://downloads.mysql.com/arc ... ar.gz[/url]2.解压MySQL5.7到/opt/mysql下tar zxf mysql-5.7.9-linux-glibc2.5-x86_64.tar.gz3. 创建一个软连接,方便升级cd /usr/local ln -s /opt/mysql/mysql-5.7.9-linux-glibc2.5-x86_64 mysql4. 创建MySQl启动用户和组groupadd mysql
useradd -d /usr/local/mysql -g mysql -s /sbin/nologin mysql chown -R mysql:mysql /usr/local/mysql5. 创建基本目录mkdir -p /data/mysql/mysql3307/{data,logs,tmp}
chown -R mysql:mysql /data/mysql/mysql33076. 创建my.cnf配置文件(见附件)vim /data/mysql/mysql3307/my.cnf7. 初始化数据库
·注意MySQL5.7初始化已经融合到bin/mysqld命令中,通过“--initialize”来完成初始化,更多详细的命令,可以执行下面的命令来查看·/usr/local/mysql/bin/mysqld --verbose --help|less本例中数据库初始化命令如下(它将没有任何的输出,但是你可以从日志进行查看):/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3307/my.cnf --initialize8. 设置环境变量echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
source /etc/profile9. 启动MySQL/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3307/my.cnf &10. 修改MySQL的root用户密码
初始完后需要用户自行更改root用户密码,否则执行“mysql -S /tmp/mysql3307.sock -u root -p”将报下面的错误:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
这时候我们需要关闭MySQL数据库,执行下面的命令更改MySQL root的初始化密码:/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql3307/my.cnf --skip-grant-tables &
mysql -S /tmp/mysql3307.sock

>update mysql.user set authentication_string=password('3wedu.net') where user='root' and host='localhost';
>flush privileges;
>\q关闭MySQL数据库,然后使用mysqld启动数据库/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3307/my.cnf &启动完毕后,如果直接执行“/usr/local/mysql/bin/mysql -S /tmp/mysql3307.sock -u root -p”,将报下面的错误:
ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords.
这时候需要执行下面的命令对数据库进行密码更改/usr/local/mysql/bin/mysql -S /tmp/mysql3307.sock -u root -p --connect-expired-password

>set password=password('3wedu.net');
>flush privileges; >\q更改完成后,就可以使用下面的命令直接登录了/usr/local/mysql/bin/mysql -S /tmp/mysql3307.sock -u root -p11. 附件:my.cnf
#my.cnf
[client]
port            = 3307
socket          = /tmp/mysql3307.sock

[mysql]
prompt="\\u@\\h:\p  \\R:\\m:\\s [\\d]>"
#tee=/data/mysql/mysql_3306/data/query.log
no-auto-rehash
[mysqld_multi]
mysqld     = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user       = zhangdh
password   = zhangdh1113
log        = /data/mysql/mysql3307/logs/multi.log
#tcp-ip

[mysqld]
#misc
user = mysql
basedir = /usr/local/mysql
datadir = /data/mysql/mysql3307/data
port = 3307
socket = /tmp/mysql3307.sock
event_scheduler = 0
#tmp
tmpdir=/data/mysql/mysql3307/tmp

#timeout
interactive_timeout = 300
wait_timeout = 300

#character set
character-set-server = utf8

open_files_limit = 65535
max_connections = 100
max_connect_errors = 100000

skip-name-resolve = 1
#logs
log-output=file
slow_query_log = 1
slow_query_log_file = slow.log
log-error = error.log
log_warnings = 2
pid-file = mysql.pid
long_query_time = 1
#log-slow-admin-statements = 1
#log-queries-not-using-indexes = 1
log-slow-slave-statements = 1


#binlog
binlog_format = row
server-id = 2003307
log-bin = /data/mysql/mysql3307/logs/mysql-bin
binlog_cache_size = 4M
max_binlog_size = 1G
max_binlog_cache_size = 2G
sync_binlog = 0
expire_logs_days = 10

#relay log
skip_slave_start = 1
max_relay_log_size = 1G
relay_log_purge = 1
relay_log_recovery = 1
log_slave_updates
#slave-skip-errors=1032,1053,1062

explicit_defaults_for_timestamp=1
#buffers & cache
table_open_cache = 2048
table_definition_cache = 2048
table_open_cache = 2048
max_heap_table_size = 96M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 256
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 256K
query_cache_min_res_unit = 512
thread_stack = 192K
tmp_table_size = 96M
key_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 32M

#myisam
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1

#innodb
innodb_buffer_pool_size = 100M
innodb_buffer_pool_instances = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 64M
innodb_log_file_size = 500M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 50
innodb_file_per_table = 1
innodb_rollback_on_timeout
innodb_status_file = 1
innodb_io_capacity = 2000
transaction_isolation = READ-COMMITTED
innodb_flush_method = O_DIRECT

#[mysqld3306]
#port            = 3306
#datadir = /data/mysql/mysql_3306/data
#socket = /tmp/mysql_3306.sock
#tmpdir=/data/mysql/mysql_3306/tmp
#server-id = 2143306
#log-bin = /data/mysql/mysql_3306/logs/mysql-bin

#[mysqld3316]
#port            = 3316
#datadir = /data/mysql/mysql_3316/data
#socket = /tmp/mysql_3316.sock
#tmpdir=/data/mysql/mysql_3316/tmp
#server-id = 2143316
#log-bin = /data/mysql/mysql_3316/logs/mysql-bin

[mysqld_safe]
#malloc-lib=/usr/local/mysql/lib/jmalloc.so 
nice=-19
open-files-limit=65535 查看全部
1.下载MySQL5.7二进制版本
mkdir /opt/mysql
cd /opt/mysql
wget [url]http://downloads.mysql.com/arc ... ar.gz[/url]
2.解压MySQL5.7到/opt/mysql下
tar zxf mysql-5.7.9-linux-glibc2.5-x86_64.tar.gz
3. 创建一个软连接,方便升级
cd /usr/local ln -s /opt/mysql/mysql-5.7.9-linux-glibc2.5-x86_64 mysql
4. 创建MySQl启动用户和组
groupadd mysql
useradd -d /usr/local/mysql -g mysql -s /sbin/nologin mysql chown -R mysql:mysql /usr/local/mysql
5. 创建基本目录
mkdir -p /data/mysql/mysql3307/{data,logs,tmp} 
chown -R mysql:mysql /data/mysql/mysql3307
6. 创建my.cnf配置文件(见附件)
vim /data/mysql/mysql3307/my.cnf
7. 初始化数据库
·注意MySQL5.7初始化已经融合到bin/mysqld命令中,通过“--initialize”来完成初始化,更多详细的命令,可以执行下面的命令来查看·
/usr/local/mysql/bin/mysqld --verbose --help|less
本例中数据库初始化命令如下(它将没有任何的输出,但是你可以从日志进行查看):
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3307/my.cnf --initialize
8. 设置环境变量
echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
source /etc/profile
9. 启动MySQL
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3307/my.cnf &
10. 修改MySQL的root用户密码
初始完后需要用户自行更改root用户密码,否则执行“mysql -S /tmp/mysql3307.sock -u root -p”将报下面的错误:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
这时候我们需要关闭MySQL数据库,执行下面的命令更改MySQL root的初始化密码:
/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql3307/my.cnf --skip-grant-tables &
mysql -S /tmp/mysql3307.sock

>update mysql.user set authentication_string=password('3wedu.net') where user='root' and host='localhost';
>flush privileges;
>\q
关闭MySQL数据库,然后使用mysqld启动数据库
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3307/my.cnf &
启动完毕后,如果直接执行“/usr/local/mysql/bin/mysql -S /tmp/mysql3307.sock -u root -p”,将报下面的错误:
ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords.
这时候需要执行下面的命令对数据库进行密码更改
/usr/local/mysql/bin/mysql -S /tmp/mysql3307.sock -u root -p --connect-expired-password

>set password=password('3wedu.net');
>flush privileges; >\q
更改完成后,就可以使用下面的命令直接登录了
/usr/local/mysql/bin/mysql -S /tmp/mysql3307.sock -u root -p
11. 附件:my.cnf
#my.cnf
[client]
port            = 3307
socket          = /tmp/mysql3307.sock

[mysql]
prompt="\\u@\\h:\p  \\R:\\m:\\s [\\d]>"
#tee=/data/mysql/mysql_3306/data/query.log
no-auto-rehash
[mysqld_multi]
mysqld     = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user       = zhangdh
password   = zhangdh1113
log        = /data/mysql/mysql3307/logs/multi.log
#tcp-ip

[mysqld]
#misc
user = mysql
basedir = /usr/local/mysql
datadir = /data/mysql/mysql3307/data
port = 3307
socket = /tmp/mysql3307.sock
event_scheduler = 0
#tmp
tmpdir=/data/mysql/mysql3307/tmp

#timeout
interactive_timeout = 300
wait_timeout = 300

#character set
character-set-server = utf8

open_files_limit = 65535
max_connections = 100
max_connect_errors = 100000

skip-name-resolve = 1
#logs
log-output=file
slow_query_log = 1
slow_query_log_file = slow.log
log-error = error.log
log_warnings = 2
pid-file = mysql.pid
long_query_time = 1
#log-slow-admin-statements = 1
#log-queries-not-using-indexes = 1
log-slow-slave-statements = 1


#binlog
binlog_format = row
server-id = 2003307
log-bin = /data/mysql/mysql3307/logs/mysql-bin
binlog_cache_size = 4M
max_binlog_size = 1G
max_binlog_cache_size = 2G
sync_binlog = 0
expire_logs_days = 10

#relay log
skip_slave_start = 1
max_relay_log_size = 1G
relay_log_purge = 1
relay_log_recovery = 1
log_slave_updates
#slave-skip-errors=1032,1053,1062

explicit_defaults_for_timestamp=1
#buffers & cache
table_open_cache = 2048
table_definition_cache = 2048
table_open_cache = 2048
max_heap_table_size = 96M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 256
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 256K
query_cache_min_res_unit = 512
thread_stack = 192K
tmp_table_size = 96M
key_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 32M

#myisam
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1

#innodb
innodb_buffer_pool_size = 100M
innodb_buffer_pool_instances = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 64M
innodb_log_file_size = 500M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 50
innodb_file_per_table = 1
innodb_rollback_on_timeout
innodb_status_file = 1
innodb_io_capacity = 2000
transaction_isolation = READ-COMMITTED
innodb_flush_method = O_DIRECT

#[mysqld3306]
#port            = 3306
#datadir = /data/mysql/mysql_3306/data
#socket = /tmp/mysql_3306.sock
#tmpdir=/data/mysql/mysql_3306/tmp
#server-id = 2143306
#log-bin = /data/mysql/mysql_3306/logs/mysql-bin

#[mysqld3316]
#port            = 3316
#datadir = /data/mysql/mysql_3316/data
#socket = /tmp/mysql_3316.sock
#tmpdir=/data/mysql/mysql_3316/tmp
#server-id = 2143316
#log-bin = /data/mysql/mysql_3316/logs/mysql-bin

[mysqld_safe]
#malloc-lib=/usr/local/mysql/lib/jmalloc.so 
nice=-19
open-files-limit=65535

3分钟搞定MySQL5.6安装

MySQLzhangdh 发表了文章 • 2 个评论 • 632 次浏览 • 2015-12-22 16:43 • 来自相关话题

1. 下载MySQL二进制版本mkdir /opt/mysql
cd /opt/mysql
wget [url]http://cdn.mysql.com/archives/ ... ar.gz[/url]2. 将mysql解压到/opt/mysql下tar zxvf mysql-5.6.27-linux-glibc2.5-x86_64.tar.gz3. 创建一个软链,便于版本升级cd /usr/local/
ln -s /opt/mysql/mysql-5.6.27-linux-glibc2.5-x86_64 mysql4. 创建MySQL启动用户groupadd mysql
useradd -g mysql -s /sbin/nologin -d /usr/local/mysql mysql5. 创建基本的目录mkdir -p /data/mysql/mysql3306/{data,logs,tmp}
chown -R mysql:mysql /data/mysql/mysql33066. 创建/etc/my.cnf配置文件(见附件)

7. 做MySQL初始化cd /usr/local/mysql
./scripts/mysql_install_db8. 创建一个启动脚本cp support-files/mysql.server /etc/init.d/mysql9. 启动和关闭/etc/init.d/mysql start|stop|restart10. 配置文件参考:
#my.cnf
[client]
port            = 3306
socket          = /tmp/mysql.sock
[mysql]
prompt="\\u@\\h:\p  \\R:\\m:\\s [\\d]>"
#tee=/data/mysql/mysql3306/data/query.log
no-auto-rehash
[mysqld]
#misc
user = mysql
basedir = /usr/local/mysql
datadir = /data/mysql/mysql3306/data
port = 3306
socket = /tmp/mysql.sock
event_scheduler = 0
#tmp
tmpdir=/data/mysql/mysql3306/tmp
#timeout
interactive_timeout = 300
wait_timeout = 300
#character set
character-set-server = utf8
open_files_limit = 65535
max_connections = 100
max_connect_errors = 100000
skip-name-resolve = 1
#logs
log-output=file
slow_query_log = 1
slow_query_log_file = slow.log
log-error = error.log
log_warnings = 2
pid-file = mysql.pid
long_query_time = 1
#log-slow-admin-statements = 1
#log-queries-not-using-indexes = 1
log-slow-slave-statements = 1
#binlog
binlog_format = row
server-id = 1003306
log-bin = /data/mysql/mysql3306/logs/mysql-bin
binlog_cache_size = 4M
max_binlog_size = 1G
max_binlog_cache_size = 2G
sync_binlog = 0
expire_logs_days = 10
#relay log
skip_slave_start = 1
max_relay_log_size = 1G
relay_log_purge = 1
relay_log_recovery = 1
log_slave_updates
#slave-skip-errors=1032,1053,1062
explicit_defaults_for_timestamp=1
#buffers & cache
table_open_cache = 2048
table_definition_cache = 2048
table_open_cache = 2048
max_heap_table_size = 96M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 256
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 256K
query_cache_min_res_unit = 512
thread_stack = 192K
tmp_table_size = 96M
key_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 32M
#myisam
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
#innodb
innodb_buffer_pool_size = 100M
innodb_buffer_pool_instances = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 64M
innodb_log_file_size = 500M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 50
innodb_file_per_table = 1
innodb_rollback_on_timeout
innodb_status_file = 1
innodb_io_capacity = 2000
transaction_isolation = READ-COMMITTED
innodb_flush_method = O_DIRECT
[mysqld_safe]
#malloc-lib=/usr/local/mysql/lib/jmalloc.so
nice=-19
open-files-limit=65535 查看全部
1. 下载MySQL二进制版本
mkdir /opt/mysql
cd /opt/mysql
wget [url]http://cdn.mysql.com/archives/ ... ar.gz[/url]
2. 将mysql解压到/opt/mysql下
tar zxvf mysql-5.6.27-linux-glibc2.5-x86_64.tar.gz
3. 创建一个软链,便于版本升级
cd /usr/local/ 
ln -s /opt/mysql/mysql-5.6.27-linux-glibc2.5-x86_64 mysql
4. 创建MySQL启动用户
groupadd mysql 
useradd -g mysql -s /sbin/nologin -d /usr/local/mysql mysql
5. 创建基本的目录
mkdir -p /data/mysql/mysql3306/{data,logs,tmp}
chown -R mysql:mysql /data/mysql/mysql3306
6. 创建/etc/my.cnf配置文件(见附件)

7. 做MySQL初始化
cd /usr/local/mysql
./scripts/mysql_install_db
8. 创建一个启动脚本
cp support-files/mysql.server /etc/init.d/mysql
9. 启动和关闭
/etc/init.d/mysql start|stop|restart
10. 配置文件参考:
#my.cnf
[client]
port            = 3306
socket          = /tmp/mysql.sock
[mysql]
prompt="\\u@\\h:\p  \\R:\\m:\\s [\\d]>"
#tee=/data/mysql/mysql3306/data/query.log
no-auto-rehash
[mysqld]
#misc
user = mysql
basedir = /usr/local/mysql
datadir = /data/mysql/mysql3306/data
port = 3306
socket = /tmp/mysql.sock
event_scheduler = 0
#tmp
tmpdir=/data/mysql/mysql3306/tmp
#timeout
interactive_timeout = 300
wait_timeout = 300
#character set
character-set-server = utf8
open_files_limit = 65535
max_connections = 100
max_connect_errors = 100000
skip-name-resolve = 1
#logs
log-output=file
slow_query_log = 1
slow_query_log_file = slow.log
log-error = error.log
log_warnings = 2
pid-file = mysql.pid
long_query_time = 1
#log-slow-admin-statements = 1
#log-queries-not-using-indexes = 1
log-slow-slave-statements = 1
#binlog
binlog_format = row
server-id = 1003306
log-bin = /data/mysql/mysql3306/logs/mysql-bin
binlog_cache_size = 4M
max_binlog_size = 1G
max_binlog_cache_size = 2G
sync_binlog = 0
expire_logs_days = 10
#relay log
skip_slave_start = 1
max_relay_log_size = 1G
relay_log_purge = 1
relay_log_recovery = 1
log_slave_updates
#slave-skip-errors=1032,1053,1062
explicit_defaults_for_timestamp=1
#buffers & cache
table_open_cache = 2048
table_definition_cache = 2048
table_open_cache = 2048
max_heap_table_size = 96M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 256
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 256K
query_cache_min_res_unit = 512
thread_stack = 192K
tmp_table_size = 96M
key_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 32M
#myisam
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
#innodb
innodb_buffer_pool_size = 100M
innodb_buffer_pool_instances = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 64M
innodb_log_file_size = 500M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 50
innodb_file_per_table = 1
innodb_rollback_on_timeout
innodb_status_file = 1
innodb_io_capacity = 2000
transaction_isolation = READ-COMMITTED
innodb_flush_method = O_DIRECT
[mysqld_safe]
#malloc-lib=/usr/local/mysql/lib/jmalloc.so
nice=-19
open-files-limit=65535

mysql 5.6单实例下有两万多个库导致启动和备份都很慢,有加速的方法吗?

回复

MySQLyejr 回复了问题 • 2 人关注 • 1 个回复 • 86 次浏览 • 4 天前 • 来自相关话题

mysql-router

回复

MySQLA673 发起了问题 • 1 人关注 • 0 个回复 • 37 次浏览 • 5 天前 • 来自相关话题

数据库5.6升级到5.7后乱码

回复

MySQLzhagyilig 回复了问题 • 2 人关注 • 5 个回复 • 108 次浏览 • 5 天前 • 来自相关话题

kingshard这个中间件怎么样?

回复

MySQLFan 发起了问题 • 1 人关注 • 0 个回复 • 31 次浏览 • 2018-05-12 11:10 • 来自相关话题

一个SQL问题

回复

MySQLqidan3500 回复了问题 • 2 人关注 • 2 个回复 • 166 次浏览 • 2018-04-24 14:26 • 来自相关话题

关于fpmmm与pmm的监控数据不一致的问题

回复

MySQLarron 回复了问题 • 3 人关注 • 2 个回复 • 107 次浏览 • 2018-04-24 14:14 • 来自相关话题

MYSQL 8.0 认证变化

回复

MySQLwubx 回复了问题 • 1 人关注 • 1 个回复 • 112 次浏览 • 2018-04-23 17:01 • 来自相关话题

sql_mode 客户端软件登陆和本地登录值不同

回复

MySQLwubx 回复了问题 • 2 人关注 • 1 个回复 • 125 次浏览 • 2018-04-21 11:52 • 来自相关话题

关于jdbc 和java 连接池的问题

回复

MySQLzhiquan 回复了问题 • 3 人关注 • 2 个回复 • 401 次浏览 • 2018-04-08 13:30 • 来自相关话题

数字和字母编码问题

回复

MySQLlxzkenney 发起了问题 • 1 人关注 • 0 个回复 • 216 次浏览 • 2018-03-08 17:16 • 来自相关话题

pt-table-checksum检测不出主从差异处理

MySQLUest 发表了文章 • 0 个评论 • 82 次浏览 • 2018-05-10 10:18 • 来自相关话题

根据课堂上吴老师和各位同学提供的思路整理而成~
最近几版pt-table-checksum在binlog_format='row',且主从存在差异数据时,却检测不出主从差异。原因就是处理过程中主库没有SET binlog_format = 'STATEMENT',导致下面两个核心语句不是以statement格式记录,从库不会进行CRC32相关运算,主从永远一致~# 查看pt版本
[root@ZST2 ~]# /usr/local/bin/pt-table-checksum --version
pt-table-checksum 3.0.4
[root@ZST2 ~]#
# pt-table-checksum 3.0.4检测不出差异数据
[root@ZST2 ~]# /usr/local/bin/pt-table-checksum --nocheck-binlog-format --nocheck-replication-filters --recursion-method=hosts --replicate=replcrash.checksums --databases=replcrash --tables=py_user,py_user_innodb --host=192.168.85.132 --port=3306 --user=mydba --password=mysql5721
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
05-08T09:41:15 0 0 8 1 0 0.257 replcrash.py_user
05-08T09:41:16 0 0 67740 5 0 1.056 replcrash.py_user_innodb

# 两个核心语句
REPLACE INTO `replcrash`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'replcrash', 'py_user', '1', NULL, NULL, NULL, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `uid`, convert(`name` using utf8mb4), `add_time`, convert(`server_id` using utf8mb4), CONCAT(ISNULL(`name`), ISNULL(`add_time`), ISNULL(`server_id`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `replcrash`.`py_user` /*checksum table*/
UPDATE `replcrash`.`checksums` SET chunk_time = '0.004092', master_crc = '5abbd632', master_cnt = '8' WHERE db = 'replcrash' AND tbl = 'py_user' AND chunk = '1'方法一:修改代码# pt-table-checksum 3.0.4 只需注释掉第9335行和9364行,这层逻辑应该应用于任何情况下,因此不需要使用if判断
[root@ZST2 ~]# vim /usr/local/bin/pt-table-checksum
...
9335 #if ( $o->get('check-binlog-format') ) {
9336 # https://bugs.launchpad.net/per ... 19352
9337 # The tool shouldn't blindly attempt to change binlog_format;
9338 # instead, it should check if it's already set to STATEMENT.
9339 # This is becase starting with MySQL 5.1.29, changing the format
9340 # requires a SUPER user.
9341 if ( VersionParser->new($dbh) >= '5.1.5' ) {
9342 $sql = 'SELECT @@binlog_format';
9343 PTDEBUG && _d($dbh, $sql);
9344 my ($original_binlog_format) = $dbh->selectrow_array($sql);
9345 PTDEBUG && _d('Original binlog_format:', $original_binlog_format);
9346 if ( $original_binlog_format !~ /STATEMENT/i ) {
9347 $sql = q{/*!50108 SET @@binlog_format := 'STATEMENT'*/};
9348 eval {
9349 PTDEBUG && _d($dbh, $sql);
9350 $dbh->do($sql);
9351 };
9352 if ( $EVAL_ERROR ) {
9353 die "Failed to $sql: $EVAL_ERROR\n"
9354 . "This tool requires binlog_format=STATEMENT, "
9355 . "but the current binlog_format is set to "
9356 ."$original_binlog_format and an error occurred while "
9357 . "attempting to change it. If running MySQL 5.1.29 or newer, "
9358 . "setting binlog_format requires the SUPER privilege. "
9359 . "You will need to manually set binlog_format to 'STATEMENT' "
9360 . "before running this tool.\n";
9361 }
9362 }
9363 }
9364 #}
...

# 修改后检测出主从不一致
[root@ZST2 ~]# /usr/local/bin/pt-table-checksum --nocheck-binlog-format --nocheck-replication-filters --recursion-method=hosts --replicate=replcrash.checksums --databases=replcrash --tables=py_user,py_user_innodb --host=192.168.85.132 --port=3306 --user=mydba --password=mysql5721
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
05-08T09:51:32 0 1 8 1 0 0.030 replcrash.py_user
05-08T09:51:33 0 0 67740 5 0 0.561 replcrash.py_user_innodb
[root@ZST2 ~]# 方法二、命令行带上--set-vars参考
详细说明参考:pt-table-checksum not detecting diffs# pt-table-checksum 3.0.4不修改代码,带上--set-vars参数检测出差异数据
[root@ZST2 ~]# /usr/local/bin/pt-table-checksum --nocheck-binlog-format --nocheck-replication-filters --recursion-method=hosts --replicate=replcrash.checksums --databases=replcrash --tables=py_user,py_user_innodb --host=192.168.85.132 --port=3306 --user=mydba --password=mysql5721 --set-vars binlog_format='statement'
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
05-10T09:25:01 0 1 11 1 0 0.024 replcrash.py_user
05-10T09:25:01 0 0 67740 5 0 0.695 replcrash.py_user_innodb
[root@ZST2 ~]#善于思考,善于搜索,不要期盼别人把结果原封不动地送到嘴里~.~
吐槽一下,本来想将代码中的关键字加粗,编辑时显示正常,一发布就乱套了 查看全部
根据课堂上吴老师和各位同学提供的思路整理而成~
最近几版pt-table-checksum在binlog_format='row',且主从存在差异数据时,却检测不出主从差异。原因就是处理过程中主库没有SET binlog_format = 'STATEMENT',导致下面两个核心语句不是以statement格式记录,从库不会进行CRC32相关运算,主从永远一致~
# 查看pt版本
[root@ZST2 ~]# /usr/local/bin/pt-table-checksum --version
pt-table-checksum 3.0.4
[root@ZST2 ~]#
# pt-table-checksum 3.0.4检测不出差异数据
[root@ZST2 ~]# /usr/local/bin/pt-table-checksum --nocheck-binlog-format --nocheck-replication-filters --recursion-method=hosts --replicate=replcrash.checksums --databases=replcrash --tables=py_user,py_user_innodb --host=192.168.85.132 --port=3306 --user=mydba --password=mysql5721
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
05-08T09:41:15 0 0 8 1 0 0.257 replcrash.py_user
05-08T09:41:16 0 0 67740 5 0 1.056 replcrash.py_user_innodb

# 两个核心语句
REPLACE INTO `replcrash`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'replcrash', 'py_user', '1', NULL, NULL, NULL, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `uid`, convert(`name` using utf8mb4), `add_time`, convert(`server_id` using utf8mb4), CONCAT(ISNULL(`name`), ISNULL(`add_time`), ISNULL(`server_id`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `replcrash`.`py_user` /*checksum table*/
UPDATE `replcrash`.`checksums` SET chunk_time = '0.004092', master_crc = '5abbd632', master_cnt = '8' WHERE db = 'replcrash' AND tbl = 'py_user' AND chunk = '1'
方法一:修改代码
# pt-table-checksum 3.0.4 只需注释掉第9335行和9364行,这层逻辑应该应用于任何情况下,因此不需要使用if判断
[root@ZST2 ~]# vim /usr/local/bin/pt-table-checksum
...
9335 #if ( $o->get('check-binlog-format') ) {
9336 # https://bugs.launchpad.net/per ... 19352
9337 # The tool shouldn't blindly attempt to change binlog_format;
9338 # instead, it should check if it's already set to STATEMENT.
9339 # This is becase starting with MySQL 5.1.29, changing the format
9340 # requires a SUPER user.
9341 if ( VersionParser->new($dbh) >= '5.1.5' ) {
9342 $sql = 'SELECT @@binlog_format';
9343 PTDEBUG && _d($dbh, $sql);
9344 my ($original_binlog_format) = $dbh->selectrow_array($sql);
9345 PTDEBUG && _d('Original binlog_format:', $original_binlog_format);
9346 if ( $original_binlog_format !~ /STATEMENT/i ) {
9347 $sql = q{/*!50108 SET @@binlog_format := 'STATEMENT'*/};
9348 eval {
9349 PTDEBUG && _d($dbh, $sql);
9350 $dbh->do($sql);
9351 };
9352 if ( $EVAL_ERROR ) {
9353 die "Failed to $sql: $EVAL_ERROR\n"
9354 . "This tool requires binlog_format=STATEMENT, "
9355 . "but the current binlog_format is set to "
9356 ."$original_binlog_format and an error occurred while "
9357 . "attempting to change it. If running MySQL 5.1.29 or newer, "
9358 . "setting binlog_format requires the SUPER privilege. "
9359 . "You will need to manually set binlog_format to 'STATEMENT' "
9360 . "before running this tool.\n";
9361 }
9362 }
9363 }
9364 #}
...

# 修改后检测出主从不一致
[root@ZST2 ~]# /usr/local/bin/pt-table-checksum --nocheck-binlog-format --nocheck-replication-filters --recursion-method=hosts --replicate=replcrash.checksums --databases=replcrash --tables=py_user,py_user_innodb --host=192.168.85.132 --port=3306 --user=mydba --password=mysql5721
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
05-08T09:51:32 0 1 8 1 0 0.030 replcrash.py_user
05-08T09:51:33 0 0 67740 5 0 0.561 replcrash.py_user_innodb
[root@ZST2 ~]#
方法二、命令行带上--set-vars参考
详细说明参考:pt-table-checksum not detecting diffs
# pt-table-checksum 3.0.4不修改代码,带上--set-vars参数检测出差异数据
[root@ZST2 ~]# /usr/local/bin/pt-table-checksum --nocheck-binlog-format --nocheck-replication-filters --recursion-method=hosts --replicate=replcrash.checksums --databases=replcrash --tables=py_user,py_user_innodb --host=192.168.85.132 --port=3306 --user=mydba --password=mysql5721 --set-vars binlog_format='statement'
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
05-10T09:25:01 0 1 11 1 0 0.024 replcrash.py_user
05-10T09:25:01 0 0 67740 5 0 0.695 replcrash.py_user_innodb
[root@ZST2 ~]#
善于思考,善于搜索,不要期盼别人把结果原封不动地送到嘴里~.~
吐槽一下,本来想将代码中的关键字加粗,编辑时显示正常,一发布就乱套了

使用GTID复制的限制条件--手册章节翻译

MySQLliyh 发表了文章 • 2 个评论 • 224 次浏览 • 2018-03-14 10:52 • 来自相关话题

最近想把传统复制转移到GTID复制,翻看了一下官方手册的,做了简单翻译,有些地方翻译的很蹩脚,但是大体可以帮助理解下本章节。如有大神,请帮忙提供点意见,有的地方真的理解不上去。
 
原文:16.1.3.4 Restrictions on Replication with GTIDs

Because GTID-based replication is dependent on transactions, some features otherwise available in MySQL are not supported when using it. This section provides information about restrictions on and limitations of replication with GTIDs.

Updates involving nontransactional storage engines.  When using GTIDs, updates to tables using nontransactional storage engines such as MyISAM cannot be made in the same statement or transaction as updates to tables using transactional storage engines such as InnoDB.

This restriction is due to the fact that updates to tables that use a nontransactional storage engine mixed with updates to tables that use a transactional storage engine within the same transaction can result in multiple GTIDs being assigned to the same transaction.

Such problems can also occur when the master and the slave use different storage engines for their respective versions of the same table, where one storage engine is transactional and the other is not.

In any of the cases just mentioned, the one-to-one correspondence between transactions and GTIDs is broken, with the result that GTID-based replication cannot function correctly.

CREATE TABLE ... SELECT statements.  CREATE TABLE ... SELECT is not safe for statement-based replication. When using row-based replication, this statement is actually logged as two separate events—one for the creation of the table, and another for the insertion of rows from the source table into the new table just created. When this statement is executed within a transaction, it is possible in some cases for these two events to receive the same transaction identifier, which means that the transaction containing the inserts is skipped by the slave. Therefore, CREATE TABLE ... SELECT is not supported when using GTID-based replication.

Temporary tables.  CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE statements are not supported inside transactions when using GTIDs (that is, when the server was started with the --enforce-gtid-consistency option). It is possible to use these statements with GTIDs enabled, but only outside of any transaction, and only with autocommit=1.

Preventing execution of unsupported statements.  To prevent execution of statements that would cause GTID-based replication to fail, all servers must be started with the --enforce-gtid-consistency option when enabling GTIDs. This causes statements of any of the types discussed previously in this section to fail with an error.

Note that --enforce-gtid-consistency only takes effect if binary logging takes place for a statement. If binary logging is disabled on the server, or if statements are not written to the binary log because they are removed by a filter, GTID consistency is not checked or enforced for the statements that are not logged.

For information about other required startup options when enabling GTIDs, see Section 16.1.3.2, “Setting Up Replication Using GTIDs”.

sql_slave_skip_counter is not supported when using GTIDs. If you need to skip transactions, use the value of the master's gtid_executed variable instead; see Injecting empty transactions, for more information.

GTID mode and mysqldump.  It is possible to import a dump made using mysqldump into a MySQL Server running with GTID mode enabled, provided that there are no GTIDs in the target server's binary log.

GTID mode and mysql_upgrade.  When the server is running with global transaction identifiers (GTIDs) enabled (gtid_mode=ON), do not enable binary logging by mysql_upgrade (the --write-binlog option).
来源: https://dev.mysql.com/doc/refman/5.7/en/replication-gtids-restrictions.html 

译文:
由于基于GTID的复制是依赖于事物,另外一些特性在MySQL中允许而使用GTID就不支持了。本章节提供了使用GTID复制的限制条件和局限性的一些信息。
涉及非实物存储引擎的变更。当使用GTID时,对于非事务引擎表的更新,如MyISAM,不能与事物存储引擎表更新,如innodb ,在同一陈述(语句)或事物中。
这个限制是由于,在同一事物混合更新非事务存储引擎表和事务存储引擎表,会导致多个GTID被分配到同一个事务。
这个问题也会发生在,主库和从库同一张表分别使用不同的存储引擎的各自版本,一个存储引擎是事务的另一个不是。
上述任意一种情况,事务和GTID之间的一一对应关系都被破坏,从而导致基于GTID的复制不能正常运行。
create table...select 语句。create table ...select 在基于语句的复制,是不安全的。当使用基于行的复制,这个语句实际上被记录成两个独立的事件--一个是表的创建,另一个是(从原表到新建表)的行的插入。当这样的语句在事务里执行,两个事件会获得同一个事务标识,这就意味着包含插入语句的事务会被从库忽略。因此,create table...select .在基于GTID的复制中是不支持的。
临时表。create temporary table 和drop temporary table 语句在GTID 内部事务中是不支持的(当服务开启--enforce-gtid-consistency 选项时)。GTID开启时,可以使用这些语句,当只能是outside 任何事务,并且仅当autocommit=1时。
防止执行不支持的语句。为了防止执行导致基于GTID复制的失败的语句,当开启GTID时,所有的服务都要开启 --enforce-gtid-consistency 这个选项。这是导致(本章之前讨论的任一类型的)语句失败或者错误。
注意 --enforce-gtid-consistency 只在二进制日志开启的语句生效。如果服务禁止了二进制日志,或者语句被过滤器删除没有写入二进制日志,GTID一致性无法检查或者强制没有写入日志语句。

关于开启GTID时,其他开启选项要求信息,参考章节16.1.3.2 “使用GTID设置复制”


当使用GTID时,sql_slave_skip_counter 是不支持的。如果需要跳过事务,使用主库的gtid_executed变量值来替代。参考注入空事务,获取更多信息


GTID 模式和mysqldump。可以导入一个用mysqldump做的备份到开启GTID的MySQL服务中,目标服务没有GTID的二进制日志(就是说可以导入由mysqldump做的备份到开启GTID的MySQL中,但是这个MySQL日志中必须没有任何gtid事务,就是gtid事务为空)


GTID 模式和mysql_upgrad.当正在运行的服务开启全局事务标识(GTID)(gtid_mode=on),不要开启二进制日志mysql_upgrade(--write-binlog选项) 查看全部
最近想把传统复制转移到GTID复制,翻看了一下官方手册的,做了简单翻译,有些地方翻译的很蹩脚,但是大体可以帮助理解下本章节。如有大神,请帮忙提供点意见,有的地方真的理解不上去。
 
原文:16.1.3.4 Restrictions on Replication with GTIDs

Because GTID-based replication is dependent on transactions, some features otherwise available in MySQL are not supported when using it. This section provides information about restrictions on and limitations of replication with GTIDs.

Updates involving nontransactional storage engines.  When using GTIDs, updates to tables using nontransactional storage engines such as MyISAM cannot be made in the same statement or transaction as updates to tables using transactional storage engines such as InnoDB.

This restriction is due to the fact that updates to tables that use a nontransactional storage engine mixed with updates to tables that use a transactional storage engine within the same transaction can result in multiple GTIDs being assigned to the same transaction.

Such problems can also occur when the master and the slave use different storage engines for their respective versions of the same table, where one storage engine is transactional and the other is not.

In any of the cases just mentioned, the one-to-one correspondence between transactions and GTIDs is broken, with the result that GTID-based replication cannot function correctly.

CREATE TABLE ... SELECT statements.  CREATE TABLE ... SELECT is not safe for statement-based replication. When using row-based replication, this statement is actually logged as two separate events—one for the creation of the table, and another for the insertion of rows from the source table into the new table just created. When this statement is executed within a transaction, it is possible in some cases for these two events to receive the same transaction identifier, which means that the transaction containing the inserts is skipped by the slave. Therefore, CREATE TABLE ... SELECT is not supported when using GTID-based replication.

Temporary tables.  CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE statements are not supported inside transactions when using GTIDs (that is, when the server was started with the --enforce-gtid-consistency option). It is possible to use these statements with GTIDs enabled, but only outside of any transaction, and only with autocommit=1.

Preventing execution of unsupported statements.  To prevent execution of statements that would cause GTID-based replication to fail, all servers must be started with the --enforce-gtid-consistency option when enabling GTIDs. This causes statements of any of the types discussed previously in this section to fail with an error.

Note that --enforce-gtid-consistency only takes effect if binary logging takes place for a statement. If binary logging is disabled on the server, or if statements are not written to the binary log because they are removed by a filter, GTID consistency is not checked or enforced for the statements that are not logged.

For information about other required startup options when enabling GTIDs, see Section 16.1.3.2, “Setting Up Replication Using GTIDs”.

sql_slave_skip_counter is not supported when using GTIDs. If you need to skip transactions, use the value of the master's gtid_executed variable instead; see Injecting empty transactions, for more information.

GTID mode and mysqldump.  It is possible to import a dump made using mysqldump into a MySQL Server running with GTID mode enabled, provided that there are no GTIDs in the target server's binary log.

GTID mode and mysql_upgrade.  When the server is running with global transaction identifiers (GTIDs) enabled (gtid_mode=ON), do not enable binary logging by mysql_upgrade (the --write-binlog option).
来源: https://dev.mysql.com/doc/refman/5.7/en/replication-gtids-restrictions.html 

译文:
由于基于GTID的复制是依赖于事物,另外一些特性在MySQL中允许而使用GTID就不支持了。本章节提供了使用GTID复制的限制条件和局限性的一些信息。
涉及非实物存储引擎的变更。当使用GTID时,对于非事务引擎表的更新,如MyISAM,不能与事物存储引擎表更新,如innodb ,在同一陈述(语句)或事物中。
这个限制是由于,在同一事物混合更新非事务存储引擎表和事务存储引擎表,会导致多个GTID被分配到同一个事务。
这个问题也会发生在,主库和从库同一张表分别使用不同的存储引擎的各自版本,一个存储引擎是事务的另一个不是。
上述任意一种情况,事务和GTID之间的一一对应关系都被破坏,从而导致基于GTID的复制不能正常运行。
create table...select 语句。create table ...select 在基于语句的复制,是不安全的。当使用基于行的复制,这个语句实际上被记录成两个独立的事件--一个是表的创建,另一个是(从原表到新建表)的行的插入。当这样的语句在事务里执行,两个事件会获得同一个事务标识,这就意味着包含插入语句的事务会被从库忽略。因此,create table...select .在基于GTID的复制中是不支持的。
临时表。create temporary table 和drop temporary table 语句在GTID 内部事务中是不支持的(当服务开启--enforce-gtid-consistency 选项时)。GTID开启时,可以使用这些语句,当只能是outside 任何事务,并且仅当autocommit=1时。
防止执行不支持的语句。为了防止执行导致基于GTID复制的失败的语句,当开启GTID时,所有的服务都要开启 --enforce-gtid-consistency 这个选项。这是导致(本章之前讨论的任一类型的)语句失败或者错误。
注意 --enforce-gtid-consistency 只在二进制日志开启的语句生效。如果服务禁止了二进制日志,或者语句被过滤器删除没有写入二进制日志,GTID一致性无法检查或者强制没有写入日志语句。

关于开启GTID时,其他开启选项要求信息,参考章节16.1.3.2 “使用GTID设置复制”


当使用GTID时,sql_slave_skip_counter 是不支持的。如果需要跳过事务,使用主库的gtid_executed变量值来替代。参考注入空事务,获取更多信息


GTID 模式和mysqldump。可以导入一个用mysqldump做的备份到开启GTID的MySQL服务中,目标服务没有GTID的二进制日志(就是说可以导入由mysqldump做的备份到开启GTID的MySQL中,但是这个MySQL日志中必须没有任何gtid事务,就是gtid事务为空)


GTID 模式和mysql_upgrad.当正在运行的服务开启全局事务标识(GTID)(gtid_mode=on),不要开启二进制日志mysql_upgrade(--write-binlog选项)

python实现mysql ping 获取c到s端网络延时探讨

pythonAionsky 发表了文章 • 0 个评论 • 288 次浏览 • 2018-02-05 02:14 • 来自相关话题

#!/usr/bin/env python # -*- coding:utf-8 -*-
import mysql.connector 
import time 
''' 
目的:监控app层穿透proxy层访问数据层的延时。
该脚本主要实现,mysql client 到 mysql server 的网络延时监控。
select 1 有查询消耗时间,在压力大的情况下并不精确; 
ping 本身为用于诊断 server是否存活;
两种方法都是利用 发起命令前后 时间差 得出网络延时; 
两种检测方法,可混合使用,通过两个指标差值计算 或单一指标的步差值 再算一定时段均值,可较为合理的反映出MySQL通讯网络是否正常。 
该脚本只实现功能,未做进一步优化。 
单向延时*500,双向延时*1000,不知道这个理解对不对。 
不知道整个MySQL ping 的思路是不是对的。
'''
config = { 
 'host': '127.0.0.1', 
 'user': 'root',
 'password': 'root', 
 'port': 3306, 
 'database': 'test', 
 'charset': 'utf8' } 
try: 
 cnn = mysql.connector.connect(**config) 
except mysql.connector.Error as e: 
 print('connect fails!{}'.format(e)) cursor = cnn.cursor() 
try: 
 #方法一,使用select 语句进行查询,包含查询消耗时间,当数据库压力大时,查询时间可能会变长,可能因为本身数据库压力大导致,但可以变相反应出数据库当前压力。 
 sql_query = 'select 1;' 
 #命令发起前获取时间戳 
 time1 = time.time() 
 cursor.execute(sql_query) 
 result_1 = cursor.fetchall() 
 print result_1 
 #方法2,使用 ping,mysql client发起一次对server ping 请求。本身用于探测server是否正常。
 cnn.ping(cnn) 
 #命令执行完毕后获取时间戳 
 time2 = time.time() 
 #通过发起命令前后来的时间差值来,来确定网络延时。单向延时*500,双向延时*1000 
 time_result = str(float((time2 - time1))*500) + "ms" 
 print time_result 
 
except mysql.connector.Error as e: 
 print('query error!{}'.format(e)) 
finally: 
 cursor.close() 
 cnn.close() 查看全部
#!/usr/bin/env python # -*- coding:utf-8 -*-
import mysql.connector 
import time 
''' 
目的:监控app层穿透proxy层访问数据层的延时。
该脚本主要实现,mysql client 到 mysql server 的网络延时监控。
select 1 有查询消耗时间,在压力大的情况下并不精确; 
ping 本身为用于诊断 server是否存活;
两种方法都是利用 发起命令前后 时间差 得出网络延时; 
两种检测方法,可混合使用,通过两个指标差值计算 或单一指标的步差值 再算一定时段均值,可较为合理的反映出MySQL通讯网络是否正常。 
该脚本只实现功能,未做进一步优化。 
单向延时*500,双向延时*1000,不知道这个理解对不对。 
不知道整个MySQL ping 的思路是不是对的。
'''
config = { 
 'host': '127.0.0.1', 
 'user': 'root',
 'password': 'root', 
 'port': 3306, 
 'database': 'test', 
 'charset': 'utf8' } 
try: 
 cnn = mysql.connector.connect(**config) 
except mysql.connector.Error as e: 
 print('connect fails!{}'.format(e)) cursor = cnn.cursor() 
try: 
 #方法一,使用select 语句进行查询,包含查询消耗时间,当数据库压力大时,查询时间可能会变长,可能因为本身数据库压力大导致,但可以变相反应出数据库当前压力。 
 sql_query = 'select 1;' 
 #命令发起前获取时间戳 
 time1 = time.time() 
 cursor.execute(sql_query) 
 result_1 = cursor.fetchall() 
 print result_1 
 #方法2,使用 ping,mysql client发起一次对server ping 请求。本身用于探测server是否正常。
 cnn.ping(cnn) 
 #命令执行完毕后获取时间戳 
 time2 = time.time() 
 #通过发起命令前后来的时间差值来,来确定网络延时。单向延时*500,双向延时*1000 
 time_result = str(float((time2 - time1))*500) + "ms" 
 print time_result 
 
except mysql.connector.Error as e: 
 print('query error!{}'.format(e)) 
finally: 
 cursor.close() 
 cnn.close()

proxysql 配置疑问

MySQLhuganggang 发表了文章 • 5 个评论 • 360 次浏览 • 2018-01-10 11:25 • 来自相关话题

1.环境
192.168.1.100  主库
192.168.1.101  从库
192.168.1.102  proxysql

2.软件
mysql5.7.19
proxysql1.4.4

3.安装
下载:wget   https://github.com/sysown/prox ... 4.rpm
安装依赖:yum  install   perl-DBD-MySQL
安装proxysql:rpm  -ivh  proxysql-1.4.4-1-centos67.x86_64.rpm 
启动:service proxysql start
mysql安装、主从配置略

4.创建数据库账号
在主库创建数据库账号

业务账号
CREATE USER 'proxytest'@'%' IDENTIFIED BY 'proxytest';
GRANT USAGE ON  *.* TO 'proxytest'@'%';
监控账号
CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor';
GRANT ALL ON * . * TO 'monitor'@'%';
FLUSH PRIVILEGES;

5.配置
连接管理端口
mysql -u admin -padmin -h 127.0.0.1 -P6032

查看有没有规则,有规则delete规则,重新配置
SELECT * FROM mysql_servers;
SELECT * from mysql_replication_hostgroups;
SELECT * from mysql_query_rules;
SELECT * FROM mysql_users;

添加server和user
insert into  mysql_servers(hostgroup_id,hostname,port,max_connections)  values(0,'192.168.1.100',3307,10),(1,'192.168.1.101',3307,10);
insert into  mysql_users(username,password,active,default_hostgroup,default_schema)  values('proxytest','proxytest',1,0,'test');
注意:  mysql_users表中active要为1,否则连接不上。还有default_hostgroup 要设置为正确的值,这里设置为0,即主库,与mysql_servers中的hostgroup_id对应。
load  mysql  servers  to  runtime;
save  mysql servers  to  disk ;
load  mysql  users  to  runtime;
save  mysql users  to  disk ;

配置监控
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password';
load  mysql  variables   to   runtime   ;
save  mysql   variables  to  disk   ;

读写分离配置
这里我们将所有除了SELECT.*FOR UPDATE的select全部发送到slave,其他的的语句发送到master。

INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT.*FOR UPDATE$',0,1);
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT',1,1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

6.测试
连上proxysql 6033进行测试
mysql -uproxytest -pproxytest -P6033
创建测试表:
CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
   `name` varchar(20) DEFAULT NULL
   );

进行DML 、DDL测试操作。

我测了没有问题,这里疑问:
 1.我看官方文档proxysql 配置读写分离没有用到mysql_replication_hostgroups 表, 
https://github.com/sysown/proxysql/wiki/ProxySQL-Read-Write-Split-(HOWTO). 
 什么情况下用到?
2.mysql_query_rules规则里面如何写不区分大小写的规则,比如大写SELECT和小写
 select 会匹配一条规则。
3. proxysql高可用就是多个proxysql前面配个keepalived ? 查看全部
1.环境
192.168.1.100  主库
192.168.1.101  从库
192.168.1.102  proxysql

2.软件
mysql5.7.19
proxysql1.4.4

3.安装
下载:wget   https://github.com/sysown/prox ... 4.rpm
安装依赖:yum  install   perl-DBD-MySQL
安装proxysql:rpm  -ivh  proxysql-1.4.4-1-centos67.x86_64.rpm 
启动:service proxysql start
mysql安装、主从配置略

4.创建数据库账号
在主库创建数据库账号

业务账号
CREATE USER 'proxytest'@'%' IDENTIFIED BY 'proxytest';
GRANT USAGE ON  *.* TO 'proxytest'@'%';
监控账号
CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor';
GRANT ALL ON * . * TO 'monitor'@'%';
FLUSH PRIVILEGES;

5.配置
连接管理端口
mysql -u admin -padmin -h 127.0.0.1 -P6032

查看有没有规则,有规则delete规则,重新配置
SELECT * FROM mysql_servers;
SELECT * from mysql_replication_hostgroups;
SELECT * from mysql_query_rules;
SELECT * FROM mysql_users;

添加server和user
insert into  mysql_servers(hostgroup_id,hostname,port,max_connections)  values(0,'192.168.1.100',3307,10),(1,'192.168.1.101',3307,10);
insert into  mysql_users(username,password,active,default_hostgroup,default_schema)  values('proxytest','proxytest',1,0,'test');
注意:  mysql_users表中active要为1,否则连接不上。还有default_hostgroup 要设置为正确的值,这里设置为0,即主库,与mysql_servers中的hostgroup_id对应。
load  mysql  servers  to  runtime;
save  mysql servers  to  disk ;
load  mysql  users  to  runtime;
save  mysql users  to  disk ;

配置监控
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password';
load  mysql  variables   to   runtime   ;
save  mysql   variables  to  disk   ;

读写分离配置
这里我们将所有除了SELECT.*FOR UPDATE的select全部发送到slave,其他的的语句发送到master。

INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT.*FOR UPDATE$',0,1);
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT',1,1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

6.测试
连上proxysql 6033进行测试
mysql -uproxytest -pproxytest -P6033
创建测试表:
CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
   `name` varchar(20) DEFAULT NULL
   );

进行DML 、DDL测试操作。

我测了没有问题,这里疑问:
 1.我看官方文档proxysql 配置读写分离没有用到mysql_replication_hostgroups 表, 
https://github.com/sysown/proxysql/wiki/ProxySQL-Read-Write-Split-(HOWTO). 
 什么情况下用到?
2.mysql_query_rules规则里面如何写不区分大小写的规则,比如大写SELECT和小写
 select 会匹配一条规则。
3. proxysql高可用就是多个proxysql前面配个keepalived ?

PCI-E盘上tokudb可能起动失败处理办法

MySQLwubx 发表了文章 • 0 个评论 • 223 次浏览 • 2017-12-19 16:02 • 来自相关话题

错误提示:2017-12-12T13:54:28.540109Z 0 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.7.19-17 started; log sequence number 0
2017-12-12T13:54:28.540671Z 0 [Note] Plugin 'FEDERATED' is disabled.
mysqld: /home/software/toku/tokudb/storage/tokudb/PerconaFT/portability/file.cc:137: void try_again_after_handling_write_error(int, size_t, ssize_t): Assertion `try_again' failed.
13:54:28 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.
Please help us make Percona Server better by reporting any
bugs at http://bugs.percona.com/ 
 
处理办法:
禁用tokudb的directio
 
[mysqld]
loose_tokudb_directio=off 查看全部
错误提示:2017-12-12T13:54:28.540109Z 0 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.7.19-17 started; log sequence number 0
2017-12-12T13:54:28.540671Z 0 [Note] Plugin 'FEDERATED' is disabled.
mysqld: /home/software/toku/tokudb/storage/tokudb/PerconaFT/portability/file.cc:137: void try_again_after_handling_write_error(int, size_t, ssize_t): Assertion `try_again' failed.
13:54:28 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.
Please help us make Percona Server better by reporting any
bugs at http://bugs.percona.com/ 
 
处理办法:
禁用tokudb的directio
 
[mysqld]
loose_tokudb_directio=off

XeLabs TokuDB 5.7.20 Release

MySQLwubx 发表了文章 • 0 个评论 • 243 次浏览 • 2017-12-19 15:06 • 来自相关话题

恭喜XeLabs TokuDB 5.7.20发布,该版本基本地Percona版本TokuDB进行优化。基于Percona 5.7.20发版。

对TokuDB提升如下

Percona Server has implemented TokuDB integration with PERFORMANCE_SCHEMA.
关于添加有默认值的日期类型字段,主从同步的问题(主:InnoDB,从:TokuDB) http://q.fireflyclub.org/?/question/47
加入show engine中XeLabs TokuDB 标识
mysql –version 加入XeLabs 标识(本次只在CentOS7的版本标识)

二进制下址地址: https://pan.baidu.com/s/1qYRyH3I

20171219 补:

提供于基于xtrabackup 2.4.9版本的支持在线热备XeLabs TokuDB, AiSQL TokuDB (不支持Percona TokuDB)工具。 下载地址,目前也用百度云盘共享。

二进制下址地址: https://pan.baidu.com/s/1qYRyH3I

更多问题加入QQ群:579036588 联系群主。 查看全部
恭喜XeLabs TokuDB 5.7.20发布,该版本基本地Percona版本TokuDB进行优化。基于Percona 5.7.20发版。

对TokuDB提升如下

Percona Server has implemented TokuDB integration with PERFORMANCE_SCHEMA.
关于添加有默认值的日期类型字段,主从同步的问题(主:InnoDB,从:TokuDB) http://q.fireflyclub.org/?/question/47
加入show engine中XeLabs TokuDB 标识
mysql –version 加入XeLabs 标识(本次只在CentOS7的版本标识)

二进制下址地址: https://pan.baidu.com/s/1qYRyH3I

20171219 补:

提供于基于xtrabackup 2.4.9版本的支持在线热备XeLabs TokuDB, AiSQL TokuDB (不支持Percona TokuDB)工具。 下载地址,目前也用百度云盘共享。

二进制下址地址: https://pan.baidu.com/s/1qYRyH3I

更多问题加入QQ群:579036588 联系群主。

python写的binlog server脚本

MySQLFan 发表了文章 • 1 个评论 • 246 次浏览 • 2017-12-08 23:53 • 来自相关话题

Binlog_Server
首先
pip install docopt==0.6.2 pymysql filelock
然后你应该使用 -h 查看帮助信息同步binlog方法
两种方式
1.通过配置文件启动[code]nohup python /scripts/binlog_server.py --config=/tmp/binlog_server.cnf --dbname=GN_PT_SLAVE1 --last-file=mysql-bin.00001 &[/code]2.命令行指定
注意backup-dir一定要以’/‘结尾[code]nohup python binlog_server.py --user=binlog_backup --password=xxxx --host=xxxx --port=3306 --backup-dir=/data4/binlog_backup/ --log=/data4/binlog_backup/BB.log --last-file=mysql-bin.00001 &
[/code]
使用—last-file手动指定binlog server起始文件. 如果指定则默认会去找backup-dir下最后一个作为起点
在脚本中 为了防止重复运行,启动binlog server时会创建了/tmp/IP__binlog_server.lock 文件.
如果有需要停止,需要手动kill binlog_server.py 和 mysqlbinlog, 并且删除/tmp/IP__binlog_server.lock 文件,不然下次起不来生成配置文件和启动脚本方法
当你需要部署几十上百个binlog server时,就需要用到make-config命令了.
首先创建一个包含csv文件包含服务器信息[code][root@cn_mu_binlog_backup scripts]# less android.csv
"40001","10.241.3.xxx","3306","loguser","youpassword","/data/app1_binlog_backup/android/40001/","40001.log","40001"
"40002","10.241.3.xxx","3306","loguser","youpassword","/data/app1_binlog_backup/android/40002/","40002.log","40002"
"40003","10.241.3.xxx","3306","loguser","youpassword","/data/app1_binlog_backup/android/40003/","40003.log","40003"
"40004","10.241.3.xxx","3306","loguser","youpassword","/data/app1_binlog_backup/android/40004/","40004.log","40004"
[/code]
第一列对于配置文件中的section,如果为空(“”)则为ip地址
2,3,4,5无需解释
第六列为binlog存储路径,如果目录不存在则会自动创建, 主要以’/‘结尾(虽然我好像在脚本中加了判断)
第七列为日志名称,默认存储在备份目录下
第八列为 mysqlbinlog的 —stop-never-slave-server-id 参数值, 可以为空(“”),为空则不指定该参数
然后使用如下命令生成配置文件[code]python /scripts/binlog_server.py make-config --info-file=app1.csv --config-file=app1.cnf
[/code]
如果 app1.cnf 已经存在,则需要你手动清空他或删除,我没有在代码里做处理
命令执行成功后会在当前目录生成下列文件[code]# ls | grep android
android.cnf
bootstrap_android.sh
crontab_android.sh
[/code]
内容如下[code][root@cn_mu_binlog_backup scripts]# head -10 android.cnf
[40001]
db_host = 10.241.0.999
db_port = 3306
db_user = loguser
db_passwd = xxx
backup_dir = /data/app1_binlog_backup/android/40001/10.241.0.999/
log = /data/app1_binlog_backup/android/40001/10.241.0.999/40001.log
server_id = 40001

[40002]
[root@cn_mu_binlog_backup scripts]# head -10 bootstrap_android.sh --如果没指定--start-from,则默认--last-file=mysql-bin.000001
nohup python /scripts/binlog_server.py --config=/scripts/android.cnf --dbname=40001 --last-file=mysql-bin.000001 &
nohup python /scripts/binlog_server.py --config=/scripts/android.cnf --dbname=40002 --last-file=mysql-bin.000001 &
nohup python /scripts/binlog_server.py --config=/scripts/android.cnf --dbname=40003 --last-file=mysql-bin.000001 &
nohup python /scripts/binlog_server.py --config=/scripts/android.cnf --dbname=40004 --last-file=mysql-bin.000001 &
nohup python /scripts/binlog_server.py --config=/scripts/android.cnf --dbname=40005 --last-file=mysql-bin.000001 &
nohup python /scripts/binlog_server.py --config=/scripts/android.cnf --dbname=40006 --last-file=mysql-bin.000001 &
nohup python /scripts/binlog_server.py --config=/scripts/android.cnf --dbname=40007 --last-file=mysql-bin.000001 &
nohup python /scripts/binlog_server.py --config=/scripts/android.cnf --dbname=40008 --last-file=mysql-bin.000001 &
nohup python /scripts/binlog_server.py --config=/scripts/android.cnf --dbname=40009 --last-file=mysql-bin.000001 &
nohup python /scripts/binlog_server.py --config=/scripts/android.cnf --dbname=40010 --last-file=mysql-bin.000001 &
[root@cn_mu_binlog_backup scripts]# head -10 crontab_android.sh
*/5 * * * * sh /scripts/mon_binlog_server.sh 40001 10.241.0.xxx >> /scripts/mon_40001_binserver.log 2>&1
*/5 * * * * sh /scripts/mon_binlog_server.sh 40002 10.241.0.xxx >> /scripts/mon_40002_binserver.log 2>&1
*/5 * * * * sh /scripts/mon_binlog_server.sh 40003 10.241.0.xxx >> /scripts/mon_40003_binserver.log 2>&1
*/5 * * * * sh /scripts/mon_binlog_server.sh 40004 10.241.0.xxx >> /scripts/mon_40004_binserver.log 2>&1
*/5 * * * * sh /scripts/mon_binlog_server.sh 40005 10.241.0.xxx >> /scripts/mon_40005_binserver.log 2>&1
*/5 * * * * sh /scripts/mon_binlog_server.sh 40006 10.241.0.xxx >> /scripts/mon_40006_binserver.log 2>&1
*/5 * * * * sh /scripts/mon_binlog_server.sh 40007 10.241.0.xxx >> /scripts/mon_40007_binserver.log 2>&1
*/5 * * * * sh /scripts/mon_binlog_server.sh 40008 10.241.0.xxx >> /scripts/mon_40008_binserver.log 2>&1
*/5 * * * * sh /scripts/mon_binlog_server.sh 40009 10.241.0.xxx >> /scripts/mon_40009_binserver.log 2>&1
*/5 * * * * sh /scripts/mon_binlog_server.sh 40010 10.241.0.xxx >> /scripts/mon_40010_binserver.log 2>&1
[/code]
接下来你就可以使用
sh bootstrap_android.sh 来启动binlog server
crontab_android.sh 中为可以添加到crontab的定时监控任务可以监控各个binlog server运行状态 (里面会用到mutt,自己装)配置文件示例[code][root@localhost 120.27.143.36]# less /scripts/binlog_server.cnf
[GN_PT_SLAVE1]
db_host=120.27.136.888
db_port=3306
db_user=binlog_backup
db_passwd=xxxx
backup_dir=/data1/backup/db_backup/120.27.136.888/ --注意一定要以/结尾
log=/data1/backup/db_backup/120.27.136.888/BB.log
server_id=

[GN_LOG_MASTER2]
db_host=120.27.143.999
db_port=3306
db_user=binlog_backup
db_passwd=xxxx
backup_dir=/data2/backup/db_backup/120.27.143.999/ --注意一定要以/结尾
log=/data2/backup/db_backup/120.27.143.999/BB.log
server_id=191037
[/code]
查看全部
Binlog_Server
首先
pip install docopt==0.6.2 pymysql filelock

然后你应该使用 -h 查看帮助信息同步binlog方法
两种方式
  • 1.通过配置文件启动
    [code]nohup python /scripts/binlog_server.py --config=/tmp/binlog_server.cnf --dbname=GN_PT_SLAVE1 --last-file=mysql-bin.00001 &
    [/code]
  • 2.命令行指定

注意backup-dir一定要以’/‘结尾
[code]nohup python binlog_server.py --user=binlog_backup --password=xxxx --host=xxxx --port=3306 --backup-dir=/data4/binlog_backup/ --log=/data4/binlog_backup/BB.log --last-file=mysql-bin.00001 &
[/code]
使用—last-file手动指定binlog server起始文件. 如果指定则默认会去找backup-dir下最后一个作为起点
在脚本中 为了防止重复运行,启动binlog server时会创建了/tmp/IP__binlog_server.lock 文件.
如果有需要停止,需要手动kill binlog_server.py 和 mysqlbinlog, 并且删除/tmp/IP__binlog_server.lock 文件,不然下次起不来生成配置文件和启动脚本方法
当你需要部署几十上百个binlog server时,就需要用到make-config命令了.
首先创建一个包含csv文件包含服务器信息
[code][root@cn_mu_binlog_backup scripts]# less android.csv 
"40001","10.241.3.xxx","3306","loguser","youpassword","/data/app1_binlog_backup/android/40001/","40001.log","40001"
"40002","10.241.3.xxx","3306","loguser","youpassword","/data/app1_binlog_backup/android/40002/","40002.log","40002"
"40003","10.241.3.xxx","3306","loguser","youpassword","/data/app1_binlog_backup/android/40003/","40003.log","40003"
"40004","10.241.3.xxx","3306","loguser","youpassword","/data/app1_binlog_backup/android/40004/","40004.log","40004"
[/code]
第一列对于配置文件中的section,如果为空(“”)则为ip地址
2,3,4,5无需解释
第六列为binlog存储路径,如果目录不存在则会自动创建, 主要以’/‘结尾(虽然我好像在脚本中加了判断)
第七列为日志名称,默认存储在备份目录下
第八列为 mysqlbinlog的 —stop-never-slave-server-id 参数值, 可以为空(“”),为空则不指定该参数
然后使用如下命令生成配置文件
[code]python /scripts/binlog_server.py make-config --info-file=app1.csv --config-file=app1.cnf
[/code]
如果 app1.cnf 已经存在,则需要你手动清空他或删除,我没有在代码里做处理
命令执行成功后会在当前目录生成下列文件
[code]# ls | grep android
android.cnf
bootstrap_android.sh
crontab_android.sh
[/code]
内容如下
[code][root@cn_mu_binlog_backup scripts]# head -10 android.cnf 
[40001]
db_host = 10.241.0.999
db_port = 3306
db_user = loguser
db_passwd = xxx
backup_dir = /data/app1_binlog_backup/android/40001/10.241.0.999/
log = /data/app1_binlog_backup/android/40001/10.241.0.999/40001.log
server_id = 40001

[40002]
[root@cn_mu_binlog_backup scripts]# head -10 bootstrap_android.sh --如果没指定--start-from,则默认--last-file=mysql-bin.000001
nohup python /scripts/binlog_server.py --config=/scripts/android.cnf --dbname=40001 --last-file=mysql-bin.000001 &
nohup python /scripts/binlog_server.py --config=/scripts/android.cnf --dbname=40002 --last-file=mysql-bin.000001 &
nohup python /scripts/binlog_server.py --config=/scripts/android.cnf --dbname=40003 --last-file=mysql-bin.000001 &
nohup python /scripts/binlog_server.py --config=/scripts/android.cnf --dbname=40004 --last-file=mysql-bin.000001 &
nohup python /scripts/binlog_server.py --config=/scripts/android.cnf --dbname=40005 --last-file=mysql-bin.000001 &
nohup python /scripts/binlog_server.py --config=/scripts/android.cnf --dbname=40006 --last-file=mysql-bin.000001 &
nohup python /scripts/binlog_server.py --config=/scripts/android.cnf --dbname=40007 --last-file=mysql-bin.000001 &
nohup python /scripts/binlog_server.py --config=/scripts/android.cnf --dbname=40008 --last-file=mysql-bin.000001 &
nohup python /scripts/binlog_server.py --config=/scripts/android.cnf --dbname=40009 --last-file=mysql-bin.000001 &
nohup python /scripts/binlog_server.py --config=/scripts/android.cnf --dbname=40010 --last-file=mysql-bin.000001 &
[root@cn_mu_binlog_backup scripts]# head -10 crontab_android.sh
*/5 * * * * sh /scripts/mon_binlog_server.sh 40001 10.241.0.xxx >> /scripts/mon_40001_binserver.log 2>&1
*/5 * * * * sh /scripts/mon_binlog_server.sh 40002 10.241.0.xxx >> /scripts/mon_40002_binserver.log 2>&1
*/5 * * * * sh /scripts/mon_binlog_server.sh 40003 10.241.0.xxx >> /scripts/mon_40003_binserver.log 2>&1
*/5 * * * * sh /scripts/mon_binlog_server.sh 40004 10.241.0.xxx >> /scripts/mon_40004_binserver.log 2>&1
*/5 * * * * sh /scripts/mon_binlog_server.sh 40005 10.241.0.xxx >> /scripts/mon_40005_binserver.log 2>&1
*/5 * * * * sh /scripts/mon_binlog_server.sh 40006 10.241.0.xxx >> /scripts/mon_40006_binserver.log 2>&1
*/5 * * * * sh /scripts/mon_binlog_server.sh 40007 10.241.0.xxx >> /scripts/mon_40007_binserver.log 2>&1
*/5 * * * * sh /scripts/mon_binlog_server.sh 40008 10.241.0.xxx >> /scripts/mon_40008_binserver.log 2>&1
*/5 * * * * sh /scripts/mon_binlog_server.sh 40009 10.241.0.xxx >> /scripts/mon_40009_binserver.log 2>&1
*/5 * * * * sh /scripts/mon_binlog_server.sh 40010 10.241.0.xxx >> /scripts/mon_40010_binserver.log 2>&1
[/code]
接下来你就可以使用
sh bootstrap_android.sh 来启动binlog server
crontab_android.sh 中为可以添加到crontab的定时监控任务可以监控各个binlog server运行状态 (里面会用到mutt,自己装)配置文件示例
[code][root@localhost 120.27.143.36]# less /scripts/binlog_server.cnf 
[GN_PT_SLAVE1]
db_host=120.27.136.888
db_port=3306
db_user=binlog_backup
db_passwd=xxxx
backup_dir=/data1/backup/db_backup/120.27.136.888/ --注意一定要以/结尾
log=/data1/backup/db_backup/120.27.136.888/BB.log
server_id=

[GN_LOG_MASTER2]
db_host=120.27.143.999
db_port=3306
db_user=binlog_backup
db_passwd=xxxx
backup_dir=/data2/backup/db_backup/120.27.143.999/ --注意一定要以/结尾
log=/data2/backup/db_backup/120.27.143.999/BB.log
server_id=191037
[/code]

pybackup(名字起的不好)备份脚本

MySQLFan 发表了文章 • 0 个评论 • 268 次浏览 • 2017-11-24 11:37 • 来自相关话题

pybackup源自于对线上备份脚本的改进和对备份情况的监控需求.
原本生产库的备份是通过shell脚本调用mydumper,之后再将备份通过rsync传输到备份机.
想要获取备份状态,时间,rsync传输时间等信息只能通过解析日志.
pybackup由python编写,调用mydumper和rsync,将备份信息存入数据库中,后期可以通过grafana图形化展示和监控备份
目前不支持2.6,仅在2.7.14做过测试
 
配合grafana做备份状态监控和告警





 
github地址:
https://github.com/Fanduzi/pybackup
 
python水平比较渣,不是谦虚,真心渣. 恳请各位提出宝贵意见 查看全部
pybackup源自于对线上备份脚本的改进和对备份情况的监控需求.
原本生产库的备份是通过shell脚本调用mydumper,之后再将备份通过rsync传输到备份机.
想要获取备份状态,时间,rsync传输时间等信息只能通过解析日志.
pybackup由python编写,调用mydumper和rsync,将备份信息存入数据库中,后期可以通过grafana图形化展示和监控备份
目前不支持2.6,仅在2.7.14做过测试
 
配合grafana做备份状态监控和告警

WechatIMG160.jpeg

 
github地址:
https://github.com/Fanduzi/pybackup
 
python水平比较渣,不是谦虚,真心渣. 恳请各位提出宝贵意见

mysql 初始化实例报错 Table 'mysql.plugin' doesn't exist

MySQLweixp 发表了文章 • 0 个评论 • 375 次浏览 • 2017-08-17 17:18 • 来自相关话题

初始化的时候抱着错,可以好好看看初始化的命令,可能是初始化命令写错了。
我就是这样的。
/usr/local/mysql/bin/mysqld --defaults-file=my.cnf --initialize--insecure 仔细看看
--initialize--insecure 这个地方多写了一个杠 查看全部
初始化的时候抱着错,可以好好看看初始化的命令,可能是初始化命令写错了。
我就是这样的。
/usr/local/mysql/bin/mysqld --defaults-file=my.cnf --initialize--insecure 仔细看看
--initialize--insecure 这个地方多写了一个杠

MySQL多实例管理 for python脚本

pythonA128_huanggr 发表了文章 • 0 个评论 • 482 次浏览 • 2017-07-27 15:38 • 来自相关话题

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

#注:多实例DB数据,my.conf,sock文件目录要统一
#每个实例要建有shutdown权限mt_user用户

import os,sys
import socket

myd = '/usr/local/mysql/bin/mysqld'
myadmin = '/usr/local/mysql/bin/mysqladmin'
m_user = 'mt_user'
m_password = 'password'

def IsOpen(ip,port):
    s = socket.socket(socket.AF_INET,socket.SOCK_STREAM)
    try:
        s.connect((ip,int(port)))
        s.shutdown(2)
        return True
    except:
        return False

result = IsOpen('127.0.0.1',sys.argv[2])

if sys.argv[1] == 'start':
    if result:
        print('Sorry that the port is occupied!')
    else:
        os.system(myd+ ' --defaults-file=/data/mysql/mysql'+sys.argv[2]+'/my.cnf&')
elif sys.argv[1] == 'restart':
    os.system('%s -u%s -p%s -S /tmp/mysql%s.sock shutdown' %(myadmin,m_user,m_password,sys.argv[2]))
    os.system('sleep 2')
    os.system(myd+ ' --defaults-file=/data/mysql/mysql'+sys.argv[2]+'/my.cnf&')
elif sys.argv[1] == 'stop':
    os.system('%s -u%s -p%s -S /tmp/mysql%s.sock shutdown' %(myadmin,m_user,m_password,sys.argv[2]))
else:
    print('Please enter ===python %s start+port or restart+port or stop+port===' %sys.argv[0])
 
 
#python mysql.py start 3306
#python mysql.py start 3307
 
#python mysql.py restart 3306
#python mysql.py restart 3307

#python mysql.py stop 3306
#python mysql.py stop 3307 查看全部
#!/usr/bin/env python
#! _*_ coding:utf-8 _*_

#注:多实例DB数据,my.conf,sock文件目录要统一
#每个实例要建有shutdown权限mt_user用户

import os,sys
import socket

myd = '/usr/local/mysql/bin/mysqld'
myadmin = '/usr/local/mysql/bin/mysqladmin'
m_user = 'mt_user'
m_password = 'password'

def IsOpen(ip,port):
    s = socket.socket(socket.AF_INET,socket.SOCK_STREAM)
    try:
        s.connect((ip,int(port)))
        s.shutdown(2)
        return True
    except:
        return False

result = IsOpen('127.0.0.1',sys.argv[2])

if sys.argv[1] == 'start':
    if result:
        print('Sorry that the port is occupied!')
    else:
        os.system(myd+ ' --defaults-file=/data/mysql/mysql'+sys.argv[2]+'/my.cnf&')
elif sys.argv[1] == 'restart':
    os.system('%s -u%s -p%s -S /tmp/mysql%s.sock shutdown' %(myadmin,m_user,m_password,sys.argv[2]))
    os.system('sleep 2')
    os.system(myd+ ' --defaults-file=/data/mysql/mysql'+sys.argv[2]+'/my.cnf&')
elif sys.argv[1] == 'stop':
    os.system('%s -u%s -p%s -S /tmp/mysql%s.sock shutdown' %(myadmin,m_user,m_password,sys.argv[2]))
else:
    print('Please enter ===python %s start+port or restart+port or stop+port===' %sys.argv[0])
 
 
#python mysql.py start 3306
#python mysql.py start 3307
 
#python mysql.py restart 3306
#python mysql.py restart 3307

#python mysql.py stop 3306
#python mysql.py stop 3307
MySQL数据库技术