优化mysql数据导入速度
1.数据刷盘策略调整：
sync_binlog,innodb_flush_log_at_trx_commit,slow_query_log
set global sync_binlog = 0;
set global innodb_flush_log_at_trx_commit = 0;
set global slow_query_log = off;
2.修改脚本为并行加载
a)insert into table xxx values(xx,xx,xx),(xx,xx,xx)...
b)begin;...;commit;
awk '{if(NR%5000!=0)ORS=" ";else ORS="\n"}1' data.sql > data1.sql
sed 's/^/begin;/' data1.sql > data2.sql
sed 's/$/commit;/' data2.sql > data3.sql
source data3.sql
3.参数还原
set global sync_binlog = 1;
set global innodb_flush_log_at_trx_commit = 1;
set global slow_query_log = on;
mysql 远程执行脚本
nohup mysql -uroot -hxxx.com -p -P3306 -Dschema_name -e "source /tmp/load.sql" >> /tmp/nohup.txt
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';
死锁、慢查询相关查询
显示慢查询状态及日志目录
SHOW GLOBAL VARIABLES LIKE '%slow_query_log%';
开启慢查询(说明: 1开启;0关闭;)
SET GLOBAL slow_query_log = 1;
显示慢查询阈值(单位秒)，默认执行时间超过10s才会被记录到日志
SHOW VARIABLES LIKE '%long_query%';
设置慢查询阈值 (注意:设置后需要重新打开mysql客户端才能到最新的值)
SET GLOBAL long_query_time = 2;
查看死锁的日志是否开启
SHOW GLOBAL VARIABLES LIKE "%innodb_print_all_deadlocks%";
开启记录死锁
SET GLOBAL innodb_print_all_deadlocks=1
InnoDB 行锁等待超时时间(默认为50秒)
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout'; -- 查看当前会话
SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout'; -- 查看全局
设置
获取死锁日志信息
SHOW ENGINE INNODB STATUS
查询sql mode
select @@sql_mode;
查询 MyISAM 引擎的表
SELECT table_catalog
,table_schema
,table_name
,ENGINE
FROM information_schema.tables
WHERE table_schema='xxx'
AND ENGINE='MyISAM';
查询一个库或者表的大小
SELECT CONCAT(ROUND(SUM(DATA_LENGTH/1024/1024),2),'M')
FROM information_schema.tables
WHERE table_schema='ss'
AND table_name='tt';
查询各表的总记录行数
SELECT table_name,table_rows
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'xxx'
ORDER BY table_rows DESC
查询表的分区情况
SELECT
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
FROM information_schema.partitions WHERE
table_schema = 'sss'
AND table_name='ttt';
创建、使用全文索引（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