MySQL 杂记

授权

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插件支持中文)

ALTER TABLE `dynamic_201606` ADD FULLTEXT INDEX `idx_user_name` (`user_name`);

select id,fnum,fdst from dynamic_201606 where match(user_name) against('zhangsan' in boolean 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 搜索超大文本文件