运维必会的MySQL企业面试题及答案(开发也可以看看)

面试题1 :MySQL集群和高可用方案有哪些,再生产场景你都用过哪些方案?
常用mysql集群可以使用双主一从,一主多从; 高可用方案:MMM、MHA、mysql+ha+drbd  读写分离:MySQL+proxy 、Amoeba

生产中我使用的是双主3从,其中从库两台对外提供读服务,一台对内给开发或者DBA提供服务

blob.png

blob.png

面试题2:MySQL数据库如何实现读写分离,你的公司是怎么实现的(请答出200服务器以上规模企业数据库的解决方案)

我们公司使用的是mysql+proxy进行读写分离。在生产中,可以有这样两种方法

1.主库:web oldboy123 10.0.0.% 3306 (slect,insert,delete,update),主库忽略mysql同步授权【binlog-ignore-db=mysql     replicate-ignore-db=mysql】

从库:主库的web用户同步到从库,然后回收权限insert,delete,update,下面具体是回收权限方法

1

2

mysql> REVOKE insert,update,delete ON oldboy.* FROM ‘web1’@’10.0.0.%’;

Query OK, 0 rows affected (0.00 sec)mysql> show grants for web@’10.0.0.%’;+————————————————————————————————————+| Grants for web1@10.0.0.%                                                                                   |+————————————————————————————————————+| GRANT USAGE ON *.* TO ‘web1’@’10.0.0.%’ IDENTIFIED BY PASSWORD ‘*23AE809DDACAF96AF0FD78ED04B6A265E05AA257’ || GRANT SELECT ON oldboy.* TO ‘web1’@’10.0.0.%’                                                            |+————————————————————————————————————+2 rows in set (0.00 sec)

2.主库:web oldboy123 10.0.0.% 3306 (select,insert,delete,update)

从库:web oldboy123 10.0.0.% 3306 (select)

设置 read-only确保从库为只读

面试题3:开发有一堆数据插入,如何防止插入的中文数据产生乱码?

1.询问开发应用程序支持什么字符集 2.将数据转换为相应应用程序字符集保存 3.设置服务器端字符集(修改配置文件my.cnf, 使用命令 set names utf-8)

面试题4:如何批量更改数据库表的引擎,例如:myisam改成innodb。

方法一:

1.通过mysqldump -uroot -poldboy -S /data/3306/mysql.sock -x -B oldboy > /opt/oldboy.sql

2.使用sed替换,sed -i ‘s/myisam/innodb/g’ /opt/oldboy.sql

3.mysql -uroot -poldboy -S /data/3306/mysql.sock </opt/oldboy.sql

方法二:

1.登录数据库,执行下面的脚本

mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e “alter table oldboy.test engine=MyISAm;”

ALTER TABLE oldboy ENGINE = INNODB;

#!/bin/sh

myuser=root

mypasswd=lixiang123

socket=/tmp/mysql.sock

mycmd=”mysql -u$myuser -p$mypasswd -S $socket”

mydump=”mysqldump -u$myuser -p$mypasswd -S $socket”

for database in $mycmd -e "show databases;"|sed '1,2d'|egrep -v "mysql|performance_schema"

do

for tables in mydump -e "show tables from $databses;"|sed '1d'

do

$mycmd “alter table $database.$tables engine = innodb;”

done

done

面试题5:如何批量更改数据库字符集,例如;GBK更改为UTF8。

方法一:可以通过mysqldump命令备份出一个sql文件,再使用sed命令替换sed -i ‘s/GBK/UTF8/g’

方法二:通过shell脚本循环,过滤出数据库结合 alter database test set character utf8

1

2

3

4

5

6

7

8

9

10

11

mysql> help alter database

Name: ‘ALTER DATABASE’

Description:

Syntax:

ALTER {DATABASE | SCHEMA} [db_name]

alter_specification …

ALTER {DATABASE | SCHEMA} db_name

UPGRADE DATA DIRECTORY NAME

alter_specification:

[DEFAULT] CHARACTER SET [=] charset_name

| [DEFAULT] COLLATE [=] collation_name

面试题6 :你的数据库是如何备份的(请答出200服务器以上规模企业数据库备份的解决方案)

我公司的服务器采用的一主多从的方案,然后单独拿出一台服务器作为数据备份,同时采用inotify监控软件实时将从库产生的mysql-bin.******文件同步到本地。如果采用更安全的方法可以使用xtrabackup工具进行物理备份,将数据备份到备份服务器

面试题7:key_buffer_size参数作用,如何修改这个参数不重启数据库就可以生效?

key_buffer_size是数据库索引缓存

