MYSQL 面试常见的问题集合
一、MYSQL 的复制原理及流程
(1) 主: binlog线程一一记录下所有的改变的数据库的语句,放进master上binlog中
(2) 从: io线程使用start salave 之后,负责从master上拉取binglog内容,放进自己的relay log中
(3) 从: sql执行线程一一执行relay log中的语句。
二、 mysql 中myIsam 与innodb 的区别
(1)5 点不同
1-> innodb 支持事务,而MyISAM不支持
2-> innodb z支持行级锁,而MyISAM 支持表级锁
3-> innodb 支持mvcc,而MyISAM 不支持。
4-> innodb 支持外键,而MyISAM不支持。
5-> innodb 不支持全文索引,而MyISAM 支持
(2) innodb 引擎四大特性
插入缓存(insert buffer) 、二次写(double write )、自适应哈希索引、预读
(3) 2者 select count(*) 哪个更快,为什么?
myisam更快,因为myisam内部维护一个计数器,可以直接调取。
三、 innodb的事务与日志的实现方式
(1) 多少种日志
错误日志: 记录出错信息,也记录一些告警信息或者正确信息。
查询日志: 记录所有对数据库请求信息,不论这些请求是否得到正确执行。
慢查询日志:设置一个阈值,将运行时间超过改值所有sql语句都记录到慢查询的日志中。
二进制日志: 记录对数据库执行更改的所有操作。
中继日志
事务日志
(2) 事务的4种隔离级别
读为提交(RU)
读已提交(RC)
可重复读(RR)
串行
(3) 事务如何通过日志来实现的?
事务日志是通过redo和innodb的存储引擎日志缓冲(innodb log buffer)来实现,当一开始一个事务的时候,会记录该事务的lsn号,当事务执行时,
会往innodb存储引擎插入事务日志;
当事务提交时,必须将存储引擎的日志缓冲写入磁盘(通过innodb_flush_log_at_trx_commit 来控制),也就是写数据前,需要先写入日志。
这种方式成为‘预写日志方式’
五、. bin log的几种日志录入格式及区别?
1. statement: 每一条会修改数据的sql都会记录在bin log 中。
优:不需要记录每一行的变化,减少binlog日志量,节约IO,提高性能。
缺点: 只是记录的执行语句,为了这些语句能在slave上正确运行,因此还必须记录每一条语句在执行的时候一些相关信息,以保证所有语句在selve
和master 端执行的时候有一样结果,一些特定函数不支持复制。
2. row 格式
有点:binlog中可以不记录执行的sql语句的上下文相关信息,仅需要记录那一条记录被修改生成什么样子。
缺点:所有的执行的语句当记录到日志中的时候,都将以每行记录的修改来记录,这样会产生大量日志内容。
3. Mixedlevel :
是以上两种level的混合使用,一般的语句修改使用statement格式保存binlog。如一些函数,statemen无法完成主从复制的操作,则采用
row格式保存binlog中。mysql会根据执行的每条具体的sql语句来区分来对待记录日志的形式。
六、 左连接、右连接、内连接有什么区别
1.内连接只显示两表中有关联的数据
2.左连接显示左表所有数据,右表没有对应的数据用NULL补齐,多了的数据删除
3.右连接显示右表所有数据,左表没有对应的数据用NULL对齐,多了的数据删除
七\ Mysql的innodb如何定位锁定问题,mysql如何减少主从复制延迟?
1 mysql的innodb如何定位锁问题?
在使用show engine innodb status检查引擎状态,发现死锁的问题
2 mysql 如何减少主从复制延迟?
主从复制延迟较大的原因:
1. 从库硬件比主库差,导致复制延迟
2.主从复制单线程,如果主库写并发太大,来不及传送到从库
3. 慢SQL语句过多
4. 网络延迟
5. master 负载
主库读写压力大,导致复制延迟,架构前端要加buffer级及缓存层
6. slave 负载
使用多台slave 来分摊读请求,再从这些slave中去一台专用服务器。
如果只做备份,不进行其他操作的话,2个减少延迟的参数:
-slave-net-timeout=seconds 单位秒,默认3600秒
含义:当slave 从数据库读取log数据失败后,等待多久重新建立连接并获取数据
-matser-connect-retry=seconds 单位为秒默认60s
含义:当重新建立主从连接时,如果连接建立失败,隔多久重试
msyql数据库主从同步延迟解决方案:
对简单减少slave同步延时方案在架构上做优化,尽量让主库的DDL快速执行。
主库配置
sync_binlog=1
innodb_flush_log_at_trx_commit=1
1、DDL语句 数据库定义语言: 数据库、表、视图、索引、存储过程,例如CREATE DROP ALTER
2、DML语句 数据库操纵语言: 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT
3、DCL语句 数据库控制语言: 例如控制用户的访问权限GRANT、REVOKE
八、 如何重置mysql root密码?
1. mysqladmin
2. mysq环境中
update mysql.user set password=password("新密码") where user='root';
3 在mysql环境中:
grant all on *.* to root @'localhost' identified by '新密码';
4 忘记root密码
1. 在my.cnf 添加 --skip_grant-tables
[mysqld_safe]
--skip_grant-tables
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
2. 重启 mariadb
systemctl restart mariadb.service
3. 进入mariadb 修改root密码
use mysql
UPDATE user SET password=password('wb513692') WHERE user='root';
flush privileges
exit
九、 binlog 如何进行恢复数据
9.1 查看binlog日志状态
show variables like ‘%log_bin%’;
9.2 binlog 日志分类
Binlog日志包括两类文件;第一个是二进制索引文件(后缀名为.index),第二个为日志文件(后缀名为.00000*),记录数据库所有的DDL和DML(除了查询语句select)语句事件
9.3 查看所有binlog日志文件列表:show master logs;
9.3 查看最后一个binlog日志的编号名称及其最后一个操作事件pos结束点的值:show master status;
9.4 Flush logs 刷新日志,此刻开始产生一个新编号的binlog文件
每当mysqld服务重启时,会自动执行刷新binlog日志命令,mysqldump备份数据时加-F选项也会刷新binlog日志
9.5 清空所有binlog日志命令:reset master;
9.6 查看binlog文件内容,使用查看工具mysqlbinlog来查看
9.7 binlog来恢复部分数据
#mysqlbinlog mysql-bin.000009|grep -B 10 -A 10 "tom"
按照position 来恢复数据
/usr/bin/mysqlbinlog --start-position=187416 --stop-position=187662 --database=ceshi /var/lib/mysql/mysql-bin.000009 | /usr/bin/mysql -uroot -p密码;
按照时间
/usr/bin/mysqlbinlog --start-datetime="2018-04-27 20:58:18" --stop-datetime="2018-04-27 20:58:35" --database=test /var/lib/mysql/mysql-bin.000009 | /usr/bin/mysql -uroot -p密码
九、 mysql 优化配置
本文来自博客园,作者:鱼丸粗面没鱼丸,转载请注明原文链接:https://www.cnblogs.com/Robi-9662/p/12442418.html