MySQL面试总结+MySQL知识点补充

• 什么是关系型数据库（关系模型）？
• 什么是非关系型数据库（NoSQL）？
• 关系型数据库与非关系型数据库之间有什么区别？
• 如何修改数据库管理员的密码？

mysqladmin工具
update user

• 忘记密码时如何跳过免密登录？
• MySQL的连接方式有哪些？

TCP/IP（通过网络的方式）
socket（套接字文件）

• MySQL的工作流程是什么样的？

连接层
SQL层
存储引擎层
文件系统

• MySQL插件式的存储引擎结果，支持多种不同的存储引擎，其中InnoDB和MyISAM有什么区别？

主要从以下几个方面考虑
事务
锁
数据管理
索引

• 如何查看默认存储引擎？

default_storage_engine

• SQL语句

用户管理
	创建用户
	修改密码
权限管理
	授权
	查看授权
	回收权限
表管理
	创建表
	插入记录
	查看表结构
表的约束规则
		NOT NULL
		Default
		UNIQUE
		PRIMARY KEY = UNIQUE + NOT NULL
		FOREIGN KEY
查询数据（重要）
	字段的优先级
	多表查询
		交叉连接
		内连接
		左连接
		右连接
		全外连接
	子查询
数据类型
	int
	float
	double
	date
	time
	datetime
	char
	varchar
	enum
	set
索引
	什么是索引？
	数据库中常用的逻辑上的索引有哪些？
	hash索引和b+树索引，各有其什么特定？
	说一说有哪些可以优化MySQL的方案？
事务
	ACID特性
		原子性
		一致性
		隔离性
		持久性
日志管理
	错误日志的作用及如何开启？
	慢日志的作用及如何开启？
	二进制日志

数据备份
	mysqldump

主从复制原理

• 如何查看mysql的连接

show processlist;
	如果MySQL变得很卡，首先就要show processlist查看是不是连接数太多了负载数太高了。 也有可能是两个人命令产生了冲突，产生了锁。
	{一个人A正在做慢查询，另一个人B正在做del改变表结构，改变表结构首先要结束表上的所有任务，等待表上的锁释放掉，因为ddel要加锁}
	这样就会导致其他人查也查不了，写也写不了，B执行的任务也执行不了.
	此时就需要show processlist；查看一下情况。

• 如何查看Linux连接

ss -tnl/netstat -tnl

• 公有云数据库

连接报错，连接有死锁，sql执行慢
需要出现问题之前把问题解决掉。
一个问题的生命周期：
出现问题（bug，某个版本）	-》 发现问题（客户反馈/自己反馈）	-》解决问题

• 传统数据库

自己搭建，自己维护，组里有人使用数据库问题会来找你。

二进制日志

备份恢复必须依赖二进制日志，主从环境必须依赖二进制日志。binlog是SQL层的功能，记录的是变更的sql语句，不记录查询语句。

• 开启二进制日志

在主配置文件中加一些东西
vim /etc/my.cnf
server_id=6 # 设置id
log_bin=mysql-bin # 开启并指定二进制日志目录及前缀名，记得关闭
selinux
binlog_format=row # binlog
的日志记录格式
修改完配置重启服务会自动生成二进制日志

• 日志记录方式
• SBR模式与RBR模式的对比（面试题）
  • SBR：可读性较高，日志量少，但是不够严谨
  • ROW:可读性很低，日志量大，足够严谨（建议使用）
• event事件简介
• 查看日志的开启情况
• 查看二进制日志文件数量
• 查看正在使用的二进制日志文件
• 日志内容查看
• binlog内容详细查看
• 基于position进行二进制日志截取

慢日志slow_log

慢日志文件是记录运行比较慢的sql语句，将这些sql语句记录下来以便进一步优化。可以自行补充一些关于慢日志的分析。

• 修改配置文件开启慢日志

备份与恢复

备份方式

• 按照备份数据位置分类
  • 热备：硬盘以及内存中的⼀些数据进⾏备份
  • 冷备：数据库硬盘中的数据
• 按照备份后文件的内容
  • 逻辑备份：⼀般内容分是sql语句
  • 裸⽂件备份：拷⻉数据库的物理文件，⼀般内容是⼆进制数据
• 按照备份数据的内容
  • 完全备份：对数据库进⾏⼀个完整的备份
  • 增量备份：在上次的完全备份基础上对更新的数据进⾏备份
  • ⽇志备份：⼆进制⽇志备份–>当数据库宕机之后进⾏数据恢复的依据
• 冷备份的优点
  • 备份简单，只要拷⻉相关⽂件即可
  • 易于跨平台
  • 恢复简单，只要把⽂件恢复到相关位置即可
  • 恢复速度快，不需要执⾏任何sql语句，也不需要重新建索引
• 冷备份的缺点
  • 冷备⽂件通常⽐逻辑⽂件⼤很多
  • 不是总可以轻易跨平台
• 逻辑备份
  • mysqldump
  • selcet * into outfile ‘path/to/file’ from tbname;

使用mysqldump工具备份

• mysqldump备份数据库的原理是把数据（包括库表）从MySQL库里以sql语句的形式直接输出或者生产备份文件的过程，这种备份成sql的方式称为逻辑备份。
  - 不带参数备份单个数据库
