MySQL收集

才不足则多谋，识不足则多虑；威不足则多怒，信不足则多言。 —— 清·陈宏谋《养正遗规》

一、常用

1. 忘记密码

   • 低版本

     • 修改my.cnf文件，在[mysqld]下添加一行skip-grant-tables
       • 关闭密码强度验证，在my.cnf中加入validate_password=off
     • 保存配置文件后，重启MySQL服务 service mysqld restart
     • 登入mysql，mysql -uroot -p,直接回车
     • use mysql
     • 修改密码
       • mysql5.6：update user set password=password(“root”) where user=”root”;
       • mysql5.7：update user set authentication_string=password(‘root’) where user=’root’ ;
     • 刷新权限flush privileges
     • 删除刚刚添加的skip-grant-tables行
     • 重启MySQL服务 service mysqld restart

   • mysql8

     • mysqld_safe –skip-grant-tables
     • 另起一个终端，mysql -uroot -p
     • alter user ‘root’@’localhost’ identified by ‘12345678’
     • flush privileges
     • exit

2. MySQL8中值得关注的新特性和改进。

   • 性能：MySQL 8.0的速度要比MySQL 5.7快2倍。MySQL 8.0 在以下方面带来了更好的性能：读/写工作负载、IO 密集型工作负载、以及高竞争（”hot spot”热点竞争问题）工作负载。
   • NoSQL：MySQL从5.7版本开始提供NoSQL存储功能，目前在8.0版本中这部分功能也得到了更大的改进。该项功能消除了对独立的NoSQL文档数据库的需求，而MySQL文档存储也为schema-less模式的JSON文档提供了多文档事务支持和完整的ACID合规性。
   • 窗口函数(Window Functions)：从 MySQL 8.0 开始，新增了一个叫窗口函数的概念，它可以用来实现若干新的查询方式。窗口函数与 SUM()、COUNT() 这种集合函数类似，但它不会将多行查询结果合并为一行，而是将结果放回多行当中。即窗口函数不需要 GROUP BY。
   • 隐藏索引：在 MySQL 8.0 中，索引可以被“隐藏”和“显示”。当对索引进行隐藏时，它不会被查询优化器所使用。我们可以使用这个特性用于性能调试，例如我们先隐藏一个索引，然后观察其对数据库的影响。如果数据库性能有所下降，说明这个索引是有用的，然后将其“恢复显示”即可；如果数据库性能看不出变化，说明这个索引是多余的，可以考虑删掉。
   • 降序索引：MySQL 8.0 为索引提供按降序方式进行排序的支持，在这种索引中的值也会按降序的方式进行排序。
   • 通用表表达式(Common Table Expressions CTE)：在复杂的查询中使用嵌入式表时，使用 CTE 使得查询语句更清晰。
   • UTF-8 编码：从 MySQL 8 开始，使用 utf8mb4 作为 MySQL 的默认字符集。
   • JSON：MySQL 8 大幅改进了对 JSON 的支持，添加了基于路径查询参数从 JSON 字段中抽取数据的 JSON_EXTRACT() 函数，以及用于将数据分别组合到 JSON 数组和对象中的 JSON_ARRAYAGG() 和 JSON_OBJECTAGG() 聚合函数。
   • 可靠性：InnoDB 现在支持表 DDL 的原子性，也就是 InnoDB 表上的 DDL 也可以实现事务完整性，要么失败回滚，要么成功提交，不至于出现 DDL 时部分成功的问题，此外还支持 crash-safe 特性，元数据存储在单个事务数据字典中。
   • 高可用性(High Availability)：InnoDB 集群为您的数据库提供集成的原生 HA 解决方案。
   • 安全性：对 OpenSSL 的改进、新的默认身份验证、SQL 角色、密码强度、授权。

