一. 简述DDL,DML,DCL,DQL，并且说明mysql各个关键字查询时候的先后顺序
DDL（DataDefinitionLanguage）：数据定义语言，用来定义数据库对象：库、表、列等；
DML（DataManipulationLanguage）：数据操作语言，用来定义数据库记录（数据）；
DQL（DataQueryLanguage）：数据查询语言，用来查询记录（数据）；
DCL（DataControlLanguage）：数据控制语言，用来定义访问权限和安全级别。
mysql各个关键字查询时候的先后顺序
1.查询中用到的关键词主要包含六个，并且他们的顺序依次为
select--from--where--group by--having--order by
其中select和from是必须的，其他关键词是可选的，这六个关键词的执行顺序
与sql语句的书写顺序并不是一样的，而是按照下面的顺序来执行
from--where--group by--having--select--order by,
from:需要从哪个数据表检索数据
where:过滤表中数据的条件
group by:如何将上面过滤出的数据分组
having:对上面已经分组的数据进行过滤的条件
select:查看结果集中的哪个列，或列的计算结果
order by :按照什么样的顺序来查看返回的数据
2.from后面的表关联，是自右向左解析的
而where条件的解析顺序是自下而上的。
也就是说，在写SQL文的时候，尽量把数据量大的表放在最右边来进行关联，
而把能筛选出大量数据的条件放在where语句的最下面。
二. 自行设计10个sql查询语句，需要用到关键字[GROUP BY/HAVING/ORDER BY/LIMIT]，至少同时用到两个。
-- 创建数据库
CREATE DATABASE student_schedule;
-- 切换到student_schedule数据库
USE student_schedule;
-- 创建students表
CREATE TABLE students (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255),
age INTEGER
);
-- 创建courses表
CREATE TABLE courses (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255)
);
-- 创建schedule表
CREATE TABLE schedule (
student_id INTEGER REFERENCES students(id) ON DELETE CASCADE,
course_id INTEGER REFERENCES courses(id) ON DELETE CASCADE
);
插入数据：
-- 插入学生数据
INSERT INTO students (name, age)
VALUES
('John', 15),
('Mary', 16),
('Bob', 16),
('Amy', 15),
('Max', 17),
('Kate', 17),
('Jill', 14),
('Alex', 15),
('Tim', 16),
('Sara', 17);
-- 插入课程数据
INSERT INTO courses (name)
VALUES
('Math'),
('History'),
('Biology'),
('English'),
('Chemistry'),
('Physics'),
('Computer Science'),
('Geography'),
('Art'),
('Music');
-- 插入课表数据
INSERT INTO schedule (student_id, course_id)
VALUES
(1, 1),
(1, 2),
(1, 3),
(2, 4),
(2, 5),
(2, 6),
(3, 7),
(3, 8),
(3, 9),
(4, 10),
(4, 1),
(4, 2),
(5, 3),
(5, 4),
(5, 5),
(6, 6),
(6, 7),
(6, 8),
(7, 9),
(7, 10),
(7, 1),
(8, 2),
(8, 3),
(8, 4),
(9, 5),
(9, 6),
(9, 7),
(10, 8),
(10, 9),
(10, 10);
整合sql文件
CREATE DATABASE student_schedule;
USE student_schedule;
CREATE TABLE students (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255),
age INTEGER
);
CREATE TABLE courses (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255)
);
CREATE TABLE schedule (
student_id INTEGER REFERENCES students(id) ON DELETE CASCADE,
course_id INTEGER REFERENCES courses(id) ON DELETE CASCADE
);
INSERT INTO students (name, age)
VALUES
('John', 15),
('Mary', 16),
('Bob', 16),
('Amy', 15),
('Max', 17),
('Kate', 17),
('Jill', 14),
('Alex', 15),
('Tim', 16),
('Sara', 17);
INSERT INTO courses (name)
VALUES
('Math'),
('History'),
('Biology'),
('English'),
('Chemistry'),
('Physics'),
('Computer Science'),
('Geography'),
('Art'),
('Music');
INSERT INTO schedule (student_id, course_id)
VALUES
(1, 1),
(1, 2),
(1, 3),
(2, 4),
(2, 5),
(2, 6),
(3, 7),
(3, 8),
(3, 9),
(4, 10),
(4, 1),
(4, 2),
(5, 3),
(5, 4),
(5, 5),
(6, 6),
(6, 7),
(6, 8),
(7, 9),
(7, 10),
(7, 1),
(8, 2),
(8, 3),
(8, 4),
(9, 5),
(9, 6),
(9, 7),
(10, 8),
(10, 9),
(10, 10);
SQL 查询语句：
- 查询所有学生的课程：
SELECT s.name AS 'Student Name', c.name AS 'Course Name'
FROM students s
JOIN schedule sc ON s.id = sc.student_id
JOIN courses c ON c.id = sc.course_id;
- 查询所有学生15岁以下的课程：
SELECT s.name AS 'Student Name', c.name AS 'Course Name'
FROM students s
JOIN schedule sc ON s.id = sc.student_id
JOIN courses c ON c.id = sc.course_id
WHERE s.age < 16;
- 统计不同年龄的学生选课数量：
SELECT s.age, COUNT(*) AS 'Number of Courses'
FROM students s
JOIN schedule sc ON s.id = sc.student_id
GROUP BY s.age;
- 统计学生选课数量大于3门的学生：
SELECT s.name AS 'Student Name', COUNT(*) AS 'Number of Courses'
FROM students s
JOIN schedule sc ON s.id = sc.student_id
GROUP BY s.name
HAVING COUNT(*) > 3;
- 查询每个学生选修的课程数量：
SELECT s.name AS 'Student Name', COUNT(*) AS 'Number of Courses'
FROM students s
JOIN schedule sc ON s.id = sc.student_id
GROUP BY s.name;
- 查询每门课程有几个学生选修：
SELECT c.name AS 'Course Name', COUNT(*) AS 'Number of Students'
FROM courses c
JOIN schedule sc ON c.id = sc.course_id
GROUP BY c.name;
- 查询15岁以下的学生选课数量：
SELECT s.age, COUNT(*) AS 'Number of Courses'
FROM students s
JOIN schedule sc ON s.id = sc.student_id
WHERE s.age < 16
GROUP BY s.age;
- 查询学生选修的课程数量排名前三的学生：
SELECT s.name AS 'Student Name', COUNT(*) AS 'Number of Courses'
FROM students s
JOIN schedule sc ON s.id = sc.student_id
GROUP BY s.name
ORDER BY COUNT(*) DESC
LIMIT 3;
- 查询15岁以下学生选修数量排名前三的课程：
SELECT c.name AS 'Course Name', COUNT(*) AS 'Number of Students'
FROM courses c
JOIN schedule sc ON c.id = sc.course_id
JOIN students s ON s.id = sc.student_id
WHERE s.age < 16
GROUP BY c.name
ORDER BY COUNT(*) DESC
LIMIT 3;
- 查询每门课程有几个16岁的学生选修：
SELECT c.name AS 'Course Name', COUNT(*) AS 'Number of Students'
FROM courses c
JOIN schedule sc ON c.id = sc.course_id
JOIN students s ON s.id = sc.student_id
WHERE s.age = 16
GROUP BY c.name;
三. xtrabackup备份和还原数据库练习
1.xtrabackup 安装
1.1在清华源下载镜像
[root@Rocky8 ~]#wget https://mirrors.tuna.tsinghua.edu.cn/percona/pdpxc-8.0.28/yum/release/8.0/RPMS/x86_64/percona-xtrabackup-80-8.0.30-23.1.el8.x86_64.rpm
1.2安装
[root@Rocky8 ~]#yum -y install percona-xtrabackup-80-8.0.30-23.1.el8.x86_64.rpm
2.xtrabackup 用法
xtrabackup工具备份和还原，需要三步实现
- 备份：对数据库做完全或增量备份
- 预准备： 还原前，先对备份的数据，整理至一个临时目录
- 还原：将整理好的数据，复制回数据库目录中
2.1备份
innobackupex [option] BACKUP-ROOT-DIR
选项说明：
–user：#该选项表示备份账号
–password：#该选项表示备份的密码
–host：#该选项表示备份数据库的地址
–databases：#该选项接受的参数为数据库名，如果要指定多个数据库，彼此间需要以空格隔开； 如：“xtra_test dba_test”，同时，在指定某数据库时，也可以只指定其中的某张表。
如：“mydatabase.mytable”。该选项对innodb引擎表无效，还是会备份所有innodb表
–defaults-file：#该选项指定从哪个文件读取MySQL配置，必须放在命令行第一个选项位置
–incremental：#该选项表示创建一个增量备份，需要指定–incremental-basedir
–incremental-basedir：#该选项指定为前一次全备份或增量备份的目录，与–incremental同时使用
–incremental-dir：#该选项表示还原时增量备份的目录
–include=name：#指定表名，格式：databasename.tablename
2.2Prepare预准备：
innobackupex --apply-log [option] BACKUP-DIR
选项说明：
–apply-log：#一般情况下,在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚 未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。此选项作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态
–use-memory：#和–apply-log选项一起使用，当prepare 备份时，做crash recovery分配的内存大 小，单位字节，也可1MB,1M,1G,1GB等，推荐1G
–export：#表示开启可导出单独的表之后再导入其他Mysql中
–redo-only：#此选项在prepare base full backup，往其中合并增量备份时候使用，但不包括对最后 一个增量备份的合并
2.3还原：
innobackupex --copy-back [选项] BACKUP-DIR
innobackupex --move-back [选项] [–defaults-group=GROUP-NAME] BACKUP-DIR
选项说明：
–copy-back：#做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir
–move-back：#这个选项与–copy-back相似，唯一的区别是它不拷贝文件，而是移动文件到目的地。这 个选项移除backup文件，用时候必须小心。使用场景：没有足够的磁盘空间同事保留数据文件和Backup副本
–force-non-empty-directories #指定该参数时候，使得innobackupex --copy-back或–move back选项转移文件到非空目录，已存在的文件不会被覆盖。如果–copy-back和–move-back文件需要从备 份目录拷贝一个在datadir已经存在的文件，会报错失败
还原注意事项：
- datadir 目录必须为空。除非指定innobackupex --force-non-empty-directorires选项指定，否则–
copy-back选项不会覆盖 - 在restore之前,必须shutdown MySQL实例，不能将一个运行中的实例restore到datadir目录中
- 由于文件属性会被保留，大部分情况下需要在启动实例之前将文件的属主改为mysql，这些文件将
属于创建备份的用户, 执行chown -R mysql:mysql /data/mysql,以上需要在用户调用innobackupex之前完成
3.利用xtrabackup8.0 完全，增量备份及还原MySQL8.0
3.1备份过程
1由于mysql8.0的特性，修改mysql基本设置
[root@Rocky8 ~]#mysql
mysql> use mysql;
mysql> select user,host from user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)
mysql> update user set host='%' where user='root' ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> grant all privileges on *.* to 'root'@'%' ;
ERROR 1410 (42000): You are not allowed to create a user with GRANT
mysql> grant all privileges on *.* to 'root'@'%' ;
Query OK, 0 rows affected (0.00 sec)
2.完全备份
[root@Rocky8 ~]#mkdir /backup/
[root@Rocky8 ~]#xtrabackup -uroot -p123456 --backup --target-dir=/backup/base
3.第一次修改数据
mysql> insert into teachers values ('5','Diao DeYi','45','M'),('6','Xiao Wen','25','F');
4.第一次增量备份
[root@Rocky8 ~]#xtrabackup -uroot -p123456 --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
5.第二次修改数据
mysql> insert into courses values ('8','Jiuyang Zhenjing');
6.第二次增量备份
[root@Rocky8 ~]#xtrabackup -uroot -p123456 --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1
7.保留备份文件属性，并备份到目标主机
[root@Rocky8 ~]#scp -r /backup/* 192.168.217.134:/backup/
3.2还原过程
1.预准备完成备份，此选项--apply-log-only 阻止回滚未完成的事务
[root@Rocky8 ~]#systemctl stop mysqld.service
[root@Rocky8 ~]#xtrabackup --prepare --apply-log-only --target-dir=/backup/base
2.合并第1次增量备份到完全备份
[root@Rocky8 ~]#xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1
3.合并第2次增量备份到完全备份(最后一次还原不需要加选项--apply-log-only)
[root@Rocky8 ~]#xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2
4.复制到数据库目录，注意数据库目录必须为空，MySQL服务不能启动
[root@Rocky8 ~]#rm -rf /var/lib/mysql/*
[root@Rocky8 ~]#ls /var/lib/mysql
[root@Rocky8 ~]#xtrabackup --copy-back --target-dir=/backup/base
5.还原属性
[root@Rocky8 ~]#ls /var/lib/mysql -l
total 85024
-rw-r----- 1 root root 156 Dec 22 22:10 binlog.000004
-rw-r----- 1 root root 14 Dec 22 22:10 binlog.index
drwxr-x--- 2 root root 132 Dec 22 22:10 hellodb
-rw-r----- 1 root root 6083 Dec 22 22:10 ib_buffer_pool
-rw-r----- 1 root root 12582912 Dec 22 22:10 ibdata1
-rw-r----- 1 root root 12582912 Dec 22 22:10 ibtmp1
drwxr-x--- 2 root root 6 Dec 22 22:10 '#innodb_redo'
drwxr-x--- 2 root root 143 Dec 22 22:10 mysql
-rw-r----- 1 root root 28311552 Dec 22 22:10 mysql.ibd
drwxr-x--- 2 root root 8192 Dec 22 22:10 performance_schema
drwxr-x--- 2 root root 28 Dec 22 22:10 sys
-rw-r----- 1 root root 16777216 Dec 22 22:10 undo_001
-rw-r----- 1 root root 16777216 Dec 22 22:10 undo_002
-rw-r----- 1 root root 507 Dec 22 22:10 xtrabackup_info
[root@Rocky8 ~]#chown -R mysql:mysql /var/lib/mysql
[root@Rocky8 ~]#ls /var/lib/mysql -l
total 85024
-rw-r----- 1 mysql mysql 156 Dec 22 22:10 binlog.000004
-rw-r----- 1 mysql mysql 14 Dec 22 22:10 binlog.index
drwxr-x--- 2 mysql mysql 132 Dec 22 22:10 hellodb
-rw-r----- 1 mysql mysql 6083 Dec 22 22:10 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Dec 22 22:10 ibdata1
-rw-r----- 1 mysql mysql 12582912 Dec 22 22:10 ibtmp1
drwxr-x--- 2 mysql mysql 6 Dec 22 22:10 '#innodb_redo'
drwxr-x--- 2 mysql mysql 143 Dec 22 22:10 mysql
-rw-r----- 1 mysql mysql 28311552 Dec 22 22:10 mysql.ibd
drwxr-x--- 2 mysql mysql 8192 Dec 22 22:10 performance_schema
drwxr-x--- 2 mysql mysql 28 Dec 22 22:10 sys
-rw-r----- 1 mysql mysql 16777216 Dec 22 22:10 undo_001
-rw-r----- 1 mysql mysql 16777216 Dec 22 22:10 undo_002
-rw-r----- 1 mysql mysql 507 Dec 22 22:10 xtrabackup_info
3.3验证过程
[root@Rocky8 ~]#mysql -uroot -p123456
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主从复制，主主复制和半同步复制
1.mysql主从复制
实验环境
192.168.217.135 mysql8.0 主服务器
192.168.217.134 mysql8.0 从服务器
1.1主节点配置
(1)修改配置文件
vim /etc/my.cnf
[mysqld]
skip-name-resolve=1 #禁止解析主机名，减少连接创建时间
log_bin #开启二进制日志
server-id=127 #设置全局唯一ID号
# server-id的取值范围
1 to 4294967295 (>= MariaDB 10.2.2)，默认值为1，MySQL8.0默认值为1
0 to 4294967295 (<= MariaDB 10.2.1)，默认值为0，如果从节点为0，所有master都将拒绝此
log-basename=master #可选项，设置datadir中日志名称，确保不依赖主机名
(2)查看二进制日志的文件和位置
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| mysql-lbin.000003 | 156 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
(3)创建有复制权限的用户账号
mysql> create user repluser@'192.168.217.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to repluser@'192.168.217.%';
Query OK, 0 rows affected (0.00 sec)
1.2从节点配置
(1)设置ID号和只读模式
[root@mysql-slave ~]#vim /etc/my.cnf
[mysqld]
server_id=134
read-only
(2)使用有复制权限的账号连接主服务器，
CHANGE MASTER TO
MASTER_HOST='192.168.217.135',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-lbin.000003', MASTER_LOG_POS=156;
(3)开启线程
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
1.3查看状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.217.135
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-lbin.000003
Read_Master_Log_Pos: 683
Relay_Log_File: mysql-slave-relay-bin.000002
Relay_Log_Pos: 852
Relay_Master_Log_File: mysql-lbin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
级联复制
中间从节点配置
[root@mysql-slave ~]#vim /etc/my.cnf
[mysqld]
server_id=134
log_bin
log_slave_updates #级联复制中间节点MySQL8.0为默认值，其他版本需要手动添加
read-only
2.mysql主主复制
实验环境
192.168.217.135 mysql8.0 主服务器1
192.168.217.134 mysql8.0 主服务器2
2.1主服务器1配置
(1)启用二进制日志，设置全局唯一ID号，设置自动增长id为奇数
[root@mysql-master1 ~]#vim /etc/my.cnf
[mysqld]
log_bin=/log/mysql-lbin
server-id=135
skip-name-resolve=1
auto_increment_offset=1 #开始点
auto_increment_increment=2 #增长幅度
[root@mysql-master1 ~]#systemctl restart mysqld.service
(2)查看二进制日志的文件和位置
mysql> show master logs;
+-------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+-------------------+-----------+-----------+
| mysql-lbin.000001 | 179 | No |
| mysql-lbin.000002 | 156 | No |
| mysql-lbin.000003 | 894 | No |
| mysql-lbin.000004 | 156 | No |
+-------------------+-----------+-----------+
4 rows in set (0.00 sec)
(3)创建有复制权限的用户账号
mysql> create user repluser@'192.168.217.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to repluser@'192.168.217.%';
Query OK, 0 rows affected (0.00 sec)
(4)使用有复制权限的账号连接主服务器1
CHANGE MASTER TO
MASTER_HOST='192.168.217.134',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-lbin.000001', MASTER_LOG_POS=156;
(5)开启线程
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
2.2主服务器2配置
(1)启用二进制日志，设置全局唯一ID号，设置自动增长id为奇数
[root@mysql-master2 ~]#vim /etc/my.cnf
[mysqld]
log_bin=/log/mysql-lbin
server-id=134
skip-name-resolve=1
auto_increment_offset=2 #开始点
auto_increment_increment=2 #增长幅度
(2)使用有复制权限的账号连接主服务器1
CHANGE MASTER TO
MASTER_HOST='192.168.217.135',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-lbin.000004', MASTER_LOG_POS=156;
(3)创建有复制权限的用户账号
mysql> create user repluser@'192.168.217.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to repluser@'192.168.217.%';
Query OK, 0 rows affected (0.00 sec)
(4)查看二进制日志的文件和位置
mysql> show master logs;
+-------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+-------------------+-----------+-----------+
| mysql-lbin.000001 | 156 | No |
+-------------------+-----------+-----------+
1 row in set (0.00 sec)
(5)开启线程
mysql> reset slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
主主复制：两个节点，都可以更新数据，并且互为主从
容易产生的问题：数据不一致；因此慎用
3.mysql半同步复制
同步到一个从节点或者超过延迟时间就返回成功，加快事务的返回--->用于主从(主备架构)
- 异步复制的优点：返回结果快，写入到主库了后就返回写入成功了，SQL query set
- 异步复制的缺陷：如果在同步的过程中，master dump thread挂了，则无法同步，从节点的数据也不是最新的；而且在slave库还需要完成SQL thread写入到磁盘，这段如果没完成也是导致数据不是最新的情况
半同步机制：加入了检查从库是否数据落盘的插件，只有主库---从库，从库的事务commit后，写入磁盘了，主库这边才返回写入成功；或者是超过了同步延时时间，也会返回写成功
半同步复制默认设置
rpl_semi_sync_master_wait_point=after_commit
缺点1： 幻读
当用户提交一个事务，该事务已经写入redo日志和binlog日志，但该事务还没写入从库，此时处在waiting
slave dump处，此时另一个用户可以读取到这条数据，而他自己却不能；
缺点2：数据丢失
一个提交的事务在waiting slave dump处crash后，主库将比从库多一条数据
增强半同步复制(MySQL5.7新增功能)
rpl_semi_rsync_master_wait_point=after_sync
优点
改善1：解决幻读
当用户发起一个事务，该事务先写入二进制后，再向从库进行同步，由于还没有完成提交，此时其他用户无法读取到该数据，解决了幻读
改善2：解决数据丢失
一个事务在waiting slave dump处crash掉后，可以通过观察从库上是否存在主库的last gtid值，如果
存在，这条数据正常恢复，如果不存在则删除主库的那条多余的GTID值，然后恢复，保证了数据的完整性
实验环境
192.168.217.135 mysql8.0 主服务器
192.168.217.134 mysql8.0 从服务器1
192.168.217.134 mysql8.0 从服务器2
3.1主节点配置：
(1)查看是否有插件
[root@mysql-master ~]#rpm -ql mysql-server |grep semisync
/usr/lib64/mysql/plugin/semisync_master.so
/usr/lib64/mysql/plugin/semisync_replica.so
/usr/lib64/mysql/plugin/semisync_slave.so
/usr/lib64/mysql/plugin/semisync_source.so
(2)安装插件
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected, 1 warning (0.00 sec)
(3)文件配置
[root@mysql-master ~]#vim /etc/my.cnf
[mysqld]
log_bin=/log/mysql/master-bin
server-id=135
skip-name-resolve=1
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_master_timeout=3000 #设置3s内无法同步，也将返回成功信息给客户端
mysql> show variables like '%rpl_semi_sync_master_enabled%';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| rpl_semi_sync_master_enabled | ON |
+------------------------------+-------+
1 row in set (0.00 sec)
3.2从节点配置
(1)安装插件
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show variables like '%rpl_semi_sync_slave_enabled%';
(2)从服务器配置文件
[root@mysql-slave ~]#vim /etc/my.cnf.d/mysql-server.cnf
server-id=134
log_bin=/log/mysql/slave-bin
rpl_semi_sync_slave_enabled=on
skip-name-resolve=1
(3)开启线程
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
3.3验证
从节点mysql服务全部关闭，观察主服务器插入数据是否能提交成功
mysql> insert classes(Class,NumOfStu) values("Gai Bang",190);
Query OK, 1 row affected (3.00 sec)
由于设置了超时时间，三秒内，由于模式为半同步复制，且从服务器全部关闭服务，没收到ack，所以无法提交事务，三秒后，到达超时时间，切换为异步模式，提交成功
五. 用mycat实现mysql的读写分离
实验环境
192.168.217.133 mysql8.0-mycat
192.168.217.135 mysql8.0 主服务器
192.168.217.134 mysql8.0 从服务器
1.安装mycat并配置mycat
(1)解压mycat
[root@mycat-server ~]#mkdir /apps/
[root@mycat-server ~]#tar xvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /apps/
(2)让系统执行mycat指令的时候，直接去调用环境变量的PATH
[root@mycat-server ~]#vim /etc/profile.d/mycat.sh
##让环境变量生效
[root@mycat-server ~]#source /etc/profile.d/mycat.sh
(3)安装java编译环境
[root@mycat-server ~]#yum -y install java
[root@mycat-server ~]#java -version
openjdk version "1.8.0_312"
OpenJDK Runtime Environment (build 1.8.0_312-b07)
OpenJDK 64-Bit Server VM (build 25.312-b07, mixed mode)
(4)开启mycat
[root@mycat-server ~]#mycat start
Starting Mycat-server...
[root@mycat-server ~]#ss -ntl | grep 8066
LISTEN 0 100 *:8066 *:*
[root@mycat-server ~]#tail -f /apps/mycat/logs/wrapper.log
STATUS | wrapper | 2022/12/25 16:21:57 | --> Wrapper Started as Daemon
STATUS | wrapper | 2022/12/25 16:21:57 | Launching a JVM...
INFO | jvm 1 | 2022/12/25 16:21:57 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2022/12/25 16:21:57 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2022/12/25 16:21:57 |
INFO | jvm 1 | 2022/12/25 16:21:59 | MyCAT Server startup successfully. see logs in logs/mycat.log
(5)在mycat 服务器上修改server.xml文件配置Mycat的连接信息
[root@mycat-server ~]#vim /apps/mycat/conf/server.xml
...省略...
#修改下面行的8066改为3306复制到到独立非注释行
<property name="serverPort">3306</property>
<property name="handlelDistributedTransactions">0</property> #将上面行放在此行前面
#或者删除注释,并修改下面行的8066改为3306
<property name="serverPort">3306</property>
<property name="managerPort">9066</property>
<property name="idleTimeout">300000</property>
<property name="authTimeout">15000</property>
<property name="bindIp">0.0.0.0</property>
<property name="dataNodeIdleCheckPeriod">300000</property> #5 * 60 * 1000L; //连
接空闲检查 删除#后面此部分
<property name="frontWriteQueueSize">4096</property> <property
name="processors">32</property> #--> 删除#后面此部分
.....
<user name="root"> #连接Mycat的用户名
<property name="password">123456</property> #连接Mycat的密码
<property name="schemas">TESTDB</property> #数据库名要和schema.xml相
对应
</user>
</mycat:server>
(6)修改schema.xml实现读写分离策略
[root@mycat-server ~]#vim /apps/mycat/conf/schema.xml
<!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***" /> #其中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="192.168.217.135:3306" user="root" password="123456">***
*** <readHost host="host2" url="192.168.217.134:3306" user="root" password="123456"/>***
</writeHost>
</dataHost>
</mycat:schema>
#以上***部分表示原配置文件中需要修改的内容
2.在mysql主服务器上创建root账号并授权
注意：要保证能使用root/123456权限成功登录主从服务器上面的mysql数据库。同时，
也一定要授权mycat机器能使用root/123456权限成功登录这两台机器的mysql数据库！！这很重要，否
则会导致登录mycat后，对库和表操作失败！
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
update user set host='%' where user='root' ;
grant all privileges on *.* to 'root'@'%' ;#重复两次
3.在服务器上测试连接mysql
#此时已完成Mycat读写分离，客户端主机访问Mycat主机
[root@Rocky8 ~]#mysql -uroot -p123456 -h192.168.217.133
#进行查询，读操作实际访问从节点
mysql> use TESTDB;
Database changed
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 134 |
+-------------+
1 row in set (0.00 sec)
#修改数据，读操作实际访问主节点，可以在/var/lib/mysql/master.log中查看到相应记录
#需要先开启通用日志，持续生效可修改配置文件
mysql> set global general_log=1;
mysql> show variables like 'general_log_file';
+------------------+---------------------------------+
| Variable_name | Value |
+------------------+---------------------------------+
| general_log_file | /var/lib/mysql/mysql-master.log |
+------------------+---------------------------------+
1 row in set (0.00 sec)
#从节点无法代替主节点
六.实现openvpn部署
实验环境
192.168.217.135 openvpn-server: eth0:192.168.217.135/24 eth1:192.168.36.5/24
192.168.217.134 公网主机
192.168.36.3 内网主机
1.安装openvpn软件包并配置环境
[root@openvpn-server ~]#yum -y install openvpn
[root@openvpn-server ~]#yum -y install easy-rsa #用来生成证书相关文件和颁布证书
#生成服务器端配置文件
[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 -r /usr/share/doc/easy-rsa/vars.example /etc/openvpn/easy-rsa-server/3/vars
#修改给CA和服务器颁发证书的有效期，适当加长
[root@openvpn-server ~]#vim /etc/openvpn/easy-rsa-server/3/vars
set_var EASYRSA_CA_EXPIRE 365000#CA证书有效期，默认十年，建议改长
set_var EASYRSA_CERT_EXPIRE 8250#服务器证书有效期，默认825天，建议改长
[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
2.准备证书相关文件
(1)脚本easyrsa帮助
[root@openvpn-server ~]#cd /etc/openvpn/easy-rsa-server/3/
[root@openvpn-server 3]#./easyrsa
Note: using Easy-RSA configuration from: /etc/openvpn/easy-rsa-server/3.0.8/vars
Easy-RSA 3 usage and overview
USAGE: easyrsa [options] COMMAND [command-options]
A list of commands is shown below. To get detailed usage and help for a
command, run:
./easyrsa help COMMAND
For a listing of options that can be supplied before the command, use:
./easyrsa help options
Here is the list of commands available with a short syntax reminder. Use the
'help' command above to get full usage details.
init-pki
build-ca [ cmd-opts ]
gen-dh
gen-req <filename_base> [ cmd-opts ]
sign-req <type> <filename_base>
build-client-full <filename_base> [ cmd-opts ]
build-server-full <filename_base> [ cmd-opts ]
revoke <filename_base> [cmd-opts]
renew <filename_base> [cmd-opts]
build-serverClient-full <filename_base> [ cmd-opts ]
gen-crl
update-db
show-req <filename_base> [ cmd-opts ]
show-cert <filename_base> [ cmd-opts ]
show-ca [ cmd-opts ]
import-req <request_file_path> <short_basename>
export-p7 <filename_base> [ cmd-opts ]
export-p8 <filename_base> [ cmd-opts ]
export-p12 <filename_base> [ cmd-opts ]
set-rsa-pass <filename_base> [ cmd-opts ]
set-ec-pass <filename_base> [ cmd-opts ]
upgrade <type>
DIRECTORY STATUS (commands would take effect on these locations)
EASYRSA: /etc/openvpn/easy-rsa-server/3.0.8
PKI: /etc/openvpn/easy-rsa-server/3/pki
(2)初始化PKI生成PKI相关目录和文件
[root@openvpn-server 3]#./easyrsa init-pki
Note: using Easy-RSA configuration from: /etc/openvpn/easy-rsa-server/3.0.8/vars
init-pki complete; you may now create a CA or requests.
Your newly created PKI dir is: /etc/openvpn/easy-rsa-server/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
(3)创建CA机构环境
[root@openvpn-server 3]#./easyrsa build-ca nopass #给CA颁发证书，nopass表示不给私钥增加密码
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 RSA private key, 2048 bit long modulus (2 primes)
........+++++
............+++++
e is 65537 (0x010001)
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Common Name (eg: your user, host, or server name) [Easy-RSA CA]:zxk
CA creation complete and you may now import and sign cert requests.
Your new CA certificate file for publishing is at:
/etc/openvpn/easy-rsa-server/3/pki/ca.crt
[root@openvpn-server 3]#tree
.
├── easyrsa
├── openssl-easyrsa.cnf
├── pki
│ ├── ca.crt #生成ca自签名的证书的文件
│ ├── 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
├── vars
└── x509-types
├── ca
├── client
├── code-signing
├── COMMON
├── email
├── kdc
├── server
└── serverClient
(4)创建并颁发服务器证书
#创建服务器证书申请文件，其中server是文件前缀
[root@openvpn-server 3]#./easyrsa gen-req server nopass
[root@openvpn-server 3]#tree
.
├── easyrsa
├── openssl-easyrsa.cnf
├── 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
├── vars
└── x509-types
├── ca
├── client
├── code-signing
├── COMMON
├── email
├── kdc
├── server
└── serverClient
#颁发服务器证书，第一个server表示证书类型，第二个server表示请求文件名的前缀
[root@openvpn-server 3]#./easyrsa sign server server
#验证结果
[root@openvpn-server 3]#tree ./pki
./pki
├── ca.crt
├── certs_by_serial
│ └── 851757FC89365FE76F16219B91F5A507.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
(5)创建Diffie-Hellman密钥
diffie-Hellman算法是Whitefield Diffie和Martin Hellman在1976年公布的一种秘钥交换算法，它是一种建立秘钥的方法，而不是加密方法，所以秘钥必须和其他一种加密算法结合使用。这种秘钥交换技术的目的在于使两个用户安全的交换一个秘钥一遍后面的报文加密。
[root@openvpn-server 3]#./easyrsa gen-dh
(6)配置客户端环境
[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]#./easyrsa init-pki
[root@openvpn-server 3]# ./easyrsa gen-req zzw nopass
[root@openvpn-server 3]# tree
.
├── easyrsa
├── openssl-easyrsa.cnf
├── pki
│ ├── openssl-easyrsa.cnf
│ ├── private
│ │ └── zzw.key
│ ├── reqs
│ │ └── zzw.req #客户端申请文件
│ └── safessl-easyrsa.cnf
└── x509-types
├── ca
├── client
├── code-signing
├── COMMON
├── email
├── kdc
├── server
└── serverClient
4 directories, 14 files
#将客户端证书申请文件复制到CA的工作目录下
[root@openvpn-server 3]# cd /etc/openvpn/easy-rsa-server/3
[root@openvpn-server 3]# ./easyrsa import-req /etc/openvpn/easy-rsa-client/3/pki/reqs/zzw.req zzw
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: zzw
You may now use this name to perform signing operations on this request.
#修改证书期限为180天
[root@openvpn-server 3]# vim vars
set_var EASYRSA_CERT_EXPIRE 180
#颁发给客户端证书
[root@openvpn-server 3]# ./easyrsa sign client zzw
[root@openvpn-server 3]#tree ./pki
./pki
├── ca.crt
├── certs_by_serial
│ ├── 483995252C9222798E129C5ACFD70429.pem #客户端证书
│ └── 851757FC89365FE76F16219B91F5A507.pem #服务器证书
├── dh.pem
├── index.txt
├── index.txt.attr
├── index.txt.attr.old
├── index.txt.old
├── issued
│ ├── server.crt
│ └── zzw.crt #客户端证书
├── openssl-easyrsa.cnf
├── private
│ ├── ca.key
│ └── server.key
├── renewed
│ ├── certs_by_serial
│ ├── private_by_serial
│ └── reqs_by_serial
├── reqs
│ ├── server.req
│ └── zzw.req
├── revoked
│ ├── certs_by_serial
│ ├── private_by_serial
│ └── reqs_by_serial
├── safessl-easyrsa.cnf
├── serial
└── serial.old
(7)将服务器对应证书私钥放入服务器对应目录
[root@openvpn-server 3]#mkdir /etc/openvpn/certs; 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/
[root@openvpn-server 3]#ll /etc/openvpn/certs/
total 20
-rw------- 1 root root 1172 Dec 26 22:28 ca.crt
-rw------- 1 root root 424 Dec 26 22:29 dh.pem
-rw------- 1 root root 4557 Dec 26 22:28 server.crt
-rw------- 1 root root 1704 Dec 26 22:28 server.key
(8)将客户端私钥证书放到对应服务器目录
[root@openvpn-server 3]#mkdir /etc/openvpn/client/zzw
[root@openvpn-server 3]#cp /etc/openvpn/easy-rsa-server/3/pki/issued/zzw.crt /etc/openvpn/client/
[root@openvpn-server 3]#cp /etc/openvpn/easy-rsa-server/3/pki/issued/zzw.crt /etc/openvpn/certs/
[root@openvpn-server 3]#cp /etc/openvpn/easy-rsa-server/3/pki/issued/zzw.crt /etc/openvpn/client/zzw/
[root@openvpn-server 3]#cp /etc/openvpn/easy-rsa-server/3/pki/ca.crt /etc/openvpn/client/zzw/
[root@openvpn-server 3]#cp /etc/openvpn/easy-rsa-client/3/pki/private/zzw.key /etc/openvpn/client/zzw/
[root@openvpn-server 3]#ll /etc/openvpn/client/zzw/
total 16
-rw------- 1 root root 1172 Dec 26 22:32 ca.crt
-rw------- 1 root root 4445 Dec 26 22:32 zzw.crt
-rw------- 1 root root 1704 Dec 26 22:32 zzw.key
3.准备openvpn服务器配置文件
[root@openvpn-server ~]#grep -Ev "^#|^$" /etc/openvpn/server.conf
;local a.b.c.d
port 1194
proto tcp
;dev tap
dev tun
;dev-node MyTap
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
;topology subnet
server 10.8.0.0 255.255.255.0
ifconfig-pool-persist ipp.txt
;server-bridge 10.8.0.4 255.255.255.0 10.8.0.50 10.8.0.100
;server-bridge
;push "route 192.168.10.0 255.255.255.0"
;push "route 192.168.36.0 255.255.255.0"
;push "route 192.168.20.0 255.255.255.0"
;client-config-dir ccd
;route 192.168.40.128 255.255.255.248
;client-config-dir ccd
;route 10.9.0.0 255.255.255.252
;learn-address ./script
;push "redirect-gateway def1 bypass-dhcp"
;push "dhcp-option DNS 208.67.222.222"
;push "dhcp-option DNS 208.67.220.220"
;client-to-client
;duplicate-cn
keepalive 10 120
cipher AES-256-CBC
;compress lz4-v2
;push "compress lz4-v2"
;max-clients 2048
;user openvpn
;group openvpn
persist-key
persist-tun
status /var/log/openvpn/openvpn-status.log
;log openvpn.log
;log-append /var/log/openvpn/openvpn.log
verb 3
;mute 20
explicit-exit-notify 1 #不支持tcp
#创建日志目录并修改所有者属性
[root@openvpn-server ~]# mkdir /var/log/openvpn
[root@openvpn-server ~]# chown openvpn. /var/log/openvpn/
4.启动VPN服务
#修改服务文件
[root@openvpn-server ~]# vim /usr/lib/systemd/system/openvpn@.service
[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
#启动服务
[root@openvpn-server ~]# systemctl enable --now openvpn@server
Created symlink /etc/systemd/system/multi-user.target.wants/openvpn@server.service → /usr/lib/systemd/system/openvpn@.service.
5.验证服务状态
[root@openvpn-server ~]# ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 32 0.0.0.0:1194 0.0.0.0:*
#查看ip
[root@openvpn-server ~]# ip a
...
tun0: <POINTOPOINT,MULTICAST,NOARP,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UNKNOWN group default qlen 100
link/none
inet 10.8.0.1 peer 10.8.0.2/32 scope global tun0
valid_lft forever preferred_lft forever
inet6 fe80::7772:e118:7c37:9e6b/64 scope link stable-privacy
valid_lft forever preferred_lft forever
#查看路由
[root@openvpn-server ~]#route -n
Kernel IP routing table
Destination Gateway Genmask Flags Metric Ref Use Iface
0.0.0.0 192.168.217.2 0.0.0.0 UG 100 0 0 eth0
10.8.0.0 10.8.0.2 255.255.255.0 UG 0 0 0 tun0
10.8.0.2 0.0.0.0 255.255.255.255 UH 0 0 0 tun0
192.168.36.0 0.0.0.0 255.255.255.0 U 101 0 0 eth1
192.168.122.0 0.0.0.0 255.255.255.0 U 0 0 0 virbr0
192.168.217.0 0.0.0.0 255.255.255.0 U 100 0 0 eth0
6.准备客户端配置文件
[root@openvpn-server ~]#vim /etc/openvpn/client/zzw/client.ovpn
client
dev tun
proto tcp
ca ca.crt
cert zzw.crt
key zzw.key # This file should be kept secret
remote 192.168.217.135 1194
cipher AES-256-CBC
compress lz4-v2
remote-cert-tls server
nobind
resolv-retry infinite
max-clients 2048
verb 3
mute 20
7.公网客户通过openvpn连接内网主机
(1)安装openvpn客户端
[root@public-client ~]#yum -y install openvpn
(2)从服务器端下载客户端的公钥私钥和CA证书还有客户端配置文件
[root@public-client ~]#scp 192.168.217.135:/etc/openvpn/client/zzw/* /etc/openvpn/
The authenticity of host '192.168.217.135 (192.168.217.135)' can't be established.
ECDSA key fingerprint is SHA256:b7JD+G//LzbCiFYlmYF6Ee/ta3tYLyZO1bXCngshcNc.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.217.135' (ECDSA) to the list of known hosts.
root@192.168.217.135's password:
ca.crt 100% 1172 1.5MB/s 00:00
client.ovpn 100% 242 254.5KB/s 00:00
zzw.crt 100% 4445 8.4MB/s 00:00
zzw.key 100% 1704 4.2MB/s 00:00
[root@public-client ~]#ll /etc/openvpn/
total 20
-rw------- 1 root root 1172 Dec 26 23:12 ca.crt
drwxr-x--- 2 root openvpn 6 Mar 18 2022 client
-rw-r--r-- 1 root root 242 Dec 26 23:12 client.ovpn
drwxr-x--- 2 root openvpn 6 Mar 18 2022 server
-rw------- 1 root root 4445 Dec 26 23:12 zzw.crt
-rw------- 1 root root 1704 Dec 26 23:12 zzw.key
(3)启动openvpn客户端
[root@public-client ~]#openvpn --daemon --cd /etc/openvpn --config client.ovpn --log-append /var/log/openvpn.log
--daemon #以daemon方式启动
--cd dir #配置文件目录
--config file #客户端配置文件
--log-append file #日志文件，若不存在会自动建立
(4)在openvpn服务器端开启ip转发功能
#开启ip转发功能
[root@openvpn-server ~]# echo net.ipv4.ip_forward=1 >>/etc/sysctl.conf
[root@openvpn-server ~]# sysctl -p
net.ipv4.ip_forward = 1
(5)在内网主机添加路由将目标地址为openvpn路由的下一跳都指向openvpn服务器
[root@private-client ~]#ip route add 10.8.0.0/24 via 192.168.36.5
(6)在公网客户端上访问内网主机
[root@public-client ~]#ping 192.168.36.3
PING 192.168.36.3 (192.168.36.3) 56(84) bytes of data.
64 bytes from 192.168.36.3: icmp_seq=1 ttl=128 time=0.551 ms
64 bytes from 192.168.36.3: icmp_seq=2 ttl=128 time=0.736 ms
64 bytes from 192.168.36.3: icmp_seq=3 ttl=128 time=0.892 ms
...
#ssh连接登录内网主机
[root@public-client ~]#ssh 192.168.36.3
The authenticity of host '192.168.36.3 (192.168.36.3)' can't be established.
ECDSA key fingerprint is SHA256:b7JD+G//LzbCiFYlmYF6Ee/ta3tYLyZO1bXCngshcNc.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.36.3' (ECDSA) to the list of known hosts.
root@192.168.36.3's password:
Activate the web console with: systemctl enable --now cockpit.socket
Last login: Mon Dec 26 23:20:26 2022 from 192.168.36.1
[root@private-client ~]#
实验成功，通过openvpn的配置实现了公网客户端和内网的互通（peer to site）
