mysql相关面试题

mysql面试题

=======================================================================

张贺，多年互联网行业工作经验，担任过网络工程师、系统集成工程师、LINUX系统运维工程师

笔者微信：zhanghe15069028807,现居济南历下区

=======================================================================

mysql面试题

笔试题

1.开启MySQL服务：

systemctl start mysqld

2.检测端口是否运行：

ss -tnlp | grep 3306

3.为MySQL设置密码或者修改密码：

update mysql.user set authentication_string passwd=('NEW-PASSWD') where user='root';
update user set authentication_string='' where user='root';

4.登陆MySQL数据库。

mysql -h HOST -P PORT -u USER -p PASSWD

5.查看当前数据库的字符集

show variables like '%char%';

6.查看当前数据库版本

select version();

7.查看所有用户和查看当前登录的用户。

select user,host from mysql.user;
select user();

8.创建GBK字符集的数据库oldboy，并查看已建库完整语句

create database oldboy default character set gbk collate gbk_chinese_ci;
create database oldboy default character set utf8 collate utf8_general_ci;

show create database oldboy\g;

9.创建用户oldboy，使之可以管理数据库oldboy

grant all on oldboy.* to oldboy@'localhost' identified by 'cba--123';

10.查看创建的用户oldboy拥有哪些权限

mysql>  show grants for 'oldboy'@'localhost';
+------------------------------------------------------------+
| Grants for oldboy@localhost                                |
+------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'oldboy'@'localhost'                 |
| GRANT ALL PRIVILEGES ON `oldboy`.* TO 'oldboy'@'localhost' |
+------------------------------------------------------------+

select * from mysql.user where user='root' \G;

11.查看当前数据库里有哪些用户

mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| oldboy        | localhost |
| root          | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)

12.进入oldboy数据库

use oldboy;

13.创建一innodb GBK表test，字段id int(4)和namevarchar(16)

mysql> create table test 
(
id int(4), 
name varchar(16)
)ENGINE=INNODB  DEFAULT CHARSET=utf8 ;

14.查看建表结构及表结构的SQL语句

mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(4)      | YES  |     | NULL    |       |
| name  | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
mysql> show create table test;
| test  | CREATE TABLE `test` (
  `id` int(4) DEFAULT NULL,
  `name` varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk |

15.插入一条数据“1,oldboy”。再次插入半条数据，也就是仅插入一个姓名即可。

insert into test values ("1","oldboy");
insert into test (name) values ('zhanghe')；

16.再批量插入2行数据

insert test (id,name) values 
("4","zhanghe"), 
("5","zhangji");

17.查询名字为zhanghe的记录

select * from oldboy.test where name="zhanghe";

18.把数据id等于1的名字oldboy更改为oldgirl

update oldboy.test set name="oldgirl" where id="2";

19.在字段name前插入age字段，类型tinyint(2)

alter table test add column age tinyint(2) after id;

20.不退出数据库,完成备份oldboy数据库

mysql> system mysqldump -B -uroot -p'cba--123' oldboy > /tmp/oldboy.sql;

21.先删除某一条数据，然后删除test表中的所有数据，并查看

delete from test where name='zhanghe';
delete from test;

22.删除表test和oldboy数据库

drop tables test;
drop database oldboy;

23.不退出数据库恢复以上删除的数据

mysql> system mysql -uroot -p123456 </opt/bak.sql

24.把库表的GBK字符集修改为UTF8。

mysql> alter database character set utf8;
mysql> alter table oldboy.test character set utf8;

25.把id列设置为主键，在Name字段上创建普通索引。

alter table test add primary key(id);
alter table test add index index_name(name);

26.在字段name后插入手机号字段(shouji)，类型char(11)。

alter table test add column shouji char(11) after name;

27.所有字段上插入2条记录（自行设定数据）

ysql> insert into test values 
    -> ('2','zhanghe','15564028007'),
    -> ('3','zhangjia','15069028007');

28.在手机字段上对前8个字符创建普通索引。

alter table test add index index_shouji(shouji(8));

29.查看创建的索引及索引类型等信息。

show index from test\G

30.删除Name，shouji列的索引。

mysql> alter table test drop index index_name;
mysql> alter table test drop index index_shouji;

31.对Name列的前6个字符以及手机列的前8个字符组建联合索引。

 create index index_name_shouji on test(name(6),shouji(8));

32.查询手机号以135开头的，名字为oldboy的记录（提前插入）。

mysql> select * from test where name='oldboy' and shouji like "135%";

33.查询上述语句的执行计划（是否使用联合索引等）。

 explain select * from test where name="oldboy" and shouji like "135%"\G

34.把test表的引擎改成MyISAM。

alter table test engine=myisam;

35.收回oldboy用户的select权限。

mysql> show grants for oldboy@'localhost';
+------------------------------------------------------------+
| Grants for oldboy@localhost                                |
+------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'oldboy'@'localhost'                 |
| GRANT ALL PRIVILEGES ON `oldboy`.* TO 'oldboy'@'localhost' 

revoke select on oldboy.* from oldboy@'localhost';

36.删除oldboy用户。

drop user oldboy@'localhost';
delete from mysql.user where user='oldboy';

37.删除oldboy数据库。

drop database oldboy；

38.使用mysqladmin关闭数据库。

mysqladmin -u root -pcba-123 -p shutdown

39.MySQL密码丢了，请找回？

vim /etc/my.cnf
skip-grant-tables
systemctl restart mysqld
update mysql.user set authentication_string passwd=('cba-123') where user='root';
flush privileges;
删除skip-grant-tables
systemctl restart mysqld 用新密码登录即可

40，查看所有的数据库

show databases;

41，查看所有的表

show tables;

面试题

请解释关系型数据库概念及主要特点？

关系型数据库是支持采用了关系模型的数据库，简单来说，关系模型就是指二维表模型，而一个关系数据库就是由二维表及其之间的联系所组成的一个数据组织。

最大的特点就是事务的一致性。其性能随着数据量的增涨而降低，IO读写是关系库一个瓶颈

请解释非关系型数据库概念及主要特点

使用键值对存储数据，且结构不固定
基于键值对，查询速度快
不提供SQL支持，学习和使用成本较高

memcache与redis的区别？

memcached仅运行在内存当中，数据无法持久化，支持的数据类型单一。

redis可以支持多种数据类型，而且数据可以持久化。

mysql语句的分类

DDL(Data Definition Language)---数据库定义语言（create、alter、drop)，管理基础数据库，例如：库，表
DCL(Data Control Language)---数据控制语言（grant、revoke、commit、rollback），用户授权，权限回收，数据提交回滚等
DML（Data Manipulation Language)---数据操作语言（select、insert、delete、update）,针对数据库里的表，记录

请详细描述char(4)和varchar(4)的差别。

char(4)定义的是固定长度4，存储时，如果字符数不够4位，会在后面用空格补全存入数据库。

varchar(4)定义的是变长长度，存储时，如果字符没有达到定义的位数4时，也不会在后面补空

如何加强MySQL安全，请给出可行的具体措施？

绑定IP

禁用匿名用户

严格控制权限

定期备份

删除test库和隐藏在家目录下的mysql_history文件

delete和truncate删除数据的区别

truncate table test执行更快，清空物理文件，清空表中的所有内容

delete from test是逻辑删除，按行删除，而且可以通过where语句选择要删除的行

MySQL Sleep线程过多如何解决

线程过多是因为数据库与客户端连接的超时时间太长了，默认改小一些；

mysql> show processlist\G
mysqladmin -uroot -p123456 processlist

修改my.cnf文件里的wait_timeout的值，让其更小一些，默认wait_timeout =28800，这里改为100

mysql> set global wait_timeout=100;
mysql> show global variables like "wait_timeout";

sort_buffer_size参数作用？如何在线修改生效？

