[TOC]
Day01 一、数据库介绍 1、什么是数据库
数据库就是一个存放计算机数据的仓库，这个仓库是按照一定的数据结构（数据结构是指数据的组织形式或数据之间的联系）来对数据进行组织和存储的，可以通过数据库提供的多种方法来管理其中的数据。
数据库（Database）是一个有组织的数据集合，通常以电子方式存储和管理。它们允许用户高效地存储、检索、更新和删除数据。数据库可以用于多种目的，从简单的个人信息管理到复杂的企业级应用程序。数据库通常通过数据库管理系统（DBMS）来进行管理和操作。
1.1 关键概念
数据表（Table） ：数据库中的基本结构，数据以行和列的形式存储，每行代表一个记录，每列代表一个字段。记录（Record） ：表中的一行，表示一个完整的数据项。字段（Field） ：表中的一列，表示数据的一个属性。主键（Primary Key） ：唯一标识表中每条记录的字段或字段组合。外键（Foreign Key） ：在一个表中引用另一个表的主键，用于建立表之间的关系。查询（Query） ：用于从数据库中检索数据的请求，可以使用结构化查询语言（SQL）来编写。
2、数据库的种类
最常用的数据库模式主要有两种，即关系型数据库 和非关系型数据库 。
3、生产环境常用数据库
关系型数据库： 数据以表格的形式存储，通过SQL进行操作。常见的关系型数据库包括MySQL、PostgreSQL、Oracle和SQL Server。非关系型数据库： ：不使用表格存储数据，适用于处理大规模和非结构化数据。常见的NoSQL数据库包括MongoDB、Redis。
4、关系型数据库 1、关系型数据库介绍
关系型数据库模型是把复杂的数据结构归结为简单的二元关系（即二维表格形式）。在关系型数据库中，对数据的操作几乎全部建立在一个或多个关系表格上，通过这些关联的表格分类、合并、连接或选取等运算来实现数据的管理。
关系型数据可以很好地存储一些关系模型的数据，比如一个老师对应多个学生的数据（“一对多”），一本书对应多个作者（“一对多”），一本书对应一个出版日期（“一对一”）
2、关系型数据库小结 1 2 3 4 - 关系型数据库在存储数据时实际就是采用的一张二维表（和 Word 和 Excell 里表格几乎一样）。 - 市场占有量较大的是 MySQL 和 Oracle 数据库，而互联网场景最常用的是 MySQL 数据库。 - 通过 SQL 结构化查询语言来存取、管理关系型数据库的数据。 - 关系型数据库在保持数据安全和数据一致性方面很强，遵循 ACID 理论 ACID指的的事务的4大特性
5、非关系型数据库 1、非关系数据库诞生的背景
非关系型数据库也被称为 NoSQL 数据库，NoSQL 的本意是 “Not Only SQL”，指的是非关系型数据库，而不是“NO SQL”的意思，NoSQL 的产生并不是要彻底否定关系型数据库，而是作为传统数据库的一个有效补充。NoSQL 数据库在特定的场景下可以发挥难以想象的高效率和高性能。特别是对于规模日益扩大的海量数据，超大规模和高并发的微博、微信、SNS 类型的纯动态网站已经显得力不从心，暴露了很多难以克服的问题，例如：传统的关系型数据库IO瓶颈、性能瓶颈都难以有效突破，于是开始出现了大批针对特定场景，以高性能和使用便利为目的功能特异化的数据库产品。NoSQL（非关系型）类的数据库就是这样的情景中诞生并得到了非常迅速的发展。
NoSQL 是非关系型数据库的广义定义。它打破了长久以来关系型数据库与ACID理论大一统的局面。NoSQL数据存储不需要固定的表结构，通常也不存在连续操作。
非关系型数据库小结 1 2 3 4 - NoSQL 数据库不是否定关系型数据库，而是作为关系数据库的一个重要补充。 - NoSQL 数据库为了灵活及高性能、高并发而生，忽略影响高性能、高并发的功能。 - 在NoSQL 数据库领域，当今的最典型产品为 Redis（持久化缓存）、MongoDB、Memcached（纯内存）等。 - NoSQL 数据库没有标准的查询语言（SQL），通常使用数据接口或者查询API。
3、非关系型数据库种类 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 1.键值（Key-Value）存储数据库 - 键值数据库就类似传统语言中使用的哈希表。可以通过key来添加、查询或者删除数据，因为使用key主键访问，所以会获得很高的性能及扩展性。这个表中有一个特定的键和一个指针指向特定的数据。Key-Value模型对于IT系统来说的优势在于简单、易部署、高并发。 - 典型产品：Memcached、Redis、MemcachedB 2、列存储（Column-Oriented）数据库 - 列存储数据库将数据存储存在列族（Column Family）中，一个列族存储经常被一起查询的相关数据。举个例子，如果有一个 Person 类，通常会一起查询他们的姓名和年龄而不是薪资。这种情况下，姓名和年龄就会被放入一个列族中，而薪资则在另一个列族中。这部分数据库通常用来应对分布式存储的海量数据。键仍然存在，但是他们的特点是指向了多个列。这些列是由列家族来安排的。 - 典型产品：Cassandra，HBase 3、面向文档（Document-Oriented）的数据库 - 面向文档数据库会将数据以文档的形式存储,数据存储的最小单位是文档，同一个表中存储的文档属性可以是不同的，数据可以使用XML、JSON等多种形式存储。 - 典型产品：MongDB、CouchDB 4、图形（Graph）数据库 - 图形数据库允许我们将数据以图的方式存储。图形结构的数据库同其他行列以及刚性结构的 SQL 数据库不同，它是使用灵活的图形模型，并且能够扩展到多个服务器上。 - 典型产品：Neo4J、InfoGr id
6、常用关系型数据库管理系统 1、Oracle 数据库
Oracle 前身叫 SDL，由 Larry Ellison 和另两个编程人员在1977创办，他们开发了自己的拳头产品，在市场上大量销售，1979年，Oracle 公司引入了第一个商用 SQL关系数据库管理系统。Oracle公司是最早开发关系数据库的厂商之一，其产品支持最广泛的操作系统平台。目前 Oracle 关系数据库产品的市场占有率数一数二。
Oracle 公司是目前全球最大的数据库软件公司，也是近年业务增长极为迅速的软件提供与服务商。
主要应用范围：传统大企业，大公司，政府，金融，证券等等。
版本升级：Oracle8i，Oracle9i，Oracle10g，Oracle11g，Oracle12c。
2、MySQL 数据库
MySQL 数据库是一个中小型关系型数据库管理系统，软件开发者为瑞典 MySQL AB 公司。在2008年1月16号被 Sun 公司收购，后 Sun 公司又被 Oracle 公司收购。目前MySQL 被广泛地应用在 Internet 上的大中小型网站中。由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，许多大中小型网站为了降低网站总体拥有成本而选择了 MySQL 作为网站数据库，甚至国内知名的淘宝网也选择弃用 Oracle 而更换为更开放的 MySQL。
MySQL 数据库主要应用范围：互联网领域，大中小型网站，游戏公司，电商平台等等。
3、MariaDB 数据库
MariaDB 数据库管理系统是 MySQL 数据库的一个分支，主要由开源社区维护。开发这个 MariaDB 数据库分支的可能原因之一是：甲骨文公司收购了MySQL 后，有将 MySQL 闭源的潜在风险，因此 MySQL 开源社区采用分支的方式来避开这个风险。
开发 MariaDB 数据库的目的是完全兼容 MySQL 数据库，包括 API 和命令行，使之能轻松的成为 MySQL 的代替品。在存储引擎方面，使用 XtraDB （英语：XtraDB）来代替MySQL 的 InnoDB MariaDB 由 MySQL 的创始人 Michael Widenius（英语：Michael Widenius）主导开发，他早前曾以 10 亿美元的价格，将自己创建的公司MySQL AB卖给了 SUN，此后，随着 SUN 被甲骨文收购，MySQL 的所有权也落入Oracle 的手中，MariaDB 数据库的名称来自 MySQL 的创始人Michael Widenius 的女儿 Maria 的名字。
4、SQL Server 数据库
Microsoft SQL Server是微软公司开发的大型关系型数据库系统。1987年，微软和IBM合作开发完成 OS/2，IBM 在其销售的 OS/2 ExtendedEdition 系统中绑定了 OS/2 DatabaseManager，而微软产品线中尚缺少数据库产品。为此，微软将目光投向 Sybase，同 Sybase 签订了合作协议，使用 Sybase 的技术开发基于 OS/2 平台的关系型数据库。1989年，微软发布了 SQLServer1.0 版。
SQL Server 的功能比较全面，效率高，可以作为中型企业或单位的数据库平台。
SQL Server 可以与 Windows 操作系统紧密集成，不论是应用程序开发速度还是系统事务处理运行速度，都能得到较大的 提升。SQL Server 的缺点是只能在 Windows 系统下运行。
主要应用范围：部分企业电商（央视购物），使用windows服务器平台的企业。
7、常用非关系型数据库管理系统 1、Memcached（Key-Value）
Memcached 是一个开源的、高性能的、具有分布式内存对象的缓存系统。通过它可以减轻数据库负载，加速动态的 Web 应用，最初版本由 LiveJoumal 的 Brad Fitzpatrick在2003年开发完成。目前全球有非常多的用户都在使用它来构建自己的大负载网站或提高自己的高访问网站的响应速度。注意：Memcache 是这个项目的名称，而Memcached 是服务器端的主程序文件名。
缓存一般用来保存一些经常被存取的对象或数据（例如，浏览器会把经常访问的网页缓存起来一样），通过缓存来存取对象或数据要比在磁盘上存取快很多，前者是内存，后 者是磁盘。Memcached 是一种纯内存缓存系统，把经常存取的对象或数据缓存在 Memcached 的内存中，这些被缓存的数据被程序通过API的方式被存取，Memcached里面的数据就像一张巨大的 HASH 表，数据以 Key-Value 对的方式存在。Memcached 通过缓存经常被存取的对象或数据，从而减轻频繁读取数据库的压力，提高网站的响应速度.官方：http://Memcached.org/
由于Memcached 为纯内存缓存软件，一旦重启所有数据都会丢失，因此，新浪网基于 Memcached 开发了一个开源项目 MemcacheDB。通过为 Memcached 增加 Berkeley DB 的持久化存储机制和异步主辅复制机制，使 Memcached 具备了事务恢复能力、持久化数据存储能力和分布式复制能力，但是最近几年逐渐被其他的持久化产品替代例如Redis。
Memcached缺点：
1、存储的数据类型比较单一 只能存储字符串
2、无法持久化(没办法把数据存放到磁盘中)
2、Redis（Key-Value）
Redis 是一个Key-Value型存储系统。但Redis支持的存储value 类型相对更多，包括 string（字符串）、list（列表）、set（集合）和 zset（有序集合）等。这些数据类型都支持 push/pop、add/remove 及取交集、并集和差集及更丰富的操作，而且这些操作都是原子性的。在此基础上，Redis 支持各种不同方式的排序。与 Memcached 一样，为了保证效率，Redis 的数据都是缓存在内存中。区别是 Redis 会周期性的把更新的数据写入磁盘或者把修改操作写入追加的记录文件，并且在此基础上实现了 Master-Slave（主从）同步。
Redis 是一个高性能的 Key-Value 数据库。Redis 的出现，很大程度补偿了 Memcached 这类 Key-Value 存储的不足，在部分场合可以对关系数据库有很好的补充作用。它提供了 Python，Ruby，Erlang，PHP 客户端，使用很方便。官方：http://www.Redis.io/documentation
Redis 特点：
1 2 3 4 1. 支持内存缓存，这个功能相当于 Memcached。 2. 支持持久化存储，这个功能相当于 MemcacheDB。 3. 数据类型更丰富。比其他 Key-Value 库功能更强。 4. 支持主从集群，分布式。
应用：缓存从存取 Memcached 更改存取 Redis。
3、MongoDB （Document-Web）
MongoDB 是一个介于关系数据库和非关系数据库之间的产品，是非关系数据库当中功能最丰富，最像关系数据库的。他支持的数据结构非常松散，类似 Json 的 Bjson 格式，因此可以存储比较复杂的数据类型。MongoDB 最大的特点是他支持查询语言非常强大，其语法有点类似于面向对象的查询语言，几乎可以实现类似关系数据库单表查询的绝大部分功能，而且还支持对数据建立索引。它的特点是高性能、易部署、易使用，存储数据非常方便。
MongoDB 服务端可运行在 Linux、Windows 或 OS X 平台，支持32位和64位应用，默认端口为 27017。推荐运行在64位平台。
MongoDB 把数据存储在文件中（默认路径为：/data/db）。
二、前言
MySQL是一个关系型数据库管理系统 ，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统 之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
MySQL所使用的 SQL 语言是用于访问数据库 的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码 这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。
RDBMS即关系数据库管理系统(Relational Database Management System)
MySQL 类型 1 2 3 4 5 6 7 8 9 10 11 # - MySQL Community Server是社区版本，开源免费，但不提供官方技术支持。MySQL Community Server也是我们通常用的MySQL的版本。根据不同的操作系统平台细分为多个版本。 # - MySQL Enterprise Edition企业版本，需付费，可以试用30天。 # - MySQL Cluster集群版，开源免费。可将几个MySQL Server封装成一个Server。MySQL Cluster CGE 高级集群版，需付费。
MySQL 安装方式 1、yum 安装 1 优点：操作简单易用。不用单独下载，服务器可以联网且yum源没有问题即可(可以选择国内的163/阿里源)
2、编译安装 1 2 3 4 5 - 5.1.X 及之前的版本是通过下载tar包以后解压后进入软件包解压路径。然后./configure、make、make install - 5.4.X 到 5.7.X 通过下载tar包以后解压后进入软件包解压路径。然后 cmake、make、make install(cmake需要提前安装) 优点：可以定制功能特性。
3、二进制安装 1 官方下载二进制包，解压初始化即可直接使用不用安装
4、rpm 安装 1 2 - 需要提前下载 rpm 软件包上传到服务器系统本地 - 使用 rpm 或者 yum 命令直接安装
MySQL 版本号 1 2 3 4 5 以 MySQL 5.7.27 这个版本的版本号为例说明每个数字含义。 - 第一个数字(5)主版本号：文件格式改动时，将作为新的版本发布； - 第二个数字(7)发行版本号：新增特性或者改动不兼容时，发行版本号需要更改； - 第三个数字(27)发行序列号：主要是小的改动，如bug的修复、函数添加或更改、配置参数的更改等。
关系型数据库与非关系型数据库的区别: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 1.关系型数据库: 优点： 1、易于维护：都是使用表结构，格式一致； 2、使用方便：SQL语言通用，可用于复杂查询； 3、复杂操作：支持SQL，可用于一个表以及多个表之间非常复杂的查询。 缺点： 1、读写性能比较差，尤其是海量数据的高效率读写； 2、固定的表结构，灵活度稍欠； 3、高并发读写需求，传统关系型数据库来说，硬盘I/O是一个很大的瓶颈。 ============================================================================= 2.非关系型数据库严格上不是一种数据库，应该是一种数据结构化存储方法的集合，可以是文档或者键值对等。 优点： 1、格式灵活：存储数据的格式可以是key,value形式、文档形式、图片形式等等，使用灵活，应用场景广泛。 2、速度快：nosql可以使用硬盘或者随机存储器作为载体，而关系型数据库只能使用硬盘； 3、高扩展性； 4、成本低：nosql数据库部署简单，基本都是开源软件。 缺点： 1、不提供sql支持； 2、无事务处理； 3、数据结构相对复杂，复杂查询方面稍欠。
MySQL的官方网址： http://www.mysql.com/ ，MySQL的社区版本下载地址为： http://dev.mysql.com/downloads/mysql/ ，在写本文时，当前的MySQL最新版本是：8.0 。
什么是sql？
SQL代表结构化查询语言(Structured Query Language)。SQL是用于访问数据库的标准化语言。
SQL包含三个部分：
1 2 3 数据定义语言包含定义数据库及其对象的语句，例如表，视图，触发器，存储过程等。 数据操作语言包含允许您更新和查询数据的语句。 数据控制语言允许授予用户权限访问数据库中特定数据的权限。
mysql安装 关闭防火墙和selinux
1、编译安装mysql5.7 1、清理安装环境：
1 2 3 4 # yum erase mariadb mariadb-server mariadb-libs mariadb-devel -y # userdel -r mysql # rm -rf /etc/my* # rm -rf /var/lib/mysql
2、创建mysql用户
1 [root@mysql-server ~]# useradd -r mysql -M -s /bin/false
3、从官网下载tar包
1 [root@mysql-server ~]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-boost-5.7.27.tar.gz
4、安装编译工具
1 2 3 # yum -y install ncurses ncurses-devel openssl-devel bison gcc gcc-c++ make cmake: # yum -y install cmake
5、创建mysql目录
1 [root@mysql-server ~]# mkdir -p /usr/local/{data,mysql,log}
6、解压
1 2 3 4 [root@mysql-server ~]# tar xzvf mysql-boost-5.7.27.tar.gz -C /usr/local/ 注:如果安装的MySQL5.7及以上的版本，在编译安装之前需要安装boost,因为高版本mysql需要boots库的安装才可以正常运行。否则会报CMake Error at cmake/boost.cmake:81错误 安装包里面自带boost包 Boost库是为C++语言标准库提供扩展的一些C++程序库
7、编译安装
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 cd 解压的mysql目录 [root@mysql-server ~]# cd /usr/local/mysql-5.7.27/ [root@mysql-server mysql-5.7.27]# cmake . \ -DWITH_BOOST=boost/boost_1_59_0/ \ -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \ -DSYSCONFDIR=/etc \ -DMYSQL_DATADIR=/usr/local/mysql/data \ -DINSTALL_MANDIR=/usr/share/man \ -DMYSQL_TCP_PORT=3306 \ -DMYSQL_UNIX_ADDR=/tmp/mysql.sock \ -DDEFAULT_CHARSET=utf8 \ -DEXTRA_CHARSETS=all \ -DDEFAULT_COLLATION=utf8_general_ci \ -DWITH_READLINE=1 \ -DWITH_SSL=system \ -DWITH_EMBEDDED_SERVER=1 \ -DENABLED_LOCAL_INFILE=1 \ -DWITH_INNOBASE_STORAGE_ENGINE=1 提示：boost也可以使用如下指令自动下载，如果不下载bost压缩包，把下面的这一条添加到配置中第二行 -DDOWNLOAD_BOOST=1/ 参数详解: -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \ 安装目录 -DSYSCONFDIR=/etc \ 配置文件存放 （默认可以不安装配置文件） -DMYSQL_DATADIR=/usr/local/mysql/data \ 数据目录 错误日志文件也会在这个目录 -DINSTALL_MANDIR=/usr/share/man \ 帮助文档 -DMYSQL_TCP_PORT=3306 \ 默认端口 -DMYSQL_UNIX_ADDR=/tmp/mysql.sock \ sock文件位置，用来做网络通信的，客户端连接服务器的时候用 -DDEFAULT_CHARSET=utf8 \ 默认字符集。字符集的支持，可以调 -DEXTRA_CHARSETS=all \ 扩展的字符集支持所有的 -DDEFAULT_COLLATION=utf8_general_ci \ 支持的 -DWITH_READLINE=1 \ 上下翻历史命令 -DWITH_SSL=system \ 使用私钥和证书登陆（公钥） 可以加密。 适用与长连接。坏处：速度慢 -DWITH_EMBEDDED_SERVER=1 \ 嵌入式数据库 -DENABLED_LOCAL_INFILE=1 \ 从本地倒入数据，不是备份和恢复。 -DWITH_INNOBASE_STORAGE_ENGINE=1 默认的存储引擎，支持外键
1 2 3 [root@mysql-server mysql-5.7.27]# make && make install 如果安装出错，想重新安装： 不用重新解压，只需要删除安装目录中的缓存文件CMakeCache.txt
需要很长时间！ 大约半小时
8、初始化
1 2 3 [root@mysql-server mysql-5.7.27]# cd /usr/local/mysql [root@mysql-server mysql]# chown -R mysql.mysql . [root@mysql-server mysql]# ./bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data ---初始化完成之后，一定要记住提示最后的密码用于登陆或者修改密码
2720C+Xa:E+j
初始化,只需要初始化一次
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 [root@mysql-server ~]# vim /etc/my.cnf ---将文件中所有内容注释掉在添加如下内容 [client] port = 3306 socket = /tmp/mysql.sock default-character-set = utf8 [mysqld] port = 3306 user = mysql basedir = /usr/local/mysql #指定安装目录 datadir = /usr/local/mysql/data #指定数据存放目录 socket = /tmp/mysql.sock character_set_server = utf8 参数详解： [client] # 默认连接端口 port = 3306 # 用于本地连接的socket套接字 socket = /tmp/mysql.sock # 编码 default-character-set = utf8 [mysqld] # 服务端口号，默认3306 port = 3306 # mysql启动用户 user = mysql # mysql安装根目录 basedir = /usr/local/mysql # mysql数据文件所在位置 datadir = /usr/local/mysql/data # 为MySQL客户端程序和服务器之间的本地通讯指定一个套接字文件 socket = /tmp/mysql.sock # 数据库默认字符集,主流字符集支持一些特殊表情符号(特殊表情符占用4个字节) character_set_server = utf8
9、启动mysql
1 2 [root@mysql-server ~]# cd /usr/local/mysql [root@mysql-server mysql]# ./bin/mysqld_safe --user=mysql &
10、登录mysql
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 [root@mysql-server mysql]# /usr/local/mysql/bin/mysql -uroot -p'2720C+Xa:E+j' 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 2 Server version: 5.7.27 Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> exit
11、修改密码
1 2 3 [root@mysql-server mysql]# /usr/local/mysql/bin/mysqladmin -u root -p'2720C+Xa:E+j' password '1' 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.
12、添加环境变量
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 [root@mysql-server mysql]# vim /etc/profile ---添加如下 PATH=$PATH:$HOME/bin:/usr/local/mysql/bin [root@mysql-server mysql]# source /etc/profile 之后就可以在任何地方使用mysql命令登陆Mysql服务器： [root@mysql-server mysql]# mysql --version mysql Ver 14.14 Distrib 5.7.27, for Linux (x86_64) using EditLine wrapper [root@mysql-server mysql]# mysql -uroot -p'1' 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 5 Server version: 5.7.27 Source distribution Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql> exit
13、配置mysqld服务的管理工具：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 [root@mysql-server mysql]# cd /usr/local/mysql/support-files/ [root@mysql-server support-files]# cp mysql.server /etc/init.d/mysqld [root@mysql-server support-files]# chkconfig --add mysqld [root@mysql-server support-files]# chkconfig mysqld on 先将原来的进程杀掉 [root@mysql-server ~]# /etc/init.d/mysqld start Starting MySQL. SUCCESS! [root@mysql-server ~]# netstat -lntp Active Internet connections (only servers) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1087/sshd tcp6 0 0 :::22 :::* LISTEN 1087/sshd tcp6 0 0 :::3306 :::* LISTEN 31249/mysqld [root@mysql-server ~]# /etc/init.d/mysqld stop
数据库编译安装完成.
2、yum安装方式 关闭防火墙和selinux
mysql的官方网站：www.mysql.com
拉到底
1 2 3 下载 [root@mysql-server ~]# wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm 或者下载到本地上传到服务器
2.安装mysql的yum仓库
1 2 [root@mysql-server ~]# rpm -ivh mysql80-community-release-el7-3.noarch.rpm [root@mysql-server ~]# yum -y install yum-utils #安装yum工具包
3、配置yum源
1 [root@mysql-server ~]# vim /etc/yum.repos.d/mysql-community.repo #修改如下
1表示开启，0表示关闭
或者
1 2 # yum-config-manager --enable mysql57-community 将禁用的yum源库启用 # yum-config-manager --disable mysql80-community 将启用的yum源库禁用
4、安装数据库
1 2 3 4 5 [root@mysql-server ~]# yum install -y mysql-community-server 启动服务 [root@mysql-server ~]# systemctl start mysqld 设置开机启动 [root@mysql-server ~]# systemctl enable mysqld
5、查找密码
1 2 3 密码保存在日志文件中 [root@mysql-server ~]# grep password /var/log/mysqld.log 2019-08-18T14:03:51.991454Z 1 [Note] A temporary password is generated for root@localhost: woHtkMgau9,w
6、修改密码
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 两种方式： 第一种： [root@mysql-server ~]# mysql -uroot -p'woHtkMgau9,w' #登录 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 2 Server version: 5.7.27 .... mysql> alter user 'root' @'localhost' identified by '1' ; Query OK, 0 rows affected (0.01 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye [root@mysql-server ~]# mysql -uroot -p'1' 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 3 Server version: 5.7.27 MySQL Community Server (GPL) ... mysql> exit Bye 第二种： # mysqladmin -u root -p'旧密码' password '新密码' 注：修改密码必须大小写数字和特殊符号都有。
扩展
通过配置文件设置密码强度
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 [root@mysql-server ~] validate_password=off [root@mysql-server ~] 可以用第二种方式修改为简单的密码： [root@mysql-server ~] 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@mysql-server ~] 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 3 Server version: 5.7.27 MySQL Community Server (GPL) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help . Type '\c' to clear the current input statement. mysql> exit Bye
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 编译安装： # ls COPYING README bin include mysql-test support-files COPYING-test README-test docs lib share 1、bin目录 用于放置一些可执行文件，如mysql、mysqld、mysqlbinlog等。 2、include目录 用于放置一些头文件，如：mysql.h、mysql_ername.h等。 3、lib目录 用于放置一系列库文件。 4、share目录 用于存放字符集、语言等信息。 yum安装： /var/lib/mysql #存放数据文件 /usr/share/mysql #用于存放字符集、语言等信息。
三、数据库基本操作 一、数据库存储引擎（扩展） 数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引、锁定等功能，使用不同的存储引擎，还可以 获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。
1 2 3 4 5 6 MySQL存储引擎介绍 文件系统： 操作系统组织和存取数据的一种机制。文件系统是一种软件。 类型：ext2 3 4 ，xfs 不管使用什么文件系统，数据内容不会变化 不同的是，存储空间、大小、速度。 MySQL引擎： 可以理解为，MySQL的“文件系统”，只不过功能更加强大。 MySQL引擎功能： 除了可以提供基本的存取功能，还有更多功能事务功能、锁定、备份和恢复、优化以及特殊功能。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 1、InnoDB存储引擎:默认引擎，最常用的。 #速度较慢，但是非常安全，支持事务。 InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键;InnoDB是默认的MySQL引擎 InnoDB特点： 支持事务处理，支持外键，支持崩溃修复和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。 2、MyISAM存储引擎：（了解）#牺牲一些安全性，一般不出错，不支持事务，速度比innodb引擎要快一些。 MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事务。 MyISAM特点： 插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比较低，也可以使用。 12306查询 只生成一条数据这种适合 3、MEMORY内存型引擎(了解)#很大程度牺牲安全性，速度很快，全部占用的内存。 MEMORY存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问 MEMORY特点： 所有的数据都在内存中，数据的处理速度快，但是安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMOEY。它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表。 4、Archive(归档引擎)
如何选择引擎： 如果要提供提交、回滚、并要求实现并发控制，InnoDB是一个好的选择；如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率；如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎；MySQL中使用该引擎作为临时表，存放查询的中间结果；如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive。
使用哪一种引擎需要灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能。
存储引擎查看：
修改搜索引擎
1 ALTER TABLE 表名 ENGINE=引擎;
看你的mysql当前默认的存储引擎:
1 mysql> show variables like '%storage_engine%';
如何查看Mysql服务器上的版本
1 mysql> select version();
创建时候指定引擎
1 mysql> create table t1(id int,manager char(10)) engine =innodb;
了解:
1 2 3 4 1.什么是外键：外键的主要作用是保持数据的一致性、完整性。 2.什么是索引:索引相当于书中的目录，可以提高数据检索的效率，降低数据库的IO的压力。MySQL在300万条记录左右性能开始逐渐下降，虽然官方文档说500~800w记录，所以大数据量建立索引是非常有必要的 3.什么是行锁定与锁表：可以将一张表锁定和可以单独锁一行的记录。为了防止你在操作的同时也有别人在操作。 4.什么是事务：事务是由一步或几步数据库的操作。这系列操作要么全部执行，要么全部放弃执行。
事务控制语言(TCL) 1 事务控制语言 （Transation Control Language） 有时可能需要使用 DML 进行批量数据的删除，修改，增加。比如，在一个员工系统中，想删除一个人的信息。除了删除这个人的基本信息外，还应该删除与此人有关的其他信息，如邮箱，地址等等。那么从开始执行到结束，就会构成一个事务。对于事务，要保证事务的完整性。要么成功，要么撤回。
事务要符合四个条件(ACID)： 事务具有四个特性 ：原子性 （Atomicity）、一致性 （Consistency）、隔离性 （Isolation）和持续性 （Durability）。这四个特性也简称ACID 性。
1 2 3 4 5 6 7 （1）原子性：事务是应用中最小的执行单位，就如原子是自然界最小颗粒，事务要么成功，要么撤回.具有不可再分的特征一样。事务是应用中不可再分的最小执行体。（最小了，不可再分了） （2）一致性：事务执行的结果，必须使数据库从一个一致性状态，变到另一个一致性状态比如：当数据库中只包含事务成功提交的结果时，数据库处于一致性状态。一致性是通过原子性来保证的。 （3）隔离性：当涉及到多用户操作同一张表时，数据库会为每一个用户开启一个事务。各个事务的执行互不干扰，任意一个事务的内部操作对其他并发的事务都是隔离的。也就是说：并发执行的事务之间不能看到对方的中间状态，并发执行的事务之间不相互影响。（说白了，就是你做你的，我做我的！） （4）持续性：持续性也称为持久性指事务一旦提交对数据所做的任何改变，都要记录到永久存储器中，通常是保存进物理数据库。即使数据库崩溃了，我们也要保证事务的完整性。
二、sql语句 增删改查
1 2 3 4 5 6 SQL（Structured Query Language 即结构化查询语言） SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统，SQL语言由IBM开发。 DDL语句 数据库定义语言：数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER DML语句 数据库操纵语言（对记录的操作）： 插入数据INSERT、删除数据DELETE、更新数据UPDATE DCL语句 数据库控制语言（和权限有关）： 例如控制用户的访问权限GRANT、REVOKE DQL语句 数据库查询语言：查询数据SELECT
程序连接数据库的文件:
1 2 A. ODBC --------- PHP<.php> B. JDBC ----------- JAVA <.jsp>
==========================================================================
库—-相当于一个目录，存放数据的
1 2 3 4 库里面存放的表， 相当于是文件。 每一行叫做记录，除第一行。 每一列叫一个字段。列上面的第一个叫字段名称。 创建一个库：---->查看库--->进入这个库----->创建表----->查看表：查看表名，表的字段（表结构），表里面的内容（表记录），查看表的状态----->修改表：添加字段，删除字段，修改字段----->修改记录（更新记录），添加记录，删除记录。各种查询，删除表，删除库。
1 2 3 4 5 6 7 8 9 10 1.创建库 mysql> create database 库名; 2.查看数据库 mysql> show databases; 3.进入数据库 mysql> use 库名 4.查看当前所在的库 mysql> select database(); 5.查看当前库下所有的表格 mysql> show tables;
创建表
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 语法: create table 表名( 字段名1 类型[(宽度) 约束条件], 字段名2 类型[(宽度) 约束条件], 字段名3 类型[(宽度) 约束条件] )[存储引擎 字符集]; ==在同一张表中，字段名是不能相同 ==宽度和约束条件可选 ==字段名和类型是必须的 ========================================================= 1.创建表: 创建表 create table t1(id int ,name varchar (20 ),age int ); 字段 类型 字段 类型(长度)，字段 类型 mysql> create table t1(id int,name varchar(50),sex enum('m','f'),age int); 2.查看有哪些表 mysql> show tables; 3.查看表结构： mysql> desc t1; 4.查看表里面的所有记录: 语法: select 内容 from 表名； mysql> select * from t1; *:代表所有内容 5.查看表里面的指定字段： 语法:select 字段，字段 from 表名； mysql> select name ,sex from t1; 6.查看表的状态 mysql> show table status like '表名'\G ---每条SQL语句会以分号结尾，想看的清楚一些以\G结尾，一条记录一条记录显示。（把表90度向左反转，第一列显示字段，第二列显示记录）使用的\G就不用添加分号了 7.修改表名称 方式一、语法:rename table 旧表名 to 新表名; mysql> rename table t1 to t2; Query OK, 0 rows affected (0.00 sec) 方式二、语法:alter table 旧表名 rename 新表名; mysql> alter table t2 rename t3; 8.使用edit(\e)编辑 mysql> \e #可以写新的语句，调用的vim编辑器，在里面结尾的时候不加分号，保存退出之后在加“;” -> ; 9.删除表 mysql> drop table 表名; 10.删除库 mysql> drop database 库名;
Day02 四、数据类型 1、数据类型 在MySQL数据库管理系统中，可以通过存储引擎来决定表的类型。同时，MySQL数据库管理系统也
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 1.整型 作用：用于存储用户的年龄、游戏的Level、经验值等。 分类：tinyint smallint mediumint int bigint 常用的是int 显示宽度：类型后面小括号内的数字是显示的最小宽度，并不能限制插入数值的大小 比如：bigint(2) 2是显示宽度 取值范围： | MySQL数据类型 | 最小值 | 最大值 | | | tinyint(n) | -128 | 127 | | smallint(n) | -32,768 | 32,767 | | mediumint(n) | -8388608 | 8388607 | | int(n) | -2,147,483,648 | 2,147,483,647 | | bigint(n) | -9,223,372,036,854,775,808| 9,223,372,036,854,7 | 结论： - 当整数值超过 int 数据类型支持的范围时，就可以采用 bigint。 - 在 MySQL 中，int 数据类型是主要的整数数据类型。 - int(n)里的n是表示SELECT 查询结果集中的显示宽度，并不影响实际的取值范围，没有影响到显示的宽度 示例： mysql> create table ldq.test1(age int (10 )); //在ldq库下创建test1表格，设置数据类型 mysql> insert into test1(age) values(1000); Query OK, 1 row affected (0.00 sec) mysql> insert into test1(age) values(2147483647); Query OK, 1 row affected (0.00 sec) mysql> insert into test1(age) values(2147483648); //超出范围，报错 ERROR 1264 (22003): Out of range value for column 'age' at row 1 mysql> select * from test1; //查询 + | age | + | 100 | | 1000 | | 2147483647 | + 3 rows in set (0.00 sec) ===================================================== 2. 浮点数类型 FLOAT DOUBLE 作用：用于存储用户的身高、体重、薪水等 float (5 ,3 ) mysql> create table test4(float_test float (5 ,2 )); mysql> desc test4; + | Field | Type | Null | Key | Default | Extra | + | float_test | float(5,2) | YES | | NULL | | + 1 row in set (0.00 sec) mysql> insert into test4(float_test) values (123.345534354 ); Query OK, 1 row affected (0.01 sec) mysql> insert into test4(float_test) values(34.39567); Query OK, 1 row affected (0.00 sec) mysql> insert into test4(float_test) values(678.99993); Query OK, 1 row affected (0.00 sec) mysql> insert into test4(float_test) values(6784.9); ERROR 1264 (22003): Out of range value for column 'float_test' at row 1 mysql> select * from test4; + | float_test | + | 123.35 | | 34.40 | | 679.00 | + 4 rows in set (0.00 sec) ==================================================================================== 定点数类型 DEC 定点数在MySQL内部以字符串形式存储，比浮点数更精确，适合用来表示货币等精度高的数据。 3. 字符串类型 作用：用于存储用户的姓名、爱好、电话，邮箱地址，发布的文章等 字符类型 char varchar - char 表示定长字符串，长度是固定的；如果插入数据的长度小于char 的固定长度时，则用空格填充；因为长度固定，所以存取速度要比varchar 快很多，甚至能快50 %，但正因为其长度固定，所以会占据多余的空间。 - varchar 表示可变长字符串，长度是可变的；插入的数据是多长，就按照多长来存储；varchar 在存取方面与char 相反，它存取慢，因为长度不固定，但正因如此，不占据多余的空间。 - 结合性能角度（char 更快），节省磁盘空间角度（varchar 更小），具体情况还需具体来设计数据库才是妥当的做法。 char (10 ) 根据10 ，占10 个. 列的长度固定为创建表时声明的长度： 0 ~ 255 varchar (10 ) 根据实际字符串长度占空间，最多10 个 列中的值为可变长字符串，长度： 0 ~ 65535 案例: mysql> create table t8(c char (5 ),v varchar (12 )); Query OK, 0 rows affected (0.42 sec) mysql> insert into t8 values('abcde','abcdef'); Query OK, 1 row affected (0.38 sec) mysql> insert into t8 values('abc','abcdef'); #char可以少于规定长度。 Query OK, 1 row affected (0.05 sec) mysql> insert into t8 values('abc777','abcdef7'); #char不能大于规定的长度。 ERROR 1406 (22001): Data too long for column 'c' at row 1 mysql> ===================================================================== 总结： 1.经常变化的字段用varchar 2.知道固定长度的用char 3.超过255字符的只能用varchar或者text 4.能用varchar的地方不用text text：文本格式 4.枚举类型 enum mysql> create table t10(name enum('m','w')); 只能从tom,jim两个里面2选其1 （enumeration） 有限制的时候用枚举 案例： mysql> insert into t10(sex) values('m'); Query OK, 1 row affected (0.00 sec) mysql> insert into t10 values('w'); Query OK, 1 row affected (0.00 sec) mysql> insert into t10 values('n'); ERROR 1265 (01000): Data truncated for column 'sex' at row 1 ================================================================== 5.日期类型 ===时间和日期类型测试：year、date、time、datetime、timestamp 作用：用于存储用户的注册时间，文章的发布时间，文章的更新时间，员工的入职时间等 注意事项: ==插入年份时，尽量使用4位值 ==插入两位年份时，<=69，以20开头，比如65, 结果2065 >=70，以19开头，比如82，结果1982 案例: mysql> create table test_time(d date,t time,dt datetime); Query OK, 0 rows affected (0.03 sec)+ mysql> desc test_time; + | Field | Type | Null | Key | Default | Extra | + | d | date | YES | | NULL | | | t | time | YES | | NULL | | | dt | datetime | YES | | NULL | | + 3 rows in set (0.01 sec) mysql> insert into test_time values (now (),now (),now ()); Query OK, 1 row affected, 1 warning (0.02 sec) mysql> select * from test_time; + | d | t | dt | + | 2019-08-23 | 00:26:29 | 2019-08-23 00:26:29 | + 1 row in set (0.00 sec) 测试年： mysql> create table t3(born_year year ); Query OK, 0 rows affected (0.40 sec) mysql> desc t3; + | Field | Type | Null | Key | Default | Extra | + | born_year | year(4) | YES | | NULL | | + 1 row in set (0.00 sec) mysql> insert into t3 values (12 ),(80 ); Query OK, 2 rows affected (0.06 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from t3; + | born_year | + | 2012 | | 1980 | + 2 rows in set (0.00 sec) mysql> insert into t3 values (2019 ),(81 ); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from t3; + | born_year | + | 2012 | | 1980 | | 2019 | | 1981 | + 4 rows in set (0.00 sec) mysql>
五、表完整性约束 作用：用于保证数据的完整性和一致性
1 2 3 4 5 6 7 8 9 约束条件 说明 PRIMARY KEY (PK) 标识该字段为该表的主键，可以唯一的标识记录，不可以为空 UNIQUE + NOT NULL FOREIGN KEY (FK) 标识该字段为该表的外键，实现表与表之间的关联 NULL 标识是否允许为空，默认为NULL。 NOT NULL 标识该字段不能为空，可以修改。 UNIQUE KEY (UK) 标识该字段的值是唯一的，可以为空，一个表中可以有多个UNIQUE KEY AUTO_INCREMENT 标识该字段的值自动增长（整数类型，而且为主键） DEFAULT 为该字段设置默认值 UNSIGNED 无符号，正数
1 2 3 4 5 6 7 8 9 10 1.主键 每张表里只能有一个主键，不能为空，而且唯一，主键保证记录的唯一性，主键自动为NOT NULL。 一个 UNIQUE KEY 又是一个NOT NULL的时候，那么它被当做PRIMARY KEY主键。 定义两种方式： # 表存在，添加约束 mysql> alter table t7 add primary key (hostname); 创建表并指定约束 mysql> create table t9(hostname char(20),ip char(150),primary key(hostname));
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 mysql> insert into t9(hostname,ip) values('l66.com' , '10.10.10.11' ); Query OK, 1 row affected (0.00 sec) mysql> insert into t9(hostname,ip) values('l66.com' , '10.10.10.12' ); ERROR 1062 (23000): Duplicate entry 'l66.com' for key 'PRIMARY' mysql> insert into t9(hostname,ip) values('l66' , '10.10.10.11' ); Query OK, 1 row affected (0.01 sec) mysql> select * from t9; +-----------+-------------+ | hostname | ip | +-----------+-------------+ | l66 | 10.10.10.11 | | l66.com | 10.10.10.11 | +-----------+-------------+ 2 rows in set (0.00 sec) mysql> insert into t9(hostname,ip) values('ldqjy' , '10.10.10.12' ); Query OK, 1 row affected (0.00 sec) mysql> select * from t9; +-----------+-------------+ | hostname | ip | +-----------+-------------+ | l66 | 10.10.10.11 | | l66.com | 10.10.10.11 | | ldqjy | 10.10.10.12 | +-----------+-------------+ 3 rows in set (0.00 sec) 删除主键 mysql> alter table tab_name drop primary key;
1 2 3 4 5 6 7 8 9 2.auto_increment自增--------自动编号，且必须与主键组合使用默认情况下，起始值为1，每次的增量为1。当插入记录时，如果为AUTO_INCREMENT数据列明确指定了一个数值，则会出现两种情况： - 如果插入的值与已有的编号重复，则会出现出错信息，因为AUTO_INCREMENT数据列的值必须是唯一的； - 如果插入的值大于已编号的值，则会把该插入到数据列中，并使在下一个编号将从这个新值开始递增。也就是说，可以跳过一些编号。如果自增序列的最大值被删除了，则在插入新记录时，该值被重用。 （每张表只能有一个字段为自曾） （成了key才可以自动增长） mysql> CREATE TABLE department3 ( dept_id INT PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR(30), comment VARCHAR(50) );
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 mysql> select * from department3; Empty set (0.00 sec) 插入值 mysql> insert into department3(dept_name, comment) values('tom' ,'test' ), ('jack' , 'test2' ); Query OK, 2 rows affected (0.00 sec) mysql> select * from department3; +---------+-----------+---------+ | dept_id | dept_name | comment | +---------+-----------+---------+ | 1 | tom | test | | 2 | jack | test2 | +---------+-----------+---------+ 2 rows in set (0.00 sec) 删除自动增长 mysql> ALTER TABLE department3 CHANGE dept_id dept_id INT NOT NULL; Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> desc department3; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | dept_id | int(11) | NO | PRI | NULL | | | dept_name | varchar(30) | YES | | NULL | | | comment | varchar(50) | YES | | NULL | | +-----------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) 再次插入数据，报错 mysql> insert into department3(dept_name,comment) values('tom' ,'test1' ),('jack' ,'test2' ); ERROR 1364 (HY000): Field 'dept_id' doesn't have a default value
1 2 3 4 5 6 3.设置唯一约束 UNIQUE，字段添加唯一约束之后，该字段的值不能重复，也就是说在一列当中不能出现一样的值。 mysql> CREATE TABLE department2 ( dept_id INT, dept_name VARCHAR(30) UNIQUE, comment VARCHAR(50) );
插入数据的时候id和comment字段相同可以插入数据，如果有相同的名字不唯一。所以插入数据失败。
1 2 3 4 4.null与not null 1. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值 2. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值 sex enum('male','female') not null default 'male' #只能选择male和female，不允许为空，默认是male
1 2 3 4 5 6 7 8 9 10 mysql> create table t4(id int(5),name varchar(10),sex enum('male' ,'female' ) not null default 'male' ); Query OK, 0 rows affected (0.00 sec) mysql> insert into t4(id,name) values(1,'tom' ); mysql> select * from t4; +------+------+------+ | id | name | sex | +------+------+------+ | 1 | tom | male | +------+------+------+ 1 row in set (0.00 sec)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 允许为null mysql> create table t1(id int(5),name varchar(10),age int(5)); Query OK, 0 rows affected (0.00 sec) mysql> desc t1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(5) | YES | | NULL | | | name | varchar(10) | YES | | NULL | | | age | int(5) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> insert into t1(id,name) values(1,'tom' ); Query OK, 1 row affected (0.00 sec) mysql> select * from t1; +------+------+------+ | id | name | age | +------+------+------+ | 1 | tom | NULL | +------+------+------+ 1 row in set (0.00 sec)
指定字符集:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 修改字符集 ：在创建表的最后面指定一下： default charset=utf8 #可以指定中文 * 未指定之前，插入 mysql> insert into t1(id,name) values(1,'石宇飞' ); ERROR 1366 (HY000): Incorrect string value: '\xE7\x9F\xB3\xE5\xAE\x87...' for column 'name' at row 1 * 创建表格式指定字符集为utf-8 mysql> create table t6(id int(2),name char(5),age int(4)) default charset=utf8; Query OK, 0 rows affected (0.00 sec) mysql> desc t6; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(2) | YES | | NULL | | | name | char(5) | YES | | NULL | | | age | int(4) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> insert into t6(id,name) values(1,'石宇飞' ); Query OK, 1 row affected (0.00 sec)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 5.默认约束 添加/删除默认约束 1.创建一个表 mysql> create table user(id int not null, name varchar(20), number int, primary key(id)); Query OK, 0 rows affected (0.01 sec) mysql> describe user; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | | number | int(11) | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) 2、设置默认值 mysql> ALTER TABLE user ALTER number SET DEFAULT 0; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESCRIBE user; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | | number | int(11) | YES | | 0 | | +--------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) 3、插入值 mysql> ALTER TABLE user CHANGE id id INT NOT NULL AUTO_INCREMENT; Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> INSERT INTO user(name) VALUES('rock' ); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO user(name) VALUES('rock' ); Query OK, 1 row affected (0.00 sec) mysql> select * from user; +----+------+--------+ | id | name | number | +----+------+--------+ | 1 | rock | 0 | | 2 | rock | 0 | +----+------+--------+ 2 rows in set (0.00 sec) 删除默认值 mysql> ALTER TABLE user ALTER number drop DEFAULT;
作业
1 1.查一下oracle和mysql的区别写出几点即可。---面试
六、表操作 6.1 添加字段 格式：ALTER TABLE 表名 ADD [COLUMN] 字段名 字段类型 [FIRST|AFTER 字段名];
使用ALTER TABLE 语句可以实现：
向已有的表中添加列
修改现有表中的列
删除现有表中的列
重命名现有表中的列
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 mysql> create database emp01 character set utf8; Query OK, 1 row affected (0.00 sec) mysql> create table emp ( id int, name varchar(20) ); Query OK, 0 rows affected (0.02 sec) mysql> desc emp; + | Field | Type | Null | Key | Default | Extra | + | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | + 2 rows in set (0.01 sec)
6.1.1 表最后列添加一个新字段 1 2 3 4 5 6 7 8 9 10 11 12 13 14 mysql> alter table emp add math int(10); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; + | Field | Type | Null | Key | Default | Extra | + | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | math | int(10) | YES | | NULL | | + 3 rows in set (0.00 sec)
6.1.2 表最后列添加多个新字段 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 mysql> alter table emp add (chinese int(10),english int(10)); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; + | Field | Type | Null | Key | Default | Extra | + | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | math | int(10) | YES | | NULL | | | chinese | int(10) | YES | | NULL | | | english | int(10) | YES | | NULL | | + 5 rows in set (0.00 sec)
6.1.3 添加字段至表内指定字段之后 语法格式：alter table 表名 add 添加字段名 类型(长度) after 字段名;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 mysql> alter table emp add age char(3) after name; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; + | Field | Type | Null | Key | Default | Extra | + | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | age | char(3) | YES | | NULL | | | math | int(10) | YES | | NULL | | | chinese | int(10) | YES | | NULL | | | english | int(10) | YES | | NULL | | + 6 rows in set (0.00 sec)
6.1.4 添加字段到第一列 语法格式：alter table 表名 add 添加字段名 类型(长度) first;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 mysql> alter table emp add class char(10) first; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; + | Field | Type | Null | Key | Default | Extra | + | class | char(10) | YES | | NULL | | | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | age | char(3) | YES | | NULL | | | math | int(10) | YES | | NULL | | | chinese | int(10) | YES | | NULL | | | english | int(10) | YES | | NULL | | + 7 rows in set (0.00 sec)
6.2 修改字段和类型 6.2.1 修改字段名称（慎用） 语法格式：ALTER TABLE 表名 CHANGE 旧列名 新列名 新数据类型;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 mysql> desc emp; + | Field | Type | Null | Key | Default | Extra | + | class | char(10) | YES | | NULL | | | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | age | char(3) | YES | | NULL | | | math | int(10) | YES | | NULL | | | chinese | int(10) | YES | | NULL | | | english | int(10) | YES | | NULL | | + 7 rows in set (0.00 sec) mysql> alter table emp change name names varchar (20 ); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; + | Field | Type | Null | Key | Default | Extra | + | class | char(10) | YES | | NULL | | | id | int(11) | YES | | NULL | | | names | varchar(20) | YES | | NULL | | | age | char(3) | YES | | NULL | | | math | int(10) | YES | | NULL | | | chinese | int(10) | YES | | NULL | | | english | int(10) | YES | | NULL | | + 7 rows in set (0.00 sec)
6.2.2 修改字段类型长度 语法格式：ALTER TABLE 表名 MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER 字段名 2】;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 mysql> desc emp; + | Field | Type | Null | Key | Default | Extra | + | class | char(10) | YES | | NULL | | | id | int(11) | YES | | NULL | | | names | varchar(20) | YES | | NULL | | | age | char(3) | YES | | NULL | | | math | int(10) | YES | | NULL | | | chinese | int(10) | YES | | NULL | | | english | int(10) | YES | | NULL | | + 7 rows in set (0.00 sec) mysql> alter table emp modify names varchar (30 ) after math; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; + | Field | Type | Null | Key | Default | Extra | + | class | char(10) | YES | | NULL | | | id | int(11) | YES | | NULL | | | age | char(3) | YES | | NULL | | | math | int(10) | YES | | NULL | | | names | varchar(30) | YES | | NULL | | | chinese | int(10) | YES | | NULL | | | english | int(10) | YES | | NULL | | + 7 rows in set (0.00 sec)
6.2.3 删除字段（慎用） 语法格式：ALTER TABLE 表名 DROP 【COLUMN】字段名
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 mysql> desc emp; + | Field | Type | Null | Key | Default | Extra | + | class | char(10) | YES | | NULL | | | id | int(11) | YES | | NULL | | | age | char(3) | YES | | NULL | | | math | int(10) | YES | | NULL | | | names | varchar(30) | YES | | NULL | | | chinese | int(10) | YES | | NULL | | | english | int(10) | YES | | NULL | | + 7 rows in set (0.00 sec) mysql> alter table emp drop class ; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; + | Field | Type | Null | Key | Default | Extra | + | id | int(11) | YES | | NULL | | | age | char(3) | YES | | NULL | | | math | int(10) | YES | | NULL | | | names | varchar(30) | YES | | NULL | | | chinese | int(10) | YES | | NULL | | | english | int(10) | YES | | NULL | | + 6 rows in set (0.02 sec) mysql> show create table emp \G
6.3 插入数据(添加纪录) 创建初始表
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 mysql> create table t3( id int, name varchar(20), sex enum('m','f'), age int ); mysql> show create table t3 \G *************************** 1. row *************************** Table: t3 Create Table : CREATE TABLE `t3` ( `id` int (11 ) DEFAULT NULL , `name` varchar (20 ) DEFAULT NULL , `sex` enum('m' ,'f' ) DEFAULT NULL , `age` int (11 ) DEFAULT NULL ) ENGINE =InnoDB DEFAULT CHARSET =utf8 1 row in set (0.00 sec)
注意：字符串必须引号引起来；记录与表头相对应，表头与字段用逗号隔开。
6.3.1 添加一条数据 语法格式：insert into 表名(字段1,字段2,字段3,字段4) values(值1,"值2","值3",值4);
1 2 3 4 5 6 7 8 9 10 mysql> insert into t3(id,name,sex,age) values(1,"小贺","f",18); Query OK, 1 row affected (0.01 sec) mysql> select * from t3; + | id | name | sex | age | + | 1 | 小贺 | f | 1 | + 1 row in set (0.00 sec)
注意：添加的记录与表头要对应。
6.3.2 添加多条数据 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 mysql> insert into t3(id,name,sex,age) values(2,"小飞","m",18),(3,"铭哥","m",17); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from t3; + | id | name | sex | age | + | 1 | 小贺 | f | 60 | | 2 | 小飞 | m | 18 | | 3 | 铭哥 | m | 17 | + 3 rows in set (0.00 sec) mysql> insert into t3 set id =4 ,name ="小建" ,sex="f" ,age=15 ; Query OK, 1 row affected (0.01 sec) mysql> select * from t3; + | id | name | sex | age | + | 1 | 小贺 | f | 60 | | 2 | 小飞 | m | 18 | | 3 | 铭哥 | m | 17 | | 4 | 小建 | f | 15 | + 4 rows in set (0.01 sec)
6.3.3 更新字段值 语法格式：UPDATE 表名 SET 更新字段=更新值 where 更新字段=原有值
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 mysql> update t3 set name = "贝贝" where id = 4; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select name from t3; + | id | name | sex | age | + | 1 | 小贺 | f | 60 | | 2 | 小飞 | m | 18 | | 3 | 铭哥 | m | 17 | | 4 | 贝贝 | f | 15 | + 4 rows in set (0.00 sec)
6.3.4 删除单行记录 语法格式：delete from 表名 where 字段=值
1 2 3 4 5 6 7 8 9 10 11 12 13 mysql> delete from t3 where name = "小贺"; Query OK, 1 row affected (0.01 sec) mysql> select * from t3; + | id | name | sex | age | + | 2 | 小飞 | m | 18 | | 3 | 铭哥 | m | 17 | | 4 | 贝贝 | f | 15 | + 3 rows in set (0.00 sec)
6.3.5 删除整张表记录 语法格式：delete from 表名;
1 2 3 4 5 mysql> delete from t3; Query OK, 3 rows affected (0.01 sec) mysql> select * from t3; Empty set (0.00 sec)
6.4 单表查询 准备测试表
1 2 [root@mysql-server ~] mysql: [Warning] Using a password on the command line interface can be insecure.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 mysql> show databases; + | Database | + | information_schema | | emp01 | | mysql | | performance_schema | | study | | sys | + 6 rows in set (0.00 sec) mysql> use study; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables ; + | Tables_in_study | + | countries | | departments | | emp_details_view | | employees | | job_grades | | job_history | | jobs | | locations | | order | | regions | + 10 rows in set (0.00 sec)
6.4.1 整表查询（慎用） 1 mysql> select * from employees;
6.4.2 多字段查询 语法格式：select 字段1,字段2,字段3，.. from employees;
1 2 mysql> select employee_id,first_name,last_name from employees;
6.4.3 比较运算符使用
1 2 3 4 5 6 7 8 9 10 11 12 13 14 mysql> select employee_id,first_name,salary from employees where salary > 1000; mysql> select employee_id,first_name,salary from employees where salary = 10000; mysql> select employee_id,first_name,salary from employees where salary != 10000; mysql> select employee_id,first_name,salary from employees where salary < 10000; mysql> select employee_id,first_name,salary from employees where salary >= 10000;
6.4.4 查询字段别名设置AS 1 2 mysql> select employee_id,first_name,salary as "salry_num" from employees where salary >= 10000;
6.4.5 去除重复行 语法格式：SELECT DISTINCT 字段名 FROM 表名;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 mysql> select distinct department_id from employees; + | department_id | + | NULL | | 10 | | 20 | | 30 | | 40 | | 50 | | 60 | | 70 | | 80 | | 90 | | 100 | | 110 | + 12 rows in set (0.00 sec)
6.4.6 逻辑运算符 逻辑运算符主要用来判断表达式的真假，在MySQL中，逻辑运算符的返回结果为1、0或者NULL。
1 2 3 4 5 6 7 8 9 10 11 mysql> select employee_id,first_name,last_name,job_id from employees where job_id not in ("PR_REP","SH_CLERK"); mysql> select last_name,salary from study.employees where salary >= 10000 and salary <= 20000; mysql> select last_name,salary from study.employees where salary >= 10000 || salary <= 20000;
注意： OR可以和AND一起使用，但是在使用时要注意两者的优先级，由于AND的优先级高于OR，因此先对AND两边的操作数进行操作，再与OR中的操作数结合。
6.5 函数 简介：函数可以把我们经常使用的代码封装起来， 需要的时候直接调用即可。这样既 提高了代码效率 ，又 提高了可维护性 。在 SQL 中我们也可以使用函数对检索出来的数据进行函数操作。使用这些函数，可以极大地提高用户对数据库的管理效率 。
函数分成内置函数和 自定义函数 。在 SQL 语言中，同样也包括了内置函数和自定义函数。内置函数是系统内置的通用函数，而自定义函数是我们根据自己的需要编写的
6.5.1 MySQL内置函数分类 MySQL提供的内置函数从实现的功能角度可以分为数值函数、字符串函数、日期和时间函数、流程控制函数、聚合函数等。 这些丰富的内置函数再分为两类： 单行函数 、聚合函数 。
6.5.2 单行函数 只对一行进行变换；每行返回一个结果。
1. 数值函数
函数名
作用
ABS(x)
取x的绝对值
SIGN(X)
取X的符号。正数返回1，负数返回-1，0返回0
PI()
取圆周率的值
RAND()
取0~1的随机值
ROUND(x)
取一个对x的值进行四舍五入后，最接近于X的整数
TRUNCATE(x,y)
取数字x截断为y位小数的结果
1 mysql> SELECT ABS(-321),ABS(456),SIGN(-10),SIGN(10),PI(),RAND(),ROUND(1.5),TRUNCATE(3.1415,2) FROM DUAL;
2. 字符串函数
函数名
作用
CHAR_LENGTH(s)
字符串s的字符数。
LENGTH(s)
返回字符串s的字节数
CONCAT(s1,s2,……,sn)
连接s1,s2,……,sn为一个字符串
CONCAT_WS(z, s1,s2,……,sn)
同CONCAT(s1,s2,…)函数，但是每个字符串之间要加上z
REPLACE(str, a, b)
字符串b替换字符串str中所有出现的字符串a
UPPER(s)
字符串s的所有字母转成大写字母
LOWER(s)
字符串s的所有字母转成小写字母
LEFT(str,n)
返回字符串str最左边的n个字符
RIGHT(str,n)
返回字符串str最右边的n个字符
LPAD(str, len, pad)
用字符串pad对str最左边进行填充，直到str的长度为len个字符
RPAD(str ,len, pad)
用字符串pad对str最右边进行填充，直到str的长度为len个字符
LTRIM(s)
去掉字符串s左侧的空格
RTRIM(s)
去掉字符串s右侧的空格
1 mysql> select char_length("mingge"),length("铭哥"),concat("胡","佳","铭"),concat_ws("z","胡","佳","铭"),replace("azzzzzZbbb","z","c") from dual;
1 mysql> select upper("mingge"),lower("mingge"),left("hello,world",5),right("hello,world",5),lpad("world",10,"hello"),rpad("hello",10,"world") from dual;
1 mysql> select ltrim(" hello"),rtrim("world ") from dual;
3. 日期和时间函数
函数名
作用
CURDATE() ，CURRENT_DATE()
当前日期，只包含年、 月、日
CURTIME() ， CURRENT_TIME()
当前时间，只包含时、 分、秒
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP()
当前系统日期和时间
UTC_DATE()
UTC（世界标准时间） 日期
UTC_TIME()
UTC（世界标准时间） 时间
UNIX_TIMESTAMP()。单位为毫秒
UNIX时间戳的形式返回当前时间。
UNIX_TIMESTAMP(date)
将时间date以UNIX时间戳的形式返回。
FROM_UNIXTIME(timestamp)
将UNIX时间戳的时间转换为普通格式的时间
1 mysql> SELECT CURDATE(),CURTIME(),NOW(),UTC_DATE(),UTC_TIME() FROM DUAL;
1 mysql> select unix_timestamp(),from_unixtime(1720851662),unix_timestamp('2024-07-13 14:22:50'),from_unixtime(1720851770) from dual;
函数名
作用
YEAR(date) / MONTH(date) / DAY(date)
返回具体的日期值
HOUR(time) / MINUTE(time) / SECOND(time)
返回具体的时间值
MONTHNAME(date)
返回月份
DAYNAME(date)
返回星期几
WEEKDAY(date)
返回周几，注意，周1是0，周2是1，。。。周日是6
QUARTER(date)
返回日期对应的季度，范围为1～4
WEEK(date) ， WEEKOFYEAR(date)
返回一年中的第几周
DAYOFYEAR(date)
返回日期是一年中的第几天
DAYOFMONTH(date)
返回日期位于所在月份的第几天
DAYOFWEEK(date)
返回周几，注意：周日是1，周一是2，。。。周六是 7
1 mysql> select year(curdate()),month(curdate()),day(curdate()) from dual;
1 mysql> select hour(curtime()),minute(now()),second(sysdate()) from dual;
1 mysql> select monthname('2024-7-13'),dayname('2024-7-13'),weekday('2024-7-13'),quarter(curdate()),week(curdate()),dayofyear(now()),dayofmonth(now()),dayofweek(now()) from dual;
4. 流程控制函数 流程处理函数可以根据不同的条件，执行不同的处理流程，可以在SQL语句中实现不同的条件选择。
函数名
作用
IF(value,value1,value2)
如果value的值为TRUE，返回value1， 否则返回value2
IFNULL(value1, value2)
如果value1不为NULL，返回value1，否则返回value2
CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 …. [ELSE resultn] END
相当于shell的if…else if…else…
1 mysql> select employee_id,first_name,last_name,salary,if(salary >= 10000,'工资略高','工资一般') from employees;
1 mysql> select first_name,last_name,commission_pct,if(commission_pct is null,0,commission_pct) from employees;
1 mysql> select first_name,last_name,commission_pct,ifnull(commission_pct,0) from employees;
1 2 3 4 5 6 mysql> select employee_id,first_name,last_name,salary, case when salary>=17000 then '工资挺高' when salary>=10000 then '工资一般' when salary>=8000 then '工资很低' else '呵呵' END "工资收入" from employees;
6.5.3 聚合函数 对一组数据进行汇总的函数，输入的是一组数据的集合，输出的是单个值。聚合函数作用于一组数据，并对一组数据返回一个值。
函数名
作用
AVG()
平均值
SUM()
总和
MAX()
最大值
MIN()
最小值
COUNT()
个数
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 mysql> select avg(salary) from employees; + | avg(salary) | + | 6461.682243 | + 1 row in set (0.00 sec) mysql> select sum (salary) from employees; + | sum(salary) | + | 691400.00 | + 1 row in set (0.00 sec) mysql> select sum (salary * 12 ) from employees; + | sum(salary * 12) | + | 8296800.00 | + 1 row in set (0.00 sec) mysql> select max (salary),min (salary) from employees; + | max(salary) | min(salary) | + | 24000.00 | 2100.00 | + 1 row in set (0.00 sec) mysql> select count (employee_id) from employees; + | count(employee_id) | + | 107 | + 1 row in set (0.01 sec) mysql> select count (*) from employees; + | count(*) | + | 107 | + 1 row in set (0.00 sec) mysql> select count (employee_id) from employees where employee_id is null ; + | count(employee_id) | + | 0 | + 1 row in set (0.00 sec)
注意：conut函数在统计默认情况不包含空值
1 2 mysql> select sum(commission_pct) / count(ifnull(commission_pct,0)) from employees;
1. 分组查询GROUP BY 在MySQL中，GROUP BY子句用于将结果集中的行按一个或多个列进行分组。常与聚合函数（如COUNT()、SUM()、AVG()、MAX()、MIN()等）结合使用，以对每个组执行某种计算。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 mysql> CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(50), department VARCHAR(50), salary INT ); mysql> INSERT INTO employees (id, name, department, salary) VALUES (1, 'Alice', 'Sales', 5000), (2, 'Bob', 'HR', 4000), (3, 'Charlie', 'Sales', 5500), (4, 'David', 'IT', 6000), (5, 'Eve', 'IT', 6200), (6, 'Frank', 'Sales', 5300); mysql> SELECT department,COUNT(*) AS num_employees FROM employees GROUP BY department; + | department | num_employees | + | HR | 1 | | IT | 2 | | Sales | 3 | + 3 rows in set (0.00 sec) mysql> SELECT department, AVG (salary) AS avg_salary FROM employeesGROUP BY department;+ | department | avg_salary | + | HR | 4000.0000 | | IT | 6100.0000 | | Sales | 5266.6667 | + 3 rows in set (0.00 sec) mysql> SELECT department, MAX (salary) AS max_salary FROM employeesGROUP BY department;+ | department | max_salary | + | HR | 4000 | | IT | 6200 | | Sales | 5500 | + 3 rows in set (0.00 sec)
注意：
GROUP BY子句应出现在SELECT查询的WHERE子句之后和ORDER BY子句之前。SELECT查询中的列必须出现在GROUP BY子句中，或者是聚合函数的参数。
6.6 表复制 在MySQL中可以基于某一张存在的表创建新表，但主键、外键和索引不会复制
语法格式：create table 新表名 select * from 存在的表名
1 2 3 4 5 mysql> create table emp01.test select * from study.employees; Query OK, 107 rows affected (0.04 sec) Records: 107 Duplicates: 0 Warnings: 0 mysql> select * from emp01.test
6.6.1 单、多字段复制 1 2 3 4 5 mysql> create table emp01.test02 select mployee_id,first_name,last_name from study.employees; Query OK, 107 rows affected (0.02 sec) Records: 107 Duplicates: 0 Warnings: 0 mysql> select * from emp01.test02
6.7 范围查询BETWEEN AND BETWEEN运算符使用的格式通常为SELECT 字段名 FROM TABLE WHERE 字段名 BETWEEN 值1 AND 值2，此时，当值大于或等于值1，并且值小于或等于值2时，结果为1，否则结果为0。
1 mysql> select last_name,salary from employees where salary between 5000 AND 10000;
6.8 IS NULL空值和IS NOT NULL非空值查询 6.8.1 IS NULL 查询 空运算符（IS NULL或者ISNULL）判断一个值是否为NULL，如果为NULL则返回1，否则返回 0。
1 2 3 4 5 6 7 8 9 mysql> SELECT employee_id,commission_pct FROM employees WHERE commission_pct IS NULL; mysql> SELECT employee_id,commission_pct FROM employees WHERE commission_pct <=> NULL; mysql> SELECT employee_id,commission_pct FROM employees WHERE ISNULL(commission_pct); mysql> SELECT employee_id,commission_pct FROM employees WHERE commission_pct = NULL;
6.8.2 IS NOT NULL 非空运算符（IS NOT NULL）判断一个值是否不为NULL，如果不为NULL则返回1，否则返回0。
1 2 3 4 5 6 mysql> SELECT employee_id,commission_pct FROM employees WHERE commission_pct IS NOT NULL; mysql> SELECT employee_id,commission_pct FROM employees WHERE NOT commission_pct <=> NULL; mysql> SELECT employee_id,commission_pct FROM employees WHERE NOT ISNULL(commission_pct);
6.9 IN运算符使用 IN运算符用于判断给定的值是否是IN列表中的一个值，如果是则返回1，否则返回0。如果给定的值为NULL，或者IN列表中存在NULL，则结果为NULL。
1 mysql> SELECT employee_id,last_name,salary,manager_id FROM employees WHERE manager_id IN (100,101);
6.10 排序查询 使用 ORDER BY子句排序;ASC:：升序；DESC：降序。ORDER BY子句在SELECT语句的结尾。在排序时，默认升序
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 CREATE TABLE employees ( id INT PRIMARY KEY , name VARCHAR (50 ), department VARCHAR (50 ), salary INT , hire_date DATE ); INSERT INTO employees (id , name , department, salary, hire_date) VALUES (1 , 'Alice' , 'Sales' , 5000 , '2020-01-15' ), (2 , 'Bob' , 'HR' , 4000 , '2019-03-22' ), (3 , 'Charlie' , 'Sales' , 5500 , '2021-06-10' ), (4 , 'David' , 'IT' , 6000 , '2018-11-30' ), (5 , 'Eve' , 'IT' , 6200 , '2017-07-19' ), (6 , 'Frank' , 'Sales' , 5300 , '2020-05-25' );
6.10.1 单列排序 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 mysql> SELECT * FROM employees ORDER BY salary ASC; + | id | name | department | salary | hire_date | + | 2 | Bob | HR | 4000 | 2019-03-22 | | 1 | Alice | Sales | 5000 | 2020-01-15 | | 6 | Frank | Sales | 5300 | 2020-05-25 | | 3 | Charlie | Sales | 5500 | 2021-06-10 | | 4 | David | IT | 6000 | 2018-11-30 | | 5 | Eve | IT | 6200 | 2017-07-19 | + 6 rows in set (0.00 sec) mysql> SELECT * FROM employees ORDER BY hire_date DESC ; + | id | name | department | salary | hire_date | + | 3 | Charlie | Sales | 5500 | 2021-06-10 | | 6 | Frank | Sales | 5300 | 2020-05-25 | | 1 | Alice | Sales | 5000 | 2020-01-15 | | 2 | Bob | HR | 4000 | 2019-03-22 | | 4 | David | IT | 6000 | 2018-11-30 | | 5 | Eve | IT | 6200 | 2017-07-19 | + 6 rows in set (0.00 sec)
6.10.2 多列排序 1 2 3 4 5 6 7 8 9 10 11 12 13 14 mysql> SELECT * FROM employees ORDER BY salary ASC, hire_date DESC; + | id | name | department | salary | hire_date | + | 2 | Bob | HR | 4000 | 2019-03-22 | | 1 | Alice | Sales | 5000 | 2020-01-15 | | 6 | Frank | Sales | 5300 | 2020-05-25 | | 3 | Charlie | Sales | 5500 | 2021-06-10 | | 4 | David | IT | 6000 | 2018-11-30 | | 5 | Eve | IT | 6200 | 2017-07-19 | + 6 rows in set (0.00 sec)
在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第 一列数据中所有值都是唯一的，将不再对第二列进行排序。
6.10.3 按别名排序 1 2 3 4 5 6 7 8 9 10 11 12 13 14 mysql> SELECT *,(salary * 0.1) AS salary_bonus FROM employees ORDER BY salary_bonus ASC; + | id | name | department | salary | hire_date | salary_bonus | + | 2 | Bob | HR | 4000 | 2019-03-22 | 400.0 | | 1 | Alice | Sales | 5000 | 2020-01-15 | 500.0 | | 6 | Frank | Sales | 5300 | 2020-05-25 | 530.0 | | 3 | Charlie | Sales | 5500 | 2021-06-10 | 550.0 | | 4 | David | IT | 6000 | 2018-11-30 | 600.0 | | 5 | Eve | IT | 6200 | 2017-07-19 | 620.0 | + 6 rows in set (0.00 sec)
6.11 分页查询 分页显示，就是将数据库中的结果集，一段一段显示出来需要的条件。
语法格式：LIMIT [偏移量,] 行数
第一个“偏移量”参数指示MySQL从哪一行开始显示，是一个可选参数，如果不指定“位置偏移 量”，将会从表中的第一条记录开始（第一条记录的位置偏移量是0，第二条记录的位置偏移量是 1，以此类推）；第二个参数“行数”指示返回的记录条数。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 mysql> SELECT * FROM employees LIMIT 0,3; + | id | name | department | salary | hire_date | + | 1 | Alice | Sales | 5000 | 2020-01-15 | | 2 | Bob | HR | 4000 | 2019-03-22 | | 3 | Charlie | Sales | 5500 | 2021-06-10 | + 3 rows in set (0.00 sec) mysql> SELECT * FROM employees LIMIT 3 ; + | id | name | department | salary | hire_date | + | 1 | Alice | Sales | 5000 | 2020-01-15 | | 2 | Bob | HR | 4000 | 2019-03-22 | | 3 | Charlie | Sales | 5500 | 2021-06-10 | + 3 rows in set (0.00 sec) mysql> SELECT id , name ,department,salary,hire_date FROM employees LIMIT 3 OFFSET 3 ; + | id | name | department | salary | hire_date | + | 4 | David | IT | 6000 | 2018-11-30 | | 5 | Eve | IT | 6200 | 2017-07-19 | | 6 | Frank | Sales | 5300 | 2020-05-25 | +
6.12 行关联 环境准备：
员工表
字段解释： employee_id：员工ID first_name：员工名称 last_name：员工姓氏 email：邮箱 phone_number：电话号码 hire_date：入职时间 job_id：职位ID salary：工资 commission_pct：奖金百分比 manager_id：管理者ID department_id：部门ID
部门表
字段解释： department_id：部门ID department_name：部门名称 manager_id：管理者ID location_id：位置ID
位置表
字段解释： location_id：位置ID street_address：街道地址 postal_code：邮政编码 city：城市名称 state_province：州或省 country_id：国家ID
测试：查看名为king的员工部门信息
1 2 3 4 5 6 7 8 9 10 11 12 13 SELECT last_name,department_idFROM employeesWHERE last_name = "king" ;SELECT department_id,department_nameFROM departmentsWHERE department_id = 80 ;SELECT employees.last_name,employees.department_id,departments.department_nameFROM employees,departmentsWHERE employees.last_name = "king" AND employees.department_id = departments.department_id;
注意在多表联查时需要注意表与表之前必须具备一个连接条件，且在查询指定字段时多个表出现相同字段需要指定是从那个表中查询。
优化：由于在进行多表联查时，需要指定查询的字段出现在哪个表中，所以导致SQL可定性较差，故可以给表设定别名；但是一旦给表设定了别名，在查询字段时必须指定。否则无法正常执行。
1 2 3 SELECT t1.last_name,t1.department_id,t2.department_nameFROM employees t1,departments t2WHERE t1.last_name = "king" AND t1.department_id = t2.department_id;
测试：查询员工的员工id、姓氏、部门名称、城市employee_id,last_name,department_name,city
1 2 3 SELECT t1.employee_id,t1.last_name,t2.department_name,t3.cityFROM employees t1,departments t2,locations t3WHERE t1.department_id = t2.department_id AND t2.location_id = t3.location_id;
6.12.1 多表查询分类 1. 等值连接 等值连接是指使用等值条件将两个或多个表的相关联字段连接在一起，从而获取符合条件的数据。
示例：查询员工的员工id、姓氏、部门名称、城市
字段名：employees.employee_id,employees.last_name,departments.department_name,locations.city
1 2 3 SELECT t1.employee_id,t1.last_name,t2.department_name,t3.cityFROM employees t1,departments t2,locations t3WHERE t1.department_id = t2.department_id AND t2.location_id = t3.location_id;
2. 非等值连接 非等值连接是指使用非等值条件（如 <, >, <=, >=, != 等）将两个或多个表的相关联字段连接在一起，从而获取符合条件的数据。
引入新表
1 2 3 4 5 6 7 8 mysql> desc job_grades; # 工资表等级表 + | Field | Type | Null | Key | Default | Extra | + | grade_level | varchar(3) | YES | | NULL | | | lowest_sal | int(11) | YES | | NULL | | | highest_sal | int(11) | YES | | NULL | | +
示例:查询每位员工的工资等级
字段名：employees.last_name,employees.salary,job_grades.grade_level
1 2 3 4 5 6 7 8 SELECT t1.last_name,t1.salary,t2.grade_level FROM employees t1,job_grades t2 WHERE t1.salary BETWEEN t2.lowest_sal AND t2.highest_sal; # 或者 SELECT t1.last_name,t1.salary,t2.grade_level FROM employees t1,job_grades t2 WHERE t1.salary >= t2.lowest_sal AND t1.salary <= t2.highest_sal;
3. 自连接 自连接是指在同一张表内进行连接查询，通常用于在表的不同行之间建立关系。这种连接可以使用表的别名 来区分同一个表的不同实例。自连接在处理层次结构或关联关系的数据时非常有用。
表内容
如上图所示：表中几乎每一位员工都有一个管理者id
示例：查询所有员工的管理者姓名
字段名：employees_id,last_name,manager_id
1 2 3 SELECT t1.employee_id,t1.last_name,t2.employee_id,t2.last_nameFROM employees t1,employees t2WHERE t1.manager_id = t2.employee_id;
4. 非自连接 非自连接主要指的是通过连接两个或多个不同的表来获取所需的数据。这些表之间的连接不是通过表本身的字段，而是通过多个表的字段。
5. 内连接 内连接用于从多个表中获取同时存在于这些表中的数据。也就是说，只有当连接条件满足时，才会返回记录。内连接是关系数据库中最常用的一种连接方式。
示例：查询员工的员工id、姓氏、部门名称、城市
字段名：employees.employee_id,employees.last_name,departments.department_name,locations.city
1 2 3 4 5 6 7 8 9 10 SELECT t1.employee_id,t1.last_name,t2.department_name,t3.cityFROM employees t1,departments t2,locations t3WHERE t1.department_id = t2.department_id AND t2.location_id = t3.location_id;SELECT t1.last_name,t2.department_name,t3.cityFROM employees t1 JOIN departments t2ON t1.department_id = t2.department_id JOIN locations t3ON t2.location_id = t3.location_id;
6. 外连接 外连接（Outer Join）用于从多个表中获取匹配的和不匹配的记录。外连接分为左外连接（Left Join）和右外连接（Right Join）。在使用外连接时，即使在一个表中没有匹配的记录，另一个表中的记录仍会包含在结果集中。
左外连接
两个表（员工表和部门表）在来连接过程中除了返回满足连接条件的行以外还返回左表中不满足条件的行
如上图所示：在employees表中，名为Grant这位员工没有部门，在进行内连接查询时，只能查询处106条记录。但是在某些场景下。需要查询处所有员工的部门情况。所以此时就需要外连接。
示例：查询所有员工的姓氏、部门名称（包括没有部门的员工）
字段名：employees.last_name,deparments.deparment_name
1 2 3 SELECT t1.employee_id,t1.first_name,1. last_name,t2.department_nameFROM employees t1 LEFT OUTER JOIN departments t2ON t1.department_id = t2.department_id;
此时所有的员工的部门信息都查询出来了。共107条记录，包括部门为空值的员工
右外连接
两个表在来连接过程中除了返回满足连接条件的行右表中不满足条件
示例：查询所有没有员工的部门信息
字段名：employees.last_name,deparments.deparment_name
1 2 3 SELECT t1.employee_id,t1.first_name,t1.last_name,t2.department_nameFROM employees t1 RIGHT OUTER JOIN departments t2ON t1.department_id = t2.department_id;
满外连接
两个表在来连接过程中除了返回满足连接条件的行以外还返回两张表中不满足条件的行
合并查询结果利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同 ，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。
UNION 操作符
1+106+106+16
UNION 操作符返回两个查询的结果集的并集，去除重复记录。
示例： 经以上实验查询出来的结论发现。employees员工表中有106名员工具有部门。departments部门表中有16个部门没有员工。那么在UNION ALL进行满外连接查询时，最终的查询结果应该是1(没有部门的员工数量)+106(有部门的员工数量)+16(没有员工的部门数量)=123条记录。相关查询SQL语句如下：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 SELECT t1.employee_id,t1.last_name,t1.first_name,t1.salary,t2.department_nameFROM employees t1,departments t2 WHERE t1.department_id = t2.department_id;SELECT t1.employee_id,t1.last_name,t1.first_name,t1.salary,t2.department_nameFROM employees t1 LEFT JOIN departments t2ON t1.department_id = t2.department_idWHERE t2.department_id IS NULL ; SELECT last_name,salaryFROM employeesWHERE department_id IS NULL ;
UNION ALL操作符
UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。
注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。
扩展内容
代码实现：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 SELECT t1.last_name,t1.salary,t2.department_nameFROM employees t1 JOIN departments t2ON t1.department_id = t2.department_id;SELECT t1.last_name,t1.salary,t2.department_nameFROM employees t1 LEFT JOIN departments t2ON t1.department_id = t2.department_id;SELECT t1.last_name,t2.department_nameFROM employees t1 RIGHT JOIN departments t2ON t1.department_id = t2.department_id;SELECT t1.last_name,t1.first_name,t2.department_nameFROM employees t1 LEFT JOIN departments t2ON t1.department_id = t2.department_idWHERE t2.department_id IS null ;SELECT t1.last_name,t2.department_name,t2.department_id FROM employees t1 RIGHT JOIN departments t2ON t1.department_id = t2.department_idWHERE t1.department_id IS NULL ; SELECT t1.employee_id,t1.last_name,t2.department_nameFROM employees t1 LEFT JOIN departments t2ON t1.department_id = t2.department_idWHERE t2.department_id IS NULL UNION ALL SELECT t1.employee_id,t1.last_name,t2.department_nameFROM employees t1 RIGHT JOIN departments t2ON t1.department_id = t2.department_id; SELECT t1.employee_id,t1.last_name,t2.department_nameFROM employees t1 LEFT JOIN departments t2ON t1.department_id = t2.department_idWHERE t2.`department_id` IS NULL UNION ALL SELECT t1.employee_id,t1.last_name,t2.department_nameFROM employees t1 RIGHT JOIN departments t2ON t1.department_id = t2.department_idWHERE t1.department_id IS NULL ;
6.13 破解密码 先将这个修改成简单密码注释掉
1 2 3 4 5 6 7 8 9 10 11 root账户没了或者root密码丢失： 关闭Mysql使用下面方式进入Mysql直接修改表权限 5.6/5.7版本： # mysqld --skip-grant-tables --user=mysql & # mysql -uroot mysql> UPDATE mysql.user SET authentication_string=password('1') WHERE user='root' AND host='localhsot'; mysql> FLUSH PRIVILEGES; # 编辑配置文件将skip-grant-tables参数注释 # 重启mysql
Day03 七、MySQL 索引 索引作为一种数据结构，其用途是用于提升检索数据的效率。索引（Index）是帮助MySQL高效获取数据的数据结构。
索引的本质：索引是数据结构。你可以简单理解为“排好序的快速查找数据结构”，满足特定查找算法。
索引是在存储引擎中实现的 ，因此每种存储引擎的索引不一定完全相同，并且每种存储引擎不一定支持所有索引类型。同时，存储引擎可以定义每个表的 最大索引数 和最大索引长度。所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。有些存储引擎支持更多的索引数和更大的索引长度。
前言 索引是存储引擎用于快速找到数据记录的一种数据结构，就好比一本教课书的目录部分，通过目录中找到对应文章的页码，便可快速定位到需要的文章。MySQL中也是一样的道理，进行数据查找时，首先查看查询条件是否命中某条索引，符合则通过索引査找相关数据，如果不符合则需要全表扫描 ，即需要一条一条地查找记录，直到找到与条件符合的记录。减少磁盘的IO次数，加快查询速度。
7.1 索引的优点
提高数据检索的效率，降低 数据库的IO成本。
通过创建唯一索引，可以保证数据库表中每一行 数据的唯一性。
对于有依赖关系的子表和父表联合查询时， 可以提高查询速度。
在使用分组和排序子句进行数据查询时，可以显著 减少查询中分组和排序的时间 ，降低了CPU的消耗。
7.2 索引的缺点
创建索引和维护索引要耗费时间，并且随着数据量的增加，所耗费的时间也会增加。
索引需要占用磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。
虽然索引大大提高了查询速度，同时却会降低更新表的速度。当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。
7.1 MySQL 索引的分类
普通索引（INDEX）：用于加速查询，不需要唯一。可以在列上创建索引以加速查询性能，没有唯一性要求。只是用于提高查询效率。这类索引可以创建在任何数据类型 中，其值是否唯一和非空，要由字段本身的完整性约束条件决定。建立索引以后，可以通过索引进行查询。例如，在name上建立一个普通索引，查询记录时就可以根据该索引进行查询。
CREATE TABLE employees (
employee_id INT ,
name VARCHAR (50 ),
INDEX idx_name (name )
);
1 2 3 4 5 6 7 8 9 - `唯一索引（UNIQUE）`：在创建唯一性索引时，限制该**索引的值必须是唯一的**，但允许有空(NULL)值。在一张数据表里可以有多个唯一索引。 - ```sql CREATE TABLE employees ( employee_id INT, name VARCHAR(20) UNIQUE );
主键索引（PRIMARY KEY）：索引列值必须唯一，不能为NULL。 一个表只能有一个主键索引；不仅确保列中的所有值都是唯一的，还不允许包含 NULL 值。每个表只能有一个主键索引，它主要用于唯一标识表中的每一行，并作为表的主标识符。
CREATE TABLE employees (
employee_id INT PRIMARY KEY ,
first_name VARCHAR (50 ),
last_name VARCHAR (50 )
);
1 2 3 4 5 6 7 8 9 10 - `全文索引（FULL TEXT）`：通过建立在文本列上的索引。允许对文本字段中的单词进行快速的搜索和匹配，通常用于处理大型文本数据，如文章内容、博客帖子和产品描述等。全文索引支持基于自然语言的查询，可以显著提高全文搜索的性能。 - ```sql CREATE TABLE articles ( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT (title, body) -- 为 title 和 body 列创建全文索引 );
7.2 MySQL 不同类型索引用途和区别
普通索引常用于过滤数据。例如，以商品种类作为索引，检索种类为“手机”的商品。
唯一索引主要用于标识一列数据不允许重复的特性，相比主键索引不常用于检索的场景。
主键索引是行的唯一标识，因而其主要用途是检索特定数据。
全文索引效率低，常用于文本中内容的检索。
7.3 MySQL 使用索引 7.3.1 创建索引 创建索引方式：创建表事定义CREATE TABLE指定需要创建的索引列，使用ALTER TABLE语句在存在的表上创建索引；使用CREATE INDEX语句在已存在的表上添加索引。
1. 普通索引（INDEX） 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 #>>> 在创建表时指定 create table test06( id int not null, name varchar(100) not null, birthdy date, sex char(1) not null, index nameindex (name(50)) ); #>>> 基于表结构创建 create table test07( id int not null, name varchar(100) not null, birthday date, sex char(1) not null ); #>>> 创建索引 create index nameindex on test07(name(50)); #>>> 修改表结构创建 create table test08( id int not null, name varchar(100) not null, birthday date, sex char(1) not null ); #>>> 添加普通索引 ALTER TABLE test08 ADD INDEX nameIndex(name(50)); #>>> 查看索引 #>>> 方式一 show index from test06; #>>> 方式二 show create table test06;
提示：Non_unique唯一代表。该约束非唯一。
2. 唯一索引（UNIQUE） 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 #>>> 在创建表时指定 create table test09( id int not null, name varchar(100) not null, birthday date, sex char(1) not null, unique index id_idex(id) ); #>>> 插入测试数据 INSERT INTO emp01.test09(id,name,sex) VALUES (1,"赵云","男"); #>>> 再次插入相同数据 INSERT INTO emp01.test09(id,name,sex) VALUES (1,"赵云","男"); ---此时会报：ERROR 1062 (23000): Duplicate entry '1' for key 'id_idex' #>>> 基于表结构创建 create table test10( id int not null, name varchar(100) not null, birthday date, sex char(1) not null ); #>>> 创建索引 CREATE unique INDEX idIndex ON test10(id);
3. 主键索引（PRIMARY KEY） 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 #>>> 创建表时时指定 create table test11( id int, name varchar(100), birthday date, sex char(1), primary key (id) ); #>>> 修改表结构创建 create table test12( id int not null, name varchar(100) not null, birthday date, sex char(1) not null ); #>>> 添加索引 ALTER TABLE test12 ADD PRIMARY KEY (id);
主键索引不能使用基于表结构创建的方式创建。
7.3.2 删除索引 1. 普通索引（INDEX） 1 2 3 4 5 #>>> 直接删除 DROP INDEX nameIndex ON test06; #>>> 修改表结构删除 ALTER TABLE test06 DROP INDEX nameIndex;
2. 唯一索引（UNIQUE） 1 2 3 4 5 #>>> 直接删除 DROP INDEX idIndex ON test09; #>>> 修改表结构删除 ALTER TABLE test09 DROP INDEX idIndex;
3. 主键索引（PRIMARY KEY） 1 ALTER TABLE test11 DROP PRIMARY KEY;
主键不能采用直接删除的方式删除。
7.3.3 查看索引 1 2 3 4 5 #>>> 方式一 SHOW INDEX FROM test06; #>>> 方式二 SHOW CREATE TABLE test06;
7.4 设置索引的原则
频繁查询的列 ：对经常在 SELECT 语句的 WHERE 子句、JOIN 子句、ORDER BY 子句和 GROUP BY 子句中出现的列创建索引。唯一性 ：对那些需要保持唯一值的列（如主键、唯一键）创建索引，以确保数据的完整性和一致性。低选择性 ：唯一性太差的字段不适合作为索引，例如性别。频繁增删改的字段不要建立索引 ：字段的变化MySQL是需要重新维护索引的，假设某个字段频繁修改，那就意味着需要频繁的重建索引，必然影响MySQL的性能。不要建立太多的索引 ：MySQL维护索引是需要空间和耗费性能的。如果索引过多，这无疑是增加了MySQL的负担。
7.5 索引失效的场景？怎么避免设置的索引失效？
index(key)每张表可以有很多列做index，必须的起名
1 2 3 4 5 6 7 面试题: 导致SQL执行慢的原因： 1.硬件问题。如网络速度慢，内存不足，I/O吞吐量小，磁盘空间满了等。 2.没有索引或者索引失效。 3.数据过多（分库分表）。 4.服务器调优及各个参数设置（调整my.cnf）。 索引：当查询速度过慢可以通过建立优化查询速度，可以当作调优
八、权限管理 8.1 用户管理 简介：用户管理涉及用户创建、权限分配、修改和删除，以及用户账户的其他管理任务。
8.1.1 MySQL服务器登录方式 1. 本地登录MySQL服务器方式
2. 远程登录MySQL服务器方式
下面详细介绍命令中的参数：
-h参数： 主机名或者主机IP，hostname为主机，hostIP为主机IP。-P参数 ：MySQL服务的端口，通过该参数连接到指定的端口。MySQL服务的默认端口是3306， 不使用该参数时自动连接到3306端口，port为连接的端口号。 -u参数 ：用户名，username为用户名。 -p参数：提示输入密码。 DatabaseName参数指明登录到哪一个数据库中。如果没有该参数，就会直接登录到MySQL数据库中，然后可以使用USE命令来选择数据库。 -D参数：指定数据库名称。-e参数 ：后面可以直接加SQL语句。登录MySQL服务器以后即可执行这个SQL语句，然后退出MySQL服务器。
示例：
3. 修改用户主机地址
1 2 3 4 5 6 7 8 mysql> update mysql.user set host = '%' where user = 'root'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
4. 修改数据库端口
1 2 3 4 [root@mysql-server ~] 在到[mysql]标签下面添加port=指定端口。重启服务
8.2 创建用户 8.2.1 方式一：CREATE USER CREATE USER语句的基本语法形式如下：
1 mysql> CREATE USER 用户名 [IDENTIFIED BY '密码'][,用户名 [IDENTIFIED BY '密码']];
参数说明：
用户名参数表示新建用户的账户，由用户（User）和主机名（Host） 构成；“[ ]”表示可选，也就是说，可以指定用户登录时需要密码验证，也可以不指定密码验证，这样用户可以直接登录。不过，不指定密码的方式不安全，不推荐使用。如果指定密码值，这里需要使用IDENTIFIED BY指定明文密码值。 CREATE USER语句可以同时创建多个用户。
示例：
1 2 3 4 5 6 7 8 9 10 mysql> create user xiaofei identified by '1'; # 默认host是 % Query OK, 0 rows affected (0.01 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> select user,host from mysql.user;
1 2 3 4 5 6 7 8 9 10 mysql> create user 'xiaohe'@'localhost' identified by '1'; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> select user,host from mysql.user;
注意：创建用户时需注意用户的主机地址，常用选项如下：
localhost：只允许本地用户登录%：所有主机远程登录192.168.246.%：192.168.246.0网段的所有主机192.168.246.252： 指定主机
8.2.2 方式二：GRANT命令 简介：在MySQL 5.7 中GRANT权限分配时，假设用户不存在，同时会创建一个新的用户，MySQL 8.0已废弃。
GRANT命令语法格式如下：
1 mysql> GRANT 权限1,权限2,…权限n ON 数据库名称.表名称 TO 用户名@用户地址 [IDENTIFIED BY ‘密码口令’];
示例：
1 2 3 4 5 6 7 8 9 10 mysql> grant select,update on mysql.* to maoxiansheng@'%' identified by '1'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> select user,host from mysql.user;
8.3 权限简介 查看MySQL 5.7 中所有权限：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 权限简介 | 权限 | 权限级别 | 权限说明 | :--------------------- | :--------------------- | :------------------------------------ | CREATE | 数据库、表或索引 | 创建数据库、表或索引权限 | DROP | 数据库或表 | 删除数据库或表权限 | GRANT OPTION | 数据库、表或保存的程序 | 赋予权限选项 #小心给予 | ALTER | 表 | 更改表，比如添加字段、索引等 | DELETE | 表 | 删除数据权限 | INDEX | 表 | 索引权限 | INSERT | 表 | 插入权限 | SELECT | 表 | 查询权限 | UPDATE | 表 | 更新权限 | LOCK TABLES | 服务器管理 | 锁表权限 | CREATE USER | 服务器管理 | 创建用户权限 | REPLICATION SLAVE | 服务器管理 | 复制权限 | SHOW DATABASES | 服务器管理 | 查看数据库权限
CREATE和DROP权限，可以创建新的数据库和表，或删除（移掉）已有的数据库和表。如果将 MySQL数据库中的DROP权限授予某用户，用户就可以删除MySQL访问权限保存的数据库。 SELECT、INSERT、UPDATE和DELETE权限允许在一个数据库现有的表上实施操作。 SELECT权限只有在它们真正从一个表中检索行时才被用到。 INDEX权限允许创建或删除索引，INDEX适用于已有的表。如果具有某个表的CREATE权限，就可以在CREATE TABLE语句中包括索引定义。 ALTER权限可以使用ALTER TABLE来更改表的结构和重新命名表。 CREATE ROUTINE权限用来创建保存的 程序（函数和程序），ALTER ROUTINE权限用来更改和删除保存的程序， EXECUTE权限用来执行保存的程序。 GRANT权限允许授权给其他用户，可用于数据库、表和保存的程序。FILE权限使用户可以使用LOAD DATA INFILE和SELECT … INTO OUTFILE语句读或写服务器上的文件，任何被授予FILE权限的用户都能读或写MySQL服务器上的任何文件（说明用户可以读任何数据库目录下的文件，因为服务器可以访问这些文件）。
8.3.1 授予权限原则 权限控制主要是出于安全因素，因此需要遵循以下几个原则：
只授予能满足需要的最小权限 ，防止用户干坏事。比如用户只是需要查询，那就只给select权限就可以了，不要给用户赋予update、insert或者delete权限。
创建用户的时候限制用户的登录主机 ，一般是限制成指定IP或者内网IP段。
为每个用户设置满足密码复杂度的密码 。
定期清理不需要的用户，回收权限或者删除用户。
8.4 查看权限 8.4.1 查看当前用户权限
8.4.2 查看其他用户权限 1 mysql> show grants for maoxiansheng@'%'\G
8.5 添加权限 示例一：授予xiaohe用户study库下所有权限
1 2 3 4 5 6 7 mysql> grant all PRIVILEGES on study.* to xiaohe@'%' identified by '1'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.02 sec) mysql> show grants for xiaohe@'%'\G;
示例二：xiaofei用户所有库所有表权限，但不包括GRANT权限
1 2 3 4 5 6 7 mysql> grant all on *.* to xiaofei@'%' identified by '1'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> show grants for xiaofei@'%'\G;
示例三：授予maoxiansheng用户mysql库user表查看、更新、删除、插入权限
1 2 3 4 5 6 7 mysql> grant select,update,insert,delete on mysql.user to maoxiansheng@'localhost' identified by '1'; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> show grants for maoxiansheng@'localhost'\G;
8.6 回收权限 收回权限就是取消已经赋予用户的某些权限。收回用户不必要的权限可以在一定程度上保证系统的安全性。 MySQL中使用 REVOKE语句取消用户的某些权限。使用REVOKE收回权限之后，用户账户的记录将从 db、host、tables_priv和columns_priv表中删除，但是用户账户记录仍然在user表中保存（删除user表中的账户记录使用DROP USER语句）。
在将用户账户从user表删除之前，应该收回相应用户的所有权限。
回收权限语法格式：
1 REVOKE 权限1 ,权限2 ,…权限n ON 数据库名称.表名称 FROM 用户名@'用户地址' ;
示例一：回收xaiofei用户所有库、所有表中的所有权限
1 2 3 4 5 6 7 mysql> revoke all privileges on *.* from xiaofei@'%'; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> show grants for xiaofei@'%'\G;
示例二：回收maoxiansheng用户mysql库user表查看、更新、删除、插入权限
1 2 3 4 5 6 7 mysql> revoke delete,insert,select,update on mysql.user from maoxiansheng@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> show grants for maoxiansheng@'localhost'\G;
8.7 修改密码 8.7.1 修改当前用户密码 方式一：MySQL 5.7 可以使用
set password = password('当前用户新密码');
1 2 3 4 5 mysql> set password = password('abc123'); Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec)
方式二：ALTER USER
使用ALTER USER命令来修改当前用户密码用户可以使用ALTER命令来修改自身密码。
alter user user() identified by '当前用户新密码';
1 2 3 4 5 mysql> alter user user() identified by 'abcd123'; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)
方式三：SET
使用SET语句来修改当前用户密码使用root用户登录MySQL后，可以使用SET语句来修改密码。
1 2 3 4 5 mysql> set password='abc123'; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)
8.7.2 修改其他用户密码 方式一：ALERT语句
使用ALTER语句修改普通用户的密码可以使用ALTER USER语句来修改普通用户的密码。
ALTER USER user [IDENTIFIED BY '新密码'] [,user[IDENTIFIED BY '新密码']]…;
1 2 3 4 5 mysql> alter user xiaohe@'%' identified by 'abcd123'; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)
方式二：SET命令
使用SET命令来修改普通用户的密码使用root用户登录到MySQL服务器后，可以使用SET语句来修改普通用户的密码。
SET PASSWORD FOR 'username'@'hostname'='new_password';
1 2 3 4 5 mysql> set password for 'xiaohe'@'%'='abc123'; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)
8.7.3 MySQL 5.7 密码管理（了解）
密码过期策略
在MySQL中，数据库管理员可以手动设置账号密码过期，也可以建立一个自动密码过期策略。
过期策略可以是全局的 ，也可以为每个账号设置单独的过期策略。
查看当前密码过期时间
1 2 3 4 5 6 7 mysql> SHOW VARIABLES LIKE 'default_password_lifetime'; + | Variable_name | Value | + | default_password_lifetime | 0 | + 1 row in set (0.02 sec)
方式一：使用SQL语句更改该变量的值并持久化
1 2 3 4 5 6 7 8 9 10 11 mysql> SET GLOBAL default_password_lifetime = 90; Query OK, 0 rows affected (0.00 sec) mysql> SHOW VARIABLES LIKE 'default_password_lifetime'; + | Variable_name | Value | + | default_password_lifetime | 90 | + 1 row in set (0.00 sec)
方式二：配置文件my.cnf配置密码过期时间
1 2 3 4 5 [root@mysql-server ~] [mysqld] default_password_lifetime = 90 [root@mysql-server ~]
方式三：单独设置特定用户的密码过期时间
除了全局设置，你还可以为特定用户设置密码过期时间。使用 ALTER USER 语句可以实现这一点。例如：将 xiaofei 用户的密码过期时间设置为 180 天。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 mysql> alter user 'xiaofei'@'%' password expire interval 180 day; Query OK, 0 rows affected (0.00 sec) mysql> SELECT User, Host, Password_last_changed, DATE_ADD(Password_last_changed, INTERVAL @@default_password_lifetime DAY) AS Password_expiry_date FROM mysql.user WHERE User='xiaofei' AND Host='%';
Password_last_changed：为上次密码修改的时间；Password_expiry_date：密码过期时间
方式四：设置用户密码永不过期，默认
1 2 3 mysql> SET GLOBAL default_password_lifetime = 0; mysql> SHOW VARIABLES LIKE 'default_password_lifetime';
8.8 删除用户 方式一：使用DROP方式删除
使用DROP USER语句来删除用户时，必须拥有DROP USER权限。
DROP USER user[,user]…;
示例：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 mysql> drop user xiaohe; # 默认Host为'%' Query OK, 0 rows affected (0.00 sec) mysql> drop user maoxiansheng@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> select user,host from mysql.user; + | user | host | + | maoxiansheng | % | | root | % | | xiaofei | % | | mysql.session | localhost | | mysql.sys | localhost | | xiaohe | localhost | + 6 rows in set (0.00 sec)
方式二：使用DELETE方式删除
DELETE FROM mysql.user WHERE Host=’<主机地址>’ AND User=’<用户名>’;
示例：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 mysql> delete from mysql.user where host='%' and user='maoxiansheng'; Query OK, 1 row affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> select user,host from mysql.user; + | user | host | + | root | % | | xiaofei | % | | mysql.session | localhost | | mysql.sys | localhost | | xiaohe | localhost | + 5 rows in set (0.00 sec)
注意：不推荐通过DELETE 方式进行删除，系统会有残留信息保留。而drop user命令会删除用户以及对应的权限，执行命令后你会发现mysql.user表和mysql.db表的相应记录都消失了。
8.9 查看密码复杂度 MySQL 默认启用了密码复杂度设置，插件名字叫做 validate_password，初始化之后默认是安装的，如果没有安装执行下面的命令会返回空或者没有值，这时需要安装该插件
1 2 # >>> 安装插件 mysql> INSTALL PLUGIN validate_password SONAME 'validate_password.so' ;
1 2 3 4 5 6 7 8 9 10 11 12 13 mysql> show variables like 'validate%'; + | Variable_name | Value | + | validate_password_check_user_name | OFF | | validate_password_dictionary_file | | | validate_password_length | 8 | | validate_password_mixed_case_count | 1 | | validate_password_number_count | 1 | | validate_password_policy | MEDIUM | | validate_password_special_char_count | 1 | +
参数解释：
validate_password_length：密码最少长度，默认值是8最少是0
validate_password_dictionary_file：用于配置密码的字典文件，字典文件中存在的密码不得使用。
validate_password_policy： 代表的密码策略，默认是MEDIUM
validate_password_number_count：最少数字字符数，默认1最小是0
validate_password_mixed_case_count ：最少大写和小写字符数(同时有大写和小写)，默认为1最少是0
validate_password_special_char_count ：最少特殊字符数，默认1最小是0
validate_password_length ：#密码最少长度，默认值是8最少是0
validate_password_dictionary_file：#用于配置密码的字典文件，字典文件中存在的密码不得使用。
validate_password_policy： #代表的密码策略，默认是MEDIUM
validate_password_number_count ：#最少数字字符数，默认1最小是0
validate_password_mixed_case_count ：#最少大写和小写字符数(同时有大写和小写)，默认为1最少是0
validate_password_special_char_count ：#最少特殊字符数，默认1最小是0
validate_password_length ：#密码最少长度，默认值是8最少是0
validate_password_dictionary_file：#用于配置密码的字典文件，字典文件中存在的密码不得使用。
validate_password_policy： #代表的密码策略，默认是MEDIUM
validate_password_number_count ：#最少数字字符数，默认1最小是0
validate_password_mixed_case_count ：#最少大写和小写字符数(同时有大写和小写)，默认为1最少是0
validate_password_special_char_count ：#最少特殊字符数，默认1最小是0
1 2 3 4 5 6 7 8 mysql> select @@validate_password_policy; + | @@validate_password_policy | + | MEDIUM | + 1 row in set (0.00 sec)
示例一：LOW策略示例
1 2 3 4 5 6 7 8 mysql> SET GLOBAL validate_password_policy = 'LOW'; mysql> SET GLOBAL validate_password_length = 8; mysql> CREATE USER 'user_low'@'localhost' IDENTIFIED BY 'abcdefgh'; mysql> CREATE USER 'user_low_fail'@'localhost' IDENTIFIED BY 'abcd';
示例二：MEDIUM策略示例
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 mysql> SET GLOBAL validate_password_policy = 'MEDIUM'; mysql> SET GLOBAL validate_password_length = 8; mysql> SET GLOBAL validate_password_mixed_case_count = 1; mysql> SET GLOBAL validate_password_number_count = 1; mysql> SET GLOBAL validate_password_special_char_count = 1; mysql> CREATE USER 'user_medium'@'localhost' IDENTIFIED BY 'Abcdef1!'; mysql> CREATE USER 'user_medium_fail'@'localhost' IDENTIFIED BY 'Abcdefgh';
示例三：STRONG策略示例
1 2 3 4 5 6 7 8 9 10 11 mysql> SET GLOBAL validate_password_policy = 'STRONG'; mysql> SET GLOBAL validate_password_length = 12; mysql> SET GLOBAL validate_password_mixed_case_count = 1; mysql> SET GLOBAL validate_password_number_count = 1; mysql> SET GLOBAL validate_password_special_char_count = 1; mysql> SET GLOBAL validate_password_dictionary_file = '/opt/study.txt';
8.10 权限控制机制 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 四张表：user db tables_priv columns_priv 1.用户认证 查看mysql.user表 2.权限认证 以select权限为例： 1.先看 user表里的select_priv权限 Y:不会接着查看其他的表 拥有查看所有库所有表的权限 N:接着看db表 2.db表: #某个用户对一个数据库的权限。 Y:不会接着查看其他的表 拥有查看所有库所有表的权限 N:接着看tables_priv表 3.tables_priv表：#针对表的权限 tables_priv:如果这个字段的值里包括select 拥有查看这张表所有字段的权限，不会再接着往下看了 tables_priv:如果这个字段的值里不包括select，接着查看下张表还需要有column_priv字段权限 4.columns_priv:针对数据列的权限表 columns_priv:有select，则只对某一列有select权限 没有则对所有库所有表没有任何权限 注：其他权限设置一样。 # 授权级别排列 - mysql.user #全局授权 - mysql.db #数据库级别授权 - 其他 #表级，列级授权
九、日志管理 简介：所谓日志，就是一种将行为动作记录到一个地方，这个地方可以是文件，文本等可存储的载体。Mysql日志就是记录整个mysql从启动、运行、到结束的整个生命周期下的行为
官网链接：https://dev.mysql.com/doc/refman/5.7/en/server-logs.html
9.1 MySQL 5.7日志分类
错误日志：默认开启无法关闭。错误日志包含mysqld启动和关闭时间的记录。它还包含诊断消息，如服务器启动和关闭期间以及服务器运行时发生的错误、警告和注释。通用查询日志 ：通用日志主要是记录所有关于服务器的活动，包括所有客户端的连接和所有执行的SQL语句,mysql的通用日志是关闭的，如果需要设置可以通过general_log 选项开启通用日志。通用查询日志记录了大量的信息，它可能会对数据库服务器的性能产生显著影响，特别在高负载的生产环境中。而且还会占用大量磁盘空间和磁盘 I/O 问题，所以一般调试的时候才会开启通用日志。二进制日志(bin log)修改数据库内容的操作，比如 INSERT、UPDATE 和 DELETE 语句，以及数据定义语言（DDL）语句，如 CREATE TABLE、ALTER TABLE 等。MySQL 的二进制日志并不是由纯粹的0和1序列组成的简单文件。实际上，它是一种特殊的二进制格式文件，这种格式旨在高效地存储和记录数据库更改操作。这种专门为 MySQL 优化的格式，既保证了存储和传输的高效性，也便于使用相应的工具进行内容查看和分析。例如mysqlbinlog这个工具就可以查看二进制文件的内容。MySQL的二进制日志不像通用日志、慢查询日志一样耗费性能，二进制日志对性能的影响通常较小。一般在主从复制的架构中才需要开启二进制日志。中继日志(Relay log)慢查询日志执行时间超过特定阈值的所有查询，可以根据这些信息监控和诊断数据库性能问题。一般情况下是需要调试时候才开启慢查询日志，如果长期开启可能会对数据库性能产生一定的影响，且在大型或高负载的环境中日志文件可能会快速增长，占用过多磁盘空间。重做日志（redo log） ：重做日志（Redo Log）是InnoDB存储引擎特有的日志系统，用于保证事务的持久性和恢复能力。重做日志也是以二进制格式存储存储的，并且如果使用的引擎是innodb，默认是开启了重做日志。commit回滚日志（undo log） ：undo log是mysql中比较重要的事务日志之一，顾名思义，undo log是一种用于撤销回退的日志，在事务没提交之前，MySQL会先记录更新前的数据到 undo log日志文件里面，当事务回滚时或者数据库崩溃时，可以利用 undo log来进行回退。
注意：降低MySQL数据库的性能。例如，在查询非常频繁的MySQL数据库系统中，如果开启了通用查询日志和慢查询日志，MySQL数据库会花费很多时间记录日志。I占用大量的磁盘空间。对于用户量非常大、操作非常频繁的数据库，日志文件需要的存储空间设置比数据库文件需要的存储空间还要大。所以不必要的日志文件尽量就不开启。嘿嘿！
9.1.1 错误日志 可以使用my.cnf文件中加入--log-error=[file_name]来指定mysqld记录的错误日志文件。
查看错误日志文件路径
1 2 3 4 5 6 7 mysql> show variables like 'log_error'; + | Variable_name | Value | + | log_error | /var/log/mysqld.log | + 1 row in set (0.00 sec)
9.1.2 通用查询日志（了解） 通用查询日志可以帮助我们了解操作发生的具体时间和操作的细节，对找出异常发生的原因极其关键。配置通用查询日志可以通过my.cnf加入一下配置参数打开：
general_log：设置是否开启通用日志，默认为关闭（0），1表示开启；general_log_file：设置通用日志的存放位置和文件名。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 mysql> show variables like '%general%' ; +------------------+---------------------------------+ | Variable_name | Value | +------------------+---------------------------------+ | general_log | OFF | | general_log_file | /var/lib/mysql/mysql-server.log | +------------------+---------------------------------+ 2 rows in set (0.00 sec) [root@mysql-server ~] general_log=ON general_log_file=/var/log /mysql/general.log [root@mysql-server ~] [root@mysql-server ~] [root@mysql-server ~] [root@mysql-server ~] [root@mysql-server ~] mysql> show variables like '%general%' ; +------------------+----------------------------+ | Variable_name | Value | +------------------+----------------------------+ | general_log | ON | | general_log_file | /var/log /mysql/general.log | +------------------+----------------------------+ 2 rows in set (0.00 sec) 注:如果发现general_log一直为off，可以尝试把general.log文件删除掉，然后重启MySQL让其自动生成general.log文件即可。 mysql> select user,host from mysql.user; [root@mysql-server ~]
重新刷新日志文件，生产环境中随着程序的运行，日志文件会越来越大。此时就需要重新生成文件或者刷新文件。
方式一：重新生成文件
1 2 3 4 [root@mysql-server ~] [root@mysql-server ~] -rwxr-xr-x. 1 mysql mysql 512 7月 12 10:36 /var/log /mysql/general.log.old [root@mysql-server mysql]
此方法效率较低，将文件名称修改后，会重新成功一个新的日志文件。在实际生产环境中需要考虑日志是否归档处理，如果不需要则直接删除就行。释放磁盘空间。
方式二：刷新文件
注意：第二种通过刷新的方式，会导致旧数据清空。如果旧数据需要保留还是建议归档或者提前备份。
9.1.3 二进制日志 在MySQL 5.7中二进制(bin log)默认是关闭状态，MySQL 8.0默认开启状态，可以通过添加my.cnf配置参数开启二进制日志。binlog可以说是MySQL中比较重要的日志了，在日常开发及运维过程中，经常会遇到。
binlog即binary log，二进制日志文件，也叫作变更日志（update log）。它记录了数据库所有执行的 DDL 和 DML等数据库更新事件的语句，但是不包含没有修改任何数据的语句（如数据查询语句select、show等）。
binlog主要应用场景：
一是用于数据恢复
二是用于数据复制
1. 查看二进制日志是否开启 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 mysql> select @@global.log_bin; + | @@global.log_bin | + | 0 | + 1 row in set (0.00 sec) mysql> show variables like '%log_bin%' ; + | Variable_name | Value | + | log_bin | OFF | | log_bin_basename | | | log_bin_index | | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | + 6 rows in set (0.00 sec)
2. 开启二进制日志 1 2 3 4 5 6 7 8 9 10 11 12 13 [root@mysql-server mysql] log -bin=/var/log /mysql/binlogserver-id=1 [root@mysql-server mysql] [root@mysql-server mysql] -rw-r-----. 1 mysql mysql 154 7月 12 12:43 /var/log /mysql/binlog.000001
新建的文件夹需要更改为mysql属主和属组，且需要具有755权限
3. 查看二进制日志 当MySQL创建二进制日志文件时，先创建一个以“mylog”为名称、以“.index”为后缀的文件，再创建一 个以“mylog”为名称、以“.000001”为后缀的文件。 MySQL服务 重新启动一次 ，以“.000001”为后缀的文件就会增加一个，并且后缀名按1递增。即日志文件的 个数与MySQL服务启动的次数相同；
如果日志长度超过了 max_binlog_size 的上限（默认是1GB），就会创建一个新的日志文件。 查看当前的二进制日志文件列表及大小。指令如下：
1 2 3 4 5 6 7 mysql> show binary logs; + | Log_name | File_size | + | binlog.000001 | 154 | + 1 row in set (0.00 sec)
查看二进制日志
1 [root@l66 mysql]# mysqlbinlog mylog.000001 -v
at 4：事件开始位置end_log_pos 123：事件结束的位置240712 12:43:46：产生事件的时间戳
1 [root@mysql-server mysql]
4. 二进制日志三种格式 查看二进制日志格式
1 2 3 4 5 6 7 mysql> show variables like 'binlog_format'; + | Variable_name | Value | + | binlog_format | ROW | + 1 row in set (0.01 sec)
4.1 Statement格式 STATEMENT 格式（语句模式，出现在 MySQL 5.1 之前） ：在这种格式下，binlog 记录的是执行的 SQL 语句的文本。
优点：日志文件通常较小，复制效率较高。
缺点：在某些情况下，由于数据库环境的差异（如表结构、字符集等），在从服务器上重放这些 SQL 语句可能会导致不一致的结果。例如，获取当前时间的函数或存储过程等，可能会导致数据不一致。
1 2 [mysqld] binlog_format = STATEMENT
4.2 ROW格式
ROW 格式（行模式，诞生于 MySQL 5.1） ：在这种格式下，binlog 记录的是每一行数据更改的具体内容。
优点：能够精确地记录数据的变化，避免了 STATEMENT 格式中的环境依赖问题，提供了更强的一致性保证。
缺点：日志文件可能会比 STATEMENT 格式大，因为记录了每一行的详细变化。此外，ROW 格式的日志在进行大量数据更新时可能会导致更高的 I/O 开销。
1 2 [mysqld] binlog_format = ROW
4.3 MIXED格式
MIXED 格式（混合模式） ：在这种格式下，binlog 可以根据具体的 SQL 语句和操作自动选择使用 STATEMENT 或 ROW 格式。
优点：结合了 STATEMENT 和 ROW 格式的优点，能够在保证一致性的同时尽可能地优化日志大小和复制性能。
缺点：由于混合使用了两种格式，可能需要更复杂的管理和监控。在某些特定情况下，MIXED 格式可能无法达到最优的性能或一致性。
1 2 [mysqld] binlog_format = MIXED
4.4 MySQL 5.7 运行时修改 1 mysql> SET GLOBAL binlog_format = 'STATEMENT';
注意：修改二进制日志格式需要具有超级用户权限（SUPER 权限），并且只对新的会话生效，已有会话不受影响。
5. MySQL 5.7二进制日常管理事项
每次重启MySQL实例，都会重新生成新的二进制文件
执行flush logs;会重新生成新的二进制日志文件
执行reset master;会删除所有的binlog日志文件。危险操作
可以通过purge binary logs to 'binlog.000002'删除binlog.000002之前的二进制文件。
可以通过set sql_log_bin=0关闭当前会话的执行操作记录至二进制日志文件，需要具有 SUPER 权限。在复制环境中使用 sql_log_bin = 0 时要小心，以避免主从数据不一致。
2. 查看慢查询日志参数 1 2 3 4 5 6 7 8 mysql> SHOW VARIABLES LIKE '%slow_query%'; + | Variable_name | Value | + | slow_query_log | ON | | slow_query_log_file | /var/log/mysql/slow_query.log | + 2 rows in set (0.00 sec)
3. 验证 1 2 3 4 5 6 7 8 9 10 11 mysql> select sleep(6); + | sleep(6) | + | 0 | + 1 row in set (6.00 sec) [root@mysql-server ~]
9.1.5 中继日志 中继日志（Relay Log）是从服务器（Slave）用于记录从主服务器（Master）接收到的二进制日志事件的日志文件。这些事件在从服务器上被重新执行以保持主从数据的一致性。中继日志的主要作用是存储从主服务器复制过来的二进制日志事件。在复制过程中，从服务器上的 I/O 线程从主服务器读取二进制日志事件，并将其写入中继日志文件。从服务器上的 SQL 线程读取中继日志，并执行其中的事件，从而更新从服务器上的数据。
扩展
UNIX Socket连接方式其实不是一个网络协议，所以只能在MySQL客户端和数据库实例在同一台服务器上的情况下使用。本地进程间通信的一种方式
1 2 3 4 5 通过socket方式登录 查看sock的存放路径 [root@l66 ~]# cat /etc/my.cnf | grep sock socket=/var/lib/mysql/mysql.sock [root@l66 ~]# mysql -uroot -p'1!' -S /var/lib/mysql/mysql.sock
Day04 十、数据备份与恢复 10.1 为什么要备份 MySQL 备份在保护数据完整性、防止各种不可预见的灾难、硬件故障、数据丢失、损坏和意外删除方面发挥着关键作用。如果没有可靠的备份，数据丢失的后果可能会很严重。企业面临运营中断、财务损失、声誉受损甚至合规违规的风险。 学习MySQL 备份的重要性以及它们如何降低这些风险将有助于组织保证数据一致性、业务连续性，并确保数据在需要时安全且可恢复。
主机故障 ：我们可能会因磁盘停滞或磁盘损坏而遇到多种问题。同样，在云服务中，我们的数据库实例可能会损坏并且无法访问。数据损坏 ：这可能发生在断电时，MySQL 无法正确写入并关闭文件，有时当 MySQL 再次启动时，由于数据损坏而无法启动，并且崩溃恢复过程无法修复它。备份 ：能够防止由于机械故障以及人为误操作带来的数据丢失，例如将数据库文件保存在了其它地方。冗余 ： 数据有多份冗余，但不等备份，只能防止机械故障带来的数据丢失，例如主备模式、数据库集群。
10.1.1 MySQL备份的专业名词
RTO ：RTO（RecoveryTimeObjective，恢复时间目标）是故障发生到业务恢复能时间点的最大长度。与之相关的问题是：多久可以恢复？ RPO ：RPO（RecoveryPointObjective，恢复点目标）是故障发生后业务系统可容忍的数据丢失量。与之相关的问题是：会丢失多少数据？
10.2 MySQL数据备份需要重视的内容
选择合适的备份方式 ：根据具体需求和数据量大小，选择适合的逻辑备份或物理备份方式。逻辑备份通常生成可读的文件，便于编辑和审查，而物理备份适用于大型数据库，恢复时间较短。备份数据文件和日志文件 ：在备份时，需要同时备份数据库的数据文件和日志文件，以确保数据的完整性。如果备份文件不完整，可能无法正确恢复数据。定期备份 ：为了确保数据的安全性，需要定期进行备份操作。备份的频率可以根据具体情况进行设置，通常建议每天备份一次。存储备份文件 ：备份文件应该存储在可靠的介质上，如硬盘；以防止数据丢失。此外，备份文件尽量存储在不同的地点，以避免因某一地点的灾害导致备份文件的损失。备份文件的安全性和完整性 ：备份文件需要受到保护，以防止未经授权的访问和篡改。同时，需要定期验证备份文件的完整和可恢复性，以确保在需要时能够成功恢复数据。备份的自动化和监控 ：可以使用自动化备份工具来定期执行备份操作，并监控备份过程，确保备份顺利完成。此外，还可以设置备份失败的告警通知，以便及时处理备份问题。备份策略的灵活性 ：备份策略应该具有一定的灵活性，以适应业务发展和数据增长的变化。需要定期评估备份策略的有效性，并根据实际情况进行调整和优化。
10.3 MySQL 备份类型
物理备份： 备份数据文件，转储数据库物理文件到某一目录。 直接复制数据库文件，适用于大型数据库环境，不受存储引擎的限制，但不能恢复到不同的MySQL版本。 备份文件相对较大(备份表空间,包含数据与索引)。物理备份恢复速度比较快，但占用空间比较大，MySQL中可以用xtrabackup工具来进行物理备份。 逻辑备份： 对数据库对象利用工具进行导出工作，汇总入备份文件内。逻辑备份恢复速度慢，需要重建索引,存储过程等。但占用空间小，更灵活。MySQL 中常用的逻辑备份工具为mysqldump 。逻辑备份就是 备份sql语句 ，在恢复的时候执行备份的sql语句实现数据库数据的重现。
热备(hot backup) ：在线备份，数据库处于运行状态，这种备份方法依赖于数据库的日志文件。对应用基本无影响(但是性能还是会有下降,所以尽量不要在主上做备份,在从库上做)
优点：不中断服务。高可用性。
缺点：对性能有一定影响，备份的时间较长时，备份文件可能不一致。
冷备(cold backup) ：需要完全停止数据库服务来进行备份。备份过程中数据库服务完全停止，没有任何运行的数据库操作。确保备份文件的一致性和完整性。copy/tar/unzip
优点：备份文件的一致性和完整性最高。不会影响数据库性能。
缺点：完全停机，服务不可用。不适用于需要高可用性的系统。
温备(warm backup) ：针对myisam的备份(myisam不支持热备),备份时候实例只读不可写，数据库锁定表格（不可写入但可读）的状态下进行的。对应用影响很大。通常加一个读锁，备份时候实例只读不可写，数据库锁定表格（不可写入但可读）的状态下进行的
合适的备份策略取决于系统的可用性要求、备份窗口和数据一致性需求。在实际操作中，可能会结合使用多种备份策略以实现最佳的备份效果和数据保护。
10.4 MySQL 备份工具
Percona XtraBackup 是一个开源的 MySQL 数据库备份工具，它由 Percona 开发，支持 MySQL、MariaDB 和 Percona Server。XtraBackup 提供了热备份功能，可以在数据库运行过程中执行备份，不会中断数据库服务。
热备份 ：在不停止数据库服务的情况下执行备份，确保数据库在备份期间仍然可用。增量备份 ：只备份自上次完整备份以来发生变化的数据，节省存储空间和备份时间。快速恢复 ：可以快速恢复备份，减少数据库停机时间。数据压缩 ：支持压缩备份数据，节省存储空间。
ibbackup 是 InnoDB Hot Backup 的命令行工具，它是 MySQL 的商业备份工具，可以进行热备份。这意味着可以在不停止数据库的情况下进行备份。ibbackup 工具已经被 Percona XtraBackup 所取代，因为 XtraBackup 提供了更多功能并且是开源的。
热备份 ：支持对运行中的数据库进行备份，而不会中断数据库的正常运行。完全备份和增量备份 ：支持完整备份和增量备份。一致性备份 ：确保备份数据的一致性，即使在备份期间有数据写入操作。支持多种存储引擎 ：主要支持 InnoDB 引擎，但也可以备份 MyISAM 表。
mysqldump 是 MySQL 数据库管理系统自带的一个用于生成数据库备份的命令行工具。它可以将数据库内容导出为 SQL 语句或其他格式，方便数据库的备份和恢复。
备份数据库 ：将数据库导出为 SQL 文件或其他格式，方便备份。迁移数据库 ：将导出的数据库文件导入到另一个数据库实例，实现数据库迁移。生成数据定义语句 ：导出数据库表的结构（CREATE TABLE 语句）。生成数据操作语句 ：导出数据库表中的数据（INSERT 语句）。支持多种格式 ：支持 SQL、CSV 等格式的导出。选择性备份 ：可以指定备份整个数据库、某些表或特定的行。
mysqlbackup 是 MySQL Enterprise Backup 工具的一部分，专为企业用户提供，提供了热备份、增量备份、压缩、加密等高级备份和恢复功能。它支持 MySQL 的所有存储引擎，包括 InnoDB 和 MyISAM，并且能够在数据库运行期间执行备份，确保最小的停机时间。
热备份 ：支持在数据库运行期间进行备份，无需停机。增量备份 ：只备份自上次完整备份以来发生变化的数据，节省存储空间和备份时间。差异备份 ：只备份自上次增量或完整备份以来发生变化的数据。压缩备份 ：对备份数据进行压缩，节省存储空间。加密备份 ：对备份数据进行加密，确保数据安全。自动验证备份 ：在备份过程中和之后验证备份数据的完整性。
10.5 物理备份的方式 10.5.1 完全备份（完整备份） 每次对数据进行完整的备份，即对整个数据库的备份、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是差异备份与增量备份的基础。
优点： 备份与恢复操作简单方便，恢复时一次恢复到位，恢复速度快缺点： 占用空间大，备份速度慢
10.5.2 增量备份 每次备份上一次备份到现在产生的新数据。只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。以上次完整备份或上次的增量备份的时间为时间点，仅备份这之间的数据变化。
特点： 因而备份的数据量小，占用空间小，备份速度快。但恢复时，需要从上一次的完整备份起按备份时间顺序，逐个备份版本进行恢复，恢复时间长，如中间某次的备份数据损坏，将导致数据的丢失。
10.5.3 差异备份 只备份跟完整备份不一样的。备份那些自从第一次次完全备份之后被修改过的所有文件，备份的时间起点是从第一次次完整备份起，且以后每次备份都是和第一次完整备份进行比较（注意是第一次，不是上一次），备份自第一次完整备份以来所有的修改过的文件。备份数据量会越来越大。
特点： 占用空间比增量备份大，比完整备份小，恢复时仅需要恢复第一个完整版本和最后一次的差异版本，恢复速度介于完整备份和增量备份之间。
简单的讲，完整备份就是不管三七二十一，每次都把指定的备份目录完整的复制一遍，不管目录下的文件有没有变化；增量备份就是每次将之前（第一次、第二次、直到前一次）做过备份之后有变化的文件进行备份；差异备份就是每次都将第一次完整备份以来有变化的文件进行备份。
*10.5.4 percona-xtrabackup 物理备份 * Xtrabackup是开源免费的支持MySQL数据库热备份的软件，在 Xtrabackup 包中主要有 Xtrabackup和innobackupex两个工具。其中Xtrabackup只能备份InnoDB和 XtraDB 两种引擎;innobackupex则是封装了Xtrabackup，同时增加了备份MyISAM引擎的功能 。它不暂停服务创建Innodb热备份。
1. 安装xtrabackup 1 2 3 4 5 6 # >>> 安装xtrabackup [root@mysql-server ~]# wget http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm [root@mysql-server ~]# rpm -ivh percona-release-latest.noarch.rpm [root@mysql-server yum.repos.d]# vim percona-original-release.repo
修改如下内容：将原来的1改为0，关闭校验
1 [root@mysql-server yum.repos.d]
1 [root@mysql-server yum.repos.d]# yum -y install percona-xtrabackup-24.x86_64
注意
1 2 3 4 5 6 7 8 9 10 11 12 13 14 # 如果安装不上报错： Transaction check error: file /etc/my.cnf from install of Percona-Server-shared-56-5.6.46-rel86.2.1.el7.x86_64 conflicts with file from package mysql-community-server-5.7.28-1.el7.x86_64 Error Summary #说是冲突 解决方式如下： 1.先安装yum install mysql-community-libs-compat -y #安装包 2.在安装yum -y install percona-xtrabackup-24.x86_64 参考：https://www.cnblogs.com/EikiXu/p/10217931.html 方式二： 1.先安装percona-xtrabackup 2.在安装mysql 或者先将mysql源back了，重新建立yum缓存。在安装percona-xtrabackup。
以上安装方式如果失效，请用youngfit提供的方式：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 ======第一种======== [root@mysql-server ~]# vim /etc/yum.repos.d/Percona.repo [percona] name = CentOS $releasever - Percona baseurl=http://repo.percona.com/centos/$releasever/os/$basearch/ enabled = 1 gpgkey = file:///etc/pki/rpm-gpg/RPM-GPG-KEY-percona gpgcheck = 1 [root@mysql-server yum.repos.d]# vim /etc/pki/rpm-gpg/RPM-GPG-KEY-percona -----BEGIN PGP PUBLIC KEY BLOCK----- Version: GnuPG v1.4.9 (GNU/Linux) mQGiBEsm3aERBACyB1E9ixebIMRGtmD45c6c/wi2IVIa6O3G1f6cyHH4ump6ejOi AX63hhEs4MUCGO7KnON1hpjuNN7MQZtGTJC0iX97X2Mk+IwB1KmBYN9sS/OqhA5C itj2RAkug4PFHR9dy21v0flj66KjBS3GpuOadpcrZ/k0g7Zi6t7kDWV0hwCgxCa2 f/ESC2MN3q3j9hfMTBhhDCsD/3+iOxtDAUlPMIH50MdK5yqagdj8V/sxaHJ5u/zw YQunRlhB9f9QUFfhfnjRn8wjeYasMARDctCde5nbx3Pc+nRIXoB4D1Z1ZxRzR/lb 7S4i8KRr9xhommFnDv/egkx+7X1aFp1f2wN2DQ4ecGF4EAAVHwFz8H4eQgsbLsa6 7DV3BACj1cBwCf8tckWsvFtldqCP4CiBB50Ku49MU2Nfwq7durfIiePF4IIYRDZgg kHKSfP3oUZBGJx00BujtTobERraaV7lIRIwETZao76MqGt9K1uIqw4NT/jAbi9ce rFaOmAkaujbcB11HYIyjtkAGq9mXxaVqCC3RPWGr+fqAx/akBLQ2UGVyY29uYSBN eVNRTCBEZXZlbG9wbWVudCBUZWFtIDxteXNxbC1kZXZAcGVyY29uYS5jb20+iGAE ExECACAFAksm3aECGwMGCwkIBwMCBBUCCAMEFgIDAQIeAQIXgAAKCRAcTL3NzS79 Kpk/AKCQKSEgwX9r8jR+6tAnCVpzyUFOQwCfX+fw3OAoYeFZB3eu2oT8OBTiVYu5 Ag0ESybdoRAIAKKUV8rbqlB8qwZdWlmrwQqg3o7OpoAJ53/QOIySDmqy5TmNEPLm lHkwGqEldqbFYoTbOCEEJi2yFLg9UJCSBM/sfPaqb2jGP7fc0nZBgUBnFuA9USX72 O0PzVAF7rCnWaIz76iY+AMI6xKeRy91TxYo/yenF1nRSJ+rExwlPcHgI685GNuFG chAExMTgbnoPx1ka1Vqbe6iza+FnJq3f4p9luGbZdSParGdlKhGqvVUJ3FLeLTqt caOn5cN2ZsdakE07GzdSktVtdYPT5BNMKgOAxhXKy11IPLj2Z5C33iVYSXjpTelJ b2qHvcg9XDMhmYJyE3O4AWFh2no3Jf4ypIcABA0IAJO8ms9ov6bFldqTqA0UW2gWQ cKFN4Q6NPV6IW0rV61ONLUc0VFXvYDtwsRbUmUYkB/L/R9fHj4lRUDbGEQrLCoE+ /HyYvr2rxP94PT6Bkjk/aiCCPAKZRj5CFUKRpShfDIiow9qxtqv7yVd514Qqmjb4 eEihtcjltGAoS54+6C3lbjrHUQhLwPGqlAh8uZKzfSZq0C06kTxiEqsG6VDDYWy6 L7qaMwOqWdQtdekKiCk8w/FoovsMYED2qlWEt0i52G+0CjoRFx2zNsN3v4dWiIhk ZSL00Mx+g3NA7pQ1Yo5Vhok034mP8L2fBLhhWaK3LG63jYvd0HLkUFhNG+xjkpeI SQQYEQIACQUCSybdoQIbDAAKCRAcTL3NzS79KlacAJ0aAkBQapIaHNvmAhtVjLPN wke4ZgCePe3sPPF49lBal7QaYPdjqapa1SQ= =qcCk -----END PGP PUBLIC KEY BLOCK----- [root@mysql-server yum.repos.d]# yum -y install percona-xtrabackup
1 2 3 [root@mysql-server yum.repos.d]# innobackupex --version xtrabackup: recognized server arguments: --datadir=/var/lib/mysql innobackupex version 2.4.29 Linux (x86_64) (revision id: 2e6c0951)
1 2 3 4 5 ======第二种======== 百度搜吧，飞哥这边就是用的这种方式 访问以下链接： https://zhuanlan.zhihu.com/p/140414143
下载完成，把包上传至服务器
2.完全备份流程: 1 2 3 4 5 6 7 8 9 10 11 12 # >>> 创建备份目录： [root@mysql-server ~]# mkdir /backup/full/ -p # >>> 备份之前，进入数据库，存入些数据 [root@mysql-server ~]# mysql -uroot -p'1' mysql> create database youngfit; mysql> use youngfit; Database changed mysql> create table t1(id int); # >>> 进行完整备份 [root@mysql-server ~]# innobackupex --user=root --password='1' /backup/full/
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 # >>> 查看完整备份文件 [root@mysql-server ~]# ll /backup/full/ drwxr-x--- 11 root root 304 7月 18 23:05 2024-07-18_23-05-47 ======================================================================== # >>> 完全备份恢复流程 1. 停止数据库 2. 清理环境 3. 重演回滚－－> 恢复数据 4. 修改权限 5. 启动数据库 # >>> 1.关闭数据库： [root@mysql-server ~]# systemctl stop mysqld # >>> 删除所有数据 [root@mysql-server ~]# rm -rf /var/lib/mysql/* [root@mysql-server ~]# rm -rf /var/log/mysqld.log [root@mysql-server ~]# rm -rf /var/log/mysql-slow/slow.log # >>> 2.重演恢复: [root@mysql-server ~]# innobackupex --apply-log /xtrabackup/full/2024-07-16_23-16-31/
innobackupex
Percona XtraBackup 工具脚本，提供了更高级别的备份和恢复操作。innobackupex 脚本实际上是一个对 xtrabackup 工具的包装，使得它更易于使用。
--apply-log
这个选项用于准备备份目录，使其可以被恢复。这个过程也被称为“应用日志”。在备份过程中，XtraBackup 会复制数据文件和事务日志。为了确保数据一致性，必须在数据恢复之前应用这些日志。此步骤会合并所有未完成的事务，从而使数据文件处于一致状态。
/xtrabackup/full/2024-07-16_23-16-31/
指定包含备份数据的目录，该目录通常包含由 XtraBackup 生成的数据文件、日志文件和其他相关文件。
1 2 3 4 5 6 7 8 # >>> 3.确认数据库目录： 恢复之前需要确认配置文件内有数据库目录指定，不然xtrabackup不知道恢复到哪里 [root@mysql-server ~]# cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql # >>> 4.恢复数据： [root@mysql-server ~]# innobackupex --copy-back /backup/full/2024-07-18_23-05-47/
1 2 3 4 5 # >>> 5.修改权限： [root@mysql-server ~]# chown mysql.mysql /var/lib/mysql -R # >>> 启动数据库: [root@mysql-server ~]# systemctl start mysqld
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 6.确认数据是否恢复 mysql> show databases; + | Database | + | information_schema | | emp01 | | mysql | | performance_schema | | study | | sys | + 6 rows in set (0.00 sec) mysql> use emp01; mysql> show tables; + | Tables_in_emp01 | + | emp | | employees | | t3 | | test | | test01 | + 5 rows in set (0.00 sec) ===可以看到数据已恢复===
3. 增量备份流程 原理：每次备份上一次备份到现在产生的新数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 mysql> create database test01; Query OK, 1 row affected (0.00 sec) mysql> create table test01(id int); Query OK, 0 rows affected (0.02 sec) mysql> insert into test01(1); mysql> select * from test01; + | id | + | 1 | + 1 row in set (0.00 sec)
1. 完整备份:周一
1 2 3 4 5 6 7 8 9 10 11 12 13 # >>> 学习环境删除备份目录 [root@mysql-server ~]# rm -rf /backup/* # >>> 第一次进行完整备份 [root@mysql-server ~]# innobackupex --user=root --password='1' /backup/ # >>> 查看备份数据 [root@mysql-server ~]# ll /backup/2024-07-18_23-17-58/ 总用量 12344 -rw-r----- 1 root root 487 7月 18 23:18 backup-my.cnf drwxr-x--- 2 root root 78 7月 18 23:17 emp01 -rw-r----- 1 root root 453 7月 18 23:18 ib_buffer_pool . . . . . .
2、增量备份：周二 —— 周三
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 mysql> use test01; mysql> select * from test01; + | id | + | 1 | + 1 row in set (0.00 sec) mysql> insert into test01(id ) values (2 ); Query OK, 1 row affected (0.01 sec) mysql> select * from test01; + | id | + | 1 | | 2 | + 2 rows in set (0.00 sec)
1 2 3 4 5 6 7 8 9 [root@mysql-server ~] --incremental-basedir:基于哪个备份文件进行增量 [root@mysql-server ~] 总用量 4 drwxr-x--- 11 root root 304 7月 18 23:18 2024-07-18_23-17-58 drwxr-x--- 12 root root 4096 7月 18 23:23 2024-07-18_23-23-14
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 mysql> use test01; mysql> insert into test01(id) values(3); # 模拟周三 Query OK, 1 row affected (0.00 sec) mysql> select * from test01; + | id | + | 1 | | 2 | | 3 | + 3 rows in set (0.00 sec)
1 2 3 4 5 6 7 8 9 10 11 12 13 # >>> 第二次增量备份(基于第二天增量备份实现) [root@mysql-server ~]# innobackupex --user=root --password='1' --incremental /backup/ --incremental-basedir=/backup/2024-07-18_23-23-14/ # 基于前一天的备份为目录 # >>> 查看备份目录 [root@mysql-server ~]# ll /backup/ 2024-07-18_23-17-58 2024-07-18_23-23-14 2024-07-18_23-28-43 # 相当于周三的增量备份 # >>> 查看一下备份目录: [root@mysql-server ~]# ls /xtrabackup/ 2024-07-18_23-17-58 2024-07-18_23-23-14 2024-07-18_23-28-43 全备周一 增量周二 增量周三
数据恢复流程
1 2 3 4 5 6 增量备份恢复流程 1. 停止数据库 2. 清理环境 3. 依次重演回滚redo log－－> 恢复数据 4. 修改权限 5. 启动数据库
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 # >>> 关闭MySQL [root@mysql-server ~]# systemctl stop mysqld # >>> 清除脏数据 [root@mysql-server ~]# rm -rf /var/lib/mysql/* /var/log/mysqld.log # >>> 依次重演回滚redo log :（第一次回滚完整备份日志） [root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2024-07-16_23-31-20/ # >>> 回滚第一次增量备份日志（基于完整备份还原） [root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2024-07-16_23-31-20/ --incremental-dir=/xtrabackup/2024-07-16_23-35-21/ --incremental-dir：增量目录 # >>> 回滚第二次增量备份日志（基于完整备份还原） [root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2024-07-16_23-31-20/ --incremental-dir=/xtrabackup/2024-07-16_23-38-36/ # >>> 恢复数据:（基于MySQL完整备份） [root@mysql-server ~]# innobackupex --copy-back /xtrabackup/2024-07-16_23-31-20/ # >>> 修改权限（属主属组） [root@mysql-server ~]# chown -R mysql.mysql /var/lib/mysql # >>> 启动MySQL服务 [root@mysql-server ~]# systemctl start mysqld 登陆上去看一下:
4、差异备份流程 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 # >>> 清理备份的环境 [root@mysql-server ~]# rm -rf /xtrabackup/* # >>> 登陆数据库，准备环境 mysql> delete from test01.t1; mysql> insert into test01.t1(id) values(1); # >>> 查看当前系统时间 [root@mysql-server ~]# date 2024年 07月 16日 星期二 23:54:54 CST # >>> 第一步：完整备份：周一 [root@mysql-server ~]# innobackupex --user=root --password='1' /xtrabackup/ ------------------------------------------------------------------------ # >>> 差异备份：周二 语法: # innobackupex --user=root --password=888 --incremental /xtrabackup --incremental-basedir=/xtrabackup/完全备份目录（周二） # >>> 3.修改当前系统时间： [root@mysql-server ~]# date 07172358 2024年 07月 17日 星期三 23:58:00 CST # >>> 4.在登陆MySQL服务器，插入测试数据 mysql> insert in to test01.t1(id) values(2); mysql> select * from test01.t1; # >>> 差异备份周二（基于周一完整备份实现） [root@mysql-server ~]# innobackupex --user=root --password='1' --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2024-07-16_23-56-32/ #备份目录基于周一完整的备份 # >>> 查看备份目录 [root@mysql-server ~]# ll /xtrabackup/ 2024-07-16_23-56-32 2024-07-18_00-02-09 ------------------------------------------------------------------------ # >>> 5.再次登陆mysql,插入测试数据 mysql> insert into test01.t1(id) values(3); mysql> select * from test01.test01; # >>> 6.在次修改时间 [root@mysql-server ~]# date 07192358 2024年 07月 19日 星期五 23:58:00 CST # >>> 7.再次差异备份（基于周一完整备份） [root@mysql-server ~]# innobackupex --user=root --password='1' --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2024-07-16_23-56-32/ #还是基于周一的备份 # >>> 8.延申到周三 mysql> insert into test01.t1(id) values(4); mysql> select * from test01.t1; # >>> 9.修改当前系统时间 [root@mysql-server ~]# date 07202358 2024年 07月 20日 星期六 23:58:00 CST # >>> 10.差异备份三 [root@mysql-server ~]# innobackupex --user=root --password='1' --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2024-07-16_23-56-32/ #还是基于周一的备份 # >>> 11.查看一下备份目录 [root@mysql-server ~]# ll /xtrabackup/ drwxr-x--- 8 root root 233 7月 16 23:56 2024-07-16_23-56-32 周一 drwxr-x--- 8 root root 259 7月 18 00:02 2024-07-18_00-02-09 周二 drwxr-x--- 8 root root 259 7月 19 23:59 2024-07-19_23-58-59 周三
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 差异备份恢复流程 1. 停止数据库 2. 清理环境 3. 重演回滚redo log（周一，某次差异）－－> 恢复数据 4. 修改权限 5. 启动数据库 ------------------------------------------------------------------------------------------------------------------------------------------------ # >>> 停止数据库 [root@mysql-server ~]# systemctl stop mysqld # >>> 删除脏数据 [root@mysql-server ~]# rm -rf /var/lib/mysql/* /var/log/mysqld.log # >>> 1.恢复全量的redo log 语法: # innobackupex --apply-log --redo-only /xtrabackup/完全备份目录(周一) [root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2024-07-16_23-56-32/ # >>> 2.恢复差异的redo log 语法:# innobackupex --apply-log --redo-only /xtrabackup/完全备份目录（周一）--incremental-dir=/xtrabacku/某个差异备份 这里我们恢复周三的差异备份 [root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2024-07-16_23-56-32/ --incremental-dir=/xtrabackup/2024-07-20_23-58-20/ #我们恢复周三的差异备份 # >>> 3.恢复数据 语法:# innobackupex --copy-back /xtrabackup/完全备份目录（周一） [root@mysql-server ~]# innobackupex --copy-back /xtrabackup/2024-07-16_23-56-32/ # >>> 修改权限（属主和属组）： [root@mysql-server ~]# chown -R mysql.mysql /var/lib/mysql # >>> 启动MySQL服务 [root@mysql-server ~]# systemctl start mysqld
登陆mysql查看一下:
10.5.5 mysqldump逻辑备份 —- 推荐优先使用 mysqldump 是 MySQL 自带的逻辑备份工具。可以保证数据的一致性和服务的可用性。
如何保证数据一致?在备份的时候进行锁表会自动锁表。锁住之后在备份。
1 2 3 本身为客户端工具: 远程备份语法: # mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql 本地备份语法: # mysqldump -u用户名 -p密码 数据库名 > 备份文件.sql
1.常用备份选项 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 -A, --all-databases #备份所有库 -B, --databases #备份多个数据库 -F, --flush-logs #备份之前刷新binlog日志 --default-character-set #指定导出数据时采用何种字符集，如果数据表不是采用默认的latin1字符集的话，那么导出时必须指定该选项，否则再次导入数据后将产生乱码问题。 --no-data，-d #不导出任何数据，只导出数据库表结构。 --lock-tables #备份前，锁定所有数据库表 --single-transaction #保证数据的一致性和服务的可用性 -f, --force #即使在一个表导出期间得到一个SQL错误，继续。
注意
1 2 3 4 # 使用 mysqldump 备份数据库时避免锁表: 对一个正在运行的数据库进行备份请慎重！！ 如果一定要在服务运行期间备份，可以选择添加 --single-transaction选项， 类似执行： mysqldump --single-transaction -u root -p123456 dbname > mysql.sql
2.备份表 1 2 3 4 5 6 7 # >>> 语法: [root@mysql-server ~]# mkdir /home/back #创建备份目录 [root@mysql-server ~]# mysqldump -uroot -p'1' company employee5 > /home/back/company.employee5.bak # >>> 备份多个表： 语法格式: mysqldump -u root -p1 数据库 表一 表二 > /db1.t1_t2.bak [root@mysql-server ~]# mysqldump -uroot -p'1' company new_t1 new_t2 > /home/back/company.new_t1_t2.bak
3、备份库 1 2 3 4 5 6 7 8 9 10 11 # >>> 备份一个库：相当于将这个库里面的所有表全部备份。 语法: # mysqldump -u root -p1 数据库1 > /db1.bak [root@mysql-server ~]# mysqldump -uroot -p'1' company > /home/back/company.bak # >>> 备份多个库： 语法：mysqldump -u root -p1 -B 数据库1 数据库2 数据库3 > /db123.bak [root@mysql-server ~]# mysqldump -uroot -p'1' -B company testdb > /home/back/company_testdb.bak # >>> 备份所有的库： 语法：# mysqldump -u root -p1 -A > /alldb.bak [root@mysql-server ~]# mysqldump -uroot -p'1' -A > /home/back/allbase.bak
到目录下面查看一下：
4、恢复数据库和表 为保证数据一致性，应在恢复数据之前停止数据库对外的服务,停止binlog日志 因为binlog使用binlog日志恢复数据时也会产生binlog日志。
为实验效果先将刚才备份的数据库和表删除了。登陆数据库：
1 2 [root@mysql-server ~]# mysql -uroot -p1 mysql> show databases;
1 2 mysql> drop database company; mysql> exit;
5、恢复库 1 2 3 4 5 # >>> 登陆mysql创建一个库 mysql> create database company; # >>> 恢复数据 [root@mysql-server ~]# mysql -uroot -p'1' company < /home/back/company.bak
6、恢复表 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 # >>> 登陆到刚才恢复的库中将其中的一个表删除掉 mysql> show databases; mysql> use company mysql> show tables; +-------------------+ | Tables_in_company | +-------------------+ | employee5 | | new_t1 | | new_t2 | +-------------------+ mysql> drop table employee5; # >>> 开始恢复: mysql> set sql_log_bin=0; Query OK, 0 rows affected (0.00 sec) mysql> source /home/back/company.employee5.bak; -------加路径和备份的文件 # >>> 恢复方式二： # mysql -u root -p1 db1 < db1.t1.bak 库名 备份的文件路径
7、备份及恢复表结构 1 2 3 4 5 6 7 8 # >>> 1.备份表结构： 语法：mysqldump -uroot -p123456 -d 数据库 表名 > dump.sql [root@mysql-server ~]# mysqldump -uroot -p'1' -d company employee5 > /home/back/emp.bak # >>> 恢复表结构：登陆数据库创建一个库 mysql> create database t1; 语法：# mysql -u root -p1 -D 数据库 < db1.t1.bak [root@mysql-server ~]# mysql -uroot -p'1' -D t1 < /home/back/emp.bak
登陆数据查看：
8、数据的导入导出,没有表结构。 表的导出和导入只备份表内记录，不会备份表结构，需要通过mysqldump备份表结构，恢复时先恢复表结构，再导入数据。
1 mysql> show variables like "secure_file_priv" ; ----查询导入导出的目录。
1 2 3 4 5 6 7 8 9 10 修改安全文件目录： 1.创建一个目录：mkdir 路径目录 [root@mysql-server ~]# mkdir /sql 2.修改权限 [root@mysql-server ~]# chown mysql.mysql /sql 3.编辑配置文件： vim /etc/my.cnf 在[mysqld]里追加 secure_file_priv=/sql 4.重新启动mysql.
1 2 3 4 5 6 1.导出数据 登陆数据查看数据 mysql> show databases; mysql> use test mysql> show tables; mysql> select * from t3 into outfile '/sql/test.t3.bak' ;
1 2 3 4 5 2.数据的导入 先将原来表里面的数据清除掉，只保留表结构 mysql> delete from t3; mysql> load data infile '/sql/test.t3.bak' into table t3; 如果将数据导入别的表，需要创建这个表并创建相应的表结构。
5、通过binlog恢复 开启binlog日志：
1 2 3 [root@mysql-server ~]# vim /etc/my.cnf log-bin=/var/log/sql-bin/mylog server-id=1
创建目录并修改权限
1 2 3 [root@mysql-server ~]# mkdir /var/log/sql-bin [root@mysql-server ~]# chown mysql.mysql /var/log/sql-bin [root@mysql-server ~]# systemctl restart mysqld
1 2 3 mysql> flush logs; mysql> create table testdb.t3(id int);
根据位置恢复
找到要恢复的sql语句的起始位置、结束位置
1 [root@mysql-server sql-bin]# mysqlbinlog mylog.000002
测试
1 2 3 4 5 6 7 8 [root@mysql-server ~]# mysql -uroot -p'1' # >>> 将这个表删除 mysql> drop table testdb.t3; Query OK, 0 rows affected (0.01 sec) # >>> 恢复： [root@mysql-server ~]# cd /var/log/sql-bin/ [root@mysql-server sql-bin]# mysqlbinlog --start-position 219 --stop-position 321 mylog.000002 | mysql -uroot -p'1'
查看：
Day05 十一、mysql优化
选择合适的数据类型及字符集 ：使用合适的数据类型可以减少存储空间和提高查询速度。这个可不能小看，数据量到达一个量级，这个就能看出明显差异。避免使用SELECT * ：仅选择必要的列，减少数据传输量。合理使用JOIN、避免子查询 ：避免过多的 JOIN 操作，尽量减少数据集的大小。使用UNION代替OR、优化ORDER BY和GROUP BY ：确保 ORDER BY 和 GROUP BY 的列上有索引。避免使用%开头的LIKE查询 ：避免使用 % 开头的 LIKE 查询，因为不能使用索引。使用批量插入、优化INSERT操作 ：使用批量插入减少插入操作的开销。使用查询缓存 ：使用查询缓存，减少重复查询的开销。调整innodb_buffer_pool_size ：innodb_buffer_pool_size 是 InnoDB 存储引擎最重要的配置参数之一，用于指定 InnoDB 缓冲池的大小。缓冲池用于缓存数据页、索引页和 InnoDB 表的其它信息。合理设置这个参数对数据库性能有很大影响。调整query_cache_size ：query_cache_size 是用于指定查询缓存的大小。查询缓存可以缓存 SELECT 查询的结果，避免重复执行相同的查询，从而提高性能。调整thread_cache_size ：增大线程缓存大小，减少线程创建开销。调整table_open_cache ：增大表缓存大小，减少表打开的开销。调整tmp_table_size和max_heap_table_size ：增大临时表和堆表的最大大小，减少磁盘 I/O。调整innodb_flush_log_at_trx_commit ：根据需求调整日志刷新策略，权衡性能和数据安全性。调整innodb_log_file_size ：增大日志文件大小，减少日志文件切换的开销。调整innodb_log_buffer_size ：增大日志缓冲区大小，提高写入性能。调整innodb_io_capacity ：根据磁盘 I/O 性能调整 InnoDB I/O 容量。调整max_connections ：增大最大连接数，支持更多并发连接。调整sort_buffer_size ：增大排序缓冲区大小，提高排序操作的性能。调整read_buffer_size ：增大读缓冲区大小，提高顺序扫描性能。在常用查询条件和连接条件的列上建立索引 ：只要发现查询较慢，优先检查where条件后面，有没有被创建索引。避免在索引列上进行计算 避免重复索引 ：检查并删除重复的索引，减少维护开销。了解mysql底层的都知道，创建索引，就会增加一个页，重复索引无疑是给增加负担。更新频繁的列慎用索引 ：对于更新频繁的列，索引会增加写操作的开销，需要慎重使用。避免过多的列使用复合索引 ：复合索引的列数不要太多，列数过多会增加索引的维护开销，并且可能导致索引文件过大。对此可以拆分为较少复合索引和单个索引使用覆盖索引 ：如果查询的所有列都在索引中，那么可以避免回表，提高性能。避免使用SELECT DISTINCT ：在没有必要的情况下避免使用 SELECT DISTINCT，因为它会导致额外的排序操作，增加查询的开销。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 引擎： 查看引擎： mysql> show engines; mysql> SHOW VARIABLES LIKE '%storage_engine%' ; mysql> show create table t1; ---查看建表信息 临时指定引擎： mysql> create table innodb1(id int)engine=innodb; 修改默认引擎： /etc/my.cnf [mysqld] default-storage-engine=INNODB ----引擎 修改已经存在的表的引擎： mysql> alter table t2 engine=myisam;
1 2 3 4 5 6 7 8 9 优化： 调优思路： 1.数据库设计与规划--以后再修该很麻烦，估计数据量，使用什么存储引擎 2.数据的应用--怎样取数据，sql语句的优化 3.mysql服务优化--内存的使用，磁盘的使用 4.操作系统的优化--内核 5.升级硬件设备 6.创建索引提升查询速度
1 2 3 4 mysql常用命令： mysql> show warnings 查看最近一个sql语句产生的错误警告，看其他的需要看.err日志 mysql> show processlist 显示系统中正在运行的所有进程。 mysql> show errors 查看最近一个sql语句产生的错误信息
1 2 3 4 5 6 7 8 9 10 11 12 字符集设置 临时： mysql> create database db1 CHARACTER SET = utf8; mysql> create table t1(id int(10)) CHARACTER SET = utf8; 5.7/ 5.5版本设置： [mysqld] character_set_server = utf8 =========================================================================================== 慢查询： 查看是否设置成功： mysql> show variables like '%query%';
1 2 3 4 5 6 7 8 当连接数的数值过小会经常出现ERROR 1040: Too many connections错误。 这是是查询数据库当前设置的最大连接数 mysql> show variables like '%max_connections%' ; 强制限制mysql资源设置: # vim /etc/my.cnf max_connections = 1024 并发连接数，根据实际情况设置连接数。 connect_timeout= 5 单位秒 ----超时时间，默认30秒
1 2 3 4 5 6 innodb引擎： innodb-buffer-pool-size //缓存 InnoDB 数据和索引的内存缓冲区的大小 innodb-buffer-pool-size=# ----值 这个值设得越高,访问表中数据需要得磁盘 I/O 越少。在一个专用的数据库服务器上,你可以设置这个参数达机器物理内存大小的 80%。 # vim /etc/my.cnf innodb-buffer-pool-size=2G
十二、AB复制 12.1 什么是主从复制? 主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库；主数据库一般是准实时的业务数据库。
MySQL 主从复制（Master-Slave Replication）是一种数据复制技术，用于在多个数据库服务器之间的数据同步。在主从复制架构中，一个服务器被设置为主服务器（Master），充当数据源，其他服务器被设置为从服务器（Slave），用来复制主服务器的数据。
主从复制的作用
高可用性 ：通过将主数据库的数据复制到一个或多个从数据库，可以在主数据库故障时快速切换到从数据库 ，以实现系统的高可用性和容错能力，从而保证系统的持续可用性。 提高整体性能和吞吐量 ：通过将读请求分散到多个从服务器上进行处理，从而减轻了主服务器的负载压力，提高数据库系统的整体性能和吞吐量。主服务器主要负责写操作，而从服务器主要负责读操作，从而分担了主服务器的压力。数据备份和恢复 ：通过主从同步，可以将主服务器上的数据异步复制到从服务器上，从而实现数据备份和灾难恢复的需求。在应对意外数据丢失、灾难恢复或误操作时，可以使用从服务器作为数据的备份源来进行数据恢复。
总结：
解决数据可靠性的问题需要用到主从同步； 解决 MySQL 服务高可用要用到主从同步； 应对高并发的时候，还是要用到主从同步。
12.2 主从复制原理 1 2 3 4 5 6 7 8 9 10 11 12 原理： 实现整个主从复制，需要由slave服务器上的IO进程和Sql进程共同完成. 要实现主从复制，首先必须打开Master端的binary log（bin-log）功能，因为整个MySQL复制过程实际上就是Slave从Master端获取相应的二进制日志，然后再在自己slave端完全顺序的执行日志中所记录的各种操作。 =========================================== 1. 在主库上把数据更改（DDL DML DCL）记录到二进制日志（Binary Log）中。 2. 备库I/O线程将主库上的日志复制到自己的中继日志（Relay Log）中。 3. 备库SQL线程读取中继日志中的事件，将其重放到备库数据库之上。 =========================================== master 负责写 -----A slave relay-log -----B I/O 负责通信读取binlog日志 SQL 负责写数据
1 2 3 4 5 步骤一：主库db的更新事件(update、insert、delete)被写到binlog 步骤二：从库发起连接，连接到主库 步骤三：此时主库创建一个binlog dump thread线程，把binlog的内容发送到从库 步骤四：从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log. 步骤五：还会创建一个SQL线程，从relay log里面读取内容，将更新内容写入到slave的db.
12.3 主从同步的模式 MySQL 中主要有以下三种主从复制的模式，分别是全同步复制、异步复制和半同步复制。
全同步复制 ：在全同步复制模式中，主服务器将数据修改操作记录到二进制日志，并等待全部从服务器确认已接收到并应用了这些日志后才继续执行后续操作。
优点：数据一致性和可靠性，全部从服务器与主服务器保持同步。如果主服务器故障，应用到从服务器的数据不会丢失。
缺点：由于全同步复制需要等待从服务器的确认，因此相对于其他复制方式，延迟很大。严重影响主服务器的性能。
异步复制 ：MySQL 主从复制中最常见和默认的模式。在异步复制模式中，主服务器将数据修改操作记录到二进制日志（Binary Log）中，并将日志传输给从服务器。从服务器接收到二进制日志后，会异步地应用这些日志进行数据复制。
优点：它的优点是及时响应给使用者，主服务器不会受到从服务器的影响而等待确认，可以提高主服务器的性能。
缺点：由于是异步复制，可能存在数据传输的延迟，且从服务器上的复制过程是不可靠的。如果主服务器故障，尚未应用到从服务器的数据可能会丢失。
半同步复制 ：半同步复制是 MySQL 主从复制中的一种增强模式。在半同步复制模式中，主服务器将数据修改操作记录到二进制日志，并等待至少一个从服务器确认已接收到并应用了这些日志后才继续执行后续操作。
优点：可以提供更高的数据一致性和可靠性，确保至少一个从服务器与主服务器保持同步。如果主服务器故障，已经确认接收并应用到从服务器的数据不会丢失。
缺点：由于半同步复制需要等待从服务器的确认，因此相对于异步复制，会增加一定的延迟，可能会影响主服务器的性能。
如果对数据一致性和可靠性要求较高，可以考虑使用半同步复制或者全同步；如果对延迟和主服务器性能要求较高，可以继续使用异步复制，根据实际需求调整复制模式。
1 2 3 4 5 6 7 8 9 10 面试： 1.主从复制延迟大比较慢原因： 主服务器配置高，从服务器的配置低。 并发量大导致主服务器读的慢。从服务器写的慢 网络延迟比较高 从服务器的读写速度慢 2.从数据库的读的延迟问题了解吗？如何解决？ 解决方法： 半同步复制—解决数据丢失的问题 并行复制—-解决从库复制延迟的问题
12.4 M-S 架构GTID 基于事务ID复制 12.4.1 什么是GTID？ GTID 的全称是 Global Transaction Identifier，全局事务 ID，当一个事务提交时，就会生成一个 GTID，相当于事务的唯一标识。
12.4.2 GTID工作原理
master更新数据时，会在事务前产生GTID，一同记录到binlog日志中。
slave端的i/o 线程将变更的binlog，写入到本地的relay log中。
sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录。
如果有记录，说明该GTID的事务已经执行，slave会忽略。
如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog。
流程：
从库 B 指定主库 A，基于主备协议建立连接。
从库 B 把集合 y 发给主库 A。
主库 A 计算出集合 x 和集合 y 的差集，也就是集合 x 中存在，集合 y 中不存在的 GTID 集合。比如集合 x 是 1100，集合 y 是 190，那么这个差集就是 91~100。这里会判断集合 x 是不是包含有集合 y 的所有 GTID，如果不是则说明主库 A 删除了从库 B 需要的 binlog，主库 A 直接返回错误。
主库 A 从自己的 binlog 文件里面，找到第一个不在集合 y 中的事务 GTID，也就是找到了 91。
主库 A 从 GTID = 91 的事务开始，往后读 binlog 文件，按顺序取 binlog，然后发给 B。
从库 B 的 I/O 线程读取 binlog 文件生成 relay log，SQL 线程解析 relay log，然后执行 SQL 语句。
GTID 方案是通过主库来自动计算位点的，不需要人工去设置位点，对运维人员友好。
12.4.3 部署主从复制（一主一从）
MySQL-master和MySQL-slave所有主机都操作
1 2 3 4 5 6 7 8 9 10 11 # >>> 准备环境两台机器，关闭防火墙和selinux。两台机器环境必须一致。时间也得一致 $ systemctl disable --now firewalld $ vim /etc/hosts 192.168.174.210 mysql-master 192.168.174.211 mysql-slave [root@mysql-server ~]# scp /etc/hosts mysql-slave:/etc/hosts [root@mysql-server ~]# ping mysql-slave PING mysql-slave (192.168.174.211) 56(84) bytes of data. 64 bytes from mysql-slave (192.168.174.211): icmp_seq=1 ttl=64 time=0.524 ms
1 2 3 4 5 6 7 8 9 10 11 12 # >>> 两台机器安装mysql5.7 [root@mysql-master ~]# wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm [root@mysql-master ~]# yum -y install mysql-community-server 安装略... [root@mysql-master ~]# systemctl start mysqld [root@mysql-master ~]# systemctl enable mysqld [root@mysql-master ~]# netstat -lntp | grep 3306 tcp6 0 0 :::3306 :::* LISTEN 11669/mysqld [root@mysql-slave ~]# netstat -lntp | grep 3306 tcp6 0 0 :::3306 :::* LISTEN 11804/mysqld 配置并修改密码 略....
MySQL-master01配置操作
1 2 3 4 5 6 7 8 9 # >>> master操作： [root@mysql-master ~]# vim /etc/my.cnf #在[mysqld]下添加如下内容 server-id=1 #定义server id master必写 log-bin=mylog #开启binlog日志，master必写 gtid_mode=ON #开启gtid enforce_gtid_consistency=1 #强制gtid # >>> 重启MySQL实例 [root@mysql-master ~]# systemctl restart mysqld
参数解释：
server-id：指定了当前 MySQL 服务器的唯一标识符（Server ID）。每个 MySQL 服务器都必须有一个唯一的 Server ID。在主从复制中，Slave 服务器根据 Master 的 Server ID 来识别来自不同 Master 的数据变更。这个值在整个复制拓扑结构中必须是唯一的。gtid_mode：启用 GTID 模式。GTID 是用于在分布式数据库系统中唯一标识事务的全局事务标识符。它可以确保主从服务器之间数据一致性，简化配置和管理复制拓扑结构。enforce_gtid_consistency：开启强制 GTID 一致性。强制所有事务必须使用 GTID 标识，以确保主从服务器之间的数据一致性。如果某个事务没有 GTID 标识，MySQL 将不会执行该事务，并且会记录错误。
1 2 3 mysql> grant replication slave,reload,super on *.* to 'slave'@'%' identified by '1'; mysql> flush privileges;
注意：如果不成功删除以前的binlog日志
replication slave：拥有此权限可以查看从服务器，从主服务器读取二进制日志。super权限：允许用户使用修改全局变量的SET语句以及CHANGE MASTER语句reload权限：必须拥有reload权限，才可以执行flush [tables | logs | privileges]
MySQL-slave01服务器配置
1 2 3 4 5 6 7 8 9 10 # >>> 修改从服务器配置 [root@mysql-slave ~]# vim /etc/my.cnf #添加如下配置 server-id=2 gtid_mode=ON enforce_gtid_consistency=1 master-info-repository=TABLE relay-log-info-repository=TABLE # >>> 重启MySQL实例 [root@mysql-slave ~]# systemctl restart mysqld
参数解释：
master-info-repository=TABLEmysql.slave_master_info 表）。这种方式比存储在文件中更加可靠和可管理，尤其是在需要备份和恢复时。relay-log-info-repository=TABLEmysql.slave_relay_log_info 表）。与存储在文件中相比，这种方式可以更容易地进行管理和恢复。
1 2 3 4 5 6 7 8 9 [root@mysql-slave ~] mysql> \e change master to master_host='192.168.174.210' , master_user='slave' , master_password='1' , master_auto_position=1 ; Query OK, 0 rows affected, 2 warnings (0.02 sec)
注意：如果使用的克隆机，slave服务器需要先执行mysq> stop slave;，然后删除/var/lib/mysql/auto.cnfUUID文件，然后重启MySQL服务会胜场新的UUID，登录MySQL服务后在执行start slave;
MySQL-slave01主机配置
1 2 3 4 mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.174.210 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mylog.000001 Read_Master_Log_Pos: 597 Relay_Log_File: mysql-slave01-relay-bin.000002 Relay_Log_Pos: 802 Relay_Master_Log_File: mylog.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 597 Relay_Log_Space: 1017 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: c5552e0d-3ecc-11ef-a940-0050562892cb Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: c5552e0d-3ecc-11ef-a940-0050562892cb:1-2 Executed_Gtid_Set: c5552e0d-3ecc-11ef-a940-0050562892cb:1-2 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)
参数解释：
Slave_IO_State
值 : “Waiting for master to send event” 表示从服务器正在等待主服务器发送新的事件。
Master_Host
值 : 192.168.174.210 表示主服务器的 IP 地址。
Master_UserMaster_PortConnect_RetryMaster_Log_File
值 : mylog.000001 表示当前正在读取的二进制日志文件。
Read_Master_Log_PosRelay_Log_File
值 : mysql-slave01-relay-bin.000002 表示当前的中继日志文件。
Relay_Log_PosRelay_Master_Log_File
值 : mylog.000001 表示主服务器的二进制日志文件。
Slave_IO_RunningSlave_SQL_RunningLast_ErrnoLast_ErrorExec_Master_Log_PosRelay_Log_SpaceUntil_ConditionSeconds_Behind_MasterMaster_SSL_AllowedLast_IO_ErrnoLast_IO_ErrorMaster_Server_IdMaster_UUID
值 : c5552e0d-3ecc-11ef-a940-0050562892cb 表示主服务器的 UUID。
Master_Info_File
值 : mysql.slave_master_info 表示使用表存储主服务器信息。
Retrieved_Gtid_Set
值 : c5552e0d-3ecc-11ef-a940-0050562892cb:1-2 表示检索到的 GTID 集合。
Executed_Gtid_Set
值 : c5552e0d-3ecc-11ef-a940-0050562892cb:1-2 表示已执行的 GTID 集合。
Auto_PositionSQL_DelaySQL_Remaining_DelaySlave_SQL_Running_State
值 : “Slave has read all relay log; waiting for more updates” 表示从服务器已读取所有中继日志，正在等待更多更新。
Master_Retry_Count
MySQL-master01 写入数据测试
1 [root@mysql-mater01 mysql]
MySQL-slave01查看数据是否同步
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 [root@mysql-slave01 ~]# mysql -uroot -p'1' -e 'show databases;' mysql: [Warning] Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | emp01 | | mysql | | performance_schema | | study | | sys | | test10 | | test11 | | test12 | +--------------------+
主从同步完成。
注意:
在关闭和启动mysql服务的时候按顺序先启动master。可以测试，先将slave库停掉，再停止master库。启动先启动master库，再启动slave库，如果数据没发生改变，也就意味着binlog日志位置没有变化，主从理论上不会失效。
面试题
1 2 3 4 5 6 7 8 9 10 11 mysql主从，master宕机，如何进行切换？ 主机故障或者宕机： 1)在salve执行： mysql> stop slave； mysql> reset master； 2)查看是否只读模式：show variables like 'read_only'; 只读模式需要修改my.cnf文件，注释read-only=1并重启mysql服务。 或者不重启使用命令关闭只读，但下次重启后失效：set global read_only=off; 3)查看show slave status \G; 4)在程序中将原来主库IP地址改为现在的从库IP地址，测试应用连接是否正常
12.5 主从复制binlog日志方式
MySQL-master01和MySQL-slave01所有主机配置
1 2 3 4 5 6 7 # >>> 添加本地解析 vim /etc/hosts 192.168.246.135 mysql-master 192.168.246.136 mysql-slave # >>> 两台主机都安装MySQL服务 略
开始配置主服务
主服务器上，必须启用二进制日志记录并配置唯一的服务器ID。需要重启服务器。
1 2 3 4 5 6 [root@mysql-master01 ~]# vim /etc/my.cnf log-bin=mysql server-id=1 # >>> 重启MySQL [root@mysql-master01 ~]# systemctl restart mysqld
MySQL-master01创建主从同步用户
1 2 mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' identified by '1'; mysql> flush privileges;
MySQL-master01查看二进制日志偏移量
1 mysql> show master status\G
MySQL-slave01配置server-id
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 # >>> 修改MySQL主配置文件 [root@mysql-slave01 ~]# vim /etc/my.cnf server-id=2 # >>> 重启服务 [root@mysql-slave ~]# systemctl restart mysqld # >>> 添加主从同步用户 [root@mysql-slave ~]# mysql -uroot -p'1' mysql> \e CHANGE MASTER TO MASTER_HOST='mysql-master01', # MySQL-master01主机地址 MASTER_USER='repl', # 主动同步用户 MASTER_PASSWORD='1', # 密码 MASTER_LOG_FILE='mysql.000001', # 二进制文件名称 MASTER_LOG_POS=581; # 二进制日志文件偏移量 -> ; # >>> 启动从服务器 mysql> start slave; # >>> 查看主从同步状态 mysql> show slave status\G
MySQL-master01创建测试数据
1 2 mysql> create database testdb; Query OK, 1 row affected (0.10 sec)
故障排错
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 # > Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work 致命错误：由于master和slave具有相同的mysql服务器uuid，导致I/O线程不进行；这些uuid必须不同才能使复制工作。 问题提示主从使用了相同的server UUID，一个个的检查： 检查主从server_id 主库： mysql> show variables like 'server_id' ; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 1 | +---------------+-------+ 1 row in set (0.01 sec) 从库： mysql> show variables like 'server_id' ; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 2 | +---------------+-------+ 1 row in set (0.01 sec) server_id不一样，排除。 检查主从状态： 主库： 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) 从库： mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 306 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) File一样，排除。 最后检查发现他们的auto.cnf中的server-uuid是一样的。。。 [root@localhost ~]# vim /var/lib/mysql/auto.cnf [auto] server-uuid=4f37a731-9b79-11e8-8013-000c29f0700f 修改uuid并重启服务
12.6 基于Gtid日志方式实现互为主从
MySQL-master01和MySQL-slave01操作
1 2 3 4 5 6 7 [root@mysql-slave01 ~] $ vim /etc/hosts 192.168.174.210 mysql-master01 192.168.174.211 mysql-master02
MySQL-master01 配置文件修改
1 2 3 4 5 6 7 8 9 [root@mysql-master01 ~] server-id=1 log -bin=mylog gtid_mode=ON enforce_gtid_consistency=1 [root@mysql-master01 ~]
MySQL-master01 创建主从复制用户
1 2 3 4 5 mysql> grant replication slave,reload,super on *.* to 'slave'@'%' identified by '1'; mysql> flush privileges;
MySQL-master01备份数据（可选）
1 2 3 [root@mysql-master01 ~] [root@mysql-master01 ~]
MySQL-master02 配置文件修改
1 2 3 4 5 6 7 8 9 [root@mysql-master02 ~] server-id=2 log -bin=mylog gtid_mode=ON enforce_gtid_consistency=1 [root@mysql-master02 ~]
MySQL-master02配置同步用户
1 2 3 4 5 6 7 8 9 10 11 12 mysql> \e change master to master_host='192.168.174.210' , master_user='slave' , master_password='1' , master_auto_position=1 ; mysql> start slave; mysql> show slave status;
MySQL-master01 插入测试数据
1 2 3 mysql> create database test20; Query OK, 1 row affected (0.00 sec)
MySQL-master02 查看是否同步
1 2 3 4 5 6 7 8 9 10 11 12 13 mysql> show databases; + | Database | + | information_schema | | emp01 | | mysql | | performance_schema | | study | | sys | | test20 | + 7 rows in set (0.00 sec)
同步完成，一主一从搭建完成
MySQL-master02操作
1 2 3 4 5 mysql> grant replication slave,reload,super on *.* to 'slave'@'%' identified by '1'; mysql> flush privileges;
MySQL-master01操作
1 2 3 4 5 6 7 8 9 10 11 12 mysql> \e change master to master_host='192.168.174.211' , master_user='slave' , master_password='1' , master_auto_position=1 ; mysql> start slave; mysql> show slave status\G
MySQL-master02 插入测试数据
1 2 mysql> create database test30; Query OK, 1 row affected (0.00 sec)
MySQL-master01 查看是否同步
1 2 3 4 5 6 7 8 9 10 11 12 mysql> show databases; + | Database | + | information_schema | | emp01 | | mysql | | performance_schema | | study | | test30 | + 10 rows in set (0.00 sec)
MySQL-master01 插入测试数据
1 2 mysql> create database test31; Query OK, 1 row affected (0.00 sec)
MySQL-master02 查看是否同步数据
1 2 3 4 5 6 7 8 9 10 11 12 13 mysql> show databases; + | Database | + | information_schema | | emp01 | | mysql | | performance_schema | | study | | test30 | | test31 | + 12 rows in set (0.00 sec)
提示：如果MySQL-master01同步状态不佳，重启一下MySQL-master02的mysqld，再去master2上。启动和停止从属状态。
12.7 基于GTID实现双主双从
所有主机修改本地解析
1 2 3 4 5 $ vim /etc/hosts 192.168.174.210 mysql-master01 192.168.174.211 mysql-master02 192.168.174.212 mysql-slave01 192.168.174.213 mysql-slave02
MySQL-master01配置文件配置
略
MySQL-master01 创建同步用户
略
MySQL-master02 配置文件修改
略
MySQL-master02 创建同步用户
略
MySQL-master01和MySQL-master02实现互为主从
略
MySQL-slave01配置文件修改
1 2 3 4 5 6 7 8 9 [root@mysql-slave01 ~] server-id=3 gtid_mode=ON enforce_gtid_consistency=1 master-info-repository=TABLE relay-log-info-repository=TABLE [root@mysql-slave01 ~]
MySQL-slave02配置文件修改
1 2 3 4 5 6 7 8 9 [root@mysql-slave01 ~] server-id=4 gtid_mode=ON enforce_gtid_consistency=1 master-info-repository=TABLE relay-log-info-repository=TABLE [root@mysql-slave01 ~]
MySQL-master01 备份所有数据
1 mysqldump -p'1' --all-databases --single-transaction --master-data=2 --flush-logs > `date +%F`-mysql-all.sql
MySQL-master01将备份数据传输至MySQL-slave01和MySQL-slave02
1 2 3 [root@mysql-master01 ~] [root@mysql-master01 ~]
MySQL-slave导入数据
1 2 3 [root@mysql-slave01 ~] [root@mysql-slave02 ~]
MySQL-slave01 配置同步用户信息
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 mysql> change master to master_host='mysql-master01', master_user='slave', master_password='1', master_auto_position=1 for channel 'mysql-master01'; mysql> change master to master_host='mysql-master02', master_user='slave', master_password='1', master_auto_position=1 for channel 'mysql-master02'; mysql> start slave; Query OK, 0 rows affected (0.01 sec) mysql> show slave status\G
提示：FOR CHANNEL 'mysql-master01'：指定复制通道的名称。
'mysql-master1'：通道名称。MySQL 支持多源复制（即一个从服务器可以从多个主服务器复制数据），每个通道可以用于不同的主服务器。此选项用于多源复制环境中，标识具体的复制通道。
MySQL-slave02 配置同步用户信息
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 mysql> change master to master_host='mysql-master01', master_user='slave', master_password='1', master_auto_position=1 for channel 'mysql-master01'; mysql> change master to master_host='mysql-master02', master_user='slave', master_password='1', master_auto_position=1 for channel 'mysql-master02'; mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G
MySQL-mater01插入测试数据
1 2 mysql> create database test32; Query OK, 1 row affected (0.00 sec)
MySQL-master02 查看同步数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 mysql> show databases; + | Database | + | information_schema | | emp01 | | mysql | | performance_schema | | study | | sys | | test30 | | test31 | | test32 | + 13 rows in set (0.00 sec)
MySQL-slave01 查看同步数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 mysql> show databases; + | Database | + | information_schema | | emp01 | | mysql | | performance_schema | | study | | sys | | test30 | | test31 | | test32 | + 12 rows in set (0.00 sec)
MySQL-slave02 查看同步数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 mysql> show databases; + | Database | + | information_schema | | emp01 | | mysql | | performance_schema | | study | | sys | | test30 | | test31 | | test32 | + 12 rows in set (0.01 sec)
17.8 MySQL主从复制面试题 1 2 3 4 5 6 1. 如何查看MySQL主从之间的数据延迟多大？ 答： 使用`SHOW SLAVE STATUS\G`查看`Seconds_Behind_Master`延迟时间。 2. 如何解决主从数据没有一致的问题？ 答：首先，使用 SHOW SLAVE STATUS\G 命令检查从服务器的复制状态，关注以下关键字段： `Slave_IO_Running` 和 `Slave_SQL_Running：`都应为 Yes。`Last_IO_Error `和 `Last_SQL_Error`：应为空。`Seconds_Behind_Master：`应尽量接近于 0。如果主从数据延迟过大，推荐主机进行备份，从机导入数据。采用GTID模式（全局事务标识符）。可以使用 pt-table-checksum 和 pt-table-sync 工具查找主库和从库的不一致数据，并恢复。
十三、读写分离 13.1 什么是读写分离 在数据库集群架构中，让主库负责处理写入操作，而从库只负责处理select查询，让两者分工明确达到提高数据库整体读写性能。 当然，主数据库另外一个功能就是负责将数据变更同步到从库中，也就是写操作。
13.2 读写分离的好处
分摊服务器压力，提高机器的系统处理效率
在写入不变，大大分摊了读取，提高了系统性能。另外，当读取被分摊后，又间接提高了写入的性能。所以，总体性能提高了。
增加冗余，提高服务可用性，当一台数据库服务器宕机后可以调整另外一台从库以最快速度恢复服务
13.3 MySQL中间件 Mycat 是“一个开源的数据库系统”，但是由于真正的数据库需要存储引擎，而 Mycat 并没有存 储引擎，所以并不是完全意义的数据库系统。 那么 Mycat 是什么？Mycat 是数据库中间件，就是介于数据库与应用之间，进行数据处理与交互的中间服务；是实现对主从数据库的读写分离、读的负载均衡。
常见的数据库中间件:
13.4 Mycat部署 MyCAT 是使用 JAVA 语言进行编写开发，使用前需要先安装 JAVA 运行环境(JRE),由于 MyCAT 中使用了 JDK7 中的一些特性，所以要求必须在 JDK7 以上的版本上运行。
所有主机本地解析
1 2 3 4 [root@mycat ~]# vim /etc/hosts 192.168.174.210 mysql-master01 192.168.174.211 mysql-slave01 192.168.174.215 mycat
架构
这里是在mysql主从复制实现的基础上，利用mycat做读写分离，架构图如下
安装jdk
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 # >>> 解压安装包 [root@mycat ~]# tar xzf jdk-8u221-linux-x64.tar.gz -C /usr/local/ && cd /usr/local/ # >>> 更改目录名称 [root@mycat local]# mv jdk1.8.0_221/ java # >>> 设置全局环境变量 [root@mycat local]# vim /etc/profile.d/jdk.sh # !/bin/bash JAVA_HOME=/usr/local/java PATH=$JAVA_HOME/bin:$PATH export JAVA_HOME PATH # >>> 重新加载 [root@mycat local]# source /etc/profile.d/jdk.sh # >>> 测试服务 [root@mycat local]# java -version java version "1.8.0_211" Java(TM) SE Runtime Environment (build 1.8.0_211-b12) Java HotSpot(TM) 64-Bit Server VM (build 25.211-b12, mixed mode) [root@mycat local]# echo $PATH /usr/local/java/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
部署MyCat
1 2 3 4 5 # >>> 下载 [root@mycat ~]# wget http://dl.mycat.io/1.6.5/Mycat-server-1.6.5-release-20180122220033-linux.tar.gz # >>> 解压 [root@mycat ~]# tar xf Mycat-server-1.6.5-release-20180122220033-linux.tar.gz -C /usr/local/
配置mycat前端 server.xml
1 2 3 MyCAT 目前主要通过配置文件的方式来定义逻辑库和相关配置: [root@mycat conf]# vim /usr/local/mycat/conf/server.xml
该文件定义用户以及系统相关变量，如端口等。其中用户信息是前端应用程序连接 mycat 的用户信息
将此5行配置注释：Mycat提供两个账号，一个管理员，一个普通用户，将普通用户配置信息注释
配置MyCat超级管理员用户
name="root" : Mycat超级管理员用户名name="password">123456<：MyCat超级管理员密码。默认：123456name="schemas">maoxiansheng<：默认前端展示数据库 ,当用户访问时默认展示的库；但不代表后端数据库真正有这个数据库。
MyCat后端配置 schema.xml
1 2 3 4 5 [root@mycat conf] [root@mycat ~]
两主两从从配置文件如下：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 <?xml version="1.0"?> <!DOCTYPE mycat :schema SYSTEM "schema.dtd" > <mycat:schema xmlns:mycat ="http://io.mycat/" > <schema name ="maoxiansheng" checkSQLschema ="false" sqlMaxLimit ="100" dataNode ="dn1" > </schema > <dataNode name ="dn1" dataHost ="localhost1" database ="maoxiansheng" /> <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 ="mysql-master01" url ="192.168.174.210:3306" user ="mycatproxy" password ="1" > <readHost host ="mysql-slave01" url ="192.168.174.212:3306" user ="mycatproxy" password ="1" /> <readHost host ="mysql-slave02" url ="192.168.174.213:3306" user ="mycatproxy" password ="1" /> </writeHost > <writeHost host ="mysql-master02" url ="192.168.174.211:3306" user ="mycatproxy" password ="1" > <readHost host ="mysql-slave01" url ="192.168.174.212:3306" user ="mycatproxy" password ="1" /> <readHost host ="mysql-slave02" url ="192.168.174.213:3306" user ="mycatproxy" password ="1" /> </writeHost > </dataHost > </mycat:schema >
以下是配置文件中的每个部分的配置块儿
逻辑库和分表设置
1 2 3 4 5 <schema name="maoxiansheng" // 逻辑库名称,与server.xml的一致 checkSQLschema="false" // 不检查sql sqlMaxLimit="100" // 最大连接数 dataNode="dn1"> // 数据节点名称 </schema>
name="maoxiansheng"：定义了 MyCAT 中的逻辑库名为 maoxiansheng。
checkSQLschema="false"：禁用 SQL 语句中的 schema 校验。关闭后端MySQL库的校验，不需要。
sqlMaxLimit="100"：限制单次查询返回的最大行数为 100。俗称最大连接数。
dataNode="dn1"：指定该逻辑库的数据节点为 dn1。
数据节点
1 2 3 <dataNode name="dn1" // 此数据节点的名称 dataHost="localhost1" // 主机组虚拟的 database="maoxiansheng" /> // 真实的数据库名称
name="dn1"：定义数据节点的名称为 dn1。和前端的数据架构中的dataNode="dn1"进行关联。
dataHost="localhost1"：指定虚拟数据节点组为 localhost1。
database="maoxiansheng"：指定在 localhost1 主机上的实际数据库名为 maoxiansheng。
主机组
1 2 3 4 5 6 7 8 <dataHost name="localhost1" // 主机组 maxCon="1000" minCon="10" // 连接 balance="1" // 负载均衡 writeType="0" // 写模式配置 dbType="mysql" dbDriver="native" // 数据库配置 switchType="1" slaveThreshold="100"> <!--这里可以配置关于这个主机组的成员信息，和针对这些主机的健康检查语句--> </dataHost>
name="localhost1"：定义数据主机的名称为 localhost1。需要和dataNode中的dataHost名称保持一致，与之关联。
maxCon="1000"：指定该后端数据主机的最大连接数为 1000。
minCon="10"：指定该数据主机的最小连接数为 10。
balance="0"：负载均衡策略，0 表示不启用。
writeType="0"：写操作的类型，0 表示主库写入。
dbType="mysql"：数据库类型为 MySQL。
dbDriver="native"：使用 MySQL 原生驱动。
switchType="1"：主从切换策略（切换类型），1 表示自动切换。根据延迟时间自动切换。
slaveThreshold="100"：从库的负载阈值100毫秒，超过这个时间范围则自动切换。
balance 属性负载均衡类型,目前的取值有 3 种:
balance=”0”： 不开启读写分离机制,所有读操作都发送到当前可用的 writeHost 上。balance=”1” ：全部的 readHost 与 writeHost 参与 select 语句的负载均衡,简单的说,当双主双从模式(M1->S1,M2->S2,并且 M1 与 M2 互为主备),正常情况下,M2,S1,S2 都参与 select 语句的负载均衡。balance=”2”： 所有读操作都随机的在 writeHost、readhost 上分发。balance=”3”： 所有读请求随机的分发到 wiriterHost 对应的 readhost 执行,writerHost 不负担读压力, 注意 balance=3 只在 1.4 及其以后版本有,1.3 没有。
writeType 属性负载均衡类型
writeType=”0”： 所有写操作发送到配置的第一个 writeHost,第一个挂了切换到还生存的第二个writeHost,重新启动后已切换后的为准.writeType=”1”： 所有写操作都随机的发送到配置的 writeHost,版本1.5 以后废弃不推荐。
健康检查
1 <heartbeat>select user()</heartbeat> #对后端数据进行检测，执行一个sql语句，user()内部函数
读写配置
1 2 3 4 <writeHost host="hostM1" url="192.168.246.135:3306" user="mycat" password="ldq@12345!"> <!-- can have multi read hosts --> <readHost host="hostS2" url="192.168.246.136:3306" user="mycat" password="ldq@12345!" /> </writeHost>
<writeHost>
host="mysql-master01"：定义主库的名称为 mysql-master01。
url="192.168.174.210:3306"：主库的连接地址。
user="mycat"：用于连接主库的用户名。
password="1"：用于连接主库的密码。
<readHost>
host="mysql-slave01"：定义从库的名称为 mysql-slave01。
url="192.168.174.211:3306"：从库的连接地址。
user="mycat"：用于连接从库的用户名。
password="1"：用于连接从库的密码。
在真实的MySQL-master01 数据库上给用户授权
1 2 mysql> grant all on *.* to 'mycatproxy'@'%' identified by '1'; mysql> flush privileges;
在mycat的机器上面测试mycatproxy用户登录：
1 2 3 # >>> 安装mysql的客户端： [root@mycat ~]# yum install -y mysql [root@mycat ~]# mysql -umycatproxy -p'1' -h mysql-master
启动Mycat ：启动之前需要调整JVM
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 # >>> jvm调优 [root@mycat mycat]# cd conf/ [root@mycat conf]# vim wrapper.conf #在设置JVM哪里添加如下内容 wrapper.startup.timeout=300 //超时时间300秒 wrapper.ping.timeout=120 # >>> 启动mycat [root@mycat conf]# /usr/local/mycat/bin/mycat start #需要稍微等待一会 Starting Mycat-server... # >>> 查看mycat是否启动 [root@mycat ~]# jps 13377 WrapperSimpleApp 13431 Jps # >>> 查看mycat端口 [root@mycat ~]# netstat -lntp | grep java
MySQL-master01 创建测试数据库
1 2 mysql> create database maoxiansheng; Query OK, 1 row affected (0.00 sec)
测试mycat（mysql-master01节点测试）
1 2 # >>> 将master当做mycat的客户端 [root@mysql-master01 ~]# mysql -uroot -p123456 -P 8066 -h mycat
连接mycat输入测试语句
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 mysql> use maoxiansheng; Database changed mysql> show tables; Empty set (0.00 sec) mysql> create table t1(id int ); Query OK, 0 rows affected (0.01 sec) mysql> insert into t1(id) values(1); Query OK, 1 row affected (0.01 sec) mysql> select * from t1; + | id | + | 1 | + 1 row in set (0.00 sec)
MySQL-slave01 查看数据
1 2 3 4 5 6 7 mysql> select * from maoxiansheng.t1; + | id | + | 1 | + 1 row in set (0.00 sec)
测试成功
1 2 3 4 如果在show table报错： mysql> show tables; ERROR 3009 (HY000): java.lang.IllegalArgumentException: Invalid DataSource:0
1 2 3 4 5 6 7 8 解决方式： 登录master服务将mycat的登录修改为% mysql> update user set Host = '%' where User = 'mycat' and Host = 'localhost' ; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> flush privileges; 或者在授权用户mycat权限为*.*
