🎰 Mysql 数据库基础原理

三大范式：
  • 第一范式（1NF）：强调的是列的原子性，即列不能够再分成其他几列。 
  •  第二范式（2NF）：首先是 1NF，另外包含两部分内容，一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。 
  • 第三范式（3NF）：首先是 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。
MySQL的三大主要分支：
    • mysql
    • mariadb
    • percona Server

 

how 如何安装Mysql数据库：

#centos7
yum info mariadb-server
yum -y install mariadb-server ;systemctl enable --now mariadb
#centos8
dnf info mysql-server  / dnf info mysql-server mariadb-server
yum -y install mysql-server ; systemctl enable --now mysqld ;ss -ntl
#ubuntu 安装
apt install mysql-server

MySQL初始化安装配置

mysql_secure_installation
回车
回车
y  这只口令
密码
y
y
y
一路y下去


修改初始化密码：
mysqladmin  -uroot -p'pe%b#S8ah)j-' password '密码'

 

🎟what is Mysql  ？ Mysql的组成部分和常用工具？

客户端常用工具：

服务器端程序：

  • mysql_safe
  • mysqld
  • mysqld_multi

how  基于Docker安装MySQL?

较为简单，只需要如下命令即可：

docker run --name mysql -d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=密码 mysql:5.7.30

初始化密码：

mysql --initialize-insecure --user=mysql --datadir=/data/mysql    //生成空密码

 

MySQL数据类型：

char（4）和Varchar（4）区别：

char：会自动填满，直到达到4 个字节 = 固定长度字节（数据更加规范）

Varchar：几个字节便几个字节 = 可变长度字节（更加节省空间）

💫面试题：varchar（50）能放几个utf-8的编码汉字？

综上所述，char和Varchar最大的区别是：char会自动填满，那么Varchar最大多能存放50，而且这里的50无论是字符还是汉字。

 

🏸24.3 MySQL 备份和恢复

systemctl stop mysql 
#备份数据
sysnc -a /var/lib/mysql 10.0.0.28:/data/    //备份数据


