mysql基础
一、数据库基础知识
1、ACID 特性
原子性（Atomicity） ： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；一致性（Consistency）： 执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；隔离性（Isolation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；持久性（Durabilily）： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。
只有保证了事务的原子性、隔离性、持久性之后，一致性才能得到保障。也就是说 A、I、D 是手段，C 是目的
2、并发事务带来了哪些问题?–多个用户对同一数据进行操作
脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务 1 读取某表中的数据 A=20，事务 2 也读取 A=20，事务 1 修改 A=A-1，事务 2 也修改 A=A-1，最终结果 A=19，事务 1 的修改被丢失。
不可重复读（Unrepeatable read）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。
1.脏读：数据被修改未被事务提交，数据被其余事务引用，产生脏数据
2.丢失修改：两个事务同时读取并修改同一数据，其中事务1的修改会被丢失
3.不可重复读：一个事务的多次读取数据中，被其余事务修改了数据，导致多次之间的数据不一致情况
4.幻读：与3类似，事务1读取几行数据时，事务2插入了一些数据，导致事务1查询时，多了一些之前不存在的插入数据
不可重复读和幻读有什么区别呢？
1、不可重复读的重点：是内容修改或者记录减少；
2、幻读的重点：在于记录新增
3、幻读其实可以看作是不可重复读的一种特殊情况，单独把区分幻读的原因主要是解决幻读和不可重复读的方案不一样。
3、事务隔离级别?
MySQL 的隔离级别基于锁和 MVCC 机制共同实现的。
除了 SERIALIZABLE （可串行化）隔离级别，其他的隔离级别都是基于 MVCC 实现。
READ-UNCOMMITTED(读取未提交) ： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、不可重复读或幻读。
READ-COMMITTED(读取已提交) ： 允许读取并发事务已经提交的数据，可以阻止脏读，但是不可重复读或幻读仍有可能发生。
REPEATABLE-READ(可重复读) ： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
SERIALIZABLE(可串行化) ： 最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。
4、innodb的特性
一：插入缓冲
二：二次写
三：自适应哈希
四：预读
5、数据表类型有哪些答：
MyISAM、InnoDB、HEAP、ISAM、DBD、MERGE以及Gemeni(一般只知道前两者即可)
6、范式
一范式：无重复的列
二范式：属性完全依赖于主键
三范式：属性不依赖其它非主注销
二、mysql复制
1、主从复制原理
主节点：
dump Thread：为每个Slave的I/O Thread启动一个dump线程，用于向其 发送binary log events
从节点：
I/O Thread：向Master请求二进制日志事件，并保存于中继日志中
SQL Thread：从中继日志中读取日志事件，在本地完成重放
2、级联复制
将主库的数据同步到级联库，然后级联库把自己的数据同步到从库上，这样可以减少主库的压力
3、半同步复制
问题：
默认情况下，MySQL的复制功能是异步的，异步复制可以提供最佳的性能，主库把binlog日志发送给从库即结束，并不验证从库是否接收完毕。
这意味着当主库或从库发生故障时，有可能从库没有接收到主库发送过来的binlog日志，这就会造成主库和从库的数据不一致，甚至在恢复时造成数据的丢失。
解决：
在开启了半同步复制机制后，主库只有当有任意一台从库已经接收到主库的数据后，告诉主库。主库收到从库同步成功的信息后，才继续后面的操作。
4、异步复制和同步复制
1、异步复制是Master将事件写入binlog，自身并不知道slave是否接收是否处理，不能保证所有事务都被所有slave接收；
2、同步复制是Master提交事务，直到事务在所有slave都已提交，才会返回客户端事务执行完毕信息。
这两种复制方式各有优劣，在异步复制方式下，系统拥有较低的延迟和较高的吞吐量，但是如果Master出了故障，有些数据因为没有被写入Slave，有可能会丢失；在同步复制方式下，如果Master出故障，Slave上有全部的备份数据，容易恢复，但是同步复制会增大数据写入延迟，降低系统吞吐量。
参考：https://www.cnblogs.com/xiaodiky/p/15706449.html
5、并行复制
多线程复制，具体方法就是把sql_thread，拆成多个worker线程，由一个coordinator分发到不同的worker，实现并行复制
6、主从复制存在的问题及解决方法
mysql主从复制存在的问题：
主库宕机后，数据可能丢失，从库只有一个sql Thread，主库写压力大， 复制很可能延时。
解决方法：
用半同步复制解决数据丢失的问题
用并行复制解决从库复制延迟的问题
三、增删查权限管理
1、drop、delete、truncate删除数据的区别？
删除速度上：drop>truncate>delete
drop：是将表所占的空间全部释放掉
truncate：则是一次从表中删除所有的数据并不把单独的删除操作记录计入日志中，删除行是不能恢复的
delete：执行删除是每次从表中删除一行，并且同时将改行的删除操作，作为事务记录在日志中保存以便进行回滚
想要删除部分数据用delete，想要删除表用drop。 想保留表但是把数据删除，如果和事务无关用truncate
2、mysql基础命令
1）数据库相关命令
1 查看MySQL服务器下所有数据库：SHOW DATABASES;
2 查看指定数据库的创建信息：SHOW CREATE DATABASE 数据库名称;
3 查看当前数据库：SELECT DATABASE();
4 创建数据库：CREATE DATABASE [IF NOT EXISTS] 数据库名称[库选项];
5 选择数据库：USE 数据库名称;
6 登录mysql数据库：mysql -u 用户名 -p 密码 数据库名;
7 删除数据库：DROP DATABASE [IF EXISTS] 数据库名称;
参考：https://blog.csdn.net/m0_64338546/article/details/127149168
2）数据表相关命令
查询表信息：
1 查看数据表：SHOW TABLES [KIKE 匹配模式];
2 查看数据表的相关信息：SHOW TABLE STATUS [FROM 数据库名] [LIKE 匹配模式];
3 查看数据表的字段信息：{DESCRIBE|DESC} 数据表名;
查看指定字段的信息：{DESCRIBE|DESC} 数据表名 字段名;
4 查看数据表的创建语句：SHOW CREATE TABLE 表名; （具体SQL语句以及表的字符编码）
5 查看数据表结构：SHOW [FULL] COLUMNS FROM 数据表名 [FROM 数据库名];
SHOW [FULL] COLUMNS FROM 数据库名.数据表名;
修改数据表：
1 修改数据表名称：ALTER TABLE 旧表名 RENAME [TO|AS] 新表名;
RENAME TABLE 旧表名1 TO 新表名1;
RENAME TABLE 旧表名1 TO 新表名1,旧表名2 TO 新表名2,...,旧表名n TO 新表名n; //批量修改多个数据库名称
2 修改表选项：ALTER TABLE 表名 表选项 [=] 值;
alter table stu charset=utf-8;
3 修改表结构：
修改字段名
ALTER TABLE 数据表名 CHANGE [COLUMN] 旧字段名 新字段名 字段类型 [字段属性];
修改字段类型
ALTER TABLE 数据表名 MODIFY [COLUMN] 字段名 新类型 [字段属性];
修改字段位置
ALTER TABLE 数据表名 MODIFY [COLUMN] 字段名 新类型 [字段属性] [FIRST|AFTER 字段名2];
4 新增字段
ALTER TABLE 数据表名 ADD [COLUMN] 新字段名 字段类型 [字段属性] [FIRST|AFTER 字段名];
ALTER TABLE 数据表名 ADD [COLUMN] (新字段名1 字段类型1, 新字段名2 字段类型2,...,新字段名n 字段类型n) 同时新增多个字段
5 删除字段
ALTER TABLE 数据表名 DROP [COLUMN] 字段名;
删除数据表
1 DROP [TEMPORARY] TABLE [IF EXISTS] 数据表1;
DROP [TEMPORARY] TABLE [IF EXISTS] 数据表1,数据表2,...,数据表n;
3）数据命令操作
1 为部分字段添加数据
INSERT [INTO] 数据表名(字段名1,字段名2,...,字段名n) {VALUES|VALUE} (值1,值2,...,值n);
INSERT [INTO] 数据表名 SET 字段名1 = 值1 [,字段名2 = 值2,...,字段名n = 值n]
2 为所有字段添加数据
INSERT [INTO] 数据表名 {VALUES|VALUE} (值1,值2,...,值n);
3 批量添加多行数据
INSERT [INTO] 数据表名 [字段列表] {VALUES|VALUE} (值列表1) [,(值列表2),...,(值列表n)];
修改数据
1 UPDATE 数据表名 SET 字段名1=值1 [,字段名2=值2,...] [WHERE 条件表达式];
删除数据
1 DELETE FROM 数据表名 [WHERE 条件表达式];
查询数据
1 查询表中全部数据
SELECT * FROM 数据表名;
2 查询表子部分字段
SELECT {字段名1,字段名2,字段名3,...,字段名n} FROM 数据表名;
3 简单条件查询数据
SELECT * FROM 数据表名 WHERE 条件表达式;
4）其余操作
1 显示目录安装位置 SHOW VARIABLES LIKE 'BASEDIR'; 2 显示数据安装位置
SHOW VARIABLES LIKE 'DATADIR';
3 错误日志目录查询
SHOW VARIABLES LIKE 'log_error';
4 删除用户
DROP USER 'test'@'localhost';
5 新增用户
CREATE USER 'szb'@'host43.example.com' IDENTIFIED BY 'centos';
6 权限
刷新权限：FLUSH PRIVILEGES;
对用户赋予所有数据库的所有权限：grant all on *.* to szb@'%';
查看权限：show grants for 用户;
撤销权限：revoke create on *.* from szb@'%';
权限参考：https://blog.csdn.net/weixin_39555954/article/details/121338317?
四、日志
1、事务日志--transaction log
2、错误日志--error log
3、通用日志--general log
4、慢查询日志--show query log
5、中继日志
6、二进制日志--binary log
1.Statement：每一条会修改数据的sql都会记录在binlog中。 2.Row:不记录sql语句上下文相关信息，仅保存哪条记录被修改。 3.Mixedlevel: 是以上两种level的混合使用，一般的语句修改使用statment格式保存binlog，statement无法完成主从复制的操作，则 采用row格式保存binlog,MySQL会根据执行的每一条具体 的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择 一种
五、数据库备份
1、mysql数据备份方式
工具一 MySQLdump工具备份
逻辑备份：类似查询满足条件的备份：select * from
工具二： xtrabackup工具备份：数据量比较大的时候使用
物理备份：备份文件+日志文件
基于压缩，备份速度快，还原快，自动备份检验，不会打断正在执行的事务，不支持远程备份
工具三、mydumper工具：https://www.jianshu.com/p/27e6ac408969
逻辑备份
备份：冷备份和热备份
根据备份的数据集合分为：完全备份、增量备份和差异备份
备份的对象：
数据
配置文件
OS相关的配置文件
二进制日志
复制相关的配置
代码：存储过程，存储函数和触发器
mysqldump备份策略：
周日做全备份+周一到周六备份二进制日志
恢复：完全备份+二进制文件到目前的事件
2、xtrabackup的备份，增量备份及恢复的工作原理
XtraBackup基于InnoDB的crash-recovery功能，它会复制InnoDB的data file，由于不锁表，复制出来 的数据是不一致的，在恢复的时候使用crash-recovery，使得数据恢复一致。
InnoDB维护了一个redo log，又称为transaction log（事务日志），它包含了InnoDB数据的所有改动 情况。当InnoDB启动的时候，它会先去检查data file和transaction log，并且会做两步操作：
XtraBackup在备份的时候，一页一页的复制InnoDB的数据，而且不锁定表，与此同时，XtraBackup还 有另外一个线程监视着transaction log，一旦log发生变化，就把变化过的log pages复制走。
为什么要 着急复制走呢？因为transaction log文件大小有限，写满之后，就会从头再开始写，所以新数据可能会 覆盖到旧的数据。
在prepare过程中，XtraBackup使用复制到的transaction log对备份出来的InnoDB data file进行crash recover
完全备份及还原：
1）在原主机做完全备份到/backups
xtrabackup --backup --target-dir=/backups/
scp -r /backups/* 目标主机:/backups
2）在目标主机上
1.预处理(确保数据一致，提交完成的事务，回滚未完成的事务)：xtrabackup --prepare --target-dir=/backups/
2.复制到数据库目录（数据库目录必须为空，mysql服务不能启动）：xtrabackup --copy-back --target-dir=/backups/
3.还原属性：chown -R mysql:mysql /var/lib/mysql
4.启动服务：systemctl start mariadb
xtrabackup完全，增量备份及还原
1 备份过程 1）完全备份：xtrabackup --backup --target-dir=/backups/base
2) 第一次修改数据；第一次增量备份：
xtrabackup --backup --target-dir=/backups/inc1 --incremental-basedir=/backups/base
3) 第二次修改数据；第二次增量备份：
xtrabackup --backup --target-dir=/backups/inc2 --incremental-basedir=/backups/inc1
4) scp -r /backups/* 目标主机:/backups/
5) 备份过程生成三个备份目录：/backups/{base,inc1,inc2}
2 还原过程
1) 预准备完全备份，--apply-log-only 阻止回滚未完成的事务
xtrabackup --prepare --apply-log-only --target-dir=/backups/base
2) 合并第一次增量备份到完全备份
xtrabackup --prepare --apply-log-only --target-dir=/backups/base --incremental-dir=/backups/inc1
3) 合并第二次增量备份到完全备份
xtrabackup --prepare --target-dir=/backups/base --incremental-dir=/backups/inc2
4) 复制到数据库目录，数据库目录必须为空，mysql服务不能启动
xtrabackup --copy-back --target-dir=/backups/base
5) 还原属性和启动服务：
chown -R mysql:mysql /var/lib/mysql
systemctl start mariadb
mysqldump
1.只备份一个数据库或者这个数据库的某个表 mysqldump database tables 2.-B 备份多个数据库 mysqldump -B DB1 3.-A 除了性能和内存中的系统数据库，其余数据库全备份 mysqldump -A 4.--master-data=2 # 默认为1，change master to非注释，2为注释 可以知道二进制备份的时间点；1是配合主从复制；2注释 启用二进制日志不让直接创建函数 测试： 1. mmysqldump database mysqldump hellodb > hellodb.sql # 只会备份数据库里的表，数据库本身需要手动创建 wc -l hellodb.sql 2.mmysqldump -B DB1 针对单个数据库备份 mysqldump -B hellodb > hellodb.sql mysqldump < hellodb.sql # 还原直接导入即可 3.备份所有数据库(不会备份--information_schema；performance_schema) mysqldump -uroot -proot -A > all.sql 4.mysqldump -A --master-data=2 > /data/all.sql -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=385; //此为注释了 只刷新一次日志事务 ==等价于flush logs mysqldump -F -A --single-transaction --master-data=2 > /data/all.sql -t # 只备份数据，不备份表结构 --flush-privileges # 刷新权限
----------------------------------------------------------------
1、完整备份一下（二进制日志启用）
mysqldump -A --master-data=2 > /data/all.sql
中间数据库发生了一些修改
2、删除还原的数据库目录
rm -rf /var/lib/mysql/*
3、还原
确保无人访问数据库
1）systemctl restart mariadb
2）查看二进制日志位置
mysql>show master logs;
3）根据 /data/all.sql日志位置和2）中的需要的二进制日志范围
mysqlbinlog --start-position=710 mysql-bin.000003 >> /data/inc.sql
mysqlbinlog mysql-bin.000003 >> /data/inc.sql
mysqlbinlog mysql-bin.000003 >> /data/inc.sql
4、先禁止用户访问，防火墙策略或者添加skip--networking(具体参数需要查一下)
1)不要直接还原，先停止二进制日志的服务
2)临时停止sql_log_bin二进制日志(因为二进制日志开启中，导入恢复的数据库，会增加二进制日志记录)
vim my.cfg配置文件中 添加skip--network
mysql> set sql_log_bin=off;
顺序执行一下完整备份和增量备份
mysql> source /data/all.sql
mysql> source /data/inc.sql
5、完成后开启二进制日志：set sql_log_bin=on;
6、验证一下，成功后并关闭禁用访问
六、数据库一些问题
1、mysql忘记root密码怎么办
1. 在配置文件里加上skip-grant-tables ，重启MySQL 2. 使用mysql -uroot -p 进入 3. 使用update 修改密码 mysql> USE mysql ; mysql> UPDATE user SET Password = password ( 'new-password' ) WHERE User='root' ;
2、判断主从延迟的方法
show slave status 查看 通过seconds_behind_master的值来判断 NULL 表示io_thread或是sql_thread有任何一个发生故障，也就是该线程的Running状态是No,而非 Yes. 0 该值为零，是我们极为渴望看到的情况，表示主从复制状态正常
3、varchar(100) 和varchar(200）的区别
varchar(100)最多存放100个字符，varchar(200)最多存放200个字符，varchar(100)和(200)存储hello所占空间一样，但后者在排序时会消耗更多内存 因为order by col采用fixed_length计算col长度memory引擎也一样
4、MySQL主要的索引类型
普通索引：是最基本的索引，它没有任何限制；
唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一；
主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值；
组合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被 使用。使用组合索引时遵循最左前缀集合；
全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较，mysql中MyISAM支持全文索引而InnoDB不支持；
5、如何加强MySQL安全，请给出可行的具体措施？
1.避免直接从互联网访问mysql数据库，确保特定主机才拥有访问权限。
禁用或限制远程访问 在my.cnf文件里设置bind-address指定ip
2.移除test数据库（默认匿名用户可以访问test数据库）
3.定期备份数据库
4..禁用local infile 存在访问控制漏洞
mysql> select load_file("/etc/passwd"); 在my.cnf里[mysqld]下添加set-variable=local-infile=0
5.移除匿名账户和废弃的账户
6.限制mysql数据库用户的权限
7.移除和禁用.mysql_history文件
6、生产一主多从从库宕机，如何手工恢复？
处理方法：重做slave 1. 停止slave 2. 导入备份数据 3. 配置master.info信息 4. 启动slave 5. 检查从库状态
7、误执行drop数据，如何通过xtrabackup恢复？
1. 关闭mysql服务 2. 移除mysql的data目录及数据 3. 将备份的数据恢复到mysql的data目录 4. 启动mysql服务
8、如何做主从数据一致性校验？
主从一致性校验有多种工具 例如checksum、mysqldiff、pt-table-checksum等
9、MySQL中MyISAM与InnoDB的区别，至少5点
a. InnoDB支持事务，而MyISAM不支持事务。
b. InnoDB支持行级锁，而MyISAM支持表级锁
c. InnoDB支持MVCC，而MyISAM不支持
d. InnoDB支持外键，而MyISAM不支持
e. InnoDB不支持全文索引，而MyISAM支持
10、网站打开慢，请给出排查方法，如是数据库慢导致，如何排查并 解决，请分析并举例？
1. 检查操作系统是否负载过高 2. 登陆mysql查看有哪些sql语句占用时间过长，show processlist; 3. 用explain查看消耗时间过长的SQL语句是否走了索引 4. 对SQL语句优化，建立索引
11、Binlog工作模式有哪些？各什么特点，企业如何选择？
1.row level行级模式 优点：记录数据详细（每行），主从一致 缺点：占用大量的磁盘空间，降低了磁盘的性能 2.statement level语句模式（默认） 优点：记录的简单，内容少 ，节约了IO，提高性能 缺点：导致主从不一致 3.MIXED混合模式 结合了statement和row模式的优点，会根据执行的每一条具体的SQL语句来区分对待记录的日志形式。 对于函数，触发器，存储过程会自动使用row level模式 企业场景选择： 1.互联网公司使用mysql的功能较少（不用存储过程、触发器、函数），选择默认的statement模式。 2.用到mysql的特殊功能（存储过程、触发器、函数）则选则MIXED模式 3.用到mysql的特殊功能（存储过程、触发器、函数），有希望数据最大化一致则选择row模式。
12、MySQL数据库cpu飙升到500%的话他怎么处理？
1 当 cpu 飙升到 500%时，先用操作系统命令 top 命令观察是不是 mysqld 占用导致的，如果不是，找出 占用高的进程，并进行相关处理 2 如果是 mysqld 造成的， show processlist，看看里面跑的 session 情况，是不是有消耗资源的 sql 在 运行。找出消耗高的 sql， 看看执行计划是否准确， index 是否缺失，或者实在是数据量太大造成。 3 一般来说，肯定要 kill 掉这些线程(同时观察 cpu 使用率是否下降)，等进行相应的调整(比如说加索引、 改 sql、改内存参数)之后，再重新跑这些 SQL。 4 也有可能是每个 sql 消耗资源并不多，但是突然之间， 有大量的 session 连进来导致 cpu 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等
浙公网安备 33010602011771号