数据架构设计实现(1)-数据库高阶应用

最新推荐文章于 2024-08-28 08:00:00 发布

开端

最新推荐文章于 2024-08-28 08:00:00 发布

阅读量160

点赞数

分类专栏： Sptring mysql springJPA 文章标签：数据库 mysql mariadb

本文链接：https://blog.csdn.net/tanwei010915/article/details/122490140

版权

Sptring 同时被 3 个专栏收录

18 篇文章 1 订阅

订阅专栏

mysql

16 篇文章 0 订阅

订阅专栏

springJPA

9 篇文章 0 订阅

订阅专栏

文章目录

1. MySQL的执行过程分析
2. MySQL内部的日志类型作用及分析
3. MySQL数据备份恢复以及执行优化

1. MySQL的执行过程分析

1.1. MySQL 5.7安装步骤

1、下载rpm包
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar

# 如果提示需要账号密码，就用这个方式下载
# wget --http-user=youremail@email.com --http-passwd=yourpassword https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar

2、查看系统是否自带mariadb
rpm -qa | grep mariadb

3、将查出的mariadb进行卸载
rpm -e --nodeps mariadb-libs-5.5.64-1.el7.x86_64

4、把刚刚下载的mysql tar解压
tar -xvf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar

5、在解压目录安装如下4个mysql核心包
rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm

6、安装到server时缺少依赖报错
error: Failed dependencies:
        libaio.so.1()(64bit) is needed by mysql-community-server-5.7.28-1.el7.x86_64
        libaio.so.1(LIBAIO_0.1)(64bit) is needed by mysql-community-server-5.7.28-1.el7.x86_64
        libaio.so.1(LIBAIO_0.4)(64bit) is needed by mysql-community-server-5.7.28-1.el7.x86_64

7、安装缺少的依赖
yum -y install libaio

8、再次安装server
rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm

9、启动mysql服务
service mysqld start

10、查看v5.7版本的默认登录密码
grep password /var/log/mysqld.log

11、登录到mysql命令行，修改默认密码
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

12、会告诉你密码不符合规范，可以修改校验等级和长度后再次执行上面的语句
set global validate_password_policy=LOW;
set global validate_password_length=6; 

13、账户授权
use mysql;
select host,user from user;
# 所有ip都可以访问数据库
grant all privileges on *.* to gavin@'%' identified by '123456';
# 只有内网网段ip才可访问，并授权账号可以授权其他人
# grant all privileges on *.* to gavin@'192.168.%' identified by '123456' with grant option;
flush privileges;

1.2. 学习的基础数据导入

create database icoding_admin;

DROP TABLE IF EXISTS `ad_role`;

CREATE TABLE `ad_role` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `role_name` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `ad_role` (`id`, `role_name`)
VALUES
	(1,'vip1'),
	(2,'vip2'),
	(3,'vip3');
	
DROP TABLE IF EXISTS `ad_user`;

CREATE TABLE `ad_user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL DEFAULT '',
  `password` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `ad_user` (`id`, `username`, `password`)
VALUES
	(1,'arry','123456'),
	(2,'gavin','1234567'),
	(3,'coding','123456');
	
DROP TABLE IF EXISTS `ad_user_role`;

CREATE TABLE `ad_user_role` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `role_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `ad_user_role` (`id`, `user_id`, `role_id`)
VALUES
	(1,1,1),
	(2,1,2),
	(3,1,3),
	(4,2,2),
	(5,3,3),
	(7,2,3);

3. 基础知识回顾

where条件解析顺序
- MySQL：自左向右
- Oralce：自右向左
SQL执行顺序
- FROM
- ON
- JOIN
- WHERE
- GROUP BY
- HAVING
- SELECT
- DISTINCT
- ORDER BY
- LIMIT
全文索引
- 只有在MyISAM的引擎才可以用，只能使用在CHAR、VARCHAR、TEXT字段使用使用
MySQL中SQL执行的过程-MySQL 5.7
- 连接器
- 查询缓存
- 分析器（词法、语法）
- 优化器
- 执行器

在这里插入图片描述

# 声明使用查询缓存，但是不建议这样使用
select SQL_CACHE * from ad_user;
# 注意：MySQL 8.0 把查询缓存这个模块去掉了

MySQL数据引擎

# MySQL支持的数据引擎
mysql> show engines;

存储引擎	说明
MyISAM	高速查询引擎，不支持事物
InnoDB	v5.5以后是MySQL的默认引擎
Archive	数据压缩存储引擎，便于数据归档
Memory	内存存储引擎

对比MyISAM和InnoDB

对比	InnoDB	MyISAM
存储文件的形式	.frm表定义文件，.ibd存放数据和索引的	.frm表定义文件，.myd数据文件，.myi索引文件
锁	表、页、行	表
事务	支持	不支持
CRUD	可同时读、写	只可同时读，不可同时写

# 查询表的引擎
show table status like '%ad_user%' \G;

MySQL数据库数据存储的位置

cd /var/lib/mysql
# 这个目录下存放的是数据库对应的各个数据库的数据文件

MySQL配置文件默认路径

vi /etc/my.cnf

2. MySQL内部的日志类型作用及分析

MySQL常用的日志有下面几个

错误日志

show variables like '%log_error%';
log_error=/var/log/mysqld.log
log_warnings=2
# log_warnings= 0| 1| 2 
# 0 关闭
# 1 开启-default
# >1 失败的连接，拒绝访问的错误也会记录

查询日志

查询日志会将所有数据库的操作都会记录（general log 通用日志）

消耗I/O，默认不开

