• 17.MySQL数据库

    1 数据库原理

    数据库：是以一定方式储存在一起、能予多个用户共享、具有尽可能小的冗余度、与应用程序彼此独立的数据集合。简单理解就是存储电子文件的仓库，用户可以对仓库中的资料进行增删改查等操作。

    数据库管理系统（Database Management System）：简称DBMS,是为了管理数据库而设计的电脑软件系统，一般具有存储、截取、安全保障、备份等基础功能。

    数据库分类：

    关系型数据库（SQL）

    • MySQL
    • MariaDB
    • Oracle

    非关系型数据库（NOSQL）

    • Redis
    • MongoDB

    键值数据库

    • Dynamo
    • LevelDB

    对于数据库相关说明，例如：数据库的设计、数据库的模型、基本词汇解释MariaDB官方给出了相关知识库：https://mariadb.com/kb/en/database-theory/。

    2 MySQL的安装和基本使用

    2.1 MySQL介绍

    2.1.1 MySQL历史

    MySQL原本是一个开源的关系数据库管理系统，不过后来被Sun公司收购，而后Sun公司又被（Oracle）公司收购，现在MySQL已成为Oracle旗下产品。

    2.1.2 MySQL分支

    MySQL的三大主要分支：

    • mysql
    • mariadb
    • percona server

    其中mysql和mariadb是较为主流的开源数据库产品

    他们的官方文档地址：

    https://dev.mysql.com/doc/

    https://mariadb.com/kb/en/

    https://www.percona.com/software/mysql-database/percona-server

    2.1.3 MySQL的特性

    • 插件式存储引擎：也称为“表类型”，存储管理器有多种实现版本，功能和特性可能均略有差别；用 户可根据需要灵活选择,Mysql5.5.5开始innoDB引擎是MYSQL默认引擎

      ​ MyISAM ==> Aria

      ​ InnoDB ==> XtraDB

    • 单进程，多线程

    • 诸多扩展和新特性

    • 提供了较多测试组件

    • 开源

    2.2 MySQL安装

    2.2.1 安装方式介绍

    • 源码包编译安装
    • 二进制程序包（类似于windows中绿色安装）：解压至指定路径，简单配置即可使用
    • 使用程序包管理器安装，也就是yum安装

    2.2.2 RPM包安装MySQL

    这种方式较为简单，一般选择国内镜像仓库，配置yum源安装即可，与普通rpm包安装没有区别，缺点是不能定制配置，生产环境不建议采用这种方式安装

    CentOS国内镜像：

    https://mirrors.tuna.tsinghua.edu.cn/mariadb/yum/

    https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/

    注意：这种方式安装完存在允许root用户空口令登陆，允许匿名用户登陆等安全问题，针对这个问题可以运行脚本mysql_secure_installation来设置

    设置数据库管理员root口令
禁止root远程登录
删除anonymous用户帐号
删除test数据库

    2.3 MySQL组成

    2.3.1 客户端程序

    • mysql：交互式的客户端工具

    • mysqldump：备份工具，基于mysql协议向mysqld发起查询请求，并将查得的所有数据转换成 insert等写操作语句保存文本文件中

    • mysqladmin：基于mysql协议管理mysqld

    • mysqlimport：数据导入工具

      MyISAM存储引擎的管理工具：

    • myisamchk：检查MyISAM库

    • myisampack：打包MyISAM表，只读

    2.3.2 服务端程序

    • mysqld_safe
    • mysqld
    • mysqld_multi 多实例 ，示例：mysqld_multi --example

    2.3.3 用户账号

    mysql用户账号组成：

    ​ 'USERNAME'@'HOST‘

    ​ HOST限制此用户可通过哪些远程主机连接mysql服务器

    ​ 支持使用通配符：

    ​ % 匹配任意长度的任意字符 172.16.0.0/255.255.0.0 或 172.16.%.%

    ​ _ 匹配任意单个字符

    2.3.4 mysql客户端命令

    mysql命令格式：

    mysql [OPTIONS] [database]

    常用选项：

    • -u, --user= 用户名,默认为root
    • -h, --host= 服务器主机,默认为localhost
    • -p, --passowrd= 用户密码,建议使用-p,默认为空密码
    • -P, --port= 服务器端口
    • -S, --socket= 指定连接socket文件路径
    • -C, --compress 启用压缩
    • -e “SQL“ 执行SQL命令
    • -V, --version 显示版本
    • -v --verbose 显示详细信息
    • --print-defaults 获取程序默认使用的配置

    mysql 使用模式：

    交互式

    客户端命令：本地执行，每个命令都完整形式和简写格式
mysql> h, help
mysql> u，use
mysql> s，status
mysql> !，system

服务端命令：通过mysql协议发往服务器执行并取回结果，命令末尾都必须使用命令结束符号，默认为分号
mysql>SELECT VERSION();

    非交互式

    mysql –uUSERNAME -pPASSWORD < /path/somefile.sql
cat /path/somefile.sql | mysql –uUSERNAME -pPASSWORD
mysql>source   /path/from/somefile.sql

    范例：

    #临时设置提示符的格式
mysql> R (u@h) [d]>\_
PROMPT set to '(u@h) [d]>\_'
(root@localhost) [student]>

    2.3.5 mysqladmin命令

    格式：

    mysqladmin [OPTIONS] command command....

    范例：

    #查看mysql服务是否正常，如果正常提示mysqld is alive
mysqladmin -uroot -pcentos   ping

#关闭mysql服务，但mysqladmin命令无法开启
mysqladmin –uroot –pcentos shutdown

#创建数据库testdb
mysqladmin -uroot –pcentos   create testdb

#删除数据库testdb
mysqladmin -uroot -pcentos   drop testdb

#修改root密码
mysqladmin –uroot –pcentos password ‘magedu’

#日志滚动,生成新文件/var/lib/mysql/mariadb-bin.00000N
mysqladmin -uroot -pcentos flush-logs

    2.3.6 服务端配置

    服务器端可以通过命令行或者配置文件进行配置，这里主要介绍配置文件配置方式。

    服务器端配置文件：

    /etc/my.cnf #Global选项
/etc/mysql/my.cnf #Global选项
~/.my.cnf #User-specific 选项

    配置文件格式：

    [mysqld]
[mysqld_safe]
[mysqld_multi]
[mysql]
[mysqldump]
[server]
[client]
格式：parameter = value
说明：_和- 相同
 1，ON，TRUE意义相同， 0，OFF，FALSE意义相同

    服务器监听的两种socket地址：

    • ip socket: 监听在tcp的3306端口，支持远程通信 ，侦听3306/tcp端口可以在绑定有一个或全部接 口IP上
    • unix sock: 监听在sock文件上，仅支持本机通信, 如：/var/lib/mysql/mysql.sock) 说明：host为localhost 时自动使用unix sock

    可以通过修改配置文件实现只侦听本地客户端

    vim /etc/my.cnf
[mysqld]
skip-networking=1

    2.4 二进制安装MySQL

    这里以安装MySQL5.7版本为例

    官方安装手册：https://dev.mysql.com/doc/refman/5.7/en/binary-installation.html

    MySQL下载地址：https://downloads.mysql.com/archives/community/

    2.4.1 创建MySQL用户和组

    [root@localhost local]# groupadd -r -g 306 mysql
[root@localhost local]# useradd -r -g 306 -u 306 -d /data/mysql -s /bin/false mysql
[root@localhost local]# getent passwd mysql
mysql:x:306:306::/data/mysql:/bin/false

    2.4.2 准备二进制文件

    [root@localhost local]# cd /usr/local
[root@localhost local]# tar xvf mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz

[root@localhost local]# ln -s mysql-5.7.33-linux-glibc2.12-x86_64 mysql
[root@localhost local]# ls
bin  games    lib    libexec  mysql-5.7.33-linux-glibc2.12-x86_64         sbin   src
etc  include  lib64  mysql    mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz  share
[root@localhost local]# chown -R root.root /usr/local/mysql/

#将路径添加至环境变量
[root@localhost mysql]# echo 'PATH=/app/boots-dev/bin/:$PATH' > /etc/profile.d/mysql.sh
[root@localhost mysql]# . /etc/profile.d/mysql.sh

    2.4.3 准备配置文件

    [root@localhost local]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
skip_name_resolve=1
socket=/data/mysql/mysql.sock        
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock

    2.4.4 创建数据库文件

    #初始命令会读取配置文件中的配置，由于之前已经指定了数据库文件位置，所以这里不用加入数据库路径选项
[root@localhost mysql]# /usr/local/mysql/bin/mysqld --initialize --user=mysql
2021-05-17T15:36:16.457156Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-05-17T15:36:18.652116Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-05-17T15:36:18.950575Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-05-17T15:36:19.034228Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 9ff6938c-b725-11eb-b707-000c2906d605.
2021-05-17T15:36:19.038515Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-05-17T15:36:19.564502Z 0 [Warning] CA certificate ca.pem is self signed.
2021-05-17T15:36:19.707592Z 1 [Note] A temporary password is generated for root@localhost: !Mn4hek<bO%.		#系统生成的首次登陆密码

#查看数据库文件是否生成
[root@localhost mysql]# ls /data/mysql/
auto.cnf    client-cert.pem  ibdata1      mysql               public_key.pem   sys
ca-key.pem  client-key.pem   ib_logfile0  performance_schema  server-cert.pem
ca.pem      ib_buffer_pool   ib_logfile1  private_key.pem     server-key.pem

    2.4.5 准备服务脚本并启动

    #准备服务脚本
[root@localhost mysql]# cp support-files/mysql.server /etc/init.d/mysqld
[root@localhost mysql]# chkconfig --add mysqld
[root@localhost mysql]# chkconfig --list

Note: This output shows SysV services only and does not include native
      systemd services. SysV configuration data might be overridden by native
      systemd configuration.

      If you want to list systemd services use 'systemctl list-unit-files'.
      To see services enabled on particular target use
      'systemctl list-dependencies [target]'.

mysqld         	0:off	1:off	2:on	3:on	4:on	5:on	6:off
netconsole     	0:off	1:off	2:off	3:off	4:off	5:off	6:off
network        	0:off	1:off	2:on	3:on	4:on	5:on	6:off

#启动数据库
[root@localhost mysql]# service mysqld start
Starting MySQL.Logging to '/data/mysql/localhost.localdomain.err'.
... SUCCESS! 

#查看是否启动成功
[root@localhost mysql]# ss -ntl
State       Recv-Q Send-Q        Local Address:Port                       Peer Address:Port         
LISTEN      0      128                       *:22                                    *:*       
LISTEN      0      80                     [::]:3306                               [::]:*

    2.4.6 修改默认口令

    [root@localhost mysql]# mysqladmin -uroot -p'aY4lhQ/uyB+%' password database
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

#测试是否能登陆
[root@localhost mysql]# mysql -uroot -pdatabase
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or g.
......

#安全初始化
[root@localhost mysql]# mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root: 
......

    2.5 源码编译安装

    这里以安装MySQL5.7版本为例

    下载源码包：https://dev.mysql.com/downloads/mysql/

    官方参考文档：https://dev.mysql.com/doc/refman/5.7/en/installing-source-distribution.html

    注意：安装过程应保证4G以上内存

    2.5.1 安装相关依赖包

    [root@localhost ~]# yum -y install bison bison-devel zlib-devel libcurl-devel libarchive-devel
boost-devel  gcc gcc-c++ cmake ncurses-devel gnutls-devel libxml2-devel
openssl-devel libevent-devel libaio-devel

    2.5.2 创建用户

    [root@localhost local]# useradd -r -s /sbin/nologin -d /data/mysql mysql

    2.5.3 源码编译安装

    [root@localhost local]# ls
