2023-08-12 第六周-CSDN博客

本文链接：https://blog.csdn.net/weixin_48636572/article/details/133898634

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

DDL 数据定义语言，用来定义数据库对象（数据库、表、字段）
DML 数据操作语言，用来对数据库的内容进行增、删、改、查
DQL 数据查询语言，用来查询数据库的内容
DCL 数据控制语言，用来创建数据库用户、控制数据库的控制权限

FROM -> WHERE -> GROUP BY -> SELECT -> ORDER BY -> LIMIT
WHERE->聚合函数->HAVING

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

①-- 按照城市进行分组，获取学生数量大于等于4的城市
select city,count(*) city_count from student group by city having city_count >= 4;
②--根据性别分组，按照男女生人数进行降序排序
select gender,count(*) num from student group by gender order by num DESC;
③--根据城市进行分组，然后分页显示第一页数据，展示十条
select city,count(*) city_count from student group by city limit 0,10;
④--每个科目成绩最高，并且成绩要不低于80的学生信息
select name,max(score) max_score from student group by course having max_score >= 80;
⑤--查看平均分大于60的科目
select course avg(score) avg_score from student group by course having avg_score > 60;
⑥--对男生女生的平均成绩进行排序
select gender, avg(score) avg_score from student group by gender order by avg_score ;
⑦--查看女生的总人数
select gender ,count(*) from students group by gender having gender = '女'
⑧--查看每个班级的人数，显示第二页
select class ,count(*) from student group by class limit 10,10;
⑨--对员工的工资进行排序，显示第一页
select * from employee order by salary limit;
⑩--对班级人数进行降序排序
select class ,count(*) num from  student group by class order by num desc;

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

#1.先安装mysql和xtrabackup
#对 MySQL中写入数据
[10:16:53 root@rocky8 ~]#yum -y install mysql-server.x86_64 
[10:19:27 root@rocky8 ~]#mysql < hellodb_innodb.sql 
[10:19:41 root@rocky8 ~]#mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.26 Source distribution

Copyright (c) 2000, 2021, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use hellodb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.01 sec)

#在官网下载xtrabackup的包后安装
[10:13:56 root@rocky8 ~]#yum -y install percona-xtrabackup-80-8.0.23-16.1.el8.x86_64_.rpm

#2.在原主机上做完全备份
[10:43:18 root@rocky8 ~]#mkdir /backup
[10:43:51 root@rocky8 ~]#xtrabackup -uroot  --backup --target-dir=/backup/base
[10:44:13 root@rocky8 ~]#ll /backup/base/
total 72756
-rw-r----- 1 root root      475 Aug 13 10:44 backup-my.cnf
-rw-r----- 1 root root      156 Aug 13 10:44 binlog.000002
-rw-r----- 1 root root       16 Aug 13 10:44 binlog.index
drwxr-x--- 2 root root      132 Aug 13 10:44 hellodb
-rw-r----- 1 root root     6083 Aug 13 10:44 ib_buffer_pool
-rw-r----- 1 root root 12582912 Aug 13 10:44 ibdata1
drwxr-x--- 2 root root      143 Aug 13 10:44 mysql
-rw-r----- 1 root root 28311552 Aug 13 10:44 mysql.ibd
drwxr-x--- 2 root root     8192 Aug 13 10:44 performance_schema
drwxr-x--- 2 root root       28 Aug 13 10:44 sys
-rw-r----- 1 root root 16777216 Aug 13 10:44 undo_001
-rw-r----- 1 root root 16777216 Aug 13 10:44 undo_002
-rw-r----- 1 root root       18 Aug 13 10:44 xtrabackup_binlog_info
-rw-r----- 1 root root      102 Aug 13 10:44 xtrabackup_checkpoints
-rw-r----- 1 root root      458 Aug 13 10:44 xtrabackup_info
-rw-r----- 1 root root     2560 Aug 13 10:44 xtrabackup_logfile
-rw-r----- 1 root root       39 Aug 13 10:44 xtrabackup_tablespaces

