目录
1.完成将server和client端的mysql配置默认字符集为utf8mb4
2.掌握如何获取SQL命令的帮助，基于帮助完成添加testdb数据库字符集utf8，排序集合utf8_bin
6.根据表扩展出几个语句，完成总结DDL DML 的用法，并配上示例。
7.导入hellodb库，总结DQL，alias，where，group by，order by，limit，having用法示例
8.基于hellodb库，总结子查询，联合查询，交叉连接，内连接，左连接，右连接，完全连接，自连接。
10-11总结mysql事件管理，用户管理，权限管理，搭建wordpress站点
14.总结mysql索引作用，同时总结哪些查询不会使用到索引
19.总结mysql备份类型，并基于mysqldump，xtrabackup完成数据库备份以恢复验证。
20.编写crontab，每天按表备份所有mysql数据，将备份数据放在以天为时间的目录下
21.编写crontab，基于xtrabackup，每周1，周5进行完全备份，周2到周4进行增量备份。
25.总结主从复制不一致的原因，如何解决不一致，如何避免不一致
28.总结mysql高可用方案及高可用级别。搭建MHA集群和galera cluster，尝试搭建TIDB集群
30.总结openvpn原理，并完成1键安装不同版本vpn脚本，可以适配rocky，ubuntu，centos主机，同时支持添加账号，注销账号
1.完成将server和client端的mysql配置默认字符集为utf8mb4
[root@Rocky8 ~]# vim /etc/my.cnf [mysqld] #服务器端 character-set-server=utf8mb4 [client] #客户端 default-character-set=utf8mb4 #重启服务 [root@Rocky8 ~]# systemctl restart mysql-servermysql 8.0和mariabd 10.6版本默认就是utf8mb4字符集。所以不需要更改
2.掌握如何获取SQL命令的帮助，基于帮助完成添加testdb数据库字符集utf8，排序集合utf8_bin
（1）如何获取帮助：
[root@Rocky8 ~]#mysql (root@localhost) [(none)]> ? contents; You asked for help about help category: "Contents" For more information, type 'help <item>', where <item> is one of the following categories: Account Management Administration Components Compound Statements Contents Data Definition Data Manipulation Data Types Functions Geographic Features Help Metadata Language Structure Loadable Functions Plugins Prepared Statements Replication Statements Storage Engines Table Maintenance Transactions Utility[root@Rocky8 ~]#mysql (root@localhost) [(none)]> CREATE DATABASE testdb CHARACTER SET utf8 COLLATE utf8_bin; (root@localhost) [(none)]> SHOW CREATE DATABASE testdb; +----------+---------------------------------------------------------------------------------------------------------------------+ | Database | Create Database | +----------+---------------------------------------------------------------------------------------------------------------------+ | testdb | CREATE DATABASE `testdb` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */ /*!80016 DEFAULT ENCRYPTION='N' */ | +----------+---------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) (root@localhost) [(none)]> SHOW CREATE DATABASE testdb\G; *************************** 1. row *************************** Database: testdb Create Database: CREATE DATABASE `testdb` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */ /*!80016 DEFAULT ENCRYPTION='N' */ 1 row in set (0.00 sec)
3.总结mysql常见的数据类型
4. 创建一个主机表host,放在testdb中，要求字段 1) id tinyint 无符号 自增 主键 。2) hostname可变字符长度256，可为空。3）ip 可变字符长度256，可为空。4）账号，可变字符长度256，可为空。5）密码，可变字符长度256，可为空。6）创建时间，时间类型，非空。7）更新时间，时间类型，默认当前时间。8）区域，只能在华南，华北，华东，三个区域之一。9）端口，无符号整数，可为空。10）外网地址，可变字符长度256，可为空。11）内网地址，可变字符长度256，可为空。
#转到testdb数据库 (root@localhost) [(none)]> USE testdb; #创建host表 (root@localhost) [testdb]> CREATE TABLE host ( id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,hostname VARCHAR(256) NULL,ip VARCHAR(256) NULL,account VARCHAR(256) NULL,password VARCHAR(256) NULL,createtime DATETIME,updatetime TIMESTAMP,area ENUM('华南','华北','华东') NOT NULL ,port INT UNSIGNED NULL,externaladdress VARCHAR(256) NULL,inaddress VARCHAR(256) NULL ); #查看表结构 (root@localhost) [testdb]> DESC host; +-----------------+----------------------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+----------------------------------+------+-----+---------+----------------+ | id | tinyint unsigned | NO | PRI | NULL | auto_increment | | hostname | varchar(256) | YES | | NULL | | | ip | varchar(256) | YES | | NULL | | | account | varchar(256) | YES | | NULL | | | password | varchar(256) | YES | | NULL | | | createtime | datetime | YES | | NULL | | | updatetime | timestamp | YES | | NULL | | | area | enum('华南','华北','华东') | NO | | NULL | | | port | int unsigned | YES | | NULL | | | externaladdress | varchar(256) | YES | | NULL | | | inaddress | varchar(256) | YES | | NULL | | +-----------------+----------------------------------+------+-----+---------+----------------+ 11 rows in set (0.00 sec)
5.给test.host表中添加多条数据
#表中添加数据 (root@localhost) [testdb]> insert into host (id,hostname,ip,account,password,createtime,updatetime,area,port) values (1,'Rocky8.zhang.stu','10.0.0.10/24','root','123456',now(),now(),'华北',3306); Query OK, 1 row affected (0.01 sec) #查看表数据 (root@localhost) [testdb]> SELECT * FROM host\G; *************************** 1. row *************************** id: 1 hostname: Rocky8.zhang.stu ip: 10.0.0.10/24 account: root password: 123456 createtime: 2023-01-02 19:21:00 updatetime: 2023-01-02 19:21:00 area: 华北 port: 3306 externaladdress: NULL inaddress: NULL *************************** 2. row *************************** id: 2 hostname: Rocky7.zhang.stu ip: 10.0.0.100 account: zhang password: 654321 createtime: 2023-01-02 20:17:00 updatetime: 2023-01-02 20:17:00 area: 华东 port: 3307 externaladdress: NULL inaddress: NULL 2 rows in set (0.00 sec)
6.根据表扩展出几个语句，完成总结DDL DML 的用法，并配上示例。
（1）总结DDL用法：
#用CREATE来创建数据库 (root@localhost) [hellodb]> CREATE DATABASE testdb1; Query OK, 1 row affected (0.00 sec) #查看数据库 (root@localhost) [hellodb]> SHOW DATABASES; +--------------------+ | Database | +--------------------| | information_schema | | mysql | | performance_schema | | sys | | testdb1 | +--------------------+ 5 rows in set (0.00 sec) #查看指定数据库结构 (root@localhost) [hellodb]> SHOW CREATE DATABASE testdb1\G; *************************** 1. row *************************** Database: testdb1 Create Database: CREATE DATABASE `testdb1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ 1 row in set (0.00 sec) #修改字符集为utf8 (root@localhost) [hellodb]> ALTER DATABASE testdb1 CHARACTER SET utf8; Query OK, 1 row affected, 1 warning (0.01 sec) #用DROP来删除数据库 (root@localhost) [hellodb]> DROP DATABASE testdb1; Query OK, 0 rows affected (0.01 sec) (root@localhost) [hellodb]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | +--------------------+ 4 rows in set (0.00 sec)（2）总结DML用法：
#在testdb中创建student表 (root@localhost) [testdb]> CREATE TABLE student(id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(3) NOT NULL,gender ENUM('F','M'),age SMALLINT UNSIGNED ); Query OK, 0 rows affected (0.02 sec) #查看表 (root@localhost) [testdb]> SHOW TABLES ; +------------------+ | Tables_in_testdb | +------------------+ | host | | student | +------------------+ 2 rows in set (0.01 sec) #查看student表类型 (root@localhost) [testdb]> DESC student; +--------+-------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------------+------+-----+---------+----------------+ | id | tinyint unsigned | NO | PRI | NULL | auto_increment | | name | varchar(3) | NO | | NULL | | | gender | enum('F','M') | YES | | NULL | | | age | smallint unsigned | YES | | NULL | | +--------+-------------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) #在student表中插入数据 (root@localhost) [testdb]> INSERT INTO student (name,gender,age) values ('小明','M',20),('小红','F',19),(' 小张','M',21); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 #查询数据 (root@localhost) [testdb]> SELECT * FROM student\G; *************************** 1. row *************************** id: 1 name: 小明 gender: M age: 20 *************************** 2. row *************************** id: 2 name: 小红 gender: F age: 19 *************************** 3. row *************************** id: 3 name: 小张 gender: M age: 21 3 rows in set (0.00 sec) #删除数据 (root@localhost) [testdb]> DELETE FROM student WHERE id=3; Query OK, 1 row affected (0.00 sec) #查看删除后数据 (root@localhost) [testdb]> SELECT * FROM student\G; *************************** 1. row *************************** id: 1 name: 小明 gender: M age: 20 *************************** 2. row *************************** id: 2 name: 小红 gender: F age: 19 2 rows in set (0.00 sec) #修改数据 (root@localhost) [testdb]> UPDATE student SET age=21 WHERE id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 #查看修改后数据 (root@localhost) [testdb]> SELECT * FROM student\G; *************************** 1. row *************************** id: 1 name: 小明 gender: M age: 21 *************************** 2. row *************************** id: 2 name: 小红 gender: F age: 19 2 rows in set (0.00 sec)
7.导入hellodb库，总结DQL，alias，where，group by，order by，limit，having用法示例
#查看表中所有字段 (root@localhost) [hellodb]> SELECT * FROM students; #查看表中指定字段 (root@localhost) [hellodb]> SELECT stuid,name,gender FROM students; #用LIMIT查看表中前n行 (root@localhost) [hellodb]> SELECT * FROM students LIMIT 3; #给指定字段加别名（alias） (root@localhost) [hellodb]> SELECT stuid 编号,name 姓名,gender 性别 FROM students; #用WHERE过滤行 (root@localhost) [hellodb]> SELECT * FROM students WHERE age <=20; #用WHERE过滤行 (root@localhost) [hellodb]> SELECT * FROM students WHERE age BETWEEN 20 AND 30; #用GROUP BY分组 (root@localhost) [hellodb]> SELECT gender,AVG (age) FROM students WHERE gender = 'M' GROUP BY gender; #用GROUP BY分组 (root@localhost) [hellodb]> SELECT gender,AVG (age) FROM students GROUP BY gender HAVING gender = 'M'; #用ORDER BY 排序 (root@localhost) [hellodb]> SELECT * FROM students WHERE classid AND teacherid IS NOT NULL ORDER BY age ASC;
8.基于hellodb库，总结子查询，联合查询，交叉连接，内连接，左连接，右连接，完全连接，自连接。
（1）子查询：子查询 subquery 即SQL语句调用另一个SELECT子句,可以是对同一张表,也可以是对不同表
#把students表中年龄平均值作为参数，来修改teachers表中tid=2对应的年龄值。 (root@localhost) [hellodb]> UPDATE teachers SET age = (SELECT AVG(age) FROM students) WHERE tid=2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0（2）联合查询： Union 实现的条件,多个表的字段数量相同,字段名和数据类型可以不同,但一般数据类型是相同 的.
(root@localhost) [hellodb]> SELECT TID ID,name,age FROM teachers UNION SELECT Stuid ID,name,age FROM students LIMIT 8; +----+---------------+-----+ | ID | name | age | +----+---------------+-----+ | 1 | Song Jiang | 45 | | 2 | Zhang Sanfeng | 27 | | 3 | Miejue Shitai | 77 | | 4 | Lin Chaoying | 93 | | 1 | Shi Zhongyu | 22 | | 2 | Shi Potian | 22 | | 3 | Xie Yanke | 53 | | 4 | Ding Dian | 32 | +----+---------------+-----+ 8 rows in set (0.00 sec)（3）交叉连接：CROSS JOIN
(root@localhost) [hellodb]> SELECT * FROM teachers CROSS JOIN students LIMIT 8; +-----+---------------+-----+--------+-------+-------------+-----+--------+---------+-----------+ | TID | Name | Age | Gender | StuID | Name | Age | Gender | ClassID | TeacherID | +-----+---------------+-----+--------+-------+-------------+-----+--------+---------+-----------+ | 4 | Lin Chaoying | 93 | F | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 3 | Miejue Shitai | 77 | F | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Zhang Sanfeng | 27 | M | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 1 | Song Jiang | 45 | M | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 4 | Lin Chaoying | 93 | F | 2 | Shi Potian | 22 | M | 1 | 7 | | 3 | Miejue Shitai | 77 | F | 2 | Shi Potian | 22 | M | 1 | 7 | | 2 | Zhang Sanfeng | 27 | M | 2 | Shi Potian | 22 | M | 1 | 7 | | 1 | Song Jiang | 45 | M | 2 | Shi Potian | 22 | M | 1 | 7 | +-----+---------------+-----+--------+-------+-------------+-----+--------+---------+-----------+ 8 rows in set (0.00 sec)（4）内连接：INNER JOIN
#把students表中的teacherid和teachers表中的tid相等交集部分显示出来 (root@localhost) [hellodb]> SELECT t.tid,t.name,s.teacherid,s.name FROM students s INNER JOIN teachers t ON s.teacherid=t.tid; +-----+---------------+-----------+-------------+ | tid | name | teacherid | name | +-----+---------------+-----------+-------------+ | 3 | Miejue Shitai | 3 | Shi Zhongyu | | 4 | Lin Chaoying | 4 | Ding Dian | | 1 | Song Jiang | 1 | Yu Yutong | +-----+---------------+-----------+-------------+ 3 rows in set (0.00 sec)（5）左连接：LEFT OUTER JOIN
#显示全部的students表数据和teachers表交集的部分，其他用null填充 (root@localhost) [hellodb]> SELECT * FROM students s LEFT OUTER JOIN teachers t ON s.teacherid=t.tid LIMIT 10; +-------+--------------+-----+--------+---------+-----------+------+---------------+------+--------+ | StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender | +-------+--------------+-----+--------+---------+-----------+------+---------------+------+--------+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F | | 2 | Shi Potian | 22 | M | 1 | 7 | NULL | NULL | NULL | NULL | | 3 | Xie Yanke | 53 | M | 2 | 16 | NULL | NULL | NULL | NULL | | 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F | | 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M | | 6 | Shi Qing | 46 | M | 5 | NULL | NULL | NULL | NULL | NULL | | 7 | Xi Ren | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL | | 8 | Lin Daiyu | 17 | F | 7 | NULL | NULL | NULL | NULL | NULL | | 9 | Ren Yingying | 20 | F | 6 | NULL | NULL | NULL | NULL | NULL | | 10 | Yue Lingshan | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL | +-------+--------------+-----+--------+---------+-----------+------+---------------+------+--------+ 10 rows in set (0.01 sec)（6）右连接：RIGHT OUTER JOIN
#显示teachers表中的全部和students表中交集的数据，其他用null填充。 (root@localhost) [hellodb]> SELECT * FROM students s RIGHT OUTER JOIN teachers t ON s.teacherid=t.tid; +-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+ | StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender | +-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+ | 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M | | NULL | NULL | NULL | NULL | NULL | NULL | 2 | Zhang Sanfeng | 27 | M | | 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F | | 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F | +-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+ 4 rows in set (0.00 sec)（7）完全连接：FULL OUTER JOIN（union左连接+右连接）
(root@localhost) [hellodb]> SELECT * FROM students s LEFT OUTER JOIN teachers t ON s.teacherid=t.tid -> UNION -> SELECT * FROM students s RIGHT OUTER JOIN teachers t ON s.teacherid=t.tid; +-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+ | StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender | +-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F | | 2 | Shi Potian | 22 | M | 1 | 7 | NULL | NULL | NULL | NULL | | 3 | Xie Yanke | 53 | M | 2 | 16 | NULL | NULL | NULL | NULL | | 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F | | 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M | | 6 | Shi Qing | 46 | M | 5 | NULL | NULL | NULL | NULL | NULL | | 7 | Xi Ren | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL | | 8 | Lin Daiyu | 17 | F | 7 | NULL | NULL | NULL | NULL | NULL | | 9 | Ren Yingying | 20 | F | 6 | NULL | NULL | NULL | NULL | NULL | | 10 | Yue Lingshan | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL |（8）自连接：即表自身连接自身
#创建emo表 (root@localhost) [testdb]> CREATE TABLE emo (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(3),leaderid INT); Query OK, 0 rows affected (0.03 sec) #在emo表中添加数据 (root@localhost) [testdb]> INSERT INTO emo (name,leaderid) VALUES ('张',NULL),('于',1),('铭',2); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 #查看数据 (root@localhost) [testdb]> SELECT * FROM emo; +----+------+----------+ | id | name | leaderid | +----+------+----------+ | 1 | 张 | NULL | | 2 | 于 | 1 | | 3 | 铭 | 2 | +----+------+----------+ 3 rows in set (0.00 sec) #显示出emo员工对应的领导名字 (root@localhost) [testdb]> SELECT e.name emo_name,l.name led_name FROM emo e LEFT OUTER JOIN emo l ON e.leaderid=l.id; +----------+----------+ | emo_name | led_name | +----------+----------+ | 张 | NULL | | 于 | 张 | | 铭 | 于 | +----------+----------+ 3 rows in set (0.00 sec) (root@localhost) [testdb]> SELECT e.name emo_name,IFNULL(l.name,'无上级') FROM emo e LEFT OUTER JOIN emo l ON e.leaderid=l.id; +----------+----------------------------+ | emo_name | IFNULL(l.name,'无上级') | +----------+----------------------------+ | 张 | 无上级 | | 于 | 张 | | 铭 | 于 | +----------+----------------------------+ 3 rows in set (0.00 sec) (root@localhost) [testdb]> SELECT e.name emo_name,IFNULL(l.name,'无上级') led_name FROM emo e LEFT OUTER JOIIN emo l ON e.leaderid=l.id; +----------+-----------+ | emo_name | led_name | +----------+-----------+ | 张 | 无上级 | | 于 | 张 | | 铭 | 于 | +----------+-----------+ 3 rows in set (0.00 sec)
9.总结select语句处理顺序
10-11总结mysql事件管理，用户管理，权限管理，搭建wordpress站点
#创建数据库wordpress (root@localhost) [(none)]> CREATE DATABASE wordpress; Query OK, 1 row affected (0.00 sec) #创建worepress用户 (root@localhost) [(none)]> CREATE USER wordpress@'10.0.0.20' IDENTIFIED BY '123456'; Query OK, 0 rows affected (0.01 sec) #给worepress@'10.0.0.20'远端用户授权 (root@localhost) [(none)]> GRANT ALL ON wordpress.* TO wordpress@'10.0.0.20'; Query OK, 0 rows affected (0.00 sec) #安装软件包 [root@Rocky8 ~]#yum -y install httpd php php-mysqlnd php-json #启动http服务 [root@Rocky8 ~]#systemclt enable --now httpd #下载并解压wordpress软件包到tmp文件夹下 [root@Rocky8 ~]#unzip -d /tmp wordpress-6.1.1-zh_CN.zip #把wordpress下的所有文件移到/var/www/html/ 下 [root@Rocky8 /tmp]#mv wordpress/* /var/www/html/ #修改所有者和所属组权限 [root@Rocky8 ~]#chown -R apache. /var/www/html/ #查看权限 [root@Rocky8 ~]#ll -d /var/www/html/ drwxr-xr-x 5 apache apache 4096 Jan 4 17:06 /var/www/html/在c盘/windows/system32/drivers/etc/hosts 添加条目 10.0.0.20 blog.zybstu.com
12.总结mysql架构原理
13. 总结myisam和innodb存储引擎的区别
14.总结mysql索引作用，同时总结哪些查询不会使用到索引
（1）mysql索引作用：更快的定位所要查询的内容。
（2）总结不会用到的索引查询：在LIKE语句中，以%号或者以"_"开头的查询内容不会用到索引
15.总结事务ACID事务特性
A：atomicity 原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚
C：consistency一致性；数据库总是从一个一致性状态转换为另一个一致性状态。
I：Isolation隔离性；一个事务所做出的操作在提交之前，是不能为其它事务所见；隔离有多种隔离 级别，实现并发
D：durability持久性；一旦事务提交，其所做的修改会永久保存于数据库中
16.总结事务日志工作原理
事务日志 transation log ：事务日志的写入类型为"追加"，因此其操作为"顺序IO"，通常也被称为预写式日志。
0模式：数据拷贝快，安全性差（断电将会丢失数据）
1模式：安全性高，IO占比高，性能差。
2模式：数据拷贝快，安全性比0模式好一些（因为系统不容易崩溃）
根据生产情况，可以进行更改模式来优化性能
事务型存储引擎自行管理和使用，建议和数据文件分开存放。
17.总结mysql日志类型，并说明如何启动日志
（1）事务日志：
事务日志文件：
[root@Rocky8 ~]#find /var/lib/mysql -name ib_l* /var/lib/mysql/ib_logfile1 /var/lib/mysql/ib_logfile0事务日志相关配置：
事务日志性能优化：(root@localhost) [mysql]> SHOW VARIABLES LIKE 'innodb_log%'; +------------------------------------+----------+ | Variable_name | Value | +------------------------------------+----------+ | innodb_log_buffer_size | 16777216 | | innodb_log_checksums | ON | | innodb_log_compressed_pages | ON | | innodb_log_file_size | 50331648 | #每个日志文件的大小 | innodb_log_files_in_group | 2 | #日志组成员个数 | innodb_log_group_home_dir | ./ | #事务文件路径 | innodb_log_spin_cpu_abs_lwm | 80 | | innodb_log_spin_cpu_pct_hwm | 50 | | innodb_log_wait_for_flush_spin_hwm | 400 | | innodb_log_write_ahead_size | 8192 | | innodb_log_writer_threads | ON | +------------------------------------+----------+ 11 rows in set (0.01 sec)#创建文件夹并修改权限 [root@Rocky8 ~]# mkdir -p /data/mysql/trans_log;chown -R mysql.mysql /data/mysql/tran_logs #修改配置文件（根据生产环境） [root@Rocky8 ~]# vim /etc/my.cnf [mysqld] innodb_log_file_size=500000000 innodb_log_files_in_group=3 innodb_log_group_home_dir=/data/mysql/tran_logs innodb_flush_log_at_trx_commit=2 #查看一下事务文件 [root@Rocky8 ~]#ll /data/mysql/tran_logs/ -h total 1.4G -rw-r----- 1 mysql mysql 476M Jan 6 11:44 ib_logfile0 -rw-r----- 1 mysql mysql 476M Jan 6 11:42 ib_logfile1 -rw-r----- 1 mysql mysql 476M Jan 6 11:42 ib_logfile2 #重启mysqld服务 [root@Rocky8 ~]# systemctl restart mysqld（2）错误日志：
错误文件路径：
(root@localhost) [mysql]> SELECT @@log_error; +---------------------------+ | @@log_error | +---------------------------+ | /var/log/mysql/mysqld.log | +---------------------------+ 1 row in set (0.00 sec)也可以重新指定错误日志的路径，在配置文件中修改。和事务日志修改方法一致。
（3）通用日志：
启动设置：
#查看默认状态为未开启 (root@localhost) [mysql]> SELECT @@general_log; +---------------+ | @@general_log | +---------------+ | 0 | +---------------+ 1 row in set (0.00 sec) #临时开启功能 (root@localhost) [mysql]> SET GLOBAL general_log=1; Query OK, 0 rows affected (0.00 sec) #永久开启功能（改配置文件） [root@Rocky8 ~]#vim /etc/my.cnf [mysqld] general_lo（4）慢查询日志：
启动设置：
#查看默认状态为未启动 (root@localhost) [mysql]> SELECT @@SLOW_QUERY_LOG; +------------------+ | @@SLOW_QUERY_LOG | +------------------+ | 0 | +------------------+ 1 row in set (0.00 sec) #临时启动 (root@localhost) [mysql]> SET GLOBAL SLOW_QUERY_LOG=1; Query OK, 0 rows affected (0.00 sec) #永久启动+修改配置 [root@Rocky8 ~]#vim /etc/my.cnf [mysqld] slow_query_log #开启 long_query_time=2 #慢查询阈值，默认是20秒。现改成2秒 log_queries_not_using_indexes #不使用索引或全索引扫描时，记录的日志（5）二进制日志（数据还原）：
#把二进制文件分离到新建的文件夹，然后设置权限 [root@Rocky8 ~]#mkdir -p /data/binlog;chown mysql.mysql /data/binlog #修改配置文件指定二进制文件存放位置 [root@Rocky8 ~]#vim /etc/my.cnf [mysqld] log_bin=/data/binlog/mysql-binlog #重启mysqld服务 [root@Rocky8 ~]#systemctl restart mysqld #查看变量sql_log_bin是否开启 [root@Rocky8 ~]#mysql testdb (root@localhost) [testdb]> select @@sql_log_bin; +---------------+ | @@sql_log_bin | +---------------+ | 1 | +---------------+ 1 row in set (0.00 sec) #查看变量log_bin是否开启 (root@localhost) [testdb]> select @@log_bin; +-----------+ | @@log_bin | +-----------+ | 1 | +-----------+ 1 row in set (0.00 sec) #以它为例进行修改，目前area是华北 (root@localhost) [testdb]> select * from host\G; *************************** 1. row *************************** id: 1 hostname: Rocky8.zhang.stu ip: 10.0.0.10/24 account: root password: 123456 createtime: 2023-01-02 19:21:00 updatetime: NULL area: 华北 port: 3306 externaladdress: NULL inaddress: NULL #现在把area改成了中国 (root@localhost) [testdb]> select * from host\G; *************************** 1. row *************************** id: 1 hostname: Rocky8.zhang.stu ip: 10.0.0.10/24 account: root password: 123456 createtime: 2023-01-02 19:21:00 updatetime: NULL area: 中国 port: 3306 externaladdress: NULL inaddress: NULL #查看二进制文件，已经记录了改变的内容 [root@Rocky8 ~]#mysqlbinlog -v /data/binlog/mysql-binlog.000001 # at 1859 #230108 19:24:21 server id 1 end_log_pos 1941 CRC32 0x9147ffdd Table_map: `testdb`.`host` mapped to number 255 # at 1941 #230108 19:24:21 server id 1 end_log_pos 2109 CRC32 0x0b4e10b5 Update_rows: table id 255 flags: STMT_END_F BINLOG ' 5ae6YxMBAAAAUgAAAJUHAAAAAP8AAAAAAAEABnRlc3RkYgAEaG9zdAALAQ8PDw8SEf4DDw8QAAMA AwADAAMAAP4GAAMAA34HAQHAAgFT3f9HkQ== 5ae6Yx8BAAAAqAAAAD0IAAAAAP8AAAAAAAEAAgAL/0AGARAAUm9ja3k4LnpoYW5nLnN0dQwA MTAuMC4wLjEwLzI0BAByb290BgAxMjM0NTaZrwU1QAbljY7ljJfqDAAAQAYBEABSb2NreTguemhh bmcuc3R1DAAxMC4wLjAuMTAvMjQEAHJvb3QGADEyMzQ1NpmvBTVABuS4reWbveoMAAC1EE4L '/*!*/; ### UPDATE `testdb`.`host` ### WHERE ### @1=1 ### @2='Rocky8.zhang.stu' ### @3='10.0.0.10/24' ### @4='root' ### @5='123456' ### @6='2023-01-02 19:21:00' ### @7=NULL ### @8='华北' ### @9=3306 ### @10=NULL ### @11=NULL ### SET ### @1=1 ### @2='Rocky8.zhang.stu' ### @3='10.0.0.10/24' ### @4='root' ### @5='123456' ### @6='2023-01-02 19:21:00' ### @7=NULL ### @8='中国' ### @9=3306 ### @10=NULL ### @11=NULL # at 2109 #用起点(at 1859)和终点位置(at 2109)截取变化的内容到binlog.sql(临时文件)。用于恢复数据 [root@Rocky8 ~]#mysqlbinlog -v /data/binlog/mysql-binlog.000001 --start-position=1859 --stop-position=2109 > binlog.sql #再次更改area为保定 (root@localhost) [testdb]> update host set area='保定' where id=1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 #把截取的文件传到mysql进行恢复数据. [root@Rocky8 ~]#mysql < binlog.sql #已完成
18.总结二进制日志的不同格式的使用场景
二进制日志记录三种格式：
基于"语句"记录：statement，记录语句，默认模式（ MariaDB 10.2.3 版本以下 ），日志量较少。
基于"行"记录：row，记录数据，日志量较大，更加安全，建议使用的格式,
基于混合模式：mixed, 让系统自行判定该基于哪种方式进行，默认模式
#查看二进制日志格式： (root@localhost) [(none)]> select @@binlog_format; +-----------------+ | @@binlog_format | +-----------------+ | ROW | +-----------------+
19.总结mysql备份类型，并基于mysqldump，xtrabackup完成数据库备份以恢复验证。
（1）总结mysql备份类型：
完全备份：整个数据集
部分备份：只备份数据子集，如部分库或表 完全备份、
增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快， 还原复杂
差异备份：仅备份最近一次完全备份以来变化的数据，备份较慢，还原简单
注意：二进制日志文件不应该与数据文件放在同一磁盘
冷备：读、写操作均不可进行，数据库停止服务
温备：读操作可执行；但写操作不可执行
热备：读、写操作均可执行
MyISAM：温备，不支持热备
InnoDB：都支持
还原要点：
做还原测试，用于测试备份的可用性
还原演练，写成规范的技术文档
（2）基于mysqldump和xtrabackup完成数据库备份以恢复验证。
#在10.0.0.10上做备份 #去10.0.0.20上做还原 *********************************************** #创建backup文件夹 [root@Rocky8 ~]# mkdir /backup/ #用mysqldump在10.0.0.10主机上做完全备份 [root@Rocky8 ~]# mysqldump -uroot -p123456 -A > /backup/all.sql #发送到10.0.0.20主机上去 [root@Rocky8 ~]# rsync -av /backup/all.sql 10.0.0.20:/backup/ #10.0.0.20主机上还原数据 [root@Rocky8 ~]# mysql < all.sql ************************************************* #用xtrabackup在10.0.0.10主机上做完全备份 #创建backup文件夹 [root@Rocky8 ~]# mkdir /backup/ #用xtraback在10.0.0.10主机上做完全备份 [root@Rocky8 ~]#xtrabackup -uroot -p123456 --backup --target-dir=/backup/base #发送到10.0.0.2主机上去 [root@Rocky8 ~]#rsync -av /backup 10.0.0.20:/ #在10.0.0.20主机上停用数据库，清空文件夹中的文件 [root@Rocky8 ~]# systemctl stop mysqld [root@Rocky8 ~]# rm -rf /var/lib/mysql/* #在10.0.0.20上还原数据库 [root@Rocky8 ~]#xtrabackup --prepare --target-dir=/backup/base [root@Rocky8 ~]#xtrabackup --copy-back --target-dir=/backup/base [root@Rocky8 ~]#chown -R mysql. /var/lib/mysql [root@Rocky8 ~]#systemctl start mysqld
20.编写crontab，每天按表备份所有mysql数据，将备份数据放在以天为时间的目录下
#创建脚本文件 [root@Rocky8 /data/scripts]#vim mysql_backup.sh #!/bin/bash DIR=/backup DB=all TIME=`date +%F_%R:%S` [ -d $DIR ] | mkdir ${DIR} &> /dev/null mysqldump -uroot -p123456 -A -F --single-transaction --source-data=2 --flush-privileges &> /dev/null | gzip > /${DIR}/${DB}_${TIME}.sql.gz ********************************************************** #给脚本文件加执行权限 [root@Rocky8 ~]#chmod +x /data/scripts/mysql_backup.sh #创建计划任务 [root@Rocky8 ~]#crontab -e SHELL=/bin/bash PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin 0 0 * * * /data/scripts/mysql_backup.sh #查看计划任务表 [root@Rocky8 ~]#crontab -l 0 0 * * * /data/scripts/mysql_backup.sh
21.编写crontab，基于xtrabackup，每周1，周5进行完全备份，周2到周4进行增量备份。
[root@Rocky8 ~]#crontab -e #!/bin/bash SHELL=/bin/bash PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin 30 2 * * 1,5 xtrabackup -uroot -p123456 --backup --target-dir=/backup/base 30 2 * * 2 xtrabackup -uroot -p123456 --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base 30 2 * * 3 xtrabackup -uroot -p123456 --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1 30 2 * * 4 xtrabackup -uroot -pmagedu --backup --target-dir=/backup/inc3 --incremental-basedir=/backup/inc2
22.总结mysql主从复制原理
主从复制相关线程：
（1）主节点：
dump Thread：为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events
（2）从节点：
I/O Thread：向Master请求二进制日志事件，并保存于中继日志中
SQL Thread：从中继日志中读取日志事件，在本地完成重放
主从复制优势：
（1）避免数据库单点失败问题
（2）实现读写分离，负载均衡。
注意：
建议数据库版本要一致。如果不一致，从节点的版本要高。
23.实现主从复制，主主复制，半同步复制，过滤复制
（1）主从复制：
#10.0.0.8主机为主节点。 #10.0.0.20主机为从节点。 ***************************************** #修改主节点服务器配置 [root@master ~]#vim /etc/my.cnf [mysqld] server-id=8 #要保证主节点ID号和从节点ID号不一致就行。建议用IP地址的最后一位 log_bin=/data/logbin/mysql-bin #创建文件夹带修改权限来放二进制日志 [root@master ~]#mkdir /data/logbin [root@master ~]#chown mysql.mysql /data/logbin #重启服务 [root@master ~]#systemctl restart mysqld #查看二进制日志文件开始位置 (root@localhost) [(none)]> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 156 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) #创建用户账号且赋予复制权限 (root@localhost) [(none)]> CREATE USER repluser@'10.0.0.%' IDENTIFIED BY '123456'; (root@localhost) [(none)]> GRANT REPLICATION SLAVE ON *.* TO repluser@'10.0.0.%'; #把主节点以前的数据做完全备份 [root@master ~]#mkdir /backup [root@master ~]#mysqldump -A -F --single-transaction --source-data=1 > /backup/full-`date +%F`.sql #把主节点的备份数据发送到从节点进行还原（从节点先建立/backup文件夹） [root@master ~]#rsync -av /backup/full-2023-01-09.sql 10.0.0.20:/backup ********************************************************* #修改从节点服务器配置 [root@Rocky8 ~]#cat >> /etc/my.cnf << EOF > [mysqld] > server-id=20 > read-only > EOF #重启服务 [root@Rocky8 ~]#systemctl restart mysqld #创建/backup文件夹来放从主节点过来的备份数据 [root@Rocky8 ~]#mkdir /backup #修改从备份文件full-2023-01-09.sql（添加一些内容） [root@Rocky8 ~]#vim /backup/full-2023-01-09.sql CHANGE MASTER TO MASTER_HOST='10.0.0.10', #主节点地址 MASTER_USER='repluser', #主节点用户 MASTER_PASSWORD='123456', #主节点用户密码 MASTER_PORT=3306, #主节点端口号 MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=156; #主节点日志文件及位置 #还原数据和开启线程 mysql> SET SQL_LOG_BIN=0; #先关闭二进制日志 mysql> SOURCE /backup/full-2023-01-09.sql; #生效 mysql> SET SQL_LOG_BIN=1; #启动二进制日志 mysql> START SLAVE; #开启从节点服务 #查看状态 mysql> SHOW SLAVE STATUS\G; ************************* 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 10.0.0.10 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 156 Relay_Log_File: Rocky8-relay-bin.000004 Relay_Log_Pos: 371 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes（2）主主复制：在主从复制的基础上进行一些修改即可
#10.0.0.8主机为原主节点。 #10.0.0.20为原从节点。 *********************************** #先在10.0.0.20原从节点主机上查找文件名称和位置 mysql> show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000001 | 10752 | | | | +---------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) #在10.0.0.8原主节点主机上执行以下命令 (root@localhost) [hellodb]> CHANGE MASTER TO MASTER_HOST='10.0.0.20', #原从节点地址 MASTER_USER='repluser', #用户 MASTER_PASSWORD='123456', #用户密码 MASTER_PORT=3306, #端口号 MASTER_LOG_FILE='binlog.000001', #原从节点查到的File MASTER_LOG_POS=10752 #原从节点查到的Position (root@localhost) [hellodb]> start slave; #开启注意：
修改数据时，只在一台主服务器上操作。这样能够避免同时写数据造成的冲突。
出现冲突怎么解决？
#系统变量，指定跳过复制事件的个数
SET GLOBAL sql_slave_skip_counter = N
#服务器选项，只读系统变量，指定跳过事件的ID
[mysqld]
slave_skip_errors=编号 | ALL
重新启动服务
（3）半同步复制：在主从复制的基础上进行一些修改即可
#在主节点服务器上安装master插件 (root@localhost) [hellodb]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so' #在主节点服务器上修改配置文件 [root@Rocky8 ~]# vim /etc/my.cnf rpl_semi_sync_master_enabled rpl_semi_sync_master_timeout=3000 #3秒内无法同步，也将返回成功信息给客户端。默认值为10秒 #重启主节点服务器 [root@Rocky8 ~]# systemctl restart mysqld **************************************************（一台主节点服务器，2台从节点服务器） #在从节点服务器上安装slave插件 (root@localhost) [hellodb]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so' #在从节点服务器上修改配置文件 [root@Rocky8 ~]# vim /etc/my.cnf rpl_semi_sync_slave_enabled #重启从节点服务器 [root@Rocky8 ~]# systemctl restart mysqld *************************************************** #在主节点服务器上查看状态变量 (root@localhost) [hellodb]> SHOW GLOBAL STATUS LIKE '%semi%' +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 2 |（4）过滤同步：在主从复制的基础上进行一些修改即可
#在从节点服务器上修改配置文件（建立白名单或者黑名单） [root@Rocky8 ~]# vim /etc/my.cnf [mysqld] replicate_do_db=db1 #白名单 只用db1数据库才进行复制 replicate_ignore_bd=db2 #黑名单 除了db2数据库才进行复制 #重启服务 [root@Rocky8 ~]# systemctl restart mysqld #查看slave状态 mysql> show slave status\G; Replicate_Do_DB: db1 #已启用 Replicate_Ignore_DB: db2 #已启用
24.总结GTID复制原理，并完成GTID复制集群
（1）复制原理：
利用 GTID复制不像传统的复制方式（异步复制、半同步复制）需要找到binlog文件名和位置点，只需知道master的IP、端口、账号、密码即可。开启GTID后，执行change master to master_auto_postion=1即可，它会自动寻找到相应的位置开始同步（2）完成GTID复制集群：
#10.0.0.10为主节点服务器 #10.0.0.20为从节点服务器 ************************************ #主节点服务器上修改配置文件 [root@Rocky8 ~]#vim /etc/my.cnf [mysqld] server-id=10 gtid_mode=ON enforce_gtid_consistency #重启服务 [root@Rocky8 ~]#systemctl restart mysqld #主节点服务器上创建用户及授权 (root@localhost) [hellodb]> CREATE USER repluser@'10.0.0.%' IDENTIFIED BY '123456'; (root@localhost) [hellodb]> GRANT REPLICATION SLAVE ON *.* TO repluser@'10.0.0.%'; *************************************************** #从节点服务器上修改配置文件 [root@Rocky8 ~]#vim /etc/my.cnf [mysqld] server-id=20 gtid-mode=ON enforce_gtid_consistency #重启服务 [root@Rocky8 ~]#systemctl restart mysqld #写入同步文件 (root@localhost) [hellodb]> CHANGE MASTER TO MASTER_HOST='10.0.0.10', #原从节点地址 MASTER_USER='repluser', #用户 MASTER_PASSWORD='123456', #用户密码 MASTER_PORT=3306, #端口号 MASTER_AUTO_POSITION=1; #使用GTID #开启slave (root@localhost) [hellodb]> start slave;
25.总结主从复制不一致的原因，如何解决不一致，如何避免不一致
（1）不一致的原因和避免：
#主库binlog格式为Statement（语句型），同步到从库执行后可能造成主从不一致。应该设置为Row（行型）
#主节点服务器上的二进制日志功能没有及时打开，导致主从数据不一致。应该及时打开二进制日志功能(sql_log_bin和log_bin都为1)
#主从数据库版本不一致，特别是高版本是主，低版本为从的情况下，主数据库上面支持的功能，从数据库可能不支持该功能。应该选择同一版本，或者主低从高配置。
#主库或从库意外宕机，宕机可能会造成binlog或者relaylog文件出现损坏，导致主从不一致。
#主从sql_mode 不一致。应该设置为一致
#从节点未设置只读，误操作写入数据。应该从节点设置为只读read-only
#MySQL自身bug导致。
（2）如何解决：
#手动重建不一致的表
#使用percona-toolkit工具辅助
26.总结数据库垂直拆分和水平拆分
（1）垂直拆分：一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，分布到不同的数据库上面，这样也就将数据或者说压力分担到不同的库上面。但是会造成部分业务表无法 join，只能通过接口方式解决，提高了系统复杂度。
（2）水平拆分：水平拆分是按照某个字段的某种规则来分散到多个库之中，每个表中包含一部分数据。数据多次扩展难度跟维护量极大
27.基于mycat实现读写分离
#10.0.0.110为客户端。 #10.0.0.40为mycat。 #10.0.0.10为主节点。 #10.0.0.20为从节点。 #注意：把mycat下载到/apps文件夹 *********************************************** #mycat执行命令在/apps/mycat/bin下，所以先把路径作为PATH变量写入脚本中 [root@Rocky8 ~]# vim /etc/profile.d/mycat.sh PATH=/apps/mycat/bin:$PATH #生效 [root@Rocky8 ~]# . /etc/profile.d/mycat.d #安装java环境（前提是把内存调整到3G） [root@Rocky8 ~]#yum -y install java #修改配置mycat配置文件 [root@Rocky8 ~]# vim /apps/mycat/conf/server.xml [root@Rocky8 ~]# vim /apps/mycat/conf/schema.xml #在主节点建用户账号和授权（为的是让mycat去连接） (root@localhost) [hellodb]> CREATE USER admin@ '10.0.0.40' IDENTIFIED BY '123456'; (root@localhost) [hellodb]> GRANT ALL ON hellodb.* TO admin@ '10.0.0.40'; #启动mycat [root@Rocky8 ~]#mycat start #查看日志表明mycat正常启动 [root@Rocky8 ~]#tail /apps/mycat/logs/wrapper.log INFO | jvm 1 | 2023/01/10 20:05:58 | MyCAT Server startup successfully. #用10.0.0.110客户端去连接mycat [root@Rocky8 ~]#mysql -uroot -p123456 -h 10.0.0.40 ********客户端通过mycat去写数据怎么知道是用的主节点服务器呢？********* #在主从节点都开启通用日志（记录对数据库的通用操作） [root@Rocky8 ~]#mysql -e 'SET GLOBAL GENERAL_LOG=1'; #在客户端修改数据然后在查数据 #通过tail -f 命令去监测主从的/var/lib/mysql/Rocky.log日志变化情况就能确定写操作是在主节点上发生的，查操作是在从节点上发生的。
28.总结mysql高可用方案及高可用级别。搭建MHA集群和galera cluster，尝试搭建TIDB集群
（1）高可用方案及级别：
MHA：Master High Availability，对主节点进行监控，可实现自动故障转移至其它从节点；通过提 升某一从节点为新的主节点，基于主从复制实现，还需要客户端配合实现，目前MHA主要支持一主 多从的架构，要搭建MHA,要求一个复制集群中必须最少有三台数据库服务器，一主二从，即一台 充当master，一台充当备用master，另外一台充当从库。
MHA工作原理：
1. MHA利用 SELECT 1 As Value 指令判断master服务器的健康性,一旦master 宕机,MHA 从宕机崩溃 的master保存二进制日志事件（binlog events）
2. 识别含有最新更新的slave 3. 应用差异的中继日志（relay log）到其他的slave 4. 应用从master保存的二进制日志事件（binlog events）到所有slave节点
5. 提升一个slave为新的master 6. 使其他的slave连接新的master进行复制
7. 故障服务器自动被剔除集群(masterha_conf_host),将配置信息去掉
8.旧master的VIP（虚拟IP地址）漂移到新的master上，用户应用就可以访问新的master
9. MHA是一次性的高可用性解决方案,Manager会自动退出
（2）搭建MHA集群：
#10.0.0.200（centos7）用来安装MHA管理端和node #10.0.0.10（rocky8）主节点，安装MHA node #10.0.0.20（rocky8）从节点，安装MHA node #10.0.0.40（rocky8）从节点，安装MHA node #注意：提前先将主从搭建好（目前用的是GTID） ************************************* #注意：在安装MHA管理端时，出现安装失败的问题。 #yum -y install epel-release #更新epel源 #yum clean all #清理yum旧缓存 #yum makecache #更新缓存 ************************************** #在10.0.0.200上安装MHA管理端和node [root@Centos7 ~]#yum -y install mha4mysql-manager-0.58-0.el7.centos.noarch.rpm mha4mysql-node-0.58-0.el7.centos.noarch.rpm #其他主机安装MHA node [root@Rocky8 ~]#yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm ************************************** #实现基于秘钥的远程登录方式（用脚本） #!/bin/bash NET=10.0.0 passwd=zhang0811 function color () { res_col=60 move_to_color="echo -en \e[${res_col}G" setcolor_success="echo -en \e[1;32m [ok]\n" setcolor_failure="echo -en \e[1;33m [failure]\n" setcolor_warning="echo -en \e[1;34m [warning]\n" setcolor_normal="echo -en \e[0m" echo -n "$1" && ${move_to_color} if [ $2 = "0" ];then ${setcolor_success} elif [ $2 = "1" ];then ${setcolor_failure} else ${setcolor_warning} fi ${setcolor_normal} } function judge_system () { . /etc/os-release if [ $ID = "rocky" -o $ID = "centos" ];then echo -e "\e[1;32m系统是rocky或centos\e[0m" sleep 1 rpm -q sshpass&>/dev/null||yum -y install sshpass color "sshpass安装完成" "0" elif [ $ID = "ubuntu" ];then echo -e "\e[1;32m系统是ubuntu\e[0m" sleep 1 dpkg -l sshpass&>/dev/null||apt -y install sshpass color "sshpass安装完成" "0" fi } function set_key () { #创建公钥和私钥 ssh-keygen -P "" -f /root/.ssh/id_rsa #自己和自己先实现key验证 sshpass -p "$passwd" ssh-copy-id -o StrictHostKeyChecking=no `hostname -I` for i in {10,20,40};do { sshpass -p "$passwd" scp -r -o StrictHostKeyChecking=no /root/.ssh "$NET.$i": &>/dev/null }& done color "可以远程管理主机" "0" } judge_system set_key ***************************************************** #创建mha文件夹和app1.cnf文件 [root@Centos7 ~]#mkdir /etc/mastermha/ ; touch /etc/mastermha/app1.cnf #修改app1.cnf配置文件 [root@Centos7 ~]#vim /etc/mastermha/app1.cnf [server default] user=mhauser password=123456 manager_workdir=/data/mastermha/app1/ manager_log=/data/mastermha/app1/manager.log remote_workdir=/data/mastermha/app1/ ssh_user=root repl_user=repluser repl_password=123456 ping_interval=1 master_ip_failover_script=/usr/local/bin/master_ip_failover report_script=/usr/local/bin/sendmail.sh check_repl_delay=0 master_binlog_dir=/data/mysql/ [server1] hostname=10.0.0.10 [server2] hostname=10.0.0.20 [server3] hostname=10.0.0.40 #在主节点服务器上创建用户（因为是主从复制，其他从节点也就有了）来让MHA管理端管理 (root@localhost) [(none)]> CREATE USER mhauser@'10.0.0.%' IDENTIFIED BY '123456'; #给该用户授权 (root@localhost) [(none)]> GRANT ALL ON *.* TO mhauser@'10.0.0.%'; #把Vip（虚拟ip）的perl脚本放到/usr/local/bin下，加执行权限。 [root@Centos7 /usr/local/bin]#ls master_ip_failover [root@Centos7 /usr/local/bin]#chmod +x master_ip_failover #还有一个已邮件方式报警的脚本也放到/usr/local/bin下，加执行权限 [root@Centos7 /usr/local/bin]#ls master_ip_failover sendmail.sh [root@Centos7 /usr/local/bin]#chmod +x sendmail.sh #修改邮件配置文件 [root@Centos7 /etc]#vim mail.rc set from=1476820896@qq.com set smtp=smtp.qq.com set smtp-auth-user=1476820896@qq.com set smtp-auth-password=swujdmmrquszjiac #修改主节点配置文件 [root@Rocky8 ~]#vim /etc/my.cnf [mysqld] server-id=10 #主从配置 gtid_mode=ON #主从配置 enforce_gtid_consistency #主从配置 general_log #开启通用日志 log-bin=/data/mysql/mysql-bin #二进制日志路径 skip_name_resolve=1 #禁止反向解析 #创建主节点二进制日志文件夹及改权限 [root@Rocky8 ~]# mkdir -p /data/mysql/ ; chown mysql. /data/mysql/ #重启主节点服务 [root@Rocky8 ~]# systemctl restart mysqld #修改从节点配置文件 [root@Rocky8 ~]#vim /etc/my.cnf [mysqld] server_id=20 #主从配置 log-bin=/data/mysql/mysql-bin #二进制日志路径 gtid_mode=on #主从配置 enforce_gtid_consistency #主从配置 relay_log_purge=0 #不清理中继日志 skip_name_resolve=1 #禁止反向解析 general_log #开启通用日志 #创建从节点二进制日志文件夹及改权限 [root@Rocky8 ~]# mkdir -p /data/mysql/ ; chown mysql. /data/mysql/ #重启从节点服务 [root@Rocky8 ~]# systemctl restart mysqld #perl脚本中，有一个Vip地址为10.0.0.100 所以需要在主节点服务器上临时配一个这个地址 [root@Rocky8 ~]#ifconfig eth0:1 10.0.0.100/24 #eth0的别名 ********************************************** #检查MHA环境 #检查key验证是否成功 [root@Centos7 ~]#masterha_check_ssh --conf=/etc/mastermha/app1.cnf Wed Jan 11 20:55:08 2023 - [info] All SSH connection tests passed successfully. #检查复制是否成功 [root@Centos7 ~]#masterha_check_repl --conf=/etc/mastermha/app1.cnf MySQL Replication Health is OK. ************************************************* #在测试环境启动MHA（前台运行） [root@Centos7 ~]#masterha_manager --conf=/etc/mastermha/app1.cnf --remove_dead_master_cof --ignore_last_failover #在生产环境启动MHA（后台运行） [root@Centos7 ~]#nohup masterha_manager --conf=/etc/mastermha/app1.cnf --remove_dead_master_conf --ignore_last_failover &> /dev/null #观察一下日志情况 [root@Centos7 ~]# tail -f /data/mastermha/app1/manager.log Wed Jan 11 21:08:06 2023 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond.. #在主节点上查看日志，MHA每隔一秒查看一次运行状态 [root@Rocky8 /data]#tail -f /var/lib/mysql/Rocky8.log 2023-01-11T13:12:29.701154Z 27 Query SELECT 1 As Value 2023-01-11T13:12:30.701891Z 27 Query SELECT 1 As Value 2023-01-11T13:12:31.703549Z 27 Query SELECT 1 As Value 2023-01-11T13:12:32.702731Z 27 Query SELECT 1 As Value ***************************************** #把主节点人为破坏也就是停止服务 [root@Rocky8 /data]# Systemctl stop mysql #日志上有相关反馈 app1: MySQL Master failover 10.0.0.10(10.0.0.10:3306) to 10.0.0.40(10.0.0.40:3306) succeeded Master 10.0.0.10(10.0.0.10:3306) is down! #也发送了邮件报警 MHA is failover! #MHA属于一次性任务，再次用再次开始就行。
注意：
MHA主要解决的是mysql的主从，主节点挂掉以后单点失败问题。MHA会自动提升从节点为新主，但是还是存在数据丢失的风险，这个风险主要来自于服务器是物理原因导致的宕机，因为如果只是mysql服务宕机了，二进制日志还可以通过ssh协议来传输到其他从节点。
（2）搭建Percona Xtradb Cluster(PXC 5.7）版的Galera Cluster集群
#准备4台centos7主机 #10.0.0.200 #10.0.0.210 #10.0.0.220 #10.0.0.230 ***************************************** 注意：仓库下载路径和资料上不一致，需要修改 #配置yum仓库的pxc（Percona XtraDB Cluster） [root@Centos7 ~]#vim /etc/yum.repos.d/pxc.repo [percona] name=percona_repo baseurl = https://mirror.tuna.tsinghua.edu.cn/percona/yum/release/7/RPMS/x86_64/ enabled = 1 gpgcheck = 0 #把配置文件传输到其他节点 [root@Centos7 ~]#scp /etc/yum.repos.d/pxc.repo 10.0.0.210:/etc/yum.repos.d/pxc.repo [root@Centos7 ~]#scp /etc/yum.repos.d/pxc.repo 10.0.0.220:/etc/yum.repos.d/pxc.repo [root@Centos7 ~]#scp /etc/yum.repos.d/pxc.repo 10.0.0.230:/etc/yum.repos.d/pxc.repo #4个节点都安装pxc [root@Centos7 /etc/yum.repos.d]#yum install Percona-XtraDB-Cluster-57 -y ********************************************************** #在10.0.0.200主机上修改配置文件 [root@Centos7 ~]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf wsrep_cluster_address=gcomm://10.0.0.200,10.0.0.210,10.0.0.220 #添加参与集群的ip地址 wsrep_node_address=10.0.0.200 #取消注释，填写当前IP地址 wsrep_node_name=pxc-cluster-node-1 #集群节点名1 wsrep_sst_auth="sstuser:s3cretPass" #用户和密码。取消注释 #在10.0.0.210主机上修改配置文件 [root@Centos7 ~]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf wsrep_cluster_address=gcomm://10.0.0.200,10.0.0.210,10.0.0.220 #添加参与集群的ip地址 wsrep_node_address=10.0.0.210 #取消注释，填写当前IP地址 wsrep_node_name=pxc-cluster-node-2 #集群节点名2 wsrep_sst_auth="sstuser:s3cretPass" #用户和密码。取消注释 #在10.0.0.220主机上修改配置文件 [root@Centos7 ~]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf wsrep_cluster_address=gcomm://10.0.0.200,10.0.0.210,10.0.0.220 #添加参与集群的ip地址 wsrep_node_address=10.0.0.220 #取消注释，填写当前IP地址 wsrep_node_name=pxc-cluster-node-3 #集群节点名3 wsrep_sst_auth="sstuser:s3cretPass" #用户和密码。取消注释 ******************************************************* 注意：有相关报警"某表不存在"或"表结构错误"。升级数据库就好了 #启动第一个节点 [root@Centos7 ~]#systemctl start mysql@bootstrap.service #升级数据库和重启 [root@Centos7 ~]#mysql_upgrade -uroot -p123456 [root@Centos7 ~]#systemctl restart mysql@bootstrap.service #创建sstuser账户和密码s3cretPass并授权 mysql> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 's3cretPass'; mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost'; #查看相关变量 mysql> SHOW VARIABLES LIKE 'wsrep%'\G *************************** 7. row *************************** Variable_name: wsrep_cluster_address Value: gcomm://10.0.0.200,10.0.0.210,10.0.0.220 *************************** 8. row *************************** Variable_name: wsrep_cluster_name Value: pxc-cluster *************************** 22. row *************************** Variable_name: wsrep_node_address Value: 10.0.0.200 #启动其他节点 [root@Centos7 ~]#systemctl start mysql #查看集群数量变成了3个 mysql> show status like 'wsrep_cluster_size'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | wsrep_cluster_size | 3 | +--------------------+-------+ 1 row in set (0.00 sec) ***************************************************** #验证集群是否成功 #在任意节点创建数据库、表和插入数据，都能同步到其他节点。 #而且解决了主主复制时同时写入数据出现的冲突问题。同时输入会在表里出现3行同样的数据 。 ******************************************************** #在把10.0.0.230从节点主机加进来。 #在10.0.0.230主机上修改配置文件 [root@Centos7 ~]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf wsrep_cluster_address=gcomm://10.0.0.200,10.0.0.210,10.0.0.220,10.0.0.230 #添加参与集群的ip地址 wsrep_node_address=10.0.0.230 #取消注释，填写当前IP地址 wsrep_node_name=pxc-cluster-node-4 #集群节点名3 wsrep_sst_auth="sstuser:s3cretPass" #用户和密码。取消注释 #启动10.0.0.230从节点主机 [root@Centos7 ~]# Systemctl start mysql mysql> show status like 'wsrep_cluster_size'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | wsrep_cluster_size | 4 | +--------------------+-------+ 1 row in set (0.00 sec) #数据都能自动同步过来。完成注意：
集群中节点的数量：整个集群中节点数量应该控制在最少3个、最多8个的范围内。3个以上节点是为了防止出现脑裂现场，少于8个是为了提高性能。因为节点数越多，输入的数据在每个节点上要校验的次数就变多，导致性能变差。
29.总结mysql配置最佳实践
1.基础规范
(1)必须使用InnoDB存储引擎
解读：支持事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高
(2)使用UTF8MB4字符集
解读：万国码，无需转码，无乱码风险，节省空间，支持表情包及生僻字
(3)数据表、数据字段必须加入中文注释
解读：N年后谁知道这个r1,r2,r3字段是干嘛的
(4)禁止使用存储过程、视图、触发器、Event
解读：高并发大数据的互联网业务，架构设计思路是"解放数据库CPU，将计算转移到服务层"，并发量 大的情况下，这些功能很可能将数据库拖死，业务逻辑放到服务层具备更好的扩展性，能够轻易实现"增 机器就加性能"。数据库擅长存储与索引，CPU计算还是上移吧！
(5)禁止存储大文件或者大照片
解读：为何要让数据库做它不擅长的事情?大文件和照片存储在文件系统，数据库里存URI多好。
2.命名规范
(1)只允许使用内网域名，而不是ip连接数据库
(2)线上环境、开发环境、测试环境数据库内网域名遵循命名规范
业务名称：xxx
线上环境：xxx.db
开发环境：xxx.rdb
测试环境：xxx.tdb
从库在名称后加-s标识，备库在名称后加-ss标识 线上从库：xxx-s.db
线上备库：xxx-sss.db
(3)库名、表名、字段名：小写，下划线风格，不超过32个字符，必须见名知意，禁止拼音英文混用
(4)库名与应用名称尽量一致，表名:t_业务名称_表的作用，主键名:pk_xxx，非唯一索引名:idx_xxx，唯 一键索引名:uk_xxx
3.表设计规范
(1)单实例表数目必须小于500，单表行数超过500万行或者单表容量超过2GB，才推荐进行分库分表。
说明：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表
(2)单表列数目必须小于30
(3)表必须有主键，例如自增主键
解读：
a)主键递增，数据行写入可以提高插入性能，可以避免page分裂，减少表碎片提升空间和内存的使用
b)主键要选择较短的数据类型， Innodb引擎普通索引都会保存主键的值，较短的数据类型可以有效的减 少索引的磁盘空间，提高索引的缓存效率
c) 无主键的表删除，在row模式的主从架构，会导致备库夯住
(4)禁止使用外键，如果有外键完整性约束，需要应用程序控制
解读：外键会导致表与表之间耦合，update与delete操作都会涉及相关联的表，十分影响sql 的性能， 甚至会造成死锁。高并发情况下容易造成数据库性能，大数据高并发业务场景数据库使用以性能优先
4.字段设计规范
(1)必须把字段定义为NOT NULL并且提供默认值
解读：
a)null的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化
b)null 这种类型MySQL内部需要进行特殊处理，增加数据库处理记录的复杂性;同等条件下，表中有较多 空字段的时候，数据库的处理性能会降低很多
c)null值需要更多的存储空，无论是表还是索引中每行中的null的列都需要额外的空间来标识
d)对null 的处理时候，只能采用is null或is not null，而不能采用=、in、、<>、!=、not in这些操作符 号。如：where name!='shenjian'，如果存在name为null值的记录，查询结果就不会包含name为null值的记录
(2)禁止使用TEXT、BLOB类型
解读：会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急 剧降低，影响数据库性能
(3)禁止使用小数存储货币
解读：使用整数吧，小数容易导致钱对不上
(4)必须使用varchar(20)存储手机号
解读：
a)涉及到区号或者国家代号，可能出现+-() b)手机号会去做数学运算么? c)varchar可以支持模糊查询，例如：like"138%" (18)禁止使用ENUM，可使用TINYINT代替 解读：
a)增加新的ENUM值要做DDL操作
b)ENUM的内部实际存储就是整数，你以为自己定义的是字符串?
5.索引设计规范
(1)单表索引建议控制在5个以内
(2)单索引字段数不允许超过5个
解读：字段超过5个时，实际已经起不到有效过滤数据的作用了
(3)禁止在更新十分频繁、区分度不高的属性上建立索引
解读：
a)更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能
b)"性别"这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类 似
(4)建立组合索引，必须把区分度高的字段放在前面
解读：能够更加有效的过滤数据
6.SQL使用规范
(1)禁止使用SELECT *，只获取必要的字段，需要显示说明列属性
解读：
a)读取不需要的列会增加CPU、IO、NET消耗
b)不能有效的利用覆盖索引
c)使用SELECT *容易在增加或者删除字段后出现程序BUG
(2)禁止使用INSERT INTO t_xxx VALUES(xxx)，必须显示指定插入的列属性
解读：容易在增加或者删除字段后出现程序BUG
(3）禁止使用属性隐式转换
解读：SELECT uid FROM t_user WHERE phone=13812345678 会导致全表扫描，而不能命中phone索 引，猜猜为什么?(这个线上问题不止出现过一次)
(4)禁止在WHERE条件的属性上使用函数或者表达式
解读：SELECT uid FROM t_user WHERE from_unixtime(day)>='2017-02-15' 会导致全表扫描 正确的写法是：SELECT uid FROM t_user WHERE day>= unix_timestamp('2017-02-15 00:00:00')
(5)禁止负向查询，以及%开头的模糊查询
解读：
a)负向查询条件：NOT、!=、<>、!、!>、NOT IN、NOT LIKE等，会导致全表扫描
b)%开头的模糊查询，会导致全表扫描
(6)禁止大表使用JOIN查询，禁止大表使用子查询
解读：会产生临时表，消耗较多内存与CPU，极大影响数据库性能
(7)禁止使用OR条件，必须改为IN查询
解读：旧版本Mysql的OR查询是不能命中索引的，即使能命中索引，为何要让数据库耗费更多的CPU帮 助实施查询优化呢? (30)应用程序必须捕获SQL异常，并有相应处理
30.总结openvpn原理，并完成1键安装不同版本vpn脚本，可以适配rocky，ubuntu，centos主机，同时支持添加账号，注销账号
（1）利用阿里云搭建openvpn
#购买配置好阿里云服务器以后 *************************************** #连接公网地址进行登录 #[root@Rocky8 ~]#ssh 47.92.139.56 #默认selinux和firewalld已关闭 [root@openvpn-server ~]# getenforce Disabled [root@openvpn-server ~]# systemctl status firewalld ● firewalld.service - firewalld - dynamic firewall daemon Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled) Active: inactive (dead) Docs: man:firewalld(1) #配置一下基于key验证登录。这样每次连接就不用输密码了（用脚本实现） #!/bin/bash NET=172.30.0 passwd=xxxxxxxxx function color () { res_col=60 move_to_color="echo -en \e[${res_col}G" setcolor_success="echo -en \e[1;32m [ok]\n" setcolor_failure="echo -en \e[1;33m [failure]\n" setcolor_warning="echo -en \e[1;34m [warning]\n" setcolor_normal="echo -en \e[0m" echo -n "$1" && ${move_to_color} if [ $2 = "0" ];then ${setcolor_success} elif [ $2 = "1" ];then ${setcolor_failure} else ${setcolor_warning} fi ${setcolor_normal} } . /etc/os-release if [ $ID = "rocky" -o $ID = "centos" ];then echo -e "\e[1;32m系统是rocky或centos\e[0m" sleep 1 rpm -q sshpass&>/dev/null||yum -y install sshpass color "sshpass安装完成" "0" elif [ $ID = "ubuntu" ];then echo -e "\e[1;32m系统是ubuntu\e[0m" sleep 1 dpkg -l sshpass&>/dev/null||apt -y install sshpass color "sshpass安装完成" "0" fi } function set_key () { ssh-keygen -P "" -f /root/.ssh/id_rsa sshpass -p "$passwd" ssh-copy-id -o StrictHostKeyChecking=no `hostname -I` for i in {1,101,102};do { sshpass -p "$passwd" scp -r -o StrictHostKeyChecking=no /root/.ssh "$NET.$i": &>/dev/null }& done color "可以远程管理主机" "0" ******************************************************* #在web1云服务器上安装http服务。目标是让window直接打开拥有内部私网地址的web1服务器 [root@web1 ~]# yum -y install httpd ; systemctl enable --now httpd ; echo 'Welcome to Baoding' > /var/www/html/index.html #安装专业颁发CA证书的软件easy-rsa [root@openvpn-server ~]# yum -y install easy-rsa #epel源 #查看一下包含的文件列表 [root@openvpn-server ~]# rpm -ql easy-rsa /usr/share/easy-rsa/3 #里面有个可以帮我生成文件的脚本 /usr/share/doc/easy-rsa/vars.example #定义了证书在颁发时的有效期 #把/usr/share/easy-rsa/3的所有文件拷贝到自建的目录下 [root@openvpn-server ~]# mkdir -p /data/easy-rsa ; cp -r /usr/share/easy-rsa/3/* /data/easy-rsa #把/usr/share/doc/easy-rsa/vars.example文件拷贝到自建的目录下 [root@openvpn-server easy-rsa]# cp /usr/share/doc/easy-rsa/vars.example /data/easy-rsa/vars #这是目前/data/easy-rsa的目录结构 [root@openvpn-server easy-rsa]# tree . ├── easyrsa ├── openssl-easyrsa.cnf ├── vars └── x509-types ├── ca ├── client ├── code-signing ├── COMMON ├── email ├── kdc ├── server └── serverClient 1 directory, 11 files #修改证书有效期 [root@openvpn-server easy-rsa]# vim vars set_var EASYRSA_CA_EXPIRE 36500 #CA证书有效期 set_var EASYRSA_CERT_EXPIRE 3650 #给openvpn服务器颁发的证书有效期 #初始化PKI生成PKI相关目录和文件 [root@openvpn-server easy-rsa]# ./easyrsa init-pki #看一下目录结构 [root@openvpn-server easy-rsa]# 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 easy-rsa]# ./easyrsa build-ca nopass #给openvpn服务器创建证书申请文件 [root@openvpn-server easy-rsa]# ./easyrsa gen-req server nopass #给openvpn服务器颁发证书（确定的时候要敲yes） [root@openvpn-server easy-rsa]# ./easyrsa sign server server #生成德芙赫尔曼秘钥 [root@openvpn-server easy-rsa]# ./easyrsa gen-dh #给客户端创建证书申请文件 [root@openvpn-server easy-rsa]# ./easyrsa gen-req zhangyongbiao nopass #在修改一下证书有限期 [root@openvpn-server easy-rsa]# vim vars set_var EASYRSA_CERT_EXPIRE 180 #给客户端颁发的证书有限期 #给客户端颁发证书（确认时敲yes） [root@openvpn-server easy-rsa]# ./easyrsa sign client zhangyongbiao #安装openvpn软件 [root@openvpn-server easy-rsa]# yum -y install openvpn #创建cert来进行服务器端文件的归类 [root@openvpn-server easy-rsa]# mkdir /etc/openvpn/certs #把相应文件拷贝过来 [root@openvpn-server pki]# cp ca.crt dh.pem private/server.key issued/server.crt /etc/openvpn/certs/ #查看一下 [root@openvpn-server pki]# ll /etc/openvpn/certs/ total 20 -rw------- 1 root root 1204 Jan 13 23:15 ca.crt -rw------- 1 root root 424 Jan 13 23:15 dh.pem -rw------- 1 root root 4608 Jan 13 23:15 server.crt -rw------- 1 root root 1708 Jan 13 23:15 server.key #创建zhangyongbiao的文件夹来进行客户端文件的归类 [root@openvpn-server pki]# mkdir /etc/openvpn/client/zhangyongbiao #将ca.crt、zhangyongbiao.crt、zhangyongbiao.key文件拷贝到zhangyongbiao的文件夹中 [root@openvpn-server openvpn]# cp /etc/openvpn/certs/ca.crt /etc/openvpn/client/zhangyongbiao [root@openvpn-server zhangyongbiao]# cp /data/easy-rsa/pki/issued/zhangyongbiao.crt /etc/openvpn/client/zhangyongbiao [root@openvpn-server zhangyongbiao]# cp /data/easy-rsa/pki/private/zhangyongbiao.key /etc/openvpn/client/zhangyongbiao #查一下 [root@openvpn-server zhangyongbiao]# ll total 16 -rw------- 1 root root 1204 Jan 13 23:23 ca.crt -rw------- 1 root root 4507 Jan 13 23:25 zhangyongbiao.crt -rw------- 1 root root 1704 Jan 13 23:27 zhangyongbiao.key **************#以下部分是对openvpn服务器端的配置****************************** #先把样版配置文件拷贝到/etc/openvpn下修改一下就作为真正的配置文件用了 [root@openvpn-server openvpn]# cp /usr/share/doc/openvpn/sample/sample-config-files/server.conf /etc/openvpn/ #修改配置文件 [root@openvpn-server openvpn]# vim server.conf port 1194 #openvpn对外监听端口,另外修改一下阿里云安全组 proto tcp dev tun ca /etc/openvpn/certs/ca.crt #存放ca证书文件的路径 cert /etc/openvpn/certs/server.crt #存放服务器证书文件的路径 key /etc/openvpn/certs/server.key #存放服务器私钥的文件路径 dh /etc/openvpn/certs/dh.pem #存放德芙赫尔曼算法文件的路径 server 10.8.0.0 255.255.255.0 #这个是当客户端拨通vpn以后从vpn获取的一个网段 push "route 172.30.0.0 255.255.255.0" #推送进入vpn后面网段路由的记录 keepalive 10 120 cipher AES-256-CBC compress lz4-v2 push "compress lz4-v2" max-clients 2048 #最多连接vpn服务器的的客户端数量 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 openvpn]# mkdir /var/log/openvpn/ [root@openvpn-server openvpn]# chown openvpn. /var/log/openvpn/ [root@openvpn-server openvpn]# ll -d /var/log/openvpn/ drwxr-xr-x 2 openvpn openvpn 6 Jan 14 21:17 /var/log/openvpn/ #创建openvpn的service启动文件（因为rocky8安装openvpn没有启动文件） [root@openvpn-server openvpn]# 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 #重新加载service配置文件 [root@openvpn-server openvpn]# systemctl daemon-reload #启动openvpn服务（注意启动名字） [root@openvpn-server private]# systemctl enable --now openvpn@server #1194端口已开 [root@openvpn-server private]# ss -netl State Recv-Q Send-Q Local Address:Port Peer Address:Port Process LISTEN 0 128 0.0.0.0:22 0.0.0.0:* ino:26695 sk:1 <-> LISTEN 0 32 0.0.0.0:1194 0.0.0.0:* ino:47944 sk:2 <-> ********************以下部分是对openvpn客户端的配置*********************** [root@openvpn-server ~]# vim /etc/openvpn/client/zhangyongbiao/client.ovpn client dev tun proto tcp remote 47.92.219.209 1194 resolv-retry infinite nobind ca ca.crt cert zhangyongbiao.crt key zhangyongbiao.key remote-cert-tls server cipher AES-256-CBC verb 3 compress lz4-v2 #看一下这4个文件 [root@openvpn-server zhangyongbiao]# ls ca.crt client.ovpn zhangyongbiao.crt zhangyongbiao.key #把这4个文件打个包发给用户 [root@openvpn-server zhangyongbiao]# tar /root/zhangyongbiao.tar * [root@openvpn-server zhangyongbiao]# sz /root/zhangyongbiao.tar
（2）打开openvpn软件以后连接，出现绿色为已建立连接。
（3）在window客户端中，用cmd命令行输入ipconfig 会看到一个从openvpn服务器给客户端的一个虚拟10.8.0.6的IP地址。
（4）现在openvpn也有一个虚拟的10.8.0.1的IP地址
[root@openvpn-server ~]# ip a 3: 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（5）windows客户端也能直接连接openvpn服务器的私网地址172.30.0.1了，但是不能连接openvpn服务器后面的web1服务器。
（6）打开openvpn服务器的forword功能且生效。不过只实现了客户端到web1的单向通信，没有实现有去有回。因为从web1回去的数据报文没有经过openvpn，而是经过了阿里云给分配的路由器，但是客户端的10.8.0.6地址是openvpn给的虚拟地址，所以就不能到达客户端。
#开启openvpn的forword功能 [root@openvpn-server ~]# echo net.ipv4.ip_forward = 1 >> /etc/sysctl.conf #生效 [root@openvpn-server ~]# sysctl -p（7）可以添加路由，但是阿里云禁止添加和修改路由。所以只能修改openvpn服务器的iptable规则，用SNAT转换
[root@openvpn-server ~]# [root@openvpn-server ~]# iptables -t nat -A POSTROUTING -s 10.8.0.0/24 ! -d 10.8.0.0 -j MASQUERADE（8）到现在，客户端可以直接ping通web1和web2了。当然也可直接登录到web1和web2服务器了
（9）给新用户颁发证书（脚本实现）
#!/bin/bash read -p "请输入姓名:" NAME Application_crt () { cd /data/easy-rsa expect <<EOF spawn ./easyrsa gen-req ${NAME} nopass #无私钥密码认证 expect { ":" { send "yes\n" } } expect eof EOF } Generate_crt () { expect <<EOF spawn ./easyrsa sign client ${NAME} expect { ":" { send "yes\n" } } expect eof EOF } Application_crt Generate_crt echo -e "\e[1;32m已完成\e[0m"（10）吊销用户证书（脚本实现）
#!/bin/bash read -p "请输入姓名:" NAME Revoke_crt () { cd /data/easy-rsa expect <<EOF spawn ./easyrsa revoke ${NAME} expect { ":" { send "yes\n" } } expect eof EOF } echo "crl-verity /data/easy-rsa/pki/crl.pem" >> /etc/openvpn/server.conf systemctl restart openvpn@server Revoke_crt echo -e "\e[1;31m已完成\e[0m"（11）启用安全增强功能（基于秘钥登录）
*******************生成ta.key的文件**************************** [root@openvpn-server ~]#openvpn --genkey --secret /etc/openvpn/certs/ta.key ***************修改openvpn服务器端配置文件********************** [root@openvpn-server ~]#vim /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 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 ****************修改客户端（以xiao明为例）配置文件*************************** [root@openvpn-server ~]#vim /etc/openvpn/client/xiaoming/client.ovpn client dev tun proto tcp remote 47.92.74.0 1194 ca ca.crt cert xiaohong.crt key xiaohong.key remote-cert-tls server cipher AES-256-CBC verb 3 compress lz4-v2 tls-auth ta.key 1 **************************为xiaoming生成证书申请文件********************** [root@openvpn-server easy-rsa]#./easyrsa gen-req xiaoming （要输入两次密码） *************************为xiaoming颁发CA证书**************************** [root@openvpn-server easy-rsa]# ./easyrsa sign client xiaohong ***********发送以下4文件到客户端（C:\Program Files\OpenVPN\config）*********** xiaoming.crt xiaoming.key xiaoming.ovpn ta.key
（12）阿里云释放实例
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