bin  etc  games  include  lib  lib64  libexec  mysql-5.7.34.tar.gz  sbin  share  src

#解压缩源码包
[root@localhost local]# tar xvf mysql-5.7.34.tar.gz

#进入目录编译安装
[root@localhost local]# cd mysql-5.7.34
[root@localhost mysql-5.7.34]# mkdir bld
[root@localhost mysql-5.7.34]# cd bld

#注意：5.7版本mysql要求Boost版本为1.59.0，由于yum版本不满足要求，所以还需再官网下载boost1.59.0版本压缩包，这里使用-DWITH_BOOST可以指定boost1.59.0版本压缩包的路径，即boost_1_59_0.tar.gz的位置
[root@localhost bld]# cmake .. -DCMAKE_INSTALL_PREFIX=/app/mysql 
-DMYSQL_DATADIR=/data/mysql/ 
-DSYSCONFDIR=/etc/ 
-DMYSQL_USER=mysql 
-DWITH_INNOBASE_STORAGE_ENGINE=1 
-DWITH_ARCHIVE_STORAGE_ENGINE=1 
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 
-DWITH_PARTITION_STORAGE_ENGINE=1 
-DWITHOUT_MROONGA_STORAGE_ENGINE=1 
-DWITH_DEBUG=0 
-DWITH_READLINE=1 
-DWITH_SSL=system 
-DWITH_ZLIB=system 
-DWITH_BOOST=/app/boots 	
-DWITH_LIBWRAP=0 
-DENABLED_LOCAL_INFILE=1 
-DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock 
-DDEFAULT_CHARSET=utf8 
-DDEFAULT_COLLATION=utf8_general_ci
[root@localhost bld]# make -j 16
#编译过程由于内存不足导致报错c++: internal compiler error，添加内存重新编译即可
[root@localhost bld]# make install

    2.5.4 配置数据库

    #配置环境变量
[root@localhost bld]# echo 'PATH=/app/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@localhost bld]# . /etc/profile.d/mysql.sh

#生成数据库文件
[root@localhost mysql]# mysqld --initialize --datadir=/data/mysql/ --user=mysql
2021-05-18T17:55:55.406121Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-05-18T17:55:55.663535Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-05-18T17:55:55.703042Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-05-18T17:55:55.760620Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 4b4b5646-b802-11eb-8afd-000c2906d605.
2021-05-18T17:55:55.772427Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-05-18T17:55:56.274206Z 0 [Warning] CA certificate ca.pem is self signed.
2021-05-18T17:55:56.660120Z 1 [Note] A temporary password is generated for root@localhost: na6t_YuJgrAs

#配置数据库配置文件
[root@localhost mysql]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
skip_name_resolve=1
socket=/data/mysql/mysql.sock        
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock

#拷贝服务启动脚本
[root@localhost mysql]# cp support-files/mysql.server /etc/init.d/mysqld
[root@localhost mysql]# chkconfig --add mysqld
[root@localhost mysql]# service mysqld start
Starting MySQL.Logging to '/data/mysql/mysql.log'.
 SUCCESS!

    2.5.5 安全初始化

    [root@localhost mysql]# mysql_secure_installation

#测试是否能登陆
[root@localhost mysql]# mysql -uroot -pdatabase
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 7
Server version: 5.7.34 Source distribution

    3 SQL语言

    3.1 关系型数据库的常见组件

    • 数据库：database
    • 表：table，行：row 列：column
    • 索引：index
    • 视图：view
    • 用户：user
    • 权限：privilege
    • 存储过程：procedure
    • 存储函数：function
    • 触发器：trigger
    • 事件调度器：event scheduler，任务计划

    3.2 SQL语言的语法标准

    3.2.1 SQL语法

    SQL语句不区分大小写，建议使用大写

    SQL语句可以多行或者单行书写，通常以;结尾

    关键词不能跨多行或简写

    注释：

    • SQL标准：

      -- 注释内容 用于单行注释，注意有空格

      /*注释内容*/ 多行注释

    • MySQL注释：

      # 注释内容

    3.2.2 数据库对象和命名

    数据库的组件（对象）：

    数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等

    命名规则：

    • 必须以字母开头，可包括数字和三个特殊字符（# _ $）
    • 不要使用MySQL的保留字
    • 同一database(Schema)下的对象不能同名

    3.2.3 SQL语句分类

    • DDL: Data Defination Language 数据定义语言

      CREATE，DROP，ALTER

    • DML: Data Manipulation Language 数据操纵语言 INSERT，DELETE，UPDATE

    • DQL：Data Query Language 数据查询语言

      SELECT

    • DCL：Data Control Language 数据控制语言

      GRANT，REVOKE，COMMIT，ROLLBACK

    官方帮助：https://dev.mysql.com/doc/refman/8.0/en/sql-statements.html

    本地mysql提供的帮助

    mysql> HELP KEYWORD

    3.3 数据类型

    官方数据类型参考链接：https://dev.mysql.com/doc/refman/8.0/en/data-types.html

    对于整数数据类型，M指示最大显示宽度。对于浮点和定点数据类型， M是可以存储的总位数。

    3.3.1 整数型

    tinyint(m) 1个字节 范围(-128~127)

    smallint(m) 2个字节 范围(-32768~32767)

    mediumint(m) 3个字节 范围(-8388608~8388607)

    int(m) 4个字节 范围(-2147483648~2147483647)

    bigint(m) 8个字节 范围(+-9.22*10的18次方)

    3.3.2 浮点型

    float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位 double(m,d) 双精度浮点型16位精度(8字节) m总个数，d小数位 设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以 实际为准，即6位

    3.3.3 定点数

    在数据库中存放的是精确值,存为十进制

    decimal(m,d) 参数m<65 是总个数，d<30且 d<m 是小数位

    MySQL5.0和更高版本将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。

    3.3.4 字符串

    char(n) 固定长度，最多255个字符

    varchar(n) 可变长度，最多65535个字符

    tinytext 可变长度，最多255个字符

    text 可变长度，最多65535个字符

    mediumtext 可变长度，最多2的24次方-1个字符

    longtext 可变长度，最多2的32次方-1个字符

    BINARY(M) 固定长度，可存二进制或字符，长度为0-M字节 VARBINARY(M) 可变长度，可存二进制或字符，允许长度为0-M字节

    内建类型：ENUM枚举, SET集合

    3.3.5 二进制数据BLOB

    BLOB和text存储方式不同，TEXT以文本方式存储，英文存储区分大小写，而Blob以二进制方式存储， 不分大小写

    3.3.6 日期时间类型

    date 日期 '2008-12-2'

    time 时间 '12:25:36'

    datetime 日期时间 '2008-12-2 22:06:44'

    timestamp 自动存储记录修改时间

    YEAR(2), YEAR(4)：年份

    timestamp字段里的时间数据会随其他字段修改的时候自动刷新，这个数据类型的字段可以存放这条记 录最后被修改的时间

    3.3.7 修饰符

    NULL 数据列可包含NULL值，默认值

    NOT NULL 数据列不允许包含NULL值，*为必填选项

    DEFAULT 默认值

    PRIMARY KEY 主键，所有记录中此字段的值不能重复，且不能为NULL

    UNIQUE KEY 唯一键，所有记录中此字段的值不能重复，但可以为NULL

    CHARACTER SET name 指定一个字符集

    AUTO_INCREMENT 自动递增，适用于整数类型

    UNSIGNED 无符号

    3.4 DDL语句

    3.4.1 create table

    获取帮助：

    HELP CREATE TABLE

    官方文档：https://dev.mysql.com/doc/refman/8.0/en/create-table.html

    创建表的三种方法

    (1) 直接创建新表

    格式：

    CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

    范例：

    #创建一个名为student的表，使其结构如下
(root@localhost) [test]> desc student;
+--------+---------------------+------+-----+---------+----------------+
| Field  | Type                | Null | Key | Default | Extra          |
+--------+---------------------+------+-----+---------+----------------+
| id     | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)         | NO   |     | NULL    |                |
| age    | tinyint(3) unsigned | YES  |     | NULL    |                |
| gender | enum('M','F')       | YES  |     | M       |                |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

#语句
(root@localhost) [test]>create table student (
id int unsigned auto_increment primary key,	#无符号的int型整数，并且自动增加设置为主键
name varchar(20) not null,	#可变字符不为空
age tinyint unsigned,	#无符号tinyint类型数据
gender enum('M','F') default 'M'	#将性别设置为一个enum枚举字符串对象，值为M和F
)ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;	#使用innodb引擎，步进为10，默认字符集为utf8

    （2)通过查询现表创建新表

    格式：

    CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    [IGNORE | REPLACE]
    [AS] query_expression

    (3) 通过复制现存的表的表结构创建，但不复制数据

    格式：

    CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }

    3.4.2 查看表

    查看支持的engine类型

    SHOW ENGINES;

    查看表：

    SHOW TABLES [FROM db_name]

    查看表结构：

    DESC [db_name.]tb_name
SHOW COLUMNS FROM [db_name.]tb_name

    查看表创建命令：

    SHOW CREATE TABLE tbl_name

    查看表状态：

    SHOW TABLE STATUS LIKE 'tbl_name’

    查看库中所有表状态：

    SHOW TABLE STATUS FROM db_name

    3.4.3 修改和删除表

    删除表

    DROP [TEMPORARY] TABLE [IF EXISTS]
    tbl_name [, tbl_name] ...
    [RESTRICT | CASCADE]

    修改表

    ALTER TABLE tbl_name
    [alter_option [, alter_option] ...]
    [partition_options]

    官方文档：https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

    范例：

    ALTER TABLE students RENAME s1;
ALTER TABLE s1 ADD phone varchar(11) AFTER name;
ALTER TABLE s1 MODIFY phone int;
ALTER TABLE s1 CHANGE COLUMN phone mobile char(11);
ALTER TABLE s1 DROP COLUMN mobile;
ALTER TABLE s1 character set utf8;
ALTER TABLE s1 change name name varchar(20) character set utf8;
ALTER TABLE students ADD gender ENUM('m','f');
ALETR TABLE students CHANGE id sid int UNSIGNED NOT NULL PRIMARY KEY;
ALTER TABLE students DROP age;
DESC students;

    3.5 DML语句

    3.5.1 insert

    一次插入一行或多行数据

    格式：

    INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    {VALUES | VALUE} (value_list) [, (value_list)] ...
    [ON DUPLICATE KEY UPDATE assignment_list]

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    SET assignment_list
    [ON DUPLICATE KEY UPDATE assignment_list]

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    SELECT ...
    [ON DUPLICATE KEY UPDATE assignment_list]

    也可以写成

    INSERT tbl_name [(col1,...)] VALUES (val1,...), (val21,...)

    3.5.2 update

    格式：

    UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

    注意：一定要加入条件限制，否则会更新所有符合条件数据，使用以下方式来避免这类错误

    mysql -U | --safe-updates| --i-am-a-dummy