mysql执行排序使用的缓冲大小。如果想要增加order by的速度，首先看是否可以让mysql使用索引而不是额外的排序阶段，如果不能，可以尝试增加sort_buffer_size变量的大小。

mysql> set global sort_buffer_size =131072;  #单位为B，即128KB，默认64K

如何在线正确清理MySQL binlog

自动清除：mysql> set global expire_logs_days=30; #设置binlog过期时间为30天

手动清除：mysql> purge binary logs to "mysql-bin.000007"; #/删除mysql-bin.000007之前的所有binlog日志

Binlog工作模式有哪些？各什么特点，企业如何选择？

1.row level行级模式

优点：记录数据详细（每行），主从一致

缺点：占用大量的磁盘空间，降低了磁盘的性能

2.statement level模式（默认）

优点：记录的简单，内容少，节约了IO，提高性能缺点：导致主从不一致

3.MIXED混合模式

结合了statement和row模式的优点，会根据执行的每一条具体的SQL语句来区分对待记录的日志形式。对于函数，触发器，存储过程会自动使用row level模式

企业场景选择：

1.互联网公司使用mysql的功能较少（不用存储过程、触发器、函数），选择默认的statement模式。

2.用到mysql的特殊功能（存储过程、触发器、函数）则选则MIXED模式

3.用到mysql的特殊功能（存储过程、触发器、函数），有希望数据最大化一致则选择row模式。

误操作执行了一个drop库SQL语句，如何完整恢复？

切断数据库与外部的联系
备份所有binlog日志先拿出来
恢复全备，在全备文件里面找到最后一个点。
把全备文件的最后一个点，当时是binlog恢复是的起点，然后跳过删除语句，将全备之后的binlog语句全部重新执行一遍。

主从复制原理：

主：binlog线程，记录所有改变了数据库数据的语句，放进master上的binlog中
从：IO线程，在使用start slave之后，负责从master上拉取binlog内容，放进自己的relay log中
从：SQL执行线程，执行relay log中的语句。

配置步骤：

主库开启binlog日志功能
全备数据库，记录好binlog文件和相应的位置
从库上配置和主库的连接信息
将全备数据导入从库
从库启动slave
在从库上查看同步状态，确认是否同步成功

如何开启从库的binlog功能？

在my.cnf文件中写入log-bin=mysql-bin

MySQL主从复制故障如何解决？

解决办法1：

登陆从库上操作：

stop slave 临时停止同步开关
set global sql_slave_skip_counter=1，将同步指针向下移动一个，也可以多个，如果多次不同步，可以重复操作。
start slave，重启主从复制开关

解决办法2：

在my.cnf配置文件中加入参数

slave-skip-errors=1032,1062,1007

如何监控主从复制是否故障?

查看slave端的IO和SQL进程状态是否OK，同步延迟时间是否小于1分钟

mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

MySQL数据库如何实现读写分离？

读写分离有两种实现方式。第一种在程序端就实现读写分离，第二种加一个中间件，比如mycat。

生产一主多从从库宕机，如何手工恢复？

处理方法：重做slave

停止slave
导入备份数据
配置master.info信息
启动slave
检查从库状态
生产一主多从主库宕机，如何手工恢复？

主库宕机分为数据库宕机和服务器宕机2种，不管哪种都要进行主从切换。

1.登陆从库检查IO线程和SQL线程状态show processlist\G，确认SQL线程已读完所有relay-log

2.登陆所有从库检查master.info信息，查看哪个从库的binlog文件和位置是最新的，选择最新的从库切换为主库（或利用半同步功能，直接选择做了实时同步的从库为主库）

3.如果主库只是数据库宕了，服务器还在运行，则可以把binlog拉取到提升为主库的从库应用。

4.登陆要切换为主库的从库，进行切换操作。

stop slave;reset master;quit

5.进入要切换的从库数据目录，删除master.info和relay-log.info文件，并检查授权表，read_only等参数

6.修改my.cnf配置文件，开启binlog，注释从库参数