#把备份的文件夹传到目标主机
[10:47:16 root@rocky8 ~]#scp -r /backup/ 10.0.0.18:/
The authenticity of host '10.0.0.18 (10.0.0.18)' can't be established.
ECDSA key fingerprint is SHA256:aOhZM49YQIH6Caj1HkdK1ZlVM2maWEkPgm+j7AsTSy0.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
[10:34:35 root@rocky8 ~]#ll /backup/
total 4
drwxr-x--- 6 root root 4096 Aug 13 10:47 base

#3.在目标主机上进行还原
[10:52:14 root@rocky8 ~]#systemctl stop mysqld  #停止目标主机的mysql
#预准备
[11:11:33 root@rocky8 ~]#xtrabackup --prepare --target-dir=/backup/base
#清空数据库目录，再重新复制到数据库目录
[11:13:01 root@rocky8 ~]#rm -rf /var/lib/mysql
[11:13:39 root@rocky8 ~]#xtrabackup --copy-back --target-dir=/backup/base
#给目录设置权限，重启服务
[11:13:42 root@rocky8 ~]#chown -R mysql:mysql /var/lib/mysql
[11:14:02 root@rocky8 ~]#service mysqld start 
Redirecting to /bin/systemctl start mysqld.service
[11:14:17 root@rocky8 ~]#mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26 Source distribution

Copyright (c) 2000, 2021, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use hellodb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.00 sec)

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

主从复制

#主节点开启二进制和设置唯一的ID号
[11:26:20 root@master ~]#vim /etc/my.cnf
[mysqld]
server-id=8
log-bin
[11:29:27 root@master ~]#systemctl restart mysqld.service 
#记住二进制文件的位置
mysql> show master logs;
+-------------------+-----------+-----------+
| Log_name          | File_size | Encrypted |
+-------------------+-----------+-----------+
| master-bin.000001 |       156 | No        |
+-------------------+-----------+-----------+
1 row in set (0.00 sec)
#创建用户和授权
mysql> create user 'repluser'@'10.0.0.%';
Query OK, 0 rows affected (0.29 sec)
mysql> grant replication slave on *.* to 'repluser'@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)

#从节点
[11:26:32 root@slave ~]#vim /etc/my.cnf
 14 [mysqld]
 15 server-id=18
[11:35:42 root@slave ~]#systemctl restart mysqld.service 
#开启从节点服务
[11:42:33 root@slave ~]#mysql
mysql> CHANGE MASTER TO
    -> MASTER_HOST='10.0.0.8',
    -> MASTER_USER='repluser',
    -> MASTER_PASSWORD='',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='master-bin.000001',
    -> MASTER_LOG_POS=156;
Query OK, 0 rows affected, 9 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 10.0.0.8
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 620
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 789
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

主主复制

#主节点1
[12:04:44 root@master1 ~]#vim /etc/my.cnf
[mysqld]
server-id=8
log-bin
auto_increment_offset=1             #自动增长开始点
auto_increment_increment=2          #自动增长幅度

[12:06:21 root@master1 ~]#systemctl restart mysqld.service 
[12:07:52 root@master1 ~]#mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26 Source distribution

Copyright (c) 2000, 2021, 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> show master logs;
+--------------------+-----------+-----------+
| Log_name           | File_size | Encrypted |
+--------------------+-----------+-----------+
| master1-bin.000001 |       156 | No        |
+--------------------+-----------+-----------+
1 row in set (0.00 sec)
#创建用户和授权
mysql> create user 'repluser'@'10.0.0.%';
Query OK, 0 rows affected (0.29 sec)
mysql> grant replication slave on *.* to 'repluser'@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)

#主节点2
[12:04:59 root@master2 ~]#vim /etc/my.cnf
 14 [mysqld]
 15 server-id=18
 16 log-bin
 17 auto_increment_offset=2
 18 auto_increment_increment=2
[12:12:05 root@master2 ~]#systemctl restart mysqld.service 
[12:23:37 root@master2 ~]#mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26 Source distribution

Copyright (c) 2000, 2021, 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> CHANGE MASTER TO
    -> MASTER_HOST='10.0.0.8',
    -> MASTER_USER='repluser',
    -> MASTER_PASSWORD='',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='master-bin.000001',
    -> MASTER_LOG_POS=156;
Query OK, 0 rows affected, 9 warnings (0.01 sec)

