马哥教育第六周作业

最新推荐文章于 2024-10-06 21:49:48 发布

toomas

最新推荐文章于 2024-10-06 21:49:48 发布

阅读量211

点赞数

文章标签：数据库 mysql postgresql

本文链接：https://blog.csdn.net/toomas/article/details/128053588

版权

马哥教育第六周作业

一、完成将server和client端的mysql配置默认字符集为utf8mb4

[22:54:00 root@rocky8 ~]#vim /etc/my.cnf
[22:54:18 root@rocky8 ~]#cat /etc/my.cnf
[mysqld]
character-set-server=utf8mb4
[client]
default-character-set=utf8mb4

二、掌握如何获取SQL命令的帮助，基于帮助完成添加testdb库，字符集utf8, 排序集合utf8_bin

MariaDB [(none)]> help create database
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification] ...

create_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name

CREATE DATABASE creates a database with the given name. To use this
statement, you need the CREATE privilege for the database. CREATE
SCHEMA is a synonym for CREATE DATABASE.

URL: https://mariadb.com/kb/en/create-database/

MariaDB [(none)]>  create database testdb character set utf8 collate utf8_bin;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> show create database testdb；
+----------+----------------------------------------------------------------------------------+
| Database | Create Database                                                                  |
+----------+----------------------------------------------------------------------------------+
| testdb   | CREATE DATABASE `testdb` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */ |
+----------+----------------------------------------------------------------------------------+
1 row in set (0.000 sec)

三、总结mysql常见的数据类型。

数值型
整数型：

tinyint :1个字节 unsigned规定只为正数，0-255，默认正负128

小数型：

浮点型 float

定点数 decimal

字符型
char (定长)

varchar (不定长)

修饰符
适用所有类型

NULL ：数据中允许出现null

NOT NULL：数据不允许出现null

DEFAULT ：默认值

PRIMARY KEY ：主键，所有字段的值不能重复，且不能为null

UNIQUE KEY ：唯一键

CHARACTER SET name ：指定一个字符集

适用数值型

AUTO_INCREMENT :自动递增

UNSIGNED ：无符号

四、创建一个主机表host,放在testdb中，要求字段 1) 主键自增id 无符号, tinyint. 2) hostname可变字符长度256，可为空。。3）ip 可变字符长度256，可为空。4）账号，可变字符长度256，可为空。5）密码，可变字符长度256，可为空。6）创建时间，时间类型，非空。7）更新时间，时间类型，默认当前时间。8）区域，只能在华南，华北，华东，三个区域之一。9）端口，无符号整数，可为空。10）外网地址，可变字符长度256，可为空。11）内网地址，可变字符长度256，可为空。

MariaDB [(none)]> use testdb
Database changed
MariaDB root@10.0.0.28:testdb> create table host (id tinyint unsigned primary key auto_increment, hostname varchar(256) null,ip varcha
                            -> r(256) null,users varchar(256) null,passwords varchar(256) null,creationtime datetime not null,updateti
                            -> me datetime,region enum('south china','north china','east china'),ports tinyint unsigned null,interneta
                            -> ddress varchar(256) null,insidenetwork varchar(256) null);
Query OK, 0 rows affected
Time: 0.016s
MariaDB root@10.0.0.28:testdb> show tables
+------------------+
| Tables_in_testdb |
+------------------+
| host             |
+------------------+

1 row in set
Time: 0.006s
MariaDB root@10.0.0.28:testdb> desc `host`
+-----------------+------------------------------------------------+------+-----+---------+----------------+
| Field           | Type                                           | Null | Key | Default | Extra          |
+-----------------+------------------------------------------------+------+-----+---------+----------------+
| id              | tinyint(3) unsigned                            | NO   | PRI | <null>  | auto_increment |
| hostname        | varchar(256)                                   | YES  |     | <null>  |                |
| ip              | varchar(256)                                   | YES  |     | <null>  |                |
| users           | varchar(256)                                   | YES  |     | <null>  |                |
| passwords       | varchar(256)                                   | YES  |     | <null>  |                |
| creationtime    | datetime                                       | NO   |     | <null>  |                |
| updatetime      | datetime                                       | YES  |     | <null>  |                |
| region          | enum('south china','north china','east china') | YES  |     | <null>  |                |
| ports           | tinyint(3) unsigned                            | YES  |     | <null>  |                |
| internetaddress | varchar(256)                                   | YES  |     | <null>  |                |
| insidenetwork   | varchar(256)                                   | YES  |     | <null>  |                |
+-----------------+------------------------------------------------+------+-----+---------+----------------+

11 rows in set
Time: 0.009s

五、给testdb.host表中添加多条数据。

