备案控制台
开发者社区 数据库 文章 正文

服务端持久化层MySQL零基础入门实战2

2023-05-24 51
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
RDS MySQL Serverless 高可用系列，价值2615元额度，1个月
简介： 服务端持久化层MySQL零基础入门实战

4.MySQL数据库管理

4.1.MySQL用户账号管理

1、新建用户

语法：create user '用户名'@'来源地址' identifled by '密码'
create user 'amber'@'192.168.159.%' indentified by '123456'

248bb611387f423ba87574d22b53f953.jpg


2、删除用户

语法：drop user '用户名'@'来源地址';
drop user 'amber'@'192.168.159.%';

127794a139b0431f8bc1773c59d0b611.jpg

3、重命名及修改主机

格式：RENAME USER ‘原用户名’@’原主机’ to ‘新用户名’@’新主机’;      #可以只修改用户名或只修改主机

create user 'amber'@'192.168.159%' to 'admin'@'%';

4、给用户设置密码

set password=password('密码');  //为当前登录的用户修改密码
set password for '用户名'@'来源地址'=password('密码'); //为其他用户设置密码

4.2.MySQL用户授权操作

1、授予用户权限

格式：grant 权限列表 on 库名.表名 to '用户名'@'来源地址' [identified by 密码];
//将所有库的所有表的所有权限付给admin用户
grant all on *.* to 'admin'@'192.168.159.%' identified by 123456;

2、查看用户权限

格式：show grants; //查看当前登录的用户的授权信息
show grants for '用户名'@'来源地址';

//查看admin用户的所有权限
show grants for 'admin'@'192.168.159.%';

3、撤销用户权限

格式：revoke 权限列表 on 库名.表名 from '用户名'@'来源地址';
//撤销admin用户的drop和create权限
revoke drop,create on *.* from 'amber'@'%';

4、常见的权限列表

ALL：设置除 GRANT OPTION之外的所有权限，如需设置，授权时加上  WITH GRANT OPTION
ALTER：允许使用 ALTER TABLE，修改表
CREATE：允许使用 CREATE TABLE，创建表
CREATE USER：用户管理权限
DELETE：允许使用 DELETE，删除数据
DROP：允许使用 DROP TABLE，删除表
INSERT：允许使用 INSERT，在表中插入信息
REPICATION SLAVE：从主服务器中读取二进制日志事件
SELECT：允许使用 SELECT，查看表内数据
SHOW DATABASES：允许使用 SHOW DATABASES显示所有数据库
UPDATE：允许使用 UPDATE修改表内的数据

4.3.MySQL日志管理

MySQL日志包括：

  • 错误日志
  • 通用查询日志
  • 二进制日志
  • 慢速查询日志

1、错误日志

包含了当MySQL启动和停止时，以及服务器在运行过程中发生的任何错误时的相关信息，默认在安装目录"/用户自己的安装路径/mysql/data/“下的"主机名.err”。

可以在登录时使用“–log-error=文件路径及文件名”的方式来指定mysql保存错误日志的位置，或者修改著配置文件/etc/my.cnf，在[mysqld]下方添加“log-error=文件路径及文件名”，重启mysqld服务。

vi /etc/my.cnf
[mysqld]
log-error=mysql_error.log //默认路径在/用户自己指定的mysql目录

2、通用查询日志

mysql所有连接和语句都将被记录。默认关闭此项日志记录，一般用作调试，平时开启会占用大量的磁盘空间。

可以在登录时使用“–log=文件路径及文件名”或“-l 文件路径及文件名”选项指定，或者修改著配置文件/etc/my.cnf，在[mysqld]下面添加“log = ON”和“log=文件路径及文件名”，重启mysqld服务。

vi /etc/my.cnf
[mysqld]
log=ON
log= /usr/local/mysql/data/mysql_general.log     //绝对路径可以省略，保证 mysql对该目录具有写入权限

3、二进制日志

包含了所有更新了的数据或者已经潜在更新了数据的所有语句，记录了数据的更改。主要目的是在恢复时能够最大可能地恢复数据库。默认是开启的，默认路径在/usr/local/mysql/data/下的，以“mysql-bin”开头的二进制日志。

可在登录时使用“ --log-bin=文件路径及文件命前缀”选项指定；或修改主配置文件/etc/my.cnf中[mysqld]下的“log-bin =文件路径及文件名”修改存放路径。

可用 mysqlbinlog命令查看二进制日志文件。

vi /etc/my.cnf
[mysqld]
log-bin=mysql-bin  //默认就有，无需修改即可