• 加-B参数备份多个数据库
• 使用gzip压缩备份数据
• 备份单个表或者多个表

恢复

• 将sql语句重新执行一遍，则完成数据的恢复
• 可以在客户端使用客户端工具运行sql脚本
• 可以在数据库服务器里面执行source命令

主从复制原理

备份服务器去同步主服务器，保证了冗余性。
io线程会去读取master上面的二进制日志文件binary_log，然后会写入到自己的中继日志Relay_log中。
SQL线程会定期的去执行中继日志里面的内容，达到主从复制的效果。

复制过程

1. 主服务器上任何的更新操作会被写入到二进制日志文件中
2. 从服务器上的IO线程：
3. 检测主服务器的二进制日志文件的变化
4. 同步主服务器的二进制日志文件到本地的中继日志中
5. 从服务器上的sql线程负责读取和执行中继日志中的sql语句

应用场景

• 一主多从会去对应读写分离
  • 写操作由主服务器
  • 读操作由服务器
• 主服务器上：drop ……
  • 要去做备份操作：在服务器上通过LVM快照进行备份
• 主服务器挂掉了，用户不可以进行写入操作
  • 高可用模型：多主架构
  • 任何一台服务器既是主服务器也是从服务器
• 高可用架构：MHA架构
  有两台服务器master和slave，从结点会开启两个线程I/Othread（会去读取master的二进制日志文件，把master上所有操作读出来之后会把其写到中继日志Relay log中）和SQL thread（SQL线程会去执行中继日志）。
  Relaylog：中继日志

实验

MHA架构

扩展自行研究此部分
简介：
MHA能够在较短的时间内实现自动故障检测和故障转移，通常在10-30秒以内;在复制框架中，MHA能够很好地解决复制过程中的数据一致性问题，由于不需要在现有的replication中添加额外的服务器，仅需要一个manager节点，而一个Manager能管理多套复制，所以能大大地节约服务器的数量;另外，安装简单，无性能损耗，以及不需要修改现有的复制部署也是它的优势之处。
MHA还提供在线主库切换的功能，能够安全地切换当前运行的主库到一个新的主库中(通过将从库提升为主库),大概0.5-2秒内即可完成。

工作流程：
1)把宕机的master二进制日志保存下来。
2)找到binlog位置点最新的slave。
3)在binlog位置点最新的slave上用relay log（差异日志）修复其它slave。
4)将宕机的master上保存下来的二进制日志恢复到含有最新位置点的slave上。
5)将含有最新位置点binlog所在的slave提升为master。
6)将其它slave重新指向新提升的master，并开启主从复制。

架构图：
案例：
主库相关配置

• 修改配置文件

[root@server2 ~]# cat /etc/my.cnf
[mysqld]
......
server_id =1
log_bin=mysql-bin
......
[root@server2 ~]# systemctl restart mariadb.service

• 创建主从复制用户

[root@server2 ~]# mysql -uroot -p1
MariaDB [(none)]> grant replication slave on *.* to rep@'192.168.80.%'
identified by '123456'

• 记录主库位置点

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 395 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

从库相关操作

• 修改从库配置文件

[root@server3 ~]# cat /etc/my.cnf
......
server_id =5
......
[root@server3 ~]# systemctl restart mariadb.service

• 从库开始连接主库

MariaDB [(none)]> change master to
master_host='192.168.80.129',master_port=3306,master_user='rep',master_passw
ord='123456',master_log_file='mysql-bin.000001',master_log_pos=395;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
......
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

• 验证结果

# 在主库上建库
MariaDB [(none)]> create database db1;
Query OK, 1 row affected (0.00 sec)
# 在从库上查库
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
+--------------------+

要变成双主双从就需要在原先从服务器上修改配置文件，加上log_bin=mysql-bin，并且再创建主从复制用户，记住从库的信息点status。在主服务器的数据库中根据从库中的信息点status连接从库库。
此时从服务器也相当于主服务器，主服务器也相当于从服务器。

MHA架构（高可用架构）

应用场景：
假如有两个从结点一个主结点，如果主结点done了，就需要从从节点中选出一个变成主结点继续去工作，这就叫做服务高可用，也叫做故障自动切换。
使用**MHA**或者**keepAlive**来进行如上活动。

MHA能够在较短的时间内实现自动故障检测和故障转移，通常在10-30秒以内;在复制框架中，MHA能够很好地解决复制过程中的数据一致性问题，由于不需要在现有的replication中添加额外的服务器，仅需要一个manager节点，而一个Manager能管理多套复制，所以能大大地节约服务器的数量;另外，安装简单，无性能损耗，以及不需要修改现有的复制部署也是它的优势之处。

MHA还提供在线主库切换的功能，能够安全地切换当前运行的主库到一个新的主库中(通过将从库提升为主库),大概0.5-2秒内即可完成。

工作流程：
1)把宕机的master二进制日志保存下来。
2)找到binlog位置点最新的slave。
3)在binlog位置点最新的slave上用relay log（差异日志）修复其它slave。
4)将宕机的master上保存下来的二进制日志恢复到含有最新位置点的slave上。
5)将含有最新位置点binlog所在的slave提升为master。
6)将其它slave重新指向新提升的master，并开启主从复制。
参考博客：https://www.cnblogs.com/fawaikuan
gtu123/p/10927888.html