MariaDB root@10.0.0.28:testdb> insert into host (id , hostname,ip ,users,passwords ,creationtime ,updatetime,region ,ports,internetadd
                            -> ress ,insidenetwork ) values (1,'rocky01','10.0.0.8','root','123456','2022-11-28 15:30','2022-11-28 15:
                            -> 38','0','80','192.168.10.8','10.0.0.8');
Query OK, 1 row affected
Time: 0.006s
MariaDB root@10.0.0.28:testdb> insert into host (id , hostname,ip ,users,passwords ,creationtime ,updatetime,region ,ports,internetadd
                            -> ress ,insidenetwork ) values (2,'rocky02','10.0.0.28','root','123456',now(),now(),'1','53',
                            -> :'192.168.10.28','10.0.0.28');
Query OK, 1 row affected
Time: 0.004s
MariaDB root@10.0.0.28:testdb> select * from `host` 
+----+----------+-----------+-------+-----------+---------------------+---------------------+-------------+-------+-----------------+-
--------------+| id | hostname | ip        | users | passwords | creationtime        | updatetime          | region      | ports | internetaddress | 
insidenetwork |+----+----------+-----------+-------+-----------+---------------------+---------------------+-------------+-------+-----------------+-
--------------+| 1  | rocky01  | 10.0.0.8  | root  | 123456    | 2022-11-28 15:30:00 | 2022-11-28 15:38:00 |             | 80    | 192.168.10.8    | 
10.0.0.8      || 2  | rocky02  | 10.0.0.28 | root  | 123456    | 2022-11-28 15:39:00 | 2022-11-28 15:42:00 | south china | 53    | 192.168.10.28   | 
10.0.0.28     |+----+----------+-----------+-------+-----------+---------------------+---------------------+-------------+-------+-----------------+-
--------------+
2 rows in set
Time: 0.007s
MariaDB root@10.0.0.28:testdb>

六、根据表扩展出几个语句，完成总结DDL, DML的用法，并配上示例。

修改第一行region字段的值

MariaDB root@10.0.0.28:testdb> update `host` set region='3' where id=1
Query OK, 1 row affected
Time: 0.005s
MariaDB root@10.0.0.28:testdb> select * from `host`
+----+----------+-----------+-------+-----------+---------------------+---------------------+-------------+-------+-----------------+-
--------------+| id | hostname | ip        | users | passwords | creationtime        | updatetime          | region      | ports | internetaddress | 
insidenetwork |+----+----------+-----------+-------+-----------+---------------------+---------------------+-------------+-------+-----------------+-
--------------+| 1  | rocky01  | 10.0.0.8  | root  | 123456    | 2022-11-28 15:30:00 | 2022-11-28 15:38:00 | east china  | 80    | 192.168.10.8    | 
10.0.0.8      || 2  | rocky02  | 10.0.0.28 | root  | 123456    | 2022-11-28 15:39:00 | 2022-11-28 15:42:00 | south china | 53    | 192.168.10.28   | 
10.0.0.28     |+----+----------+-----------+-------+-----------+---------------------+---------------------+-------------+-------+-----------------+-
--------------+
2 rows in set
Time: 0.007s

删除内网地址字段

MariaDB root@10.0.0.28:testdb> alter table `host` drop insidenetwork;
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
Query OK, 0 rows affected
Time: 0.036s
MariaDB root@10.0.0.28:testdb> select * from `host`
+----+----------+-----------+-------+-----------+---------------------+---------------------+-------------+-------+-----------------+
| id | hostname | ip        | users | passwords | creationtime        | updatetime          | region      | ports | internetaddress |
+----+----------+-----------+-------+-----------+---------------------+---------------------+-------------+-------+-----------------+
| 1  | rocky01  | 10.0.0.8  | root  | 123456    | 2022-11-28 15:30:00 | 2022-11-28 15:38:00 | east china  | 80    | 192.168.10.8    |
| 2  | rocky02  | 10.0.0.28 | root  | 123456    | 2022-11-28 15:39:00 | 2022-11-28 15:42:00 | south china | 53    | 192.168.10.28   |
+----+----------+-----------+-------+-----------+---------------------+---------------------+-------------+-------+-----------------+

2 rows in set
Time: 0.006s

插入一行新数据

MariaDB root@10.0.0.28:testdb> insert into host (id , hostname,ip ,users,passwords ,creationtime ,updatetime,region ,ports,internetadd
                            -> ress  ) values (3 ,'rocky01','10.0.0.18','root','123456','2022-11-28 16:30','2022-11-28 16:30','0','80'
                            -> ,'192.168.10.8');
Query OK, 1 row affected
Time: 0.005s
MariaDB root@10.0.0.28:testdb> select * from `host`
+----+----------+-----------+-------+-----------+---------------------+---------------------+-------------+-------+-----------------+
| id | hostname | ip        | users | passwords | creationtime        | updatetime          | region      | ports | internetaddress |
+----+----------+-----------+-------+-----------+---------------------+---------------------+-------------+-------+-----------------+
| 1  | rocky01  | 10.0.0.8  | root  | 123456    | 2022-11-28 15:30:00 | 2022-11-28 15:38:00 | east china  | 80    | 192.168.10.8    |
| 2  | rocky02  | 10.0.0.28 | root  | 123456    | 2022-11-28 15:39:00 | 2022-11-28 15:42:00 | south china | 53    | 192.168.10.28   |
| 3  | rocky01  | 10.0.0.18 | root  | 123456    | 2022-11-28 16:30:00 | 2022-11-28 16:30:00 |             | 80    | 192.168.10.8    |
+----+----------+-----------+-------+-----------+---------------------+---------------------+-------------+-------+-----------------+

3 rows in set
Time: 0.007s
MariaDB root@10.0.0.28:testdb>

七、导入hellodb库，总结DQL, alias, where子句，gruop by, order by, limit, having使用示例。

导入hellodb库

[root@localhost ~]$rz -E
rz waiting to receive.
[root@localhost ~]$ls
anaconda-ks.cfg  hellodb_innodb.sql  Python-2.6          Python-3.7.7      reset2.sh
fact.sh          menu.sh             Python-2.6.tar.bz2  Python-3.7.7.tgz  reset.sh
[root@localhost ~]$mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 20
Server version: 10.3.28-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

(root@localhost) [(none)]> create database hellodb;
Query OK, 1 row affected (0.001 sec)
(root@localhost) [(none)]> exit;
Bye
[root@localhost ~]$mysql -uroot -p123456 --default-character-set=utf8mb4 hellodb < /root/hellodb_innodb.sql

示例：

#alias
mysql> select Stuid 学号,Name 姓名,Age 年龄 from students;
 
#where
mysql> select * from students where Age <= 20;
 
mysql> select * from students where Age <= 20 and gender = 'F';
 
mysql> select * from students where Age <= 20 or gender = 'F';
 
mysql> select * from students where classid in (1,2,3);
 
mysql> select * from students where age between 20 and 30;
 
#limit 显示前几个
mysql> select * from students limit 3;
 
mysql> select * from students limit 2,3;
 
mysql> select * from students where classid is null;
 
mysql> select * from students where name = 'ma chao';
 
mysql> select * from students where name like 'x%';
 
 
mysql> select distinct age from students; #去重
 
mysql> select avg(age) from students;     #平均年龄
 
 
#分组 group by
mysql> select gender,avg(age) from students group by gender; #分别计算男生和女生平均年龄
 
mysql> select gender,avg(age) from students group by gender having gender = 'M';
 
mysql> select gender,avg(age) from students where gender = 'F' group by gender;
 
mysql> select classid,gender,avg(age) from students group by classid,gender having classid is not null;
 
mysql> select gender,max(age) from students group by gender;
 
#排序 order by
mysql> select * from students order by age; #正序
 
mysql> select * from students order by age desc; #倒序
 
mysql> select * from students order by age desc,classid; #按年龄倒序排序，年龄一样时按班级排序
 
mysql> select * from students order by age desc limit 10; #挑出年龄最大前十个

八、基于hellodb 库, 总结子查询，关联查询，交叉连接，内连接，左连接，右连接，完全连接，自连接。

1、子查询

update teachers set age=(select max(age) from students) where tid=3;
 
select * from teachers where age = (select max(age) from students);

2、关联查询

mysql> select stuid,name,age,gender from students union select * from teachers;

3、交叉连接

select * from students cross join teachers;

4、内连接

mysql> select t.tid,t.name,s.stuid,s.name from students s inner join teachers t on s.stuid=t.tid ;
 
mysql> select st.stuid,st.name,sc.score,co.course from students st inner join scores sc on sc.stuid=st.stuid inne

5、左连接

select * from students s left outer join teachers t on s.teacherid=t.tid；

6、右连接

select * from students s right outer join teachers t on s.teacherid=t.tid ;

7、完全连接

mysql> 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 ;

8、自连接

mysql> select e.name emp_name,IFNULL(l.name,'无上级') emp_leader from emp e left outer join emp l on e.leaderiid=l.id;

九、总结select语句处理顺序。

在这里插入图片描述

MySQL 整个查询执行过程，总的来说分为 6 个步骤 :

SQL执行步骤：请求、缓存、SQL解析、优化SQL查询、调用引擎执行，返回结果
1、连接：客户端向 MySQL 服务器发送一条查询请求，与connectors交互：连接池认证相关处理。
2、缓存：服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果，否则进入下一阶段
3、解析：服务器进行SQL解析(词法语法)、预处理。
4、优化：再由优化器生成对应的执行计划。
5、执行：MySQL 根据执行计划，调用存储引擎的 API来执行查询。
6、结果：将结果返回给客户端，同时缓存查询结果。

SELECT语句执行流程

FROM Clause --> WHERE Clause --> GROUP BY–> HAVING Clause --> SELECT --> ORDER BY --> LIMIT

十、总结mysql事件管理,用户管理，权限管理。

#创建用户
mysql> create user wang@'10.0.0.%' identified by '123456';
#更改密码
mysql> alter user wang@'10.0.0.%' identified by '654321';
#授权
mysql> grant all on testdb.* to wang@'10.0.0.%';
#刷新权限
flush privileges;

十一基于apache, php, mysql搭建wordpress站点。

192.168.3.7   客户机
 
 
192.168.3.48 实现主服务器DNS
yum -y install bind
vim /etc/named.conf
options {    
//      listen-on port 53 { 127.0.0.1; };   #注释掉这两行
//      allow-query     { localhost; }; 
 
vim /etc/named.rfc1912.zones 
#添加以下内容
zone "gao.com" {
        type master;
        file "gao.com.zone";
};
 
vim /var/named/gao.com.zone
$TTL 1D
@            IN   SOA  master   admin.magedu.org ( 1   ; serial
                                                   1D  ; refresh
                                                   1H ; retry
                                                   1W  ; expire
                                                   3H )   ; minimum
                  NS master
 
master            A  192.168.3.48
blog              A  192.168.3.8
 
chmod 640 /var/named/gao.com.zone
chgrp named /var/named/gao.com.zone
systemctl start named
 
 
#防火墙
eth0:10.0.0.8      
eth1:192.168.3.8   
[root@firewall ~]#iptables -t nat -A PREROUTING -d 192.168.10.8 -p tcp --dport 80 -j DNAT --to-destination 10.0.0.7:80       #DNAT
 
 
eth0:10.0.0.58   lmp wordpress
在wordpress官网下载压缩包
unzip -d wordpress-6.1.1-zh_CN.zip -d /opt/
 
cd /opt/wordpress
 
yum -y install httpd php php-mysqlnd php-json
 
systemctl enable --now httpd
 
mv * /var/www/html/
 
chown -R apache. /var/www/html/
 
mysql -u wordpress -h 10.0.0.68 -p'123456'
 
 
 
eth0:10.0.0.68 mysql
create user wordpress@'10.0.0.%' identified by '123456';
grant all on wordpress.* to wordpress@'10.0.0.%'

十二、总结mysql架构原理

1、MySQL整体逻辑架构(MySQL’s Logical Architecture)
在这里插入图片描述
第一层：连接层，所包含的服务并不是MySQL所独有的技术。它们都是服务于C/S程序或者是这些程序所需要的：连接处理，身份验证，安全性等等。

第二层：核心服务层。这是MySQL的核心部分。通常叫做 SQL Layer。在 MySQL据库系统处理底层数据之前的所有工作都是在这一层完成的，包括权限判断， sql解析，行计划优化， query cache 的处理以及所有内置的函数(如日期,时间,数学运算,加密)等等。各个存储引擎提供的功能都集中在这一层，如存储过程，触发器，视图等。

第三层：存储引擎层。通常叫做StorEngine Layer ，也就是底层数据存取操作实现部分，由多种存储引擎共同组成。它们负责存储和获取所有存储在MySQL中的数据。就像Linux众多的文件系统一样。每个存储引擎都有自己的优点和缺陷。服务器是通过存储引擎API来与它们交互的。这个接口隐藏了各个存储引擎不同的地方。对于查询层尽可能的透明。这个API包含了很多底层的操作。如开始一个事物，或者取出有特定主键的行。存储引擎不能解析SQL，互相之间也不能通信。仅仅是简单的响应服务器的请求。

第四层：数据存储层。主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

在这里插入图片描述
1.Connectors
指的是不同语言中与SQL的交互，如php、java等。

2 Management Serveices & Utilities：
系统管理和控制工具

3 Connection Pool: 连接池
管理缓冲用户连接，线程处理等需要缓存的需求。

负责监听对 MySQL Server 的各种请求，接收连接请求，转发所有连接请求到线程管理模块。每一个连接上 MySQL Server 的客户端请求都会被分配（或创建）一个连接线程为其单独服务。而连接线程的主要工作就是负责 MySQL Server 与客户端的通信，
接受客户端的命令请求，传递 Server 端的结果信息等。线程管理模块则负责管理维护这些连接线程。包括线程的创建，线程的 cache 等。

4 SQL Interface: SQL接口。
接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface

5 Parser: 解析器。
SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本。

在 MySQL中我们习惯将所有 Client 端发送给 Server 端的命令都称为 query ，在 MySQL Server 里面，连接线程接收到客户端的一个 Query 后，会直接将该 query 传递给专门负责将各种 Query 进行分类然后转发给各个对应的处理模块。
主要功能：
a . 将SQL语句进行语义和语法的分析，分解成数据结构，然后按照不同的操作类型进行分类，然后做出针对性的转发到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。
b. 如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的

6 Optimizer: 查询优化器。
SQL语句在查询之前会使用查询优化器对查询进行优化。就是优化客户端请求的 query（sql语句），根据客户端请求的 query 语句，和数据库中的一些统计信息，在一系列算法的基础上进行分析，得出一个最优的策略，告诉后面的程序如何取得这个 query 语句的结果

他使用的是“选取-投影-联接”策略进行查询。
用一个例子就可以理解： select uid,name from user where gender = 1;
这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤
这个select查询先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤
将这两个查询条件联接起来生成最终查询结果

7 Cache和Buffer：查询缓存。
Cache：主要功能是将客户端提交给MySQL 的 Select 类 query 请求的返回结果集 cache 到内存中，与该 query 的一个 hash 值做一个对应。该 Query 所取数据的基表发生任何数据的变化之后， MySQL 会自动使该 query 的Cache 失效。在读写比例非常高的应用系统中， Query Cache 对性能的提高是非常显著的。当然它对内存的消耗也是非常大的。

如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等

buffer与cache的区别？
缓存那里实际上有buffer和cache两个，那它们之间是否有什么不同呢？简单的说就是，buffer是写缓存，cache是读缓存。

8 、存储引擎接口
存储引擎接口模块可以说是 MySQL 数据库中最有特色的一点了。目前各种数据库产品中，基本上只有 MySQL 可以实现其底层数据存储引擎的插件式管理。这个模块实际上只是一个抽象类，但正是因为它成功地将各种数据处理高度抽象化，才成就了今天 MySQL 可插拔存储引擎的特色。

 从图2还可以看出，MySQL区别于其他数据库的最重要的特点就是其插件式的表存储引擎。MySQL插件式的存储引擎架构提供了一系列标准的管理和服务支持，这些标准与存储引擎本身无关，可能是每个数据库系统本身都必需的，如SQL分析器和优化器等，而存储引擎是底层物理结构的实现，每个存储引擎开发者都可以按照自己的意愿来进行开发。
注意：存储引擎是基于表的，而不是数据库。

十三、总结myisam和Innodb存储引擎的区别。

Innodb 引擎特点
行级锁
可缓存数据和索引
支持外键
支持MVCC(多版本并发控制机制)
支持事务
最大支持64TB

MySIAM
表级锁
不能缓存数据和索引
不支持外键
不支持MVCC
不支持事务

十四、总结mysql索引作用，同时总结哪些查询不会使用到索引。

索引作用
索引可以降低服务所需要扫描的数据量，减少了IO次数

索引可以帮助服务器避免排序和使用临时表

索引可以帮助将随机I/O转为顺序I/O

查询到的记录很多会不使用索引

查询无索引的字段不使用索引

查询姓名包含x,或以x结尾不使用索引，只有左前缀使用索引

十五、总结事务ACID事务特性

A:atomicity 原子性：整个事务中的所有操作要么全部成功执行，要么全部失败回滚
C:consitstency 一致性：数据库总是从一个一致性状态转换到另一个一致性状态，类似能量守恒状态
I:isolation 隔离性：一个事务所做出的操作再提交之前，是不能被其他事务所见
D:durability 持久性：一旦事务提交，其所做的修改永久保存在数据库中

十六、总结事务日志工作原理。

redo log: 记录某数据块被修改后的值，数据更新前先记录redo log(WALWrite Ahead Log),可以用来恢复未写入data file的已成功事务更新的数据

undo log: 保存和执行的操作相反的操作，即记录某数据被修改前的值，可以用来事务失败时进行rollback

十七、总结mysql日志类型，并说明如何启动日志。

事务日志： transaction log

vim /etc/my.cnf
 
[mysqld]
innodb_flush_log_at_trx_commit=0
innodb_log_file_size=500000000
innodb_log_files_in_group=3
innodb_log_group_home_dir=/data/tran_logs

错误日志： error log

vim /etc/my.cnf
 
[mysqld]
log_error=/data/log_error/mysqld.log
 
mkdir /data/log_error/ -p;chown mysql.mysql /data/log_error
systemctl restart mysqld

通用日志：general log

 
 
vim /etc/my.cnf
 
[mysqld]
general_log=on
 
systemctl restart mysqld

慢查询日志： slow query log

vim /etc/my.cnf
 
[mysqld]
slow_query_log=on
log_queries_not_using_indexes=on
 
systemctl restart mysqld

二进制日志：binary log

[root@firewall ~]#vim /etc/my.cnf
 
[mysqld]
#innodb_flush_log_at_trx_commit=0
#innodb_log_file_size=500000000
#innodb_log_files_in_group=3
#innodb_log_group_home_dir=/data/tran_logs
 
#log_error=/data/log_error/mysqld.log
#general_log
#slow_query_log
log_bin=/data/binlog/mysql-bin
sql_log_bin=on
 
[root@firewall ~]#mkdir -p  /data/binlog/
[root@firewall ~]#chown mysql. /data/binlog/
[root@firewall ~]#systemctl restart mysqld

十八、总结二进制日志的不同格式的使用场景。

set sql_log_bin=0 #还原数据或导入大批量的测试数据时
set sql_log_bin=1 #数据库正常工作时
 
binlog_format=ROW

基于“语句”记录：statement,记录语句，日志量较少，节约空间。

基于“行”记录：row，记录数据，日志量较大，更加安全，有助于还原数据。

混合模式：mixed,让系统自行判断基于哪种方式进行

十九、总结mysql备份类型，并基于mysqldump, xtrabackup完成数据库备份与恢复验证。

完全备份：整个数据集

部分备份：只备份数据子集

增量备份：仅备份最近一次完全备份或增量备份以来变化的数据，备份较快，还原复杂

差异备份：仅备份最近一次完全备份以来变化的数据，备份较慢，还原简单

冷、温、热备份

冷备：读、写操作均不可进行，数据库停止服务

温备：读操作可执行，但写操作不可执行

热备：读、写操作均可执行

物理和逻辑备份

物理备份：直接复制数据文件进行备份，与存储引擎有关，占用较多的空间，速度快

逻辑备份：从数据库中‘导出’数据另存而进行的备份，与存储引擎无关，占用空间少，速度慢
mysqldump实现备份还原

 10.0.0.8
 173  2022-11-23 16:18:57 mysqldump -uroot -F -A --single-transaction --master-data=2 > /data/full_`date +%F`.sql;
  175  2022-11-23 16:19:45 vim /data/full_`date +%F`.sql
  180  2022-11-23 16:22:00 mysql hellodb;
  mysql> insert students (name,age,gender) values('libai',33,'M');
 
  mysql> insert students (name,age,gender) values('yasuo',63,'M');
 
  mysql> insert students (name,age,gender) values('kate',22,'F');
 
  mysql> flush logs;
 
  mysql> insert students (name,age,gender) values('jack',42,'M');
 
 
  mysql> insert students (name,age,gender) values('rose',26,'F');
 
  mysql> drop table students;
 
  mysql> insert teachers (name,age,gender) values('zz',26,'F');
 
  mysql> insert teachers (name,age,gender) values('giao',23,'M');
 
  182  2022-11-23 16:32:41 mysqlbinlog --start-position=156 /data/binlog/mysql-bin.000011
  183  2022-11-23 16:33:03 mysqlbinlog --start-position=156 /data/binlog/mysql-bin.000011 > /data/inc.sql
  184  2022-11-23 16:33:50 mysqlbinlog /data/binlog/mysql-bin.000012 >> /data/inc.sql
  185  2022-11-23 16:34:05 ll /data
  186  2022-11-23 16:34:38 grep drop /data/inc.sql 
  187  2022-11-23 16:34:53 grep -i drop /data/inc.sql 
  188  2022-11-23 16:36:11 sed -n '/DROP TABLE `students` /p' /data/inc.sql
  189  2022-11-23 16:36:30 sed -i '/DROP TABLE `students` /d' /data/inc.sql
  190  2022-11-23 16:36:36 grep -i drop /data/inc.sql 
  191  2022-11-23 16:36:49 sed -n '/DROP TABLE `students` /p' /data/inc.sql
  192  2022-11-23 16:37:59 scp /data/* 10.0.0.68:/data

10.0.0.68
[root@rocky8 ~]#mysql -uroot -p123456
mysql> set sql_log_bin=0;
mysql> source /data/full_2022-11-23.sql;
mysql> set sql_log_bin=1;

[root@firewall ~]#mysqldump -uroot -p -hlocalhost -B hellodb > /backup/hellodb_B.sql
Enter password: 
[root@firewall ~]#ll /backup/*
-rw-r--r-- 1 root root    7799 Nov 23 10:06 /backup/hellodb_B.sql
-rw-r--r-- 1 root root 4266136 Nov 23 10:04 /backup/hellodb.sql
 
mysql> drop database hellodb;
Query OK, 7 rows affected (0.02 sec)
 
mysql> \. /backup/hello_B.sql

xtrabackup完成数据库备份与恢复验证

10.0.0.8 原主机
 
tar xvf Percona-XtraBackup-8.0.30-23-r873b467185c-el8-x86_64-bundle.tar 
yum -y install percona-xtrabackup-80-8.0.30-23.1.el8.x86_64.rpm
 
mkdir /backup1
xtrabackup -uroot -p123456 --backup --target-dir=/backup1/base

10.0.0.68 目标主机
[root@rocky8 /]#tar xvf Percona-XtraBackup-8.0.30-23-r873b467185c-el8-x86_64-bundle.tar
 
[root@rocky8 /]#yum -y install percona-xtrabackup-80-8.0.30-23.1.el8.x86_64.rpm
 
[root@rocky8 /]#xtrabackup --prepare --target-dir=/backup1/base
 
 
[root@rocky8 /]#systemctl stop mysqld
 
 
[root@rocky8 mysql]#xtrabackup --copy-back --target-dir=/backup1/base
 
[root@rocky8 mysql]#chown -R mysql:mysql /var/lib/mysql
[root@rocky8 mysql]#systemctl start mysqld

二十、编写crontab，每天按表备份所有mysql数据。将备份数据放在以天为时间的目录下。

[root@firewall ~]#vim backup.sh
 
#!/bin/bash
DIR=/backup
TIME=`date +%F`
TIMEA=`date +%F_%H-%M-%S`
pass=808356
B=hellodb
mkdir ${DIR}/${TIME}
 
mysqldump -uroot   $B students | gzip > ${DIR}/${TIME}/${B}.students_${TIMEA}.sql.g
 
chmod +x backup.sh
[root@firewall ~]#crontab -e
 
8 14 * * * /root/backup.sh
 
 
[root@firewall 2022-11-23]#ll
-rw-r--r-- 1 root root 1114 Nov 23 14:08 hellodb.students_2022-11-23_14-08-01.sql.gz

二十一、编写crontab, 基于xtrabackup，每周1，周5进行完全备份，周2到周4进行增量备份。

[root@firewall backup]#crontab -e
 
0 14 * * 1,5   /usr/bin/xtrabackup -uroot -p123456 --backup --target-dir=/backup/base
0 15 * * 2     /usr/bin/xtrabackup -uroot -p123456 --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
0 15 * * 3     /usr/bin/xtrabackup -uroot -p123456 --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1
0 15 * * 4     /usr/bin/xtrabackup -uroot -p123456 --backup --target-dir=/backup/inc3 --incremental-basedir=/backup/inc2

二十二、总结mysql主从复制原理。

在这里插入图片描述
主节点开启二进制，

当主节点收到写操作，数据库更新数据，生成二进制，写进二进制日志中，自动开启dump线程，实时把主节点的二进制日志发送给从节点，从节点事先开启IO线程，IO线程接受从主节点dump线程发送的二进制日志，写入relay log,sql线程把relay log中数据读入，从而应用在数据库中，使数据库数据进行更新。

二十三、实现mysql主从复制，主主复制，半同步复制，过滤复制，

主从复制

10.0.0.8 主节点
[root@firewall ~]#vim /etc/my.cnf
 
[mysqld]
log_bin=/data/binlog/mysql-bin
server-id=8
 
systemctl restart mysqld.service 
 
mysql
 
mysql> create user repluser@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
 
mysql> grant replication slave on *.* to repluser@'10.0.0.%';
Query OK, 0 rows affected (0.01 sec)
 
[root@firewall ~]#mysqldump -uroot -p -A -F --master-data --single-transaction > /backup/full_`date +%F`.sql
 
[root@firewall ~]#scp /backup/full_2022-11-23.sql 10.0.0.68:/backup

10.0.0.68 slave68
[root@rocky8 /]#vim /etc/my.cnf
[mysqld]
server-id=68
read-only
 
[root@rocky8 /]#systemctl restart mysqld.service 
 
vim /backup/full_2022-11-23.sql
CHANGE MASTER TO
MASTER_HOST='10.0.0.8',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=156;
 
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
 
mysql> source /backup/full_2022-11-23.sql
 
mysql> set sql_log_bin=1;
 
mysql> start slave;

主主复制

10.0.0.8 主节点
[root@firewall ~]#vim /etc/my.cnf
 
[mysqld]
log_bin=/data/binlog/mysql-bin
server-id=8
 
systemctl restart mysqld.service 
 
mysql
 
mysql> create user repluser@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
 
mysql> grant replication slave on *.* to repluser@'10.0.0.%';
Query OK, 0 rows affected (0.01 sec)
 
[root@firewall ~]#mysqldump -uroot -p -A -F --master-data --single-transaction > /backup/full_`date +%F`.sql
 
[root@firewall ~]#scp /backup/full_2022-11-23.sql 10.0.0.68:/backup
 
 
mysql> CHANGE MASTER TO 
    -> MASTER_HOST='10.0.0.68',
    -> MASTER_USER='repluser',
    -> MASTER_PASSWORD='123456',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='mysql-bin.00003', MASTER_LOG_POS=156;
Query OK, 0 rows affected, 9 warnings (0.03 sec)
 
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

10.0.0.68 slave68
[root@rocky8 /]#vim /etc/my.cnf
[mysqld]
server-id=68
read-only
 
[root@rocky8 /]#systemctl restart mysqld.service 
 
vim /backup/full_2022-11-23.sql
CHANGE MASTER TO
MASTER_HOST='10.0.0.8',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=156;
 
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
 
mysql> source /backup/full_2022-11-23.sql
 
mysql> set sql_log_bin=1;
 
mysql> start slave;

半同步复制

10.0.0.8
[root@firewall data]#mysqldump -uroot -p -A --single-transaction --source-data=1 > /data/all.sql
 
[root@firewall ~]#cd /data/
 
[root@firewall data]#scp /data/all.sql 10.0.0.58:/data
 
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
 
[root@firewall data]#vim /etc/my.cnf
 
[mysqld]
log_bin=/data/binlog/mysql-bin
server-id=8
rpl_semi_sync_master_enabled
rpl_semi_sync_master_timeout=3000
 
[root@firewall ~]#systemctl restart mysqld.service 
 
mysql> SHOW GLOBAL VARIABLES LIKE '%semi%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 3000       |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.01 sec)
 
mysql> SHOW status LIKE '%semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 2     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)

[root@rocky8 ~]#vim /etc/my.cnf
 
[mysqld]
server-id=58
rpl_semi_sync_slave_enabled
 
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
 
[root@rocky8 ~]#vim /data/all.sql 
 
CHANGE MASTER TO
MASTER_HOST='10.0.0.8',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000017', MASTER_LOG_POS=156;
 
[root@rocky8 data]#mysql < all.sql
 
mysql> set global rpl_semi_sync_slave_enabled=1;
 
mysql> start slave;
 
[root@rocky8 ~]#scp /data/all.sql 10.0.0.68:/data

[root@rocky8 ~]#vim /etc/my.cnf
 
[mysqld]
server-id=68
rpl_semi_sync_slave_enabled
 
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
 
[root@rocky8 data]#systemctl restart mysqld.service
 
[root@rocky8 data]#mysql < all.sql
 
mysql> start slave;

过滤复制

mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
 
mysql> create database db2;
Query OK, 1 row affected (0.00 sec)
 
mysql> use db1
Database changed
mysql> create table t1;
ERROR 4028 (HY000): A table must have at least one visible column.
mysql> create table t1(id int);
Query OK, 0 rows affected (0.01 sec)
 
mysql> create database db3;
Query OK, 1 row affected (0.00 sec)
 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| db2                |
| db3                |
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
| wordpress          |
| zabbix             |

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| db2                |
| db3                |
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
| wordpress          |
| zabbix             |
+--------------------+
11 rows in set (0.01 sec)

 
[root@rocky8 data]#vim /etc/my.cnf
 
[mysqld]
server-id=68
rpl_semi_sync_slave_enabled
replicate_do_db=db1
replicate_do_db=db2
 
[root@rocky8 data]#systemctl restart mysqld.service     
 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| db2                |
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
| wordpress          |
| zabbix             |
+--------------------+

二十四、总结GTID复制原理，并完成GTID复制集群。

GTID复制原理
在这里插入图片描述
开启GTI功能可以支持多DUMP线程的并发复制，而且在MYSQL5.6实现了基于库级别多SQL线程并发。在MySQL5.7利用GTID的Logic clock逻辑时钟。保证了同库级别下的事务顺序问题。即可以实现基于事务级别的并发回放。从而大大减少了同步的延迟。

同时GTID具有幂等性特性，即多次执行结果是一样的。

10.0.0.8
[root@firewall data]#vim /etc/my.cnf
 
[mysqld]
log_bin=/data/binlog/mysql-bin
server-id=8
gtid_mode=on
enforce_gtid_consistency
[root@firewall data]#systemctl restart mysqld.service

10.0.0.58
[root@rocky8 data]#vim /etc/my.cnf
 
[mysqld]
server-id=58
gtid_mode=on
enforce_gtid_consistency
[root@rocky8 ~]#systemctl restart mysqld.service
 
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
mysql> reset slave all;
Query OK, 0 rows affected, 1 warning (0.01 sec)
 
mysql> CHANGE MASTER TO 
    -> MASTER_HOST='10.0.0.8',
    -> MASTER_USER='repluser',
    -> MASTER_PASSWORD='123456',
    -> MASTER_PORT=3306,
    -> MASTER_AUTO_POSITION=1;
 
mysql> show slave status\G;
 
mysql> start slave;
 
mysql> show slave status\G;

二十五、总结主从复制不一致的原因，如何解决不一致，如何避免不一致

主从复制不一致原因
主库binlog格式为statement，同步到库执行后可能造成主从不一致。

主库执行更改前有执行set sql_log_bin=0,会使主库不记录binlog,从库也无法变更这部分数据

从节点未设置只读，误操作写入数据

主库或从库意外宕机，宕机可能会造成binlog或者relaylog文件出现损坏，导致主从不一致

主从实例版本不一致，特别是高版本是主，低版本为从的情况下，主数据库上面支持的功能，从数据库上面可能不支持该功能

主从sql_mode不一致

MySQL自身bug导致

主从不一致修复方法
将从库重新实现

使用percona-toolkid工具辅助

手动重建不一致的表

如何避免主从不一致
主库binlog采用ROW格式

主从实例数据库版本保持一致

主库做好账号权限把控，不可以执行set sql_log_bin=0

从库开启只读，不允许人为写入

定期进行主从一致性检验

二十六、总结数据库水平拆分和垂直拆分

水平拆分
在这里插入图片描述

水平切分是按照某个字段的某种规则来分散到多个库之中，每个表包含一部分数据，简单来说，我们可以将数据的水平拆分理解为是按照数据行的切分，就是将表中的某些行切分到一个数据库，而另外的行又切分到其他数据库中
优点：

拆分规则抽象良好，join操作基本都可以数据库完成

不存在单库大数据，高并发的性能瓶颈

应用端改造较少

提高了系统的稳定性和负载能力

缺点：

拆分规则难以抽象

分片事务一致性难以解决

数据多次扩展跟维护量极大

跨库join性能较差

共同的缺点

引入分布式事务的问题

跨节点join的问题

跨节点合并排序分页问题

多数据源管理问题

垂直拆分
在这里插入图片描述

垂直切分优缺点

优点：

拆分后业务清晰，拆分规则明确

系统之间整合或扩展容易

数据维护简单

缺点：

部分业务表无法join，只能通过接口方式解决，提高了系统复杂度

受每种业务不同的限制存在单库性能瓶颈，不易数据扩展和性能提高

事务处理复杂

二十七、基于mycat实现读写分离

已经实现主从复制10.0.0.8master,10.0.0.58 slave
10.0.0.8 master

mysql> create user admin@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
 
mysql> grant all on hellodb.* to admin@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
 
mysql> select @@general_log
    -> ;
+---------------+
| @@general_log |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)
 
mysql> set global general_log=1;

10.0.0.58 slave

mysql> set global general_log=1;

10.0.0.28 mycat

[root@mycat ~]#mkdir /apps
[root@mycat ~]#ll
-rw-r--r--  1 root root 21760812 May 11  2022 Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz
[root@mycat ~]#tar xf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /apps
 
[root@mycat ~]#vi /etc/profile.d/mycat.sh
 
#!/bin/bash
PATH=/apps/mycat/bin:$PATH
[root@mycat ~]#. /etc/profile.d/mycat.sh
[root@mycat ~]#free -h
              total        used        free      shared  buff/cache   available
Mem:          2.7Gi       221Mi       2.1Gi       8.0Mi       420Mi       2.3Gi
Swap:         2.0Gi          0B       2.0Gi
[root@mycat ~]#yum -y install java
[root@mycat ~]#mycat start
[root@mycat ~]#tail /apps/mycat/logs/wrapper.log 
STATUS | wrapper  | 2022/11/24 17:14:59 | --> Wrapper Started as Daemon
STATUS | wrapper  | 2022/11/24 17:14:59 | Launching a JVM...
INFO   | jvm 1    | 2022/11/24 17:15:00 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO   | jvm 1    | 2022/11/24 17:15:00 |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
INFO   | jvm 1    | 2022/11/24 17:15:00 | 
INFO   | jvm 1    | 2022/11/24 17:15:01 | MyCAT Server startup successfully. see logs in logs/mycat.log
[root@mycat ~]#vim /apps/mycat/conf/server.xml 
                <property name="handleDistributedTransactions">0</property>
                <property name="serverPort">3306</property> <property name="managerPort">9066</property>
 
[root@mycat ~]#vim /apps/mycat/conf/schema.xml
 
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
    </schema>
    <dataNode name="dn1" dataHost="localhost1" database="hellodb" />
    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="host1" url="10.0.0.8:3306" user="admin"
                   password="123456">
         <readHost host="host2" url="10.0.0.58:3306" user="admin" password="123456" />
        </writeHost>
    </dataHost>
</mycat:schema>

10.0.0.38 client

[root@localhost yum.repos.d]#yum -y install mariadb
[root@localhost yum.repos.d]#mysql -uroot -p123456 -h10.0.0.28
MySQL [TESTDB]> select @@server_id
    -> ;
+-------------+
| @@server_id |
+-------------+
|          58 |
+-------------+
1 row in set (0.01 sec)
MySQL [TESTDB]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | zz            |  26 | F      |
|   6 | giao          |  23 | M      |
|   7 | lang          |  45 | M      |
|   8 | zhaozilong    |  33 | M      |
|   9 | liuan         |  91 | M      |
|  10 | a             |  91 | M      |
|  11 | a             |  91 | M      |
+-----+---------------+-----+--------+
 
MySQL [TESTDB]> update teachers set age=@@server_id where tid=11;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
MySQL [TESTDB]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | zz            |  26 | F      |
|   6 | giao          |  23 | M      |
|   7 | lang          |  45 | M      |
|   8 | zhaozilong    |  33 | M      |
|   9 | liuan         |  91 | M      |
|  10 | a             |  91 | M      |
|  11 | a             |   8 | M      |
+-----+---------------+-----+--------+
11 rows in set (0.01 sec)

二十八、总结mysql高可用方案及高可用级别，搭建MHA集群和galera cluster，尝试搭建TIDB集群。

Mysql 高可用级别
在这里插入图片描述
MHA集群
10.0.0.7 manager

yum -y install mha4mysql-manager-0.58-0.el7.centos.noarch.rpm mha4mysql-node-0.58-0.el7.centos.noarch.rpm
ssh 10.0.0.8
ssh 10.0.0.58
ssh 10.0.0.68
ssh 10.0.0.7
 
ssh-keygen
ssh-copy-id 127.0.0.1
 
rsync -a .ssh 10.0.0.8:/root/
rsync -a .ssh 10.0.0.58:/root/
rsync -a .ssh 10.0.0.68:/root/
 
mkdir /etc/mastermha/app1.conf
user=mhauser
password=magedu
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.8
candidate_master=1
[server2]
hostname=10.0.0.58
[server3]
hostname=10.0.0.68
candidate_master=1
 
masterha_check_ssh --conf=/etc/mastermha/app1.cnf
Fri Nov 25 15:09:50 2022 - [info] All SSH connection tests passed successfully.
 
masterha_check_repl --conf=/etc/mastermha/app1.cnf
MySQL Replication Health is OK.
 
masterha_manager --conf=/etc/mastermha/app1.cnf --remove_dead_master_conf  --ignore_last_failover
 
[root@Centos ~]#tail /data/mastermha/app1/manager.log -f
 
Fri Nov 25 14:37:55 2022 - [info] Checking master_ip_failover_script status:
Fri Nov 25 14:37:55 2022 - [info]   /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=10.0.0.8 --orig_master_ip=10.0.0.8 --orig_master_port=3306 
Checking the Status of the script.. OK 
Fri Nov 25 14:37:55 2022 - [info]  OK.
Fri Nov 25 14:37:55 2022 - [warning] shutdown_script is not defined.
Fri Nov 25 14:37:55 2022 - [info] Set master ping interval 1 seconds.
Fri Nov 25 14:37:55 2022 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.
Fri Nov 25 14:37:55 2022 - [info] Starting ping health check on 10.0.0.8(10.0.0.8:3306)..
Fri Nov 25 14:37:55 2022 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..

10.0.0.8 master

yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm
 
[root@master ~]#vim /etc/my.cnf
 
[mysqld]
log_bin=/data/mysql/mysql-bin
server-id=8
skip_name_resolve=1
general_log
 
mkdir /data/mysql
chown mysql.mysql /data/mysql
 
systemctl restart mysqld
 
ifconfig eth0:1 10.0.0.100/24
 
create user repluser@'10.0.0.%' identified by '123456'
grant replication slave on *.* to repluser@'10.0.0.%'
create user mhauser@'10.0.0.%' identified by 'magedu'
grant all on *.* to mhauser@'10.0.0.%'
 
mysqldump -uroot -p -A --single-transaction --source-data=1 > /data/all.sql
 
scp /data/all.sql 10.0.0.58:/data

10.0.0.58 slave1 ,10.0.0.68同理 server-id=68

yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm
 
[root@slave1 ~]#vim /etc/my.cnf
[mysqld]
server-id=58
read-only
log_bin=/data/mysql/mysql-bin
skip_name_resolve=1
general_log
relay_log_purge=0
 
mkdir /data/mysql
chown mysql.mysql /data/mysql
 
systemctl restart mysqld
 
mysql
 
set sql_bin_log=0;
 
source /data/all.log;
 
start slave;

galera cluster
10.0.0.7

[root@Centos yum.repos.d]#vim /etc/yum.repos.d/pxc.repo 
 
[percona]
name=percona_repo
baseurl=https://mirror.tuna.tsinghua.edu.cn/percona/release/7/RPMS/x86_64/
enabled=1
gpgcheck=0
 
scp /etc/yum.repos.d/pxc.repo 10.0.0.38:/etc/yum.repos.d/pxc.repo
scp /etc/yum.repos.d/pxc.repo 10.0.0.48:/etc/yum.repos.d/pxc.repo
scp /etc/yum.repos.d/pxc.repo 10.0.0.47:/etc/yum.repos.d/pxc.repo
 
 
yum -y install  Percona-XtraDB-Cluster-57
 
[root@Centos yum.repos.d]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
 
# Cluster connection URL contains IPs of nodes
#If no IP is found, this implies that a new cluster needs to be created,
#in order to do that you need to bootstrap this node
wsrep_cluster_address=gcomm://10.0.0.7,10.0.0.38,10.0.0.48
 
# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW
 
# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB
 
# Slave thread to use
wsrep_slave_threads= 8
 
wsrep_log_conflicts
 
# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2
 
# Node IP address
wsrep_node_address=10.0.0.7
# Cluster name
wsrep_cluster_name=pxc-cluster
 
#If wsrep_node_name is not specified,  then system hostname will be used
wsrep_node_name=pxc-cluster-node-1
 
#pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER
pxc_strict_mode=ENFORCING
 
# SST method
wsrep_sst_method=xtrabackup-v2
 
#Authentication for SST method
wsrep_sst_auth="sstuser:s3cretPass"
 
systemctl start mysql@bootstrap.service #第一台启动命令是不同的
 
ss -ntl
cat /var/log/mysqld.log | grep password
 
mysql -uroot -pdxnBiywtC9+A
 
alter user 'root'@'localhost' identified by '123456';
 
mysql> create user 'sstuser'@'localhost' identified by 's3cretPass';
Query OK, 0 rows affected (0.00 sec)
 
mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT on *.* to'sstuser'@'localhost';
 
mysql> SHOW VARIABLES LIKE 'wsrep%'\G;
 
SHOW STATUS LIKE 'wsrep%'\G;
 
Variable_name: wsrep_cluster_size
        Value: 4

10.0.0.38

yum -y install  Percona-XtraDB-Cluster-57
 
[root@localhost yum.repos.d]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
 
wsrep_node_address=10.0.0.38 #主要是ip地址和节点名称需要修改，其他和10.0.0.7配置相同
 
wsrep_node_name=pxc-cluster-node-2
 
 
systemctl start mysql

10.0.0.48 与10.0.0.38配置相似
10.0.0.47要加入集群

添加自己的IP地址及修改IP和节点名称，其他与10.0.0.7一致
 
[root@localhost ~]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
 
wsrep_cluster_address=gcomm://10.0.0.7,10.0.0.38,10.0.0.48,10.0.0.47
 
 
[root@localhost ~]#systemctl start mysql

二十九、总结mysql配置最佳实践。

基础规范
1.必须使用InnoDB存储引擎

2.使用UTF8MB4字符集

3.数据表、数据字段必须加入中文注释

4.禁止使用存储过程、视图、触发器、Event

5.禁止存储大文件或大照片

命名规范
6.只允许使用内网域名，而不是IP连接数据库

7.线上环境、开发环境、测试环境数据库内网域名遵循命名规范

8.库名、表名、字段名：小写，下划线风格，不超过32个字符，必须见名知意

9.库名与应用名称尽量一致

表设计规范
10.单实例表数目必须小于500

11.单表列数目必须小于30

12.表必须有主键

13.禁止使用外键，如果有外键完整性约束，需要应用程序控制

字段设计规范
14.必须把字段定义为NOT NULL并且提供默认值

15.禁止使用TEXT、BLOB类型

16.禁止使用小数存储货币

17.必须使用varchar(20)存储手机号

18.禁止使用ENUM,可使用TINYINT代替

索引设计规范
19.单表索引建议在五个以内

20.单索引字段数不允许超过5个

21.禁止在更新十分频繁、区分度不高的属性建立索引

22.建立组合索引、必须把区分度高的字段放在前面

SQL使用规范
23.禁止使用SELECT*,只要获取必要的字段，需要显示说明列属性

三十、总结openvpn原理，并完成1键安装不同版本vpn脚本，可以适配rocky, ubuntu, centos主机。同时支持添加账号，注销账号。

openvpn原理
VPN: Virual Private Network ,虚拟私有网络，或称为虚拟专用网络，常用于在公用网络上建立专用网络，进行加密通讯，VPN网关通过对数据包的加密和数据包目标地址的转换实现远程访问。

OpenVPN是一个基于OpenSSL库的应用层VPN实现。OpenVPN允许参与建立VPN的单点使用共享密钥，电子证书，或者用户名/密码进行身份验证。

虚拟网卡在Linux2.4版本以上，操作系统支持一个名为tun的设备，tun设备的驱动程序中包含两个部分，一部分是字符设备驱动，一部分是网卡驱动。网卡的驱动把从TCP/IP协议栈收到的数据包结构skb放于tun设备的读取队列，用户进程通过调用字符设备接口read获得完整的IP数据包，字符驱动read函数的功能是从设备的读取队列读取数据，将核心态的skb传递给用户；反过来字符驱动write函数给用户提供了把用户态的数据写入核心态的接口，write函数把用户数据写入核心空间并穿入TCP/IP协议栈。该设备既能以字符设备的方式被读写，作为系统的虚拟网卡，也具有和物理网卡相同的特点：能够配置IP地址和路由。对虚拟网卡的使用是OpenVPN实现其SSL VPN功能的关键。

地址池以及路由
OpenVPN服务器一般需要配置一个虚拟IP地址池和一个自用的静态虚拟IP地址（静态地址和地址池必须在同一个子网中），然后为每一个成功建立SSL连接的客户端动态分配一个虚拟IP地址池中未分配的地址。这样，物理网络中的客户端和OpenVPN服务器就连接成一个虚拟网络上的星型结构局域网，OpenVPN服务器成为每个客户端在虚拟网络上的网关。OpenVPN服务器同时提供对客户端虚拟网卡的路由管理。当客户端对OpenVPN服务器后端的应用服务器的任何访问时，数据包都会经过路由流经虚拟网卡，OpenVPN程序在虚拟网卡上截获数据IP报文，然后使用SSL协议将这些IP报文封装起来，再经过物理网卡发送出去。OpenVPN的服务器和客户端在虚拟网卡之上建立起一个虚拟的局域网络，这个虚拟的局域网对系统的用户来说是透明的。
客户端与服务端安全连接的建立
OpenVPN的服务器和客户端支持tcp和udp两种连接方式，只需在服务端和客户端预先定义好使用的连接方式（tcp或udp）和端口号，客户端和服务端在这个连接的基础上进行SSL握手。连接过程包括SSL的握手以及虚拟网络上的管理信息，OpenVPN将虚拟网上的网段、地址、路由发送给客户端。连接成功后，客户端和服务端建立起SSL安全连接，客户端和服务端的数据都流入虚拟网卡做SSL的处理，再在tcp或udp的连接上从物理网卡发送出去。
数据包的处理过程
发送数据流程
应用层的外出数据，经过系统调用接口传入核心TCP/IP层做处理，在TCP/IP经过路由到虚拟网卡，虚拟网卡的网卡驱动发送处理程序hard_start_xmit()将数据包加入skb表并完成数据包从核心区到用户区的复制，OpenVPN调用虚拟网卡的字符处理程序tun_read()，读取到设备上的数据包，对读取的数据包使用SSL协议做封装处理后，通过socket系统调用发送出去。
接受数据流程
物理网卡接收数据包，经过核心TCP/IP上传到OpenVPN，OpenVPN通过link_socket_read()接收数据包，使用SSL协议进行解包处理，经过处理的数据包OpenVPN调用虚拟网卡的字符处理程序tun_write()写入虚拟网卡的字符设备，设备驱动程序完成数据从用户区到核心区的复制，并将数据写入skb链表，然后调用网卡netif_rx()接收程序，数据包再次进入系统TCP/IP协议栈，传到上层应用程序。如图1所示。
在这里插入图片描述
数据包的封装
OpenVPN提供tun和tap两种工作模式。在tun模式下，从虚拟网卡上收到的是不含物理帧头IP数据包，SSL处理模块对IP包进行SSL封装；在tap模式下，从虚拟网卡上收到的是包含物理帧头的数据包，SSL处理模块对整个物理帧进行SSL封装。Tap模式称为网桥模式，整个虚拟的网络就像网桥方式连接的物理网络。这种模式可以传输以太网帧、IPX、NETBIOS等数据包，应用范围更广。
OpenVPN与Openssl
OpenVPN软件包需要和openssl软件一起安装，因为OpenVPN调用了Openssl函数库，OpenVPN的客户端和服务端建立SSL链接的过程是通过调用Openssl来实现的。通过bio_write()/函数把数据写入Openssl的状态机通道，bio_read()从Openssl读取结果。OpenVPN还调用Openssl的加解密函数处理转发的数据包。
总结
OpenVPN是一款基于SSL的开源VPN软件，它实现了利用SSL来保证网络通讯安全性的目的，同时避免了传统SSL VPN仅提供简单的Web应用的不足，它具有支持各种应用协议，支持Windows，Linux，BSD，MAC OS等多平台的特点。在上面的应用系统中，通过对OpenVPN的灵活部署，实现了五种访问形式。其中部门内用户点到点通信、用户跨部门访问应用服务器、用户跨部门点到点通信、数据包在客户端到SSL VPN服务器以及SSL VPN服务器间接力传送；跨部门的应用服务器数据通信时，SSL VPN服务器要做内网数据向外网转发的反向代理，而这些应用需求在传统SSL代理服务器中是比较难实现的。
脚本实现

#!/bin/bash
install_openvpn () {
. /etc/os-release
 
if [ $ID = "rocky" -o $ID = "centos" ];then
    echo OS version is rocky or centos
    yum -y install openvpn
	yum -y install easy-rsa
	cp /usr/share/doc/openvpn/sample/sample-config-files/server.conf /etc/openvpn
    cp -r /usr/share/easy-rsa/ /etc/openvpn/server
	cp /usr/share/doc/easy-rsa/vars.example /etc/openvpn/server/easy-rsa/3
    cd /etc/openvpn/server/easy-rsa/3
	./easyrsa init-pki
	./easyrsa build-ca nopass
	./easyrsa gen-req server nopass
	./easyrsa sign server server <<EOF
yes
EOF
    ./easyrsa gen-dh
    cp -a /usr/share/easy-rsa/ /etc/openvpn/client
	cd /etc/openvpn/client/easy-rsa/3
	cp /usr/share/doc/easy-rsa/vars.example /etc/openvpn/client/easy-rsa/3/vars
	./easyrsa init-pki
	
	
elif [ $ID = "ubuntu" ];then
    echo OS version is Ubuntu
    apt update
    apt  -y install openvpn 
    apt -y install easy-rsa
	cp /usr/share/doc/openvpn/sample/sample-config-files/server.conf /etc/openvpn
    cp -r /usr/share/easy-rsa/ /etc/openvpn/server
	cp /usr/share/doc/easy-rsa/vars.example /etc/openvpn/server/easy-rsa/3
    cd /etc/openvpn/server/easy-rsa/3
	./easyrsa init-pki
	./easyrsa build-ca nopass
	./easyrsa gen-req server nopass
	./easyrsa sign server server <<EOF
yes
EOF
    ./easyrsa gen-dh
    cp -a /usr/share/easy-rsa/ /etc/openvpn/client
	cd /etc/openvpn/client/easy-rsa/3
	cp /usr/share/doc/easy-rsa/vars.example /etc/openvpn/client/easy-rsa/3/vars
	./easyrsa init-pki
 
else
    echo "不支持OS"
    exit
fi
}
 
useradd () {
read -p "请输入创建用户姓名的拼音（例如:libai):" NAME
cd /etc/openvpn/client/easy-rsa/3
 
./easyrsa gen-req ${NAME} nopass <<EOF
EOF
 
./easyrsa import-req /data/openvpn/easy-rsa/pki/reqs/${NAME}.req ${NAME}
 
 
./easyrsa sign client ${NAME} <<EOF
yes
EOF
}
 
userdel () {
read -p "请输入删除用户姓名的拼音（例如:libai):" NAME
/etc/openvpn/client/easy-rsa/3
 
./easyrsa revoke ${NAME}  <<EOF
yes
EOF
 
./easyrsa gen-crl
 
systemctl restart openvpn@server.service
 
}
 
 
 
 
echo -en "\E[$[RANDOM%7+31];1m"
cat <<EOF
请选择：
1）安装openvpn
2）创建用户
3）删除用户
4）选择结束 
EOF
echo -en '\E[0m'
 
read -p  "请输入上面数字1-5: " MENU
 
case $MENU in
1)
   echo "正在安装openvpn"
   install_openvpn
   ;;
2)
    echo "正在创建用户"
    useradd
	;;
3)
    echo "正在删除用户"
    userdel
	;;
4)
    echo "选择结束"
	break
    ;;
*)
    echo "INPUT FALSE!"
esac