视频链接: MySQL_基础+高级篇- 数据库 -sql -mysql教程_mysql视频_mysql入门_尚硅谷

243 - 255

数据库的作用

  1. 可将数据持久化
  2. 可存储大量数据
  3. 方便检索
  4. 保证数据的一致性、完整性
  5. 安全、可共享
  6. 通过组合分析,可以产生新数据

相关概念

DB

数据库(database):存储数据的“仓库”,保存一系列有组织的数据

DBMS

数据库管理系统(Database Management System)。数据库是通过DBMS创建和操作的容器。

SQL

结构化查询语言(Structure Query Language):专门用来与数据库通信的语言。

三者关系

数据库存储数据的特点

  • 将数据放到表中,表再放到库中
  • 一个数据库可以有多个表,每个表都有一个名字,用来标识自己。表明具有唯一性
  • 表具有一些特性,这些特性的定义了数据在表中如何存储,类似Java中“类”的设计
  • 表由列组成,称为字段。所有表都是由一个或多个列组成的,每一列类似Java中的“属性”
  • 表中的数据是按行存储的,每一行类似于Java中的“对象”

小结

  • 表 -> 类
  • 列,字段 -> 属性
  • 行 -> 对象

Mysql安装与卸载

mysql 的数据库软件是 C/S 结构,底层是 TCP/IP 协议的程序

S:服务器端

C:客户端

服务器端,先启动,有一个端口号,监听/等待客户端的连接

docker swarm 部署 mysql 服务

这里尝试使用docker 部署docker服务

关于 swarm

swarm 基本结构图

实际部署举例

实例部署

按大概理解,swamdocker自带的集群管理工具,可以用来管理多台机器下部署的docker容器,就像虚拟机软件可以管理多个虚拟机一样。此处不提,主要是docker的部署不会影响到系统环境的内容,学习软件过程中可以很方便置换版本,部署mysql也是如此。

而部署docker swarm使用本地的docker也是可以直接使用的,但由于路由器的ip地址不固定,为了体现集群,这里使用docker-machine部署。window下使用的docker-machine只能用virtualbox的驱动,而Linux下可以直接使用kvm

环境

OS: openSUSE Tumbleweed

Kernel: 5.9.14-1-default

部署软件:docker-machine docker-machine-kvm2 docker

配置swarm

注意:docker-machine 会从github拉取文件,网速可能是个问题。可以尝试下载对应的镜像,放在对应的文件夹上,会自动跳过自动下载。

1. 创建管理机器

该机器用来设置 docker swarm 主管理节点,其他机器接受该机器的管理

### docker-machine create -d kvm2 swarm-manager
docker-machine create -d virtualbox swarm-manager

### 查看机器地址
docker-machine ls

### 地址为 tcp://192.168.99.100:2376

### 登录到对应机器 创建swarm服务
docker-machine ssh swarm-manager

### 初始化swarm
docker swarm init --advertise-addr 192.168.99.100 ## 上面获得的地址

### 复制保留输出的信息
docker swarm join --token SWMTKN-1-5nh2frk32tbgjwl393kes6u49hrpbytzwi3r1jxrnbi64r8yeu-19rr0krx58t4fpd7qvzdxzj8x 192.168.99.100:2377
## 这是其他机器加入集群的密钥

机器地址

2. 创建工作节点

退出,回到主机,此时创建其他节点。

### 创建三台主机放置 服务
docker-machine create -d virtualbox swarm-worker-mysql1
docker-machine create -d virtualbox swarm-worker-mysql2
docker-machine create -d virtualbox swarm-worker-mysql3

### 分别进入上面的主机, 加入swarm-manager中
docker swarm join --token SWMTKN-1-5nh2frk32tbgjwl393kes6u49hrpbytzwi3r1jxrnbi64r8yeu-19rr0krx58t4fpd7qvzdxzj8x 192.168.99.100:2377

加入swarm

3. 检查工作节点是否生效

查看集群信息

检查状态

生效状态

工作

服务节点

接下来可以直接在docker-manager 中管理其他节点的docker了。

部署服务

本地创建一个文件夹,配置服务文件放置到这里

文件结构

stack.yml:

## 修正内容
version: '3.2'

services:
  db:
    image: mysql:5.6
    environment:
      - TZ=Asia/Shanghai
      - MYSQL_ROOT_PASSWORD=pass_word_123_456
    ports:
      - "3306:3306"
    volumes:
      - mysql56_data:/var/lib/mysql
    networks:
      - mysql56_network
    deploy:
      mode: replicated
      replicas: 1
      placement:
        constraints: [node.role == manager]

