第六周作业

一、简述DDL,DML,DCL,DQL，并且说明mysql各个关键字查询时候的先后顺序

在关系型数据库中，常用的SQL操作分为四种类型：DDL（数据定义语言）、DML（数据操作语言）、DCL（数据控制语言）和DQL（数据查询语言）。

1. DDL（数据定义语言）：用于定义数据库对象的结构，例如创建表、修改表结构、删除表等。常见的DDL关键字包括：CREATE、ALTER、DROP、TRUNCATE等。

2. DML（数据操作语言）：用于对数据库中的数据进行操作，例如插入、更新、删除数据。常见的DML关键字包括：SELECT、INSERT、UPDATE、DELETE等。

3. DCL（数据控制语言）：用于授权、权限控制和事务管理等操作。常见的DCL关键字包括：GRANT、REVOKE、COMMIT、ROLLBACK等。

4. DQL（数据查询语言）：用于从数据库中查询数据。常见的DQL关键字是SELECT，它用于从一个或多个表中检索数据。

对于MySQL中关键字查询时的先后顺序，一般遵循以下顺序：

1. DDL：首先定义数据库对象的结构，如创建表、修改表结构等。

2. DML：在表结构定义完毕后，对表中的数据进行操作，如插入、更新、删除数据。

3. DQL：在DML操作后，可以进行数据查询，如使用SELECT语句从表中检索数据。

4. DCL：最后，对数据库进行授权、权限控制和事务管理等操作，如授予用户权限，提交或回滚事务等。

    

二、自行设计10个sql查询语句，需要用到关键字[GROUP BY/HAVING/ORDER BY/LIMIT]，至少同时用到两个

使用GROUP BY/HAVING查询

 

使用ORDER BY/LIMIT查询

 

使用GROUP BY/LIMIT查询

 

使用GROUP BY/HAVING/LIMIT查询

 

 

三、xtrabackup备份和还原数据库练习

进入官网，下载包

 

1、xtrabackup实现完全备份及还原

原主机操作

yum安装之前下载的包

yum -y install percona-xtrabackup-80-8.0.33-28.1.el8.x86_64.rpm

创建backup目录，备份，备份到远程主机

mkdir /backup

xtrabackup -uroot -p123456 --backup --target-dir=/backup/base

scp -r /backup/ 10.0.0.135:/

远程备份主机操作

注意：恢复主机mysql服务停止，并且数据目录为空

yum -y install percona-xtrabackup-80-8.0.33-28.1.el8.x86_64.rpm

确保数据一致，提交完成事务，回滚未完成事务

xtrabackup --prepare --target-dir=/backup/base/

 

复制到数据库目录

xtrabackup --copy-back --target-dir=/backup/base/

 

还原属性

chown -R mysql.mysql /var/lib/mysql

启动服务

systemctl start mysqld

进入数据库查看，还原正常

 

 

2、xtrabackup完全，增量备份及还原

注意：恢复主机mysql服务停止，并且数据目录为空

原主机操作

yum安装之前下载的包

yum -y install percona-xtrabackup-80-8.0.33-28.1.el8.x86_64.rpm

创建backup目录，备份

mkdir /backup

xtrabackup -uroot -p123456 --backup --target-dir=/backup/base

第一次数据修改

 

第一次增量备份

xtrabackup -uroot -p123456 --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base

第二次数据修改

 

第二次增量备份

xtrabackup -uroot -p123456 --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1

增量数据文件大小

 

 

远程备份主机操作

yum -y install percona-xtrabackup-80-8.0.33-28.1.el8.x86_64.rpm

完全备份还原，此选项--apply-log-only 组织回滚未完成的事务

xtrabackup --prepare --apply-log-only --target-dir=/backup/base/

合并第一次增量备份到完全备份--apply-log-only

xtrabackup --prepare --apply-log-only --target-dir=/backup/base/ --incremental-dir=/backup/inc1

合并第二次增量备份到完全备份，最后一次还原不需要加

xtrabackup --prepare --target-dir=/backup/base/ --incremental-dir=/backup/inc2

复制到数据库目录

xtrabackup --copy-back --target-dir=/backup/base/

还原属性

 

启动服务

systemctl start mysqld

进入数据库查看状态，以及新加数据

 

 

四、实现mysql主从复制，主主复制和半同步复制

1、mysql主从复制

主节点操作

进入vim /etc/my.cnf编辑

 

创建账号授权

create user repluser@'10.0.0.%' identified by '123456';

grant replication slave on . to repluser@'10.0.0.%';

备份mysqldump -uroot -p123456 -A -F --single-transaction --source-data > /backup/full-date +%F.sql

把备份的包备份到slave节点

 

slave节点

从主节点备份的数据库

 

进入vim /etc/my.cnf编辑修改server-id

 

进入vim /backup/full-2023-07-24.sql 编辑

 

进入salve节点mysql，先关闭二进制，还原数据库

 