3. 空字符串和null

   • 对比

     • MySQL空值是不占用空间，NULL是占用空间
     • count()统计某列的记录数，NULL值系统会自动忽略掉，空值会进行统计到其中
     • NULL判断用IS NULL或IS NOT NULL及ifnull()函数来进行处理，空字符判断用=或!=或<>来进行处理
     • 对于timestamp数据类型的字段，若插入NULL则出现的值是当前系统时间，插入空值则会出现0000-00-00 00:00:00
     • 对于空值的判断到底是使用is null还是=''要根据实际情况来进行区分

   • 注意事项

     • 任何值跟NULL做等值/不等值比较、加减乘数运算都为NULL
     • AVG():忽略NULL值，而不是将其作为0参与计算
     • COUNT(*):不忽略NULL值，COUNT(字段名):忽略NULL值
     • SUM():忽略NULL值，且当对多个列运算求和时，如果运算的列中任意一列的值为 NULL，则忽略这行的记录
     • GROUP BY:两个NULL值视为相同
     • 如果运行ORDER BY field ASC，则NULL值出现在最前，反之则NULL值出现在最
     • DISTINCT无论遇到多少个空值，结果中只返回一个NULL

4. 取1到10的第二大值

>建表
CREATE TABLE `test_tb1` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `val` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

>插入
INSERT INTO `test_tb1` VALUES (1, 1);
INSERT INTO `test_tb1` VALUES (2, 2);
INSERT INTO `test_tb1` VALUES (3, 3);
INSERT INTO `test_tb1` VALUES (4, 4);
INSERT INTO `test_tb1` VALUES (5, 5);
INSERT INTO `test_tb1` VALUES (6, 6);
INSERT INTO `test_tb1` VALUES (7, 7);
INSERT INTO `test_tb1` VALUES (8, 8);
INSERT INTO `test_tb1` VALUES (9, 9);
INSERT INTO `test_tb1` VALUES (10, 10);

>取结果
SELECT max(val) FROM test_tb1 
WHERE val NOT IN (SELECT max(val) FROM test_tb1)

5. Mac下无mysql配置文件my.cnf

   • 问题背景

     • 执行SELECT TRACE INTO outfile '/path/yourfile' FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;语句时报错：ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement，由此可判断出选项设置的原因。查看选项配置show variables like 'secure_file_priv';得知：secure_file_prive设为null表示限制mysqld不允许导入导出，设为/path/表示限制mysqld的导入导出只能发生在默认的/path/目录下，设为’’(空字符串)表示不对mysqld 的导入导出做限制。尝试通过命令行修改此选项set global secure_file_priv='';，报错ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable，后来得知需要通过修改配置文件my.cnf来实现。However，Mac下的MySQL5.7.26没有此文件(/etc/目录下)，只能手动创建。

   • 开干

     • 查看配置文件可存放位置mysql --help|grep my.cnf，会出现/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf四个路径
       • 或者mysqld --help --verbose | less
     • sudo mkdir -p /usr/local/mysql/etc
     • cd /usr/local/mysql/etc
     • sudo vim my.cnf

     [mysqld]
     secure_file_priv=''

     • 配置文件是ASNI/ASCII格式
     • sudo chmod 664 my.cnf
     • 重启MySQL
     • 查看show variables like 'secure_file_priv';是否更改成功

6. 在MySQL里，NULL跟任何值执行等值判断和不等值判断的结果，都是NULL，包括select NULL = NULL。

7. 三大范式五大约束：1NF是对属性的原子性，要求属性具有原子性，不可再分解；2NF是对记录的惟一性，要求记录有惟一标识，即实体的惟一性，即不存在部分依赖；3NF是对字段的冗余性，要求任何字段不能由其他字段派生出来，它要求字段没有冗余，即不存在传递依赖。

   • 三大范式
     • 第一范式(1NF)：数据表中的每一列(每个字段)必须是不可拆分的最小单元，也就是确保每一列的原子性。
     • 第二范式(2NF)：满足1NF后，要求表中的所有列都必须依赖于主键，而不能有任何一列与主键没有关系，也就是说一个表只描述一件事情。
     • 第三范式(3NF)：必须先满足第二范式(2NF)，要求表中的每一列只与主键直接相关而不是间接相关，(表中的每一列只能依赖于主键)。
   • 五大约束
     • PRIMARY KEY:主键约束
     • UNIQUE：唯一性约束
     • DEFAULT 默认值约束
     • NOT NULL：非空约束，该字段不能为空
     • FOREIGN KEY :外键约束

