授权
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
FLUSH PRIVILEGES;
查询死锁
SELECT concat('kill ', id ,';')
FROM `information_schema`.`PROCESSLIST`
WHERE state='statistics';
创建、使用全文索引（MySQL 5.7+内置ngram插件支持中文）
需要先设置分词大小
[mysqld]
ngram_token_size=2
ALTER TABLE `posts` ADD FULLTEXT INDEX `idx_fulltext` (`markdown`) WITH PARSER ngram;
# 按自然语言搜索模式查询
SELECT * FROM posts WHERE MATCH (markdown) AGAINST ('关键词' IN NATURAL LANGUAGE MODE);
# 按布尔全文搜索模式查询
## 1 匹配既有管理又有数据库的记录
SELECT * FROM posts WHERE MATCH (markdown) AGAINST ('+数据库 +管理' IN BOOLEAN MODE);
## 2 匹配有数据库，但是没有管理的记录
SELECT * FROM posts WHERE MATCH (markdown) AGAINST ('+数据库 -管理' IN BOOLEAN MODE);
## 3 匹配MySQL，但是把数据库的相关性降低
SELECT * FROM posts WHERE MATCH (markdown) AGAINST ('>数据库 +MySQL' INBOOLEAN MODE);
随机查询记录
SELECT file_id
FROM `dc_resource_file` t1
JOIN (SELECT RAND() * (SELECT MAX(file_id) FROM `dc_resource_file`) AS nid
) t2
ON t1.file_id > t2.nid
LIMIT 1000;
分析性能
#SET profiling = 1;
## 列出最近的会话
SHOW PROFILES;
## 你可以给SHOW PROFILES指定一个Query_ID来查看指定的语句
SHOW PROFILE FOR QUERY xx;
## 还可以给输出添加新的列。如，查看用户和CPU使用
SHOW PROFILE CPU FOR QUERY 1;
查看索引散列程度 Cardinality
SHOW INDEX FROM user;
处理数据临时备份表，支持mysql 5.6、5.7
CREATE TABLE table_user_copy LIKE table_user;
INSERT IGNORE INTO table_user_copy
SELECT *
FROM table_user;
查询名为xxx的字段
SELECT column_name, table_name FROM information_schema.columns WHERE table_schema = 'xxx_db' AND column_name LIKE '%xxx%';
查询某一表的字段
SELECT CONCAT(COLUMN_NAME, ' AS ', COLUMN_NAME, ',')
FROM information_schema.COLUMNS
WHERE table_name = 'table_user'
AND table_schema = 'xxx_db'
ORDER BY ORDINAL_POSITION
查询外键依赖
SELECT
CONCAT(table_name, '.', column_name) AS 'foreign key',
CONCAT(
referenced_table_name,
'.',
referenced_column_name
) AS 'references'
FROM
information_schema.key_column_usage
WHERE table_schema = 'xxx_db'
AND referenced_table_name='table_user';
查询unix时间戳
SELECT UNIX_TIMESTAMP()
Select UNIX_TIMESTAMP('2006-11-04 12:23:00');
select FROM_UNIXTIME(1496200744);
查询表记录行数
SELECT table_name,data_length
FROM information_schema.tables
WHERE table_schema='xxx_db'
AND data_length>'9977856'
ORDER BY table_name ASC
#order by data_length desc
数据库设计结构导出
SELECT
c.TABLE_COMMENT `表中文名称`, c.table_name `表英文名称`,
IF(c.column_key='PRI', 1, 'NULL') `主键定义`,
c.`column_comment` `字段中文名`, c.column_name `字段英文名`,
c.data_type `字段类型`, IFNULL(c.CHARACTER_MAXIMUM_LENGTH, 'NULL')`字段长度`,
IF(s.`TABLE_CATALOG` IS NULL, 'N', 'Y') `是否索引`, c.`column_comment` `注解`
FROM
(
SELECT c.*, t.TABLE_COMMENT
FROM information_schema.COLUMNS c
INNER JOIN information_schema.tables t
ON t.TABLE_SCHEMA='db_xxx'
AND t.table_name IN ('table_a', 'table_b')
WHERE c.`TABLE_SCHEMA`='db_xxx'
AND c.`TABLE_NAME` IN ('table_a', 'table_b')
) c
LEFT JOIN
(SELECT *
FROM information_schema.statistics s
WHERE s.TABLE_SCHEMA='db_xxx'
AND s.table_name IN ('table_a', 'table_b')
) s
ON c.column_name=s.column_name
GROUP BY c.table_name, c.column_name
ORDER BY c.table_name, c.ORDINAL_POSITION
ghost blog 更新博文中图片、附件链接为 https
UPDATE posts SET markdown=REPLACE(markdown, 'http://ghost.oss.sherlocky.com', 'https://ghost.oss.sherlocky.com'),
html=REPLACE(html, 'http://ghost.oss.sherlocky.com', 'https://ghost.oss.sherlocky.com')
#select * from posts
WHERE LOCATE('http://ghost.oss.sherlocky.com', markdown)>0
mysql 数据一致性比较工具（可分析表数据内容一致性）
工具下载地址：
http://www.ttlsa.com/mysql/mysqldbcompare-compare-two-databases/
常见用法：
mysqldbcompare --server1=root:@192.168.1.121 --server2=root:@192.168.1.121 --changes-for=server1 --skip-row-count --difftype=sql db_xxx:db_yyy > C:/different_xxx.sql
binlog 恢复日志
可通过 mysqlbinlog 过滤某一时段的日志到一个临时文件，以方便分析。
/usr/local/mysql/bin/mysqlbinlog --base64-output=decode-rows -v --database=db_xxx --start-datetime='2019-01-16 08:00:00' --stop-datetime='2019-01-16 12:00:00' mysql-bin.000001 > /data/tmp/mysql-bin.000001_05.sql
日志文件过大可通过 linux grep、sed 等命令快速查看，参考grep & sed 搜索超大文本文件。
在线修改大表结构
可使用 pt-online-schema-change
归档历史数据
可使用 pt-archiver