MySQL
关注
分享
扫一扫
文章平均质量分 68
遇星
我想成为我自己
展开
-
xtrabackup和mysqldump备份大致过程
xtrabackup备份过程1.从最新的checkpoint开始读redo，因为直接拷贝数据文件，有可能还有脏数据留在内存还未刷到磁盘，不能直接从当前redo lsn读起，而是checkpoint读起；2.拷贝ibdata和数据文件3.拷贝ibd4.flush tables with read lock前，观察ftwrl-wait-timeout秒，如果已经执行超过ftwrl-wait-threshold秒的慢查询在观察期间不能结束，则终止备份进程；这是防止直接发起ftwrl，当无法flush t原创 2020-05-08 17:35:32 · 347 阅读 · 0 评论 -
MySQL 一些容易迷惑的加锁例子
以下实验基于这个表，当然隔离级别是RR，不然RC下也没那么多复杂的加锁情况了：mysql> select * from sam;+----+------+------+| id | c2 | c3 |+----+------+------+| 1 | 1 | 1 || 5 | 5 | 5 || 10 | 10 | 10 |+----...原创 2020-05-02 13:59:32 · 441 阅读 · 0 评论 -
MySQL innodb 表空间传输实现单表迁移
表空间传输，可以说是单表传输吧，在某些情况下会有它的使用场景，记录一下。例如在3306实例有一个t表mysql> desc t;+-------+---------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+---------+------+--...原创 2020-04-29 12:10:17 · 534 阅读 · 0 评论 -
MySQL binlog_row_image不同取值影响查询结果
介绍一个前提，对于一个事务内，DML是当前读，而且DML所做的更新，对本事务也是可见的。例如，假设sam表只有一行数据是col1=1,当全表update后，虽然事务还未提交，而且即使在RR级别下，SQL1建立了一致性视图，但经过SQL2更新后，SQL3是能够看到自身更新的数据，这是因为SQL2更新后，所有数据行的trx_id都变为当前事务的trx_id，自然是可见的。也可以这样理解，我自己做的...原创 2020-04-29 11:22:10 · 494 阅读 · 0 评论 -
两个insert之间发生死锁的例子
为什么没有产生唯一键冲突报错？因为还没获得行锁，无法判断唯一性一、简单insert之间的死锁测试表:mysql> select * from sam;+----+------+------+| id | col1 | col2 |+----+------+------+| 1 | 1 | 1 || 2 | 2 | 2 || 3 | 3 |...原创 2020-04-28 23:33:56 · 1202 阅读 · 2 评论 -
RR级别下，为什么唯一索引等值查询可以退化为行锁，而普通索引不行？
因为在等值查询时，由于唯一索引的唯一性，只要行锁，就能保证不会幻读；而普通索引的等值索引，还使用行锁的，那么可以插入相同的行，就会出现幻读。例如：select * from test where col=1 for update；如果col是唯一索引，那么只要锁住col=1的行，就无法产生幻读；如果col是普通索引，那么锁住col的行，依然可以插入col1=1的数据，那么重复执行就会比之...原创 2020-04-28 21:15:00 · 639 阅读 · 0 评论 -
null会存在索引里吗？null可以使用索引吗？为什么不建议列值默认是null？
一、null会存在索引里吗？别的RDBMS不清楚，Oracle是怎样的也记不清了，但是对于MySQL的二级索引，是存储null值的。例如：mysql> desc sam;+-------+---------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-----...原创 2020-04-28 15:23:57 · 4682 阅读 · 0 评论 -
MySQL 行格式总结
compact1.隐藏列，6 bytes 事务id列，7 bytes 回滚指针列，1 bit 删除标记位；2.变长行头，记录变长列的长度3.5 bytes 行头，用于连续行之间的链接和行锁4.位向量，null标记位，假设可为null的列为N，那么需要celling(N/8) bytes5.null值不占用行的空间6.存在非null值的列数据7.如果无显示指定主键，多一个6 bytes...原创 2020-04-27 22:19:37 · 389 阅读 · 0 评论 -
一个不走索引的更新语句，到底会不会锁全表
经常看到有人说，如果一个DML不能走索引，就要锁全表。这正确吗？我曾经也这样以为，直到现在我才发现，对一半错一半吧。答案应该是：例如如下一个表：mysql> show create table sam\G*************************** 1. row *************************** Table: samCreate Ta...原创 2020-04-23 22:53:04 · 2032 阅读 · 0 评论 -
MySQL 行锁该怎么分析
一些可以用于排查的信息：1.information_schema.innodb_locks可以看到哪些事务在持有哪些锁，例如：+----------------------+-------------+-----------+-----------+-----------------------------------+------------+------------+----------...原创 2020-04-22 16:22:11 · 394 阅读 · 0 评论 -
关于Redis分布式锁的思考
一个分布式锁，需要满足以下：1.独享，即一个锁不能同时被两个客户端持有2.无死锁，即当一个持有锁的客户端异常退出了，锁要自动释放，否则其他客户端永远都拿不到锁3.单点问题，即若只用一个单实例来实现分布式锁，那么当这个节点挂了，也就导致分布式锁功能停止了。这其实并不是分布式锁的独有问题，单点问题是所有单实例Redis服务的共有问题。对于单点问题，Redis主从的方案也无法完全解决，例如客户...原创 2020-04-22 14:22:29 · 269 阅读 · 0 评论 -
MySQL grant之后是否需要flush privileges
MySQL grant之后是否需要flush privileges在MySQL旧版本的使用上，很多人习惯直接更新mysql.user等表来完成用户的修改密码，修改权限等，最后需要flush privileges来生效。而使用grant来授权或revoke来收权，是不需要执行flush privileges的。直接修改系统表的方式之所以需要flush privileges，是改了系统表之后，内...原创 2020-04-21 14:40:02 · 1564 阅读 · 1 评论 -
临时表的特性，临时表是怎么写binlog的
临时表的特性，临时表是怎么写binlog的通过create temporary table …创建的临时表，仅对当前会话可见，当当前会话线程退出以后，将会自动删除临时表。需要注意的是，如果创建的临时表和已存在的表重名，那么访问该表明时，是访问临时表，如：mysql> use samDatabase changedmysql> show tables;+-----------...原创 2020-04-20 11:39:58 · 731 阅读 · 0 评论 -
关于double write的思考
double write是Innodb防止部分写的特性，大致原理是：在把buffer pool脏页刷盘时，先把脏页写入内存中的double write buffer，之后将double write buffer的全部内容分两次写入系统表空间上连续的128个页，然后再把系统表空间上的double write数据写入到各个数据页对应的表的数据文件上。那么问题就来了：1.既然直接从buffer p...原创 2020-04-19 11:26:54 · 460 阅读 · 0 评论 -
insert into bak select * from test会锁表吗
答案是跟隔离级别有关系，RR级别下会将test表所有记录上行锁，以及所有记录间隔上gap锁；对bak插入的记录加行锁；RC级别下test表不加任何锁；对bak插入的记录加行锁；即区别在于test表上的锁。实验证明：1.RR级别会话1执行insert into bak select * from test;mysql> begin;Query OK, 0 rows affect...原创 2020-04-16 18:03:57 · 752 阅读 · 0 评论 -
一个redo和binlog两段提交的思考
对于事务提交，是这样一个流程：1.发出commit语句；2.redo进入prepare阶段；3.写binlog；4.redo进入commit阶段；5.事务完成提交还有两个参数有关联，1.innodb_flush_log_at_trx_commit各取值含义：0，事务提交时，将redo保留在redo buffer，redo日志既不write到OS cache，也不fsync到磁盘，...原创 2020-04-15 23:40:11 · 240 阅读 · 0 评论 -
关于RR和间隔锁的一些实验
关于RR和间隔锁的一些实验测试表如下：mysql> show create table test\G*************************** 1. row *************************** Table: testCreate Table: CREATE TABLE `test` ( `id` int(11) NOT NULL, ...原创 2020-04-15 16:25:08 · 281 阅读 · 0 评论 -
MySQL 主从复制的一个BUG
发现一个主从复制的BUG，从库会自动跳过不存在的主键列，看例子。主库有一个sam表，其中id是自增主键：mysql> desc sam;+-------+---------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-----...原创 2020-01-03 17:04:24 · 438 阅读 · 0 评论 -
InnoDB: Cannot open table xxx/xxx from the internal data dictionary of InnoDB though the .frm file
遇到以下报错：[Warning] InnoDB: Cannot open table xxx/xxx from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/inno...原创 2019-11-27 10:38:18 · 9731 阅读 · 1 评论 -
Xtrabackup The --slave-info option requires GTID enabled for a multi-threaded slave.
使用xtrabackup遇到以下错误：The --slave-info option requires GTID enabled for a multi-threaded slave.是因为备份时使用了–slave-info，这样就会在xtrabackup_slave_info文件里记录备份结束时binlog位点以及GTID等信息。乍一看，输出binlog位点以及GTID信息跟GTID e...原创 2019-11-21 19:25:13 · 1446 阅读 · 0 评论 -
Xtrabackup Unable to obtain lock. Please try again later.
使用Xtrabackup遇到以下错误：191121 07:00:48 Waiting for query 81858758 (duration 2958 sec)191121 07:00:49 Unable to obtain lock. Please try again later.这个错误主要跟innobackupex的ftwrl-wait-timeout有关，因为在备份脚本中设置了f...原创 2019-11-21 19:09:09 · 1269 阅读 · 0 评论 -
xtrabackup: error: it looks like InnoDB log has wrapped around before xtrabackup could process all r
使用innobackupex备份遇到以下错误：xtrabackup: error: log block numbers mismatch:xtrabackup: error: expected log block no. 665497466, but got no. 673689450 from the log file.xtrabackup: error: it looks like In...原创 2019-11-21 18:49:09 · 1253 阅读 · 0 评论 -
MySQL innodb file format以及innodb row format
一、概念当前MySQL Innodb拥有两种文件格式：1.旧版本的antelope，对应到两种行格式：redundant和compact2.新版本的barracuda，对应到两种行格式：dynamic和compressed文件格式和行格式可以通过参数设置：SET GLOBAL innodb_file_format=Barracuda;SET GLOBAL innodb_default_...原创 2019-11-08 14:21:40 · 1469 阅读 · 0 评论 -
[ERROR] InnoDB: Attempted to open a previously opened tablespace
启动时遇到以下报错：[ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace xxx/xxxx uses space ID: 4 at filepath: ./xxx/xxxx.ibd. Cannot open tablespace mysql/gtid_slave_pos whic...原创 2019-10-21 18:17:53 · 1185 阅读 · 0 评论 -
[ERROR] mysqld: Can't create/write to file '/apps/tmp/ibgKVn0u' (Errcode: 2 "No such file or directo
今天开发找来说MySQL怎么都启动不了，启动时报以下错误：[ERROR] mysqld: Can't create/write to file '/apps/tmp/ibgKVn0u' (Errcode: 2 "No such file or directory")InnoDB: Error: unable to create temporary file; errno: 2错误比较明显，...原创 2019-10-21 17:49:12 · 1774 阅读 · 0 评论 -
MySQL @和@@
@是用户定义的变量，如set @a=1.@@是系统变量，例如查看系统参数，select @@global.log_error_verbosity，则查询全局的log_error_verbosity参数值；如果不带global参数，则默认是查询会话级的参数值，但是如果select @@var，该var没有会话级别的选项，那么当然是显示全局级别的值；会话级的参数也可以通过select @@sessi...原创 2019-10-18 11:51:38 · 2529 阅读 · 0 评论 -
the master has purged binary logs containing GTIDs that the slave requires的处理
从库kill -9重启之后，复制出现异常：mysql> show slave status\G*************************** 1. row ***************************Slave_IO_State:Master_Host: xxx.xxx.xxx.xxxMaster_User: xxxxMaster_Port: 3306Con...原创 2018-11-15 11:10:26 · 4104 阅读 · 0 评论 -
MySQL日志体系介绍
MySQL提供了几种日志功能，如下：日志类型作用Error log记录mysqld在启动、关闭、运行时出现的问题General query log记录客户端发出的SQL和连接命令Binary log记录更改数据的语句Relay log只在从库存在，存放接受到的主库的binary log内容Slow query log慢查询日志DDL...原创 2018-11-21 20:53:53 · 247 阅读 · 0 评论 -
MYSQL锁机制详解
最近在学习MySQL的锁机制和隔离级别，发现跟Oracle还是有比较大的差异。例如Oracle默认隔离级别是read committed，而MySQL默认是repeatable read。Oracle除了串行化，无法避免幻读。而MySQL在repeatable read级别下，通过next-key锁机制，避免了幻读的产生。记录一下理论理解以及实验验证。InnoDB锁类型有如下几种：共享锁（S）...原创 2018-11-19 13:59:02 · 331 阅读 · 0 评论 -
MySQL列权限授予及注意事项
对于权限的授予，库和表级别的都比较常见，但具体到表的某个列的访问权限怎么授予呢？创建视图？是否也可以按照独立的列授予insert|delete|update的权限呢？其实，MySQL对列的授权，是有专门的语法实现的，例如授予test库priv_test表的id列的select权限给sam用户，可以用以下语法实现：grant select(id) on test.priv_test to sam...原创 2018-11-19 16:10:31 · 4408 阅读 · 0 评论 -
由一个关于auto_increment的奇怪案例看MySQL8的一些改进优化
偶然发现一个比较奇怪的问题，在使用alter table tab auto_increment=N修改表的自增初始值时，information_schema.tables这个表的auto_increment列并没有同步更新：mysql> show create table test.incr_test\g+-----------+-----------------------------...原创 2018-11-22 18:16:43 · 2192 阅读 · 0 评论 -
MySQL server has gone away
通常遇到MySQL server has gone away，是因为会话长时间空闲，MySQL主动将会话关闭了mysql> show databases;ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id: 5674Current da...原创 2018-11-23 13:54:38 · 5479 阅读 · 0 评论 -
MySQL8.0的information_schema.tables信息不准确怎么办
在MySQL8.0以前，通常会通过infomation_schema的表来获取一些元数据，例如从tables表中获取表的下一个auto_increment值，从indexes表获取索引的相关信息等。但在MySQL8.0去查询这些信息的时候，出现了不准确的情况。例如auto_increment，--此时test表的auto_increment是204mysql> show create ...原创 2018-11-29 10:17:06 · 98263 阅读 · 7 评论 -
MySQL Innodb引擎体系结构
Innodb 主要组成部件：1.Buffer Pool2.Change Buffer3.Adaptive Hash index4.Redo Log Buffer5.System Tablespace6.Innodb Data Dictionary7.Doublewrite Buffer8.Undo Logs9.File-Per-Table Tablespace10.Undo T...原创 2018-11-26 11:22:07 · 355 阅读 · 0 评论 -
metadata lock为何物？好吃吗？
说来话长，废话少说，呵呵。metadata lock这家伙中文名叫元数据锁，在MySQL5.5中引入。我们都知道MySQL中有表锁，行锁，页锁，各自的级别分别是表，行，页。而元数据锁只对表的元数据启用。当有线程开始使用表的时候，就会对该表所有元数据上锁，即metadata lock。来个实际的例子：--session 1开启一个查询test表的事务，不提交mysql> begin;Q...原创 2018-11-29 15:05:56 · 244 阅读 · 0 评论 -
MySQL 替换查询结果中的null或特定字符
偶尔会遇到有人问，表里某个列存在null值，查询结果集里想要把null替换成特定的字符，怎么做？看到有人写了各种各样比较复杂的SQL，其实在MySQL里只要一个函数就能搞定，那就是ifnull，长话短说，看例子：--将结果集中的null替换成'空'mysql> select id from test;+------+| id |+------+| 1 || 2...原创 2018-11-30 08:52:12 · 6103 阅读 · 2 评论 -
关于MySQL密码验证，Client does not support authentication protocol requested by server
话说我已经暗恋MySQL许久，终于在今天勇敢向他表白，敲出了那句mysql -u -p，说了声：我爱你…谁知：[root@mysql3 ~]# mysql -uroot -psam123Warning: Using a password on the command line interface can be insecure.ERROR 1045 (28000): Access denie...原创 2018-12-04 20:50:42 · 3933 阅读 · 0 评论 -
MySQL 系统表空间ibdata：我也不想当一个死肥宅
InnoDB的系统表空间，由一个或多个ibdata文件构成，存放InnoDB表的元数据，数据字典等等。说到数据字典，在MySQL8.0里忽然又想起另一个文件mysql.ibd，这个也是存放数据字典的地方，那是不是这两者是不是重复了呢？只能说有可能有重叠的数据，有可能一部分类型的数据字典信息放在ibdata，另一部分类型的数据字典信息放在mysql.idb。而两者可以明确区别的是，ibdata对应...原创 2018-11-30 16:14:37 · 930 阅读 · 0 评论 -
MySQL常见时间函数简介
函数名简介ADDDATE()增加时间，天ADDTIME()增加时间，时分秒CONVERT_TZ()时区转换CURDATE()返回当前时间，date格式CURTIME()返回当前时间，time格式CURRENT_TIMESTAMP()返回当前时间，timestamp格式，与now()类似DATE()提取时间值的date部分D...原创 2018-12-03 11:09:11 · 228 阅读 · 0 评论 -
MySQL8.0.12二进制安装
刚好需要搭建一个测试环境，记录一下搭建过程。1.配置文件。配置文件指定一些关键的选项，其他依照默认设定[root@mysql8 mysql]# cat /etc/my.cnf[mysql]user=rootpassword=sam123socket=/usr/local/mysql/mysql.sockport=3306[mysqld]user=mysqlport=3306...原创 2018-12-05 14:08:17 · 239 阅读 · 0 评论