networks:
  mysql56_network:
    driver: overlay
    attachable: true

volumes:
  mysql56_data:

不过命令方式部署可能有些复杂,我们可以选择一个网页前端。

-> 此处参考文章: 《docker stack 部署 mysql 5.6》

portainer 的部署

使用 portainer 的目的是简化命令操作,使得管理变得可视化。

1. swarm-manager 文件布局

### 创建一个文件夹放置 portainer
mkdir -p /hostname/xxx/codes/workspaces/docker/portainer

### /hosthome 选择一个地方放置存储数据
mkdir -p /hosthome/xxx/codes/workspaces/docker/vol/portainer/data

2. 创建网络

### 本地网络
docker network create \
  --driver=bridge \
  --subnet=172.11.0.0/16 \
  --ip-range=172.11.0.0/16 \
  --gateway=172.11.0.1 \
  --attachable \
  local-net


### swarm 网络
docker network create \
  --driver=overlay \
  --subnet=172.12.0.0/16 \
  --ip-range=172.12.0.0/16 \
  --gateway=172.12.0.1 \
  --attachable \
  swarm-net

3. 部署portainer

创建文件portainer-stack.yml

version: '3.4'
services:

  portainer:
    image: portainer/portainer
    volumes:
      - /var/run/docker.sock:/var/run/docker.sock
      - /hosthome/muk/codes/workspaces/docker/vol/portainer/data:/data
      ## 这里置换成你设置的文件夹
    deploy:
      placement:
        constraints: [node.role == manager]
      replicas: 1
      restart_policy:
        condition: on-failure
      resources:
        limits:
          cpus: "0.5"
          memory: 200M
      update_config:
        parallelism: 1
        delay: 5s
        monitor: 10s
        max_failure_ratio: 0.1
        order: start-first
    ports:
      - 9000:9000
    networks:
      - swarm-net

networks:
  swarm-net:
    external: true

-› 此处参考文章 :《docker 部署 portainer(http)》

部署完成

查看部署详情:

部署详情

这个服务没有部署成功!

我们查看下日志:

日志

根据信息,该 stack.yml 文件有出错的部分,后面排查 volumenetwork 部分有问题。暂时不用该文件部署。

我们参考官方文档进行部署: 官方文档-> Linux 环境 部署 portainer

## docker swarm
 curl -L https://downloads.portainer.io/portainer-agent-stack.yml -o portainer-agent-stack.yml
 docker stack deploy -c portainer-agent-stack.yml portainer

实验环境下,直接使用部署,如果有需要,可以自己更改配置:

## 停止 删除 服务
docker stack rm portainer-stack
### 接下来部署
docker stack deploy -c portainer-agent-stack.yml portainer-stack

正常运行

我们在虚拟机已经创建了,如何从外部访问呢?

4. 设置端口映射,外部访问

docker-machine 可以直接访问端口,直接使用前面获取的URL加对应端口就行。

docker-machine ip swarm-manager
## 获取ip后使用该ip:port可以直接访问

我们也可以设置物理机的端口映射,我们这里直接设置 virtualbox 中的端口映射

  1. 打开网络设置

    网络设置

  2. 设置网卡1

    找到适配器1

  3. 选择端口映射(Port Forwarding)

    设置

    Host IPHost Port 必须设置,物理机访问的地址。Guest IP 留空,不然会出错,Guest Port 设置到 docker 中暴露的端口。

    确认后,可以直接使用127.0.0.1:9000访问portainer

5. 使用portainer

登录界面

第一步是设计密码进入。

portainer 的管理面板很直观,我们可以很方便配置对应的服务。

管理面板

管理swarm

部署mysql

参考本章的首部,使用 stack 部署,可以将配置文件复制到 portainer 相关位置,后进行复制。

我们可以使用 portainer 自定义 dockerhub 地址,当然主地址需要在系统内配置。

配置registry

docker-machine 创建的虚拟机配置主地址并不容易,起码不是物理机下那么方便。

1. 创建 service

创建service

这里我们通过 service 创建一个 mysql:5.6 的服务。

2. 设置环境变量

设置环境变量

此处添加环境变量 MYSQL_ROOT_PASSWORD ,设置 mysql 的初始密码;添加 TZ ,设置环境变量 Asia/Shanghai ,确保时区设定与物理机一致。

3. 设置 volume

先创建:

创建volume

后挂载:

挂载

其他服务按需设置:

设置

4. 创建服务

创建成功会显示 running。相对编写文件还是比较直观。

设置成功

只是体验一下集群部署,但是学习数据库没有必要那么多集群,后面可以删除部分服务。

mysql 连接

创建完成后我们就可以连接数据库了,这里使用 DataGirp 实验。

软件连接

前面我们使用 virtualbox 设置了端口映射,可以直接使用 localhost 作为 Host 连接。也可以使用 docker-machine 中设定的 IP

连接成功

同理,其他管理软件也可以连接上。

其他安装方式可以参考官网。

docker 部署只是安装存在一些过程的问题,后续的更换版本和生产/开发环境置换很方便,借助虚拟机还可以使得自己的本地环境保持纯净。

配置

进入 portainer ,选择其中一个服务,进入图标 >_ ,这个表示命令行。

进入服务

## 命令
cat /etc/mysql/mysql.conf.d/mysqld.cnf 
## 该文件是 mysql 的服务端配置文件


# Copyright (c) 2014, 2016, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License, version 2.0,
# as published by the Free Software Foundation.
#
# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms,
# as designated in a particular file or component or in included license
# documentation.  The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License, version 2.0, for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
#log-error      = /var/log/mysql/error.log
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

## 可以在此处配置mysql

## 但对于docker来说,配置应该在部署的时候
## 如果需要修改该文件,我们后续会在 config 处添加

这里我们尝试配置 mysql 的字符集:

查看原来的字符集:

show variables  like '%character%';

字符信息

1. 添加配置

portainer 添加一个 config

添加配置

my.cnf

## my.cnf
[mysqld]
character-set-server=utf8mb4

[mysql]
default-character-set=utf8mb4

[client]
default-character-set=utf8mb4

2. 在 service 中添加 config

选择我们之前添加的配置,后面对应在容器中的文件位置。

config

文件名称不限制。

3. 查看配置

配置完成,默认字符集变成了 uft8mb4

配置完成

其他片配置都可以通过该方法修改,后面修改可以修改 portainer 中的内容。

命令行模式

1. 进入命令行

打开命令行

## 查看 mysql 服务
service mysql status

## 输出
[info] MySQL Community Server 5.6.50 is running.

## 可以用 service 控制 mysql 服务的启动。该容器只有一个mysql服务是我们使用的服务

2. 进入 mysql

## 连接本机数据库数据库
mysql -uroot -ppass_word

## 语法
### mysql -u用户名 -p密码
### 上述是明文显示

### mysql -uroot -p 
### 上述可以隐藏密码

进入mysql

# 使用地址
mysql -hlocalhost -P3306 -uroot -p

## -h 后面接ip/url ,如果部署swarm实例,可以尝试使用ip地址连接其他容器中mysql