set global key_buffer_size = 26777216;不过这个只是临时生效,想要彻底生效可以在配置文件中修改

面试题8:delete from test和truncate table test区别?

delete from test是逻辑删除

truncate table test 是物理删除

面试题9:你维护的企业里MySQL服务架构能说下么?

我们企业采用的是一主三从,当主挂掉时能够自动切换到从上面(这个采用的是MMM 、keepalived实现的),读写分离Amoba

面试题10:网站打开慢,请给出排查方法,如果是因为数据库慢导致,如何排查并解决,请分析并举例?

网站打开慢的具体分析方法为:使用top,vmstat,free等命令帮助分析系统性能瓶颈

如果发现网站打开慢是由数据库造成的,那么可以采用下面的方法进行分析:1.修改配置文件#log-slow-queries = /data/3307/slow.log,开启mysql满日志查询,人为观察或是使用第三方工具mysqlsla分析慢查询最多的语句 2.通过explain 分析 慢查询语句 3.如果上述得出是结果为下面内容表示改语句并未走索引,而系统给出的可以创建的索引是primary(主键),但是具体创建索引可以根据具体条件判断,即 where后面跟随的语句

possible_keys: PRIMARY

key: NULL

面试题11:什么是数据库的事务,事务有哪些特性?

数据库事务是指逻辑上的一组sql语句,组成这组操作的各个sql语句,执行时要么成功要么失败

数据库事务:具有原子性、隔离性、持久性、一致性

面试题12:MySQL有哪些常用引擎?企业中如何批量更改表的引擎?

企业中常用的引擎有:MyISAM InnoDB Cluster Memory

1

2

3

4

5

6

7

8

9

10

11

12

13

14

mysql> show engines;

+——————–+———+—————————————————————-+————–+——+————+

| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |

+——————–+———+—————————————————————-+————–+——+————+

| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |

| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |

| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |

| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |

| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |

| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |

| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |

| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |

+——————–+———+—————————————————————-+————–+——+————+

8 rows in set (0.00 sec)

面试题13: MyISAM与Innodb数据库引擎有什么特点与区别,企业中如何选择?

MyISAM引擎:1.不支持事务 2.表级锁定 3.读写互相阻塞,但是读的时候并不阻塞另外的读 4.只会缓存索引 5.读取速度快,占用服务器资源相对较少 6.不支持外键约束

Innodb引擎:1.支持事务 2.支持行级锁定 3.读写阻塞与事务隔离级别相关 4.具有非常高的缓存性能 5. 5.5版本以后已经支持全文索引 6支持外键 7.占用资源比较多

InnoDB引擎在企业使用场景

1.需要事务 2.行级锁定对高并发有很好的适应能力,但需要确保查询是通过索引完成 3.数据读写及更新较为频繁的场景,如微信 微博 SNS BBS等 4.数据一致性要求较高的业务,如银行 5.硬件设备内存较大,可以利用InnoDB较好的缓存能力来提供内存的利用率,尽可能减少磁盘IO 6.相比MyISAM引擎,InnoDB引擎更消耗资源,速度没有MyISAM引擎快

MyISAM引擎企业中使用场景

1.不支持事务 2.读数据比较多的应用 3.硬件资源较差的机器 4.对数据的一致性要求不高的业务 5.采用主从复制,从库可以使用MyISAM 6.数据修改相对较少的业务

面试题14:MySQL binlog的工作模式有哪些?各有什么特点,企业中如何选择?

1.Row(行模式);

将一条语句变成多条语句,例如:删除一个表delete from test,里面有五条数据它会生成3条语句,delete 张三 from test where id=1;delete 李四 from test where id=2;delete 王五 from test where id=3;

2.Statement(语句模式)

每一条修改的数据都会完整的记录到主库master的binlog里面,在slave上完整执行在master执行的sql语句;例如:主库操作delete from test;那么在从可也会执行delete from test;

3.mixed(混合模式)

结合前面的两种模式,如果在工作中有使用函数 或者触发器等特殊功能需求的时候,使用混合模式

例如当输出的数据达到100万条的时候,它就会选择 statement模式,而不会选择Row Level行模式

面试题15:如何分表分库备份及批量恢复(口述脚本实现过程)?

#!/bin/sh

myuser=root

mypasswd=lixiang123

socket=/tmp/mysql.sock

mycmd=”mysql -u$myuser -p$mypasswd -S $socket”

mydump=”mysqldump -u$myuser -p$mypasswd -S $socket”

for database in $mycmd -e "show databases;"|sed '1,2d'|egrep -v "mysql|performance_schema"

do

for tables in mydump -e "show tables from $databses;"|sed '1d'

do

