mysql与redis面试题_下面关于redis中事务和mysql面试题

16.数据库实现数据备份

热备 :

innobackupex

完全备份 :

innobackupex --user 用户名 --password 密码 备份目录名 --no-timestamp

完全恢复 :

innobackupex --apply-log 目录名 # 准备恢复数据

innobackupex --copy-back 目录名 # 恢复数据

增量备份 :

innobackupex --user 用户名 --password 密码 --incremental-basedir= 目录名 --no

timestamp 增量恢复 :

innobackupex --apply-log --redo-only 目录名 --incremental-dir= 目录名 # 准备恢复数据

innobackupex --copy-back 目录名 # 恢复数据

一款强大的在线热备工具

备份过程性在线不锁表 , 适合生产环境

冷备 :

cp tar( 物理备份 ) mysqldump( 逻辑备份 )

备份 :

cp -r /var/lib/mysql

tar -zcvf /root/mysql.tar.gz

恢复 :

cp -r 备份目录 /mysql.bak /var/lib/mysql

tar -zxf /root/mysql.tar.gz -C /var/lib/mysql

vhown -R mysql.mysql /var/lib/mysql

物理备份的缺点 :

跨平台性差

备份时间长、冗余备份、浪费存储空间

mysqldump( 逻辑备份 )

完全备份 : 备份所有数据

增量备份 : 备份上次备份后 , 所有新产生的数据

差异备份 : 本分完全备份后 , 所有新产生的数据

完全备份 : mysqldump -uroot -p 密码 库名 > 目录 /xxx.sql

完全恢复 :

mysql -uroot -p 密码 [ 库名 ] < 目录 /xxx.sql

效率低、备份和还原速度比较慢、锁表

备份过程中 , 数据插入和更新操作被阻塞

ps:mysqldump 在执行备份的时候会锁表 , 如果想让其备份时不锁表要加 --skip-opt 选项

17.innodb和myisam存储引擎区别

18.mysql的innodb如何定位锁问题，mysql如何减少主从复制延迟？

mysql 的 innodb 如何定位锁问题 : 在使用 show engine innodb status 检查引擎状态时，发现了死 锁问题在 5.5 中， information_schema 库中增加了三个关于锁的表（ MEMORY 引擎） innodb_trx

## 当前运行的所有事务 innodb_locks ## 当前出现的锁 innodb_lock_waits ## 锁等待的对应关 系

mysql 如何减少主从复制延迟 :

如果延迟比较大，就先确认以下几个因素：

从库硬件比主库差，导致复制延迟 主从复制单线程，如果主库写并发太大，来不及传送到从库就会导致延迟。

更高版本的 mysql 可以支持多线程复制

慢 SQL 语句过多

网络延迟

master 负载，主库读写压力大，导致复制延迟，架构的前端要加 buffffer 及缓存层

slave 负载，一般的做法是，使用多台 slave 来分摊读请求，再从这些 slave 中取一台专用的服务 器，只作为备份用，不进行其他任何操作 . 另外， 2 个可以减少延迟的参数 :–slave-net

timeout=seconds 单位为秒 默认设置为 3600 秒

参数含义：当 slave 从主数据库读取 log 数据失败后，等待多久重新建立连接并获取数据

–master-connect-retry=seconds 单位为秒 默认设置为 60 秒

参数含义：当重新建立主从连接时，如果连接建立失败，间隔多久后重试

通常配置以上 2 个参数可以减少网络问题导致的主从数据同步延迟

MySQL 数据库主从同步延迟解决方案

最简单的减少 slave 同步延时的方案就是在架构上做优化，尽量让主库的 DDL 快速执行，还有就是主库是写，对数据安全性较高，比如 sync_binlog=1 ， innodb_flflush_log_at_trx_commit= 1 之类

的设置，而 slave 则不需要这么高的数据安全，完全可以讲 sync_binlog 设置为 0 或者关闭 binlog ，innodb_flflushlog 也可以设置为 0 来提高 sql 的执行效率。另外就是使用比主库更好的硬件设备作为 slave