[root@centos8 ~]#vim /etc/my.cnf
[mysql]
safe-updates

    3.5.3 delete

    格式：

    DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
   [WHERE where_condition]
   [ORDER BY ...]
   [LIMIT row_count]

    注意：删除表中数据，但不会自动缩减数据文件的大小。

    如果想清空表，保留表结构，也可以使用下面语句，此语句会自动缩减数据文件的大小。

    TRUNCATE TABLE tbl_name;

    缩减表大小

    OPTIMIZE TABLE tb_name

    3.6 DQL语句

    3.6.1 单表查询

    格式：

    SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    [into_option]
    [FROM table_references
      [PARTITION partition_list]]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
    [HAVING where_condition]
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [into_option]
    [FOR {UPDATE | SHARE}
        [OF tbl_name [, tbl_name] ...]
        [NOWAIT | SKIP LOCKED]
      | LOCK IN SHARE MODE]
    [into_option]

    7⼤⼦句书写顺序

    （1）from：从哪些表中筛选

    （2）on：关联多表查询时，去除笛卡尔积

    （3）where：从表中筛选的条件

    ​ 过滤条件：布尔型表达式

    ​ 算术操作符：+, -, *, /, %

    ​ 比较操作符：=,<=>（相等或都为空）, <>, !=(非标准SQL), >, >=, <, <=

    ​ BETWEEN min_num AND max_num

    ​ IN (element1, element2, ...)

    ​ IS NULL

    ​ IS NOT NULL

    ​ DISTINCT 去除重复行，范例：SELECT DISTINCT gender FROM students;

    ​ LIKE: % 任意长度的任意字符 _ 任意单个字符

    ​ RLIKE：正则表达式，索引失效，不建议使用

    ​ REGEXP：匹配字符串可用正则表达式书写模式，同上

    ​ 逻辑操作符：NOT，AND，OR，XOR

    （4）group by：根据指定的条件把查询结果进行“分组”以用于做“聚合”运算

    ​ 常见聚合函数：avg(), max(), min(), count(), sum()

    ​ 一旦分组group by ，select语句后只跟分组的字段，聚合函数

    （5）having：在统计结果中再次筛选

    （6）order by：排序

    ​ 降序：desc

    ​ 升序：要么默认，要么加asc

    （7）limit：对查询的结果进行输出行数数量限制

    必须按照（1）-（7）的顺序【编写】⼦句。

    select语句执行流程

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

    having与where的区别？

    （1）where是从表中筛选的条件，而having是分组（统计）结果中再次筛选

    （2）where后⾯不能加“分组/聚合函数”，而having后⾯可以跟分组函数

    范例：

    #查询每个部门的男生的人数，并且显示人数超过5人的，按照人数降序排列，
(root@localhost) [test]>  SELECT did,COUNT(*) "人数"
    -> FROM t_employee
    -> WHERE gender = '男'
    -> GROUP BY did
    -> HAVING COUNT(*)>5
    -> ORDER BY 人数 DESC;
+------+--------+
| did  | 人数   |
+------+--------+
|    1 |     11 |
+------+--------+
1 row in set (0.00 sec)

#查询各个部门的岗位工资总和是多少
mysql> SELECT department_id,job_id,SUM(salary)
    -> FROM employees
    -> GROUP BY department_id,job_id;
+---------------+------------+-------------+
| department_id | job_id     | SUM(salary) |
+---------------+------------+-------------+
|            90 | AD_PRES    |    24000.00 |
|            90 | AD_VP      |    34000.00 |
|            60 | IT_PROG    |    28800.00 |
|           100 | FI_MGR     |    12000.00 |
|           100 | FI_ACCOUNT |    39600.00 |
|            30 | PU_MAN     |    11000.00 |
|            30 | PU_CLERK   |    13900.00 |

#获取最小⼯资小于2000的职位
mysql> select job_id,min(salary)
    -> from employees
    -> group by job_id
    -> having min(salary)<3000
    -> order by min(salary);
+----------+-------------+
| job_id   | min(salary) |
+----------+-------------+
| ST_CLERK |     2100.00 |
| PU_CLERK |     2500.00 |
| SH_CLERK |     2500.00 |
+----------+-------------+

    3.6.2 多表查询

    3.6.2.1 子查询

    在SQL语句嵌套着查询语句，不过性能较差

    where型

    #返回单个值
(root@localhost) [hellodb]> select name,age from students where age > (select avg(age) from teachers);
+-------------+-----+
| name        | age |
+-------------+-----+
| Sun Dasheng | 100 |
+-------------+-----+
1 row in set (0.01 sec)

#使用in返回多个值
(root@localhost) [hellodb]> select name,age from students where age in (select age from teachers);
+-------------+-----+
| name        | age |
+-------------+-----+
| Shi Zhongyu |  77 |
+-------------+-----+
1 row in set (0.00 sec)

    exists型

    #选出ID和老师相同的学生
(root@localhost) [hellodb]> select * from students s where exists (select * from teachers t where s.teacherid=t.tid);
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu |  77 | M      |       2 |         3 |
|     4 | Ding Dian   |  32 | M      |       4 |         4 |
|     5 | Yu Yutong   |  26 | M      |       3 |         1 |
+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)
上述语句把students的记录逐条代入到Exists后面的子查询中，如果子查询结果集不为空，即说
明存在，那么这条students的记录出现在最终结果集，否则被排除

    from型

    (root@localhost) [hellodb]> SELECT s.aage,s.ClassID FROM (SELECT avg(Age) AS aage,ClassID FROM students
    -> WHERE ClassID IS NOT NULL GROUP BY ClassID) AS s WHERE s.aage>30;
+---------+---------+
| aage    | ClassID |
+---------+---------+
| 54.3333 |       2 |
| 46.0000 |       5 |
+---------+---------+
2 rows in set (0.01 sec)

    3.6.2.2 联合查询

    union实现多个表之间纵向合并

    范例：

    #将teachers和students表纵向合并
(root@localhost) [hellodb]> SELECT Name,Age FROM students UNION SELECT Name,Age FROM teachers;
+---------------+-----+
| Name          | Age |
+---------------+-----+
| Shi Zhongyu   |  77 |
| Shi Potian    |  22 |
| Xie Yanke     |  53 |
| Ding Dian     |  32 |
| Yu Yutong     |  26 |
| Shi Qing      |  46 |
| Xi Ren        |  19 |
......

    3.6.2.3 交叉连接(笛卡尔乘积)

    利用 cross join实现

    (root@localhost) [hellodb]>  select * from teachers , students;
(root@localhost) [hellodb]> select * from students cross join teachers;
+-----+---------------+-----+--------+-------+---------------+-----+--------+---------+-----------+
| TID | Name          | Age | Gender | StuID | Name          | Age | Gender | ClassID | TeacherID |
+-----+---------------+-----+--------+-------+---------------+-----+--------+---------+-----------+
|   1 | Song Jiang    |  45 | M      |     1 | Shi Zhongyu   |  77 | M      |       2 |         3 |
|   2 | Zhang Sanfeng |  94 | M      |     1 | Shi Zhongyu   |  77 | M      |       2 |         3 |
|   3 | Miejue Shitai |  77 | F      |     1 | Shi Zhongyu   |  77 | M      |       2 |         3 |
|   4 | Lin Chaoying  |  93 | F      |     1 | Shi Zhongyu   |  77 | M      |       2 |         3 |
|   1 | Song Jiang    |  45 | M      |     2 | Shi Potian    |  22 | M      |       1 |         7 |

    3.6.2.4 关联查询

    image

    内连接

    #格式
select 字段列表
from A表 inner join B表
on 关联条件
where 等其他子句;

或

select 字段列表
from A表 , B表
where 关联条件 and 等其他子句;

#查询学生的老师
(root@localhost) [hellodb]> select * from students inner join teachers on students.teacherid=teachers.tid;
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name        | Age | Gender | ClassID | TeacherID | TID | Name          | Age | Gender |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
|     1 | Shi Zhongyu |  77 | M      |       2 |         3 |   3 | Miejue Shitai |  77 | F      |
|     4 | Ding Dian   |  32 | M      |       4 |         4 |   4 | Lin Chaoying  |  93 | F      |
|     5 | Yu Yutong   |  26 | M      |       3 |         1 |   1 | Song Jiang    |  45 | M      |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
3 rows in set (0.00 sec)

(root@localhost) [hellodb]> select * from students,teachers where students.teacherid=teachers.tid;
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name        | Age | Gender | ClassID | TeacherID | TID | Name          | Age | Gender |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
|     1 | Shi Zhongyu |  77 | M      |       2 |         3 |   3 | Miejue Shitai |  77 | F      |
|     4 | Ding Dian   |  32 | M      |       4 |         4 |   4 | Lin Chaoying  |  93 | F      |
|     5 | Yu Yutong   |  26 | M      |       3 |         1 |   1 | Song Jiang    |  45 | M      |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
3 rows in set (0.00 sec)

    左右外连接

    #左外连接格式
select 字段列表
from A表 left join B表
on 关联条件
where 等其他子句;

#如果连接的部分右表没有值则会显示为null
(root@localhost) [hellodb]>  select s.stuid,s.name,s.age,s.teacherid,t.tid,t.name,t.age
    -> from students as s left outer join teachers as t on s.teacherid=t.tid;
+-------+---------------+-----+-----------+------+---------------+------+
| stuid | name          | age | teacherid | tid  | name          | age  |
+-------+---------------+-----+-----------+------+---------------+------+
|     5 | Yu Yutong     |  26 |         1 |    1 | Song Jiang    |   45 |
|     1 | Shi Zhongyu   |  77 |         3 |    3 | Miejue Shitai |   77 |
|     4 | Ding Dian     |  32 |         4 |    4 | Lin Chaoying  |   93 |
|     2 | Shi Potian    |  22 |         7 | NULL | NULL          | NULL |
|     3 | Xie Yanke     |  53 |        16 | NULL | NULL          | NULL |
|     6 | Shi Qing      |  46 |      NULL | NULL | NULL          | NULL |
|     7 | Xi Ren        |  19 |      NULL | NULL | NULL          | NULL |
|     8 | Lin Daiyu     |  17 |      NULL | NULL | NULL          | NULL |
|     9 | Ren Yingying  |  20 |      NULL | NULL | NULL          | NULL |
|    10 | Yue Lingshan  |  19 |      NULL | NULL | NULL          | NULL |
|    11 | Yuan Chengzhi |  23 |      NULL | NULL | NULL          | NULL |
|    12 | Wen Qingqing  |  19 |      NULL | NULL | NULL          | NULL |
|    13 | Tian Boguang  |  33 |      NULL | NULL | NULL          | NULL |
|    14 | Lu Wushuang   |  17 |      NULL | NULL | NULL          | NULL |
|    15 | Duan Yu       |  19 |      NULL | NULL | NULL          | NULL |
|    16 | Xu Zhu        |  21 |      NULL | NULL | NULL          | NULL |
|    17 | Lin Chong     |  25 |      NULL | NULL | NULL          | NULL |
|    18 | Hua Rong      |  23 |      NULL | NULL | NULL          | NULL |
|    19 | Xue Baochai   |  18 |      NULL | NULL | NULL          | NULL |
|    20 | Diao Chan     |  19 |      NULL | NULL | NULL          | NULL |
|    21 | Huang Yueying |  22 |      NULL | NULL | NULL          | NULL |
|    22 | Xiao Qiao     |  20 |      NULL | NULL | NULL          | NULL |
|    23 | Ma Chao       |  23 |      NULL | NULL | NULL          | NULL |
|    24 | Xu Xian       |  27 |      NULL | NULL | NULL          | NULL |
|    25 | Sun Dasheng   | 100 |      NULL | NULL | NULL          | NULL |
+-------+---------------+-----+-----------+------+---------------+------+
25 rows in set (0.01 sec)

#右外连接格式
select 字段列表
from A表 right join B表
on 关联条件
where 等其他子句;

