雅冰石的专栏

生产环境当有关于一个大表的大操作时(比如select count一个大表)，此时对大表做DDL会被阻塞，提示等待table metadata lock。如果业务比较频繁的话，该DDL会阻塞关于该表的后续select sql。如果生产库cpu使用率打满，想通过创建索引优化导致cpu使用率打满的慢sql时，被阻塞了，则无法快速处理该故障。使用gh-ost工具可以在不锁表的情况下在线修改表结构，仅仅在最后切换表名(rename table)时锁表，但很快，就一两秒左右。

假如割接有问题，从云上B回滚到C，不覆盖IDC的A，这样保险些。2、根据数据量大小决定提前一天或者多久做好全量+增量数据同步，关注增量同步状态，日志等信息。4、检查DRS增量同步状态，确认状态为“增量迁移中”，且同步无延迟(A->B,B->C)以华为云DRS为例--检查A库和B库据数据一致性，数据行比对，确认数据源目的数据一致。② 等迁移任务1到增量状态之后，再创建云B到idc C的全量+增量实时迁移任务2。2. 选择行数对比（迁移对比->数据级对比->创建对比任务->行数对比)还有的话，将其kill。

线上发现一条类似这样的慢sql（查询时长8s）：from t15;t14的id和id2字段上都有索引，但是因为条件里有or，导致走的是全表扫描:如果没用count(*),而是select 字段这种方式，那可以用union这种方式替代or，但这里是count(),则有些不同。

/在①delete过程中，多次执行该sql，前几次很快（2秒钟）执行完毕，t1数据量是删除前的数据量，但发现有时会阻塞，阻塞了74秒，进程状态是Executing hook on transaction begin，不阻塞后，显示查询结果为0，读到的是① delete后的数据量。//在①delete过程中，多次执行该sql，t1数据量一直是删除前的数据量，直到① delete完毕，这里变成0条数据，说明是同步复制，节点2删除完，节点1才执行成功。//①执行期间，在节点2上查询同一个表，出现阻塞。

表没有主键的时候，delete很多数据，会导致从库出现很长时间延迟，因此需要严格把控，定期检查，确保主库上不存在没有主键的表。发现trx_query为空，以前线上从库出现延迟，能看到当前运行的sql的，不知为何这次看不到。因为上面已经在从库建过主键了，所以从库复制会停止，提示主键冲突，可以通过跳过这个事务来处理。#查看慢事务对应的进程id，注意别多杀了，只杀运行时间很久的，导致延迟的sql。如果没有主键，就需要先在从库上为这个表建下主键，先解决这个延迟的问题。只需要在从库执行以下命令就能跳过一个事务。

pt-archiver的原理主要是根据定义的时间间隔(sleep参数)，扫描要清理的数据表。它按照指定的规则分批(limit参数)将查询到的记录转移到其他表或文件中，发现它是按主键去删除的表数据，对数据库影响很小。

备份失败，查看日志报错：2024-04-07T03:00:04.420147+08:00 0 [ERROR] [MY-011825] [Xtrabackup] Found tables with row versions due to INSTANT ADD/DROP columns2024-04-07T03:00:04.420242+08:00 0 [ERROR] [MY-011825] [Xtrabackup] This feature is not stable and will cause back

MySQL 服务端包含一个插件库，可以自定义安装各类插件。connection_control 插件也是其中一种，主要用来控制客户端在登录操作连续失败一定次数后的响应的延迟。该插件可有效的防止客户端暴力登录的风险。：用来控制登录失败的次数及延迟响应时间。：将登录失败的操作记录至 information_schema系统库中。连接控制插件文件的基本名称为 connection_control。

默认值为MEDIUM。: 要求密码包含数字、特殊字符、小写字母和大写字母的组合。插件的密码策略可以通过 MySQL 的系统变量。: 允许较弱的密码，只要密码长度足够即可。: 要求密码包含数字、特殊字符和字母的组合。然后重启数据库，将其改为STRONG。

然后设置每天自动备份的定时任务。

前提：该实验的前提是回收站的变量已开启：要确保recycle_bin的变量是on。