20.如何重置mysql root密码？

一、 在已知 MYSQL 数据库的 ROOT 用户密码的情况下，修改密码的方法：

1 、 在 SHELL 环境下，使用 mysqladmin 命令设置： mysqladmin –u root –p password “ 新密码 ”回车后要求输入旧密码

2 、 在 mysql> 环境中 , 使用 update 命令，直接更新 mysql 库 user 表的数据：

Update mysql.user set password=password(‘ 新密码 ’) where user=’root’; flflush privileges; 注 意： mysql 语句要以分号 ” ； ” 结束

3 、 在 mysql> 环境中，使用 grant 命令，修改 root 用户的授权权限。

grant all on . to root@’localhost’ identifified by ‘ 新密码 ’ ；

二、 如查忘记了 mysql 数据库的 ROOT 用户的密码，又如何做呢？方法如下：

1 、 关闭当前运行的 mysqld 服务程序： service mysqld stop （要先将 mysqld 添加为系统服务）

2 、 使用 mysqld_safe 脚本以安全模式（不加载授权表）启动 mysqld 服务

/usr/local/mysql/bin/mysqld_safe –skip-grant-table &

3 、 使用空密码的 root 用户登录数据库，重新设置 ROOT 用户的密码 ＃ mysql -u root Mysql>

Update mysql.user set password=password(‘ 新密码 ’) where user=’root’; Mysql> flflush

privileges;

21.mysql数据备份工具

mysqldump 工具

mysqldump 是 mysql 自带的备份工具，目录在 bin 目录下

面： /usr/local/mysql/bin/mysqldump ，支持基于 innodb 的热备份，但是由于是逻辑备份，所以 速度不是很快，适合备份数据比较小的场景， Mysqldump 完全备份 + 二进制日志可以实现基于时 间点的恢复。

基于 LVM 快照备份

在物理备份中，有基于文件系统的物理备份（ LVM 的快照），也可以直接用 tar 之类的命令对整个 数据库目录进行打包备份，但是这些只能进行泠备份，不同的存储引擎备份的也不一样， myisam 自动备份到表级别而 innodb 不开启独立表空间的话只能备份整个数据库。

tar 包备份

percona 提供的 xtrabackup 工具，支持 innodb 的物理热备份，支持完全备份，增量备份，而且速 度非常快，支持 innodb 存储引起的数据在不同，数据库之间迁移，支持复制模式下的从机备份恢 复备份恢复，为了让 xtrabackup 支持更多的功能扩展，可以设立独立表空间，打开

innodb_fifile_per_table 功能，启用之后可以支持单独的表备份

22.使用binlog日志恢复数据的语法格式?

mysqlbinlog [ 选项 ] binlog 日志文件名 | mysql -u 用户名 -p 密码

23.简述primary key的特点?

一个表只能有一个主键

字段的值不允许重复且不允许赋 null 值

多个字段做主键称为复合主键

24.drop,delete,truncate的区别?

TRUNCA 和 DELETE 只能删除数据 , 而 DROP 则删除整个表 ( 结构和数据 )

1)DELETE 删除操作作为事务记录在日志中保存一遍进行回滚操作

2)TRUNCATE TABLE 则一次性从表中删除所有的数据并不把单独的删除记录计入日志保存 , 删除行 是不能恢复的 , 并且在删除的过程中不会激活与表有关的删除触发器 , 执行速度快

3) 表和索引所占空间 , 档表被 TRUNCATE 后 , 这个表和索引所占的空间会恢复到出事大小 , 而 DELETE 操作不会减少表或索引所占用的空间 ,drop 语句将表所占用的空间完全释放

25.binlog的三种日志格式?

Statement: 每一条会修改数据的 sql 都会记录在 binlog 中

Row: 不记录 sql 语句上下文相关信息 , 仅保存哪条记录被修改