4、慢查询日志

记录所有执行时间超过long_query_time秒的SQL语句，可用于找到执行时间长的查询，以用于优化。默认未开启。

可在登录时使用“–log-slow-queries[=文件路径及文件名]”选项开启，若为指定文件路径及文件会，会在 /usr/local/mysql/data下生成“主机名 -slow.log”；或修改主配置文件/etc/my.cnf，在[mysqld]下添加“long_query_time”和“log-slow-queries =文件路径及文件名”，重启 mysqld服务。

vi /etc/my.cnf
[mysqld]
long_query_time = 5  //单位秒
log-slow-queries = mysql_slow.log

4.4.MySQL数据乱码问题

1、MySQL数据乱码的可能原因

（1）服务器系统字符设置问题

（2）数据表语言设置问题

（3）客户端连接语言系统的问题

2、解决办法

（1）在创建数据库时设定

格式：create database 库名 character set 'utf8' collate 'utf8_general_ci'

案例：create database lixiang character set 'utf8' collate 'utf8_general_ci'

（2）在创建表时设定字符集

格式：CREATE TABLE表名(字段格式) DEFAULT CHARSET=utf8;
案例：create table student(name char(40),age int(20)) default charset=utf8;

（3）使用 SET NAMES设置默认字符集

格式：set names utf8;

（4）永久修改，修改主配置文件在[mysql]字段中加入：default_character_set=utf8;

 vim /etc/my.cnf
[mysql]
//注意，不是[mysqld]！！！否则启动时报错！！！（若为 yum安装的不会报错）
default-character-set = utf8

5.MySQL完全备份与恢复

5.1.数据备份的重要性

在生产环境中，数据的安全性是至关重要的，任何数据的丢失都有可能产生严重的后果。

造成数据丢失的原因如下：

  • 程序错误
  • 人为错误
  • 计算机失败
  • 磁盘失败
  • 灾难（火灾、地址）和盗窃

5.2.数据备份的分类

1、从物理与逻辑的角度，备份可以分为物理备份和逻辑备份。

（1）物理备份：对数据库操作系统的物理文件（如数据文件、日志文件等）的备份。物理备份又可以分为脱机备份（冷备份）和联机备份（热备份）。

  • 冷备份：是在关闭数据库的时候进行的。
  • 热备份：数据库处于运行状态，这种备份方法依赖于数据库的日志文件。
  • 温备份：数据库锁定表格（不可写入但可读）的状态下进行的。

（2）逻辑备份：对数据库逻辑组件（如表等数据库对象）的备份

2、从数据库的备份策略角度，备份可分为完全备份、差异备份和增量备份。

（1）**完全备份：**每次对数据进行完整的备份。

  • 对整个数据的备份、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是差异备份与增量备份的基础。
  • 优点：备份与恢复操作简单方便
  • 缺点：数据存在大量的重复：占用大量的空间，备份与恢复的时间长
  • （2）**差异备份：**备份的时间节点从上一次完全备份之后被修改过的所有文件。
  • 备份的时间节点是从上一次完整备份起，备份数据量会越来越大。恢复数据时只恢复上一次的完全备份与最近一次的差异备份。
  • （3）**增量备份：**只有那些在上一次完全备份或者增量备份后被修改的文件才会被备份
  • 以上一次完全备份或上次的增量备份的时间为时间点，仅备份这之间的数据变化，因而备份的数据量小，占用空间小，备份速度快。但恢复时，
  • 需要从上一次的完整备份起到最后一次增量备份依次恢复，如中间某次的备份数据损坏，将导致数据丢失。

5.3.MySQL完全备份操作

1、直接打包数据文件

进入到数据目录文件：
cd /data/mydata/
tar Jcf mysql_all-$(date +%F).tar.xz /data/mydata/

模拟删除数据：
删除school库
rm -rf /data/mydata/school 

show databases;


70d7b38d63814e1d9f919f5438fa71c6.jpg

恢复数据：
tar -xf all_data-2021-11-10.tar.xz
进入到解压目录中：
cd /root/mysql_data_bak/data/mydata
mv * /data/mydata/

再次登录MySQL查看：
show databases;

abdc1f092c454a30adcea06bb35e87b9.jpg

2、使用专用的备份工具mysqldump

MySQL自带的备份工具，相当方便对MySQL进行备份。通过改命令工具可以将指定的库、表、或者全部的库导出为SQL脚本，在需要时可以进行数据恢复。

使用mysqldump对MySQL完全备份操作

  • 数据备份参数：