还原后数据库不变，查看备节点状态

 

 

 

2、mysql主主复制

准备两台主机10.0.0.135/136

10.0.0.135主机

编辑vim /etc/my.cnf

 

systemctl restart mysqld重启数据库服务

mysql中建立账号并且授权

 

数据库备份，并备份到备份主机

 

 

10.0.0.136主机

编辑vim /etc/my.cnf

 

安装半同步插件

 

编辑vim /data/all.sql

 

数据导入

 

 

启动从节点，框选显示yes

 

在10.0.0.135主机上配置

 

 

3、mysql半同步复制

准备三台主机10.0.0.135（主）10.0.0.136/137（备）

yum -y install mysql-server安装好数据库

主设备配置

安装半同步插件

 

编辑vim /etc/my.cnf

 

systemctl restart mysqld重启数据库服务

mysql中建立账号并且授权

 

数据库备份，并备份到136/137两台主机

 

 

10.0.0.136主机的从设备配置

编辑vim /etc/my.cnf

 

安装半同步插件

 

编辑vim /data/all.sql

 

 

启动从节点，框选显示yes

 

数据导入

 

 

10.0.0.137主机的从设备配置（从节点配置一样）

编辑vim /etc/my.cnf

 

安装半同步插件

 

编辑vim /data/all.sql

 

启动从节点，框选显示yes

 

数据导入

 

查看半节点同步是否开启，on表示开启

 

查看主节点同步是否开启

 

 

五、用mycat实现mysql的读写分离

此处主从按照上面配置，此处不再展示，主从配置完成后，方能继续以下步骤

使用四台主机10.0.0.135/136配置主从10.0.0.137配置mycat10.0.0.130设置为客户端

下载安装JDK yum -y install java

 

 

下载解压安装mycat

wget http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server- 1.6.7.4-release-20200105164103-linux.tar.gz 

 

 

 

启动mycat，查看端口，查看启动是否报错

 

客户端测试连接

 

修改mycatserver.xml、schema.xml配置文件以及住从账号创建

创建主从账号以及授权（只需要在主上面建立，因为配置主从辅助关系，主有备也有）

 

vim /apps/mycat/conf/schema.xml

 

vim /apps/mycat/conf/server.xml

 

客户端上查看（端口号已修改所以不用加），现在能看到数据库表的信息

 

验证mycat读写分离

从客户端中写入数据

 

观察到主节点有写入记录

从节点未有写记录显示

 

六、实现openvpn部署，并且测试通过，输出博客或者自己的文档存档。

七、mysql如何实现崩溃后恢复？

MySQL数据库在崩溃后可以通过以下几种方式来实现恢复：

1. 启用二进制日志（Binary Log）：MySQL提供了二进制日志功能，它记录了所有数据库操作语句的日志。在崩溃后，可以使用二进制日志进行恢复。需要确保在MySQL配置文件中启用了二进制日志，并定期备份二进制日志文件。

2. 使用事务日志（InnoDB存储引擎）：如果使用的是InnoDB存储引擎，它提供了事务日志（也称为重做日志）功能。事务日志记录了正在进行中的事务操作，包括对表的修改。在崩溃后，MySQL可以使用事务日志进行恢复。

3. 备份与恢复：定期备份MySQL数据库是非常重要的。使用数据库备份工具，如mysqldump命令行工具或第三方xtrabackup备份工具，可以将数据库备份到另一个存储位置。在崩溃后，可以使用备份文件来还原数据库。

4. 使用故障转移与复制功能：MySQL支持主从复制和主主复制功能。通过设置主从复制或主主复制，可以将数据复制到多个服务器上。当主服务器崩溃时，可以使用其中一个从服务器继续提供服务。

 

八、myisam和innodb各自在什么场景使用？

MyISAM和InnoDB是MySQL数据库中两种常见的存储引擎。

MyISAM适用于以下场景：

1. 对于读密集型应用：MyISAM在处理大量的SELECT操作时性能较好，因为它使用了表级锁定而不是行级锁定，这意味着在读取数据时不会出现锁冲突。

2. 需要全文搜索功能：MyISAM支持全文索引，使得在文本数据上进行高效的全文搜索成为可能。

3. 插入和查询的比例较低：由于MyISAM会在执行写操作时锁定整个表，所以在插入和查询的比例较低的应用中，效果更好。

InnoDB适用于以下场景：

1. 对于写密集型应用：InnoDB在处理大量的INSERT和UPDATE操作时性能较好，因为它使用了行级别的锁定，可以避免锁冲突，提高并发性能。

2. 需要事务支持：InnoDB是MySQL的默认事务存储引擎，支持ACID（原子性、一致性、隔离性和持久性）事务，可以确保数据的完整性和一致性。

3. 强调数据的安全性：InnoDB支持外键约束和崩溃恢复机制，可以保证数据的安全性和稳定性。