MySQL

information_schema

外键信息

数据库 dbname 中的所有外键信息：

SELECT CONSTRAINT_NAME, a.TABLE_NAME, REFERENCED_TABLE_NAME, COLUMN_NAME, REFERENCED_COLUMN_NAME FROM `KEY_COLUMN_USAGE` a join `TABLE_CONSTRAINTS` b using (CONSTRAINT_NAME) where b.CO
NSTRAINT_TYPE = 'FOREIGN KEY' and b.TABLE_SCHEMA = 'dbname';

大小查询

查询表大小

SELECT 
    table_name AS `Table`, 
    round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
WHERE table_schema = "$DB_NAME"
    AND table_name = "$TABLE_NAME";

显示各数据库的大小

SELECT 
    table_schema AS `Database`, 
    round((sum(data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
GROUP BY table_schema;

表的最后修改时间

select TABLE_NAME,UPDATE_TIME from INFORMATION_SCHEMA.tables where TABLE_SCHEMA='$DB_NAME';

所有表名

mysql ... -e "select concat(TABLE_SCHEMA, '.', TABLE_NAME) name from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'"

授权

创建用户

create user username identified by 'password';

更改密码

set password for 'username'@'host' = password('newpassword');

授权

grant all on db.* to 'user'@'host' identified by 'pwd';

撒消授权

revoke all on db.* from 'user'@'host';

刷新权限

flush privileges;

显示授权信息

show grants for user;

列出用户

SELECT user, host FROM mysql.user;

在禁用权限的情况下为用户授权

在使用--skip-grant-tables参数禁用权限的情况下，普通的授权指令均无效。这时可以使用如下方法来为用户授权：

REPLACE INTO mysql.user SET user='root', host='localhost', password=PASSWORD('123456'), create_priv='Y', drop_priv='Y', grant_priv='Y';

性能

启用慢查询日志

-- 显示当前设置
select @@slow_query_log; -- 默认关闭
select @@long_query_time; -- 默认 10s
select @@slow_query_log_file; -- 慢查询日志文件位置
-- 设置
set global slow_query_log = 1;
set global long_query_time = 0.01; -- 10ms

也可以用show variables like '%query%';来查看这些设置。

ALTER TABLE

ALTER TABLE通常会复制然后删除表（在 PostgreSQL 中并不是这样）。

对于某些操作，可以指定原地修改表：

ALTER TABLE star.star ADD COLUMN tags varchar(512) NOT NULL DEFAULT '', ALGORITHM=inplace, LOCK=none;

使用 LOCK=SHARED 来在更改表的过程中禁止写入。有时会加字段时会对自增字段返回 Duplicate entry xxx，此时可以提升锁的级别。

ROLLBACK

• ROLLBACK（相比COMMIT）很慢

安装与启动

启动一个新实例

注意写对参数；写错时仅仅会不使用，并不会报错。

mysqld_safe --defaults-file=/etc/my3307.cnf &

Percona 初始化密码

获取临时密码：

journalctl -u mysqld -n 1000 | grep generated

设置新密码（需要有大小写、数字、符号，还不止一个）：

set password for 'root'@'localhost' = password('xxx');

主从复制

在主上，显示从的信息：

show slave hosts;

在从上，显示主的信息：

show slave status\G

主从一致检查

使用 pt-table-checksum 检查数据库的主从一致性。

在目标数据库上创建用于检查的用户：

for i in 120 121 160 217 161 123 209 16; do
  host=192.168.1.$i
  echo $host
  ssh -t $host mysql -uroot -ppass -S /data1/mysql/data/mysql.sock -e "\"create user 'checksums'@'192.168.1.%' identified by 'pass'; grant select, process, super, replication slave on *.* to 'checksums'@'192.168.1.%'; grant all privileges on percona.checksums to 'checksums'@'192.168.1.%';\""
done

然后对主执行 pt-table-checksum 命令：

pt-table-checksum -h 192.168.1.120 -uchecksums -ppass --no-check-binlog-format

执行完毕之后，可以使用如下 SQL 在从库上检查不一致的数据段：

select * from percona.checksums where master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)\G

在所有机器上执行：

for i in 120 121 160 217 161 123 209 16; do
  host=192.168.1.$i
  echo $host
  ssh -t $host mysql -uroot -ppass -S /data1/mysql/data/mysql.sock -e "\"select * from percona.checksums where master_cnt <> this_cnt OR master_crc <> this_crc OR  ISNULL(master_crc) <> ISNULL(this_crc)\\G\""
done > result

添加从库

备份数据库

使用 innobackupex 备份（这样不用锁表导出）。这里只备份 db 这一个库

innobackupex --user=root --password=PASSWORD --socket=/data1/mysql/data/mysql.sock --databases=db backup-db

准备备份

innobackupex --apply-log --export backup-db/2016-06-01_17-00-49/

恢复数据库

在新机器上恢复备份（需要先关闭 MySQL，并删除 ibdata1、ib_logfile* 文件）

innobackupex --copy-back --force-non-empty-directories backup-db/2016-06-01_17-00-49
chown mysql: -R .../mysql/data

启动主从复制

从备份的 xtrabackup_binlog_info 文件找到复制的起始位置，然后启动主从复制

CHANGE MASTER TO MASTER_HOST='12.34.56.789', MASTER_USER='user', MASTER_PASSWORD='pass', MASTER_LOG_FILE='mysql-bin.abcdef', MASTER_LOG_POS=xxx;
start slave;

如果只复制指定的表，可以在 my.cnf 里这样设置（5.7.3 以后也可以用 SQL 来修改）

replicate-wild-do-table = db.%

问题处理

• 线上MYSQL同步报错故障处理总结

跳过复制失败的数据

stop slave;
set global sql_slave_skip_counter=1;
start slave;

管理

用户登录审计

INSTALL PLUGIN audit_log SONAME 'audit_log.so';
SET GLOBAL audit_log_policy = 'logins';

然后在 $datadir/audit.log 文件里就可以看到用户登录与退出的信息了（文件位置和格式都不能动态修改）。

注意事项

整数作为时间戳

TIMESTAMP类型与整数比较时，会将整数转成字符串之后再转为时间来比较，比如整数20141028000000会被认为是2014年10月28日凌晨零点。

时间戳精度

MySQL 5.6.4 开始支持微秒级精度，之前只支持到秒级。MySQL-Python 只支持秒级精度（存储时可能是微秒级精度）。

非BMP字符

MySQL 的utf8编码只允许三个字符。5.5 之后新出了最高四字节的utf8mb4编码。可以将字段的编码设置为此。一定要注意在处理数据前执行SET NAMES utf8mb4;，否则插入数据可能变为一串问号。

布尔类型实际上是 tinyint

MySQL 没有真正的布尔类型，表面上支持但实际上作为tinyint(1)对待和使用。

不支持 CHECK

MySQL 的所有引擎都只解析并忽略 CHECK 子句。

并发更新与删除

在默认的 repeatable read ISOLATION LEVEL下，并发删除数据能够成功执行。并且，读出来的东西被另一个事务删掉、自己这边再删掉之后还是可以读出来。Serializable 级别正常。

并发更新时也是如此。在 repeatable read 级别下，两个事务同时更新一条记录的字段（如update t set v = v + 1 where id = 2），更新都能够成功，但是条件会被重新求值（如同 PostgreSQL 的 read committed 级别）。同时，在被其它事务更新之后，本事务依旧能够读取到旧的结果（既看不到其它事务的影响，也看不到自己已执行部分的影响）。

参见PostgreSQL#并发更新与删除。

默认事务会自动提交

MySQL 默认会处于自动提交模式（即使在某些非交互环境下）。执行set autocommit = 0;来禁止这一点。如在 oursql 中要如下设置：

conn.cursor().execute('set autocommit = 0')

默认 binlog 格式能记录的操作十分有限

设置为mixed比较好：

SET GLOBAL binlog_format = 'MIXED';

客户端在本地默认连接默认的 socket 文件

MySQL 客户端在连接localhost（不指定时默认）时，总是会去连接默认的 socket 文件，即使指定了端口号也是如此。必须同时指定-h 127.0.0.1。

命令行客户端在 Ctrl-C 时退出

绕过方案是，使用--sigint-ignore参数来使之在Ctrl-C时什么都不做（甚至不清除已输入、还未执行的语句）。

没有默认值的非 NULL 整数字段默认为 0

MySQL [nsb]> create table t (myid int(11) not null);
Query OK, 0 rows affected (0.40 sec)

MySQL [nsb]> insert into t () values ();
Query OK, 1 row affected, 1 warning (0.08 sec)

MySQL [nsb]> show warnings;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1364 | Field 'myid' doesn't have a default value |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)

MySQL [nsb]> select * from t;
+------+
| myid |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

版本 5.5 不支持一个表多个 CURRENT_TIMESTAMP 字段

MySQL、Percona 5.5 会报错：

Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

已知 Percona 5.6 支持。

DDL 不支持事务

MySQL 在事务中遇到 DDL 时会自动提交。

mysql++ 连接时报没有 utf8mb4 字符集

有时客户端（MySQL 客户端 C 库版本：50537，SONAME 16）连接服务器时会报错：

Character set 'utf8mb4' is not a compiled character set and is not specified in the '/usr/share/mysql/charsets/Index.xml' file

升级 mysql-libs，或者改（configure 文件）为链接到更新的 libperconaserverclient 库即可。

字符串类型与整数字面量比较

在存储的数据为字符串、而查询时使用整数时，MySQL 会把数据库中的数据转为实数来处理。此时会忽略非数值的部分，并且不会使用索引。

mysql_safe 会忽略不认识的参数

如果不小心写错了参数（比如把 --defaults-file 写成单数），mysql_safe 会忽略该参数，导致使用了错误的配置。

所有权限不是所有权限

ALL PRIVILEGES 不包括 FILE, PROCESS, RELOAD, SHUTDOWN, 和 GRANT 权限。

工具

安装后的配置程序名为mysql_secure_installation，可用来设置 root 密码、是否允许匿名用户等。

mysql_install_db是用来初始化数据库的程序。

参见

• MariaDB
• MySQL的 C/C++ API
• SQL
• SQLite
• 命令行客户端 mysql
• pymysql
• MySQL Connector/Python
• oursql
• SQLAlchemy
• innobackupex

外部链接

• 正确重置MySQL密码 | 火丁笔记
• mysql命令行下用户管理«海底苍鹰(tank)博客
• What does "size" in int(size) of MySQL mean? » alexander kirk  » Blog Archive
• How to select the first/least/max row per group in SQL at Xaprb
• MySQL索引背后的数据结构及算法原理 - T2噬菌体 - 博客园
• MySQL: InnoDB 还是 MyISAM? | 酷壳 - CoolShell.cn
• MySQL中两种索引Hash与B-Tree的区别
• MySQL复制的概述、安装、故障、技巧、工具 | 火丁笔记
• MySQL "replace into" 的坑 - Xupeng's blog
• Mark S. Kolich - MySQL Triggers and SUPER Privileges: "Access denied; you need the SUPER privilege for this operation."
• Terrible choices: MySQL | ionel's codelog
• Galera Cluster Essential Guide - Fantasy

优化

• MySQL性能优化的最佳20+条经验 | 酷壳 - CoolShell.cn
• MySQL优化的奇技淫巧之STRAIGHT_JOIN | 火丁笔记

资源

• docker 镜像

管理

• gh-ost: GitHub’s online schema migration tool for MySQL - GitHub Engineering
• Context aware MySQL pools via HAProxy - GitHub Engineering