$mydump $database $tables|gzip >/data/backup/${database}/${databse}_${tables}_$(date +%F)_sql.gz

done

done

面试题16:企业中MySQLDUMP备份时使用了-A -B参数,请问此时如何进行单表单库恢复?

寻找另外一台mysql备份机器,将先前mysqldump产生的sql文件导入;在按照面试题033的方法进行分表分库备份,将产生的结果再次导入线上服务器

面试题17:生产场景不停不锁主库,不夜里操作,如何白天从容批量创建从库?

mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock -A -B -F –master-data=1 -x >/3306.sql

从库在change master to master_host = ‘10.0.0.101’, master_port=3306, master_user=’rep’, master_password=’oldboy123′;

start slave;

面试题18:生产场景,一主多从环境,从库宕机,请问你如何恢复?

从库宕机不太影响主机业务,然后查看从库是服务上的宕机还是服务器宕机,如果是应用程序的问题,查看从库的具体状态再另外分析

面试题19:生产场景,一主多从环境,主库宕机,请问你如何恢复,注意要说完整过程。

当1主多从时,留一台从库专门用来做数据备份

如果主库能够ssh连接上,mysql_bin.0000**文件没有丢失,将其补全到所有从库

选定一个从库提升为主库,开启binlog并将其他所有从库change master to

面试题20:解释下有关数据库的ACID是什么意思?

原子性:一个事务(transaction)中的所有操作,要么成功要么全部失败,并不会结束在中间某个环节。事务在执行过程中发生错误,会被回滚到事务开始前的状态,就像这个事务从来没有执行过一样

一致性:在事务开始之前和事务结束以后,数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则,这包含资料的精准度、串联性以及后续数据库可以自发性的完成预设工作

隔离性:当两个或多个事务并发访问(此处访问指的是查询和修改操作)数据库的同一数据时所表现出来的相互关系。事务隔离分为不同级别,包括读未提交(read uncommitted)、读提交(read committed)、可重复读(repeated read)和串行化(Serializable)

持久化:在事务完成以后,该事务对数据所做的更改便持久地保存在数据库之中,并且是完全的

面试题21:企业生产MySQL 如何优化?

1.选择好的硬件,做raid10

2.开启慢查询,做索引

3.设置sleep超时时间

4.开启mysql慢查询日志,通过mysqlsla分析日志定时发送给开发,做sql语句的优化

面试题22:企业生产MySQL 如何设计备份方案?你是怎么做的?

我们企业采用的双主三从,实际是双主单写,两台主之间做keepalived高可用,另外两台从库实现读写分离,还有一台从库用来做备份用

面试题23:企业中MySQL root密码忘了怎么解决,多实例密码忘了又如何解决?

1

2

3

4

首先停止mysql,kill mysqld

mysqld_safe –defaults-file=/data/3306/my.cnf –skip-grant-table &

mysql -uroot -S /data/3306/mysql.sock

update mysql.user set password=password(“123456”) where user=’root’ and host=’localhost’;

面试题24:什么是MySQL引擎? MyISAM与Innodb数据库引擎特点与区别?
mysql数据库引擎是表的一种存储机制,不同的存储引擎可能在存取空间大小,存储性能以及引擎功能存在不同。常见的数据库引擎有MyISAM,Innodb,MEMORY, Cluster(集群)

MyISAM:

1.不支持事务  2.表级锁定 3.读写互相阻塞 4.只会缓存索引 5.读取速度较快,占用资源较少 6.不支持外键约束,但是支持全文索引

InnoDB:

1.支持事务 2.行级锁定(5.6版本以后支持全文索引) 3.读写阻塞与事务隔离级别相关 4.高速缓存(DATA caches) 5.占用系统资源较多 6.支持外键约束,支持全文索引

面试题25:如何配置描述MySQL多实例?

面试题26:工作中数据库被误操作执行了一个删除的SQL语句,你如何完整恢复丢失的数据?

1.首先得刷新mysql-binlong文件,查看具体mysql-bin.*****文件

mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock -F –master-data=2 -B oldboy > /opt/oldboy.sql

2.得到出问题时候具体的文件mysql-bin.000006

mysqlbinlog -d oldboy mysql-bin.000006 >/bin.sql (在mysql_bin文件导出oldboy库的sql语句到bin.sql文件中)

3.查看oldboy.sql文件,过滤出刚刚误操作的语句删除掉 ,再次导入sql文件即可

mysql -urtoo -poldboy -S /data/3306/mysql.sock  -B oldboy </opt/oldboy.sql

[如果忘记之前执行了什么语句,那么就定位到那个时候的时间,然后再次按照上述操作执行,尽量减少数据的丢失]