mysql> 
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show master logs;
+--------------------+-----------+-----------+
| Log_name           | File_size | Encrypted |
+--------------------+-----------+-----------+
| master2-bin.000001 |       179 | No        |
| master2-bin.000002 |       156 | No        |
+--------------------+-----------+-----------+
2 rows in set (0.00 sec)
mysql> CHANGE MASTER TO
    -> MASTER_HOST='10.0.0.18',
    -> MASTER_USER='repluser',
    -> MASTER_PASSWORD='',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='master2-bin.000002',
    -> MASTER_LOG_POS=156;
Query OK, 0 rows affected, 9 warnings (0.03 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

半同步复制

#主服务器配置
[12:40:01 root@master ~]#vim /etc/my.cnf
[mysqld]
server-id=8
log-bin
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_master_timeout=3000
#从服务器配置
[12:40:34 root@slave1 ~]#vim /etc/my.cnf
[mysqld]
server-id=18
rpl_semi_sync_master_enabled=ON
[12:40:16 root@slave2 ~]#vim /etc/my.cnf
[mysqld]
server-id=28
rpl_semi_sync_master_enabled=ON

#主服务器的配置
#安装插件进行配置
[12:47:10 root@master ~]#mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26 Source distribution

Copyright (c) 2000, 2021, 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> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SHOW PLUGINS;
| rpl_semi_sync_master            | ACTIVE   | REPLICATION        | semisync_master.so | GPL     |
mysql> SET GLOBAL rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.01 sec)
mysql> SET GLOBAL rpl_semi_sync_master_timeout = 3000;
Query OK, 0 rows affected (0.00 sec)

#从节点的配置
#安装插接进行配置
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SET GLOBAL rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE '%semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set (0.01 sec)

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

#1.创建主从，如上述一样，不过多赘述
#2.安装mycat  
[13:05:10 root@maycat-server ~]#yum -y install java
[13:19:39 root@maycat-server ~]#java -version
openjdk version "1.8.0_332"
OpenJDK Runtime Environment (build 1.8.0_332-b09)
OpenJDK 64-Bit Server VM (build 25.332-b09, mixed mode)
[13:20:47 root@maycat-server ~]#wget http://dl.mycat.org.cn/1.6.7.6/20210303094759/Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz
[13:23:55 root@maycat-server ~]#mkdir /apps
[13:24:45 root@maycat-server ~]#tar xvf Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz -C /apps/
#配置变量
[13:25:29 root@maycat-server ~]#echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
[13:26:15 root@maycat-server ~]#source /etc/profile.d/mycat.sh
[13:26:25 root@maycat-server ~]#ss -ntl
State         Recv-Q         Send-Q                 Local Address:Port                  Peer Address:Port        Process        
LISTEN        0              128                          0.0.0.0:22                         0.0.0.0:*                          
LISTEN        0              128                             [::]:22                            [::]:*                          
#启动mycat
[13:27:33 root@maycat-server ~]#mycat start
Starting Mycat-server...
[13:28:48 root@maycat-server ~]#ss -ntlp
State      Recv-Q     Send-Q         Local Address:Port           Peer Address:Port     Process                                 
LISTEN     0          128                  0.0.0.0:22                  0.0.0.0:*         users:(("sshd",pid=1031,fd=4))         
LISTEN     0          1                  127.0.0.1:32000               0.0.0.0:*         users:(("java",pid=26588,fd=4))        
LISTEN     0          128                     [::]:22                     [::]:*         users:(("sshd",pid=1031,fd=6))         
LISTEN     0          50                         *:40925                     *:*         users:(("java",pid=26588,fd=72))       
LISTEN     0          50                         *:1984                      *:*         users:(("java",pid=26588,fd=73))       
LISTEN     0          128                        *:8066                      *:*         users:(("java",pid=26588,fd=97))       
LISTEN     0          50                         *:34979                     *:*         users:(("java",pid=26588,fd=74))       
LISTEN     0          128                        *:9066                      *:*         users:(("java",pid=26588,fd=93))       
[13:28:59 root@maycat-server ~]#tail /apps/mycat/logs/wrapper.log  #查看日志是否开启成功
INFO   | jvm 1    | 2023/08/13 13:27:49 | MyCAT Server startup successfully. see logs in logs/mycat.log