--all-databases, -A： 备份所有数据库
--databases, -B： 用于备份多个数据库，如果没有该选项，mysqldump 把第一个名字参数作为数据库名，后面的作为表名。使用该选项，mysqldum把每个名字都当作为数据库名。
--force, -f：即使发现sql错误，仍然继续备份
--host=host_name, -h host_name：备份主机名，默认为localhost
--no-data, -d：只导出表结构
--password[=password], -p[password]：密码
--port=port_num, -P port_num：制定TCP/IP连接时的端口号
--quick, -q：快速导出
--tables：覆盖 --databases or -B选项，后面所跟参数被视作表名
--user=user_name, -u user_name：用户名
--xml, -X：导出为xml文件

（1）对单个库进行完全备份

格式：mysqldump -u用户名 -p密码 [选项] [数据库名] >/备份路径/备份文件名
案例：mysqldump -uroot -p123456 school>/root/mysql_data_bak/all_data-$(date +%Y%m%d).sql

（2）对多个库进行完全备份

格式：mysqldump -u用户名 -p密码 [选项] --databases [数据库名1] [数据库名2] >/备份路径/备份文件名

案例：mysqldump -uroot -p123456 --databases school mysql>/root/mysql_data_bak/school+mysql-$(date +%Y%m%d).sql

（3）对全部数据库进行备份

格式：mysqldump -u用户名 -p密码 [选项] --all-databases >/备份路径/备份文件名
案例：mysqldump -uroot -p123456 --opt --all-databases >/root/mysql_data_bak/all_data-$(date +%Y%m%d).sql

（4）对表进行完全备份

格式：mysqldump -u用户名 -p密码 [选项] 数据库名 表名 >/备份路径/备份文件名
案例：mysqldump -uroot -p123456 school student >/root/mysql_data_bak/school_student-$(date +%Y%m%d).sql

（5）对表结构的备份

格式：mysqldump -u用户名 -p密码 -d 数据库名 表名 >/备份路径/备份文件名
案例：mysqldump -uroot -p123456 -d school student >/root/mysql_data_bak/school_student_desc-$(date +%Y%m%d).sql

a84d787c454442bdaf24f79978d19f5f.jpg

mysql容器mysqldump命令备份

（1）对单个库进行完全备份

docker exec 容器名（或容器id）sh -c 'exec mysqldump -u用户名 -p密码 数据库名' >/备份的本地路径地址 

备份数据库结构：
docker exec mysql sh -c 'exec mysqldump -uroot -p123456 -t school' >/mysql/backup-data/school-$(date "+%Y-%m-%d_%H:%M:%S").sql
docker exec mysql sh -c 'exec mysqldump -uroot -p123456 school' >/mysql/backup-data/school-$(date "+%Y-%m-%d_%H:%M:%S").sql

bdf5c9cd8d3d4474adaebccdd79719bf.jpg

（2）对多个数据库进行完全备份

docker exec 容器名（或容器id）sh -c 'exec mysqldump -u用户名 -p密码 --databases 数据库名1 数据库名2' >/备份的本地路径地址 

docker exec mysql sh -c 'exec mysqldump -uroot -p123456 --databases school mysql' >/mysql/backup-data/school+mysql-$(date "+%Y-%m-%d_%H:%M:%S").sql

7c16fab3539e4349a77b6961a4eb3c22.jpg


（3）对所有数据库进行完全备份

docker exec 容器名（或容器id）sh -c 'exec mysqldump -u用户名 -p密码 --all-databases' >/备份的本地路径地址 

docker exec mysql sh -c 'exec mysqldump -uroot -p123456 --all-databases' >/mysql/backup-data/all-databases-$(date "+%Y-%m-%d_%H:%M:%S").sql


a6ee115aeb5c41aaafa2d9e4086990ac.jpg

（4）对表进行完全备份

docker exec 容器名（或容器id）sh -c 'exec mysqldump -u用户名 -p密码 数据库名 表名' >/备份的本地路径地址 

docker exec mysql sh -c 'exec mysqldump -uroot -p123456 school student' >/mysql/backup-data/school_student-$(date "+%Y-%m-%d_%H:%M:%S").sql

887d90f536da4698bd81f038d98d8685.jpg

（5）对表结构的备份

docker exec 容器名（或容器id）sh -c 'exec mysqldump -u用户名 -p密码 -d 数据库名 表名' >/备份的本地路径地址 

docker exec mysql sh -c 'exec mysqldump -uroot -p123456 -d school student' >/mysql/backup-data/desc_school_student-$(date "+%Y-%m-%d_%H:%M:%S").sql