show variables like '%general_log%';
log_output=FILE
FILE、TABLE、FILE,TABLE、NONE

慢查询日志

show variables like '%slow%';
[mysqld]
slow_query_log=ON
slow_launch_time=3
slow_query_log_file=/usr/local/slow.log

chown -R mysql:mysql /usr/local/

select sleep(3),user from user;

直接查看慢查询日志

Time                 Id Command    Argument
# Time: 2020-06-17T13:05:20.509651Z
# User@Host: root[root] @ localhost []  Id:     2
# Query_time: 12.000509  Lock_time: 0.000111 Rows_sent: 3  Rows_examined: 3
use icoding_admin;
SET timestamp=1592399120;
select sleep(4),username from ad_user;
# Time: 2020-06-17T13:09:14.528655Z
# User@Host: root[root] @ localhost []  Id:     2
# Query_time: 12.000488  Lock_time: 0.000097 Rows_sent: 3  Rows_examined: 3
SET timestamp=1592399354;
select sleep(4),id from ad_user;

可以使用mysql提供的慢查询命令来查看

# 根据时间降序
mysqldumpslow -s -t /var/lib/mysql/DB213-slow.log
# 根据记录数降序
mysqldumpslow -s -r /var/lib/mysql/DB213-slow.log
# 根据执行次数降序
mysqldumpslow -s -C /var/lib/mysql/DB213-slow.log
# 帮助手册
mysqldumpslow --help

内容

Reading mysql slow query log from /var/lib/mysql/DB213-slow.log
Count: 1  Time=12.00s (12s)  Lock=0.00s (0s)  Rows=3.0 (3), root[root]@localhost
  select sleep(N),id from ad_user

Count: 1  Time=12.00s (12s)  Lock=0.00s (0s)  Rows=3.0 (3), root[root]@localhost
  select sleep(N),username from ad_user

二进制日志：binlog

这个是数据库中最重要的日志，会记录所有DML，不会记录select

事务日志
中继日志：reply log

3. MySQL数据备份恢复以及执行优化

3.1. Binlog是有三种模式的

statement
- 纯粹的记录DML的语句
- update ad_user set username=‘gavin.huang’ where id=1;
- delete from ad_user where id=1;

# statement
# MySQL会自动生成一个mysql-bin-00001.log
# chown -R mysql:mysql /usr/local/binlog/
log_bin=/usr/local/binlog/mysql-bin
binlog_format=statement
# binlog日志切割的大小
max_binlog_size=500m
# binlog过期清理时间
expire_logs_days=3

[mysqld]
server-id=213

mysql> show binlog events in 'mysql-bin.000001';

row（v5.7版本默认是row模式）
- 过去的历史值和现在的新值

# row模式的日志查询
mysqlbinlog --base64-output=decode-rows -vv mysql-bin.000002

查询的结果

### DELETE FROM `icoding_admin`.`ad_user`
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='coding' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
###   @3='123456' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */

### UPDATE `icoding_admin`.`ad_user`
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='gavin' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
###   @3='1234567' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='gavin.huang' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
###   @3='1234567' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */

怎么快速找到误操作的语句

row模式的定位

mysqlbinlog --base64-output=decode-rows --start-datetime='2020-06-17 22:10' --stop-datetime='2020-06-17 22:25' -vv mysql-bin.000002

mysqlbinlog也可以查询statement模式的数据，得到时间区间

作业：自己设置一下statement和row模式mixed模式的binlog记录，自己查看一下相关内容

如果进行大批量的数据操作，这个时候数据库是安全，不让MySQL记录

mysql> set sql_log_bin=0; #临时关闭binlog

mixed

# 混合模式
statement：95%

3.2. 数据备份

备份的场景和分析

全量备份
差异备份

在这里插入图片描述

增量备份

在这里插入图片描述

时间点备份

备份类型

热备：热备是不能通过简单的copy命令
温备：只能进行读操作
冷备
物理备份：copy文件
逻辑备份

常用备份工具

mysqldump
Percona提供的xtrabackup

# mysqldump --help
# --master-data 0(不记录position) 1(记录position位置) 2(记录position位置并注释该条)
# routines 存储过程
# triggers 触发器
# events 事件
# single-transaction
# --ignore-table=icoding_admin.ad_user_role --ignore-table=icoding_admin.ad_user
# 基于innodb引擎
mysqldump -uroot -p123456 -h127.0.0.1 --master-data=2 --routines --triggers --events --single-transaction --databases icoding_admin --ignore-table=icoding_admin.ad_user_role > mydb.sql

3.3. 数据恢复

备份如何设计

全量备份的方式

使用crontab结合mysqldump来做定时备份
增量时间点补偿
- 如何补偿
- 考虑修改的变化：update、delete
- 借助我们的binlog

# 第一步，先做全量备份,可以定时处理
18 20 * * * mysqldump -uroot -p123456 --databases icoding_admin > /usr/local/bak/bak.sql
16 20 * * * rm -rf /usr/local/bak/bak.sql
# 第二步，开启binlong statement/row/mixed
# 对binlog做数据导出
# 场景1:如果数据量小，比如关键的某一条数据，row模式下可以直接手工处理
mysqlbinlog --base64-output=decode-rows --start-position=9623 --stop-position=9858  -vv mysql-bin.000002
# 场景2:数据量特别大，需要导出进行处理
mysqlbinlog --start-position=9623 --stop-position=9858 mysql-bin.000002 > ist.sql
# 第三步，增量数据的恢复根据需要把当前binlog记录关掉
mysql> set sql_log_bin=0;
mysql> source ist.sql