#修改mycat的配置文件
[13:35:28 root@maycat-server ~]#vim /apps/mycat/conf/server.xml
 <property name="serverPort">3306</property>
                <property name="handleDistributedTransactions">0</property>

       <user name="root">
                <property name="password">123456</property>
                <property name="schemas">TESTDB</property>
#               <property name="readOnly">true</property>
#                <property name="defaultSchema">TESTDB</property>
        </user>

[root@mycat ~]#cat /apps/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
 <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100"
dataNode="dn1">
 </schema>
 <dataNode name="dn1" dataHost="localhost1" database="hellodb" />
 <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
  writeType="0" dbType="mysql" dbDriver="native" switchType="1"
slaveThreshold="100">
 <heartbeat>select user()</heartbeat>
 <writeHost host="host1" url="10.0.0.18:3306" user="root"
   password="">
         <readHost host="host2" url="10.0.0.28:3306" user="root"
password="" />
 </writeHost>
   </dataHost>
</mycat:schema>
[13:51:03 root@maycat-server ~]#mycat restart

#在主节点上创建用户并授权
mysql> create user 'root'@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT ALL ON *.* TO 'root'@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)

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

#先配置实验用的网络环境

1 openvpn server：
CentOS 8.2
eth0:10.0.0.8/24 NAT模式,模拟公网IP
eth1:192.168.10.1/24 仅主机模式,私网IP

2 内网主机两台
第一台主机
eth0:192.168.10.100/24 仅主机模式,私网IP，无需网关
第二台主机
eth0:192.168.10.200/24 仅主机模式,私网IP，无需网关

3 Windows 客户端
Windows 10

#1、配置环境
[14:37:17 root@openvpn-server ~]#ip a
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000
    inet 10.0.0.28/24 brd 10.0.0.255 scope global noprefixroute eth0
3: eth1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000
    inet 192.168.10.1/24 brd 192.168.10.255 scope global noprefixroute eth1
[14:38:00 root@web1 ~]#ip a
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000
    inet 192.168.10.100/24 brd 192.168.10.255 scope global noprefixroute eth0
[14:38:00 root@wbe2 ~]#ip a
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000
    inet 192.168.10.200/24 brd 192.168.10.255 scope global noprefixroute eth0

#2、安装openvpn和证书工具
[root@openvpn-server ~]#yum -y install openvpn easy-rsa

#3、生成相关的配置文件
##openvpn配置文件
[root@openvpn-server ~]#cp /usr/share/doc/openvpn/sample/sample-config-files/server.conf /etc/openvpn/
##证书的相关文件
[root@openvpn-server ~]#cp -r /usr/share/easy-rsa/ /etc/openvpn/easy-rsa-server
##颁发证书相关变量的配置文件
[root@openvpn-server ~]#cp /usr/share/doc/easy-rsa/vars.example /etc/openvpn/easy-rsa-server/3/vars
##增加CA和openvpn服务器证书的有效期
[root@openvpn-server ~]#vim /etc/openvpn/easy-rsa-server/3/vars
set_var EASYRSA_CA_EXPIRE      36500
set_var EASYRSA_CERT_EXPIRE    3650

[root@openvpn-server ~]#tree /etc/openvpn/
/etc/openvpn/
├── client
├── easy-rsa-server
│   ├── 3 -> 3.0.8
│   ├── 3.0 -> 3.0.8
│   └── 3.0.8
│       ├── easyrsa
│       ├── openssl-easyrsa.cnf
│       ├── vars
│       └── x509-types
│           ├── ca
│           ├── client
│           ├── code-signing
│           ├── COMMON
│           ├── email
│           ├── kdc
│           ├── server
│           └── serverClient
├── server
└── server.conf

7 directories, 12 files

#4、初始化PKI和CA颁发机构环境
##查看easyrsa脚本帮助用法
[root@openvpn-server ~]#cd /etc/openvpn/easy-rsa-server/3/
[root@openvpn-server 3]#file ./easyrsa 
./easyrsa: POSIX shell script, ASCII text executable
[root@openvpn-server 3]#./easyrsa 
##初始化PKI生成PKI相关目录文件
[root@openvpn-server 3]#./easyrsa init-pki