还原
yum-y install mysql-server
cp -a /data/mysql/* /var/lib/mysql
systemctl start mysqld
🔪xtrabackup 备份工具
yum -y install percona-xtrabckup  //centos8并没有提供
centos8 额外安装
ll percona-xtrabackup-24.2.4.20-1.e18.x86_64.rpm
yum -y install percona-xtrabackup-24.2.4.20-1.e18.x86_64.rpm
rpm -ql percona-xtrabackup-24.2.4.20-1.e18.x86_64.rpm
ll /usr/bin/innobackupex
file /usr/bin/xtrabackup

面试题：数据引擎：InnoDB 和 MyISAM的区别？

两者引擎之间的比较：

简单总结：

  • MyISAM 不支持事务存储，不能使用于关键业务场景的数据备份。
  • B-tree indexes中MyISAM 最整个表进行加锁。而InnoDB更加灵活只是让一行的数据加琐，不会出现MyISAM对整个表格加锁而其他人无法访问修改的特殊情况。
  • MVCC（多版本的并发支持），InnoDB支持而MyISAM并不支持。MVCC简单而言，便是数据库中在不同版本时间内，由于数据库的增删改查，形成动态的数据，届时看到的数据是不相同的。
  • Data caches(数据缓存)，InnoDB支持而MyISAM并不支持。Data caches，数据缓存可以极大提高数据访问的效率，大大提高性能。

 

⚽MySQL的索引结构

MySQL的INDEX索引

what 是索引？

定义：索引是排序的快速查找的特殊数据结构，定位作为查找条件的字段上，又称之为键Key，索引通常存储引擎实现。

简单理解：索引是一种特殊的数据结构，文件程序的内容好比一本书，而索引相当于目录或者书签，便于快速找到查询的数据内容。

那么，我们不妨温故以下数据结构的概念。

🏐概念解析： 数据结构

详解：(106条消息) 数据结构：八种数据结构大全_緑水長流*z的博客-CSDN博客_数据结构

数据结构（Data Structure）是指相互之间存在一种或多种特定关系的数据元素集合，是带有结构的数据元素的集合，它指的是数据元素之间的相互关系，即数据的组织形式。

数据结构看似复杂，其实可分为九类：

详解：(106条消息) 九大数据结构_FloriztW的博客-CSDN博客

数据结构按逻辑分类，可分为以下几种，如下图所示：

 

Mysql 中的索引：

索引的分类：

索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引：

MyISAM和InnoDB存储引擎：只支持BTREE索引， 也就是说默认使用BTREE，不能够更换。

存储引擎的类型及特点：

引擎名称

优点

缺陷

应用场景

MyISAM

独立于操作系统，这说明可以轻松地将其从Windows服务器移植到Linux服务器

不支持事务/行级锁/外键约束

适合管理邮件或Web服务器日志数据

InnoDB

健壮的事务型存储引擎；支持事务/行级锁/外键约束自动灾难恢复/AUTO_INCREMENT

　

需要事务支持，并且有较高的并发读取频率

MEMORY

为得到最快的响应时间，采用的逻辑存储介质是系统内存

当mysqld守护进程崩溃时，所有的Memory数据都会丢失；不能使用BLOB和TEXT这样的长度可变的数据类型

临时表

MERGE

是MyISAM类型的一种变种。合并表是将几个相同的MyISAM表合并为一个虚表

　

常应用于日志和数据仓库

ARCHIVE

归档的意思，支持索引，拥有很好的压缩机制

仅支持插入和查询功能

经常被用来当做仓库使用

 

索引的使用场景分类：

详解：再有人问你MySQL索引原理，就把这篇文章甩给他！ (qq.com)

    • Hash索引（Hash索引适合精确查找，但是范围查找不适合）
    • 二叉树
    • B树
    • B+数（MySQL中最常用的索引数据结构，选择的原因如下）
      • 所有数据记录节点都是按照键值的大小存放在同一层的叶子节点上，而非叶子结点只存储key的信息，这样可以大大减少每个节点的存储的key的数量，降低B+ 树的高度
      • B+ 树叶子节点的关键字从小到大有序排列
      • B+ 树的层级更少
      • B+ 树查询速度更稳定
      • B+ 树天然具备排序功能
      • B+ 树全节点遍历更快

索引优缺点：

优点
  1. MySQL数据类型都可被引用，便于数据库从快速查询
  2. 提高效能
  3. 唯一索引可确保数据的唯一性（一般为一行）

缺点
  1. 索引占据内存空间，尤其是数据库数据量非常大时
  2. 数据库的数据动态增删改查，索引也会随之发生变化，创建和维护需要花费大量时间。

使用原则
  1. 对经常更新的表就避免对其进行过多的索引，对经常用于查询的字段应该创建索引，
  2. 数据量小的表最好不要使用索引。
  3. 在一同值少的列上(字段上)不要建立索引。
 

why Mysql中使用索引解决的问题？

索引用于快速找出在某个列中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。

how Mysql中的索引使用？

Mysql索引原理：再有人问你MySQL索引原理，就把这篇文章甩给他！ (qq.com)

Mysql索引如何查询，详解：再有人问你MySQL是如何查询数据的，请把这篇文章甩给他！ (qq.com)

 

面试常问：Mysql 事务隔离级别

🎤事务管理（ACID）

原子性（Atomicity）

原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

一致性（Consistency）

事务前后数据的完整性必须保持一致。

隔离性（Isolation）

事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

持久性（Durability）

持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

事务的并发问题：

事务隔离级别

脏读

不可重复读

幻读

读未提交（read-uncommitted）

是

是

是

不可重复读（read-committed）

否

是

是

可重复读（repeatable-read）

否

否

是

串行化（serializable）

否

否

否

 

    • 脏读：事务A抢先读取了事务B数据（看了不该看的数据），但是事务B数据是动态的，和真正事务A未来读取的数据不一致，故称脏数据。
    • 不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。
    • 幻读：简单理解为海市蜃楼，侧重在于新增和删除数据，分为：读取数据但是忘记了，或者没有读取数据却认为读取了。

 

🔧  MySQL二进制日志管理

how 查看使用中的二进制日志文件

♠二进制日志 = 备份
SHOW PASTER EVENTS [IN 'log_name'][FROM pos][LIMIT [offset,] row_count]


#重启数据库命令
systemctl restart mysql

一般而言“二进制文件”增长速度大小 >> MySQL文件的增长速度

工作中“二进制”文件使用“行型”而不是“句型”。

how清除所有的二进制文件

RESET MASTER

切换二进制文件

FLUSH LOGS;

 

Mysql数据库丢失如何恢复？

systemctl start mariadb.service    //启动数据库
mysql < hellodb_innodb.sql    // 导入数据
mysqldump -A --master-data=2 > |grep > /opt/all.sql  //备份+压缩
ll /opt/all.sql.gz -h    //查看大小
#对数据库进行操作
mysql
use hellodb
inster teachers  values(null,'xiaoming',20,'M');
exit


ll /data/logbin/    //查看二进制文件

思路：

  • 定制"二进制"文件备份
  • 数据还原
    • 先“完全备份”还原
    • 再恢复“二进制文件”
  • 开启"二进制"文件备份
mysql
systemctl start mariadb
set sql_log_bin=0;       // 临时禁用二进制文件，对还原的增删改查操作不记录数据
source /opt/all.sql          //还原完全备份
ll /opt      //查看二进制文件
source /opt/binlog.sql;         //还原"二进制文件"
select *[file]               //查看还原文件
mysqldump  数据备份
-uroot   用户名
-p   密码
-A   完全备份
-F   刷新日志
-E   备份事件/一般不加（-A，加上去会显得有点多余）
-R   存储过程函数/一般不加
--triggers  触发器/一般不加
--single-transaction  开启单一事务
--master-data=1    主数据位置
--flush-privilegse   刷新权限
--default-character-set=uft8   指定字符集
--hex-blob  指定"二进制"文件数据
>$(BACKUP)/fullbak_${BACKUP_TIME}.sql   备份文件（文件夹）/文件路径

🎪 安装xtrackup 备份工具

安装下载 xtrabackup

#将下载文件拖入Xshell中
ls
复制
yum -y install percona-xtrabackup-24-2.4.20-1.el8.x86_64.rpm
如下图所示
rpm  percona-xtrabackup-24-2.4.20
 
 
 

🧪Mysql主从复制原理和实现

主从复制原理图：
#配置master主节点配置
vim /etc/my.cnf   //修改文件配置
开启二进制文件，配置文件插入以下内容
[mysql]
server_id=18
log-bin=/data/mysql/logbin/mysql-bin
创建文件夹
cat /etc/my.cnf   //查看mysql文件路径
mkdir -p /data/mysql/logbin   //复制路径
chown -R mysql.mysql /data/mysql/     //　chown将指定文件修改为所有者权限
systemctl restart mysql      //重启数据库
ll /data/mysql/logbin/           //查看二进制是否生成


mysql  //进入mysql
>select @@server_id    //查看server_id
>show master logs    //查看二进制位置
创建账号（mysql8.0开始创建和授权命令需要分开进行）
>create user repluser@'10.0.0.%' identified by '123456';             // 设置账号和密码
授权账号
>grant relication slave on *.* to repluser@'10.0.0.%';  //将主权限全部授权给slave
>show processlist     //查看主节点


从节点配置
修改配置文件
vim /etc/my.cnf
插入以下内容,开启二进制问价
[mysql]
server_id=18
log-bin=/data/mysql/logbin/mysql-bin
read-only
systemctl restart mysql    //重启配置文件
mysql
>show processlist   //查看线程
>help  change master to    //查看命令帮助，并复制内容和格式
 
CHANGE MASTER TO
 MASTER_HOST='10.0.0.18'    //主节点网关地址
 MASTER_USER='repluser'    //复制账户名称
 MASTER_PASSWOED='123456'    //密码
 MASTER_PORT='3306'     //端口，不需要配置
 MASTER_LOG_FILE='mysql-bin.0000001'   //最初复制的文件
 MASTER-LOG-POS='156'    //同上上截图所示
 
>show slave status\G
查看从主节点的数据时间差 "Seconds_Behind_Master:NULL"
>start slave     //同时开启主从线程
>show processlist

另一台虚拟机

show databases    //查看数据库
create darabase db1; //分别查看两台虚拟机是否同时出现"db1"文件

📔复杂排错的解决方法

Mysql数据库中，如果有一行的数据出现了错误，便会停住在错误行，剩下的数据则无法顺利进行，为了保证整个数据顺利且有效的运行，可以展示忽略错误，到最后运行完毕再对数据进行排错和修改

SET GLOBAL sql_slave_skip_counter = N
#服务器选项，只读系统变量，指定跳过事件的ID
[mysqld]
slave_skip_error=1007|ALL


或者
#SET GLOBAL sql_slave_skip_counter = 1;
#start slave