第六周mysql作业

发布于：2022-11-07 ⋅ 阅读:(486) ⋅ 点赞:(0)

1.SQL分类

1.1 数据定义语言（DDL）

  数据定义语言 (Data Definition Language, DDL) 是SQL语言集中，负责数据结构定义与数据库对象定义的语言，由CREATE、ALTER与DROP三个语法所组成，最早是由 Codasyl (Conference on Data Systems Languages) 数据模型开始，现在被纳入 SQL 指令中作为其中一个子集。

1.2 数据操纵语言（DML）

  数据操纵语言（Data Manipulation Language, DML）是SQL语言中，负责对数据库对象运行数据访问工作的指令集，以INSERT、UPDATE、DELETE三种指令为核心，分别代表插入、更新与删除。

1.3 数据查询语言（DQL）

  数据查询语言（Data Query Language, DQL）是SQL语言中，负责进行数据查询而不会对数据本身进行修改的语句，这是最基本的SQL语句。保留字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有FROM，WHERE，GROUP BY，HAVING和ORDER BY。这些DQL保留字常与其他类型的SQL语句一起使用。

1.4 数据控制语言（DCL）

  数据控制语言 (Data Control Language) 在SQL语言中，是一种可对数据访问权进行控制的指令，它可以控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权。由 GRANT 和 REVOKE 两个指令组成。DCL以控制用户的访问权限为主，GRANT为授权语句，对应的REVOKE是撤销授权语句。

1.5数据定义语言(DDL)

   数据定义语言 (Data Definition Language, DDL) ,它主要包括三个关键字：create ，alter , drop（数据库关键字不分大小写 ）,主要操作对象 有数据库、表、索引、视图等.

1.6mysql各个关键字查询时候的先后顺序

select--from--where--group by--having--order by，其中select和from是必选的。具体如下：

  1.  FROM ...,...,....

  2.  WHERE 多表的连接条件

  3.  AND 不包含组函数的过滤条件

  4.  GROUP BY ...,...

  5.  HAVING 包含组函数的过滤条件

  6. SELECT ...,....,...

  7.  ORDER BY ... ASC/DESC

  8.  LIMIT限制最多输出几行。

2.自行设计10个sql查询语句，需要用到关键字[GROUP BY/HAVING/ORDER BY/LIMIT]，至少同时用到两个。

3.xtrabackup备份和还原数据库练习

[root@rocky8 ~]# ls
anaconda-ks.cfg  hellodb_innodb.sql  percona-xtrabackup-80-8.0.23-16.1.el8.x86_64.rpm

#安装xtrabackup软件包 

[root@rocky8 ~]# yum -y install percona-xtrabackup-80-8.0.23-16.1.el8.x86_64.rpm
#新建backup目录并修改xtrabackup的存放路径

[root@rocky8 ~]# mkdir /backup
[root@rocky8 ~]# xtrabackup -uroot --backup --target-dir=/backup/base
xtrabackup: Transaction log of lsn (18481319) to (18481329) was copied.
221103 19:42:34 completed OK!
[root@rocky8 backup]# ll /backup/base/
total 72756
-rw-r----- 1 root root      475 Nov 3 19:42 backup-my.cnf
-rw-r----- 1 root root      156 Nov 3 19:42 binlog.000003
-rw-r----- 1 root root       16 Nov 3 19:42 binlog.index
drwxr-x--- 2 root root      132 Nov 3 19:42 hellodb
-rw-r----- 1 root root     6094 Nov 3 19:42 ib_buffer_pool
-rw-r----- 1 root root 12582912 Nov 3 19:42 ibdata1
drwxr-x--- 2 root root      143 Nov 3 19:42 mysql
-rw-r----- 1 root root 28311552 Nov 3 19:42 mysql.ibd
drwxr-x--- 2 root root     8192 Nov 3 19:42 performance_schema
drwxr-x--- 2 root root       28 Nov 3 19:42 sys
-rw-r----- 1 root root 16777216 Nov 3 19:42 undo_001
-rw-r----- 1 root root 16777216 Nov 3 19:42 undo_002
-rw-r----- 1 root root       18 Nov 3 19:42 xtrabackup_binlog_info
-rw-r----- 1 root root      102 Nov 3 19:42 xtrabackup_checkpoints
-rw-r----- 1 root root      484 Nov 3 19:42 xtrabackup_info
-rw-r----- 1 root root     2560 Nov 3 19:42 xtrabackup_logfile
-rw-r----- 1 root root       39 Nov 3 19:42 xtrabackup_tablespaces

