MySQL笔记
关注
分享
扫一扫
mysql
Bcc的笔记
2015年开始在搜狐畅游实习，2016年毕业，目前在抖音为直播业务保驾护航，欢迎加入我们
展开
-
MySQL load data from file
需求背景：将redis rdb解析的结果集文件快速录入的mysql表结构CREATE TABLE `redis_analyze` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增id', `db_index` int(11) DEFAULT NULL COMMENT 'redis db 下标', `key_name` varchar(255) DEFAULT NULL COMMENT 'key name', `data_typ.原创 2021-01-23 20:58:40 · 450 阅读 · 1 评论 -
mysqld got signal 11
问题发生背景问题实例之前使用的是percona server，是安装pmm镜像自带的数据库，之后通过mysqldump迁移到了MySQL server，目前是只有有pmm server 访问pmm库，MySQL server就crash，并通过守护进程mysqld_safe 自动重启，crash 集尝试重启的一部分信息如下11:24:49 UTC - mysqld got sign...原创 2018-01-11 21:24:00 · 293 阅读 · 0 评论 -
ssh 远程执行绝对路径命令mysqld_multi 报my_print_defaults不存在
通过SSH直接执行远程命令(这种方式会使用Bash的non-interactive + non-login shell模式)找不到命令参考：http://ghoulich.xninja.org/2017/05/09/how-to-find-env-vars-with-ssh-remote-exec-commands/ssh root@ip1 "/usr/local/mysql/bin...原创 2017-12-01 23:56:00 · 184 阅读 · 0 评论 -
not exists、left join/is null、not in 行为
测试数据20:25:52[test](;)> select * from t;+------+------+| id | b |+------+------+| 1 | NULL || 2 | 1 || 3 | 4 |+------+------+3 rows in set (0.00 sec)实现查询t表中id 的值不存在b中的id...原创 2017-11-18 21:55:00 · 548 阅读 · 2 评论 -
慢查询阻塞了xtrabackup进而阻塞以后的sql导致的系统瘫痪问题
收到开发反应一库的sql频繁超时，系统几乎瘫痪，无法执行任何操作，我登上库先查看到当前的线程，发现有大量的线程状态是 Waiting for table flush查看当前的事务从昨天开始执行，到今天早晨还没执行完，具体原因还没深究，先将此线程释放，然后备份才可以flush table成功继而备份完成后后面一系列被阻塞的sql都得以正常运行mysql> select ...原创 2017-12-01 23:34:00 · 83 阅读 · 0 评论 -
SQL 优化案例之变更表结构
从慢日志报表中看到一条很长的SQLselect id from myinfo WHERE 1 = 1 and (( SUBSTRING_INDEX(location_axis, '$', 3) like concat('%$', 2334) or SUBSTRING_INDEX(location_axis, '$', 3) like concat...原创 2017-11-16 19:52:00 · 48 阅读 · 0 评论 -
Percona XtraBackup 2.4新特性之恢复单个表数据
参考文档：https://www.percona.com/doc/percona-xtrabackup/2.4/xtrabackup_bin/restoring_individual_tables.html需要满足的条件：innodb_file_per_table开启innodb存储引擎表有对应的.ibd文件满足以上条件就可以通过物理备份集生成以表为单位的export文件测试...原创 2017-10-16 23:17:00 · 124 阅读 · 0 评论 -
RR隔离级别下通过next-key locks 避免幻影读
---恢复内容开始---mysql innodb目前使用范围最广的两种隔离级别为RC和RR，RR修复了RC中所存在的不可重复读READ COMMITED不可重复读在同一事务中两次查看的结果集不同，如下session1的查看结果集受session2提交的事物影响05:47:35[test](;)> select @@tx_isolation;+--------------...原创 2017-09-18 16:50:00 · 112 阅读 · 0 评论 -
INSERT ... ON DUPLICATE KEY UPDATE Syntax
如果表上有唯一值约束，为防止insert时唯一值约束列上发生唯一键冲突报错，可以采用insert into table ... on duplicate update ...来做处理测试表结构，id和col1上都有唯一值约束CREATE TABLE `t1` ( `id` int(11) NOT NULL, `col1` int(11) DEFAULT NULL, `c...原创 2017-08-20 22:45:00 · 65 阅读 · 0 评论 -
sql自动审核工具-inception
[inception使用规范及说明文档](http://mysql-inception.github.io/inception-document/usage/)[代码仓库](https://github.com/mysql-inception/inception)inception介绍inception是去哪网团队开发的一个集审核、执行、备份及生成回滚语句于一身的MySQL自动化运维...原创 2017-08-08 17:29:00 · 351 阅读 · 0 评论 -
The BLOB and TEXT Types
官网参考：https://dev.mysql.com/doc/refman/5.7/en/blob.html字符串类型对应的存储需求Data TypeStorage RequiredCHAR(M)M × w bytes, 0 <= M <= 255, where w is the number of bytes required for the maximu...原创 2017-07-27 14:27:00 · 101 阅读 · 0 评论 -
Replication and Triggers
参考官网：https://dev.mysql.com/doc/refman/5.7/en/replication-features-triggers.html需要了解复制和触发器关系的背景:程序变更的数据和账单对不上，但从程序上无法找到问题，于是开发人员想在数据库层记录通过触发器来记录每次变更的历史数据和新数据创建触发器的语句# 原始表t1# 创建存储变更信息的表_trigg...原创 2017-07-13 13:35:00 · 62 阅读 · 0 评论 -
pmm metrics 数据采集来源
handler状态参数mysql> show global status like '%handler%';+----------------------------+------------+| Variable_name | Value |+----------------------------+------------+| H...原创 2017-09-22 17:45:00 · 132 阅读 · 0 评论 -
InnoDB 存储引擎的锁机制
测试环境隔离级别：REPEATABLE-READ行级别的 - Share and Exclusive Locks共享锁 S：允许持有S锁的事务对行进行读操作排他锁 X: 允许持有X锁的事务对行进行update或delete操作表级别的意向锁 - Intention LockInnoDB支持多粒度的锁定，允许行锁和表锁共存。通过意向锁来实现。比如，SELECT ... LO...原创 2016-09-29 19:03:00 · 63 阅读 · 0 评论 -
说说慢日志
MySQL Server提供general query 和slow query的跟踪记录功能，现在只说说慢查询日志。开启参数：--slow_query_log记录格式参数：--log-output=TABLE,FILE 如果value是TABLE，则慢日志记录在表mysql.slow_log设置慢日志的文件名：slow_query_log_file默认情况下，Administ...原创 2016-09-29 11:28:00 · 64 阅读 · 0 评论 -
xtrabackup: error: last checkpoint LSN (3409281307) is larger than last copied LSN (3409274368)
1.错误发生场景：使用2.4.1版本的xtrabackup工具进行全备，备份日志中报出此错误2.知识要点：MySQL中，redo 日志写进程会在三种条件下被触发从log buffer中写日志到redo log file中，分别是每隔一秒、日志达到log buffer的1/3、每一次提交的时候。刷新日志的LSN到log file是采用轮询的方式进行的，比如有三组日志当这三组都被写满的时候再...原创 2016-09-09 15:08:00 · 60 阅读 · 0 评论 -
查看数据库中没有进行comment的字段
为落实数据库规范，既每个表字段都需要有comment注释，所以需要过滤生产表中没有comment的字段，搜索出还不错的过滤语句show full columns from table where Comment='' ;show clumns的语法如下SHOW [FULL] COLUMNS {FROM | IN} tbl_name [{FROM | IN} db_name...原创 2016-09-18 15:36:00 · 352 阅读 · 0 评论 -
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
salve复制线程停止，尝试start slave 时报ERROR 1872错误mysql> system perror 1872MySQL error code 1872 (ER_SLAVE_RLI_INIT_REPOSITORY): Slave failed to initialize relay log info structure from the repository解决...原创 2016-09-09 15:09:00 · 295 阅读 · 0 评论 -
通过FEDERATED存储引擎跨实例访问数据
通过FEDERATED存储引擎同步两实例间的表数据需求情景：实例1中A库中的三个视图是实例2中的B库所依赖的，B需要A库中三个视图的实时数据。方案：通过FEDERATED来完成跨势力的查询FEDERATED存储引擎表只会创建表结构，不会存储表数据，可以通过建表语句中的CONNECTION [=] 'connect_string'来指定访问远端数据的连接方式connect_string：s...原创 2016-09-09 15:07:00 · 96 阅读 · 0 评论 -
通过 purge_relay_logs 自动清理relaylog
使用背景线上物理备份任务是在从库上进行的，xtrabackup会在备份binlog的时候执行flush logs，relay-log会rotate到新的一个文件号，导致sql thread线程应用完之后无法自动删除这时候就可以引入purge_relay_logs做成执行计划自动清理relay log安装yum install -y mha4mysql-node-0.56-0.e...原创 2018-06-28 16:41:00 · 290 阅读 · 0 评论 -
xtrabackup: error: last checkpoint LSN (3409281307) is larger than last copied LSN (3409274368). #2
1.错误发生场景：使用2.4.1版本的xtrabackup工具进行全备，备份日志中报出此错误2.知识要点：MySQL中，redo 日志写进程会在三种条件下被触发从log buffer中写日志到redo log file中，分别是每隔一秒、日志达到log buffer的1/3、每一次提交的时候。刷新日志的LSN到log file是采用轮询的方式进行的，比如有三组日志当这三组都被写满的时候再...原创 2018-03-03 15:22:00 · 110 阅读 · 0 评论 -
通过FEDERATED存储引擎同步两实例间的表数据
需求情景：实例1中A库中的三个视图是实例2中的B库所依赖的，B需要A库中三个视图的实时数据。方案：通过FEDERATED来完成跨势力的查询FEDERATED存储引擎表只会创建表结构，不会存储表数据，可以通过建表语句中的CONNECTION [=] 'connect_string'来指定访问远端数据的连接方式connect_string： scheme://user_name[:...原创 2018-03-03 15:19:00 · 62 阅读 · 0 评论 -
通过performance schema收集慢查询
MySQL5.6起performance schema自动开启，里面涉及记录 statement event的表mysql> show tables like '%statement%';+----------------------------------------------------+| Tables_in_performance_schema (%stateme...原创 2018-01-22 22:10:00 · 119 阅读 · 0 评论 -
记一次定时备份任务的失败原因
备份计划失败原因：使用which xtrabackup 获取xtrabackup的绝对路径，直接调用脚本可以获取，但是在crontab中获取不了，报以下异常# which 从环境变量中搜索++ which xtrabackup which: no xtrabackup in (/usr/bin:/bin)原因是crontab中环境变量是(/usr/bin:/bin)，非此目录下...原创 2018-01-12 22:17:00 · 126 阅读 · 0 评论 -
MySQL 添加审计功能
MySQL社区版没有自带的设计功能或插件。调研发现MariaDB的audit plugin 同样适用于MySQL，支持更细粒度的审计，比如只审计DDL操作，满足我们的需求。因为最近测试环境的某表结构经常性的被变更且数据被清空的情况，所以引入MariaDB的插件对DDL进行审计MariaDB audit plugin 官网MariaDB audit plugin 下载地址查看MySQ...原创 2018-04-16 13:33:00 · 95 阅读 · 0 评论 -
MySQL将内存用在了哪里
本片文章参考官网讲述MySQL是如何分配内部内存，同时涉及到如何合适设的置内存分配以及如何监控内存的使用情况官方文档MySQL在启动时默认被分配给512MB RAM,可以通过设置相关内存参数对其进行设置，下面时MySQL使用内存的地方1、InnoDB buffer pool 用于缓存表数据、索引及其他的一些辅助缓冲池，为了高效进行缓存管理，buffer pool 应用多种LRU(l...原创 2018-03-03 14:53:00 · 269 阅读 · 0 评论 -
修改mysql数据的字符集校验规则使其区分大小写
mysql 使用utf8字符集默认的校验规则collate为utf8_general_ci，不区分数据的大小写测试如下13:50:04[test](;)> alter table test add col1 varchar(25) character set utf8 collate utf8_bin;Query OK, 0 rows affected (0.16 sec)...原创 2017-12-26 16:12:00 · 153 阅读 · 0 评论 -
MySQL binlog_rows_query_log_events在线设置无效
binlog_rows_query_log_events 对binlog_format=row有效，设为true后可以在binary log中记录原始的语句官方文档显示binlog_rows_query_log_events是动态参数，可以在线设置生效，但是测试中发现只有重启才可以生效版本信息22:25:13[test](;)> select version();+---...原创 2017-11-13 22:46:00 · 492 阅读 · 0 评论 -
MySQL5.7 MTS work线程stack
复制现象是，slave线程状态正常，但是sql 线程不应用，所以delay越来越大，查看复制状态mysql> show slave status\G*************************** 1. row *************************** Slave_IO_State: System lock...原创 2017-11-04 12:07:00 · 149 阅读 · 0 评论 -
MySQL错误集锦
允许 enforce-gtid-consistency 可能引发的错误21:43:04[test](;)> CREATE TABLE test (c1 BIGINT UNSIGNED NOT NULL);Query OK, 0 rows affected (0.11 sec)21:44:22[test](;)> insert into test values(0)...原创 2017-10-18 21:59:00 · 62 阅读 · 0 评论 -
MySQL metadata lock
什么是元数据描述数据库中的数据的数据都是元数据，如库名、表明、列名、版本名，和show语句展示的大多数内容都是元数据，以及在information_shema中记录数据库对象的表中的内容也是元数据为什么MySQL要设置元数据锁为了保证可以并发访问数据库对象及保证数据的一致性，所以应用metadata lock，如session1正在扫描t表数据，此会话持有t表的元数据锁，这时ses...原创 2017-09-14 23:03:00 · 83 阅读 · 0 评论 -
MySQL复制原理
参考官网：https://dev.mysql.com/doc/refman/5.7/en/replication-implementation-details.htmlMySQL复制功能是通过三个线程实现的，包括主上的binlog dump tread和从上的sql thread、I/O ttreadBinlog dump threadmaster创建binlog dump thr...原创 2017-07-13 20:58:00 · 49 阅读 · 0 评论 -
MySQL主从复制报错一致性问题解决
当MySQL主从复制中因为不一致报错的情况，我们第一时间想到的就是使用pt-table-checksum来进行检查主从一致并进行修复，但是使用此工具的前提是主从复制线程都为on状态，所以这种情况下可以先转化一下思维方式，可以先将错误屏蔽掉恢复主从复制线程状态，然后再使用利器pt工具进行检查并修复。从上设置slave_skip_errors ，默认此参数为OFF，此参数为静态变量，所...原创 2017-03-30 17:28:00 · 90 阅读 · 0 评论 -
MySQL Group Replication
在>=mysql5.7.17的版本中开始支持组复制插件。组复制中的成员至少需要三个才会起到容错作用，各成员在通信层通过原子广播及总订单消息的传递一起应用或回滚事务组从而达到数据的强一致性。组复制的成员是独立处理事务的，rw事务需要通过组的冲突检查才可以进行，ro事务则不需要组之间的通信而直接提交。当一个成员要提交rw事务时，会原子广播写入的行数据和相关的写入集(变更行的唯一身份...原创 2017-05-05 17:16:00 · 81 阅读 · 0 评论 -
MySQL 待解决死锁
官方文档：https://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html线上出现一个死锁现象，信息显示的是两条对同一个表的不同记录的update操作，表上只有一个主键索引，更新的条件上无索引，时间地段显示两个update只相差1ms业务场景是同时一个事务中先是insert再update新插入的行，存在并发；数据库环境是5....原创 2017-06-22 10:43:00 · 50 阅读 · 0 评论 -
MySQL容量规划之tcpcopy应用之道
官方文档：https://github.com/session-replay-tools/mysql-replay-moduletcpcopy可以将正式环境上来自客户端的请求复制一份到测试端并复现，想要真实的对MySQL进行容量规划，可以借助tcpcopy来将线上的流量呈倍数的增长，将其复制到测试环境，从而快速定位测试环境出现瓶颈时负载情况，进而做好容量的全局把控部署伪装客户端I...原创 2017-04-20 18:14:00 · 111 阅读 · 0 评论 -
MySQL 子查询优化案例
开发人员给了一个sql，结构如下delete from B where ID in (select NID from H where guid='xxx');内部sql满足条件的结果集只有一条，但是整个删除操作执行了将近1分钟，如果是将结果集放在括号里或者将in改为= ，执行的速度可以实现毫秒级别但是如果内部查询结果集多于一行，采用第一种方案的话需要更改程序，后来又试了一种更改为j...原创 2017-03-23 21:42:00 · 126 阅读 · 0 评论 -
MySQL5.7多源复制实践
MySQL5.7开始新增多源复制功能，即允许一个salve同时复制多个主库的事务，slave会创建通往每个主库的管道。多源复制在应用来自多个源的事务的时候不会对有冲突的事务进行检测。配置实现主库支持基于GTID和binlog position的配置；从库存储master信息的仓库需要为table：#在配置文件中加入master-info-repository=TABLE rel...原创 2017-04-08 12:30:00 · 76 阅读 · 0 评论 -
MySQL 通过semi join 优化子查询
半连接是MySQL 5.6.5引入的，多在子查询exists中使用，对外部row source的每个键值，查找到内部row source匹配的第一个键值后就返回，如果找到就不用再查找内部row source其他的键值了。测试环境mysql> desc class;+------------+-------------+------+-----+---------+-----...原创 2016-10-09 16:38:00 · 158 阅读 · 0 评论 -
MySQL5.6下使用xtrabackup部分备份恢复到MySQL5.7
现有需求：需要备份MySQL5.6环境下的部分表到MySQL5.7环境下并进行恢复通过xtrabackup 实现部分备份有三种方式：参考链接：http://blog.youkuaiyun.com/zhu19774279/article/details/49681767我这里测试了两种方法：way1/usr/bin/innobackupex --defaults-file=/etc/mys...原创 2016-10-08 18:22:00 · 242 阅读 · 0 评论