4e71a5d841b74386b7b7511247282318.jpg

5.4.恢复数据库

1、使用mysqldump备份后，恢复数据库

（1）source命令

语法：source 备份的sql脚本路径
案例：source /data/mydata/all_data-20211110.sql

（2）mysql命令

格式：
mysql -u用户名 -p密码 < 备份的脚本路径
mysql -u用户名 -p密码 库名 < 表备份的脚本路径

案例：
mysql -uroot -p123456 </data/mydata/all_data-20211110.sql
mysql -uroot -p123456 school </data/mydata/school_student-20211110.sql

2、mysql容器进行数据恢复

（1）恢复所有库

docker exec -i 容器名（或容器id）sh -c 'exec mysql -u用户名 -p密码' </备份的本地路径地址 

docker exec -i mysql sh -c 'exec mysql -uroot -p123456' </mysql/backup-data/all-databases-2021-11-04_09\:06\:27.sql

（2）恢复某个表库

先恢复库结构：
docker exec mysql sh -c 'exec mysqldump -uroot -p123456 school' >/mysql/backup-data/desc_school-$(date "+%Y-%m-%d_%H:%M:%S").sql
在恢复库数据：
docker exec mysql sh -c 'exec mysqldump -uroot -p123456 school' >/mysql/backup-data/school-$(date "+%Y-%m-%d_%H:%M:%S").sql

（3）恢复某个表

docker exec -i 容器名（或容器id）sh -c 'exec mysql -u用户名 -p密码 数据库名' </备份的本地路径地址 

docker exec mysql sh -c 'exec mysqldump -uroot -p123456 school' >/mysql/backup-data/school_student-2021-11-04_09\:15\:42.sql

5.5.Mysql数据库备份脚本

#!/bin/bash
#MySQL数据库备份脚本
#设置登录变量
MY_USER="root"
MY_PASS="123456"
MY_HOST="192.168.159.30"
MY_CONN="-u$MY_USER -p$MY_PASS -h$MY_HOST"
#设置备份数据库
MY_DB1="school"
#定义备份路径、工具、时间、文件名
BF_DIR="/mysql/backup/"
BF_CMD="/usr/bin/mysqldump"
BF_TIME=$(date +%Y%m%d-%H%M)
SQL_NAME="$MY_DB1-$BF_TIME"
#备份为.sql脚本，然后打包压缩（打包后删除原文件）
[ -d $BF_DIR] || mkdir -p $BF_DIR
cd $BF_DIR
$BF_CMD $MY_CONN --databases $MY_DB1 >$SQL_NAME.sql
/bin/tar zcf $SQL_NAME.tar.gz $SQL_NAME.sql --remove &>/dev/null

7b1da75b9a144f9d965cb33e823aa874.jpg

  • 加入定时：crontab -e
  • 0 2 * * * /root/mysql-backup.sh
  • 查看定时：crontal -l


d6edfeec83b548c19ed2f313c5f57820.jpg

6.MySQL增量备份与恢复

6.1.MySQL增量备份的概念

使用 mysqldump进行完全备份，备份的数据中有重复数据，备份时间与恢复时间长。而增量备份就是备份自上一次备份之后增加或改变的文件或内容。

1、增量备份的特点

  • 没有重复数据，备份量不大，时间短
  • 恢复麻烦：需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复。
  • MySQL没有提供直接的增量备份办法，可以通过 MySQL提供的二进制日志（binary logs）间接实现增量备份。
  • 2、MySQL二进制日志对备份的含义

二进制日志保存了所有更新或者可能更新的数据库操作。

二进制日志在启动MySQL服务器后开始记录，并在文件达到max_binlog_size所设置的大小或者收到flush logs命令后重新创建新的日志。

6.2.MySQL增量恢复

1、应用场景

（1）人为的SQL语句破坏了数据库

（2）在进行下一次全备之前发生系统故障导致数据库数据丢失

（3）在主从架构中，主库数据发生了故障

2、增量恢复的方法

（1）一般的恢复：备份的二进制日志内容全部恢复

格式：mysqlbinlog [–no-defaults] 增量备份文件 | mysql -u用户名 -p密码

（2）基于时间点恢复：便于跳过某个发生错误的时间点实现数据恢复

  • 从日志开头截止到某个时间点的恢复：
格式：mysqlbinlog [--nodefaults] --stop-datetime='年-月-日 小时:分钟:秒' 二进制日志文件 | mysql -u用户名 -p密码