[root@openvpn-server 3]#tree
.
├── easyrsa
├── openssl-easyrsa.cnf
├── pki
│   ├── openssl-easyrsa.cnf
│   ├── private
│   ├── reqs
│   └── safessl-easyrsa.cnf
├── vars
└── x509-types
    ├── ca
    ├── client
    ├── code-signing
    ├── COMMON
    ├── email
    ├── kdc
    ├── server
    └── serverClient

4 directories, 13 files

##创建CA机构环境
[root@openvpn-server 3]#./easyrsa build-ca nopass
Your new CA certificate file for publishing is at:
/etc/openvpn/easy-rsa-server/3/pki/ca.crt       #生成自签名的证书文件

[root@openvpn-server 3]#tree pki
pki
├── ca.crt                                       #生成的自签名的证书文件
├── certs_by_serial
├── index.txt
├── index.txt.attr
├── issued
├── openssl-easyrsa.cnf
├── private
│   └── ca.key                                    #生成的私钥文件
├── renewed
│   ├── certs_by_serial
│   ├── private_by_serial
│   └── reqs_by_serial
├── reqs
├── revoked
│   ├── certs_by_serial
│   ├── private_by_serial
│   └── reqs_by_serial
├── safessl-easyrsa.cnf
└── serial

12 directories, 7 files

#5、创建服务器端证书申请
##创建服务器证书申请文件，其中server是文件前缀
[root@openvpn-server 3]#./easyrsa gen-req server nopass
Keypair and certificate request completed. Your files are:
req: /etc/openvpn/easy-rsa-server/3/pki/reqs/server.req                      #生成请求文件
key: /etc/openvpn/easy-rsa-server/3/pki/private/server.key                   #生成私钥文件

[root@openvpn-server 3]#tree pki
pki
├── ca.crt
├── certs_by_serial
├── index.txt
├── index.txt.attr
├── issued
├── openssl-easyrsa.cnf
├── private
│   ├── ca.key
│   └── server.key
├── renewed
│   ├── certs_by_serial
│   ├── private_by_serial
│   └── reqs_by_serial
├── reqs
│   └── server.req
├── revoked
│   ├── certs_by_serial
│   ├── private_by_serial
│   └── reqs_by_serial
├── safessl-easyrsa.cnf
└── serial

12 directories, 9 files

#6、颁发服务器端证书
##第一个server表示证书的类型，第二个server表示请求文件名的前缀
[root@openvpn-server 3]#./easyrsa sign server server
Request subject, to be signed as a server certificate for 3650 days:          #vars文件指定的有效期

subject=
    commonName                = server

Certificate created at: /etc/openvpn/easy-rsa-server/3/pki/issued/server.crt   #生成服务器证书文件

[root@openvpn-server 3]#tree pki
pki
├── ca.crt
├── certs_by_serial
│   └── 41ED3602BB7FDF3215AA321299C1C2BF.pem                                   #生成的服务器证书文件
├── index.txt
├── index.txt.attr
├── index.txt.attr.old
├── index.txt.old
├── issued
│   └── server.crt                                                             #生成的服务器证书文件
├── openssl-easyrsa.cnf
├── private
│   ├── ca.key
│   └── server.key
├── renewed
│   ├── certs_by_serial
│   ├── private_by_serial
│   └── reqs_by_serial
├── reqs
│   └── server.req
├── revoked
│   ├── certs_by_serial
│   ├── private_by_serial
│   └── reqs_by_serial
├── safessl-easyrsa.cnf
├── serial
└── serial.old

12 directories, 14 files

#7、创建Diffie-Hellman密钥
[root@openvpn-server 3]#./easyrsa gen-dh

Note: using Easy-RSA configuration from: /etc/openvpn/easy-rsa-server/3.0.8/vars
Using SSL: openssl OpenSSL 1.1.1k  FIPS 25 Mar 2021
Generating DH parameters, 2048 bit long safe prime, generator 2
This is going to take a long time
...................................++*++*++*++*

DH parameters of size 2048 created at /etc/openvpn/easy-rsa-server/3/pki/dh.pem  #生成密钥的位置