Mixdlever: 是以上两种的混合使用

26.请说出maxadmin命令常用选项的作用?

-u -p -P

-u 指定用户名

-p ( 小写 p) 指定密码

-P ( 大写 P) 指定端口号

27.下列慢查询日志选项的作用?

slow-query-log 、 slow-query-log-fifile 、 long-query-time

slow-query-log: 启用慢查询日志

slow-query-log-fifile: 定义慢查询日志名称

long-query-time: 指定超时时间

28.存储过程与触发器的区别

两者唯一的区别是触发器不能使用 EXECUTE 语句使用 , 而是在用户执行 Transact-SQL 语句时自动触 发 ( 激活 ) 执行 , 触发器是在一个修改了指定表中的数据时执行的存储过程 , 触发器主要是通过事件执 行触发而被执行的 , 而存储过程可以通过存储过程名称名字而直接调用

29.存储过程与函数的区别

函数 : 只能返回一个变量的限制 存储过程 : 可以返回多个变量的限制

函数是可以嵌入在 sql 中使用的 , 可以在 select 中调用 , 而存储过程不行

存储过程可以返回参数 , 函数只能返回值或者表对象

存储过程一般是作为一个独立的部分来执行 , 二函数可以作为查询语句的一个部分来调用

31.mysql的普通索引和复合索引

普通索引：就是单个列作为索引。如 indexname(a)

复合索引：就是由多个列组成的索引，如 indexname(a,b,c) ，复合索引遵循最左匹配原则。

32.数据的完全，差异，增量备份概述

1 ）完全备份

每次对数据进行完整的备份，完全备份是增量备份的基础，完全备份保存的是备份完成时刻的数 据库

优点：

安全性高

缺点：

冗余数据太多，空间利用率低

2 ）差异备份

备份那些自从上次完全备份之后被修改过的文件

优点：

相比完全备份，降低了冗余数据，提高了空间利用率，安全性差于完全备份

缺点：

仍旧存在冗余数据存在

3 ）增量备份

只有那些在上次完全备份或者增量备份后被修改的文件才会被备份

增量备份就是备份自上一次备份之后增加或变化的文件或者内容

优点：

没有冗余数据，效率最高，空间利用率最大化

缺点：

安全性降低了

33.冷备份和热备份的优缺点

热备份的优点：

可在表空间或数据文件级备份，备份时间短。

备份时数据库仍可使用。

可达到秒级恢复（恢复到某一时间点上）。

可对几乎所有数据库实体作恢复。

恢复是快速的，在大多数情况下在数据库仍工作时恢复。

热备份的不足

不能出错，否则后果严重。

若热备份不成功，所得结果不可用于时间点的恢复。

因难于维护，所以要特别仔细小心，不允许“以失败而告终”。

什么是冷备份？

我们将在线的备份称为热备份，而相对的，将脱机数据备份称为冷备份。冷备份是定时将数据备份至备份服务器或目标硬件，在服务器动作完成后，保持相同状态，在一台服务器出现问题时可由另一台直接提供服务，出现故障时不会自动接管，需手动开启硬件和服务，保证工作的连续性和数据完整性。

冷备份的优点

1、 是非常快速的备份方法（只需拷文件）

2、 容易归档（简单拷贝即可）

3、 容易恢复到某个时间点上（只需将文件再拷贝回去）

4、 能与归档方法相结合，做数据库“最佳状态”的恢复。

5、 低度维护，高度安全。

冷备份的不足

1、 单独使用时，只能提供到“某一时间点上”的恢复。

2、 再实施备份的全过程中，数据库必须要作备份而不能作其他工作。也就是说，在冷备份过程中，数据库必须是关闭状态。

3、 若磁盘空间有限，只能拷贝到磁带等其他外部存储设备上，速度会很慢。

4、 不能按表或按用户恢复。

34.索引是个什么样的数据结构呢？