案例：mysqlbinlog --no-defaults --stop-datetime='2016-05-07 15:21:17' /data/mydata/mysql-bin.000006 | mysql -uroot -p123456
  • 从某个时间点到日志结尾的恢复：
格式：mysqlbinlog [--nodefaults] --start-datetime='年-月-日 小时:分钟:秒' 二进制日志文件 | mysql -u用户名 -p密码

案例：mysqlbinlog --nodefaults --start-datetime='2016-05-07 15:21:17' /data/mydata/mysql-bin.000006 | mysql -uroot -p123456
  • 从某个时间点到某个时间点的恢复：
格式：mysqlbinlog [--nodefaults] --start-datetime='年-月-日 小时:分钟:秒' --stop-datetime='年-月-日 小时:分钟:秒' 二进制日志文件 | mysql -u用户名 -p密码

案例：mysqlbinlog --nodefaults --start-datetime='2016-05-07 15:21:17' --stop-datetime='2017-05-07 15:21:17' /data/mydata/mysql-bin.000006 | mysql -uroot -p123456

（3）基于位置恢复：可能在同一时间点既有错误的操作也有正确的操作，基于位置进行恢复更加精准

格式：

mysqlbinlog --stop-position='操作id' 二进制日志文件 | mysql -u用户名 -p密码

案例：mysqlbinlog --stop-position='246' /data/mydata/mysql-bin.000006 | mysql -uroot -p123456

mysqlbinlog --start-position='操作id' 二进制日志文件 | mysql -u用户名 -p密码

案例：mysqlbinlog --start-position='246' /data/mydata/mysql-bin.000006 | mysql -uroot -p123456

7.MySQL存储引擎

7.1.存储引擎概念介绍

  • MySQL中的数据用各种不同的技术存储在文件系统中，每一种技术都使用不同的存储机制、索引技巧，锁定水平并最终提供不同的功能和能力，这些不同的技术以及配套的功能在MySQL中称为存储引擎。
  • 存储引擎就是 MySQL讲数据存储在文件系统中的存储方式或者存储格式目前 MySQL常用的两种存储引擎：MyISAM、InnoDB。
  • MySQL存储引擎是 MySQL数据库服务器中的组件，负责为数据库执行实际的数据 I/O操作，使用特殊存储引擎的主要优点之一在于，仅需提供特殊应用所需的特性，数据库中的系统开销较小，具有更有效和更高的数据库性能。
  • MySQL系统中，存储引擎处于文件系统之上，在数据保存到数据文件之前会传输到存储引擎，之后按照各个存储引擎的存储格式进行存储。

7.2.MyISAM介绍

1、MyISAM概述

MyISAM存储引擎是MySQL关系数据库系统5.5版本之前默认的存储引擎，前身是ISAM。

ISAM是一个定义明确且历经时间考验的数据表格管理方法，在设计之时就考虑到数据库被查询的次数远大于更新的次数。

ISAM的特点：ISAM执行读取操作的速度很快，而且占用不大量的内存和存储资源，它不支持事物，不能够容错。

MyISAM管理非事务表，是 ISAM的扩展格式，提供 ISAM里所没有的索引和字段管理的大量功能。

MyISAM使用一种表格锁定的机制，以优化多个并发的读写操作，MyISAM提供高速存储和检索，以及全文搜索能力，受到web开发的青睐。

2、MyISAM的特点

（1）不支持事务

（2）表级锁定形式，数据在更新时锁定整个表

（3）数据库在读写过程中相互阻塞，会在数据写入的过程中阻塞用户数据的读取，也会在数据读取的过程中阻塞用户的数据写入

（4）可以通过 key_buffer_size来设置缓存索引，提高访问性能，减少磁盘 IO的压力但缓存只会缓存索引文件，不会缓存数据

（5）采用 MyISAM存储引擎数据单独写入或读取，速度过程较快而且占用资源相对较少

（6）MyISAM存储引擎不支持外键约束，只支持全文索引

（7）每个 MyISAM在磁盘上存储成三个文件，每一个文件的名字以表的名字开始，扩展名指出文件类型

.frm文件存储表定义

.MYD文件存储数据（MYData）

.MYI文件存储索引文件（MYIndex）

3、MyISAM使用的生成场景

（1）公司业务不需要事务支持

（2）一般单方读取数据比较多的业务，或单方面写入数据比较多的业务，如：www.blog，图片信息数据库，用户数据库，商品库等业务，MyISAM存储引擎数据读写都比较频繁的场景不适合。