(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 |
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
|     1 | Shi Zhongyu |   77 | M      |       2 |         3 |   3 | Miejue Shitai |  77 | F      |
|     4 | Ding Dian   |   32 | M      |       4 |         4 |   4 | Lin Chaoying  |  93 | F      |
|     5 | Yu Yutong   |   26 | M      |       3 |         1 |   1 | Song Jiang    |  45 | M      |
|  NULL | NULL        | NULL | NULL   |    NULL |      NULL |   2 | Zhang Sanfeng |  94 | M      |
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+

    3.6.2.5 完全外连接

    Mysql不支持上图语句，所以这里我们使用左外的A,union,右外的B

    #格式
select 字段列表
from A表 left join B表
on 关联条件
where 等其他子句

union

select 字段列表
from A表 right join B表
on 关联条件
where 等其他子句;

(root@localhost) [hellodb]> select * from students left join teachers  on
    -> students.teacherid=teachers.tid
    -> union
    -> select * from students right join teachers on students.teacherid=teachers.tid;
 所得出的表就是左外和右外表相联合

    3.6.2.6 自连接

    两个关联查询的表是同⼀张表，通过取别名的⽅式来虚拟成两张表

    #格式
select 字段列表
from 表名 别名1 inner/left/right join 表名 别名2
on 别名1.关联字段 = 别名2的关联字段
where 其他条件

#查询员工的编号，姓名，薪资和他领导的编号，姓名，薪资
#这些数据全部在员工表中
#把t_employee表，即当做员工表，又当做领导表
mysql> SELECT emp.eid "员工编号",emp.ename "员工姓名",emp.salary "工资",mgr.eid "领导编号",mgr.ename "领导姓名",mgr.salary "领 导工资"
    -> FROM t_employee emp INNER JOIN t_employee mgr
    -> ON emp.mid=mgr.eid;
+--------------+--------------+---------+--------------+--------------+--------------+
| 员工编号     | 员工姓名     | 工资    | 领导编号     | 领导姓名     | 领导工资     |
+--------------+--------------+---------+--------------+--------------+--------------+
|            1 | 孙红雷       | 8000.46 |            7 | 贾乃亮       |        15700 |
|            2 | 何炅         | 7000.67 |            7 | 贾乃亮       |        15700 |
|            3 | 邓超         |    8000 |            7 | 贾乃亮       |        15700 |
|            4 | 黄晓明       |    9456 |           22 | 刘烨         |       130990 |
|            5 | 陈赫         |    8567 |            7 | 贾乃亮       |        15700 |
|            6 | 韩庚         |   12000 |            7 | 贾乃亮       |        15700 |

    练习：

    1、 导入hellodb.sql生成数据库

    (1) 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄

    (root@localhost) [hellodb]> select Name,age from students where age > 25 and Gender='M';
+--------------+-----+
| Name         | age |
+--------------+-----+
| Shi Zhongyu  |  77 |
| Xie Yanke    |  53 |
| Ding Dian    |  32 |
| Yu Yutong    |  26 |
| Shi Qing     |  46 |
| Tian Boguang |  33 |
| Xu Xian      |  27 |
| Sun Dasheng  | 100 |
+--------------+-----+
8 rows in set (0.00 sec)

    (2) 以ClassID为分组依据，显示每组的平均年龄

    (root@localhost) [hellodb]> select ClassID,avg(age) from students where ClassID is not null group by ClassID
D;
+---------+----------+
| ClassID | avg(age) |
+---------+----------+
|       1 |  20.5000 |
|       2 |  54.3333 |
|       3 |  20.2500 |
|       4 |  24.7500 |
|       5 |  46.0000 |
|       6 |  20.7500 |
|       7 |  19.6667 |
+---------+----------+
7 rows in set (0.00 sec)

    (3) 显示第2题中平均年龄大于30的分组及平均年龄

    (root@localhost) [hellodb]> select ClassID,avg(age) from students where ClassID is not null group by ClassID having avg(age) > 30;
+---------+----------+
| ClassID | avg(age) |
+---------+----------+
|       2 |  54.3333 |
|       5 |  46.0000 |
+---------+----------+
2 rows in set (0.01 sec)

    (4) 显示以L开头的名字的同学的信息

    (root@localhost) [hellodb]> select * from students where name like 'L%';
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|     8 | Lin Daiyu   |  17 | F      |       7 |      NULL |
|    14 | Lu Wushuang |  17 | F      |       3 |      NULL |
|    17 | Lin Chong   |  25 | M      |       4 |      NULL |
+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.01 sec)

    2、数据库授权magedu用户，允许192.168.1.0/24网段可以连接mysql

    (root@localhost) [hellodb]> GRANT ALL ON mysql TO magedu@'192.168.1.%' IDENTIFIED BY 'database';
Query OK, 0 rows affected, 1 warning (0.01 sec)

    3.7 用户和权限管理

    3.7.1 用户管理

    和用户相关的元数据数据库是mysql,其中包含了数据库系统授权表

    (root@localhost) [mysql]> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |

    用户账号的组成

    'USERNAME'@'HOST'
@'HOST'表示主机名
如：user2@'192.168.1.%'

    创建用户

    CREATE USER 'USERNAME'@'HOST' [IDENTIFIED BY 'password']；

    用户重命名：RENAME USER

    RENAME USER old_user_name TO new_user_name;

    删除用户：

    DROP USER 'USERNAME'@'HOST‘

    修改用户密码：

    #方法1