#8、准备客户端证书环境
##上面服务器端证书配置完成，下面是客户端证书
##复制内容，从头再来
[root@openvpn-server 3]#cp -a /usr/share/easy-rsa/ /etc/openvpn/easy-rsa-client
[root@openvpn-server 3]#cd /etc/openvpn/easy-rsa-client/3/
[root@openvpn-server 3]#tree
.
├── easyrsa
├── openssl-easyrsa.cnf
├── vars
└── x509-types
    ├── ca
    ├── client
    ├── code-signing
    ├── COMMON
    ├── email
    ├── kdc
    ├── server
    └── serverClient

1 directory, 11 files

##生成证书所需要的目录文件
[root@openvpn-server 3]#./easyrsa init-pki

Your newly created PKI dir is: /etc/openvpn/easy-rsa-client/3/pki

[root@openvpn-server 3]#tree
.
├── easyrsa
├── openssl-easyrsa.cnf
├── pki
│   ├── openssl-easyrsa.cnf
│   ├── private
│   ├── reqs
│   └── safessl-easyrsa.cnf
├── vars
└── x509-types
    ├── ca
    ├── client
    ├── code-signing
    ├── COMMON
    ├── email
    ├── kdc
    ├── server
    └── serverClient

4 directories, 13 files

##生成客户端用户的证书申请
[root@openvpn-server 3]#./easyrsa gen-req mazhuobo nopass
Keypair and certificate request completed. Your files are:
req: /etc/openvpn/easy-rsa-client/3/pki/reqs/mazhuobo.req                  #证书申请文件
key: /etc/openvpn/easy-rsa-client/3/pki/private/mazhuobo.key               #私钥文件

[root@openvpn-server 3]#tree
.
├── easyrsa
├── openssl-easyrsa.cnf
├── pki
│   ├── openssl-easyrsa.cnf
│   ├── private
│   │   └── mazhuobo.key                                                     #私钥文件
│   ├── reqs
│   │   └── mazhuobo.req                                                     #证书申请文件
│   └── safessl-easyrsa.cnf
├── vars
└── x509-types
    ├── ca
    ├── client
    ├── code-signing
    ├── COMMON
    ├── email
    ├── kdc
    ├── server
    └── serverClient

4 directories, 15 files

##颁发客户端证书
[root@openvpn-server 3]#cd /etc/openvpn/easy-rsa-server/3
##将客户端证书请求文件复制到CA的工作目录下
[root@openvpn-server 3]#./easyrsa import-req /etc/openvpn/easy-rsa-client/3/pki/reqs/mazhuobo.req mazhuobo

Note: using Easy-RSA configuration from: /etc/openvpn/easy-rsa-server/3.0.8/vars
Using SSL: openssl OpenSSL 1.1.1k  FIPS 25 Mar 2021

The request has been successfully imported with a short name of: mazhuobo
You may now use this name to perform signing operations on this request.
[root@openvpn-server 3]#tree pki
pki
├── ca.crt
├── certs_by_serial
│   └── 41ED3602BB7FDF3215AA321299C1C2BF.pem
├── dh.pem
├── index.txt
├── index.txt.attr
├── index.txt.attr.old
├── index.txt.old
├── issued
│   └── server.crt
├── openssl-easyrsa.cnf
├── private
│   ├── ca.key
│   └── server.key
├── renewed
│   ├── certs_by_serial
│   ├── private_by_serial
│   └── reqs_by_serial
├── reqs
│   ├── mazhuobo.req
│   └── server.req
├── revoked
│   ├── certs_by_serial
│   ├── private_by_serial
│   └── reqs_by_serial
├── safessl-easyrsa.cnf
├── serial
└── serial.old

12 directories, 16 files

##修改给客户端颁发的证书有效期
[root@openvpn-server 3]#vim vars 
set_var EASYRSA_CERT_EXPIRE    90

##颁发客户端证书
[root@openvpn-server 3]#./easyrsa sign client mazhuobo
Request subject, to be signed as a client certificate for 90 days:                       #证书有效期

subject=
    commonName                = mazhuobo

Certificate created at: /etc/openvpn/easy-rsa-server/3/pki/issued/mazhuobo.crt           #证书文件