（3）对数据业务一致性要求不是非常高的业务


（4）使用读写并发访问相对较低的业务


（5）数据修改相对较少的业务


（6）服务器硬件资源相对比较差


7.3.InnoDB介绍

1、InnoDB的特点

（1）支持事务：支持 4个事务隔离级别

（2）行级锁定，但是全表扫描仍然会是表级锁定

（3）读写阻塞与事务隔离级别相关

（4）具有非常高效的缓存特性：能缓存索引，也能缓存数据

（5）表与主键以簇的方式存储

（6）支持分区、表空间，类似 oracle数据库

（7）支持外键约束，5.5以前不支持全文索引，5.5版本以后支持全文索引

（8）对硬件资源要求比较高

2、InnoDB使用的生成场景

（1）业务需要事务的支持

（2）行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引来完成

（3）业务数据更新较为频繁的场景，如：论坛，微博

（4）业务数据一致性要求较高，如：银行业务

（5）硬件设备内存较大，利用 InnoDB较好的缓存能力来提高内存利用率，减少磁盘 IO的压力。

7.4.InnoDB索引的结构


b833d8748aba416596b22b38b044367c.jpg

InnoDB以表空间Tablespace（idb文件）结构进行组织，每个Tablespace包含多个Segment段，每个段（分为2种段：叶子节点Segment和非叶子节点Segment），一个Segment段包含多个Extent（区），一个Extent占用1M空间包含64个Page（每个Page16k），InnoDB B+Tree一个逻辑节点就分配一个物理Page，一个节点一次IO操作，一个Page里含有多个有序数据Row行数据，Row行数据中包含多个Filed属性数据等信息。

7.5.企业选择存储引擎

1、需要考虑每个存储引擎提供了哪些不同的核心功能及应用场景

2、支持的字段和数据类型

  • 所有引擎都支持通用的数据类型，但不是所有引擎都支持其他的字段类型，如二进制对象。
  • 3、锁定类型：不同的存储引擎支持不同级别的锁定
  • 表锁定：MyISAM、MEMORY
  • 行锁定：InnoDB
  • 页锁定：BDB
  • 4、索引的支持
  • 建立索引在搜索和恢复数据库中的数据的时候能显著提高性能
  • 不同的存储引擎提供不同的制作索引的技术
  • 有些存储引擎根本不支持索引
  • 5、事务处理的支持
  • 事务处理功能通过提供在想表中更新和插入信息期间的可靠性
  • 可根据企业业务是否要支持事务选择存储引擎

7.5.配置适合的存储引擎

1、查看数据库可配置的存储引擎

show engines \G;

fc295c46fff84db7adb20878b5d4c77a.jpg

2、查看表正在使用的存储引擎

（1）查看正在MySQL的默认引擎

语法：SHOW TABLE STATUS FROM库名    WHERE name = ‘表名’;
show table status from mysql where name='user' \G;

52f2182a702e46ce9133f5964982f81b.jpg

（2）配置存储引擎为所选择的类型

语法：ALTER TABLE 表名 ENGINE=引擎
alter table mysql.user engine=innodb;

cdd2c588b1b24f59acf4dd5abe63f51b.jpg



a0acb7a68a374e189797596fea390609.jpg

（3）修改my.cnf的default-storage-engine为引擎

[mysqld]
default-storage-engine = InnoDB
重启mysqld：/usr/local/mysql/support-files/mysql.server start

8.MySQL事务

8.1.事务简介

1、事物的概念

  • 事务是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起想系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么不都执行。
  • 事务是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元。
  • 事务适用于多用户同时操作的数据库系统的场景，如银行、保险公司及证券交易系统等等。通过事务的完整性以保证数据的一致性。

2、事务的 ACID特点

  • 原子性
  • 事务是一个完整的操作，事务的各元素是不可分的（原子的），事务的所有元素必须作为一个整体提交或回滚。如果事务中的任何元素失败，则整个事务将失败。
  • 一致性
  • 当事务完成时，数据必须处于一致状态：在事务开始之前，数据库汇总存储的数据处于一致状态；在正在进行的事务中，数据可能处于不一致的状态；当事务完成时，数据必须再次回到已知的一致状态。
  • 隔离性
  • 对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应该以任何方式依赖于或影响其他事务。修改数据的事务可以在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据。
  • 持久性
  • 事务的持久性指不管系统是否发生了故障，事务处理的结果都是永久的。一旦事务被提交，事务的效果会被永久地保留在数据库中。
  • 3、事务的操作
  • 默认情况下 MySQL的事务是自动提交的，当 sql语句提交时事务便自动提交。
  • 手动对事务进行控制的方法：
  • 事务处理命令控制
  • 使用 set设置事务处理方式
  • begin开始一个事务
    commit提交一个事务
    rollback回滚一个事务（撤销）
  • 使用 set命令进行控制
    set autocommit=0禁止自动提交
    set autocommit=1开启自动提交