8. 常用命令

   • 建库 create database name [default charset utf8 collate utf8_general_ci];

   • 显示连接状态 show processlist;

   • 查看事务隔离级别 show variables like 'transaction_isolation'; (不同版本查的时候可能不一样)

     • show variables like ‘%_isolation’;

     Variable_name	Value	说明
     transaction_isolation	REPEATABLE-READ	>= 5.7
     tx_isolation	REPEATABLE-READ	<= 5.6

   • 设置事务隔离级别 set session transaction isolation level 级别;

     • read uncommitted : 读取尚未提交的数据
     • read committed：读已提交 — 可以解决脏读(oracle默认级别)
     • repeatable read：可重复读 — 可以解决脏读和不可重复读(mysql默认的)
     • serializable：串行化 — 可以解决脏读、不可重复读和幻读(相当于锁表)

   • 重建表 alter table A engine=InnoDB

     • 从 MySQL 5.6 版本开始，alter table t engine = InnoDB(也就是recreate)
     • analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了 MDL 读锁
     • optimize table t 等于 recreate+analyze

   • 重新统计索引 analyze table t

   • 查看表的相关信息 show table status

   • InnoDB引擎下对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加，最后返回累计值。

     • 按照效率排序：count(字段)<count(主键 id)<count(1)≈count(*)
     • count(主键 id)，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加
     • count(1)，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加
     • count(字段)，如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。
     • count()，并不会把全部字段取出来，而是专门做了优化，不取值。count() 肯定不是 null，按行累加。

   • 设置排序的行数据长度 SET max_length_for_sort_data = 16;

   • 关闭t表：flush tables t with read lock;

   • 关闭所有打开的表：flush tables with read lock;

   • 关闭自动提交事务 set autocommit=0;

9. 字符串

   字符(Character)是各种文字和符号的总称，包括各国家文字、标点符号、图形符号、数字等。字符集(Character set)是多个字符的集合，字符集种类较多，每个字符集包含的字符个数不同，常见字符集名称：ASCII字符集、GB2312字符集、BIG5字符集、GB18030字符集、Unicode字符集等。

   • 创建表

   create table rand(
       id varchar(255),
       name varchar(50),
       key(id)
   );

   • 插入数据

   insert into rand(`id`,`name`) values('CAMT-RQC-MNL-00098-REVA','张三'),('CAMT-RQC-MNL-00097-REVA','张二'),('CAMT-RQC-MNL-00099-REVA','张四');

   • 查询select * from rand order by id
   • 查看执行计划
   • 字符串索引
     • 直接创建完整索引，这样可能比较占用空间
     • 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引
     • 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题
     • 创建hash字段索引(crc32)，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描

10. SQL练习

    • 创建表

    • 学生表

    • 课程表

    • 教师表

    • 成绩表

      CREATE TABLE `student`(
      	`s_id` VARCHAR(20),
      	`s_name` VARCHAR(20) NOT NULL DEFAULT '',
      	`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
      	`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
      	PRIMARY KEY(`s_id`)
      );
      
      CREATE TABLE `course`(
      	`c_id`  VARCHAR(20),
      	`c_name` VARCHAR(20) NOT NULL DEFAULT '',
      	`t_id` VARCHAR(20) NOT NULL,
      	PRIMARY KEY(`c_id`)
      );
      
      CREATE TABLE `teacher`(
      	`t_id` VARCHAR(20),
      	`t_name` VARCHAR(20) NOT NULL DEFAULT '',
      	PRIMARY KEY(`t_id`)
      );
      
      CREATE TABLE `score`(
      	`s_id` VARCHAR(20),
      	`c_id`  VARCHAR(20),
      	`s_score` INT(3),
      	PRIMARY KEY(`s_id`,`c_id`)
      );

    • 初始化数据

    • 学生表

    • 课程表

    • 教师表

    • 成绩表

      insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
      insert into Student values('02' , '钱电' , '1990-12-21' , '男');
      insert into Student values('03' , '孙风' , '1990-05-20' , '男');
      insert into Student values('04' , '李云' , '1990-08-06' , '男');
      insert into Student values('05' , '周梅' , '1991-12-01' , '女');
      insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
      insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
      insert into Student values('08' , '王菊' , '1990-01-20' , '女');
      
      insert into Course values('01' , '语文' , '02');
      insert into Course values('02' , '数学' , '01');
      insert into Course values('03' , '英语' , '03');
      
      insert into Teacher values('01' , '张三');
      insert into Teacher values('02' , '李四');
      insert into Teacher values('03' , '王五');
       
      insert into Score values('01' , '01' , 80);
      insert into Score values('01' , '02' , 90);
      insert into Score values('01' , '03' , 99);
      insert into Score values('02' , '01' , 70);
      insert into Score values('02' , '02' , 60);
      insert into Score values('02' , '03' , 80);
      insert into Score values('03' , '01' , 80);
      insert into Score values('03' , '02' , 80);
      insert into Score values('03' , '03' , 80);
      insert into Score values('04' , '01' , 50);
      insert into Score values('04' , '02' , 30);
      insert into Score values('04' , '03' , 20);
      insert into Score values('05' , '01' , 76);
      insert into Score values('05' , '02' , 87);
      insert into Score values('06' , '01' , 31);
      insert into Score values('06' , '03' , 34);
      insert into Score values('07' , '02' , 89);
      insert into Score values('07' , '03' , 98);