[root@openvpn-server 3]#tree pki
pki
├── ca.crt
├── certs_by_serial
│   ├── 1DE23BEC76F9E00DAE268260585D0B89.pem
│   └── 41ED3602BB7FDF3215AA321299C1C2BF.pem
├── dh.pem
├── index.txt
├── index.txt.attr
├── index.txt.attr.old
├── index.txt.old
├── issued
│   ├── mazhuobo.crt
│   └── server.crt
├── openssl-easyrsa.cnf
├── private
│   ├── ca.key
│   └── server.key
├── renewed
│   ├── certs_by_serial
│   ├── private_by_serial
│   └── reqs_by_serial
├── reqs
│   ├── mazhuobo.req
│   └── server.req
├── revoked
│   ├── certs_by_serial
│   ├── private_by_serial
│   └── reqs_by_serial
├── safessl-easyrsa.cnf
├── serial
└── serial.old

12 directories, 18 files

#9、将CA和服务器证书相关文件复制到服务器相应目录
[root@openvpn-server 3]#mkdir /etc/openvpn/certs
[root@openvpn-server 3]#cp /etc/openvpn/easy-rsa-server/3/pki/ca.crt /etc/openvpn/certs/
[root@openvpn-server 3]#cp /etc/openvpn/easy-rsa-server/3/pki/issued/server.crt /etc/openvpn/certs/
[root@openvpn-server 3]#cp /etc/openvpn/easy-rsa-server/3/pki/private/server.key /etc/openvpn/certs/
[root@openvpn-server 3]#cp /etc/openvpn/easy-rsa-server/3/pki/dh.pem /etc/openvpn/certs/

#10、将客户端证书相关文件复制到服务器相应目录
##且看仔细是那个目录下的那个文件
[root@openvpn-server 3]#cp /etc/openvpn/easy-rsa-client/3.0.8/pki/private/mazhuobo.key /etc/openvpn/client/mazhuobo/
[root@openvpn-server 3]#cp /etc/openvpn/easy-rsa-server/3.0.8/pki/issued/mazhuobo.crt /etc/openvpn/client/mazhuobo/
[root@openvpn-server 3]#cp /etc/openvpn/easy-rsa-server/3.0.8/pki/ca.crt /etc/openvpn/client/mazhuobo/
[root@openvpn-server 3]#ll /etc/openvpn/client/mazhuobo/
total 16
-rw------- 1 root root 1204 Aug 20 16:08 ca.crt
-rw------- 1 root root 4494 Aug 20 16:08 mazhuobo.crt
-rw------- 1 root root 1708 Aug 20 16:04 mazhuobo.key

#11、准备OpenVPN服务器配置文件
[root@openvpn-server ~]#vim /etc/openvpn/server.conf
[root@openvpn-server ~]#grep '^[a-Z].*' /etc/openvpn/server.conf
port 1194
proto tcp
dev tun
ca /etc/openvpn/certs/ca.crt
cert /etc/openvpn/certs/server.crt
key /etc/openvpn/certs/server.key  # This file should be kept secret
dh /etc/openvpn/certs/dh.pem
server 10.8.0.0 255.255.255.0
push "route 192.168.10.0 255.255.255.0"
keepalive 10 120
cipher AES-256-CBC
compress lz4-v2
push "compress lz4-v2"
max-clients 2048
user openvpn 
group openvpn
status /var/log/openvpn/openvpn-status.log
log-append  /var/log/openvpn/openvpn.log
verb 3
mute 20

##准备日志相关目录
[root@openvpn-server ~]#getent passwd openvpn
openvpn:x:994:991:OpenVPN:/etc/openvpn:/sbin/nologin
[root@openvpn-server ~]#mkdir /var/log/openvpn
[root@openvpn-server ~]#chown openvpn.openvpn /var/log/openvpn
[root@openvpn-server ~]#ll -d /var/log/openvpn
drwxr-xr-x 2 openvpn openvpn 6 Aug 20 19:22 /var/log/openvpn

#12、启动OpenVPN服务
##centos8上缺少文件从centos7上拷贝
[19:27:55 root@centos7 ~]#scp /lib/systemd/system/openvpn@.service 10.0.0.28:/lib/systemd/system/
[root@openvpn-server ~]#systemctl daemon-reload 
[root@openvpn-server ~]#systemctl enable --now openvpn@server

