索引和备份

索引

事务

存储引擎

概念：存储引擎，就是一种数据库存储数据的一种机制，索引的技巧，锁定水平。

存储的方式和存储的格式。

存储引擎也属于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