• 查询”01”课程比”02”课程成绩高的学生的信息及课程分数

select a.*,b.s_score as 01_score,c.s_score as 02_score from student as a
join score b ON a.s_id=b.s_id and b.c_id='01'
join score c ON a.s_id=c.s_id and c.c_id='02' or c.c_id=null
where b.s_score>c.s_score

select a.*,b.s_score as 01_score,c.s_score as 02_score from student a,score b,score c
where a.s_id=b.s_id
and a.s_id=c.s_id
and b.c_id='01'
and c.c_id='02'
and b.s_score>c.s_score

• 查询”01”课程比”02”课程成绩低的学生的信息及课程分数

select a.*,b.s_score as 01_score,b.s_score as 02_score from student a
join score b on a.s_id=b.s_id and b.c_id='01'
join score c on a.s_id=c.s_id and c.c_id='02' or b.c_id=null
where b.s_score<c.s_score

select a.*,b.s_score 01_score,c.s_score 02_score from Student a,Score b,Score c
where a.s_id=b.s_id
and a.s_id=c.s_id
and b.c_id='01'
and c.c_id='02'
and b.s_score <c.s_score

• 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

select a.s_id,a.s_name,avg(b.s_score) avg_score from Student a
join Score b on a.s_id=b.s_id
group by a.s_id
having avg_score>=60

select b.s_id,b.s_name,ROUND(AVG(a.s_score),2) as avg_score from student b 
join score a on b.s_id = a.s_id
group by b.s_id,b.s_name 
having avg_score >=60;

• 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)

select b.s_id,b.s_name,ROUND(AVG(a.s_score),2) as avg_score from student b 
join score a on b.s_id = a.s_id
group by b.s_id,b.s_name 
having avg_score <60
union all
select a.s_id,a.s_name,0 as avg_score from student a 
where a.s_id not in (select distinct s_id from score);

• 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

select a.s_id,a.s_name,count(b.c_id),sum(b.s_score) from student a
join score b on a.s_id=b.s_id 
group by a.s_id

select a.s_id,a.s_name,count(b.c_id) as sum_course,sum(b.s_score) as sum_score from student a 
left join score b on a.s_id=b.s_id
group by a.s_id,a.s_name;

• 查询”李”姓老师的数量

select count(*) from teacher where t_name like '李%'

• 查询学过”张三”老师授课的同学的信息

select a.* from student a
join Score b on a.s_id=b.s_id
join Course c on b.c_id=c.c_id
join Teacher d on c.t_id=d.t_id
where d.t_name='张三'

select a.* from student a 
join score b on a.s_id=b.s_id 
where b.c_id in(
  select c_id from course where t_id =(
    select t_id from teacher where t_name = '张三'
  )
);

• 查询没学过”张三”老师授课的同学的信息

select a.* from student a  where a.s_id not in(
	select b.s_id from Score b where c_id in(
		select c_id from course where t_id in(
			select t_id from teacher where t_name='张三'
		)
	)
)

select * from student c where c.s_id not in(
	select a.s_id from student a 
	join score b on a.s_id=b.s_id
	where b.c_id in(
	  select a.c_id from course a 
		join teacher b on a.t_id = b.t_id 
		where t_name ='张三')
);