SET PASSWORD FOR 'user'@'host' = PASSWORD(‘password');

#方法2
UPDATE mysql.user SET password=PASSWORD('password') WHERE clause;
#mariadb 10.3
update mysql.user set authentication_string=password('ubuntu') where
user='mage';

#此方法需要执行下面指令才能生效：
FLUSH PRIVILEGES;

    注意：新版mysql中用户密码可以保存在mysql.user表的authentication_string字段中，而且authentication_string 优先生效

    忘记管理员密码解决方法：

    #修改数据库配置文件
[root@centos8 ~]#vim /etc/my.cnf
[mysqld]
skip-grant-tables             #跳过用户密码认证                                    
          skip-networking		#防止远程无密码登陆数据库
[root@centos8 ~]#systemctl restart mariadb

#修改root用户密码
[root@centos8 ~]#mysql
MariaDB [(none)]> update mysql.user set authentication_string=password('ubuntu')
where user='root';

[root@centos8 ~]#systemctl restart mariadb

#改完注释掉选项
[root@centos8 ~]#vim /etc/my.cnf
[mysqld]
#skip-grant-tables                                                              
        #skip-networking
[root@centos8 ~]#systemctl restart mariadb

#测试是否能使用新密码连接
[root@centos8 ~]#mysql -uroot -pubuntu

    3.7.2 权限管理

    #格式
GRANT priv_type [(column_list)],... ON [object_type] priv_level TO 'user'@'host'
[IDENTIFIED BY 'password'] [WITH GRANT OPTION];

priv_type: ALL [PRIVILEGES]

object_type:TABLE | FUNCTION | PROCEDURE

priv_level: *(所有库) |*.*   | db_name.* | db_name.tbl_name | tbl_name(当前库
的表) | db_name.routine_name(指定库的函数,存储过程,触发器)

    参考链接：https://dev.mysql.com/doc/refman/5.7/en/grant.html

    范例：

    GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost‘;

GRANT ALL ON wordpress.* TO wordpress@'192.168.8.%' IDENTIFIED BY 'database';

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.8.%' IDENTIFIED BY 'database'
WITH GRANT OPTION;

    取消授权：REVOKE

    #格式
REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ... ON
[object_type] priv_level FROM user [, user] ...

REVOKE DELETE ON testdb.* FROM 'testuser'@‘172.16.0.%’;

    参考链接：https://dev.mysql.com/doc/refman/5.7/en/revoke.html

    查看用户的权限

    Help SHOW GRANTS
SHOW GRANTS FOR 'user'@'host';
SHOW GRANTS FOR CURRENT_USER[()];

    4 MySql架构

    4.1 MySql架构

    MySql架构分层

    1）网络连接层

    2）服务层

    3）存储引擎层

    4）系统文件层

    image

    网络连接层：

    客户端连接器:提供与mysql服务器的连接，每种语言(java, c, php)通过自己的api技术维护与mysql服务器的连接

    服务层：

    • 连接池（Connection Pool）：负责存储和管理客户端与数据库的连接，一个线程负责管理一个连接

    • 系统管理和控制工具：如备份恢复、安全管理、集群管理等

    • SQL接口（Sql interface ）：用于接受客户端的各种SQl命令，例如DDL, DML，存储过程，视图，触发器等

    • 解析器（Parser）：负责将请求的SQL解析成一个解析树，然后根据一些MySql规则进一步检查解析树是否合法

    • 查询优化器（Query Optimizer）：在解析树通过解析器语法检查之后，查询优化器将其转换为执行计划，然后与存储引擎进行交互

      例如：select uid,name from user where gender=1;

      1）Select首先根据where语句选择，而不是查询所有数据然后过滤

      2）Select query基于uid和name列进行查询

      3）将两个查询条件联合起来生成查询结果

    • 缓存（Cache）：缓存是由一系列小缓存组成。比如表缓存，记录缓存，权限缓存，引擎缓存，如果查询缓存中有命中的查询结果，查询语句就可以直接去查询缓存中取数据

    存储引擎：

    在MySQL中存储引擎负责存储和读取数据，并且和底层的文件系统交互。存储引擎是MySQL的插件，主流的两种存储引擎是MyISAM 和InnoDB

    文件系统层：

    包括日志文件、配置文件、数据文件、pid文件、socket文件

    4.2 存储引擎

    MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的 存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力,此种技术称为存储擎,MySQL 支 持多种存储引擎其中目前应用最广泛的是InnoDB和MyISAM两种

    官方参考资料：https://dev.mysql.com/doc/refman/8.0/en/storage-engines.html

    4.2.1 MyISAM存储引擎和 InnoDB引擎

    两种引擎主要特性

    特性 MyISAM InnoDB
    事务(transactions) 不支持 支持
    MVCC 不支持 支持
    Locking granularity table row
    数据缓存 不支持 支持
    索引缓存 支持 支持
    聚簇索引 不支持 支持
    读写阻塞 读时不能写 事务隔离级别相关

    文件

    文件 MyISAM InnoDB
    表格定义 tbl_name.frm tbl_name.frm
    数据文件 tbl_name.MYD tbl_name.idb
    索引文件 tbl_name.MYI tbl_name.idb

    每个表单独使用一个表空间存储表的数据和索引

    两类文件放在对应每个数据库独立目录中
数据文件(存储数据和索引)：tb_name.ibd
表格式定义：tb_name.frm

    4.2.2 存储引擎的管理

    查看MySQL支持的存储引擎

    show engines

    查看默认的存储引擎

    show variables like '%storage_engine%';

    设置默认的存储引擎

    vim /etc/my.cnf
[mysqld]
default_storage_engine= InnoDB

    查看库中所有表使用的存储引擎

    show tables status from db_name;

    查看库中指定表的存储引擎

    show table status like 'tb_name';
show create table tb_name;

    设置表的存储引擎

    create table tb_name(...) engine=innodb;
alter table tb_name engine=innodb;

    4.3 Mysql中数据库和服务器配置

    4.3.1 MySQL中的数据库

    初始的Mysql自带数据库

    mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

    • MySQL

      是mysql的核心数据库，类似于Sql Server中的master库，主要负责存储数据库的用户、权限设 置、关键字等mysql自己需要使用的控制和管理信息

    • performance_schema数据库

      主要用于收集数据库服务器性能参数,库里表的存储引擎均为 PERFORMANCE_SCHEMA，用户不能创建存储引擎为PERFORMANCE_SCHEMA的表

    • sys数据库

      目标是把 performance_schema的把复杂度降低，让DBA能更好的阅读这个库里的内容。让DBA更快的了 解DB的运行情况

    • information_schema数据库

      一个虚拟数据库，物理上并不存在information_schema数据库类似与“数 据字典”，提供了访问数据库元数据的方式，即数据的数据。比如数据库名或表名，列类型，访问 权限（更加细化的访问方式）

    4.3.2 服务器状态配置

    官方文档：

    https://dev.mysql.com/doc/refman/8.0/en/server-option-variable-reference.html

    服务器选项：

    #查看mysqld可用选项列表和及当前值
[root@localhost mysql]# /app/mysql/bin/mysql --verbose --help

#获取mysqld当前启动选项
[root@localhost mysql]# mysqld --print-defaults
mysqld would have been started with the following arguments:
--datadir=/data/mysql --skip_name_resolve=1 --socket=/data/mysql/mysql.sock --log-error=/data/mysql/mysql.log --pid-file=/data/mysql/mysql.pid

    通常服务器选项有两种设置方式

    #1.在命令行中设置
[root@localhost mysql]# /app/mysql/bin/mysqld --basedir=/usr

#2.在配置文件中my.cnf设置
vim /etc/my.cnf
[mysqld]
skip_name_resolve=1

    查看服务器变量

    SHOW GLOBAL VARIABLES; #只查看global变量
SHOW [SESSION] VARIABLES;#查看所有变量(包括global和session)

#查看指定的系统变量
SHOW VARIABLES LIKE 'VAR_NAME';
SELECT @@VAR_NAME;

    修改变量

    #修改全局变量
SET GLOBAL system_var_name=value;
SET @@global.system_var_name=value;

#修改会话变量
SET [SESSION] system_var_name=value;
SET @@[session.]system_var_name=value;

    4.4 查询缓存

    4.4.1 查询缓存原理

    image

    缓存SELECT操作或预处理查询的结果集和SQL语句，当有新的SELECT语句或预处理查询语句请求，先 去查询缓存，判断是否存在可用的记录集，判断标准：与缓存的SQL语句，是否完全一样，区分大小写

    查询缓存相关的服务器变量

    • query_cache_type：是否开启缓存功能，取值为ON, OFF, DEMAND
    • query_cache_size：查询缓存总共可用的内存空间；单位字节，必须是1024的整数倍，最小值 40KB，低于此值有警报，当此值为0时也表示未开启查询缓存

    更多选项参考官方文档：

    https://dev.mysql.com/doc/refman/5.7/en/query-cache.html

    mysql> show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |

    查询缓存相关的状态变量

    mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1031832 |
| Qcache_hits             | 0       |
| Qcache_inserts          | 0       |
| Qcache_lowmem_prunes    | 0       |

    4.4.2 MySQL 8.0 变化

    MySQL8.0 取消查询缓存的功能

    有时因为查询缓存往往弊大于利。比如:查询缓存的失效非常频繁，只要有对一个表的更新，这个表 上的所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清 空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务有一张静态表，很长 时间更新一次，比如系统配置表，那么这张表的查询才适合做查询缓存。

    目前大多数应用都把缓存做到了应用逻辑层，比如:使用redis或者memcache

    4.5 INDEX索引

    索引：是排序的快速查找的特殊数据结构，定义作为查找条件的字段上，又称为键key，就像是每本书的目录索引通过存储 引擎实现，有了索引能以降低服务需要扫描的数据量，减少了IO次数，提高查询语句执行的速度

    4.5.1 索引结构

    B+Tree索引

    image

    B+Tree索引：按顺序存储，每一个叶子节点到根结点的距离是相同的；左前缀索引，适合查询范围类的 数据

    B-tree和B+Tree唯一不同之处就是B+Tree是将数据存储在页上

    B+Tree索引的限制：

    • 如不从最左列开始，则无法使用索引，如：查找名为huanhuan，或姓为g结尾
    • 不能跳过索引中的列：如：查找姓li，年龄30的，只能使用索引第一列

    Hash索引

    Hash索引：基于哈希表实现，只有精确匹配索引中的所有列的查询才有效，索引自身只存储索引列对 应的哈希值和数据指针，索引结构紧凑，查询性能好

    聚簇和非聚簇索引

    • 聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据
    • 非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因

    4.5.2 索引优化

    • 在where条件中，始终将索引列单独放在比较符号的一侧，尽量不要在列上进行运算（函数 操作和表达式操作）
    • 左前缀索引：构建指定索引字段的左侧的字符数，要通过索引选择性（不重复的索引值和数据表的 记录总数的比值）来评估，尽量使用短索引，如果可以，应该制定一个前缀长度
    • 多列索引：AND操作时更适合使用多列索引，而非为每个列创建单独的索引
    • 选择合适的索引列顺序：无排序和分组时，将选择性最高放左侧
    • 只要列中含有NULL值，就最好不要在此列设置索引，复合索引如果有NULL值，此列在使用时也 不会使用索引
    • 对于like语句，以 % 或者 _ 开头的不会使用索引，以 % 结尾会使用索引
    • 对于经常在where子句使用的列，最好设置索引
    • 对于有多个列where或者order by子句，应该建立复合索引
    • 尽量不要使用not in和<>操作,虽然可能使用索引,但性能不高

    官方文档：https://dev.mysql.com/doc/refman/8.0/en/optimization-indexes.html

    4.5.3 管理索引

    创建索引

    CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

    删除索引

    DROP INDEX index_name ON tbl_name;
ALTER TABLE tbl_name DROP INDEX index_name(index_col_name);

    查看索引

    SHOW INDEXES FROM [db_name.]tbl_name;

    优化表空间：

    #用于删除数据库后仍然占用空间情况
OPTIMIZE TABLE tb_name;

    4.5.4 EXPLAIN 工具

    可以通过EXPLAIN来分析索引的有效性,获取查询执行计划信息，用来查看查询优化器如何执行查询

    官方文档：https://dev.mysql.com/doc/refman/8.0/en/using-explain.html

    EXPLAIN SELECT clause

    EXPLAN输出格式说明： type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ，一般来说，得保证查询至少达到range级别，最好能达到ref

    4.6 并发控制

    4.6.1 锁机制

    锁：

    • 读锁：共享锁，也称为 S 锁,只读不可写（包括当前事务） ，多个读互不阻塞
    • 写锁：独占锁，排它锁，也称为 X 锁,写锁会阻塞其它事务（不包括当前事务）的读和写

    锁粒度：

    • 表级锁：MyISAM
    • 行级锁：InnodB

    分类：

    • 隐式锁：由存储引擎自动施加锁
    • 显式锁：用户手动请求

    显示锁的使用：

    官方文档：https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html

    4.6.2 事务

    1、事务简介

    事务Transactions：一组原子性的SQL语句，或一个独立工作单元

    事务的作用：保证所有事务都作为⼀个⼯作单元来执⾏，即使出现了故障，都不能改变 这种执⾏⽅式。当在⼀个事务中执⾏多个操作时，要么所有的事务都被提交(commit)，那么这些修改 就永久地保存下来；要么数据库管理系统将放弃所作的所有修改，整个事务回滚(rollback)到最初状态。最常见的例子就是银行卡转账，有了事务就可以防止转出和转入金额不一致情况。

    2、事务的ACID属性：

    • A：atomicity原子性；也就是不可分割，整个事务要么全都执行，要么都不执行
    • C：consistency一致性；数据库总是从一个一致性状态转换为另一个一致性状态
    • I：Isolation隔离性；一个事务所做出的操作在提交之前，是不能为其它事务所见；隔离有多种隔 离级别，实现并发
    • D：durability持久性；一旦事务提交，其所做的修改会永久保存于数据库中

    3、事务管理

    官方文档：https://dev.mysql.com/doc/refman/8.0/en/commit.html

    显示启动事务

    BEGIN
BEGIN WORK
START TRANSACTION

    结束事务：

    #提交
COMMIT
#回滚
ROLLBACK

    注意：DDL语句是不支持ROLLBACK的

    在MySQL中事务默认是自动提交的，也可以修改这个选项

    set autocommit={1|0} 
默认为1，为0时设为非自动提交
若设置为0表示我们的DML操作都需使用commit提交后才会生效

    事务还支持设置保存点，类似于虚拟机中的快照

    SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier

    查看当前事务：

    #查看当前正在进行的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

#查看当前锁定的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

#查看当前等锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

    4、事务的隔离级别

    数据库事务的隔离性：数据库系统必须具有隔离并发运⾏各个事务的能⼒, 使它们不会相互影响, 避免各 种并发问题。⼀个事务与其他事务隔离的程度称为隔离级别。数据库规定了多种事务隔离级别, 不同隔 离级别对应不同的⼲扰程度, 隔离级别越⾼, 数据⼀致性就越好, 但并发性越弱。

    数据库提供的 4 种事务隔离级别：

    隔离级别
    read-uncommitted 允许事务读取其他事务未提交和已提交的数据。会出现脏读、不可重复读、 幻读 问题
    read-committed 只允许事务读取其他事务已提交的数据。可以避免脏读，但仍然会出现不可重复读、幻读问题
    repeatable-read 确保事务可以多次从⼀个字段中读取相同的值。在这个事务持续期间，禁⽌其 他事务对这个字段进⾏更新。可以避免脏读和不可重复读。但是幻读问题仍然 存在。
    serializable 确保事务可以从⼀个表中读取相同的⾏，相同的记录。在这个事务持续期间， 禁⽌其他事务对该表执⾏插⼊、更新、删除操作。所有并发问题都可以避免， 但性能⼗分低下。

    注意：在mysql 中REPEATABLE READ的隔离级别也可以避免幻读。可以在官方文档看到详细说明https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html

    MVCC（多版本并发控制机制）只在REPEATABLE READ和READ COMMITTED两个隔离级别下工作。其他两个隔离级别都和MVCC不兼容,因为READ UNCOMMITTED总是读取最新的数据行，而不是符合当前事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁

    指定事务隔离级别：

    • 服务器变量tx_isolation指定，默认为REPEATABLE-READ，可在GLOBAL和SESSION级进行设置

      SET tx_isolation='READ-UNCOMMITTED|READ-COMMITTED|REPEATABLEREAD|SERIALIZABLE'

    • 服务器选项中指定

      vim /etc/my.cnf
[mysqld]
transaction-isolation=SERIALIZABLE

    4.7 日志管理

    4.7.1 事务日志

    事务日志记录了所有的事务操作，包括未提交的事务，而且它是先于数据库数据的

    事务日志分为Redo Log和Undo Logs，它是由事务型存储引擎自行管理和使用

    官方文档：https://dev.mysql.com/doc/refman/8.0/en/innodb-redo-log.html

    事务日志性能优化

    innodb_flush_log_at_trx_commit=0|1|2

1 此为默认值，日志缓冲区将写入日志文件，并在每次事务后执行刷新到磁盘。 这是完全遵守ACID特性

0 提交时没有写磁盘的操作; 而是每秒执行一次将日志缓冲区的提交的事务写入刷新到磁盘。 这样可提供
更好的性能，但服务器崩溃可能丢失最后一秒的事务

2 每次提交后都会写入OS的缓冲区，但每秒才会进行一次刷新到磁盘文件中。 性能比0略差一些，但操作系
统或停电可能导致最后一秒的交易丢失

    因此0和2模式性能最好，而1模式更加安全，一般为了性能考虑我们会选择2模式

    1.配置为2和配置为0，性能差异并不大，因为将数据从Log Buffer拷贝到OS cache，虽然跨越用户态与内
核态，但毕竟只是内存的数据拷贝，速度很快

2.配置为2和配置为0，安全性差异巨大，操作系统崩溃的概率相比MySQL应用程序崩溃的概率，小很多，设置
为2，只要操作系统不奔溃，也绝对不会丢数据

    设置为1，同时sync_binlog = 1表示最高级别的容错

    其他相关变量

    #使用show variables like '%innodb_log%';查看

innodb_log_file_size   50331648 每个日志文件大小
innodb_log_files_in_group  2     日志组成员个数
innodb_log_group_home_dir ./ 事务文件路径
innodb_flush_log_at_trx_commit 默认为1

    官方文档：https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_log_group_home_dir

    4.7.2 错误日志

    日志官方文档链接：https://dev.mysql.com/doc/refman/8.0/en/server-logs.html

    错误日志主要记录的是mysqld启动和关闭过程中输出的事件信息，运行过程中产生的错误信息

    查看错误日志文件路径

    mysql> show global variables like 'log_error';
+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| log_error     | /data/mysql/mysql.log |
+---------------+-----------------------+
1 row in set (0.01 sec)

    记录哪些级别的日志

    mysql>  SHOW GLOBAL VARIABLES LIKE 'log_error_verbosity';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| log_error_verbosity | 3     |
+---------------------+-------+
1 row in set (0.01 sec)

    官方文档：https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_log_error_verbosity

    4.7.3 通用日志

    记录对数据库的通用操作，包括:错误的SQL语句

    通用日志相关设置

    #是否开启通用日志
general_log=ON|OFF

#通用日志文件位置
general_log_file=HOSTNAME.log

#通用日志保存位置（文件或者表）
log_output=TABLE|FILE|NONE

    4.7.4 慢查询日志

    记录执行查询时长超出指定时长的操作

    慢查询相关变量

    slow_query_log=ON|OFF #开启或关闭慢查询，支持全局和会话，只有全局设置才会生成慢查询文件
long_query_time=N #慢查询的阀值，单位秒
slow_query_log_file=HOSTNAME-slow.log  #慢查询日志文件
log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan,
query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
#上述查询类型且查询时长超过long_query_time，则记录日志
log_queries_not_using_indexes=ON  #不使用索引或使用全索引扫描，不论是否达到慢查询阀值的语
句是否记录日志，默认OFF，即不记录
log_slow_rate_limit = 1 #多少次查询才记录，mariadb特有
log_slow_verbosity= Query_plan,explain #记录内容
log_slow_queries = OFF    #同slow_query_log，MariaDB 10.0/MySQL 5.6.1 版后已删除

    日志查看

    #可以打开日志文件直接查看
[root@localhost mysql]# cat /data/mysql/localhost-slow.log 
/app/mysql/bin/mysqld, Version: 5.7.34 (Source distribution). started with:
Tcp port: 3306  Unix socket: /data/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 2021-06-18T17:03:12.310465Z
# User@Host: root[root] @ localhost []  Id:     2

#日志分析工具查看
[root@localhost mysql]# mysqldumpslow -s c -t 10 /data/mysql/localhost-slow.log 

Reading mysql slow query log from /data/mysql/localhost-slow.log
Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts

    官方文档：https://dev.mysql.com/doc/refman/8.0/en/mysqldumpslow.html

    4.7.5 profile工具

    可以显示语句的执行详细过程

    启用profile

    mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.01 sec)

    查看语句执行过程

    #查看执行的最新语句列表