8.2.事务隔离级别

  • read uncommitted（读未提交）：即便是事务没有commit，但是其他连接仍能读取到未提交的数据，这是所有隔离级别中最低的一种。
  • read committed（读已提交）：当前会话只能读到其他事务提交的数据，未提交的数据读不到。
  • repeatable read（可重复读）：MySQL默认的隔离级别，当前会话可以重复读，就是每次读取的结果集都相同，而不管其他事务有没有提交。
  • serializable（串行化）：其他绘画对该表的写操作将被挂起。可以看到，这是隔离界别中最严的，也是性能最差的。
  • 设置事务隔离级别代码
//设置read uncommitted级别：
set session transaction isolation level read uncommitted;
//设置read committed级别：
set session transaction isolation level read committed;
//设置repeatable read级别：
set session transaction isolation level repeatable read;
//设置serializable级别：
set session transaction isolation level serializable;
  • ⼀个数据库事务通常包含了⼀个序列的对数据库的读/写操作。它的存在包含有以下两个⽬的：
1、为数据库操作序列提供了⼀个从失败中恢复到正常状态的⽅法，同时提供了数据库即使在异常状态下仍能保持⼀致性的⽅法。
2、当多个应⽤程序在并发访问数据库时，可以在这些应⽤程序之间提供⼀个隔离⽅法，以防⽌彼此的操作互相⼲扰。
  • 查看当前事务隔离级别
SELECT @@tx_isolation;

8.3.脏读幻读不可重复读

1、脏读：所谓脏读是指一个事务中访问到了另外一个事务未提交的数据。

914759b050f24a15bd5f79ebe13dfdfb.jpg

脏读就是指：当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另一个事务也访问这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是脏数据，依据脏数据所做的操作可能是不正确的。

2、不可重读读：在同一事务中，两次读取同一数据，得到的内容不同

7303889743ae4443b035e04f9f145017.jpg

3、幻读：同一事务中，用相同的操作读取两次，得到的记录数不相同



6fc7978b00c643c4995cf74526ba97ee.jpg

  • 幻读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。