log-bin=/data/3307/mysql-bin
#log-slave-updates
#read-only

对同步用户进行提权，保证权限与主库用户权限一样
重启数据库提生为主库
其他从库操作

（1）检查运行环境和用户

（2）停止从库，修改master信息

（3）启动从库同步，检查同步状态
修改web程序的连接配置，从原主库指向新主库
维护损坏的主库，完成后作为从库使用，或切换回来
如果主库没有宕机，只是想按计划切换一下主库，就非常简单

（1）主库锁表

（2）登陆所有从库检查同步状态，查看是否完成同步。

（3）其他按上面步骤进行切换

MySQL出现复制延迟有哪些原因？如何解决？

一个主库的从库太多，导致复制延迟

建议从库数量3-5个为宜，要复制的从节点数量过多，会导致复制延迟

从库硬件比主库差，导致复制延迟

查看master和slave的系统配置，可能会因为机器配置问题，包括磁盘IO、CPU、内存等各方面因素造成复制的延迟，一般发生在高并发大数据量的写入场景。

慢SQL语句过多

假如一条SQL语句执行时间是20秒，那么执行完毕到从库上能查到数据也至少是20秒，可以修改后分多次写入，通过查看慢查询日志或show full processlist命令找出执行时间长的查询语句或者大的事务。。

主从库之间网络延迟

主从库的网卡，网线，连接的交换机等网络设备都可能成为复制的瓶颈，导致复制延迟，另外，跨公网主从复制很容易导致主从复制延迟。

主库读写压力大，导致复制延迟

主库硬件要搞好一点，架构的前端要加buffer。

给出企业生产大型MySql集群架构可行备份方案？

利用mysqldump做定时备份，根据情况可按天或按周做全库备份。
用rsync+inotify对主库binlog做实时备份
请解释全备、增备、冷备、热备概念及企业实践经验？

全备：备份数据库所有数据

增备：一次性备份所有数据，然后再增量备份。

冷备：需要关闭mysql服务，读写请求均不允许状态下进行。

温备：服务在线，但仅支持读请求，不允许写请求的情况下备份。

热备：备份的同时，业务不受影响。

69. MySQL的SQL语句如何优化？
在表中建立索引，优先考虑where、group by使用到的字段
尽量避免使用select *，返回无用的字段会降低查询效率
尽量避免使用in和not in，会导致数据库引擎放弃索引进行全表扫描
尽量避免使用or，会导致数据库引擎放弃索引进行全表扫描
尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描
70. MySQL中MyISAM与InnoDB的区别，至少5点

（1）问5点不同

InnoDB支持事务，而MyISAM不支持事务。
InnoDB支持行级锁，而MyISAM支持表级锁
InnoDB支持MVCC，而MyISAM不支持
InnoDB支持外键，而MyISAM不支持
InnoDB不支持全文索引，而MyISAM支持

（2） InnoDB引擎的3大特性

插入缓存（insert buffer）、二次写（double write）、自适应哈希索引（ahi）、预读（read ahead）（3）二者select count(*)哪个更快，为什么？

MyISAM更快，因为MyISAM内部维护了一个计数器，可以直接调取。

面试题035：开发有一堆数据发给dba执行，DBA执行需注意什么？

MySQL高可用方案有哪些，各自特点，企业如何选择？

主从复制+读写分离

优点：成本低、架构简单、易实施、维护方便

缺点：master出现问题后不能自动到slave上，需要人工干涉。

MySQL Cluster

优点：安全性高，稳定性高。可以在线增加节点

缺点：架构复杂，至少三个节点，对于引擎只能用ndb，不支持外键，管理复杂，部署费时而且是收费的。

Heartbeat /keepalived+双主从复制

优点：安全性、稳定性高，出现故障系统将自动切换，从而保证服务的连续性。

缺点：可能会发生脑裂

HeartBeat+DRBD+MySQL

优点：安全性、稳定性、出现故障系统将自动切换，从而保证服务的连续性。

