第六周作业 SQL语句、xtrabackup备份、OpenVPN
1、简述DDL,DML,DCL,DQL，并且说明mysql各个关键字查询时候的先后顺序
1.1 DDL数据定义语句（Data Definition Language)
主要负责数据结构定义与数据库对象定义的语句，由create，alter，drop三个语法组成。DDL操作时隐形提交的，不能rollback。
1.2 DML数据操纵语言(Data Manipulation Language)
负责对数据库对象运行数据访问工作的指令集，以insert，update，delete三种指令为核心，分别代表插入、更新与删除。
1.3 DQL数据查询语句(Data Query Language)
负责进行数据查询而不会对数据本身进行修改的语句，这是最基本的SQL语句。select是DQL用的最多的。其他还有from，where，group by，having和order by。这些DQL保留字常与其他类型的SQL语句一起使用。
1.4 DCL数据控制语句(Data Control Language)
对数据访问权进行控制的指令，它可以控制特定用户账号对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。由grant和revoke两个指令组成。DCL以控制用户的访问权限为主，grant为授权语句，对应的revoke是撤销授权语句。
1.5 mysql各个关键字查询时候的先后顺序
2、自行设计10个sql查询语句，需要用到关键字[GROUP BY/HAVING/ORDER BY/LIMIT]，至少同时用到两个。
#1 分组后取出男生的平均年龄
(root@localhost) [hellodb]> select gender,avg(age) from students group by gender having gender = 'M';
+--------+----------+
| gender | avg(age) |
+--------+----------+
| M | 33.0000 |
+--------+----------+
1 row in set (0.01 sec)
#2 或者这样处理
(root@localhost) [hellodb]> select gender,avg(age) from students where gender='M' group by gender;
+--------+----------+
| gender | avg(age) |
+--------+----------+
| M | 33.0000 |
+--------+----------+
1 row in set (0.00 sec)
#3 多组分组统计
(root@localhost) [hellodb]> select classid,gender,avg(age) from students where classid is not null group by classid,gender order by classid;
+---------+--------+----------+
| classid | gender | avg(age) |
+---------+--------+----------+
| 1 | F | 19.5000 |
| 1 | M | 21.5000 |
| 2 | M | 36.0000 |
| 3 | F | 18.3333 |
| 3 | M | 26.0000 |
| 4 | M | 24.7500 |
| 5 | M | 46.0000 |
| 6 | F | 20.0000 |
| 6 | M | 23.0000 |
| 7 | F | 18.0000 |
| 7 | M | 23.0000 |
+---------+--------+----------+
11 rows in set (0.00 sec)
#4 以ClassID为分组依据，显示平均年龄大于30的分组及平均年龄
mysql> select classid,avg(age) as 平均年龄 from students where classid is not null group by classid having 平均年龄>30;
+---------+--------------+
| classid | 平均年龄 |
+---------+--------------+
| 2 | 36.0000 |
| 5 | 46.0000 |
+---------+--------------+
2 rows in set (0.00 sec)
#5 以年龄排序后，显示年龄最大的前10位同学的信息
mysql> select * from students order by age limit 10;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
+-------+--------------+-----+--------+---------+-----------+
10 rows in set (0.00 sec)
#6 以ClassID分组，显示每班的同学的人数
mysql> select classid,count(stuid) from students where classid is not null group by classid order by classid;
+---------+--------------+
| classid | count(stuid) |
+---------+--------------+
| 1 | 4 |
| 2 | 3 |
| 3 | 4 |
| 4 | 4 |
| 5 | 1 |
| 6 | 4 |
| 7 | 3 |
+---------+--------------+
7 rows in set (0.00 sec)
#7 以ClassID分组，显示其平均年龄大于25的班级
mysql> select classid,avg(age) 平均年龄 from students where classid is not null group by classid having 平均年龄>25;
+---------+--------------+
| classid | 平均年龄 |
+---------+--------------+
| 2 | 36.0000 |
| 5 | 46.0000 |
+---------+--------------+
2 rows in set (0.00 sec)
#8 以Gender分组，显示各组中年龄大于25的学员的年龄之和
mysql> select classid,gender,sum(age) from students where classid is not null and age > 25 group by classid,gender order by classid,gender;
+---------+--------+----------+
| classid | gender | sum(age) |
+---------+--------+----------+
| 2 | M | 86 |
| 3 | M | 26 |
| 4 | M | 32 |
| 5 | M | 46 |
+---------+--------+----------+
4 rows in set (0.00 sec)
#9 查看学生中年龄前三名
mysql> select * from students order by age desc limit 3;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)
#10 查看前三个班级中的学生总人数
mysql> select classid,count(*) 数量 from students where classid is not null group by classid order by classid limit 3;
+---------+--------+
| classid | 数量 |
+---------+--------+
| 1 | 4 |
| 2 | 3 |
| 3 | 4 |
+---------+--------+
3 rows in set (0.00 sec)3、xtrabackup备份和还原数据库练习
利用xtrabackup完全，增量备份及还原
#1 备份过程
#1.1 在原来主机上做完全备份到/backup
[16:18:45 root@rocky ~]$ mkdir /backup
[16:21:07 root@rocky ~]$ xtrabackup -uroot -plgq123456** --backup --target-dir=/backup/base
##1.2 第一次修改数据
(root@localhost) [hellodb]> insert students (name,age,gender)values('rose',20,'F');
Query OK, 1 row affected (0.00 sec)
##1.3 第一次增量备份
[17:37:23 root@rocky ~]$ xtrabackup -uroot -p'lgq123456**' --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
##1.4 第二次修改数据
(root@localhost) [hellodb]> insert students (name,age,gender)values('jack',22,'M');
Query OK, 1 row affected (0.00 sec)
##1.5 第二次增量备份
[17:38:30 root@rocky ~]$ xtrabackup -uroot -p'lgq123456**' --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1
##1.6 拷贝三次备份数据到测试服务器
[18:57:42 root@rocky ~]$ scp -r /backup/ 10.0.0.9:/
#2 还原过程
##2.1 预准备完成备份，此次不需要回滚未完成事务
[19:02:59 root@rocky8 /]$ xtrabackup --prepare --apply-log-only --target-dir=/backup/base
##2.2 合并第一次增量备份到完全备份
[19:04:30 root@rocky8 /]$ xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1
##2.3 合并第二次增量备份，最后一次还原所以不需要加--apply-log-only
[19:07:54 root@rocky8 /]$ xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2
##2.4 复制到数据库目录，确保/var/lib/mysql目录为空，MySQL服务为停止状态
[19:09:40 root@rocky8 /]$ xtrabackup --copy-back --target-dir=/backup/base
##2.5还原属性
[19:11:19 root@rocky8 /]$ chown -R mysql:mysql /var/lib/mysql/
##2.6 启动服务登录数据库查看修改的数据时候还原回来了
[19:11:42 root@rocky8 /]$ systemctl start mysqld.service
[19:12:34 root@rocky8 /]$ mysql -uroot -p
Enter password:
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> 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> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 26 | rose | 20 | F | NULL | NULL |
| 27 | jack | 22 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
27 rows in set (0.00 sec)4、实现mysql主从复制，主主复制和半同步复制
4.1 mysql主从复制
实验环境：
- 主节点：10.0.0.3
- 从节点：10.0.0.9
#1 主节点设置
##1.1 设置配置文件，并重启服务
[10:07:35 root@master ~]$ vi /etc/my.cnf
[mysqld]
server-id=3
log_bin=/data/binlog/mysql-bin
[10:27:19 root@master ~]$ systemctl restart mysqld.service
##1.2 登录mysql，创建复制用户以及授权
[10:43:17 root@master ~]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
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.
(root@localhost) [(none)]> create user 'repluser'@'10.0.0.%' identified by 'lgq123456';
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [(none)]> grant replication slave on *.* to 'repluser'@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
##1.3 进行完全备份
[10:50:47 root@master ~]$ mysqldump -uroot -p'lgq123456**' -A -F --single-transaction --master-data=1 > /backup/full_`date +%F_%T`.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[10:50:50 root@master ~]$ ls /backup/
full_2022-09-19_10:50:50.sql
##1.4 拷贝备份数据库到10.0.0.9
[10:50:54 root@master ~]$ scp /backup/* 10.0.0.9:/backup
root@10.0.0.9's password:
full_2022-09-19_10:50:50.sql 100% 1597KB 96.0MB/s 00:00
#2 从节点
##2.1 修改配置文件，并重启
[10:55:39 root@slave1 ~]$ vi /etc/my.cnf
[mysqld]
server-id=9
read-only
[10:57:19 root@slave1 ~]$ systemctl start mysqld.service
##2.2 修改备份文件，在change master to中添加主节点信息
[10:58:25 root@slave1 ~]$ vi /backup/full_2022-09-19_10.sql
...省略
CHANGE MASTER TO
MASTER_HOST='10.0.0.3', #主节点IP地址
MASTER_USER='repluser', #在主节点上创建的账号
MASTER_PASSWORD='lgq123456', #密码
MASTER_PORT=3306, #端口号
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=156;
...省略
##2.3 还原备份
###暂时关闭二进制日志
mysql> set sql_log_bin=0;
###还原
mysql> source /backup/full_2022-09-19.sql;
##2.4 开启主从节点的链接线程
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.3
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 156
Relay_Log_File: slave1-relay-bin.000002
Relay_Log_Pos: 324
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 156
Relay_Log_Space: 534
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
Master_UUID: 4c7960e2-2dec-11ed-b13a-000c29b92bb4
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
#3 测试主从是否同步
##3.1 主节点上更新数据
(root@localhost) [hellodb]> insert teachers (name,age,gender) values('zhang',28,'F');
Query OK, 1 row affected (0.01 sec)
(root@localhost) [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | zhang | 28 | F |
+-----+---------------+-----+--------+
5 rows in set (0.00 sec)
##3.2 从节点查看数据是否同步
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | zhang | 28 | F |
+-----+---------------+-----+--------+
5 rows in set (0.00 sec)4.2 主主复制
#1 在第一个master节点上实现
##1.1 修改配置信息重启服务
[15:00:10 root@master ~]$ vi /etc/my.cnf
[mysqld]
server-id=3
log_bin=/data/binlog/mysql-bin
auto_increment_offset=1
auto_increment_increment=2
[15:01:28 root@master ~]$ systemctl restart mysqld.service
##1.2 创建并授权账号
(root@localhost) [(none)]> create user 'repluser'@'10.0.0.%' identified by 'lgq123456';
(root@localhost) [(none)]> grant replication slave on *.* to 'repluser'@'10.0.0.%';
#2 在第二个master上实现
##2.1 修改配置文件
[15:24:16 root@rocky8 ~]$ vi /etc/my.cnf
[mysqld]
server-id=9
auto_increment_offset=2
auto_increment_increment=2
[15:25:21 root@rocky8 ~]$ systemctl start mysqld.service
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.3',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='lgq123456',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000003',
-> MASTER_LOG_POS=156;
mysql> start slave;
mysql> show master logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 3185009 | No |
| binlog.000002 | 156 | No |
+---------------+-----------+-----------+
2 rows in set (0.00 sec)
#3 在第一个节点上实现
(root@localhost) [(none)]> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.9',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='lgq123456',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='binlog.000002',
-> MASTER_LOG_POS=156;
Query OK, 0 rows affected, 9 warnings (0.01 sec)
(root@localhost) [(none)]> start slave;4.3 半同步复制
实验环境：
- master：10.0.0.3
- slave1：10.0.0.9
- slave2：10.0.0.10
#1 master节点实现
##1.1 安装插件
(root@localhost) [(none)]> install plugin rpl_semi_sync_master SONAME 'semisync_master.so';
##1.2 修改配置文件，重启服务
[16:41:10 root@master ~]$ vi /etc/my.cnf
[mysqld]
server-id=3
log_bin=/data/binlog/mysql-bin
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_master_timeout=3000 #设置3s内无法同步，也将返回成功信息给客户端
[16:42:43 root@master ~]$ systemctl restart mysqld.service
##1.3 创建并授权账号
(root@localhost) [(none)]> create user 'repluser'@'10.0.0.%' identified by 'lgq123456';
(root@localhost) [(none)]> grant replication slave on *.* to 'repluser'@'10.0.0.%';
##1.4 备份数据库
[16:49:11 root@master ~]$ mysqldump -uroot -p'lgq123456**' -A -F --single-transaction --master-data > /backup/all.sql
[16:50:12 root@master ~]$ scp /backup/all.sql 10.0.0.9:/backup
[16:51:10 root@master ~]$ scp /backup/all.sql 10.0.0.10:/backup
#2 在slave1上实现
##2.1 安装插件
[16:54:16 root@rocky8 ~]$ systemctl start mysqld.service
[16:54:29 root@rocky8 ~]$ mysql
mysql> install plugin rpl_semi_sync_slave SONAME 'semisync_slave.so';
##2.2 修改配置文件
[16:55:56 root@rocky8 ~]$ vi /etc/my.cnf
[mysqld]
server-id=9
rpl_semi_sync_slave_enabled=ON
[16:57:51 root@rocky8 ~]$ systemctl restart mysqld.service
##2.3 修改备份数据文件，在change master to语句改为以下信息
[16:59:02 root@rocky8 ~]$ vi /backup/all.sql
CHANGE MASTER TO
MASTER_HOST='10.0.0.3',
MASTER_USER='repluser',
MASTER_PASSWORD='lgq123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=156;
##2.4 同步备份数据库
[17:00:27 root@rocky8 ~]$ mysql < /backup/all.sql
##2.5 登录数据库，开启slave
mysql> start slave;
#3 在slave1上实现
##3.1 安装插件
[17:02:16 root@rocky8 ~]$ systemctl start mysqld.service
[17:02:29 root@rocky8 ~]$ mysql
mysql> install plugin rpl_semi_sync_slave SONAME 'semisync_slave.so';
##3.2 修改配置文件
[17:04:56 root@rocky8 ~]$ vi /etc/my.cnf
[mysqld]
server-id=10
rpl_semi_sync_slave_enabled=ON
[17:05:51 root@rocky8 ~]$ systemctl restart mysqld.service
##3.3 修改备份数据文件，在change master to语句改为以下信息
[17:06:02 root@rocky8 ~]$ vi /backup/all.sql
CHANGE MASTER TO
MASTER_HOST='10.0.0.3',
MASTER_USER='repluser',
MASTER_PASSWORD='lgq123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=156;
##3.4 同步备份数据库
[17:07:27 root@rocky8 ~]$ mysql < /backup/all.sql
##2.5 登录数据库，开启slave
mysql> start slave;
#观察半同步功能是否开启
mysql> SHOW GLOBAL VARIABLES LIKE '%semi%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+---------------------------------+-------+
#在主节点上查看半同步状态变量
(root@localhost) [(none)]> show status LIKE '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 2 |#有两个从节点
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
#测试是否可以同步
##在主节点插入数据
(root@localhost) [hellodb]> insert teachers (name,age,gender)values('c',35,'M');
Query OK, 1 row affected (0.00 sec)
(root@localhost) [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | zhang | 28 | F |
| 6 | sun | 24 | F |
| 7 | a | 42 | F |
| 8 | b | 34 | M |
| 9 | a | 42 | F |
| 10 | c | 35 | M |
+-----+---------------+-----+--------+
10 rows in set (0.00 sec)
##slave1测试
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | zhang | 28 | F |
| 6 | sun | 24 | F |
| 7 | a | 42 | F |
| 8 | b | 34 | M |
| 9 | a | 42 | F |
| 10 | c | 35 | M |
+-----+---------------+-----+--------+
10 rows in set (0.00 sec)
##slave2测试
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | zhang | 28 | F |
| 6 | sun | 24 | F |
| 7 | a | 42 | F |
| 8 | b | 34 | M |
| 9 | a | 42 | F |
| 10 | c | 35 | M |
+-----+---------------+-----+--------+
10 rows in set (0.00 sec)
#半同步复制测试
##暂停slave1从节点复制
mysql> stop slave;
##主节点插入数据
(root@localhost) [hellodb]> insert teachers (name,age,gender)values('d',32,'F');
Query OK, 1 row affected (0.01 sec)##同步很快
##slave2也可以获得同步数据
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | zhang | 28 | F |
| 6 | sun | 24 | F |
| 7 | a | 42 | F |
| 8 | b | 34 | M |
| 9 | a | 42 | F |
| 10 | c | 35 | M |
| 11 | d | 32 | F |
+-----+---------------+-----+--------+
11 rows in set (0.00 sec)
##再次暂停slave2的从节点复制
mysql> stop slave;
##再次在主节点插入数据
(root@localhost) [hellodb]> insert teachers (name,age,gender)values('e',19,'M');
Query OK, 1 row affected (3.00 sec)#发现运行时间变成了3秒，因为前面在配置文件中设置了3s内无法同步，也将返回成功信息给客户端5、用mycat实现mysql的读写分离
实验环境：
- clinet：10.0.0.5
- mycat：10.0.0.4
- master: 10.0.0.3
- slave：10.0.0.9
1、mysql主从节点搭建实现
#1 主节点设置
##1.1 设置配置文件，并重启服务
[10:07:35 root@master ~]$ vi /etc/my.cnf
[mysqld]
server-id=3
log_bin=/data/binlog/mysql-bin
[10:27:19 root@master ~]$ systemctl restart mysqld.service
##1.2 登录mysql，创建复制用户以及授权
[10:43:17 root@master ~]$ mysql -uroot -p
(root@localhost) [(none)]> create user 'repluser'@'10.0.0.%' identified by 'lgq123456';
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [(none)]> grant replication slave on *.* to 'repluser'@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
##1.3 进行完全备份
[10:50:47 root@master ~]$ mysqldump -uroot -p'lgq123456**' -A -F --single-transaction --master-data=1 > /backup/full_`date +%F_%T`.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[10:50:50 root@master ~]$ ls /backup/
full_2022-09-19_10:50:50.sql
##1.4 拷贝备份数据库到10.0.0.9
[10:50:54 root@master ~]$ scp /backup/* 10.0.0.9:/backup
root@10.0.0.9's password:
full_2022-09-19_10:50:50.sql 100% 1597KB 96.0MB/s 00:00
#2 从节点
##2.1 修改配置文件，并重启
[10:55:39 root@slave1 ~]$ vi /etc/my.cnf
[mysqld]
server-id=9
read-only
[10:57:19 root@slave1 ~]$ systemctl start mysqld.service
##2.2 修改备份文件，在change master to中添加主节点信息
[10:58:25 root@slave1 ~]$ vi /backup/full_2022-09-19_10.sql
...省略
CHANGE MASTER TO
MASTER_HOST='10.0.0.3', #主节点IP地址
MASTER_USER='repluser', #在主节点上创建的账号
MASTER_PASSWORD='lgq123456', #密码
MASTER_PORT=3306, #端口号
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=156;
...省略
##2.3 还原备份
###暂时关闭二进制日志
mysql> set sql_log_bin=0;
###还原
mysql> source /backup/full_2022-09-19.sql;
##2.4 开启主从节点的链接线程
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.3
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 156
Relay_Log_File: slave1-relay-bin.000002
Relay_Log_Pos: 324
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 156
Relay_Log_Space: 534
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
Master_UUID: 4c7960e2-2dec-11ed-b13a-000c29b92bb4
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
#3 测试主从是否同步
##3.1 主节点上更新数据
(root@localhost) [hellodb]> insert teachers (name,age,gender) values('zhang',28,'F');
Query OK, 1 row affected (0.01 sec)
(root@localhost) [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | zhang | 28 | F |
+-----+---------------+-----+--------+
5 rows in set (0.00 sec)
##3.2 从节点查看数据是否同步
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | zhang | 28 | F |
+-----+---------------+-----+--------+
5 rows in set (0.00 sec)2、在MySQL代理服务器10.0.0.4安装mycat并启动
#下载
[11:14:25 root@mycat ~]$ wget http://dl.mycat.org.cn/1.6.7.6/20210303094759/Mycat-server-
1.6.7.6-release-20210303094759-linux.tar.gz
#解压到/apps下
[11:27:47 root@mycat ~]$ mkdir /apps/; tar xf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /apps/
[11:28:33 root@mycat ~]$ ls /apps/
mycat
#添加到环境变量
[11:29:17 root@mycat ~]$ echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
[11:31:19 root@mycat ~]$ source /etc/profile.d/mycat.sh
#安装java，要求内存尽量大点，本实验使用的是3G
[11:33:59 root@mycat ~]$ yum -y install java
#启动mycat
[11:35:34 root@mycat ~]$ mycat start
Starting Mycat-server...
[11:36:04 root@mycat ~]$ ss -nlt
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 1 127.0.0.1:32000 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 50 *:46681 *:*
LISTEN 0 50 *:1984 *:*
LISTEN 0 100 *:8066 #监听端口 *:*
LISTEN 0 100 *:9066 *:*
LISTEN 0 50 *:37615 *:*
LISTEN 0 128 *:80 *:*
LISTEN 0 128 [::]:22 [::]:*
#客户端测试连接，默认密码123456
[11:40:26 root@client ~]$ mysql -uroot -p123456 -h 10.0.0.4 -P8066
MySQL [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+3、在MyCat服务器上修改server.xml文件配置MyCat的链接信息
#在主节点服务器中创建连接mycat的账号
(root@localhost) [(none)]> create user admin@'10.0.0.%' identified by 'lgq123456**';
Query OK, 0 rows affected (0.01 sec)
(root@localhost) [(none)]> grant all on hellodb.* to admin@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
[11:37:35 root@mycat ~]$ vi /apps/mycat/conf/server.xml
#修改部分参看下面图片
#修改/apps/mycat/conf/schema.xml配置信息
[11:51:49 root@mycat ~]$ vi /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" /> #此处的database需要修改成自己想要利用mycat实现监控的数据库名字
<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.3:3306" user="admin"
password="lgq123456**"> #需要执行写操作的节点ip以及账号密码信息，是指之前在mysql主节点上创建的账户信息
<readHost host="host2" url="10.0.0.9:3306" user="admin" password="lgq123456**" /> #从节点信息，若是多个从节点可以多加几条
</writeHost>
</dataHost>
</mycat:schema>
#重启mycat
[12:04:01 root@mycat ~]$ mycat restart上面配置中，balance改为1，表示读写分离。以上配置达到的效果就是10.0.0.3为主库，10.0.0.9为从库
注意：要保证能使用admin/lgq123456**权限成功登录10.0.0.3和10.0.0.9机器上面的mysql数据库。同时，也一定要授权mycat机器能使用admin/lgq123456**权限成功登录这两台机器的mysql数据库！！这很重要，否则会导致登录mycat后，对库和表操作失败！
4、在客户端连接测试
[11:41:47 root@client ~]$ mysql -uroot -p123456 -h 10.0.0.4
MySQL [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.01 sec)
MySQL [(none)]> use TESTDB
Database changed
MySQL [TESTDB]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.01 sec)5、通过通用日志确认实现读写分离
#在主节点上开启通用日志
(root@localhost) [(none)]> set global general_log=ON;
Query OK, 0 rows affected (0.01 sec)
(root@localhost) [(none)]> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log | ON |
+---------------+-------+
#开启后会在/var/lib/mysql/目录下生成`hostname`.log文件，其中十秒钟会产生select user()，这是在进行存活性健康性检测，mycat检测数据库存活性。
#从节点开启
mysql> set global general_log=ON;
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log | ON |
+---------------+-------+
#客户端更新数据来查看读写分离的实现
MySQL [TESTDB]> insert teachers(name,age,gender)values('f',27,'M');
#查看主从节点服务器中的通用日志文件
##主节点
[12:19:52 root@master ~]$ tail -f /var/lib/mysql/master.log
2022-09-20T04:22:34.040744Z 24 Query SET names utf8;
2022-09-20T04:22:34.041030Z 24 Query insert teachers(name,age,gender)values('f',27,'M') #主节点进行写
2022-09-20T04:22:40.574552Z 24 Query select user()
##从节点
[12:20:04 root@slave ~]$ tail -f /var/lib/mysql/slave.log
2022-09-20T04:22:34.048808Z 10 Query BEGIN
2022-09-20T04:22:34.049447Z 10 Query COMMIT /* implicit, from Xid_log_event */
2022-09-20T04:22:37.232799Z 14 Query select * from teachers #从节点进行读操作6、从节点坏了，MyCat自动调整读请求至主节点
#停掉从节点
[16:28:32 root@slave ~]$ systemctl stop mysqld.service
#服务端查看读操作是哪个节点
MySQL [TESTDB]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 3 |
+-------------+6、实现openvpn部署，并且测试通过，输出博客或者自己的文档存档。
阿里云 OpenVPN 实战环境
准备阿里云网络实验环境
1 阿里云创建专有网络
指定城市和可用区:华北3张家口可用区A区
网段名VPC1和地址段172.16.0.0/12,默认资源组
交换机名switch-test 可用区A IPv4的地址段 172.30.0.0/24
安全组开放22端口
2 创建OpenVPN服务器有公网IP的实例1个
指定城市和可用区:华北3张家口可用区A区
计算型c6 2vCPU 4G
网络:VPC1 交换机:switch-test
公网IP 按量收费 100M
默认安全组 默认配置 22,3389,icmp
centos8.5
系统盘 存储默认高效云盘40G
3 创建局域网的服务器无公网IP的实例2个
按量付费
指定城市和可用区:华北3张家口可用区A区
共享型 2vCPU2G
centos8.5
系统盘 存储默认高效云盘40G
网络:VPC1 switch-test
无公网IP
默认安全组
主网卡sw1
4 重设所有实例密码
5 修改安全组打开 1194/TCP/UDP1、购买第一台有公网IP的ECS
2、 再购买两台内网无公网的ECS
注意和第一台主机在同一个地域和可用区
过程同上，唯一不同之处就是在配置私有IP时，配成172.30.0.101和172.30.0.102，不需要公网IP。
3、 修改网络防火墙规则
默认VPN的端口无法访问,修改网络防火墙规则,添加1规则实现1194/TCP/UDP端口允许通过
4、自动安装OpenVPN以及配置客户端用户脚本
#!/bin/bash
#
#***********************************************************
#Author: yanli
#Date: 2022-09-22
#FileName: openvpn.sh
#Description: 自动安装OpenVPN及配置服务器和客户端配置文件
#***********************************************************
. /etc/init.d/functions
COLOR='echo -e \E[36m'
END='\E[0m'
OPENVPN_SERVER='openvpn.yanlinux.cn'
NET='10.8.0.0/24'
GATEWAY='172.30.0.1'
#安装OpenVPN以及证书管理工具easy-sra
install(){
rpm -qi openvpn &> /dev/null
if [[ $? -eq 0 ]];then
$COLOR "\nopenvpn is installed\n"$END
else
yum -y install openvpn && $COLOR "\nopenvpn has been installed\n"$END
fi
rpm -qi easy-rsa &> /dev/null
if [[ $? -eq 0 ]];then
$COLOR "\neasy-rsa is installed\n"$END
else
yum -y install easy-rsa && $COLOR "\neasy-rsa has been installed\n"$END
fi
}
#准备相关配置文件
setfiles(){
#准备证书颁发文件
mkdir -p /data/easy-rsa; cp -r /usr/share/easy-rsa/3/* /data/easy-rsa
#准备颁发证书相关变量的配置文件
cp /usr/share/doc/easy-rsa/vars.example /data/easy-rsa/vars
#修改证书有效期
echo -e 'set_var EASYRSA_CA_EXPIRE 36500\nset_var EASYRSA_CERT_EXPIRE 3650' >> /data/easy-rsa/vars
#初始化PKI生成PKI相关目录和文件
cd /data/easy-rsa
/data/easy-rsa/easyrsa init-pki && $COLOR "\n相关配置文件以及PKI初始化已完成\n"$END
}
#搭建CA机构环境
set_CA(){
cd /data/easy-rsa
/data/easy-rsa/easyrsa build-ca nopass <<EOF
EOF
[ -e /data/easy-rsa/pki/ca.crt ] && $COLOR "\nCA搭建完成,自签名文件已生成\n"$END || $COLOR "\nCA搭>建失败！！！\n"$END
}
#服务端证书颁发,并将CA和服务器证书相关文件复制到服务器相应的目录
server_cert(){
cd /data/easy-rsa
#创建服务器证书申请文件，其中server是文件前缀
/data/easy-rsa/easyrsa gen-req server nopass <<EOF
openvpn
EOF
#颁发服务端证书
/data/easy-rsa/easyrsa sign server server <<EOF
yes
EOF
[ -e /data/easy-rsa/pki/issued/server.crt ] && $COLOR "\n服务器证书颁发完毕\n"$END || $COLOR "\n>服
务器证书颁发失败！！！\n"$END
#创建 Diffie-Hellman 密钥
/data/easy-rsa/easyrsa gen-dh &> /dev/null
[ -e /data/easy-rsa/pki/dh.pem ] && $COLOR "\n密钥创建成功\n"$END || $COLOR "\n>密钥创建失败！！！\n"$END
#在openvpn软件目录中创建证书存放目录
mkdir /etc/openvpn/certs
#拷贝CA和服务器证书文件
cp /data/easy-rsa/pki/ca.crt /data/easy-rsa/pki/issued/server.crt /data/easy-rsa/pki/dh.pem /data/easy-rsa/pki/private/server.key /etc/openvpn/certs/
#启用防止DoS攻击的安全增强配置
openvpn --genkey --secret /etc/openvpn/certs/ta.key
}
#创建服务器配置文件并启动OpenVPN服务
server_conf_start(){
cat > /etc/openvpn/server.conf <<EOF
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 172.30.0.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
tls-auth /etc/openvpn/certs/ta.key 0
EOF
#准备日志相关目录
mkdir /var/log/openvpn/
chown openvpn. /var/log/openvpn/
#启动服务
##centos8缺失了unit文件，需要手动创建
cat > /usr/lib/systemd/system/openvpn@.service <<EOF
[Unit]
Description=OpenVPN Robust And Highly Flexible Tunneling Application On %I
After=network.target
[Service]
Type=notify
PrivateTmp=true
ExecStart=/usr/sbin/openvpn --cd /etc/openvpn/ --config %i.conf
[Install]
WantedBy=multi-user.target
EOF
systemctl daemon-reload
systemctl enable --now openvpn@server
}
#客户端证书创建，并将用户私钥与证书相关文件复制到服务器相关的目录
client_user_cert(){
read -p "请输入用户的姓名拼音(如:yanli): " NAME
read -p "请输入用户的密码：" PASSWD
#生成客户端用户的证书申请
cd /data/easy-rsa
expect <<EOF
spawn /data/easy-rsa/easyrsa gen-req ${NAME}
expect {
"Enter PEM pass phrase:" { send "$PASSWD\n";exp_continue }
"Verifying - Enter PEM pass phrase:" { send "$PASSWD\n";exp_continue }
"Common Name" { send "\n" }
}
expect eof
EOF
#修改给用户颁发的证书有效期
sed -i 's/set_var EASYRSA_CERT_EXPIRE 3650/set_var EASYRSA_CERT_EXPIRE 180/g' /data/easy-rsa/vars
/data/easy-rsa/easyrsa sign client ${NAME} <<EOF
yes
EOF
[ -e /data/easy-rsa/pki/issued/${NAME}.crt ] && $COLOR "\n${NAME}用户证书创建成功\n"$END || $COLOR "\n>创建失败！！！\n"$END
#在/etc/openvpn/client/目录中创建用户的对应的目录
mkdir /etc/openvpn/client/${NAME}
##拷贝CA文件和用户自己对应的证书文件
cp /etc/openvpn/certs/{ca.crt,ta.key} /data/easy-rsa/pki/issued/${NAME}.crt /data/easy-rsa/pki/private/${NAME}.key /etc/openvpn/client/${NAME}
#生成用户客户端配置文件
cat >/etc/openvpn/client/${NAME}/client.ovpn <<EOF
client
dev tun
proto tcp
remote ${OPENVPN_SERVER} 1194
resolv-retry infinite
nobind
#persist-key
#persist-tun
ca ca.crt
cert ${NAME}.crt
key ${NAME}.key
remote-cert-tls server
tls-auth ta.key 1
cipher AES-256-CBC
verb 3
compress lz4-v2
EOF
}
#实现访问VPN服务器的内网主机
set_ip_forward(){
#在服务器开启ip_forward转发功能
echo net.ipv4.ip_forward = 1 >> /etc/sysctl.conf
sysctl -p
#配置实现内网服务器回应外网的请求的路由
##在内网每个主机上添加路由,由于使用的是阿里云服务器测试，不支持修改路由，所以下面命令不可用，在生产
中最好使用下面添加路由
#route add -net ${NET} gw ${GATEWAY}
##在OpenVPN服务器配置iptables规则实现
iptables -t nat -A POSTROUTING -s ${NET} ! -d ${NET} -j SNAT --to ${GATEWAY} && $COLOR "\n路由规则>创建完毕，可以访问内网中的主机\n"$END || $COLOR "\n>路由创建失败！！！\n"$END
}
#离职员工证书注销
revoke_cert(){
cd /data/easy-rsa/
read -p "请输入离职用户的姓名拼音(如:yanli): " NAME
/data/easy-rsa/easyrsa revoke ${NAME} <<EOF
yes
EOF
#每次吊销证书后都需要更新证书吊销列表文件,并且需要重启OpenVPN服务
/data/easy-rsa/easyrsa gen-crl
[ -e /data/easy-rsa/pki/crl.pem ] && $COLOR "\n吊销列表创建成功\n"$END || $COLOR "\n>创建失败！！！
\n"$END
cat >> /etc/openvpn/server.conf <<EOF
crl-verify /data/easy-rsa/pki/crl.pem
EOF
systemctl restart openvpn@server.service
}
while :;do
${COLOR}"1.搭建OpenVPN服务 "${END}
${COLOR}"2.创建用户客户端信息 "${END}
${COLOR}"3.吊销离职用户的信息 "${END}
${COLOR}"4.退出 "${END}
read -p "$(${COLOR}'请输入你的选项:'${END})" option
case $option in
1)
install
setfiles
set_CA
server_cert
server_conf_start
set_ip_forward
;;
2)
client_user_cert
;;
3)
revoke_cert
;;
4)
break
;;
*)
${COLOR}"输入错误！"${END}
esac
done5、脚本运行结果
[root@openvpn-server ~]$ sh openvpn.sh
#目录信息
[root@openvpn-server easy-rsa]$ tree /etc/openvpn/
/etc/openvpn/
├── certs
│ ├── ca.crt
│ ├── dh.pem
│ ├── server.crt
│ ├── server.key
│ └── ta.key
├── client
│ └── yanlinux
│ ├── ca.crt
│ ├── client.ovpn
│ ├── ta.key
│ ├── yanlinux.crt
│ └── yanlinux.key
├── server
└── server.conf
4 directories, 11 files
[root@openvpn-server easy-rsa]$ tree /data/easy-rsa/
/data/easy-rsa/
├── easyrsa
├── openssl-easyrsa.cnf
├── pki
│ ├── ca.crt
│ ├── certs_by_serial
│ │ └── BACE212EFB3C76F5AFA234B4A622A1F8.pem
│ ├── crl.pem
│ ├── dh.pem
│ ├── index.txt
│ ├── index.txt.attr
│ ├── index.txt.attr.old
│ ├── index.txt.old
│ ├── issued
│ │ ├── server.crt
│ │ └── yanlinux.crt
│ ├── openssl-easyrsa.cnf
│ ├── private
│ │ ├── ca.key
│ │ ├── server.key
│ │ └── yanlinux.key
│ ├── renewed
│ │ ├── certs_by_serial
│ │ ├── private_by_serial
│ │ └── reqs_by_serial
│ ├── reqs
│ │ ├── server.req
│ │ └── yanlinux.req
│ ├── revoked
│ │ ├── certs_by_serial
│ │ │ ├── 3E5EBDD04A5CB95EFC0C5C8DB8D3C567.crt
│ │ │ └── ED29EE34D64D23A8D11E06533324A27E.crt
│ │ ├── private_by_serial
│ │ │ ├── 3E5EBDD04A5CB95EFC0C5C8DB8D3C567.key
│ │ │ └── ED29EE34D64D23A8D11E06533324A27E.key
│ │ └── reqs_by_serial
│ │ ├── 3E5EBDD04A5CB95EFC0C5C8DB8D3C567.req
│ │ └── ED29EE34D64D23A8D11E06533324A27E.req
│ ├── safessl-easyrsa.cnf
│ ├── serial
│ └── serial.old
├── vars
└── x509-types
├── ca
├── client
├── code-signing
├── COMMON
├── email
├── kdc
├── server
└── serverClient
14 directories, 36 files6 Windows 配置部署 OpenVPN 客户端
6.1 Windows 安装 OpenVPN 客户端
官方客户端下载地址：
https://openvpn.net/community-downloads/
6.2 Windows 客户端配置准备
#在服务器打包证书并下载到windows客户端
[root@openvpn-server openvpn]$ zip /root/yanli.zip ./client/yanli/*
[root@openvpn-server openvpn]$ sz /root/yanli.zip
##将其放到C:\Program Files\OpenVPN\config文件夹下6.3 Windows 客户端建立OpenVPN连接
点击桌面的OpenVPN GUI软件，然后点击连接，成功连接到服务端
6.4 Windows 客户端验证通信
6.4.1 在Windows 客户端测试访问OpenVPN后端服务器
后端服务器显示是来自于OpenVPN服务器的连接
6.4.2 验证OpenVPN服务器连接状态
[root@openvpn-server easy-rsa]$ ss -nt
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
ESTAB 0 0 172.30.0.1:1194 180.111.117.59:55052
ESTAB 0 0 172.30.0.1:38686 100.100.30.26:80
ESTAB 0 36 172.30.0.1:22 180.111.117.59:50503 6.4.3 验证 Windows 客户端的 IP地址
6.4.4 Windows客户端验证OpenVPN内网中其他机器的网络是否通畅
