面试题1 ：MySQL集群和高可用方案有哪些，再生产场景你都用过哪些方案？
常用mysql集群可以使用双主一从，一主多从； 高可用方案：MMM、MHA、mysql+ha+drbd 读写分离：MySQL+proxy 、Amoeba
生产中我使用的是双主3从，其中从库两台对外提供读服务，一台对内给开发或者DBA提供服务
面试题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 |
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%’; |