• 查询学过编号为”01”并且也学过编号为”02”的课程的同学的信息

select a.* from student a
join score b on a.s_id=b.s_id
join score c on a.s_id=c.s_id
where b.c_id='01' and c.c_id='02'

select a.* from student a,score b,score c 
where a.s_id = b.s_id 
and a.s_id = c.s_id 
and b.c_id='01' and c.c_id='02';

• 查询学过编号为”01”但是没有学过编号为”02”的课程的同学的信息

select a.* from student a 
where a.s_id in (select s_id from score where c_id='01') 
and a.s_id not in (select s_id from score where c_id='02')

• 查询没有学全所有课程的同学的信息

select s.* from student s 
left join score s1 on s1.s_id=s.s_id
group by s.s_id 
having count(s1.c_id)<(select count(*) from course)	

select * from student
where s_id not in(
  select s_id from score
  group by s_id 
  having count(*) = (select count(distinct c_id)  from course))

• 查询至少有一门课与学号为”01”的同学所学相同的同学的信息

select a.* from student a
left join score b on a.s_id=b.s_id
where b.c_id in(
	select c.c_id from score c where c.s_id='01'
)
group by b.s_id

select * from student where s_id in(
	select distinct a.s_id from score a where a.c_id in(
		select a.c_id from score a where a.s_id='01'
	)
)

• 查询和”01”号的同学学习的课程完全相同的其他同学的信息

select a.* from student a
where s_id in(
	select s_id from score
	group by s_id
	having count(s_id) = (
		select count(distinct(c_id)) from score where s_id='01'
	)
)
and a.s_id != '01'

• 查询没学过”张三”老师讲授的任一门课程的学生姓名

select a.* from student a
where s_id not in(
	select s_id from score where c_id in(
		select c_id from course where t_id in(
			select t_id from teacher where t_name='张三'
		)
	)
)

11. 备份恢复

    • 备份
    • 格式：mysqldump -h主机名 -P端口 -u用户名 -p密码 --databases 数据库名 > /path/文件名.sql
    • mysqldump -h 127.0.0.1 -P 3306 -uroot -p123456 –databases study > ~/study.sql
    • 恢复
    • 格式：mysql -h主机名 -P端口 -u用户名 -p密码 数据库名 < /path/文件名.sql
    • mysql -uroot -P3306 -p6NbAFQBE study < ~/study.sql
      • 恢复的前提是数据库study已建好，否则会报错

12. 建表原则

13. mysql inner join和left join性能对比

二、连接

1. timestampdiff函数

2. group_concat

3. mysql server gone away

4. MySQL主从复制延迟

5. MySQL基准测试

6. MySQL使用IN、EXISTS、ANY、ALL关键字的子查询

7. B+树中的非叶子节点会冗余一份在叶子节点中，并且叶子节点之间用指针相连

8. B+树中一个节点为一页或者页的倍数最合适

9. truncate与delete的区别是什么？truncate 是永久删出表中的每一行，且不可恢复

10. 什么是触发器？触发器是指一段代码，当触发某个事件时，自动执行这些代码，在mysql数据库中有如下6种触发器：

    • Before Insert
    • After Insert
    • Before Update
    • After Update
    • Before Delete
    • After Delete

11. char、varchar、text的区别：

    • varchar可以指定字符数，text不能指定
    • 内部存储varchar是存入的实际字符数+1个字节（n<=255）或2个字节（n>255）,text是实际字符数+2个字节
    • text不能有默认值,默认值为null
    • varchar可以直接创建索引，text创建索引要指定前多少个字符，varchar查询速度快于text，在都创建索引的情况下，text的索引几乎不起作用。
    • 查询text需要创建临时表

12. varchar(50)中50的含义：最多存放50个字符

    • varchar（50）和（200）存储“hello”所占用的空间是一样的，但是200在排序时会消耗更多内存。

13. 对于联合索引(col1,col2,col3)，查询语句SELECT * FROM test WHERE col2=2;是否能够触发索引？大多数人都会说NO，实际上却是YES。

    原因：

EXPLAIN SELECT * FROM test WHERE col2=2;
EXPLAIN SELECT * FROM test WHERE col1=1;