mysql> show profiles;
+----------+------------+------------------------+
| Query_ID | Duration   | Query                  |
+----------+------------+------------------------+
|        1 | 0.00040025 | select * from teachers |
+----------+------------+------------------------+
1 row in set, 1 warning (0.00 sec)

#查看语句执行的详细过程
mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000092 |
| checking permissions | 0.000010 |

    4.7.6 二进制日志

    描述数据库更改的“事件”，例如表创建操作或表数据的更改。以及潜在导致数据库改变的SQL语句，二进制日志还有连个重要作用：

    • 复制
    • 备份，通过“重放”日志文件中的事件来生成数据副本

    建议将二进制日志和数据文件分开存放

    二进制日志三种记录格式

    • 基于“语句”记录：statement，记录语句
    • 基于“行”记录：row，记录数据，日志量较大，更加安全，建议使用的格式
    • 混合模式：mixed, 让系统自行判定该基于哪种方式进行

    官方文档：https://dev.mysql.com/doc/refman/8.0/en/binary-log.html

    格式配置

    mysql>  show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

    二进制日志相关的服务器变量：

    sql_log_bin=ON|OFF：#是否记录二进制日志，默认ON，支持动态修改，系统变量，而非服务器选项

log_bin=/PATH/BIN_LOG_FILE：#指定文件位置；默认OFF，表示不启用二进制日志功能，上述两项都开
启才可以而且这个选项只能在配置文件中修改
#在MySQL中还需指定server_id系统变量，否则不能启动MySQL

binlog_format=STATEMENT|ROW|MIXED：#二进制日志记录的格式，默认STATEMENT

max_binlog_size=1073741824：#单个二进制日志文件的最大体积，到达最大值会自动滚动，默认为1G

#说明：文件达到上限时的大小未必为指定的精确值
binlog_cache_size=4m #此变量确定在每次事务中保存二进制日志更改记录的缓存的大小（每次连接）

max_binlog_cache_size=512m #限制用于缓存多事务查询的字节大小。

sync_binlog=1|0：#设定是否启动二进制日志即时同步磁盘功能，默认0，由操作系统负责同步日志到磁盘

expire_logs_days=N：#二进制日志可以自动删除的天数。 默认为0，即不自动删除

    二进制日志文件组成

    有两类文件
1.日志文件：mysql|mariadb-bin.文件名后缀，二进制格式,如： mariadb-bin.000001
2.索引文件：mysql|mariadb-bin.index，文本格式

    二进制日志语句

    #查看二进制日志文件列表
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       154 |
+------------------+-----------+
1 row in set (0.00 sec)

#查看使用中的二进制文件
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

#查看指定二进制文件中的指定内容
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
show binlog events in 'mysql-bin.000001' from 6516 limit 2,3

    离线查看二进制日志

    官方文档：https://dev.mysql.com/doc/refman/8.0/en/mysqlbinlog.html

    #二进制日志的客户端命令工具，支持离线查看二进制日志
mysqlbinlog [OPTIONS] log_file…
 --start-position=# 指定开始位置
 --stop-position=#
 --start-datetime=  #时间格式：YYYY-MM-DD hh:mm:ss
 --stop-datetime=
 --base64-output[=name]
        -v -vvv
        
 [root@localhost mysql]# mysqlbinlog -v /data/mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#210619  2:51:55 server id 0  end_log_pos 123 CRC32 0x83172d33 	Start: binlog v 4, server v 5.7.34-log created 210619  2:51:55 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '

    清除指定二进制日志

    PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }

PURGE BINARY LOGS TO 'mariadb-bin.000003'; #删除mariadb-bin.000003之前的日志
PURGE BINARY LOGS BEFORE '2017-01-23';
PURGE BINARY LOGS BEFORE '2017-03-22 09:25:30';

    删除所有二进制日志，index文件重新计数

    RESET MASTER [TO #]; #删除所有二进制日志文件，并重新生成日志文件，文件名从#开始记数，默认从
1开始，一般是master主机第一次启动时执行，MariaDB 10.1.6开始支持TO #

    生成新日志文件；

    FLUSH LOGS;

    5 备份和恢复

    5.1 概述

    备份主要就是为了防止数据丢失

    5.1.1 备份类型

    完全备份：整个数据集

    部分备份：只备份数据子集，如部分库或表

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

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

    冷、温、热备份

    • 冷备：读、写操作均不可进行，数据库停止服务
    • 温备：读操作可执行；但写操作不可执行
    • 热备：读、写操作均可执行
    • MyISAM：温备，不支持热备而InnoDB：都支持

    物理备份和逻辑备份

    • 物理备份：直接复制数据文件进行备份，与存储引擎有关，占用较多的空间，速度快
    • 逻辑备份：从数据库中“导出”数据另存而进行的备份，与存储引擎无关，占用空间少，速度慢，可 能丢失精度

    5.1.2 备份工具

    备份内容：数据、二进制日志和配置文件

    • cp, tar等复制归档工具：物理备份工具
    • LVM的快照：先加读锁，做快照后解锁，几乎热备；不过需要使用LVM
    • mysqldump：逻辑备份工具，适用所有存储引擎
    • xtrabackup：由Percona提供支持对InnoDB做热备(物理备份)的工具，支持完全备份、增量备份
    • MariaDB Backup： 从MariaDB 10.1.26开始集成，基于Percona XtraBackup 2.3.8实现

    范例：实现MySQL冷备份

    源主机：10.0.0.2
备份主机：10.0.0.3
#两个主机安装数据库，注意备份主机不启动服务
[root@centos8 ~]# yum -y install mysql-server

#10.0.0.2步骤
[root@centos8 ~]# systemctl stop mysqld
#复制配置文件
[root@centos8 ~]# rsync -av /etc/my.cnf.d/mysql-server.cnf 10.0.0.3:/etc/my.cnf.d/
#打包并复制数据库文件
[root@centos8 ~]# cd /var/lib/mysql
[root@centos8 mysql]# tar zcvf mysql.tar.gz *
#拷贝数据至备份主机
[root@centos8 mysql]# scp mysql.tar.gz 10.0.0.3:/data
#拷贝二进制日志至备份主机
[root@centos8 mysql]# rsync -av /data/ 10.0.0.3:/data

#10.0.0.3步骤
#将文件解压缩至数据库目录
[root@centos8 ~]# cd /var/lib/mysql
[root@centos8 mysql]# tar xvf mysql.tar.gz
#修改数据库权限
[root@centos8 mysql]# chown -R mysql.mysql /var/lib/mysql
[root@centos8 mysql]# chown -R mysql.mysql /data/
#启动数据库
[root@centos8 ~]# systemctl start mysqld

    5.2 mysqldump备份

    5.2.1 mysqldump 说明

    逻辑备份工具：

    通过SQL语句来复现原表，达到逻辑备份其命令格式如下：

    mysqldump [OPTIONS] database [tables]   #支持指定数据库和指定多表的备份，但数据库本身定
义不备份
mysqldump [OPTIONS] –B DB1 [DB2 DB3...] #支持指定数据库备份，包含数据库本身定义也会备份
mysqldump [OPTIONS] –A [OPTIONS]        #备份所有数据库，包含数据库本身定义也会备份

    参考文档：https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html

    mysqldump常见选项说明：

    -A, --all-databases #备份所有数据库，含create database
-B, --databases db_name…  #指定备份的数据库，包括create database语句
-E, --events：#备份相关的所有event scheduler
-R, --routines：#备份所有存储过程和自定义函数
--triggers：#备份表相关触发器，默认启用,用--skip-triggers，不备份触发器
--default-character-set=utf8 #指定字符集
--master-data[=#]： #此选项须启用二进制日志
#1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1，适合于主从复制多机使用
#2：记录为被注释的#CHANGE MASTER TO语句，适合于单机使用
#此选项会自动关闭--lock-tables功能，自动打开-x | --lock-all-tables功能（除非开启--single-transaction）
-F, --flush-logs #备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，配合-A 或 -B 选项时，会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新，可通过和--
single-transaction或-x，--master-data 一起使用实现，此时只刷新一次二进制日志
--compact #去掉注释，适合调试，生产不使用
-d, --no-data #只备份表结构,不备份数据
-t, --no-create-info #只备份数据,不备份表结构,即create table
-n,--no-create-db #不备份create database，可被-A或-B覆盖
--flush-privileges #备份mysql或相关时需要使用
-f, --force       #忽略SQL错误，继续执行
--hex-blob        #使用十六进制符号转储二进制列，当有包括BINARY， VARBINARY，
BLOB，BIT的数据类型的列时使用，避免乱码
-q, --quick     #不缓存查询，直接输出，加快备份速度

    InnoDB建议备份策略

    mysqldump –uroot -p –A –F –E –R --triggers --single-transaction --master-data=1
--flush-privileges --default-character-set=utf8 --hex-blob
>${BACKUP}/fullbak_${BACKUP_TIME}.sql

    MyISAM建议备份策略

    mysqldump –uroot -p –A –F –E –R –x --master-data=1 --flush-privileges --
triggers --default-character-set=utf8 --hex-blob
>${BACKUP}/fullbak_${BACKUP_TIME}.sql

    范例：：每天2：30做完全备份，早上10：00误删除students，10：10才发现故障，现需要将数据 库还原到10：10的状态，且恢复被删除的students表

    #2点30开始完全备份，注意该实验中由于可以使用口令登陆所以没有指定用户和密码，实际生产中应添加-u和-p选项
[root@centos8 mysql]# mysqldump -A -F -E -R --triggers --single-transaction --master-data=2 | gzip > /backup/all-`date +%F`.sql.gz
#查看是否备份成功
[root@centos8 mysql]# ll /backup/
total 228
-rw-r--r-- 1 root root 232381 Jun 24 01:24 all-2021-06-24.sql.gz

#备份后的数据更新
mysql> insert students (name,age,gender) values('rose',20,'f');
Query OK, 1 row affected (0.02 sec)

mysql> insert students (name,age,gender) values('jack',22,'M');
Query OK, 1 row affected (0.02 sec

#10：00误删除表格
mysql> drop table students;
Query OK, 0 rows affected (0.14 sec)

#其他表继续发生更新
mysql> insert teachers (name,age,gender)values('sun',25,'M');
Query OK, 1 row affected (0.03 sec)

mysql> insert teachers (name,age,gender)values('ma',30,'M');
Query OK, 1 row affected (0.02 sec)

mysql> 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 | sun           |  25 | M      |
|   6 | ma            |  30 | M      |
+-----+---------------+-----+--------+
6 rows in set (0.01 sec)

#10：10发现表删除，准备进行还原，停止数据库访问
[root@centos8 backup]# gunzip all-2021-06-24.sql.gz
[root@centos8 backup]# ll
total 1056   -rw-r--r-- 1 root root 1080060 Jun 24 01:24 all-2021-06-24.sql

#找到完全备份二进制位置
[root@centos8 backup]# grep '-- CHANGE MASTER TO' all-2021-06-24.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=156;

#备份从完全备份后的二进制日志
 [root@centos8 backup]# mysqlbinlog --start-position=156 /data/mysql-bin.000007 > /backup/inc.sql
[root@centos8 backup]# ll
total 1064
-rw-r--r-- 1 root root 1080060 Jun 24 01:24 all-2021-06-24.sql
-rw-r--r-- 1 root root    7724 Jun 24 02:07 inc.sql

#从备份中删除删表的语句
[root@centos8 ~]#vim /data/inc.sql
#DROP TABLE `student_info` /* generated by server */
#如果文件过大，可以使用sed实现
[root@centos8 ~]#sed -i.bak '/^DROP TABLE/d' /data/inc.sql
                          
#利用完全备份和修改过的二进制日志进行还原
mysql> source /backup/all-2021-06-24.sql
mysql> source /backup/inc.sql
mysql> set sql_log_bin=1;

    5.3 XtraBackup

    percona提供的mysql数据库备份工具，惟一开源的能够对innodb和xtradb数据库进行热备的工具

    xtrabackup版本说明

    xtrabackup版本升级到2.4后，相比之前的2.1有了比较大的变化：innobackupex 功能全部集成到 xtrabackup 里面，只有一个 binary程序，另外为了兼容考虑，innobackupex作为 xtrabackup 的软链 接，即xtrabackup现在支持非Innodb表备份，并且 Innobackupex 在下一版本中移除，建议通过 xtrabackup替换innobackupex

    官方文档：https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html

    8.0版本下载地址：https://downloads.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.25-17/binary/redhat/8/x86_64/percona-xtrabackup-80-8.0.25-17.1.el8.x86_64.rpm

    xtrabackup备份过程

    image

    备份生成的相关文件

    官方文档：https://www.percona.com/doc/percona-xtrabackup/8.0/xtrabackup-files.html

    • xtrabackup_info：文本文件，innobackupex工具执行时的相关信息，包括版本，备份选项，备 份时长，备份LSN(log sequence number日志序列号)，BINLOG的位置
    • xtrabackup_checkpoints：文本文件，备份类型（如完全或增量）、备份状态（如是否已经为 prepared状态）和LSN范围信息,每个InnoDB页(通常为16k大小)都会包含一个日志序列号LSN。 LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的
    • xtrabackup_binlog_info：文本文件，MySQL服务器当前正在使用的二进制日志文件及至备份这 一刻为止二进制日志事件的位置，可利用实现基于binlog的恢复
    • backup-my.cnf：文本文件，备份命令用到的配置选项信息
    • xtrabackup_logfile：备份生成的二进制日志文件

    5.3.1 xtrabackup用法

    xtrabackup工具备份三部曲

    • 备份：对数据库做完全或增量备份
    • 预准备： 还原前，先对备份的数据，整理至一个临时目录，
    • 还原：将整理好的数据，复制回数据库目录中

    xtrabackup 选项参考

    https://www.percona.com/doc/percona-xtrabackup/LATEST/xtrabackup_bin/xbk_option_reference.html

    格式：

    xtrabackup [--defaults-file=#] --backup|--prepare|--copy-back|--stats [OPTIONS]
--user：#该选项表示备份账号
--password：#该选项表示备份的密码
--host：#该选项表示备份数据库的地址
--databases：#该选项接受的参数为数据库名，如果要指定多个数据库，彼此间需要以空格隔开；
如："xtra_test dba_test"，同时，在指定某数据库时，也可以只指定其中的某张表。
如："mydatabase.mytable"。该选项对innodb引擎表无效，还是会备份所有innodb表
--defaults-file：#该选项指定从哪个文件读取MySQL配置，必须放在命令行第一个选项位置
--incremental：#该选项表示创建一个增量备份，需要指定--incremental-basedir
--incremental-basedir：#该选项指定为前一次全备份或增量备份的目录，与--incremental同时使用
--incremental-dir：#该选项表示还原时增量备份的目录
--include=name：#指定表名，格式：databasename.tablename

    范例：新版 xtrabackup完全备份及还原

    实验环境
Percona XtraBackup 8.0.25
Mysql版本8.0.21
[root@centos8 backup]# cat /etc/redhat-release 
CentOS Linux release 8.2.2004 (Core) 

1、安装软件包
[root@centos8 opt]# ls
percona-xtrabackup-80-8.0.25-17.1.el8.x86_64.rpm
[root@centos8 opt]# yum localinstall percona-xtrabackup-80-8.0.25-17.1.el8.x86_64.rpm 

2、在原主机做完全备份到/backup
#/backup目录不需事先创建，注意若mysql登陆若需要密码要加上-p选项
[root@centos8 opt]# xtrabackup -uroot --backup --target-dir=/backup/
.....
210628 19:48:23 completed OK!
#拷贝备份文件至还原主机
[root@centos8 opt]# scp -r /backup/* 10.0.0.3:/backup/
root@10.0.0.3's password: 
all-2021-06-24.sql                     100% 1055KB   6.0MB/s   00:00 

3、在目标主机上还原
#预准备：确保数据一致，提交完成的事务，回滚未完成的事务
[root@centos8 ~]# xtrabackup --prepare --target-dir=/backup/
.....
210628 11:55:42 completed OK!
#复制到数据库目录,数据库目录必须为空，MySQL服务不能启动
[root@centos8 ~]# xtrabackup --copy-back --target-dir=/backup/
.....
210628 12:27:27 [01] ...done.
210628 12:27:27 completed OK!
#修改属性
[root@centos8 ~]# chown -R mysql:mysql /var/lib/mysql
[root@centos8 ~]# systemctl start mysqld
#确认服务是否启动
[root@centos8 ~]# ss -ntl
State         Recv-Q        Send-Q               Local Address:Port                Peer Address:Port       
LISTEN        0             128                        0.0.0.0:22                       0.0.0.0:*          
LISTEN        0             70                               *:33060                          *:*          
LISTEN        0             128                              *:3306                           *:*          
LISTEN        0             128                           [::]:22                          [::]:*

    范例：利用xtrabackup完全，增量备份及还原

    实验环境与上例全量备份相同
1、备份
#完全备份
[root@centos8 ~]#xtrabackup -uroot --backup --target-dir=/backup/base
#第一次数据修改和增量备份
[root@centos8 ~]#xtrabackup -uroot --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
#第二次数据修改和增量备份
[root@centos8 ~]#xtrabackup -uroot --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1
#查看备份文件夹
[root@centos8 backup]# ls /backup/
base  inc1  inc2
#拷贝文件至备份主机
[root@centos8 backup]# scp -r /backup/* 10.0.0.3:/backup/

2、还原
#准备全量备份文件，注意使用--apply-log-only 阻止回滚未完成的事务
[root@centos8 ~]# xtrabackup --prepare --apply-log-only --target-dir=/backup/base
#合并第1次增量备份到完全备份
[root@centos8 ~]# xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1
#合并第2次增量备份到完全备份，注意最后一次还原不需要加选项--apply-log-only
[root@centos8 ~]# xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2
#复制到数据库目录
[root@centos8 ~]# xtrabackup --copy-back --target-dir=/backup/base
#修改文件属性
[root@centos8 ~]#chown -R mysql:mysql /var/lib/mysql
#启动服务
[root@centos8 ~]# systemctl start mysqld

    范例：xtrabackup单表导出和导入

    参考链接：https://www.percona.com/doc/percona-xtrabackup/8.0/xtrabackup_bin/partial_backups.html

    #备份单个表
[root@centos8 backup]# xtrabackup --backup --datadir=/var/lib/mysql --target-dir=/backups/ --tables="db1.export_test"
#备份表结构
[root@centos8 /]# mysql -e 'show create table db1.export_test' > /backups/student.sql
#拷贝文件至备份主机
[root@centos8 /]# scp -r /backups/* 10.0.0.3:/backup/

备份主机上执行
#导出单个表
[root@centos8 ~]# xtrabackup --prepare --export --target-dir=/backup/
#创建表
mysql> CREATE TABLE `export_test` (
  `a` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
PAGER set to stdout
PAGER set to stdout
Query OK, 0 rows affected (0.19 sec)
#删除表空间
mysql> alter table export_test discard tablespace;
Query OK, 0 rows affected (0.06 sec)
#拷贝导出的表到本地数据库目录
[root@centos8 ~]# cp /backup/db1/* /var/lib/mysql/db1/
#修改文件权限
[root@centos8 ~]# chown -R mysql.mysql /var/lib/mysql/db1/
#导入表空间
mysql> ALTER TABLE export_test IMPORT TABLESPACE;
Query OK, 0 rows affected (0.26 sec)

mysql> select * from export_test;
+------+
| a    |
+------+
|    1 |
|    2 |
+------+
3 rows in set (0.00 sec)

    6 MySQL 集群 Cluster

    6.1 MySQL主从复制

    6.1.1 主从复制架构和原理

    主从复制原理：

    主节点发生数据更新，并将更新的语句记录到二进制日志当中，主节点会启动一个dump线程，用于向其从节点发送binary log events。从节点会启动I/O Thread和SQL Thread两个线程，其中I/O Thread用于向Master请求二进制日志事件，并保存于中继日志中；SQL Thread则用于从中继日志中读取日志事件，在本地完成重放。

    常见的复制架构：

    • 主从复制
    • 一主多从架构
    • 一主一从带多从架构

    6.1.2 主从复制的实现

    参考文档：https://dev.mysql.com/doc/refman/8.0/en/replication.html

    配置步骤：

    1）主节点配置

    启用二进制日志

    #通过原理可知主从复制是以二进制日志为基础进行复制操作，所以主节点必须开启二进制日志
[mysqld]
log_bin

    为当前节点设置一个全局惟一的ID号

    [mysqld]
server-id=#
log-basename=master  #可选项，设置datadir中日志名称，确保不依赖主机名

    创建有复制权限的用户账号

    GRANT REPLICATION SLAVE  ON *.* TO 'repluser'@'HOST' IDENTIFIED BY 'replpass';

    查看从二进制日志的文件和位置开始进行复制

    SHOW MASTER LOG;

    从节点配置：

    启动中继日志

    [mysqld]
server_id=# #为当前节点设置一个全局惟的ID号
log-bin
read_only=ON #设置数据库只读，针对supper user无效
relay_log=relay-log #relay log的文件路径，默认值hostname-relay-bin
relay_log_index=relay-log.index  #默认值hostname-relay-bin.index

    使用有复制权限的用户账号连接至主服务器，并启动复制线程

    #8.0.23之前语法
CHANGE MASTER TO MASTER_HOST='masterhost',
MASTER_USER='repluser',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='mariadb-bin.xxxxxx',
MASTER_LOG_POS=#;

#8.0.23之后语法
CHANGE REPLICATION SOURCE TO

START SLAVE [IO_THREAD|SQL_THREAD];
SHOW SLAVE STATUS;

    范例：新建主从复制

    配置主节点
#修改配置文件
[root@centos8 backup]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server_id=2
log_bin=/data/mysql-bin

#创建复制用户
mysql> CREATE USER 'repl'@'10.0.0.%' IDENTIFIED BY 'passwd';
Query OK, 0 rows affected (0.04 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.%';
Query OK, 0 rows affected (0.01 sec)

#查看二进制日志位置
mysql> show master logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |       179 | No        |
| mysql-bin.000002 |       179 | No        |
| mysql-bin.000003 |       948 | No        |
| mysql-bin.000004 |       203 | No        |
| mysql-bin.000005 |       203 | No        |
| mysql-bin.000006 |       203 | No        |
| mysql-bin.000007 |      1626 | No        |
| mysql-bin.000008 |       687 | No        |
+------------------+-----------+-----------+

从节点配置
#修改配置文件，建议将二进制日志也开启
[root@centos8 ~]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=3
log_bin=/data/mysql-bin

#启动数据库服务
[root@centos8 ~]# systemctl start mysqld

#添加复制信息
[root@centos8 ~]# mysql
mysql> help change master to
mysql> CHANGE MASTER TO
    ->   MASTER_HOST='10.0.0.2',
    ->   MASTER_USER='repl',
    ->   MASTER_PASSWORD='passwd',
    ->   MASTER_PORT=3306,
    ->   MASTER_LOG_FILE='mysql-bin.000008',
    ->   MASTER_LOG_POS=687,
    ->   MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 2 warnings (0.11 sec)
mysql>  start slave;
Query OK, 0 rows affected (0.02 sec)

mysql> show slave statusG;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.2
                  Master_User: repl
        			Seconds_Behind_Master: 0	##复制的延迟时间

测试
#主服务器创建数据库
mysql> create database db2;
Query OK, 1 row affected (0.04 sec)

#查看从服务器是否同步
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db2                |

    6.1.3 主从复制的注意事项

    1. 限制从服务器为只读，否则会导致复制出错
    read_only=ON
#注意：此限制对拥有SUPER权限的用户均无效

    2.清除从节点信息

    RESET SLAVE #从服务器清除master.info ，relay-log.info, relay log ，开始新的relay log
RESET SLAVE  ALL #清除所有从服务器上设置的主服务器同步信息，如HOST，PORT, USER和
PASSWORD 等

    3.复制错误的解决方法

    #系统变量，指定跳过复制事件的个数，该变量适用于下 START REPLICA | SLAVE一条语句；下 START REPLICA | SLAVE一条语句还将该值更改回 0
SET GLOBAL sql_slave_skip_counter = N

#服务器选项，只读系统变量，指定跳过事件的ID
[mysqld]
slave_skip_errors=1007|ALL

    4.START SLAVE 语句，指定执到特定的点

    START SLAVE [thread_types]
START SLAVE [SQL_THREAD] UNTIL
   MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos
START SLAVE [SQL_THREAD] UNTIL
   RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos
thread_types:
   [thread_type [, thread_type] ... ]
thread_type: IO_THREAD | SQL_THREAD

    6.2 MySQL 中间件代理服务器

    简单来说中间件代理服务器是为了实现数据库的分表分库，将数据库拆分开来以应对海量的数据存储，更多详细的介绍可以参看Mycat的官方文档，文档中对于中间件代理给出了很详细的解释http://www.mycat.org.cn/document/mycat-definitive-guide.pdf

    6.2.1 Mycat实战

    下面将通过一个范例实现Mycat中间件代理

    系统环境

    #主机系统版本
[root@centos8 ~]# cat /etc/centos-release
CentOS Linux release 8.2.2004 (Core) 

#主机分配
mycat-server 10.0.0.5 内存建议2G以上
mysql-master 10.0.0.2 #负责数据写入
mysql-slave 10.0.0.3	#负责数据读取

#关闭防火墙和selinux
systemctl stop firewalld
setenforce 0

    1、创建主从数据库

    这一步在之前主从复制案例中已经实现，此处不再重新部署。

    2、在10.0.0.5安装mycat并启动

    #下载并安装JDK
[root@centos8 ~]# yum -y install java mysql
[root@centos8 ~]# java -version
openjdk version "1.8.0_292"
OpenJDK Runtime Environment (build 1.8.0_292-b10)
OpenJDK 64-Bit Server VM (build 25.292-b10, mixed mode)

#下载安装mycat
http://dl.mycat.org.cn/1.6.7.6/20210303094759/
[root@centos8 ~]# ls
anaconda-ks.cfg  jdk-16.0.1_linux-x64_bin.rpm  Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz
[root@centos8 ~]# mkdir /app
[root@centos8 ~]# tar xvf Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz -C /app/

#配置环境变量
[root@centos8 ~]# echo 'PATH=/app/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
[root@centos8 ~]# source /etc/profile.d/mycat.sh 

#查看端口情况
[root@centos8 ~]# ss -ntl
State         Recv-Q        Send-Q               Local Address:Port               Peer Address:Port        
LISTEN        0             128                        0.0.0.0:22                      0.0.0.0:*           
LISTEN        0             128                           [::]:22                         [::]:*   

#启动mycat
[root@centos8 ~]# mycat start
Starting Mycat-server...
[root@centos8 ~]# ss -tpln
State    Recv-Q   Send-Q     Local Address:Port      Peer Address:Port                                     
LISTEN   0        1              127.0.0.1:32000          0.0.0.0:*      users:(("java",pid=15136,fd=4))   
LISTEN   0        128              0.0.0.0:22             0.0.0.0:*      users:(("sshd",pid=991,fd=4))     
LISTEN   0        50                     *:1984                 *:*      users:(("java",pid=15136,fd=73))  
LISTEN   0        128                    *:8066                 *:*      users:(("java",pid=15136,fd=97))  
LISTEN   0        50                     *:43973                *:*      users:(("java",pid=15136,fd=72))  
LISTEN   0        128                    *:9066                 *:*      users:(("java",pid=15136,fd=93))  
LISTEN   0        128                 [::]:22                [::]:*      users:(("sshd",pid=991,fd=6))     
LISTEN   0        50                     *:43385                *:*      users:(("java",pid=15136,fd=74))

#查看日志是否启动成功
[root@centos8 ~]# tail /app/mycat/logs/wrapper.log
INFO   | jvm 5    | 2021/06/27 04:38:07 | Error: A fatal exception has occurred. Program will exit.
FATAL  | wrapper  | 2021/06/27 04:38:07 | There were 5 failed launches in a row, each lasting less than 300 seconds.  Giving up.
FATAL  | wrapper  | 2021/06/27 04:38:07 |   There may be a configuration problem: please check the logs.
STATUS | wrapper  | 2021/06/27 04:38:08 | <-- Wrapper Stopped
STATUS | wrapper  | 2021/06/27 04:55:51 | --> Wrapper Started as Daemon
STATUS | wrapper  | 2021/06/27 04:55:51 | Launching a JVM...
INFO   | jvm 1    | 2021/06/27 04:55:55 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO   | jvm 1    | 2021/06/27 04:55:55 |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
INFO   | jvm 1    | 2021/06/27 04:55:55 | 
INFO   | jvm 1    | 2021/06/27 04:55:59 | MyCAT Server startup successfully. see logs in logs/mycat.log

    3、修改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="mycat" />
        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="host1" url="10.0.0.2:3306" user="root" password="123456">
                <readHost host="host2" url="10.0.0.3:3306" user="root" password="123456"/>
                </writeHost>
        </dataHost>
</mycat:schema>

#重启mycat
[root@centos8 conf]# mycat restart

#遇见的报错
schema TESTDB didn't config tables,so you must set dataNode property
schema标签中属性与可嵌套的table 标签有依赖关系 。如果不设置table标签，就必须设置schema标签中dataNode属性。

    4、在后端主服务器创建用户并对mycat授权

    mysql>  create database mycat;
Query OK, 1 row affected (0.06 sec)

mysql> CREATE USER 'root'@'10.0.0.5' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.03 sec)

mysql> GRANT ALL ON *.* TO 'root'@'10.0.0.5';
Query OK, 0 rows affected (0.05 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

#若mycat使用账号对后端服务器无权限会报下面错误
mysql> show tables;
ERROR 1184 (HY000): Invalid DataSource:0

    5、在Mycat服务器上连接并测试

    [root@centos8 conf]# mysql -uroot -p123456 -h127.0.0.1 -P8066 -DTESTDB
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or g.

mysql>  create table t1(id int);
Query OK, 0 rows affected (0.24 sec)

    6、通过通用日志确认实现读写分离

    #开启通用日志功能
mysql> set global general_log=on;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log   | ON    |
+---------------+-------+
1 row in set (0.01 sec)

#查看日志文件保存位置
mysql> show variables like 'general_log_file';
+------------------+----------------------------+
| Variable_name    | Value                      |
+------------------+----------------------------+
| general_log_file | /var/lib/mysql/centos8.log |
+------------------+----------------------------+
1 row in set (0.01 sec)

#设置日志文件保存位置
mysql> set global general_log_file='tmp/general.log';

#查看通用日志
[root@centos8 ~]# tail -f /var/lib/mysql/centos8.log
/usr/libexec/mysqld, Version: 8.0.21 (Source distribution). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
2021-06-27T09:38:27.480755Z	   16 Query	show variables like 'general_log'
2021-06-27T09:38:32.833139Z	  297 Query	select user()

#在mycat服务器执行查询命令
mysql> select * from tb2;

#在主从服务器上分别确认通用日志只有10.0.0.3服务器出现查询日志，表示已实现读写分离
2021-06-27T09:42:01.264139Z	  301 Query	select * from tb1

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

    [root@centos8 ~]# systemctl stop mysqld

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

    注意：停止主节点，MyCAT不会自动调度读请求至从节点
  • 相关阅读:
    [LeetCode] 638. Shopping Offers
    [LeetCode] 1436. Destination City
    [LeetCode] 405. Convert a Number to Hexadecimal
    [LeetCode] 1909. Remove One Element to Make the Array Strictly Increasing
    [LeetCode] 1475. Final Prices With a Special Discount in a Shop
    [LeetCode] 650. 2 Keys Keyboard
    [LeetCode] 1382. Balance a Binary Search Tree
    [LeetCode] 917. Reverse Only Letters
    [LeetCode] 1189. Maximum Number of Balloons
    [LeetCode] 447. Number of Boomerangs
  • 原文地址：https://www.cnblogs.com/bestvae/p/14784602.html
Copyright © 2020-2023  润新知