#13、生成客户端用户的配置文件
[root@openvpn-server ~]#grep '^[[:alpha:]].*' /usr/share/doc/openvpn/sample/sample-config-files/client.conf > /etc/openvpn/client/mazhuobo/client.ovpn
[root@openvpn-server ~]#vim /etc/openvpn/client/mazhuobo/client.ovpn
[root@openvpn-server ~]#cat !*
cat /etc/openvpn/client/mazhuobo/client.ovpn
client
dev tun
proto udp
remote my-server-1 1194
resolv-retry infinite
nobind
#persist-key
#persist-tun
ca ca.crt
cert client.crt
key client.key
remote-cert-tls server
#tls-auth ta.key 1
cipher AES-256-CBC
verb 3                            #此值不能随便指定，否则无法通信
compress lz4-v2                   #此项在OpenVPN2.4.X版本使用，需要和服务器端保持一致，如不指定，默认使用comp-lz压缩

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

一、MySQL数据库崩溃原因

MySQL数据库崩溃的原因有很多，比如硬件故障、操作系统崩溃、MySQL软件本身故障等。无论是哪种原因导致的数据库崩溃，都会对数据的完整性和安全性造成影响。

二、MySQL数据库崩溃恢复方法

1.备份数据

ysqldump命令或者第三方工具进行。

2.查看错误日志

当MySQL数据库崩溃时，会生成错误日志。通过查看错误日志，可以了解崩溃原因和相关信息，为后续的恢复工作提供参考。

3.恢复数据

MySQL数据库崩溃后，需要进行数据恢复。具体恢复方法如下：

（1）使用MySQL自带的数据恢复工具

ysqlbinlogysqldump等。可以根据具体情况选择合适的工具进行恢复。

（2）使用第三方数据恢复工具

en MySQL Recovery等。这些工具可以帮助用户快速恢复数据。

4.优化数据库

在恢复数据之后，需要对数据库进行优化，以提高数据库的性能和稳定性。具体优化方法如下：

（1）优化MySQL配置文件

通过修改MySQL配置文件，可以优化数据库的性能和稳定性。比如可以调整缓存大小，优化查询语句等。

（2）定期清理数据

定期清理无用数据可以提高数据库的性能和稳定性。比如可以删除过期的日志、临时表等。

（3）优化数据库设计

优化数据库设计可以提高数据库的性能和稳定性。比如可以将大表拆分成多个小表，使用索引优化查询等。

三、MySQL数据库崩溃预防措施

为了避免MySQL数据库崩溃，可以采取以下预防措施：

（1）定期备份数据

定期备份数据可以保证数据的完整性和安全性。建议每天备份一次数据。

（2）定期检查数据库

定期检查数据库可以发现潜在的问题，及时解决问题，避免数据库崩溃。

（3）定期优化数据库

定期优化数据库可以提高数据库的性能和稳定性，避免数据库崩溃。

MySQL数据库崩溃是一种常见的问题，但是只要掌握了正确的恢复方法和预防措施，就可以避免数据丢失和数据库崩溃。在使用MySQL数据库时，一定要注意备份数据、定期检查数据库和优化数据库，以确保数据的完整性和安全性。
#八、myisam和innodb各自在什么场景使用？

MyISAM和InnoDB的区别

InnoDB

InnoDB 是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB 是默认的 MySQL 引擎。

特点：

DML 操作遵循 ACID 模型，支持事务
行级锁，提高并发访问性能
支持外键约束，保证数据的完整性和正确性

文件：

xxx.ibd: xxx代表表名，InnoDB 引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引。

MyISAM

MyISAM 是 MySQL 早期的默认存储引擎。

特点：

不支持事务，不支持外键
支持表锁，不支持行锁
访问速度快

文件：

xxx.sdi: 存储表结构信息
xxx.MYD: 存储数据
xxx.MYI: 存储索引

存储引擎的选择

InnoDB: 如果应用对事物的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，则 InnoDB 是比较合适的选择
MyISAM: 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不高，那这个存储引擎是非常合适的。