远程主机：192.168.72.143，查看backup下的文件copy过来的备份文件

[root@rocky8 ~]# ll /backup/base/
total 72756
-rw-r----- 1 root root      475 Nov 3 19:49 backup-my.cnf
-rw-r----- 1 root root      156 Nov 3 19:49 binlog.000003
-rw-r----- 1 root root       16 Nov 3 19:49 binlog.index
drwxr-x--- 2 root root      132 Nov 3 19:49 hellodb
-rw-r----- 1 root root     6094 Nov 3 19:49 ib_buffer_pool
-rw-r----- 1 root root 12582912 Nov 3 19:49 ibdata1
drwxr-x--- 2 root root      143 Nov 3 19:49 mysql
-rw-r----- 1 root root 28311552 Nov 3 19:49 mysql.ibd
drwxr-x--- 2 root root     8192 Nov 3 19:49 performance_schema
drwxr-x--- 2 root root       28 Nov 3 19:49 sys
-rw-r----- 1 root root 16777216 Nov 3 19:49 undo_001
-rw-r----- 1 root root 16777216 Nov 3 19:49 undo_002
-rw-r----- 1 root root       18 Nov 3 19:49 xtrabackup_binlog_info
-rw-r----- 1 root root      102 Nov 3 19:49 xtrabackup_checkpoints
-rw-r----- 1 root root      484 Nov 3 19:49 xtrabackup_info
-rw-r----- 1 root root     2560 Nov 3 19:49 xtrabackup_logfile
-rw-r----- 1 root root       39 Nov 3 19:49 xtrabackup_tablespaces

# 安装xtrabackup，还原后修改数据库权限并重启mysql服务

[root@rocky8 ~]# yum -y install percona-xtrabackup-80-8.0.23-16.1.el8.x86_64.rpm
[root@rocky8 ~]#xtrabackup --prepare --target-dir=/backup/base
[root@rocky8 ~]#xtrabackup --copy-back --target-dir=/backup/base
[root@rocky8 ~]#chown -R mysql:mysql /var/lib/mysql
[root@rocky8 ~]#service mysqld restart

4.实现mysql主从复制，主主复制和半同步复制

4.1主从复制

4.1.1打开/etc/my.cnf修改主节点的MySQL配置

4.1.2修改主节点二进制日志的存放位置并重启服务

 4.1.3进行主从复制的完全备份

4.1.4创建复制用户并授权

mysql create user repluser@'192.168.72.146' identified by '123456';

grant replication slave on *.* to repluser@'192.168.72.146';

4.1.5给做好的完全备份添加好从节点的授权账号信息以及二进制的标记位

4.1.6进行slave从节点服务器配置修改 

 5.

4.1.7修改完后重启mysqld服务：

 4.1.8将备份复制到从节点

 4.1.9关掉从节点的二进制同步

 4.1.10从节点用完全备份文件还原数据库并重新开启二进制日志标记位

source /data/all.sql;
set sql_log_bin=1;

 4.1.11查看主从复制前的从节点状态突然发现主节点与从节点UUID冲突，估计是因为我的从节点虚拟机是从主节点克隆过来的：

 4.1.12尝试重新生成从节点服务器的UUID并修改网卡配置文件

 4.1.13从节点重新加载网卡并查看新的UUID是否修改成功

  4.1.14删除从节点mysql目录下的auto.cnf文件并重启mysql服务以彻底解决UUID冲突

 4.1.15开启主从同步并查看从节点的线程及主从连接状态

 

 4.1.16查看主节点的主从复制线程及网络和端口号连接是否正常

 

  4.1.17主节点增删改数据库里的teacher表，

   4.1.18发现从节点可以立即同步成功，到此主从复制正式同步成功

 

 4.1主主复制--在前一步主从复制的基础上构建而成

4.2.1主主复制从节点创建账号授权给主节点

 4.2.2主主复制查看从节点二进制日志位置

 

 4.2.3主主复制主节点关联从节点二进制日志

4.2.4主主复制start slave后查看同步状态

 4.2.5主主复制查看142的master线程状态及slave节点146机线程状态

 4.2.6主主复制成功后查看端口连接状态

 4.2.7主主复制的结果验证

 

 4.3半同步复制

4.3.1半同步复制主节点改配置文件

 

 4.3.2半同步复制从节点改配置文件

  4.3.3主节点安装半同步复制的插件并开启半同步线程

  4.3.4主节点安装半同步复制的插件并开启半同步线程

 4.3.5在从节点上的mysql里执行start slave命令即可

[root@mycat ~]# start slave