在恢复数据的时候尽量停止掉mysql,不然一边恢复数据又一边写入数据 【如果可以容忍一段时间的数据丢失,那么可以不关闭数据库

备注:全量备份到出问题只产生了一个 mysql-bin.000021 文件,可是使用下面方法恢复,如果产生多个文件可以使用追加的方法

面试题27:MySQL出现同步延迟有哪些原因?如何解决?
1.从库太多

2.从库的硬件性能差会导致同步延迟
3.慢sql语句过多

4.主从复制的设计问题(例如主从采用单线程,而主库并发太大,来不及传入从库)

5.网络问题

面试题28:详细MySQL主从同步的原理及配置主从的完整步骤。
主库开启bin_log,这时主库就会产生mysql_bin.000***文件和IO线程,如果从库想要连接主库,必须在主库上面进行授权grant replication on *.* to rep@10.0.0.% indentified by ‘123456’;这个时候在从库要change master to ……到主库,与主库建立IO线程的连接同时在从库要开启同步开关start slave,此时从库就会产生一份really_log和sql线程,sql线程负责把数据写入磁盘

面试题29:MySQL如何实现双向互为主从复制A<==>B?

主库从库都需要开启bin_log日志文件

主库:#________m-m m1 start________

auto_increment_increment    = 2        #自增ID的间隔
auto_increment_offset           = 1        #ID的初始位置
log-slave-updates   = 1
log-bin = /data/3306/mysql-bin
expire_logs_days = 7
#________m-m m1 end________

从库:

#________m-m m1 start________

auto_increment_increment    = 2        #自增ID的间隔
auto_increment_offset           = 2        #ID的初始位置
log-slave-updates   = 1
log-bin = /data/3306/mysql-bin
expire_logs_days = 7
#________m-m m1 end________

面试题30:MySQL如何实现级联同步A–>B–>C?
从库B开启bin_log日志

在配置文件my.cnf下面模块添加如下内容:

log-bin = /data/3307/mysql-bin

log-slave-updates = 1

expire_logs_days = 7

面试题31:MySQL主从复制故障如何解决?如何监控主从复制是否故障?
在从库使用show slave status\G;查看从库的IO线程和SQL线程是否为yes,如果遇上具体问题具体分析。一般会出现创建库的问题,语句的问题。我们只需要在修改从库配置文件my.cnf

slave-skip-errors = 1032,1062,1005,1007

查看从库Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0,这三个状态是否正常,想要查看主从同步到底延迟多少,可以在主库插入时间戳

时间戳设置:

主库:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

CREATE TABLE test (‘id’ INT (3) UNSIGNED AUTO_INCREMENT, ‘date1’ TIMESTAMP (8) PRIMARY KEY(‘id’));

insert into test values(1,null);

mysql> select * from test;

+—-+———————+

| id | date1               |

+—-+———————+

|  2 | 2015-07-30 05:04:02 |

+—-+———————+

1 row in set (0.00 sec)

从库

mysql> select * from test;

+—-+———————+

| id | date1               |

+—-+———————+

|  1 | 2015-07-30 04:59:22 |

|  2 | 2015-07-30 05:04:02 |

+—-+———————+

2 rows in set (0.00 sec)

可以看到从库id=2的记录与主库基本无差异

面试题32:MySQL Sleep线程过多如何解决?

在配置文件修改超时时间

[mysqld]
interactive_timeout = 120 ;此参数设置后wait_timeout自动生效。
wait_timeout = 120

php程序不要使用长连接;java程序调整连接池

打开mysql慢查询

面试题33:如何调整已有MySQL数据库的字符集,例如:从UTF8改成GBK,请给出完整步骤。

面试题34:请描述MySQL里中文数据乱码的背后原理,以及工作中如何防止数据库数据乱码?

1.linux客户端字符集 UTF8(应用程序) 2.linux服务器端字符集 LANG=”zh_CN.UTF-8″ 3.数据库  客户端字符集 UTF8

面试题35:MySQL库授权oldboy用户管理bbs库的所有表,10.0.0.0整个网段访问,密码是123456,请给出详细命令(不低于2种方法)?

方法一:

1 grant all on bbs.* to oldboy@10.0.0.% identified by ‘123456’;

方法二:

登录mysql,可以用>help create user;查看帮助

CREATE USER ‘jeffrey’@’localhost’ IDENTIFIED BY PASSWORD ‘*90E462C37378CED12064BB3388827D2BA3A9B689’;

1

2

create user ‘oldboy’@’10.0.0.%’ identified by password ‘123456’;

grant all on bbs.* to ‘oldboy’@’10.0.0%’;

0

发表评论

邮箱地址不会被公开。