mysql生产趟坑中

数据库基础

https://blog.csdn.net/daduryi/article/details/88369860

时间字段是否适合建立索引

https://blog.csdn.net/hello_sgw/article/details/78601762

processlist

information_schema.processlist
mysqladmin -uuser -ppasswd processlist | grep template
http://blog.itpub.net/29371470/viewspace-2137391/

linux如何查看端口被哪个进程占用？

https://www.cnblogs.com/CEO-H/p/7794306.html

https://dev.mysql.com/doc/refman/5.6/en/mysqldump.html

Slave_SQL_Running: No mysql同步故障解决方法

https://blog.51cto.com/kerry/277414

mysql （master/slave）复制原理及配置

https://www.cnblogs.com/jirglt/p/3549047.html

mysql 数据类型:

https://www.cnblogs.com/mjbrian/p/6866263.html
https://www.cnblogs.com/usual2013blog/p/3747644.html

mysql有几种不同长度的text

TINYBLOB, TINYTEXT       L + 1 bytes, where L < 2^8    (255 Bytes)
BLOB, TEXT               L + 2 bytes, where L < 2^16   (64 Kibibytes)
MEDIUMBLOB, MEDIUMTEXT   L + 3 bytes, where L < 2^24   (16 Mebibytes)
LONGBLOB, LONGTEXT       L + 4 bytes, where L < 2^32   (4 Gibibytes)

mysql text字段为什么不能有默认值

两个统计查询

SELECT date(created_at), count(*) FROM coloro.wx_emoji_obs group by date(created_at);
SELECT date(created_at), count(distinct user_id) FROM coloro.wx_emoji_obs group by date(created_at);

什么是schemas? 和databases一样吗?

MySQL 中 Schema 等价于 数据库。[7]

CREATE SCHEMA是CREATE DATABASE的同义词。

mysql 删表

https://www.cnblogs.com/shuaiandjun/p/6042600.html

mysqldump source *.sql很慢

source 慢 https://blog.csdn.net/m0_37355537/article/details/80082194
https://blog.csdn.net/brazor/article/details/71629959
https://blog.csdn.net/codepen/article/details/52160715

跨schema迁移数据

1. https://yq.aliyun.com/ask/5787
2. mysqldump

    mysqldump -uuser -ppasswd -h10.111.1.1 -P3306 coloro table1 talbe2 table3 table3 table4> www_site.sql
    mysql -uuser -ppasswd www_site < www_site.sql

3. https://blog.csdn.net/finersoft/article/details/79927763

跨schema连表是可以的(同一个mysql server)

select A.id, B.id, B.user_id from www_site.cv_www_site_user as A join coloro.cv_www_site_video as B on A.id = B.user_id;

sql_mode模式

(group by报错? 太长截断报错? nn了但没default, create时不设值报错?)

sql_mode介绍 https://www.cnblogs.com/Zender/p/8270833.html
查看当前sql_mode https://www.cnblogs.com/zengkefu/p/5636614.html
修改sql_mode https://blog.csdn.net/a2459956664/article/details/77950575

#1055 - Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column this is incompatible with sql_mode=only_full_group_by
--------------------- 
ONLY_FULL_GROUP_BY

mysql 杀掉会话

mysql> source source.sql 执行完insert后,最后一步开锁遇到: Waiting for table metadata lock
https://blog.csdn.net/u013235478/article/details/68062939
https://blog.csdn.net/samHuangLiang/article/details/80994865

How large should be mysql innodb_buffer_pool_size?

https://dba.stackexchange.com/questions/27328/how-large-should-be-mysql-innodb-buffer-pool-size

MySQL主从复制面试之作用和原理

https://blog.csdn.net/darkangel1228/article/details/80004222

查看mysql binlog内容

https://blog.csdn.net/a1010256340/article/details/80306952

关于Mysql INT类型最大值问题

https://blog.csdn.net/liu510817387/article/details/7934131

MySQL删除数据后磁盘空间的释放总结

https://blog.csdn.net/jiangguilong2000/article/details/78423459
truncate table须知 https://yq.aliyun.com/articles/598071

主从同步一致性问题

https://blog.csdn.net/xmh594603296/article/details/82461222
https://www.cnblogs.com/KunLunSu/p/6826247.html
https://blog.csdn.net/yuanyuanispeak/article/details/52162885
1 半同步复制
2 数据库中间件（自己做）
3 缓存记录写key法（用内存数据库）
https://www.cnblogs.com/exceptioneye/p/5060967.html 一个看不懂的

将某个从库切换成主库且保证数据一致性？

MySQL中添加唯一约束和联合唯一约束

https://blog.csdn.net/yumushui/article/details/38960619

datetime 和 timestamp类型的区别

MySQL 使用 Workbench 建表时 PK NN UQ BIN UN ZF AI G 的含义

https://blog.csdn.net/lj402159806/article/details/78812667

快速查看innodb表的行数（不精确）

select * from information_schema.tables where table_name in (‘user’’) \G;
TABLE_ROWS列。

原子替换表(生产可用)

rename table user_logs to user_logs_old, user_logs_bak to userr_logs;

DDL DML DCL

https://www.cnblogs.com/zjero/p/5287865.html

mysql的undo log和redo log

https://www.cnblogs.com/wyy123/p/7880077.html

释放库空间

1. 删除无用的记录
2. 将记录无用大字段置空
3. optimize https://blog.csdn.net/hsd2012/article/details/51485250
   不optimize的情况下，删除记录，空间会被再次利用，置空text字段，待确认。 InnoDB默认不可用!

mysql 遇到了Invalid default value for 'created_at’的错误

https://blog.csdn.net/ch5057997/article/details/78540837

慢查询日志概念

https://www.cnblogs.com/saneri/p/6656161.html

表的水平拆分和垂直拆分

https://www.cnblogs.com/dwliu/p/6289598.html

MySQL数据库水平分表策略–一致性hash

https://blog.csdn.net/ydyang1126/article/details/70313981
https://blog.csdn.net/xiaobluesky/article/details/50429428
MySQL数据迁移工具的设计与实现 https://www.jianshu.com/p/afd2d9dee75b

mysql 5.7复制过滤CHANGE REPLICATION FILTER

https://blog.csdn.net/jolly10/article/details/80014181

help CHANGE REPLICATION FILTER
Syntax:
CHANGE REPLICATION FILTER filter[, filter]
	[, ...] [FOR CHANNEL channel]

filter:
    REPLICATE_DO_DB = (db_list)
  | REPLICATE_IGNORE_DB = (db_list)
  | REPLICATE_DO_TABLE = (tbl_list)
  | REPLICATE_IGNORE_TABLE = (tbl_list)
  | REPLICATE_WILD_DO_TABLE = (wild_tbl_list)
  | REPLICATE_WILD_IGNORE_TABLE = (wild_tbl_list)
  | REPLICATE_REWRITE_DB = (db_pair_list)

翻页优化

1. 延迟关联,通过使用覆盖索引, 优化limit 9000000, 10 的查询效率

   SELECT * from materials.cv_newalbum_material inner join (SELECT id FROM materials.cv_newalbum_material order by id desc limit 1000000, 1) as x using(id);

2. seq使用where翻页: 把id加到where里

MHA

https://www.docin.com/p-1066239808.html
https://www.cnblogs.com/gomysql/p/3675429.html

从库sql报错, 查看并跳过该事务

https://blog.csdn.net/seteor/article/details/17264633
https://blog.51cto.com/fccwcom/1180076

create role 和create user有区别

如何在MySQL中分配innodb_buffer_pool_size

https://www.cnblogs.com/wanbin/p/9530833.html