5.用mycat实现mysql的读写分离

需要三台机器：

[root@mycat ~]是mycat客户端，IP是192.168.72.1

mysql主节点IP是192.168.72.8

mysql从节点IP是192.168.72.18

#步骤分解：

5.1在前面的主从同步实验的基础上安装Java环境和mycat包（Mycat基于Java，7及以上版本

[root@mycat ~]#yum -y install java
[root@mycat ~]#wget http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz
[root@mycat ~]#mkdir /apps
[root@mycat ~]#[root@mycat ~]#tar xvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /apps/
[root@mycat ~]#echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
[root@mycat ~]#. /etc/profile.d/mycat.sh

5.2启动mycat

[root@mycat ~]#mycat
Usage: /apps/mycat/bin/mycat { console | start | stop | restart | status | dump }

#注意: 此步启动较慢,需要等一会儿,另外如果内存太小,会导致无法启动

[root@mycat ~]#mycat start
Starting Mycat-server...
#可以看到打开多个端口,其中8066和9066端口用于连接MyCAT

[root@mycat ~]#ss -ntlp
State      Recv-Q Send-Q              Local Address:Port                             Peer Address:Port
LISTEN     0      1                       127.0.0.1:32000                               *:*users:(("java",pid=2478,fd=4))
LISTEN     0      128                             *:22                                          *:*users:(("sshd",pid=1293,fd=3))
LISTEN     0      100                     127.0.0.1:25                                          *:*users:(("master",pid=1387,fd=13))
LISTEN     0      50                           [::]:1984                                     [::]:*users:(("java",pid=2478,fd=70))
LISTEN     0      128                          [::]:8066                                     [::]:*users:(("java",pid=2478,fd=94))
LISTEN     0      50                           [::]:40998                                    [::]:*users:(("java",pid=2478,fd=69))
LISTEN     0      128                          [::]:9066                                     [::]:*users:(("java",pid=2478,fd=90))
LISTEN     0      50                           [::]:37942                                    [::]:*users:(("java",pid=2478,fd=71))
LISTEN     0      128                          [::]:22                                       [::]:*users:(("sshd",pid=1293,fd=4))
LISTEN     0      100                         [::1]:25                                       [::]:*users:(("master",pid=1387,fd=13))
5.3使用默认密码123456来连接mycat

[root@mycat ~]mysql -uroot -p123456 -h 192.168.72.8 -P8066

5.4在mycat 服务器上修改server.xml文件，配置Mycat的连接信息

[root@mycat ~]#vim /apps/mycat/conf/server.xml
#删除注释“<!-- -->”,并修改下面行的8066改为3306
<property name="serverPort">3306</property>
<property name="managerPort">9066</property>
<property name="idleTimeout">300000</property>
<property name="authTimeout">15000</property>
<property name="bindIp">0.0.0.0</property>
<property name="dataNodeIdleCheckPeriod">300000</property> #5 * 60 * 1000L; //连
接空闲检查   删除#号后面的部分
<property name="frontWriteQueueSize">4096</property> <property name="processors">32</property>

 <user name="root" defaultAccount="true">                #连接Mycat的用户名
        <property name="password">MyCAT2022.</property>  #连接Mycat的密码
        <property name="schemas">TESTDB</property>       #数据库名要和schema.xml相对应

5.5 修改schema.xml实现读写分离策略