文章标签：
云数据库 RDS MySQL 版
日志服务
容器
SQL
关系型数据库
索引
数据安全/隐私保护
文件存储
MySQL
数据库
缓存
存储
关键词：
云数据库 RDS MySQL 版持久化
云数据库 RDS MySQL 版实战
云数据库 RDS MySQL 版服务端
服务端持久化云数据库 RDS MySQL 版实战
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境，其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
互联网小阿祥
目录
相关文章
请回答1024
|
19天前
|
SQL 存储 关系型数据库
不允许你不知道的 MySQL 优化实战（三）
不允许你不知道的 MySQL 优化实战（三）
请回答1024
26 1
LightOf
|
23天前
|
关系型数据库 MySQL
【MySQL实战笔记】07 | 行锁功过：怎么减少行锁对性能的影响？-01
【4月更文挑战第18天】MySQL的InnoDB引擎支持行锁，而MyISAM只支持表锁。行锁在事务开始时添加，事务结束时释放，遵循两阶段锁协议。为减少锁冲突影响并发，应将可能导致最大冲突的锁操作放在事务最后。例如，在电影票交易中，应将更新影院账户余额的操作安排在事务末尾，以缩短锁住关键行的时间，提高系统并发性能。
LightOf
15 4
LightOf
|
24天前
|
关系型数据库 MySQL 数据库
【MySQL实战笔记】 06 | 全局锁和表锁 ：给表加个字段怎么有这么多阻碍？-01
【4月更文挑战第17天】MySQL的锁分为全局锁、表级锁和行锁。全局锁用于全库备份，可能导致业务暂停或主从延迟。不加锁备份会导致逻辑不一致。推荐使用`FTWRL`而非`readonly=true`因后者可能影响其他逻辑且异常处理不同。表级锁如`lock tables`限制读写并限定操作对象，常用于并发控制。元数据锁（MDL）在访问表时自动加锁，确保读写正确性。
LightOf
70 31
请回答1024
|
19天前
|
SQL 关系型数据库 MySQL
不允许你不知道的 MySQL 优化实战（二）
不允许你不知道的 MySQL 优化实战（二）
请回答1024
26 2
菜菜的后端私房菜
|
5天前
|
存储 缓存 算法
MySQL持久化不为人知的一面⭐️卡顿现象的根源与对策
MySQL持久化不为人知的一面⭐️卡顿现象的根源与对策
菜菜的后端私房菜
13 1
1941623231718325
|
14天前
|
缓存 关系型数据库 MySQL
MySQL数据库性能优化实战
【4月更文挑战第30天】本文探讨了MySQL性能优化实战技巧，包括硬件与配置优化（如使用SSD、增加内存和调整配置参数）、索引优化（创建合适索引、使用复合索引及定期维护）、查询优化（避免全表扫描、减少JOIN和使用LIMIT）、分区与分片（表分区和数据库分片），以及使用缓存、定期清理数据库和监控诊断。通过这些方法，可以提升数据库性能和响应速度。
1941623231718325
40 0
龙大吉
|
16天前
|
存储 关系型数据库 MySQL
MySQL数据库实战：从入门到精通
本文介绍了MySQL的使用和优化，适合Web开发者阅读。首先，确保安装并配置好MySQL，熟悉SQL基础。接着，通过命令行客户端连接数据库，执行创建、查询、添加、修改和删除数据等操作。学习数据类型并创建表存储数据。最后，探讨了数据库优化，包括查询优化和索引使用，以提升性能。
龙大吉
26 2
yma16
|
18天前
|
安全 关系型数据库 MySQL
node实战——后端koa结合jwt连接mysql实现权限登录（node后端就业储备知识）
node实战——后端koa结合jwt连接mysql实现权限登录（node后端就业储备知识）
yma16
23 3
请回答1024
|
19天前
|
SQL 关系型数据库 MySQL
不允许你不知道的 MySQL 优化实战（一）
不允许你不知道的 MySQL 优化实战（一）
请回答1024
26 2
LightOf
|
22天前
|
关系型数据库 MySQL 中间件
【MySQL实战笔记】07 | 行锁功过：怎么减少行锁对性能的影响？-02 死锁和死锁检测
【4月更文挑战第19天】在高并发环境下，死锁发生在多个线程间循环等待资源时，导致无限期等待。MySQL中，死锁可通过`innodb_lock_wait_timeout`参数设置超时或`innodb_deadlock_detect`开启死锁检测来解决。默认的50s超时可能不适用于在线服务，而频繁检测会消耗大量CPU。应对热点行更新引发的性能问题，可以暂时关闭死锁检测（风险是产生大量超时），控制并发度，或通过分散记录减少锁冲突，例如将数据分拆到多行以降低死锁概率。
LightOf
24 1

热门文章

最新文章

  • 1
    PostgreSQL 聚合函数讲解 - 3 总体|样本 方差, 标准方差
  • 2
    一文读懂AnalyticDB MySQL过滤条件智能下推原理
  • 3
    mysql主从监控脚本：
  • 4
    mysql主从配置一些注意
  • 5
    mysql (双主，互主)
  • 6
    MySql创建用户及授权
  • 7
    mysql数据库引擎问题汇总
  • 8
    Mysql使用正则表达式-regexp
  • 9
    MySQL数据库约束条件和自增长序列
  • 10
    MySQL 5.6数据导入报 GTID 相关错误
  • 1
    连接和管理RDS
    30
  • 2
    购买和初始化阿里云RDS
    32
  • 3
    rds教程
    30
  • 4
    TiDB与MySQL、PostgreSQL等数据库的比较分析
    200
  • 5
    OBCP实践 - 迁移 MySQL 数据到 OceanBase 集群
    51
  • 6
    Flink CDC产品常见问题之mysql整库同步到starrock时任务挂掉如何解决
    54
  • 7
    TIDB和MySQL的区别
    216
  • 8
    Flink CDC产品常见问题之mysql整库同步到starrock时任务挂掉如何解决
    78
  • 9
    [MySQL]事务原理之redo log,undo log
    127
  • 10
    [MySQL]SQL优化之索引的使用规则
    42

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布

    • 相关实验场景

    更多
  • 测试场景（RDS无优惠）
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 使用Python完成RDS数据开发及分析
  • Excel文件转存到RDS数据库
  • 使用CloudLens采集RDS日志并进行审计分析

    • 推荐镜像

    更多
  • mysql
  • postgresql
  • mariadb
    • 下一篇
    2024年阿里云免费云服务器及学生云服务器申请教程参考