索引
事务
存储引擎
概念:存储引擎,就是一种数据库存储数据的一种机制,索引的技巧,锁定水平。
存储的方式和存储的格式。
存储引擎也属于mysql当中的组件,实际上操作的,执行的就是数据的读写I/O。
mysql的存储引擎的分类:
mysql5.5之后默认开始使用innodb,事务型速记存储引擎。支持ACID,支持行锁定。
InnoDB:事务型速记的首选引擎,支持ACID事务,支持行级锁定,MYSQL5.5成为默认数据库引擎。
MYsam:5.5之前默认的存储引擎,插入的速度和查询速度很快,但是不支持事务。
Memory:内存型存储引擎,数据在写时都保存在内存当中,一旦重启所有数据全部消失。
CSV:逗号分割数据的存储引擎,数据文件.csv文件保存的,execl,保存的文件就是一个普通的文本文件。不支持索引
InnoDB存储引擎:
1、读写阻塞(缩表)和事务的隔离级别
2、能够高效的缓存数据支持多种类的索引。
3、表的索引的类型默认是BTREE
4、支持外键,支持全文索引。
5、对硬件的资源要求比较高。
6、行级锁定,会把行锁住,禁止操作。
模糊查询:
like进行查询时,会进行全表扫描,在扫描的过程中会锁定整个表。
没有创建索引的列,进行查询时,也会锁定整个表。
使用的是索引列,锁定条件的行,行锁定。
innoDB行锁和索引的关系:
行锁是通过索引来实现的。
如果没有索引,InnoDB会使用默认的隐藏索引来对记录进行加锁。
加了索引就是锁行,不加索引就是锁表。
mysql默认就是自动提交写入。
oracl是提交才能写入
当指定条件不是索引的时候,会锁住全表。
死锁:事务相互等待对方的资源,最后形成一个环路造成的
发生了死锁,数据库会自动选择一个事务作为受害者,回滚该事务以解除死锁。
mysql会终止其中一个事务,但是不会回滚。
for update 排他锁,当一个事务的操作未完成时,其他事务可以读取但是不能写入。写锁
如何避免死锁的情况出现:
1、以固定的顺序访问表和行
2、大事务尽量拆分成小的事务
3、为表添加合理的索引。
mysql的备份和恢复和日志管理(配置文件当中的设置)
备份的目的是什么:备灾
在生成环境中,数据的安全性非常重要。造成数据丢失的原因:
1、程序出错
2、人为的问题
3、磁盘故障
备份的分类: 物理备份:对磁盘或者对文件直接进行备份。
冷备份:脱机备份,先把指定的程序关闭,然后对资料进行备份
热备份:联机备份,不用关闭程序就可以对资料进行备份
逻辑备份(热备份):根据数据库文件当中保存的sql语句,表结构等等,以特定的格式和命令对文件的内容进行还原。
只能对表,库没了没有办法恢复。
主从复制可以恢复库。
物理备份(全量备份):把数据库的内容整个一次性的做备份。
[root@mysql1 ~]# tar -Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/
mysql自带的备份命令,可以备份库,也可以备份库里面的表
mysqldump
[root@mysql1 ~]# mysqldump -u root -p --databases xy102 > /opt/xy102.sql #将整个库备份出来 [root@mysql1 ~]# mysql -u root -p < /opt/xy102.sql #将备份的库导入数据库中
[root@mysql1 ~]# mysqldump -u root -p --databases xy102 xy103 > /opt/xy_all.sql #将多个库同时进行备份
增量备份:
开启二进制日志的功能:
binlog 逻辑备份,会生成一个文件,这个里面包含了sql语句,要使用特定的方式和语句才能恢复。
binlog_format=MIXED 记录二进制日志文件的格式 STATEMENT 基于sql语句,只是记录用户操作的sql语句,高并发的情况下,记录操作的sql语句的顺序可能会出错。导出数据时,就会有丢失或误差。效率高 ROW 基于行,记录每一行的数据,准确,高并发也不会出错,但是恢复的效率低。 MIXED 混合模式,正常情况下使用statement,高并发使用row,智能判断
查看日志文件
[root@mysql1 data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000005
将日志文件中恢复数据库文件数据
[root@mysql1 ~]# mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000001 | mysql -u root -p
刷新日志文件
[root@mysql1 ~]# mysqladmin -u root -p flus-logs
以什么节点开始
mysqlbinlog --no-defaults --start-position='8224' mysql-bin-000001 | mysql -u root -p
以什么时间开始到什么时间结束
[root@mysql1 ~]# mysqlbinlog --no-defaults --start-datetime='2024-7-19 14:12:05' --stop-datetime='2024-7-19 14:13:07' mysql-bin-000001 | mysql -u root -p
general_log=ON #开启通用查询日志 general_log_file=usr/local/mysql/data/mysql_general.log #查询日志的保存位置 log-error=/usr/local/mysql/data/mysql_error.log #错误日志的保存位置,错误日志默认是开启的 slow_query_log=ON #开启慢查询日志 slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log #设定慢查询日志的位置 long_query_time=5 #默认的慢查询时间是10秒。超过5秒的记录都会保存
作业
现有需求,本地数据库迁移到阿里云上。
库名 xy102
表 test01
test02
test01 test02
3条数据。
word文档的形式,包含详细的步骤
每一步都要有截图
最终展示迁移的结果,以及途中遇到的问题和解决的办法
操作:
1、创建一个库
CREATE DATABASE xy102;
2、创建两个表并创建数据
CREATE TABLE test01 ( id int(5), name VARCHAR(10), age int(3), sex VARCHAR(2) ); insert into test01 values(1,'小明',18,'男'); insert into test01 values(2,'小红',19,'女'); insert into test01 values(3,'小刚',19,'男'); insert into test01 values(4,'小绿',17,'女'); insert into test01 values(5,'小黑',20,'男'); create table if not exists test02 ( id int(4) not null auto_increment, name varchar(10) not null, sex char(10) not null, hobby varchar(50), primary key (id)); insert into test02 values(1,'user1','male','running'); insert into test02 values(2,'user2','female','singing');
3、将创建的库进行热备份
[root@mysql1 opt]# mysqldump -u root -p --databases xy102 > /opt/xy102.sql Enter password: [root@mysql1 opt]# ls Discuz_X3.5_SC_UTF8.zip mysql-8.0.30-el7-x86_64.tar.gz nginx-1.22.0 nginx-1.22.0.tar.gz php-8.1.27.tar.gz xy102.sql
4、将mysql的压缩包和xy102.sql的文件传到阿里云上
[root@mysql1 opt]# scp mysql-8.0.30-el7-x86_64.tar.gz root@121.43.108.58:/opt/ root@121.43.108.58's password: mysql-8.0.30-el7-x86_64.tar.gz 100% 470MB 5.1MB/s 01:32 [root@mysql1 opt]# scp xy102.sql root@121.43.108.58:/opt/ root@121.43.108.58's password: xy102.sql 100% 2919 105.0KB/s 00:00
5、编译安装数据库
[root@aliyun opt]# tar -xf mysql-8.0.30-el7-x86_64.tar.gz [root@aliyun opt]# mv mysql-8.0.30-el7-x86_64 mysql [root@aliyun opt]# mv mysql /usr/local/ [root@aliyun opt]# useradd -s /sbin/nologin mysql useradd: user 'mysql' already exists [root@aliyun opt]# chown -R mysql:mysql /usr/local/mysql/ [root@aliyun opt]# chown mysql:mysql /etc/my.cnf [root@aliyun opt]# vim /etc/my.cnf [root@aliyun opt]# echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile [root@aliyun opt]# source /etc/profile [root@aliyun opt]# cd /usr/local/mysql/bin/ [root@aliyun bin]# ./mysqld \ > --initialize-insecure \ > --user=mysql \ > --basedir=/usr/local/mysql \ > --datadir=/usr/local/mysql/data [root@aliyun bin]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld [root@aliyun bin]# chmod +x /etc/init.d/mysqld [root@aliyun bin]# systemctl daemon-reload [root@aliyun bin]# systemctl restart mysqld [root@aliyun bin]# mysqladmin -u root -p password "123456" Enter password: mysqladmin: [Warning] Using a password on the command line interface can be insecure. Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety. [root@aliyun bin]# mysql -u root -p123456 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.30 MySQL Community Server - GPL Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE USER 'root'@'%' IDENTIFIED BY '123456'; Query OK, 0 rows affected (0.01 sec) mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'; Query OK, 0 rows affected (0.01 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456'; Query OK, 0 rows affected (0.01 sec)
6、将xy102的库导入阿里云的数据库中
[root@aliyun bin]# mysql -u root -p < /opt/xy102.sql