一、简述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: 如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性、并发性要求不高,那这个存储引擎是非常合适的。