14. 索引的设计原则

    • 适合索引的列是出现在where字句中的列，或者连接字句中指定的列
    • 基数较小的列，索引效果差，没必要在此列建立索引
    • 使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间
    • 不要过度索引，占用额外的磁盘空间，并降低写操作的性能。在修改表能容的时候，索引进行更新甚至重构

15. mysql中in和exists区别

    • in在查询的时候，首先查询子查询的表，然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选。所以相对内表比较小的时候，in的速度较快。
    • 适合外大内小
    • exists:将主查询的数据，放到子查询中做条件验证，根据验证结果(TRUE 或者 FALSE)来决定主查询数据结果是否得到保留。
    • 适合外小内大

16. union和union all

    • union
    • 对两个结果集进行并集操作，不包括重复行，相当于distinct, 同时进行默认规则的排序
    • 会对获取的结果进行排序操作
    • union all
    • 对两个结果集进行并集操作，包括重复行，即所有的结果全部显示，不管是不是重复
    • 不会对获取的结果进行排序操作

17. 在数据库中如何优化？

    • 对查询进行优化，尽量避免全表扫描，首先应考虑在where 及order by 涉及的列上建立索引
    • 应尽量避免在where 字句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描
    • 应尽量避免在where字句中使用！=或<>操作符，否则将引擎放弃使用索引而进行全表扫描
    • 应尽量避免在where字句中使用or来连接条件，如果一个字段有索引，一个字段没有索引，将导致全表扫描
    • in 和 not in 也要慎用，否则会导致全表扫描
    • like 模糊全匹配也将导致全表扫描

18. 参数控制

    • show processList
    • wait_timeout

19. float和decimal

20. 单精度双精度

21. MySQL连接池

22. 数据库内核月报

23. 下载入口

24. 一文搞懂锁知识

25. MySQL深分页问题

26. Binlog in Redo

27. RadonDB MySQL

28. 自增ID

    • 查看自增id
    • select * from information_schema.tables where table_schema=’db_name’ and table_name=’table_name’\G;
    • show create table db_name.table_name;

29. [LevelDB、RocksDB]

30. [TiDB、TiSpark]

31. [DPDK]

32. 保证数据安全

    • 权限控制与分配(数据库和服务器权限)
    • 制作操作规范
    • 定期进行培训
    • 搭建延迟备库
    • 做好SQL审计，只要是对线上数据有更改操作的语句(DML和DDL)都需要进行审核
    • 做好备份。
    • (1)如果数据量比较大，用物理备份xtrabackup，定期对数据库进行全量备份，也可以做增量备份。
    • (2)如果数据量较少，用mysqldump或者mysqldumper，再利用binlog来恢复或者搭建主从的方式来恢复数据。
    • 如果发生了数据删除的操作，又可以从以下几个点来恢复:
    • DML误操作语句造成数据不完整或者丢失。可以通过flashback，美团的 myflash，也是一个不错的工具，本质都差不多，都是先解析 binlog event，然后在进行反转。把 delete 反转为insert，insert 反转为 delete，update前后 image 对调。所以必须设置binlog_format=row 和 binlog_row_image=full，切记恢复数据的时候，应该先恢复到临时的实例，然后在恢复回主库上。
    • DDL语句误操作(truncate和drop)，由于DDL语句不管 binlog_format 是 row 还是 statement ，在 binlog 里都只记录语句，不记录 image 所以恢复起来相对要麻烦得多。只能通过全量备份+应用 binlog 的方式来恢复数据。一旦数据量比较大，那么恢复时间就特别长
    • rm 删除：使用备份跨机房，或者最好是跨城市保存。

33. ERROR 1045 (28000): Access denied for user ‘dbreadonly’@’your_ip’ (using password: YES)

    • 权限问题
    • 未设置远程访问

34. 错误：

    • Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ‘database_name.table_name.batch_number’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

• SQLSTATE[22003]: Numeric value out of range: 1690 BIGINT UNSIGNED value is out of range in ‘(database_name.table_name.field - database_name.table_name.field)’,mysql当两个字段相减时，如果其中一个或两个字段的类型的unsigned无签名类型，如果相减的值小于0则会报错（BIGINT UNSIGNED value is out of range），详见
• Syntax error or access violation: 1059 Identifier name ‘idx_xxx_at_xxx_id_xxx_id_xxx_type_status’ is too long