35.常用的SQL命令分类

1 ） DDL 数据定义语言 如： create 、 alter 、 drop

2 ） DML 数据操作语言 如： insert 、 update 、 delete

3 ） DCL 数据控制语言 如： grant 、 revoke

4 ） DTL 数据事物语言 如： commit 、 rollback 、 savepoint

36.数据库读写分离的路由用户和监控用户的作用

路由用户：监控读写分离

监控用户：监控数据库服务器

37.物理备份逻辑备份区别

物理备份：

物理备份是磁盘块为基本单位将数据从主机复制到备机。物理备份就是备份数据文件

了，比较形象点就是 cp 下数据文件，但真正备份的时候自然不是的 cp 这么简单。（物理备份又分

两种 一种冷备份一种热备份），物理备份恢复速度比较快，占用空间比较大。

逻辑备份：

逻辑备份是以文件为基本单位将数据从主机复制到备机。将数据导出至文本文件中；

mysqldump 是采用 SQL 级别的备份机制，他将数据表导成 SQL 脚本文件，是最常用的逻辑备份方

法。逻辑备份速度比较慢，占用空间比较小。逻辑备份的恢复成本高。

38.完全备份，增量备份和差异备份的区别

差异备份的定义：

备份自上一次完全备份之后有变化的数据。

增量备份的定义：

备份自上一次备份（包含完全备份、差异备份、增量备份）之后有变化的数

据。

增量备份和差异备份的区别：

完全备份：

备份全部选中的文件夹，并不依赖文件的存档属性来确定备份那些文件。（在备份过

程中，任何现有的标记都被清除，每个文件都被标记为已备份，换言之，清除存档属性）。

差异备份：

差异备份是针对完全备份：备份上一次的完全备份后发生变化的所有文件。（差异备

份过程中，只备份有标记的那些选中的文件和文件夹。它不清除标记，既：备份后不标记为已备

份文件，换言之，不清除存档属性）。

增量备份：

增量备份是针对于上一次备份（无论是哪种备份）：备份上一次备份后，所有发生变

化的文件。（增量备份过程中，只备份有标记的选中的文件和文件夹，它清除标记，既：备份后

标记文件，换言之，清除存档属性。）

不同备份类型可以存在一定组合，

参考示例如下：

完全备份和差异备份：

在星期一进行完全备份，在星期二至星期五进行差异备份。如果在星期五

数据被破坏了，则你只需要还原星期一完全的备份和星期四的差异备份。这种策略备份数据需要

较少的时间，但还原数据使用较少的时间。

完全备份和增量备份：

在星期一进行完全备份，在星期二至星期五进行增量备份。如果在星期五

数据被破坏了，则你需要还原星期一正常的备份和从星期二至星期五的所有增量备份。这种策略

备份数据需要较少的时间，但还原数据使用较多的时间。

39.恢复数据时，差异备份和增量备份哪个更快

完全备份是指对整个系统或用户指定的所有文件数据进行一次全面的备份。增量备份只是备份相 对于上一次备份操作以来新创建或者更新过的数据。差异备份即备份上一次完全备份后产生和更新的所有新的数据。恢复速度完全备份最快，增量备份最慢，差异备份介于两者之间。因此由慢 到快依次为增量备份、差异备份和完全备份。

40.读写分离的好处

MYSQL 读写分离，基本的原理是让主数据库（ master ）处理事务性增、改、删操作（ INSERT 、 UPDATE 、 DELETE ），而从数据库（ slave ）处理 SELECT 查询操作。数据库复制被用来把事务性

操作导致的变更同步到集群中的从数据库。从介绍可以看出 MYSQL 的读写分离可以减少数据库压 力，提高性能。

1. 增加冗余

2. 增加了机器的处理能力

3. 对于读操作为主的应用，使用读写分离是最好的场景，因为可以确保写的服务器压力更小，而读又可以接受点时间上的延迟

41.Mysql读写分离:

maxscale是mariadb公司开发的一套数据库中间件，可以很方便的实现读写分离方案；并且提供了读写分离的负载均衡和高可用性保障。

mycat:也具有数据读写分离的功能

过程概述:

(1)配置好集群环境

(2)下载安装 MaxScale

(3)配置 MaxScale，添加各数据库信息

(4)启动 MaxScale，查看是否正确连接数据库

(5)客户端连接 MaxScale，进行测试

42.mysql数据库如果发现CPU很高如何排查处理

首先使用 top 命令查看是不是 mysql 的 CPU 占用过高

查询原因，可能的原因：

1 ）一般情况下， mysql 占用过高多是有慢查询

2 ）执行查询 show processlist; 显示当前运行的线程，找到那个经常出现的 SQL 语句

解决：

1 ）打开慢查询日志，这里可以加上慢查询日志，然后重启 mysql ，等待日志。 slow-query-log=1

# 慢查询：确认开启

slow-query-log-fifile='/var/www/htmllog/slowlog.log' # 慢查询：日志文件及路径

long_query_time = 3 # 慢查询：指定超过 3s 仍未完成的语句，为执行过慢的语句

2 ）将这条语句建立索引

例如此语句为 select conut(*) from xxx where a=1 and b != 2 and objuid=?

那么我们将要根据 objuid 建立前缀索引： alter table xxx add index objuid(objuid)

43.mysql内连接、左连接、右连接以及全连接查询

1 ）内连接查询 inner join

关键字： inner join on

语句： select * from a_table a inner join b_table b on a.a_id = b.b_id;

说明：组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分

2 ）左连接查询 left join 关键字： left join on / left outer join on

语句： SELECT * FROM a_table a left join b_table b ON a.a_id = b.b_id;

说明： left join 是 left outer join 的简写，它的全称是左外连接，是外连接中的一种。 左 ( 外 ) 连

接，左表 (a_table) 的记录将会全部表示出来，而右表 (b_table) 只会显示符合搜索条件的记录。右表记录不足的地方均为 NULL 。

3 ）右连接 right join

关键字： right join on / right outer join on

语句： SELECT * FROM a_table a right outer join b_table b on a.a_id = b.b_id;

说明： right join 是 right outer join 的简写，它的全称是右外连接，是外连接中的一种。与左 ( 外 ) 连接相反，右 ( 外 ) 连接，左表 (a_table) 只会显示符合搜索条件的记录，而右表 (b_table) 的记录将会 全部表示出来。左表记录不足的地方均为 NULL 。

4 ）全连接 union

关键字： union /union all

语句： (select colum1,colum2...columN from tableA ) union (select colum1,colum2...columN

from tableB ) 或 (select colum1,colum2...columN from tableA ) union all (select

colum1,colum2...columN from tableB ) ；

union 语句注意事项：

        1.通过 union 连接的 SQL 它们分别单独取出的列数必须相同；

        2.不要求合并的表列名称相同时，以第一个 sql 表列名为准；

        3.使用 union 时，完全相等的行，将会被合并，由于合并比较耗时，一般不直接使用 union 进行合并，而是通常采用union all 进行合并；

4. 被 union 连接的 sql 子句，单个子句中不用写 order by ，因为不会有排序的效果。但可以对

最终的结果集进行排序；

(select id,name from A order by id) union all (select id,name from B order by id); // 没有排序

效果

(select id,name from A ) union all (select id,name from B ) order by id; // 有排序效果

44.冷迁移和热迁移是什么？

45.mysqldump备份不锁表的参数

使用 mysqldump 备份数据库时会默认锁表，加上 --skip-opt 选项会默认不锁表，再加上 --single

transaction 会加上事务，不会锁表

在用 mysqldump 备份使用那些参数选项是最完美的组合呢？

--skip-opt

--create-options ---- 添加 create 相关的选项

--single-transaction ---- 一致性备份

-q ---- 采用快速的 dump 方式 ( 提高导出性能 )