[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="192.168.72.8:3306" user="root"
                   password="123456">
         <readHost host="host2" url="192.168.72.18:3306" user="root"
password="123456" />
        </writeHost>
    </dataHost>
</mycat:schema>

5.6重启mycat登录并修改数据测试分表效果

[root@mycat ~]​mycat restart
[root@mycat ~]#mysql -uroot -pMyCAT2022. -h127.0.0.1 -Ddb1
MySQL [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+
MySQL [(none)]>  use TESTDB;
MySQL [TESTDB]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+

5.7建立新的主从同步授权关系：

mysql> create user admin@'192.168.72.%' identified by '123456';

mysql> grant all on db1.* to admin@'192.168.72.%';

 #确认从节点server_id和主机名设置 

MySQL [db1]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|          18 |
+-------------+
MySQL [db1]> select @@hostname;
+------------+
| @@hostname |
+------------+
| slave      |
+------------+

#在mysql中确认通用日志已开启

MySQL [db1]> set global general_log=on;

#确认主节点写 

MySQL [db1]> 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      |
+-----+---------------+-----+--------+

MySQL [db1]> update teachers set age=@@server_id where tid=4;

#查看mysql数据库，确认主节点已经修改了teachers表中的第4行数据

MySQL [db1]> 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  |   8 | F      |
+-----+---------------+-----+--------+

#查看mysql通用日志，确认主节点已经修改了teachers表中的第4行数据 

​[root@mycat~]tail-f /var/lib/mysql/rocky8.Log
usr/libexec/mysqld，Version:8.0.21 (Source distribution).started with:cp port:3306 Unix socket:/var/lib/mysql/mysql.sock
Time      Id Command   Argument
2022-11-04T09:53:30.741055Z 15 Query select user()
2022-11-04T09:53:40.741365Z 13 Query select user()
2022-11-04T09:53:50.741447Z 15 Query select user()
2022-11-04T09:54:00.740835Z 13 Query select user()
2022-11-04T09:54:10.741304Z 15 Query select user()
2022-11-04T09:54:20.741252Z 13 Query select user()
2022-11-04T09:54:30.741103Z 15 Query select user()
2022-11-04T09:54:40.741504Z 13 Query select user()
2022-11-04T09:54:40.746993Z 24 Connect admin@192.168.72.8 on db1
2022-11-04T09:54:40.746993Z 23 Connect admin@192.168.72.8 on db1
2022-11-04T09:54:50.740682Z 15 Query select user()
2022-11-04T09:55:00.741398Z 13 Query select user()
2022-11-04T09:55:10.741250Z 24 Query select user()
2022-11-04T09:55:15,829663Z 12 Query update teachers set age=@@server_id where tid=4;
2022-11-04T09:56:20.741599Z 23 Query select user()

5.8停止从节点后，MyCAT自动调度读请求至主节点

#mycat客户端

MySQL [TESTDB]> select * from teachers;

 #mysql主节点

​
MySQL [db1]> 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  |   8 | F      |
+-----+---------------+-----+--------+

​

#停止从节点后，确认主节点开始读

[root@mycat ~]systemctl stop mysqld.service
mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         8|
+-------------+
1 row in set (0.00 sec)

#启动从节点后，确认从节点读

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         18|
+-------------+
1 row in set (0.00 sec)
​[root@mycat~]tail-f /var/lib/mysql/rocky8.Log
usr/libexec/mysqld，Version:8.0.21 (Source distribution).started with:cp port:3306 Unix socket:/var/lib/mysql/mysql.sock
Time      Id Command   Argument
2022-11-04T09:58:30.741055Z 15 Query select user()
2022-11-04T09:58:40.741365Z 13 Query select user()
2022-11-04T09:58:50.741447Z 15 Query select user()
2022-11-04T09:59:00.740835Z 13 Query select user()
2022-11-04T09:59:10.741304Z 15 Query select user()
2022-11-04T09:59:20.741252Z 13 Query select user()
2022-11-04T09:59:30.741103Z 15 Query select user()
2022-11-04T09:59:40.741504Z 13 Query select user()
2022-11-04T09:59:40.746993Z 24 Connect admin@192.168.72.18 on db1
2022-11-04T09:59:40.747093Z 23 Connect admin@192.168.72.18 on db1
2022-11-04T09:59:50.740882Z 15 Query select user()
2022-11-04T10:00:00.741298Z 13 Query select user()
2022-11-04T10:00:00.741350Z 24 Query select user()
2022-11-04T10:02:00,829863Z 12 Query select * from teachers;

5.9停止主节点，MyCAT不会自动调度写请求至从节点

MySQL [db1]> update teachers set age=@@server_id where tid=4;
ERROR 1160 (HY000): java.net.ConnectException: Connection refused

6. 实现openvpn部署，并且测试通过，输出博客或者自己的文档存档。

6.1openvpn环境准备-下载epel源和依赖包

 6.2openvpn环境准备-下载epel源和依赖包

 6.3初始化pki,生成相关证书和文件

6.4创建服务端CA证书

6.5给服务端证书签名

6.6创建赫尔曼密钥

 6.7创建一个工作目录，生成客户端证书和私钥

6.8切换到server证书目录下，完成服务端和客户端之间的签约

6.9把相关证书全都copy到指定的路径下

 6.10查看所有创建完成的证书文件和私钥文件

 6.11修改服务器端配置文件

6.12修改openvpn服务配置文件，设为开机自启动

6.13在启动openvpn访问云服务器内网前，做好客户端CA证书和密钥文件的环境准备

 6.14在Windows上安装openvpn客户端连接云服务器进行测试：

openvpn官网：https://openvpn.net/

openvpn客户端安装界面：

本文含有隐藏内容，请 开通VIP 后查看

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布