缺点：只用一台服务器提供服务，成本高，可能发生脑裂

如何分表分库备份及批量恢复（口述脚本实现过程）？

备份库：

mysqldump -u 用户名 -p 密码数据库名 >备份的文件名

备份表

mysqldump -u 用户名 -p 密码数据库名表名 >备份的文件名

如何批量更改数据库表的引擎？

#!/bin/sh
cmd="mysql -uroot -pabc123 -e"
$cmd "use 库名;show tables;"|grep -v Tables >/file.txt  #将表名存到file.txt文件里
tables=/file.txt
for n in `cat $tables`  #让变量n分别取file.txt文件里的表名
do
  $cmd "use 库名;alter table $n engine=myisam"
done

如何批量更改数据库字符集?

#!/bin/sh
cmd="mysql -uroot -pabc123 -e"
$cmd "show databases;"|grep -v Database >/file.txt
databases=/file.txt
for n in `cat $databases`
do
  $cmd "alter database $n default character set utf8;"
done

如何做主从数据一致性校验

主从一致性校验有多种工具例如checksum、mysqldiff、pt-table-checksum等

MySQL索引的种类及工作原理。

普通索引：最基本的索引，没有任何限制。

唯一索引：与普通索引类似，不同的是，索引列的值必须唯一，但允许有空值。

主键索引：它是一种特殊的唯一索引，不允许有空值。一张表只能有一个主键。

组合索引：就是将多个字段建到一个索引里。

mysqldump导入导出默认把所有数据都缩减在一行里面，为了查看和修改方便，如何将数据以多行插入的形式导出

用--skip-extend-insert选项

公司现有的数据库架构，总共有几组mysql库？

我们公司现在有两组MySQL。其中一套是生产库，一套是测试库，都是一主一备的结构。

mysql的权限怎么管理？
只给insert,update，select和delete四个权限即可。有时候delete都不给。

如果发现io或cpu压力很大，如何解决问题？
首先我会用top命令和iostat命令，定位是什么进程在占用cpu和磁盘io；

如果是mysql占用的资源比较多，可以由以下几种方式：

给mysqll加多一些缓存，尽量避免它与硬盘进行IO
减少超时时间，这样占用连接的进程
开启慢查询日志，有执行比较慢的语句提交给开发，让他们做优化，可以是优化语句也可以是加索引。

如何监控MySQL的增删改查次数？

mysql> show global status where variable_name in('com_select','com_insert','com_delete','com_update');
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_delete  | 0   |
| Com_insert  | 0   |
| Com_select  | 31897 |
| Com_update  | 0   |
+---------------+-------+
4 rows in set (0.00 sec)

用set分别设置字符集变量值

mysql> set global character_set_client=utf8;
mysql> set global character_set_connection=utf8;
mysql> set global character_set_results=utf8;
mysql> set global character_set_database;
mysql> set global character_set_server;
mysql> set global character_set_system;

请描述MySQL里中文数据乱码原理，如何防止乱码？

mysql客户端 mysql服务端操作系统等字符集不一致导致的乱码，将上述字符集调成一致。

mysql的典型配置文件

[mysqld]
user = mysql
port = 3306
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
bind-address = 0.0.0.0
pid-file=/var/run/mysqld/mysqld.pid
character-set-server = utf8
collation-server = utf8_general_ci
log-error=/var/log/mysqld.log

max_connections = 10240
open_files_limit = 65535
innodb_buffer_pool_instances = 4
innodb_buffer_pool_size = 4G
innodb_flush_log_at_trx_commit= 2
sync_binlog = 0
innodb_log_file_size = 256M
innodb_flush_method = O_DIRECT
interactive_timeout = 1800
wait_timeout = 1800
slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 8
master-info-repository=TABLE
relay-log-info-repository=TABLE

posted @ 2020-03-18 19:05 张贺贺呀阅读(716) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

张贺贺

mysql相关面试题

mysql面试题

目录

笔试题

面试题

mysql的典型配置文件

公告