3. 基本语句

  1. 显示数据库

    show databases;

    显示数据库

  2. 进入数据库

    ## 进入数据库 use 数据库名称
    use mysql;
    ## 显示表
    show tables;

    进入数据库

    ## 本数据库查看其他数据库的表
    show tables from information_schema;
    
    ## 查看当前所在的数据库
    select database();
    
    ## 结果
    # mysql> select database();
    # +------------+
    # | database() |
    # +------------+
    # | mysql      |
    # +------------+
    # 1 row in set (0.04 sec)
  3. 操作数据

    ## 1. 先创建一个数据库
    create database test;
    ## 2. 进入 test 创建一个表
    use test;
    create database stuinfo;
    ## 3. 创建表
    create table stuinfo(stuid int, stuname varchar(20), gender char, borndate datetime);
    
    ### 结果
    # mysql> use  test
    # Database changed
    # mysql> create table stuinfo(
    #     -> stuid int,
    #     -> stuname varchar(20),
    #     -> gender char,
    #     -> borndate datetime);
    # Query OK, 0 rows affected (0.02 sec)
    
    ## 4. 查看表
    show tables;
    desc stuinfo;
    ### 结果
    # mysql> show tables;
    # +----------------+
    # | Tables_in_test |
    # +----------------+
    # | stuinfo        |
    # +----------------+
    # 1 row in set (0.00 sec)
    # mysql> desc stuinfo;
    # +----------+-------------+------+-----+---------+-------+
    # | Field    | Type        | Null | Key | Default | Extra |
    # +----------+-------------+------+-----+---------+-------+
    # | stuid    | int(11)     | YES  |     | NULL    |       |
    # | stuname  | varchar(20) | YES  |     | NULL    |       |
    # | gender   | char(1)     | YES  |     | NULL    |       |
    # | borndate | datetime    | YES  |     | NULL    |       |
    # +----------+-------------+------+-----+---------+-------+
    # 4 rows in set (0.01 sec)
    
    ## 5. 查看表中数据
    select * from stuinfo;
    ### 结果
    # mysql> select * from stuinfo;
    # Empty set (0.00 sec)
    
    ## 6. 插入数据
    # 注意: 使用 portainer 无法输入中文字体 
    # 这是 console 的问题,可以尝试使用自己的 命令行使用
    # 前面已经配置字符集为 utf8mb4 ,数据库中可以正常处理中文字体的
    # 但是 docker 容器中的 shell 并不支持中文
    # 这里使用客户端尝试输入数据可行
    # 附加
    # set names utf8mb4; 设置字符集
    insert into stuinfo values(1, '张三', '男', '2000-01-01');
    insert into stuinfo values(1, '李四', '男', '2000-01-01');
    
    ## 7. 查询数据
    select * from stuinfo;
    ### 结果
    # mysql> select * from stuinfo;
    # +-------+---------+--------+---------------------+
    # | stuid | stuname | gender | borndate            |
    # +-------+---------+--------+---------------------+
    |  #    1 | 张三    | 男     | 2000-01-01 00:00:00 |
    # |     1 | 李四    | 男     | 2000-01-01 00:00:00 |
    # +-------+---------+--------+---------------------+
    # 2 rows in set (0.00 sec)
    
    ## 8. 更新数据
    update stuinfo set borndate='2000-01-02' where stuid=2;
    ### 结果
    # mysql> update stuinfo set borndate='2000-01-02' where stuid=2;
    # Query OK, 0 rows affected (0.00 sec)
    # Rows matched: 1  Changed: 0  Warnings: 0
    # 
    # mysql> select * from stuinfo;
    # +-------+---------+--------+---------------------+
    # | stuid | stuname | gender | borndate            |
    # +-------+---------+--------+---------------------+
    # |     1 | 张三    | 男     | 2000-01-01 00:00:00 |
    # |     2 | 李四    | 男     | 2000-01-02 00:00:00 |
    # +-------+---------+--------+---------------------+
    # 2 rows in set (0.01 sec)
    
    ## 9. 删除数据
    delete from stuinfo where stuid = 1;
    ## 删除stuid 为 2 的数据
    
    ## 10. 修改表结构
    alter table stuinfo add column email varchar(20)
    ### 结果
    # mysql> alter table stuinfo add column email varchar(20)
    #    -> ;
    # Query OK, 0 rows affected (0.09 sec)
    # Records: 0  Duplicates: 0  Warnings: 0
    
    # mysql> desc stuinfo
    #     -> ;
    # +----------+-------------+------+-----+---------+-------+
    # | Field    | Type        | Null | Key | Default | Extra |
    # +----------+-------------+------+-----+---------+-------+
    # | stuid    | int(11)     | YES  |     | NULL    |       |
    # | stuname  | varchar(20) | YES  |     | NULL    |       |
    # | gender   | char(1)     | YES  |     | NULL    |       |
    # | borndate | datetime    | YES  |     | NULL    |       |
    # | email    | varchar(20) | YES  |     | NULL    |       |
    # +----------+-------------+------+-----+---------+-------+
    # 5 rows in set (0.00 sec)
    ## 11. 退出
    exit;

    插曲-使用自带的终端登录容器:

    docker-machine ssh swarm-worker-mysql1 ## 你容器的名字
    docker ps ## 查看id 后方便进入
    docker exec -it fafd0 /bin/bash ## 需要置换你本机的id

    docker-machine进入

4. 扩展——注释

  1. 单行注释 # 即可
  2. 多行注释 /* */

SQL 语句

结构化查询语言,可以应用到所有关系数据库中。

语法要求

  • 可以单行或多行书写,以分号结尾;
  • 可以用空格和缩进来来增强语句的可读性;
  • 关键字不区分大小写,建议使用大写;

分类

  • DDL(Data Definition Language): 数据定义语言,用来定义数据库对象——库、表、列等等;create drop alter
  • DML(Data Manipulation Language): 数据操作语言,用来定义数据库记录(数据);insert update delete
  • DCL (Data Control Language): 数据控制语言,用来定义访问权限和安全级别;
    • TCL(Transaction Control Language)
  • DQL(Data Query Language): 数据查询语言,用来查询记录(数据); select