前言

三个 mysql 日常高频小技巧：给新库整理数据字典、连接多个实例时防止在错误的库上执行 SQL、以及老库字符集不一致导致查询乱码的处理。

一、用 information_schema 生成数据字典

接手一个没有文档的数据库时，用 information_schema 可以一键导出数据字典。

1. 查询库下所有表

1
2
3
4
5

SELECT
  t.table_name 表名,
  t.table_comment 表说明
FROM information_schema.TABLES t
WHERE table_schema = 'your_db';

2. 查询字段明细

阅读全文

前言

mysql的表日积月累也会出现过千万的大表，有些历史数据可以定期归档清理的，保留最近的数据即可。但是delete table_name where id = ‘’;这种方式只是逻辑上的删除，不会释放表空间和索引的。因此需要在历史数据归档后做表分析才行

1、大表归档大量数据

先远程备份，可以开发成shell脚本，设置crontab定期备份

阅读全文

前言

三个日常高频的数据库锁问题合在一起：mysql 里怎么查「谁锁了谁」、Oracle 的锁会话处理（附节）、以及 DELETE 大量数据后表空间为什么不回收、怎么回收。都是拿来就能用的 SQL。

一、mysql 锁排查四连查

1. 查询是否锁表

1

SHOW OPEN TABLES WHERE In_use > 0;

2. 查询当前进程（看哪些会话在跑、哪些在等待）

1

SHOW PROCESSLIST;

3. 查看正在锁的事务

阅读全文

前言

主从复制是 mysql 高可用的基础：读写分离、故障切换、异地备份都建立在它之上。这篇整理主从搭建的完整步骤、状态检查要点，以及复制中断后的几种恢复手段——生产环境主从挂掉大多是「中断后不知道怎么安全地恢复」，这部分才是重点。

一、主库（Master）配置

1. 修改 my.cnf 开启 binlog

1
2
3

[mysqld]
log-bin    = mysql-bin
server-id  = 1

2. 重启后创建复制账号

阅读全文

前言

攒的一些 mysql 日常巡检命令和优化散记，合在一起记录。巡检部分用 show status/variables 就能完成，优化部分是建表和索引设计层面的经验，标注了适用版本。

1、日常巡检命令

1.1 最后一条语句的报错信息

1
2
3
4
5

-- 显示最后一个执行语句产生的错误、警告和通知
show warnings;

-- 只显示错误
show errors;

调试 SQL 时比看客户端提示更全，写存储过程排查尤其有用。

阅读全文

前言

排查历史数据问题时经常要翻 binlog：某个时间段数据到底被谁改成了什么样。mysqlbinlog 只能把 binlog 转成可读文本，想要按语句聚合统计（哪类语句最多、耗时分布），用 percona toolkit 里的 pt-query-digest 加上 --type binlog 就能对转出来的结果做分析。我的环境是 centos，亲测可用。

1、单独安装 pt-query-digest

不想装整套 percona toolkit 的话，单独装 pt-query-digest 就够了：

阅读全文

前言

收到数据库磁盘占比告警了
前提条件，磁盘采用lvm管理方式，做在线无损扩容才方便管理，其他方式没测试过

1、分配一个1T的新盘，HDD、SDD随规划选择

这个一般在磁阵或者云管理平台操作，不需要什么命令，一般都是图形化操作

2、查找新分配的1T新盘

1
2

fdisk -l     
lvmdiskscan

个人建议第二个命令，2个命令都会返回一堆查询值，怎么判断谁是新盘，谁是已使用的盘，这点有运维经验的人还是不成问题的，不详说

阅读全文

有些不靠谱的同事真的很误事，运维了几年的系统从来没出过误删表数据的事件，3月14日下午突然被同事误删了订单表全量数据，查询表返回0 rows，突然大脑一片空白，心想完了完了，最重要的表数据全部被清理了，虽然有每日凌晨5点的全量备份，但是截止下午的数据怎么找回？从来没碰到过误删事件，也从来没处理过数据恢复……

一、先挽救每日备份的数据

找到每日全量备份的数据，把订单表还原到测试库，然后单独把订单表重新备份，针对误删的表进行全量恢复 采用的工具是mydumper，非mysql官方的mydump 心想：好在有每日凌晨5点的全量备份，虽然在自己手里，从开发了每日全量备份脚本并配置定时任务每天执行，从来没派上用场，也希望不会派上用场，但是良好的备份意识还是挽救了90%的数据。 半小时左右，90%的数据恢复，然后把问题上报。

阅读全文

前言

Mydumper是一个针对MySQL和Drizzle的高性能多线程备份和恢复工具。开发人员主要来自MySQL,Facebook,SkySQL公司。性能比自带的mysqldump强劲
github: https://github.com/mydumper/mydumper

1、安装

redhat、centos举例

1.1 安装依赖

1.1.1 安装编译库

1

yum install -y cmake gcc gcc-c++ git make

1.1.2 安装开发库

1

yum install -y glib2-devel mysql-devel openssl-devel pcre-devel zlib-devel libzstd-devel

1.1.3 安装mysql开发版库

1
2
3

yum install -y mysql-devel
yum install -y Percona-Server-devel-57
yum install -y mariadb-devel

1.2 mydumper安装

1
2
3
4
5
6

wget https://launchpad.net/mydumper/0.9/0.9.1/+download/mydumper-0.9.1.tar.gz
tar zxvf mydumper-0.9.1.tar.gz
cd mydumper-0.9.1
cmake .
make
make install

2、常用备份、还原命令

2.1 备份

1

mydumper -u root -p XXX -h localhost -B XXX -o /data/backup/20180916

2.2 恢复

1

myloader -u root -p XXX -h localhost -B XXX -d /data/backup/20180916

3、参数说明

3.1 mydumper 的常用参数

3.2 myloader使用参数

4、metadata文件

metadata文件存储了binlog位置点和备份开始结束的时间

阅读全文

前言

如果不使用最新版本的mysql，但是低版本的mysql官方不提供编译好的安装包，只能自己编译安装了。以下步骤和参数针对5.6、5.7生效，新版本有变动请阅读官方文档介绍。

版本停更提示：5.6 已于 2021-02、5.7 已于 2023-10 结束官方支持（EOL），两者均不再有安全补丁，新项目请直接上 8.0/8.4 LTS。老库升级前注意 8.0 的行为差异：

……

阅读全文