-e ---- 采用多重 insert 语句形式 ( 提高还原性能 ) ，当一个表的数据量很大情况

下不知道会不会导致死锁？

--no-autocommit ---- 采用批量提交方式 ( 提高还原性能 )

-R ---- 导出存储过程，函数，和触发器

--master-data=2 ---- 如果有写 log-bin 且版本为 5.0 以上的版本，则再加上 --master-data=2

--events ---- 如果是 5.1 以上的版本使用，包含事件

46.Mysql数据库常见的3种存储引擎的区别

MySQL 常见的三种存储引擎为 InnoDB 、 MyISAM 和 MEMORY 。

其区别体现在事务安全、存储限制、空间使用、内存使用、插入数据的速度和对外键的支持

1 、事务安全

InnoDB 支持事务安全 ,MyISAM 和 MEMORY 两个不支持。

2 、储存限制

InnoDB 有 64TB 的存储限制， MyISAM 和 MEMORY 要是具体情况而

3 、空间使用

InnoDB 对空间使用程度较高， MyISAM 和 MEMORY 对空间使用程度较低。

4 、内存使用

InnoDB 和 MEMORY 对内存使用程度较高， MyISAM 对内存使用程度较低。

5 、插入数据的速度：

InnoDB 插入数据的速度较低， MyISAM 和 MEMORY 插入数据的速度较高。

6 、对外键的支持：

InnoDB 对外键支持情况较好， MyISAM 和 MEMORY 两个不支持外键

47.Mysql版本升级

使用in-place upgrade方式，将MySQL5.6升级到MySQL5.7:

升级前的准备：备份+留意新版本的变更内容

1、对5.7的软件包，下载，解压

2、关闭当前MySQL（5.6）

3、替换二进制文件（5.7 替换 5.6）

          \# cd /usr/local

          \# mv mysql  mysql5.6

          \# mv mysql5.7 mysql

4、使用现有的数据目录启动MySQL

5、检查所有表是否与当前版本兼容，并更新系统库

6、重启，确保对系统表所做的变更得以生效

48.Mysql数据迁移

需要两个服务器数据库版本相同才可迁移

1：单个或多个数据库

mysqldump -h远程ip -u用户 -p密码 -P3306 --default-character-set=utf8  --databases 单个或多个数据库名称空格分开 | mysql -h127.0.0.1 -uroot -p -P3306

 执行后输入本地数据库密码即可

2：所有数据库

mysqldump -h远程ip -u用户 -p密码 -P3306 --default-character-set=utf8 --all-databases | mysql -h127.0.0.1 -uroot -p -P3306

3：某库某表

mysqldump -h远程ip -u用户 -p密码 -P3306 --default-character-set=utf8  dbname table1 table2…… | mysql -h127.0.0.1 -uroot -p -P3306

49.FTP的主动模式和被动模式

FTP 协议有两种工作方式： PORT 方式和 PASV 方式，中文意思为主动式和被动式。

PORT （主动）方式的连接过程是：客户端向服务器的 FTP 端口（默认是 21 ）发送连接请求，服务 器接受连接，建立一条命令链路。当需要传送数据时，客户端在命令链路上用 PORT 命令告诉服务器：

“ 我打开了 XX 端口，你过来连接我 ” 。于是服务器从 20 端口向客户端的 XX 端口发送连接请

求，建立一条数据链路来传送数据。

PASV （被动）方式的连接过程是：客户端向服务器的 FTP 端口（默认是 21 ）发送连接请求，服务器接受连接，建立一条命令链路。当需要传送数据时，服务器在命令链路上用 PASV 命令告诉客户端：

“ 我打开了 XX 端口，你过来连接我 ” 。于是客户端向服务器的 XX 端口发送连接请求，建立一条数据链路来传送数据。

从上面可以看出，两种方式的命令链路连接方法是一样的，而数据链路的建立方法就完 全不同。