当给某个表加字段的时候，提示报错：Invalid default value for ddate查看了下这个表的表结构，发现ddate这个字段的定义是ddate datetime not null default '0000-00-00 00:00:00'

需求：搭建一个新的从库，只复制过滤源端数据库里的其中一个数据库workflow到新实例上。

但这种方法看不出来具体是哪里不一致，要想看具体哪里不一致，可使用percona tool kit的pt-table-checksum比对，使用pt-table-sync来发现及同步不一致的数据。可以使用checksum table比对mysql主从表数据是否一致。确认主从上输出的值是否完全一致，若一致，则说明主从数据一致。checksum table 表名;

写了一个往mysql里导数据的脚本(mysql -u root < sql文件这种方式），然后用nohup调用这个脚本，结果却报错： 将导数据脚本改成source这种方式(mysql -u root -e "source sql文件"也报错:使用screen。执行screen命令开启了一个新的窗口，在新的窗口里执行nohup 导数据的脚本，然后按ctrl+a+d退出该会话，后来看日志就没那个报错了--常用screen命令

有次遇到一个执行得很慢的大事务，就把它kill掉了，但是发现show processlist;还是能看到kill的sql，sql的状态虽然变为了killed，但是没有真正杀掉，这个时候可以看下事务回滚的进度，能大致了解到该sql什么时候能回滚完。select * from information_schema.INNODB_TRX where trx_id=事务id \G;会看到trx_rows_modified值不断减少，直到其变为0，表示回滚完毕。

这里想为现有的一个数据库新建一个从库，但只复制其中一个库。' identified by '密码';#重启从实例，使参数生效。

给表建索引的时候报了这个错：Incorrect key file for table 'pub_user_app_test'; try to repair itREPAIR TABLE pub_user_app_test我当时执行报错：The storage engine for the table doesn't support repair看了下这个表的存储引擎，是innodb，于是执行了如下sql得以修复：ALTER TABLE pub_user_app_test ENGINE=INNODB;

tuning-primer是mysql的一个优化工具，针于mysql的整体进行一个体检，对潜在的问题，给出优化的建议。将上述页面的内容粘贴进来。

mysqltuner是mysql一个常用的数据库性能诊断工具，主要检查参数设置的合理性包括日志文件、存储引擎、安全建议及性能分析。针对潜在的问题，给出改进的建议，是mysql优化的好帮手。

-这里一次展示不全，分两次截的图。

连续执行同样的 SQL 数次，就可以快速构造千万级别的数据。

derived table中文译为派生表，关于派生表的含义，翻阅了MySQL的官方手册，并没有找到相对应的解释，不过在SQL92标准中有对它进行定义，原文如下解释为：派生表为直接或者间接的通过一个查询表达式从一个或者多个表中得到的表。某种意义上来讲，MySQL中的视图也是派生表。举个例子：在如下SQL语句中，表A即为派生表。derived_merge指的是一种查询优化技术，作用就是把派生表合并到外部的查询中，提高数据检索的效率。

我们在日常维护数据库的时候，如果遇到慢语句查询的时候，我们一般会怎么做？执行EXPLAIN去查看它的执行计划？是的。我们经常会这么做，然后看到执行计划展示给我们的一些信息，告诉我们MySQL是如何执行语句的。BUT，执行计划往往只给我们带来了最基础的分析信息，比如是否有使用索引，还有一些其他供我们分析的信息，比如使用了临时表、排序等等。我们能从这些信息里面找一些优化点，这样就足够了吗？看看这张图里的执行计划，我们可以提很多问题：为什么t2表上明明使用了索引在Extra列中还是能看到temp

SQLAdvisor是由美团点评公司开发维护的一个分析SQL给出索引优化建议的工具。它基于MySQL原生态词法解析，结合分析SQL中的where条件、聚合条件、多表Join关系 给出索引优化建议。目前SQLAdvisor在美团点评内部广泛应用。

利用binlog2sql恢复mysql数据

如果sql文件编码不是UTF8的，有时会出现中文乱码，而且调用存储过程可能会报错：MySql.Data.MySqlClient.MySqlException (0x80004005): Illegal mix of collations for operation 'concat'。所以需要确保sql文件的编码是UTF8格式。

mysql 将毫秒数转为时间字符串

#创建测试数据库create database csl_test character set utf8 collate utf8_unicode_ci;#创建测试表注：要分区的字段 需要为主键。use csl_test; # 切换到刚刚创建的测试数据库create table t_partition_test ( pk_id bigint(20) not null auto_increment, time datetime not null, msg varchar(2...

一 问题描述在mysql中创建主键报错。#sqlALTER TABLE t1 ADD PRIMARY KEY(id,NAME);Data truncated for column 'name' at row 3二 出错原因name存在空值三 解决办法将其设置为空，然后再次创建复合主键--本篇文章参考了MySQL中 Data truncated for column 'xxx'解决方法_weixin_30316097的博客-CSDN博客...

一 安装mysql 8.0 yum源cd /etc/yum.repos.d/curl https://repo.mysql.com//mysql80-community-release-el7-3.noarch.rpm > centos7.mysql.rpmyum install centos7.mysql.rpm-y看到/etc/yum.repo.d目录下生产了以下两个文件：-rw-r--r-- 1 root root 2108 Apr 25 2019 mysq...

yum install -y perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes perl-ExtUtils-MakeMaker perl-Digest-MD5wgethttp://www.percona.com/downloads/percona-toolkit/2.2.13/tarball/percona-toolkit-2.2.13.tar.gztar -zxvpf percona-toolkit-2.2.13.tar.gzcd per...

一 问题描述 local_infile变量为ON，表示能使用load data local infile命令：该设置允许用户从客户端把文件加载进数据库表中，存在访问控制漏洞。因此，需要禁用该变量。二 禁用方法set global local_infile=off;vi my.cnf在[mysqld]下添加一行：local_infile=off三 注意事项global local_infile=off只是禁用了load data local infile，不影.

一 问题描述MariaDB [baidd]> select * from t1 into outfile '/var/lib/mysql/t1.txt';Query OK, 1 row affected (0.000 secMariaDB [baidd]> load data infile '/var/lib/mysql/t1.txt' into table t2;ERROR 1261 (01000): Row 1 doesn't contain data for all co...

一 问题描述同事反馈执行创建用户报错。sql：CREATE USER nrms@'%' IDENTIFIED BY '密码';报错：operation create user failed二 出错原因之前用delete方式删除过该用户，因此无法再使用create user这种方式创建用户三 解决办法#使用drop user删除，再create userMariaDB [(none)]> drop user nrms@'%';Query OK, 0 rows a

一 问题描述Lock tables 表名 write会阻塞select，那怎样查找是谁执行的Lock tables呢？示例：USE baidd;LOCK TABLE t1 WRITE;在另一个会话里执行select就被卡住了。所以此时想查出是谁执行的lock tables，然后将其进程杀掉。二 解决办法Mysql 5.7是performance_schema.metadata_locks能实现这一功能。我本地测试的mysql版本是5.7.31。2.1 开启

一 问题描述查看binlog里内容，发现无法正常查看：mysqlbinlog --no-defaults -v master-bin.000543 > baidd_543.log报错：ERROR: Error in Log_event::read_log_event(): 'Event truncated', data_len: 1413827653, event_type: 109ERROR: Could not read entry at offset 946675767: Er

一 问题描述执行一个普通的update报错：ERROR 1205 (HY000): Lock wait timeout exceeded二 排查思路查看是否有阻塞：SELECT p2.`HOST` Blockedhost,p2.`USER` BlockedUser,r.trx_id BlockedTrxId, r.trx_mysql_thread_id BlockedThreadId, TIMESTAMPDIFF...

一 问题描述机房搬迁后启动从库，数据库起来了，但是从库复制报错：mysql> show slave status \G;*************************** 1. row *************************** Slave_IO_State: Master_Host: 172.16.28.90 Master_User: rep_sa

一 安装MHA可参考https://blog.csdn.net/yabingshi_tech/article/details/47341705二 安装ProxySQL2.1 安装ProxySQL[root@slave2 ~]# rpm -ivh proxysql-2.0.5-1-centos7.x86_64.rpm点击下载[root@slave2 ~]# proxysql --version[root@slave2 ~]# systemctl start proxysql...