Day01
数据库的类型 关系型数据库：Oracle、Microsoft SQL Server、MySQL/MariaDB 优点：支持事务，使用表结构，易于维护，可用复杂查询，支持sql语句 缺点：读写性能差，因为表结构固定，所以存储结构不灵活，对于高并发的读写请求，磁盘I/O是很大的瓶颈。 非关系型数据库： MongoDB Memcached Redis 优点：存储格式灵活，读写速度快，具有高扩展性， 缺点：不支持sql语句，无事务处理；数据结构相对复杂，【复杂查询】方面稍欠。 数据库的引擎 InnoDB（存储引擎）：MySQL的默认存储引擎，支持ACID事务模型、行锁定和外键约束等特性，适用于高并发、高可用的OLTP（在线事务处理）场景。 MyISAM（存储引擎）：支持【全文索引和压缩】的存储引擎，不支持事务和行级锁，适合处理大量非事务性读写操作的应用。 Memory（内存型引擎）：将数据存储在内存中的存储引擎，适用于需要快速读写数据的应用，但因为数据存储在内存中，关闭MySQL服务器会导致数据丢失。 Archive（归档引擎）：用于存储历史和归档数据的存储引擎，支持高效的插入和查询数据，并允许通过查询进行数据压缩。 1）. 数据的存储结构不同.frm文件同样存储为表结构文件，.ibd文件存储的是数据和索引文件 .frm文件存储表定义 .MYD(MYD)存储数据文件。.MYI(MYIndex)存储索引文件。 由于MyISAM的索引和数据是分开存储的，因此索引查找的时候，MyISAM的叶子节点存储的是数据所在的地址，而不是数据而InnoDB叶子节点存储的是整个数据行所有的数据【行索引】 2）. 存储空间的消耗不同 MyISAM可被压缩，存储空间较小。支持三种不同的存储格式：静态表（默认，但是注意数据末尾不能有空格，会被去掉）、动态表、压缩表innoDB需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。InnoDB所在的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。 3）. 对事务的支持不同 MyISAM强调的是性能，每次查询具有原子性，其执行速度比Innodb类型更快，但是不提供事务支持。 InnoDB除了提供事务支持和外部键等高级数据库功能。还具有事务提交（commit）、回滚（rollback）和崩溃修复能力（crach recovery capabilities）等这些事务安全（transaction-safe ACID compliant）型表。 4）. 对锁的支持不同 如果只是执行大量的查询, MyISAM是更好的选择。MyISAM在增删的时候需要锁定整个表格，效率会低一些。 innoDB支持行级锁，删除插入的时候只需要锁定操作行就行。如果有大量的插入、修改删除操作，使用InnoDB性能能会更高 5）. 对外键的支持不同 MyISAM不支持外键，而InoDB支持外键 使用建议 1、如果需要支持事务，选择InnoDB，不需要事务则选择MyISAM。 2、如果大部分表操作都是查询，选择MyISAM，有写又有读选InnoDB。 3、如果系统崩溃导致数据难以恢复，且成本高，不要选择MyISAM。 事务的四个特性 原子性：事务是一个不可分割的工作单位，要么全部成功，要么全部失败。事务中的所有操作要么全部执行，要么全部不执行，不会出现部分执行的情况。 一致性：事务开始前和结束后，数据库系统都必须处于一致状态。事务执行前后，数据的完整性、约束条件等都必须保持一致性。 隔离性：事务之间是相互隔离的，每个事务都不能感知到其他事务的结果，保证多个事务的并发执行不会出现相互影响的情况。 持久性：事务执行成功后，对数据库所作的修改是永久保存的，即使系统崩溃也不会丢失。 SQL语句 SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统 数据定义语言（DDL）：用于定义数据库对象，例如表、视图、索引等。常用的DDL语句包括create、alter、drop等。 数据操作语言（DML）：用于对数据库中的数据进行操作，例如插入、修改、删除等。常用的DML语句包括select、insert、update、delete等。 数据查询语言（DQL）：用于查询数据库中的数据。常用的DQL语句为seclect语句。 数据控制语言（DCL）：用于定义数据库的安全性和访问权限。常用的DCL语句为grant、revoke等。 定义（create）、操作（delete、update、insert）、控制（修改权限）、查询（select） 数据库的数据类型 整数型int、浮点型float、文本字符串型char、varchar、日期时间型date、枚举型enum。 数据库的约束 主键约束（Primary key）：用于唯一标识表中的每个记录，保证每条记录的唯一性且不为空。 唯一约束（Unique）：用于确保表中指定的列或组合列的值都是唯一的，但可以为空。 非空约束（Not null）：用于确保表中的指定列不允许为空。 外键约束（Foreign key）：用于确保表与其他表之间的引用完整性，保证在进行关联查询等操作时不会出现脏数据。 自增（auto_increment）：标识该字段的值自动增长（整数类型，而且为主键）。 默认约束（default）：为该字段设置默认值。 数据库的索引 数据库索引好比是一本书前面的目录，能加快数据库的查询速度。 索引可以加快查询速度，但同时也会降低插入、更新和删除操作的速度，并占用更多的磁盘空间。因此，在设计数据库索引时应根据实际情况进行权衡和选择，避免过多或不必要的索引造成额外的性能损失和资源浪费。 普通索引（INDEX）：索引值可重复；普通索引常用于过滤数据。 唯一索引（UNIQUE）：索引值必须唯一，可以为null；唯一索引主要用于标识一列数据不允许重复的特性，相比主键索引不常用于检索的场景。 主键索引（PRIMARY KEY）：索引值必须唯一，不能为null，一个表只能有一个主键索引；主键索引是行的唯一标识，因而其主要用途是检索特定数据。 全文索引（FULL TEXT）：给每个字段创建索引。全文索引效率低，常用于文本中内容的检索。 数据库的备份与恢复 mysql备份类型 物理备份：物理备份是对数据库的整体备份，包含完整的二进制数据文件和日志文件等底层存储结构。一般情况下，物理备份速度较快，可实现快速还原和数据迁移，但也存在一些缺陷，如不能跨平台备份、恢复时间较长等。 逻辑备份：逻辑备份是对数据库中逻辑组件（如表、视图、存储过程等）的备份，其备份对象为 SQL 语句或脚本。相比于物理备份，逻辑备份不仅备份了数据，还包括了结构定义和约束等信息，因此逻辑备份能够更好地保障数据的完整性和一致性。但逻辑备份的恢复效率相对较低，可能需要较长的时间来执行恢复操作。 物理备份的方式 完全备份：完全备份是指对整个数据库进行备份的方式，包括数据文件、日志文件和配置文件等。完成一次完全备份后，即可获得一个独立、完整的备份，可以用于随时恢复数据库信息。但是，完全备份需要消耗大量磁盘空间，并且备份和还原时间比较长。 差异备份：差异备份是指在上一次完全备份的基础上，只备份自上次完全备份以来发生变化的数据部分。相比于完全备份，差异备份所需时间更短，占用空间也更少。恢复时需要先恢复完全备份，再逐一恢复差异备份，恢复过程相对较长。 增量备份：增量备份是指将自上一次备份后新增或修改的数据部分进行备份，相当于对差异备份的再次优化。增量备份需要存储每次备份与上次备份的差异，备份速度快，占用空间相对少。但是，恢复时需要依次恢复每个增量备份，恢复时间更久。 备份策略：使用物理备份，每周一做完整备份，周二至周日做增量备份。因为周一的时候服务器压力最小。 主从复制原理（主从复制方式：binlog日志、gtid） 主库开启 binlog 日志，授权用户和密码，从库指定主库ip、授权用户和密码、binlog日志文件、节点位置，执行start slave后会开启两个线程io线程和sql线程，主库开启dump线程。 当主库binlog日志内容更新后dump线程会告诉io线程，io线程读取binlog日志，并将内容记录到从库的中继日志，sql线程读取中继日志，并执行sql语句，从而达到数据一致的目的。 主从复制的作用 做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。 架构的扩展。当业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率,提高单个机器的I/O性能。 读写分离，减轻主服务器的负载压力，提高系统的并发处理能力。 主从复制延迟的原因和解决方法 从库硬件比主库差，导致复制延迟。包括磁盘I/O，CPU，内存等各方面因素都可能造成复制的延迟。 解决方法： 从库换成主库同等机型的配置 慢SQL语句太多，主库上执行了大事务，比如一个事务在主库上执行了 10 分钟，那这个事务很可能就会导致从库延迟 10 分钟。 解决方法：如果单个SQL的写入时间太长，可以修改后分多次写入。 主从复制的设计问题，例如主从复制单线程，如果主库写并发太大，来不及传送到从库，就会导致延迟。 解决方法：升级MySQL的版本（5.7 以上的版本），开启多线程并行复制功能。 主从库之间的网络延迟，主从库的网卡，网线，连接的交换机等网络设备都可能导致复制延迟。 解决方法：等网络恢复后再进行主从复制。 读写分离 在数据库集群架构中，让主库负责处理写入操作，而从库只负责处理select查询，让两者分工明确达到提高数据库整体读写性能。当然，主数据库另外一个功能就是负责将数据变更同步到从库中，也就是写操作。 Mycat是数据库中间件，就是介于数据库与应用之间，进行数据处理与交互的中间服务是实现对主从数据库的读写分离、读的负载均衡。 读写分离的好处 分摊服务器压力，提高机器的系统处理效率； 在写入不变，大大分摊了读取，提高了系统性能。另外，当读取被分摊后，又间接提高了写入的性能。所以，总体性能提高了； 增加冗余，提高服务可用性，当一台数据库服务器宕机后可以调整另外一台从库以最快速度恢复服务。 MySQL数据库的优化 安全方面：修改默认端口号，禁止root用户远程登录，对用户降权，以普通用户运行mysql； 性能方面：升级硬件，内存、磁盘、优化sql语句（开启慢查询）、设置索引； 参数优化：innodb的buffer参数调大，连接数调大、缓存的参数优化； 架构方面：读写分离，一主多从，高可用。
一、数据库介绍
1、什么是数据库
-
数据库就是一个存放计算机数据的仓库，这个仓库是按照一定的数据结构（数据结构是指数据的组织形式或数据之间的联系）来对数据进行组织和存储的，可以通过数据库提供的多种方法来管理其中的数据。
2、数据库的种类
-
最常用的数据库模式主要有两种，即关系型数据库和非关系型数据库。
3、生产环境常用数据库
- 生产环境主流的关系型数据库有 Oracle、Microsoft SQL Server、MySQL/MariaDB等。 - 生产环境主流的非关系型数据库有 MongoDB Memcached Redis
4、关系型数据库
1、关系型数据库介绍
-
数据的存储形式：
2、关系型数据库小结
- 关系型数据库在存储数据时实际就是采用的一张二维表（和 Word 和 Excell 里表格几乎一样）。 - 市场占有量较大的是 MySQL 和 Oracle 数据库，而互联网场景最常用的是 MySQL 数据库。 - 通过 SQL 结构化查询语言来存取、管理关系型数据库的数据。 - 关系型数据库在保持数据安全和数据一致性方面很强，遵循 ACID 理论 ACID指的的事务的4大特性
5、非关系型数据库
1、非关系数据库诞生的背景
-
，以高性能和使用便利为目的功能特异化的数据库产品。
-
NoSQL 是非关系型数据库的广义定义。它打破了长久以来关系型数据库与ACID理论大一统的局面。NoSQL数据存储不需要固定的表结构，通常也不存在连续操作。
非关系型数据库小结
- NoSQL 数据库不是否定关系型数据库，而是作为关系数据库的一个重要补充。 - NoSQL 数据库为了灵活及高性能、高并发而生，忽略影响高性能、高并发的功能。 - 在NoSQL 数据库领域，当今的最典型产品为 Redis（持久化缓存）、MongoDB、Memcached（纯内存）等。 - NoSQL 数据库没有标准的查询语言（SQL），通常使用数据接口或者查询API。
3、非关系型数据库种类
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 数据库
-
主要应用范围：传统大企业，大公司，政府，金融，证券等等。
-
版本升级：Oracle8i，Oracle9i，Oracle10g，Oracle11g，Oracle12c。
2、MySQL 数据库
-
体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，许多大中小型网站为了降低网站总体拥有成本而选择了 MySQL 作为网站数据库，甚至国内知名的淘宝网也选择弃用 Oracle 而更换为更开放的 MySQL。
-
MySQL 数据库主要应用范围：互联网领域，大中小型网站，游戏公司，电商平台等等。
3、MariaDB 数据库
-
MariaDB 数据库管理系统是 MySQL 数据库的一个分支，主要由开源社区维护。
4、SQL Server 数据库
-
SQL Server 的功能比较全面，效率高，可以作为中型企业或单位的数据库平台。
-
SQL Server 可以与 Windows 操作系统紧密集成，不论是应用程序开发速度还是系统事务处理运行速度，都能得到较大的 提升。SQL Server 的缺点是只能在【 Windows】 系统下运行。
-
主要应用范围：部分企业电商（央视购物），使用windows服务器平台的企业。
7、常用非关系型数据库管理系统
1、Memcached（Key-Value）
-
Memcached 是一个开源的、高性能的、具有分布式内存对象的缓存系统。通过它可以减轻数据库负载，加速动态的 Web 应用，
-
缓存一般用来保存一些经常被存取的对象或数据（例如，浏览器会把经常访问的网页缓存起来一样），通过缓存来存取对象或数据要比在磁盘上存取快很多，前者是内存，后 者是磁盘。Memcached 是一种纯内存缓存系统，把经常存取的对象或数据缓存在 Memcached 的内存中，这些被缓存的数据被程序通过API的方式被存取，Memcached里面的数据就像一张巨大的 HASH 表，数据以 Key-Value 对的方式存在。Memcached 通过缓存经常被存取的对象或数据，从而减轻频繁读取数据库的压力，提高网站的响应速度.官方：memcached - a distributed memory object caching system
-
由于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 客户端，使用很方便。官方：https://redis.io/docs/
-
Redis 特点：
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是一个关系型数据库管理系统，
RDBMS即关系数据库管理系统(Relational Database Management System)
MySQL 类型
#### 1、MySQL Community Server - MySQL Community Server是社区版本，开源免费，但不提供官方技术支持。MySQL Community Server也是我们通常用的MySQL的版本。根据不同的操作系统平台细分为多个版本。 #### 2、MySQL Enterprise Edition - MySQL Enterprise Edition企业版本，需付费，可以试用30天。 #### 3、MySQL Cluster - MySQL Cluster集群版，开源免费。可将几个MySQL Server封装成一个Server。MySQL Cluster CGE 高级集群版，需付费。
MySQL 安装方式
1、yum 安装
优点：操作简单易用。不用单独下载，服务器可以联网且yum源没有问题即可(可以选择国内的163/阿里源)
2、编译安装
- 5.1.X 及之前的版本是通过下载tar包以后解压后进入软件包解压路径。然后./configure、make、make install - 5.4.X 到 5.7.X 通过下载tar包以后解压后进入软件包解压路径。然后 cmake、make、make install(cmake需要提前安装) 优点：可以定制功能特性。
3、二进制安装
官方下载二进制包，解压初始化即可直接使用不用安装
4、rpm 安装
- 需要提前下载 rpm 软件包上传到服务器系统本地 - 使用 rpm 或者 yum 命令直接安装
MySQL 版本号
以 MySQL 5.7.27 这个版本的版本号为例说明每个数字含义。 - 第一个数字(5)主版本号：文件格式改动时，将作为新的版本发布； - 第二个数字(7)发行版本号：新增特性或者改动不兼容时，发行版本号需要更改； - 第三个数字(27)发行序列号：主要是小的改动，如bug的修复、函数添加或更改、配置参数的更改等。
关系型数据库与非关系型数据库的区别:
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的官方网址： MySQL ，MySQL的社区版本下载地址为： MySQL :: Download MySQL Community Server ，在写本文时，当前的MySQL最新版本是：8.0 。
什么是sql？
SQL代表结构化查询语言(Structured Query Language)。SQL是用于访问数据库的标准化语言。
SQL包含三个部分：
数据定义语言包含定义数据库及其对象的语句，例如表，视图，触发器，存储过程等。 数据操作语言包含允许您更新和查询数据的语句。 数据控制语言允许授予用户权限访问数据库中特定数据的权限。
mysql安装
关闭防火墙和selinux
1、编译安装mysql5.7【13号机】
1、清理安装环境：
# yum erase mariadb mariadb-server mariadb-libs mariadb-devel -y # userdel -r mysql # rm -rvchf /etc/my # rm -rvf /var/lib/mysql
2、创建mysql用户
[root@mysql-server ~]# useradd -r mysql -M -s /bin/false
3、从官网下载tar包
[root@mysql-server ~]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-boost-5.7.27.tar.gz ----------------------------------- 已安装安装包mysql-boost-5.7.27.tar.gz
4、安装编译工具
# yum -y install ncurses ncurses-devel openssl-devel bison gcc gcc-c++ make cmake
5、创建mysql目录
[root@mysql-server ~]# mkdir -p /usr/local/mysql/{data,log}
6、解压
[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、编译安装
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 默认的存储引擎，支持外键
[root@mysql-server mysql-5.7.2j7]# make -j4 && make install 如果中途安装进程被强制杀死，说明交换空间不足，扩展交换分区； 如果安装出错，想重新安装： 不用重新解压，只需要删除安装目录中的缓存文件CMakeCache.txt
需要很长时间！大约半小时
8、初始化
[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 ---初始化完成之后，一定要记住提示最后的密码用于登陆或者修改密码【在终端上显示密码；yum安装从log日志中查找。】 d.aJj9oj8wpj
初始化,只需要初始化一次
[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
[root@mysql-server ~]# cd /usr/local/mysql [root@mysql-server mysql]# ./bin/mysqld_safe --user=mysql &
10、登录mysql
[root@mysql-server mysql]# /usr/local/mysql/bin/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 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、修改密码
内部： set password=password("1");
原提示的随机 密码'd.aJj9oj8wpj' 外部 [root@mysql-server mysql]# /usr/local/mysql/bin/mysqladmin -u root -p'd.aJj9oj8wpj' 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、添加环境变量
软链接，进行设置： ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql ctrl+d 退出当前命令 [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 -V 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服务的管理工具：
[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 -tanlpu 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
数据库编译安装完成.
删除编译的mysql
rm -rvf /etc/my.cf /usr/local/mysql
如果恢复的话，make install重新第八步初始化
2、yum安装方式【】
mkdir -p /usr/local/mysql/{data,log}
关闭防火墙和selinux
mysql的官方网站：www.mysql.com
拉到底
下载 [root@mysql-server ~]# wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm 或者下载到本地上传到服务器
2.安装mysql的yum仓库
[root@mysql-server ~]# rpm -ivh mysql80-community-release-el7-3.noarch.rpm [root@mysql-server ~]# yum -y install yum-utils #安装yum工具包
3、配置yum源
[root@mysql-server ~]# vim /etc/yum.repos.d/mysql-community.repo #修改如下
1表示开启，0表示关闭
或者
# yum-config-manager --enable mysql57-community 将禁用的yum源库启用 # yum-config-manager --disable mysql80-community 将启用的yum源库禁用
4、安装数据库
d守护进程类似服务httpd mysqld vsftpd 失败的软件包是：mysql-community-common-5.7.43-1.el7.x86_64 GPG 密钥配置为：file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql 输入 # rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022 启动服务 root@mysql-server ~]# yum install -y mysql-community-server [root@mysql-server ~]# systemctl start mysqld ; systemctl enable mysqld -------------------------------------
5、查找密码
密码保存在日志文件中 [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 TuExMS?eh2e+
6、修改密码&y>M!4+*D7G)
两种方式：
第一种：
[root@mysql-server ~]# mysql -uroot -p'>+0fetSDQF%m' #登录
大小写特殊字符数字密码规则
mysql> alter user 'root'@'localhost' identified by 'Tang@123';
第二种：
# mysqladmin -u root -p'>+0fetSDQF%m' password '1'
mysqladmin -u root -p'旧密码' password '新密码'
注：修改密码必须大小写数字和特殊符号都有。
第三种【不加分号不行】
set password=password("1");
扩展
通过配置文件设置密码强度s
[root@mysql-server ~]# vim /etc/my.cnf #在最后添加如下内容 validate_password=off [root@mysql-server ~]# systemctl restart mysqld #重启mysql生效
编译安装： # 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 #用于存放字符集、语言等信息 a-9q>%5h6+7M
3缓存安装【号机】
1 从文件夹中缓存源 rz 6个rpm到root下， 【上传rpm缓存包到root目录下】
-
yum -y install *.rpm 【安装rpm包】
-
systemctl start mysqld 【启动mysqld服务】
-
cat /var/log/mysqld.log |grep pass 【查看原始密码】
-
mysql -p'原始密码' 【登陆并修改原始密码】
set password=password("Tang@123");
-
vim /etc/my.cnf 【在最后添加如下内容，跳过密码验证 】 validate_password=off
character-set-server=utf8
systemctl restart mysqld 【修改配置文件需要重启】
mysql -p1 【修改密码后登陆验证】
4mysql支持输入中文：：
yum安装更改支持中文【编译安装直接支持中文】 创建库名同时指定中文格式； character-set-server=utf8 ________________________________ 自动补全 rz mysql.sh 然后bash执行该插件 echo 'export PATH=/usr/local/python/bin:$PATH' >> /etc/profile source /etc/profile 输入 mycli -p1 自动补全表名，库名 列名； vi /etc/my.cnf [mysql] auto-rehash ———————————————————————————————————————————— 出现 sql_mode=only_full_group_by 错误 需修改 配置文件/ etc/my.cnf [mysqld] 段添加 sql模式，重启 mysqld sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
增上改查执行顺序
1-1查询 Select [字段别名]/* from 数据源 [where条件子句] [group by子句] [having子句] [order by子句] [limit 子句]; 1-2添加 Insert into 表名 [字段1,字段2,......] values (值1,值2,......); /*插入单条记录*/ Insert into 表名 [字段1,字段2,......] values (值1,值2,......), (值1,值2,......); /*插入多条记录*/ 3.删除 DELETE [IGNORE] FROM 表名 [WHERE 条件1, 条件2, ...] [ORDER BY ...] [LIMIT ...]; 子句执行顺序：FROM -> WHERE -> ORDER BY -> LIMIT -> DELETE 4.更新 UPDATE [IGNORE] 表名 SET 字段1=值1, 字段2=值2, ...... [WHERE 条件1 ......] [ORDER BY ......] [LIMIT ......]; UPDATE 表1 JOIN 表2 ON 条件 SET 字段1=值1, 字段2=值2, ......; 引申出另一种写法 UPDATE 表1 JOIN 表2 SET 字段1=值1, 字段2=值2, ...... WHERE 条件;
去重
1.distinct关键字只能在select子句中使用一次
SELECT DISTINCT job, DISTINCT ename FROM t_emp;
写2个distinct直接报错
2.distinct关键字只能写在select子句的【第一个字段前面】，否则报错，若有多个字段，则只有多个字段的值都相同的情况才会被认为是重复记录，distinct才会生效
三、数据库基本操作
一、数据库存储引擎（扩展）
数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的【存储机制、索引、锁定】等功能，使用不同的存储引擎，还可以 获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。
MySQL存储引擎介绍 文件系统： 操作系统组织和存取数据的一种机制。文件系统是一种软件。 类型：ext2 3 4 ，xfs 不管使用什么文件系统，数据内容不会变化 不同的是，存储空间、大小、速度。 MySQL引擎： 可以理解为，MySQL的“文件系统”，只不过功能更加强大。 MySQL引擎功能： 除了可以提供基本的存取功能，还有更多功能【事务功能、锁定、备份和恢复、优化以及特殊】功能。
1、InnoDB存储引擎:默认引擎，最常用的。 InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键;InnoDB是默认的MySQL引擎 InnoDB特点： 原子性；一致性；隔离性；持续性 支持事务处理，支持外键，支持崩溃修复和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。 2、MyISAM存储引擎：（了解） MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事务。 MyISAM特点： 插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能【实现处理高效率】。如果应用的完整性、并发性要求比较低，也可以使用。 12306查询 只生成一条数据这种适合 3、MEMORY内存型引擎(了解)【快，成本高，不安全】 MEMORY存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问 MEMORY特点： 所有的数据都在内存中，数据的【处理速度快，但是安全性不高。】如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMOEY。它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表。 4、Archive(归档引擎)高并发一般日志，一个小格子kb单位，查询比较慢服务压力比较大
如何选择引擎：如果要提供提交、回滚、并要求实现并发控制，InnoDB是一个好的选择；如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率；如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎；MySQL中使用该引擎作为临时表，存放查询的中间结果；如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive。
使用哪一种引擎需要灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能。
存储引擎查看：
mysql> show engines;
修改搜索引擎
ALTER TABLE 表名 ENGINE=引擎;
看你的mysql当前默认的存储引擎:
mysql> show variables like '%storage_engine%';
如何查看Mysql服务器上的版本
mysql> select version();
创建时候指定引擎
mysql> create table t1(id int,manager char(10)) engine =innodb; --------------------- 查看表/库复数 show tables/dababases; 删除库/表 drop table/databse 名字
了解:
1.什么是外键：外键的主要作用是保持数据的一致性、完整性。 2.什么是索引:索引相当于书中的目录，可以提高数据检索的效率，降低数据库的IO的压力。MySQL在300万条记录左右性能开始逐渐下降，虽然官方文档说500~800w记录，所以大数据量建立索引是非常有必要的 3.什么是行锁定与锁表：可以将一张表锁定和可以单独锁一行的记录。为了防止你在操作的同时也有别人在操作。 4.什么是事务：事务是由一步或几步数据库的操作。这系列操作要么全部执行，要么全部放弃执行。
事务控制语言(TCL)
事务控制语言 （Transation Control Language） 有时可能需要使用 DML 进行批量数据的删除，修改，增加。比如，在一个员工系统中，想删除一个人的信息。除了删除这个人的基本信息外，还应该删除与此人有关的其他信息，如邮箱，地址等等。那么从开始执行到结束，就会构成一个事务。对于事务，要保证事务的完整性。要么成功，要么撤回。
事务要符合四个条件(ACID)：
事务具有四个特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持续性（Durability）。这四个特性也简称ACID性。
（1）原子性：事务是应用中最小的执行单位，就如原子是自然界最小颗粒，事务要么成功，要么撤回.具有不可再分的特征一样。事务是应用中不可再分的最小执行体。（最小了，不可再分了） （2）一致性：事务执行的结果，必须使数据库从一个一致性状态，变到另一个一致性状态比如：当数据库中只包含事务成功提交的结果时，数据库处于一致性状态。一致性是通过原子性来保证的。 （3）隔离性：当涉及到多用户操作同一张表时，数据库会为每一个用户开启一个事务。各个事务的执行互不干扰，任意一个事务的内部操作对其他并发的事务都是隔离的。也就是说：并发执行的事务之间不能看到对方的中间状态，并发执行的事务之间不相互影响。（说白了，就是你做你的，我做我的！） （4）持续性：持续性也称为持久性指事务一旦提交对数据所做的任何改变，都要记录到永久存储器中，通常是保存进物理数据库。即使数据库崩溃了，我们也要保证事务的完整性。
二、sql语句
创建库：creat database 库名； 查看库 ：show databases;
进入库：use 库名 【唯一一个命令可以不加分号执行】
查看当前所在库：select database (); 删除库：drop database 库名;
创建表：create table 表名; 查看表：show tables; 删除表：drop table 表名;
查看表结构：desc 表名; 【查看字段】
查看表内容：select * from 表名;
查看表状态：show table status like '表名'\G
注意查看表状态语句结尾不能加分号；
修改表名: rename table 旧表名 to 新表名; 或者
alter table 旧表名 rename 新表名;
修改表名时必须加table,不是直接更改表名；
字段起别名；
select id as '身份证号' ,name as '姓名' from t4;
添加主键：alter table 表名 add primary key (字段);；
删除主键：alter table 表名 drop primary key;
添加自增：alter table 表名 change 字段 字段 字段类型;
删除自增：alter table 表名 change 字段 字段 字段类型;
重置自增id：alter table 表名 auto_increment=1
设置唯一约束：unique; 指定字符集：default charset=utf8;
设置默认值：alter table 表名 alter 字段 set default 0;
删除默认值：alter table 表名 alter 字段 drop default;
删除唯一索引 ALTER TABLE student DROP INDEX idIndex
alter table 表名 add 添加的字段(和类型) after name; -------把添加的字段放到name后面
插入字段：alter table 表名 add 字段 字段类型; 多个用,隔开
删除字段：alter table 表名 drop 字段;
删除表/数据库drop table/database 表名/库名
【AUTO_INCREMENT 标识该字段的值自动增长（整数类型，而且为主键）】
delete 之后，插入数据从上一次主键自增加1开始， truncate则是从1开始
delete 删除， 是一行一行的删除， truncate：全选删除 truncate删除的速度是高于delete的
删除主键 ALTER TABLE student DROP PRIMARY KEY;
修改字段和类型：alter table 表名 change 旧字段 新字段 类型;
或者只修改类型 alter table 表名 modify 字段 类型；
追加修改字段类型 ALTER TABLE user ALTER number [SET ]DEFAULT 0;
插入数据：
insert into 表名(字段) values(数据); 多个数据用,隔开
例如 inset into t1 (name) vlues('zs')
insert into 表名 set 字段=数据;
例如 insert into t1 set name ='zs'
添加多个用户；
insert into zhoukou.QF(id,name)values(1,'tony1'),(2,'tony2');
如果字段较少，正常顺序输出，可以省略；
insert into QF values (3,'tony'),(4,'feixiang'),(5,'flectrag');
修改数据：update 表名 set 修改的字段=数据 where 给谁修改;
删除单条或多条数据：delete from 表名 where 字段=数据;
删除整张表 delete from 表名;
单表查询：select 字段 from 表名; 设置别名：旧名 as 新名
多表查询：左外连接：表A left [outer] join 表B on 关联条件，表A是主表，表B是从表
右外连接：表A right [outer] join 表B on 关联条件，表B是主表，表A是从表
排序查询：select 字段 from 表名 order by 字段; 默认从小到大排序 降序添加desc
查看表的n行：select * from 表名 limit 5; #只显示前5行 0是第一行 0,1 从第一行开始查看1行
分组查询：select count(字段),字段 from 表名 group by 字段;
组去重：select group_concat(字段),字段 from 表名 group by 字段;
统计字段数量：count() max() 最大值 min()最小值 avg()平均值 now() 现在的时间 sum() 计算和
去重：select distinct 字段 from 表名;
表复制：create table 新表 select * from 旧表; 复制表结构＋记录 （key不会复制: 主键、外键和索引）
mysql -u用户名 -p密码 -h ip地址 -P端口号 -e接SQL语句，可以写多条用；隔开 -D指定登录的数据库
flush privileges 更新授权表
create user tom@'%' identified by '1'; #创建用户为tom，并设置密码。
grant all on *.* to '用户名'@’localhost’; 授权用户权限 %允许所有主机远程登陆包括localhost。
grant 权限列表 on 库名.表名 to '用户名'@'客户端主机' identified by '1'；创建用户并授权
show grants\G 查看自己的权限
show grants for tom@'localhost'\G 查看别人的权限
revoke select,delete on *.* from jack@'%'; #回收指定权限
revoke all privileges on *.* from jack@'%'; #回收所有权限
set passsword='new_password'; 修改密码
set passsword for user@'localhost'='new_password' 修改指定用户密码
drop user 'user'@'localhost'; 删除用户
delete from mysql.user where user='tom' and host='localhost'; 删除指定用户
解决binlog日志不记录insert语句
登录mysql后，设置binlog的记录格式：
set binlog_format=statement;
然后，最好在my.cnf中添加：
binlog_format=statement
修改完配置文件之后重启mysql服务
----------------------
在/不在..范围 in VS not in 【都不要is，is只和 null搭配】
select qfeduid,name,job,sal from qfedu where deptid in(10,20);
select qfeduid,name,job,sal from qfedu where deptid not in(10,20);
名字含有字母
select * from qfedu where name like '%s%' and name like '%a%' ;
默认升序asc,降序desc
先执行from子句，再执行where子句，然后group by子句，再次having子句，之后select子句，最后order by子句
————————————————————————————————————————————
把10部门中，工龄达到20年的员工，底薪增加200元
UPDATE t_emp
SET sal=sal+200
WHERE deptno=10 AND DATEDIFF(NOW(),hiredate)/365 >= 20
----
ifnull
MySQL IFNULL函数是MySQL控制流函数之一，它接受两个参数，如果不是NULL，则返回第一个参数。 否则，IFNULL函数返回第二个参数。 两个参数可以是文字值或表达式。 以下说明了IFNULL函数的语法： IFNULL(expression_1,expression_2); 场景比如代理商： 省代，市级代理； 电话 ：办公电话，家庭电话
工龄年数
UPDATE t_emp SET sal=sal+200 WHERE deptno=10 AND DATEDIFF(NOW(),hiredate)/365 >= 20u'p
sh mysql -u用户名 -p密码 -h ip地址 -P端口号 -e接SQL语句，可以写多条用；隔开 -D指定登录的数据库 flush privileges 更新授权表 create user tom@'%' identified by '1'; #创建用户为tom，并设置密码。 grant all on *.* to '用户名'@’localhost’; 授权用户权限 %允许所有主机远程登陆包括localhost。 grant 权限列表 on 库名.表名 to '用户名'@'客户端主机' identified by '1'；创建用户并授权 show grants\G 查看自己的权限 show grants for tom@'localhost'\G 查看别人的权限 revoke select,delete on *.* from jack@'%'; #回收指定权限 revoke all privileges on *.* from jack@'%'; #回收所有权限 set passsword='new_password'; 修改密码 set passsword for user@'localhost'='new_password' 修改指定用户密码 drop user 'user'@'localhost'; 删除用户 delete from mysql.user where user='tom' and host='localhost'; 删除指定用户 解决binlog日志不记录insert语句 登录mysql后，设置binlog的记录格式： set binlog_format=statement; 然后，最好在my.cnf中添加： binlog_format=statement 修改完配置文件之后重启mysql服务
增删改查
SQL（Structured Query Language 即结构化查询语言） SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统，SQL语言由IBM开发。 DDL语句 数据库定义语言：数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER DML语句 数据库操纵语言（对记录的操作）： 插入数据INSERT、删除数据DELETE、更新数据UPDATE DCL语句 数据库控制语言（和权限有关）： 例如控制用户的访问权限GRANT、REVOKE DQL语句 数据库查询语言：查询数据SELECT
程序连接数据库的文件:
A. ODBC --------- PHP<.php> B. JDBC ----------- JAVA <.jsp>
==========================================================================
库----相当于一个目录，存放数据的
库里面存放的表， 相当于是文件。 每一行叫做记录，除第一行。 每一列叫一个字段。列上面的第一个叫字段名称。 创建一个库：---->查看库--->进入这个库----->创建表----->查看表：查看表名，表的字段（表结构），表里面的内容（表记录），查看表的状态----->修改表：添加字段，删除字段，修改字段----->修改记录（更新记录），添加记录，删除记录。各种查询，删除表，删除库。
1.创建库 mysql> create database 库名; 2.查看数据库 mysql> show databases; 3.进入数据库 mysql> use 库名 4.查看当前所在的库 mysql> select database(); 5.查看当前库下所有的表格 mysql> show tables;
创建表
语法:
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.整型
作用：用于存储用户的年龄、游戏的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查询结果集中的显示宽度，并不影响实际的取值范围，没有影响到显示的宽度
#整形的宽度仅为显示宽度，不是限制。因此建议整形无须指定宽度。
1【超出范围，内存泄漏】数字在固定数值范围内；
2 id tinyint unsigned 无负值插入，空间大小由原来的【-128--127】变化为【0，255】；
3属主类型只要不超过int的最大范围都能插入，对字节位数限制大小不影响；
mysql> insert into test1(age) values(2147483648); //超出范围，报错
ERROR 1264 (22003): Out of range value for column 'age' at row 1
=====================================================
2.浮点数类型 FLOAT DOUBLE
作用：用于存储用户的身高、体重、薪水等
float(5,3) #一共5位，小数占3位.做了限制【即整数最多两位】
mysql> create table test4(float_test float(5,2)); #案例 宽度不算小数点
+
【小数包含整数；小数包含整数部分和小数部分】
定点数类型（decimal,用于货币等高精度，小数部分以字符串形式进行存储，相对精确，也会四舍五入)float，小数部分输入可能会四舍五入；不准确；
3.字符串类型 【查询速度和定长】
作用：用于存储用户的姓名、爱好、电话，邮箱地址，发布的文章等
字符类型 char varchar --存字符串
- char表示定长字符串，长度是固定的；如果插入数据的长度小于char的固定长度时，则用空格填充；因为长度固定，所以存取速度要比varchar快很多，甚至能快50%，但正因为其长度固定，所以会占据多余的空间。
- varchar表示可变长字符串，长度是可变的；插入的数据是多长，就按照多长来存储；varchar在存取方面与char相反，它存取慢，因为长度不固定，但正因如此，不占据多余的空间。
- 结合性能角度（char更快），节省磁盘空间角度（varchar更小），具体情况还需具体来设计数据库才是妥当的做法。
char(10) 根据10，占10个.
列的长度固定为创建表时声明的长度： 0 ~ 255
varchar(10) 根据实际字符串长度占空间，最多10个
列中的值为可变长字符串，长度： 0 ~ 65535
总结：
1.经常变化的字段用varchar
2.知道固定长度的用char
3.超过255字符的只能用varchar或者text
4.能用varchar的地方不用text
text：文本格式
-----------------------------------------------------------------
4.枚举类型 enum【多选一，比如星期几】
mysql> create table t10(sex enum('m','w'));
只能从tom,jim两个里面2选其1（enumeration） 有限制的时候用枚举
案例：mysql> insert into t10(sex) values('m');
set类型
集合中一个到多个，多个之间逗号隔开；
============================================================
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> insert into test_time values(now(),now(),now());
mysql> select * from test_time;
————————————————————————————————————————————————————————————
mysql> create table t9(sex enum('f','m'),hobby set('zhang','xue','feng'),duty year);
mysql> insert into t9 values('f','zhang,xue',89),('m','xue',59),('f','zhang,xue,feng',03);
mysql> select * from t9;
+------+----------------+------+
| sex | hobby | duty |
+------+----------------+------+
| f | zhang,xue | 1989 |
| m | xue | 2059 |
| f | zhang,xue,feng | 2003 |
+------+----------------+------+
复制表【蠕虫复制】
表复制：key不会被复制: 主键、外键和索引c 复制表 1.复制表结构＋记录 （key不会复制: 主键、外键和索引） 语法:create table 新表 select * from 旧表;s mysql> create table new_t1 select * from employee5; 2.【复制单个字段和记录:】 mysql> create table new_t2(select id,name from employee5); 3复制备份表 create table 新表 like 旧表；
like %a VS -a
如果不为空则满足条件，是WHERE comm IS NOT NULL而不是WHERE comm != NULL 比如名字我只记得后面是LACK，第一个字母忘了，WHERE ename like "_LACK" 我只记得是A开头的， WHERE ename LIKE "A%"
五、表完整性约束
外键约束 MySQL字段约束有四种，主键约束，非空约束，唯一约束，外键约束。需要注意的是：外键约束是唯一不推荐的约束，开发中很少用 外键约束有三种约束模式: 都是针对父表的约束(子表约束父表) 级联模式（CASCADE）: 父表的删除或更新操作之后，对应子表关联的数据也跟着被删除或更新 置空模式（SET NULL）: 父表的删除或更新操作之后，子表对应的数据(外键字段)被置空 严格模式（NO ACTION 或 RESTRICT 或 不写）：父表不能删除或者更新一个已经被子表数据引用的记录 FOREIGN KEY (person_id) REFERENCES Persons(id) ON DELETE SET NULL ON UPDATE CASCADE 【默认严格模式，添加后可设置 置空就加SET NULL，级联就加CASCADE，那我们来看看如果外键约束后面加上ON DELETE SET NULL ON UPDATE CASCADE： 外键闭环 子表t_emp外键约束着父表，删除失败。必须先删除子表的约束数据才能删除父表的数据，那这样就失去了增减改查的灵活性了，并且更严重的是， 如果形成外键闭环，我们将无法删除任何一张表的数据记录
作用：用于保证数据的完整性和一致性
约束条件 说明 PRIMARY KEY (PK) 标识该字段为该表的主键，可以唯一的标识记录，不可以为空 UNIQUE + NOT NULL FOREIGN KEY (FK) 标识该字段为该表的外键，实现表与表之间的关联 NULL 标识是否允许为空，默认为NULL。 NOT NULL 标识该字段不能为空，可以修改。 UNIQUE KEY (UK) 标识该字段的值是唯一的，可以为空，一个表中可以有多个UNIQUE KEY AUTO_INCREMENT 标识该字段的值自动增长（整数类型，而且为主键） DEFAULT 为该字段设置默认值 UNSIGNED 无符号，正数
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)); 或者 id后直接声明为主键 mysql> create table t4(id int primary key,name varchar(20)); ----------------------------- 唯一键与主键本质相同，区别就是唯一键【默认允许为空，而且是多个为空】，如果唯一键也不允许为空，则与主键的约束作用是一致的. 在SQL中，NULL值被视为未知的。因此多个NULL值并不被视为相等。这意味着如果一个唯一键的字段是NULL，仍然可以插入另一个NULL值到那个字段。
删除主键 mysql> alter table tab_name drop primary key; 复合主键 ALTER TABLE语句将first_name和last_name设置为复合主键： ALTER TABLE employees DROP PRIMARY KEY, ADD PRIMARY KEY (employee_id, first_name); 先删除原来主键，再新增联合主键 —————————————————————————————————————————————————— 外键 外键是一种数据库表的约束，它用于确保数据的一致性。在关系数据库中，一个表中的字段可以是另一个表的主键。这种字段被称为“外键”。 ：一张表可以有多个外键，外键是加在子表上的。 方式1：创建表的时候增加外键，在所有的表字段之后，使用foreign key(外键字段) references 外部表(主键字段) 首先创建一个主表： CREATE TABLE departments ( dept_id INT NOT NULL, dept_name VARCHAR(50), PRIMARY KEY (dept_id) ); 然后在创建员工表的时候，定义dept_id作为外键，引用departments表的dept_id字段： CREATE TABLE employees ( emp_id INT NOT NULL, emp_name VARCHAR(50), dept_id INT, PRIMARY KEY (emp_id), FOREIGN KEY (dept_id) REFERENCES departments(dept_id) ); 如何查看外键呢？DESC命令是看不到的可以查看建表语句可以看到创建外键的语句 SHOW CREATE TABLE employees; ———————————————— 方式2：在新增表之后增加外键 Alter table 表名 add [constraint 外键名字] foreign key(外键字段) references 父表(主键字段) ----------------------------- 删除外键 ALTER TABLE employees DROP FOREIGN KEY employees_ibfk_1; 如果要删除父表的主键，应该先删除子表的外键【子表的外键来源于附表的主键 外键条件 1.外键要存在，首先必须保证表的存储引擎是InnoDB：确实，只有InnoDB和其他一些支持事务的存储引擎才支持外键。MyISAM等其他一些不支持事务的存储引擎虽然可以创建含有外键的表，但是外键不会有任何效果。 2.外键字段的字段类型必须与父表的主键类型完全一致：这是正确的，否则在定义外键时会出错。 ———
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) );
!
删除自动增长 mysql> ALTER TABLE department3 CHANGE dept_id dept_id INT
3.设置唯一约束 UNIQUE，字段添加唯一约束之后，该字段的值不能重复，也就是说在一列当中不能出现一样的值。 mysql> CREATE TABLE department2 ( dept_id INT, dept_name VARCHAR(30) UNIQUE, comment VARCHAR(50) ); 删除唯一键，首先删除first_name字段的唯一键： mysql> ALTER TABLE employees DROP INDEX first_name; 更新唯一键，添加新的唯一键到last_name字段： mysql> ALTER TABLE employees ADD UNIQUE (last_name);
插入数据的时候id和comment字段相同可以插入数据，如果有相同的名字不唯一。所以插入数据失败。
4.null与not null
1. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值
2. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值
sex enum('male','female') not null default 'male' #只能选择male和female，不允许为空，默认是male
mysql> create table t4(id int(5),name varchar(10),sex enum('male','female') not null default 'male');
指定字符集:
系统配置之前就进行安装【之后安装的数据库可用，之前的库不能使用】 vim /etc/my.cnf character-set-server = utf8 修改字符集 ：在创建表的最后面指定一下： default charset=utf8 #可以指定中文 create table t6(id int(2),name char(5),age int(4)) default charset=utf8; 或者创建数据库时就指定 create database t6 charset uft8;
5.默认约束 添加/删除默认约束 1.创建一个表 create table user(id int not null, name varchar(20), number int, primary key(id)); 【alter 修改字段属性是追加；modify/change 修改字段属性是覆盖形式】 ALTER TABLE user ALTER number SET DEFAULT 0; 删除默认值 mysql> ALTER TABLE user ALTER number drop DEFAULT;
作业
1.查一下oracle和mysql的区别写出几点即可。---面试 一、并发性 mysql: mysql以表级锁为主，对资源锁定的粒度很大，如果一个session对一个表加锁时间过长，会让其他session无法更新此表中的数据。 虽然InnoDB引擎的表可以用行级锁，但这个行级锁的机制依赖于表的索引，如果表没有索引，或者sql语句没有使用索引，那么仍然使用表级锁。 oracle: oracle使用行级锁，对资源锁定的粒度要小很多，只是锁定sql需要的资源，并且加锁是在数据库中的数据行上，不依赖与索引。所以oracle对并发性的支持要好很多。 二、一致性 oracle: oracle支持serializable的隔离级别，可以实现最高级别的读一致性。每个session提交后其他session才能看到提交的更改。oracle通过在undo表空间中构造多版本数据块来实现读一致性，每个session查询时，如果对应的数据块发生变化，oracle会在undo表空间中为这个session构造它查询时的旧的数据块。 mysql: mysql没有类似oracle的构造多版本数据块的机制，只支持read commited的隔离级别。一个session读取数据时，其他session不能更改数据，但可以在表最后插入数据。session更新数据时，要加上排它锁，其他session无法访问数据。 三、事务 oracle很早就完全支持事务。 mysql在innodb存储引擎的行级锁的情况下才支持事务。 四、数据持久性 oracle 保证提交的数据均可恢复，因为oracle把提交的sql操作线写入了在线联机日志文件中，保持到了磁盘上，如果出现数据库或主机异常重启，重启后oracle可以考联机在线日志恢复客户提交的数据。 mysql: 默认提交sql语句，但如果更新过程中出现db或主机重启的问题，也许会丢失数据。 五、提交方式 oracle默认不自动提交，需要用户手动提交。 mysql默认是自动提交。 六、逻辑备份 oracle逻辑备份时不锁定数据，且备份的数据是一致的。 mysql逻辑备份时要锁定数据，才能保证备份的数据是一致的，影响业务正常的dml使用。 七、热备份 oracle有成熟的热备工具rman，热备时，不影响用户使用数据库。即使备份的数据库不一致，也可以在恢复时通过归档日志和联机重做日志进行一致的回复。 mysql: myisam的引擎，用mysql自带的mysqlhostcopy热备时，需要给表加读锁，影响dml操作。innodb的引擎，它会备份innodb的表和索引，但是不会备份.frm文件。用ibbackup备份时，会有一个日志文件记录备份期间的数据变化，因此可以不用锁表，不影响其他用户使用数据库。但此工具是收费的。 innobackup是结合ibbackup使用的一个脚本，他会协助对.frm文件的备份。 八、sql语句的扩展和灵活性 mysql对sql语句有很多非常实用而方便的扩展，比如limit功能，insert可以一次插入多行数据，select某些管理数据可以不加from。 oracle在这方面感觉更加稳重传统一些。 九、复制 oracle:既有推或拉式的传统数据复制，也有dataguard的双机或多机容灾机制，主库出现问题是，可以自动切换备库到主库，但配置管理较复杂。 mysql:复制服务器配置简单，但主库出问题时，丛库有可能丢失一定的数据。且需要手工切换丛库到主库。 十、性能诊断 oracle有各种成熟的性能诊断调优工具，能实现很多自动分析、诊断功能。比如awr、addm、sqltrace、tkproof等 mysql的诊断调优方法较少，主要有慢查询日志。 十一、权限与安全 mysql的用户与主机有关，感觉没有什么意义，另外更容易被仿冒主机及ip有可乘之机。 oracle的权限与安全概念比较传统，中规中矩。 十二、分区表和分区索引 oracle的分区表和分区索引功能很成熟，可以提高用户访问db的体验。 mysql的分区表还不太成熟稳定。 十三、管理工具 oracle有多种成熟的命令行、图形界面、web管理工具，还有很多第三方的管理工具，管理极其方便高效。 mysql管理工具较少，在linux下的管理工具的安装有时要安装额外的包(phpmyadmin， etc)，有一定复杂性。 服务端提供oracle服务的实例，其是数据库的核心，用于数据库的管理，对象的管理与存储、数据的存储、查询、数据库资源的监控、监听等一些服务。 而客户端只是一个与服务端交互的工具，如sqlplus，在sqlplus里执行SQL语句传到服务端，服务端进行解析后执行SQL里的操作，并将操作结果输出到客户端。 这就完成了一个客户端与服务端交互的过程。 其他： 1. Oracle是大型数据库而Mysql是中小型数据库，Oracle市场占有率达40%，Mysql只有20%左右，同时Mysql是开源的而Oracle价格非常高。 2. Oracle支持大并发，大访问量，是OLTP(On-Line Transaction Processing联机事务处理系统)最好的工具。 3. 安装所用的空间差别也是很大的，Mysql安装完后才152M而Oracle有3G左右，且使用的时候Oracle占用特别大的内存空间和其他机器性能。 4.Oracle也Mysql操作上的一些区别 ①主键 Mysql一般使用自动增长类型，在创建表时只要指定表的主键为auto increment,插入记录时，不需要再指定该记录的主键值，Mysql将自动增长；Oracle没有自动增长类型，主键一般使用的序列，插入记录时将序列号的下一个值付给该字段即可；只是ORM框架是只要是native主键生成策略即可。 ②单引号的处理 MYSQL里可以用双引号包起字符串，ORACLE里只可以用单引号包起字符串。在插入和修改字符串前必须做单引号的替换：把所有出现的一个单引号替换成两个单引号。 ③翻页的SQL语句的处理 MYSQL处理翻页的SQL语句比较简单，用LIMIT 开始位置, 记录个数；ORACLE处理翻页的SQL语句就比较繁琐了。每个结果集只有一个ROWNUM字段标明它的位置, 并且只能用ROWNUM<100, 不能用ROWNUM>80 ④ 长字符串的处理 长字符串的处理ORACLE也有它特殊的地方。INSERT和UPDATE时最大可操作的字符串长度小于等于4000个单字节, 如果要插入更长的字符串, 请考虑字段用CLOB类型，方法借用ORACLE里自带的DBMS_LOB程序包。插入修改记录前一定要做进行非空和长度判断，不能为空的字段值和超出长度字段值都应该提出警告,返回上次操作。 ⑤空字符的处理 MYSQL的非空字段也有空的内容，ORACLE里定义了非空字段就不容许有空的内容。按MYSQL的NOT NULL来定义ORACLE表结构, 导数据的时候会产生错误。因此导数据时要对空字符进行判断，如果为NULL或空字符，需要把它改成一个空格的字符串。 ⑥字符串的模糊比较 MYSQL里用 字段名 like '%字符串%',ORACLE里也可以用 字段名 like '%字符串%' 但这种方法不能使用索引, 速度不快。 ⑦Oracle实现了ANSII SQL中大部分功能，如，事务的隔离级别、传播特性等，而Mysql在这方面还是比较弱 ———————————————— 版权声明：本文为优快云博主「苏白衣」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。 原文链接：https://blog.youkuaiyun.com/weixin_29774037/article/details/113120687
六、表操作
1、添加字段
1-1添加一个新字段 alter table 表名 add 字段 类型; mysql> alter table t3 add math int(10);-------添加的字段 1-2加多个字段,中间用逗号隔开。 mysql> alter table t3 add (chinese int(10),english int(10));。 1-3把添加的字段放到name后面 alter table 表名 add 添加的字段(和类型) after name; 1-把添加的字段放在第一个 alter table 表名 add 添加的字段(和类型) first;
2.修改字段和类型
1.修改名称、数据类型、类型 alter table 表名 change 旧字段 新字段 类型; #change修改字段名称，类型，约束，顺序 mysql> alter table t3 change max maxs int(15) after id; #修改字段名称与修饰并更换了位置 2.修改字段类型，约束，顺序 alter table 表名 modify 字段 类型； #modify 不能修改字段名称 mysql> alter table t3 modify maxs int(20) after math; #修改类型并更换位置 3.删除字段 mysql> alter table t3 drop maxs; #drop 丢弃的字段。
3.插入数据(添加纪录)
mysql> create table t3(id int, name varchar(20), sex enum('m','f'), age int);
字符串必须引号引起来
记录与表头相对应，表头与字段用逗号隔开。
1.添加一条记录
insert into 表名(字段1,字段2,字段3,字段4) values(1,"tom","m",90);
注:添加的记录与表头要对应，
2.添加多条记录
mysql> insert into t3(id,name,sex,age) values(2,"jack","m",19),(3,"xiaoli","f",20);
3.用set添加记录
mysql> insert into t3 set id=4,name="zhangsan",sex="m",age=21;
4.更新记录
update 表名 set 修改的字段 where 给谁修改;
update t3 set id=6 where name="xiaoli";
5.删除记录
1.删除单条记录
mysql> delete from t3 where id=6; #删除那个记录，等于几会删除那个整条记录
Query OK, 1 row affected (0.35 sec)
2.删除所有记录
mysql> delete from t3;
4.单表查询
测试表:company.employee5
mysql> create database company; #创建一个库；
创建一个测试表:
mysql> CREATE TABLE company.employee5(
id int primary key AUTO_INCREMENT not null,
name varchar(30) not null,
sex enum('male','female') default 'male' not null,
hire_date date not null,
post varchar(50) not null,
job_description varchar(100),
salary double(15,2) not null,
office int,
dep_id int
);
插入数据:
mysql> insert into company.employee5(name,sex,hire_date,post,job_description,salary,office,dep_id) values
('jack','male','20180202','instructor','teach',5000,501,100),
('tom','male','20180203','instructor','teach',5500,501,100),
('robin','male','20180202','instructor','teach',8000,501,100),
('alice','female','20180202','instructor','teach',7200,501,100),
('tianyun','male','20180202','hr','hrcc',600,502,101),
('harry','male','20180202','hr',NULL,6000,502,101),
('emma','female','20180206','sale','salecc',20000,503,102),
('christine','female','20180205','sale','salecc',2200,503,102),
('zhuzhu','male','20180205','sale',NULL,2200,503,102),
('gougou','male','20180205','sale','',2200,503,102);
mysql> use company
语法：
select 字段名称,字段名称2 from 表名 条件
简单查询
mysql> select * from employee5;
多字段查询：
mysql> select id,name,sex from employee5;
有条件查询：where [< > = != is is not ]
mysql> select id,name from employee5 where id<=3;
mysql> select id,name,salary from employee5 where salary>2000;
设置别名：as
mysql> select id,name,salary as "salry_num" from employee5 where salary>5000;
给 salary 的值起个别名，显示值的表头会是设置的别名
统计记录数量：count()
mysql> select count(*) from employee5;
统计字段得到数量:
mysql> select count(id) from employee5;
避免重复DISTINCT:表里面的数据有相同的
mysql> select distinct post from employee5;
#字段 表名
3.多条件查询: and ----和 语法: select 字段，字段2 from 表名 where 条件 and where 条件； mysql> SELECT name,salary from employee5 where post='hr' AND salary>1000; mysql> SELECT name,salary from employee5 where post='instructor' AND salary>1000; 4.多条件查询: or ----或者 语法： select 字段，字段2 from 表名 where 条件 or 条件； mysql> select name from employee5 where salary>5000 and salary<10000 or dep_id=102; mysql> select name from employee5 where salary>2000 and salary<6000 or dep_id=100; 5.关键字 BETWEEN AND 什么和什么之间。 mysql> SELECT name,salary FROM employee5 WHERE salary BETWEEN 5000 AND 15000; mysql> SELECT name,salary FROM employee5 WHERE salary NOT BETWEEN 5000 AND 15000; mysql> select name,dep_id,salary from employee5 where not salary>5000; 注:not 给条件取反 6.关键字IS NULL 空的 mysql> SELECT name,job_description FROM employee5 WHERE job_description IS NULL; mysql> SELECT name,job_description FROM employee5 WHERE job_description IS NOT NULL; #-取反 不是null mysql> SELECT name,job_description FROM employee5 WHERE job_description=''; #什么都没有==空 NULL说明： 1、等价于没有任何值、是未知数。 2、NULL与0、空字符串、空格都不同,NULL没有分配存储空间。 3、对空值做加、减、乘、除等运算操作，结果仍为空。 4、比较时使用关键字用“is null”和“is not null”。 5、排序时比其他数据都小（索引默认是降序排列，小→大），所以NULL值总是排在最前。 7.关键字IN集合查询 一般查询： mysql> SELECT name,salary FROM employee5 WHERE salary=4000 OR salary=5000 OR salary=6000 OR salary=9000; IN集合查询 mysql> SELECT name, salary FROM employee5 WHERE salary IN (4000,5000,6000,9000); mysql> SELECT name, salary FROM employee5 WHERE salary NOT IN (4000,5000,6000,9000); #取反 8.排序查询 order by ：指令，在mysql是排序的意思。 mysql> select name,salary from employee5 order by salary; #-默认从小到大排序。 mysql> select name,salary from employee5 order by salary desc; #降序，从大到小 9.limit 限制 【LIMIT子句参数只能写一个，LIMIT子句在UPDATE中只能包含有一个参数，代表取前3条数据。】 mysql> select * from employee5 limit 5; #只显示前5行 mysql> select name,salary from employee5 order by salary desc limit 0,1; #从第几行开始，打印一行 查找什么内容从那张表里面降序排序只打印第二行。 注意： 0-------默认第一行 1------第二行 依次类推... mysql> SELECT * FROM employee5 ORDER BY salary DESC LIMIT 0,5; #降序，打印5行 mysql> SELECT * FROM employee5 ORDER BY salary DESC LIMIT 4,5; #从第5条开始，共显示5条 mysql> SELECT * FROM employee5 ORDER BY salary LIMIT 4,3; #默认从第5条开始显示3条。 打印第五行4，0； ELECT * FROM employee5 ORDER BY salary LIMIT 4,1; 10.分组查询 ：group by mysql> select count(name),post from employee5 group by post; +-------------+------------+ count可以计算字段里面有多少条记录，如果分组会分组做计算 mysql> select count(name),group_concat(name) from employee5 where salary>5000; 查找 统计（条件：工资大于5000）的有几个人（count(name)），分别是谁（group_concat(name)） +-------------+----------------------------+ | count(name) | group_concat(name) | +-------------+----------------------------+ | 5 | tom,robin,alice,harry,emma | +-------------+----------------------------+ 11.GROUP BY和【GROUP_CONCAT()】函数一起使用 GROUP_CONCAT()-------组连接 mysql> SELECT dep_id,GROUP_CONCAT(name) FROM employee5 GROUP BY dep_id; #以dep_id分的组，dep_id这个组里面都有谁 mysql> SELECT dep_id,GROUP_CONCAT(name) as emp_members FROM employee5 GROUP BY ------------------- 使用 group_concat() 或 any_value() group_concat()：将分到同一组的数据默认用逗号隔开作为返回数据 any_value()：将分到同一组的数据里第一条数据的指定列值作为返回数据 ------------------------------------- 12.函数 max() 最大值 mysql> select max(salary) from employee5; 查询薪水最高的人的详细信息： mysql> select name,sex,hire_date,post,salary,dep_id from employee5 where salary = (SELECT MAX(salary) from employee5); min()最小值 select min(salary) from employee5; avg()平均值 select avg(salary) from employee5; now() 现在的时间 select now(); sum() 计算和 select sum(salary) from employee5 where post='sale'; length()字符串长度； round()四舍五入 ceil()向上取整； floor()向下取整； ____________________________________ 日期函数 13.2.1 获取系统时间函数 NOW()函数能获得系统日期和时间，格式yyyy-MM-dd hh:mm:ss，数据库的最小时间单位是秒s，而不是毫秒ms CURDATE()函数能获得当前系统日期，格式yyyy-MM-dd CURTIME()函数能获得当前系统时间，格式hh:mm:ss DATE_FORMAT(日期, 表达式) 该函数用于格式化日期，返回用户想要的日期格式 eg：比如查看员工入职的年份 SELECT ename, DATE_FORMAT(hiredate,"%Y") AS result FROM t_emp; --------------------------- 那就是HAVING子句，HAVING子句的出现主要是为了WHERE子句不能使用聚合函数的问题，HAVING子句不能独立存在，必须依赖于GROUP BY子句而存在，GROUP BY 执行完成就立即执行HAVING子句 (1) from (2) on (3) join (4) where （可以使用表的别名） (5) group by(可以开始使用select中字段的别名（不是表的别名），后面的语句中都可以使用) (6) having + 聚合函数 (7) select (8) distinct (9) order by (10) limit 1~3是table部分——形成表 4~6是filter部分——过滤条件 7~10是show部分——展示 where查询效率比having效率高；但是聚合函数和分组情况下只能having； 选择题注意细节：表名是否加s;字段间逗号； --------------------------------------------- create table bla(id int,id2 int); insert bla values(null,null),(1,null),(null,1),(1,null),(null,1),(1,null),(null,null); select count(*),count(id),count(id2) from bla; +----------+-----------+------------+ | count(*) | count(id) | count(id2) | +----------+-----------+------------+ | 7 | 3 | 2 | select count(*) 比count（列名）（常量1）查询速率要快；查的行数包含允许查询结果为nul的行数； 按照效率排序的话，count(字段)<count(主键 id)<count(1)=count(*)，所以我建议你，尽量使用 count(*)或count（1）。
5.多表查询
概念：当在查询时，所需要的数据不在一张表中，可能在两张表或多张表中。此时需要同时操作这些表。即关联查询。
内连接：在做多张表查询时，这些表中应该存在着有关联的两个字段,组合成一条记录。只连接匹配到的行
实战
准备两张表（表一）
create table employee6( emp_id int auto_increment primary key not null, emp_name varchar(50), age int, dept_id int);insert into employee6(emp_name,age,dept_id) values('xiaoli',19,200),('tom',26,201),('jack',30,201),('alice',24,202),('robin',40,200),('zhangsan',28,204);
create table department6(dept_id int, dept_name varchar(100));insert into department6 values(200,'hr'),(201,'it'), (202,'sale'),(203,'op');
内连接查询：只显示表中有匹配的数据
只找出有相同部门的员工
mysql> select employee6.emp_id,employee6.emp_name,employee6.age,department6.dept_name from employee6,department6 where employee6.dept_id = department6.dept_id;
外连接：在做多张表查询时，所需要的数据，除了满足关联条件的数据外，还有不满足关联条件的数据。此时需要使用外连接.外连接分为三种 左外连接：表A left [outer] join 表B on 关联条件，表A是主表，表B是从表 右外连接：表A right [outer] join 表B on 关联条件，表B是主表，表A是从表 全外连接：表A full [outer] join 表B on 关联条件，两张表的数据不管满不满足条件，都做显示。 案例：查找出所有员工及所属部门 1.左外链接 mysql> select emp_id,emp_name,dept_name from employee6 left join department6 on employee6.dept_id = department6.dept_id; +--------+----------+-----------+ | emp_id | emp_name | dept_name | +--------+----------+-----------+ | 1 | xiaoli | hr | | 5 | robin | hr | | 2 | tom | it | | 3 | jack | it | | 4 | alice | sale | | 6 | zhangsan | NULL | +--------+----------+-----------+ 6 rows in set (0.01 sec) 2.右外连接 案例：找出所有部门包含的员工 mysql> select emp_id,emp_name,dept_name from employee6 right join department6 on employee6.dept_id = department6.dept_id; +--------+----------+-----------+ | emp_id | emp_name | dept_name | +--------+----------+-----------+ | 1 | xiaoli | hr | | 2 | tom | it | | 3 | jack | it | | 4 | alice | sale | | 5 | robin | hr | | NULL | NULL | op | +--------+----------+-----------+ 6 rows in set (0.00 sec)
update内连接
对于内连接来说，选取哪个表为驱动表都没关系。而外连接的驱动表是固定的，左(外)连接的驱动表就是左边那个表，右(外)连接的驱动表就是右边那个表。 对于外连接来说，on和where是有区别的。 即使被驱动表中的记录无法匹配on子句的条件，该驱动表的记录仍然是满足条件的一条记录，对应被驱动表的各个字段用NULL填充。 简言之，对于外连接，驱动表的记录一定都有，被驱动表不匹配就用NULL填充。 而where过滤条件是在记录连接过后的普通过滤条件，即连接的记录会再次判断是否符合条件，不符合就从结果集中剔除。 需要起别名【】笛卡尔积 UPDATE语句中的内连接 因为相关子查询效率非常低，所以我们可以利用表连接的方式来改造UPDATE语句 UPDATE 表1 JOIN 表2 ON 条件 SET 字段1=值1, 字段2=值2, ...... 引申出另一种写法 UPDATE 表1 JOIN 表2 SET 字段1=值1, 字段2=值2, ...... WHERE 条件; 表连接的UPDATE语句可以修改多张表的记录 eg：把ALLEN调往RESEARCH部门，职务调整为ANALYST /*表连接的几种写法*/ UPDATE t_emp e JOIN t_dept d ON e.ename="ALLEN" AND d.dname="RESEARCH" SET e.deptno=d.deptno, e.job="ANALYST" UPDATE t_emp e JOIN t_dept d SET e.deptno=d.deptno, e.job="ANALYST" WHERE e.ename="ALLEN" AND d.dname="RESEARCH" UPDATE t_emp e,t_dept d SET e.deptno=d.deptno, e.job="ANALYST" WHERE e.ename="ALLEN" AND d.dname="RESEARCH" ———————————————— 把底薪低于公司平均底薪的员工，底薪增加150元 sql语句如下 UPDATE t_emp e JOIN (SELECT AVG(sal) avg FROM t_emp) t ON e.sal<t.avg SET e.sal=e.sal+150
update外连接
UPDATE语句中的外连接 UPDATE语句的表连接既可以是内连接，又可以是外连接。 基本语法 UPDATE 表1 [LEFT | RIGHT] JOIN 表2 ON 条件 SET 字段1=值1, 字段2=值2, ......; eg：把没有部门的员工，或者SALES部门低于2000元底薪的员工，都调往20部门 UPDATE t_emp e LEFT JOIN t_dept d ON e.deptno=d.deptno SET e.deptno=20 WHERE e.deptno IS NULL OR (d.dname="SALES" AND e.sal<2000);
6.破解密码
先将这个修改成简单密码注释掉
root账户没了或者root密码丢失：
关闭Mysql使用下面方式进入Mysql直接修改表权限
5.6/5.7版本：
# mysqld --skip-grant-tables --user=mysql &
# mysql -uroot
mysql> UPDATE mysql.user SET authentication_string=password('Tang@123') WHERE user='root' AND host='localhsot';
mysql> FLUSH PRIVILEGES;
#编辑配置文件将skip-grant-tables参数注释my
#重启mysql
Day03
七、MySQL 索引
索引作为一种数据结构，其用途是用于提升检索数据的效率。
1、MySQL 索引的分类
- 普通索引（INDEX）：索引列值可重复 - 唯一索引（UNIQUE）：索引列值必须唯一，可以为NULL - 主键索引（PRIMARY KEY）：索引列值必须唯一，不能为NULL，一个表只能有一个主键索引 - 全文索引（FULL TEXT）：给每个字段创建索引
2、MySQL 不同类型索引用途和区别
- 普通索引常用于过滤数据。例如，以商品种类作为索引，检索种类为“手机”的商品。 - 唯一索引主要用于标识一列数据不允许重复的特性，相比主键索引不常用于检索的场景。 - 主键索引是行的唯一标识，因而其主要用途是检索特定数据。 - 全文索引效率低，常用于文本中内容的检索。
3、MySQL 使用索引
1、创建索引
1、普通索引（INDEX）
mysql> create table s1(id int not null auto_increment primary key, name varchar(100) not null, birthdy date, sex enum('男','女')default '男');
____________________________________________________________
要想完成自增必须是主键；
# 在创建表时指定
mysql> create table s1(id int not null auto_increment primary key, name varchar(100) not null, birthdy date, sex char(1) not null, index nameindex (name(50)));
Query OK, 0 rows affected (0.02 sec)
# 基于表结构创建
mysql> create table s2(id int not null, name varchar(100) not null, birthday date, sex char(1) not null);
Query OK, 0 rows affected (0.01 sec)
mysql> create index nameindex on student2(name(50));
# 修改表结构创建
create table s3(id int not null, name varchar(100) not null, birthday date, sex char(1) not null);
Query OK, 0 rows affected (0.01 sec)
mysql> ALTER TABLE student3 ADD INDEX nameIndex(name(50));
mysql> show index from student3; //查看某个表格中的索引
2、唯一索引（UNIQUE）
# 在创建表时指定 mysql> create table student4(id int not null, name varchar(100) not null, birthday date, sex char(1) not null, unique index id_idex (id)); Query OK, 0 rows affected (0.00 sec) # 基于表结构创建 mysql> create table student5(id int not null, name varchar(100) not null, birthday date, sex char(1) not null); Query OK, 0 rows affected (0.00 sec) mysql> CREATE unique INDEX idIndex ON student5(id);
3、主键索引（PRIMARY KEY）
# 创建表时时指定 mysql> create table student6(id int not null, name varchar(100) not null, birthday date, sex char(1) not null, primary key (id)); Query OK, 0 rows affected (0.01 sec) # 修改表结构创建 mysql> create table student7(id int not null, name varchar(100) not null, birthday date, sex char(1) not null); Query OK, 0 rows affected (0.01 sec) mysql> ALTER TABLE student7 ADD PRIMARY KEY (id); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
主键索引不能使用基于表结构创建的方式创建。
2、删除索引
1、普通索引（INDEX）
# 直接删除 mysql> DROP INDEX nameIndex ON student1; # 修改表结构删除 mysql> ALTER TABLE student2 DROP INDEX nameIndex;
2、唯一索引（UNIQUE）
# 直接删除 mysql> DROP INDEX idIndex ON student4; # 修改表结构删除 mysql> ALTER TABLE student DROP INDEX idIndex;
3、主键索引（PRIMARY KEY）
mysql> ALTER TABLE student DROP PRIMARY KEY;
主键不能采用直接删除的方式删除。
3、查看索引
mysql> SHOW INDEX FROM tab_name;
4、选择索引的原则
- 常用于查询条件的字段较适合作为索引，例如WHERE语句和JOIN语句中出现的列 - 唯一性太差的字段不适合作为索引，例如性别 - 更新过于频繁（更新频率远高于检索频率）的字段不适合作为索引 - 使用索引的好处是索引通过一定的算法建立了索引值与列值直接的联系，可以通过索引直接获取对应的行数据，而无需进行全表搜索，因而加快了检索速度 - 但由于索引也是一种数据结构，它需要占据额外的内存空间，并且读取索引也加会大IO资源的消耗，因而索引并非越多越好，且对过小的表也没有添加索引的必要
index(key)每张表可以有很多列做index，必须的起名 面试题: 导致SQL执行慢的原因： 1.硬件问题。如网络速度慢，内存不足，I/O吞吐量小，磁盘空间满了等。 2.没有索引或者索引失效. 3.数据过多（分库分表） 4.服务器调优及各个参数设置（调整my.cnf） 索引：当查询速度过慢可以通过建立优化查询速度，可以当作调优
八、权限管理
1.用户管理
1. 登录和退出MySQL 本地登录客户端命令： # mysql -uroot -pqf123 远程登陆： 客户端语法：mysql -u 用户名 -p 密码 -h ip地址 -P端口号:如果没有改端口号就不用-P指定端口 # mysql -h192.168.246.253 -P 3306 -uroot -pqf123
如果报错进入server端服务器登陆mysql执行: mysql> use mysql mysql> update user set host = '%' where user = 'root'; mysql> flush privileges;
# mysql -h192.168.246.253 -P 3306 -uroot -pqf123 -e 'show databases;' -h 指定主机名 【默认为localhost】 -大P MySQL服务器端口 【默认3306】 -u 指定用户名 【默认root】 -p 指定登录密码 【默认为空密码】 -e 接SQL语句，可以写多条拿;隔开 # mysql -h192.168.246.253 -P 3306 -uroot -pqf123 -D mysql -e 'select * from user;' 此处 -D mysql为指定登录的数据库 修改端口rpm安装：vim /etc/my.cnf 在到【mysql】标签下面添加port=指定端口。重启服务
2.创建用户
方法一：CREATE USER语句创建
mysql> create user tom@'localhost' identified by 'qf@123'; #创建用户为tom，并设置密码。
mysql> FLUSH PRIVILEGES; #更新授权表
注:
identified by ：设置密码
在用户tom@' ' 这里 选择:
%：允许所有主机远程登陆包括localhost。也可以指定某个ip，允许某个ip登陆。也可以是一个网段。
localhost:只允许本地用户登录
==客户端主机 % 所有主机远程登录
192.168.246.% 192.168.246.0网段的所有主机
192.168.246.252 指定主机
localhost 只允许本地用户登录
GRANT ---授权。 mysql> GRANT ALL ON *.* TO 'user3'@’localhost’; #权限 库名.表名 账户名 mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)
修改远程登陆: 将原来的localhost修改为%或者ip地址 mysql> use mysql mysql> update user set host = '192.168.246.%' where user = 'user3'; mysql> FLUSH PRIVILEGES;
3、刷新权限
修改表之后需要刷新权限 方式1： mysql > flush privileges;
方式二：使用命令创建用户并授权：grant
也可创建新账户(不过后面的版本会移除这个功能，建议使用create user)
语法格式：
grant 权限列表 on 库名.表名 to '用户名'@'客户端主机' IDENTIFIED BY 'Qf@123'；
==权限列表 all 所有权限（不包括授权权限）
select,update
select, insert
#注意：root用授权时候grant授权权限不要给予
==数据库.表名 *.* 所有库下的所有表
web.* web库下的所有表
web.stu_info web库下的stu_info表
#单独授权
给刚才创建的用户tom授权：
mysql> grant select,insert on *.* to 'tom'@'localhost';
mysql> FLUSH PRIVILEGES;
4.权限简介
权限简介 | 权限 | 权限级别 | 权限说明 | :--------------------- | :--------------------- | :------------------------------------ | CREATE | 数据库、表或索引 | 创建数据库、表或索引权限 | DROP | 数据库或表 | 删除数据库或表权限 | GRANT OPTION | 数据库、表或保存的程序 | 赋予权限选项 #小心给予 | ALTER | 表 | 更改表，比如添加字段、索引等 | DELETE | 表 | 删除数据权限 | INDEX | 表 | 索引权限 | INSERT | 表 | 插入权限 | SELECT | 表 | 查询权限 | UPDATE | 表 | 更新权限 | LOCK TABLES | 服务器管理 | 锁表权限 | CREATE USER | 服务器管理 | 创建用户权限 | REPLICATION SLAVE | 服务器管理 | 复制权限 | SHOW DATABASES | 服务器管理 | 查看数据库权限
5、查看权限
查看权限 1.看自己的权限： mysql> SHOW GRANTS\G *************************** 1. row *************************** Grants for root@%: GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION 2.看别人的权限： mysql> SHOW GRANTS FOR tom@'localhost'\G *************************** 1. row *************************** Grants for tom@localhost: GRANT SELECT, INSERT ON *.* TO 'tom'@'localhost'
6、移除权限
移除用户权限 语法：REVOKE 权限 ON 数据库.数据表 FROM '用户'@'IP地址'; - 被回收的权限必须存在，否则会出错 - 整个数据库，使用 ON datebase.*； - 特定的表：使用 ON datebase.table； mysql> revoke select,delete on *.* from jack@'%'; #回收指定权限 mysql> revoke all privileges on *.* from jack@'%'; #回收所有权限 mysql> flush privileges;
7、修改密码
＝＝＝root修改自己密码 方法一: 语法： mysqladmin -uroot -p'123' password 'new_password' #123为旧密码 案例: # mysqladmin -uroot -p'qf123' password 'qf@123'; 方法二: mysql>SET PASSWORD='new_password'; ==root修改其他用户密码 mysql> use mysql mysql> SET PASSWORD FOR user3@'localhost'='new_password' 用户 = 新密码
8、删除用户
方法一：DROP USER语句删除 DROP USER 'user3'@'localhost'; 方法二：DELETE语句删除 DELETE FROM mysql.user WHERE user='tom' AND host='localhost'; 更新授权表： FLUSH PRIVILEGES;
9、查看密码复杂度
MySQL 默认启用了密码复杂度设置，插件名字叫做 validate_password，初始化之后默认是安装的如果没有安装执行下面的命令会返回空或者没有值，这时需要安装该插件
安装插件 mysql> INSTALL PLUGIN validate_password SONAME 'validate_password.so';
1.查看密码复杂度 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 2.查看密码策略 mysql> select @@validate_password_policy; +----------------------------+ | @@validate_password_policy | +----------------------------+ | MEDIUM | +----------------------------+ 1 row in set (0.00 sec) 策略： - 0 or LOW 设置密码长度（由参数validate_password_length指定） - 1 or MEDIUM 满足LOW策略，同时还需满足至少有1个数字，小写字母，大写字母和特殊字符 - 2 or STRONG 满足MEDIUM策略，同时密码不能存在字典文件（dictionary file）中 3.查看密码的长度 mysql> select @@validate_password_length; +----------------------------+ | @@validate_password_length | +----------------------------+ | 8 | +----------------------------+ 1 row in set (0.00 sec) 4.设置密码复杂度 mysql> set global validate_password_length=1; #设置密码长度为1个 mysql> set global validate_password_number_count=2; #设置密码数字最少为2个 5.设置密码复杂性策略 mysql> set global validate_password_policy=LOW; 也可以是数字表示。#设置密码策略 mysql> flush privileges; #刷新授权
10、权限控制机制
四张表：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 #数据库级别授权 - 其他 #表级，列级授权
九、日志管理
1 错误日志 ：启动，停止，关闭失败报错。rpm安装日志位置 /var/log/mysqld.log #默认开启 2 通用查询日志：所有的查询都记下来。 #默认关闭，一般不开启 3 二进制日志(bin log)：实现备份，增量备份。只记录改变数据，除了select都记。 4 中继日志(Relay log)：读取主服务器的binlog，在slave机器本地回放。保持与主服务器数据一致。 5 slow log：慢查询日志，指导调优，定义某一个查询语句，执行时间过长，通过日志提供调优建议给开发人员。 6 DDL log： 定义语句的日志。
Error Log [root@qfedu ~]# vim /etc/my.cnf log-error=/var/log/mysqld.log 编译安装的在/usr/local/mysql/ Binary Log:前提需要开启 [root@qfedu ~]# vim /etc/my.cnf log-bin=/var/log/mysql-bin/mylog #如果不指定路径默认在/var/lib/mysql server-id=1 #AB复制的时候使用，为了防止相互复制，会设置一个ID，来标识谁产生的日志 [root@qfedu ~]# mkdir /var/log/mysql-bin [root@qfedu ~]# chown mysql.mysql /var/log/mysql-bin/ [root@qfedu ~]# systemctl restart mysqld
查看binlog日志：开启之后等一会 [root@qfedu mysql]# mysqlbinlog mylog.000001 -v # at 4 #时间的开始位置 # end_log_pos 319 #事件结束的位置(position) #190820 19:41:26 #时间点 注： 1. 重启mysqld 会截断 2. mysql> flush logs; 会截断 3. mysql> reset master; 删除所有binlog,不要轻易使用，相当于：rm -rf / 4. 删除部分 mysql> PURGE BINARY LOGS TO 'mylog.000004'; #删除mysqllog.000004之前的日志 5. 暂停 仅当前会话 SET SQL_LOG_BIN=0; #关闭 SET SQL_LOG_BIN=1; #开启 ===================================== 解决binlog日志不记录insert语句 登录mysql后，设置binlog的记录格式： mysql> set binlog_format=statement; 然后，最好在my.cnf中添加： binlog_format=statement 修改完配置文件之后记得重启服务 ================================================ Slow Query Log ： 慢查询日志 slow_query_log=1 #开启 slow_query_log_file=/var/log/mysql-slow/slow.log long_query_time=3 #设置慢查询超时间，单位是秒 # mkdir /var/log/mysql-slow/ # chown mysql.mysql /var/log/mysql-slow/ # systemctl restart mysqld 验证查看慢查询日志 mysql> select sleep(6); # cat /var/log/mysql-slow/slow.log
扩展
UNIX Socket连接方式其实不是一个网络协议，所以只能在MySQL客户端和数据库实例在同一台服务器上的情况下使用。本地进程间通信的一种方式
通过socket方式登录 查看sock的存放路径 [root@qfedu ~]# cat /etc/my.cnf | grep sock socket=/var/lib/mysql/mysql.sock [root@qfedu ~]# mysql -uroot -p'QianFeng@123!' -S /var/lib/mysql/mysql.sock
Day04
十、数据备份与恢复
mysql备份步骤（完全备份、差异备份、增量备份、逻辑备份） 1-1 完全备份和恢复的步骤 完全备份步骤： 1.先下载完全备份工具 2.创建完全备份目录 mkdir /bf 3.进行完全备份 innobackupex --user=root --password='1' /bf •恢复步骤： 1.关闭mysql数据库 2.重演回滚 innobackupex --apply-log /bf/完全备份目录 3.恢复数据 innobackupex --copy-back /bf/完全备份目录 5.修改权限 chown mysql.mysql /var/lib/mysql -R 6.重启mysql 1-2 增量备份和恢复的步骤 增量备份步骤： 1.创建完全备份目录 mkdir /bf 2.进行完全备份 innobackupex --user=root --password='1' /bf 3.第一次增量备份 innobackupex --user=root --password='1' --incremental /z1 --incremental-basedir=/bf/完全备份目录 4.第二次增量备份 innobackupex --user=root --password='1' --incremental /z2 --incremental-basedir=/z1/第一次增量备份目录 •恢复步骤： 1.关闭mysql数据库 2.重演回滚 innobackupex --apply-log --redo-only /bf/完全备份目录 3.重演回滚 innobackupex --apply-log --redo-only /bf/完全备份目录 --incremental-dir=/z1/第一次增量备份目录 4.重演回滚 innobackupex --apply-log --redo-only /bf/完全备份目录 --incremental-dir=/z2/第二次增量备份目录 5.恢复数据 innobackupex --copy-back /bf/完全备份目录 6.修改权限 chown mysql.mysql /var/lib/mysql -R 7.重启mysql 差异备份和恢复的步骤 差异备份步骤： 1.创建完全备份目录 mkdir /bf 2.进行完全备份 innobackupex --user=root --password='1' /bf 3.第一次差异备份 innobackupex --user=root --password='1' --incremental /c1 --incremental-basedir=/bf/完全备份目录 4.第二次差异备份 innobackupex --user=root --password='1' --incremental /c2 --incremental-basedir=/bf/完全备份目录 •恢复步骤： 1.关闭mysql数据库 2.重演回滚 innobackupex --apply-log --redo-only /bf/完全备份目录 3.重演回滚 innobackupex --apply-log --redo-only /bf/完全备份目录 --incremental-dir=/c1/第一次差异备份目录 5.恢复数据 innobackupex --copy-back /bf/完全备份目录 6.修改权限 chown mysql.mysql /var/lib/mysql -R 7.重启mysql mysqldump逻辑备份 远程备份语法: # mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql 本地备份语法: # mysqldump -u用户名 -p密码 数据库名 > 备份文件.sql 常用备份选项 -A #备份所有库 -B #备份多个数据库 -F #备份之前刷新binlog日志 --default-character-set #指定导出数据时采用何种字符集，如果数据表不是采用默认的latin1字符集的话，那么导出时必须指定该选项，否则再次导入数据后将产生乱码问题。 -d #不导出任何数据，只导出数据库表结构。 -f #即使在一个表导出期间得到一个SQL错误，继续。 --lock-tables #备份前，锁定所有数据库表 使用 mysqldump 备份数据库时避免锁表: 对一个正在运行的数据库进行备份请慎重！！ 如果一定要 在服务运行期间备份，可以选择添加 --single-transaction选项， 类似执行： mysqldump --single-transaction -u root -p1 db > mysql.sql • 备份库语法: # mysqldump -u root -p1 db > /db.bak 多个库加-B用空格隔开 备份表语法: # mysqldump -u root -p1 db t > /db.t.bak 多个表用空格隔开 备份表结构：语法：mysqldump -uroot -p1 -d db t > /dump.sql • 恢复库语法：先创建一个库，mysql -uroot -p1 库名 < 备份库存放位置 恢复表语法：set sql_log_bin=0; #停止binlog日志 source 备份表存放的位置 或者 mysql -u root -p1 db < 备份表存放的位置 恢复表结构语法：先创建一个库，mysql -uroot -p1 -D 库名 < 备份表结构的存放位置 • 通过binlog恢复 1.开启binlog日志shi vim /etc/my.cnf log-bin=/var/log/sql-bin/mylog server-id=1 2.创建目录并修改权限 mkdir /var/log/sql-bin chown mysql.mysql /var/log/sql-bin 3.重启mysql systemctl restart mysqld 4.查看要恢复的起始节点和结束节点 mysqlbinlog mylog.000002 5.恢复 mysqlbinlog --start-position 219 --stop-position 321 mylog.000002 | mysql -uroot -p'1'
1、为什么要备份
-
备份：能够防止由于机械故障以及人为误操作带来的数据丢失，例如将数据库文件保存在了其它地方。
-
冗余： 数据有多份冗余，但不等备份，只能防止机械故障带来的数据丢失，例如主备模式、数据库集群。
2.MySQL数据备份需要重视的内容
备份内容 databases Binlog my.conf 所有备份数据都应放在非数据库本地，而且建议有多份副本。 测试环境中做日常恢复演练，恢复较备份更为重要。
备份过程中必须考虑因素：
1. 数据的一致性2. 服务的可用性
3、MySQL 备份类型
1.物理备份： 直接复制数据库文件，适用于大型数据库环境，不受存储引擎的限制，但不能恢复到不同的MySQL版本。 ####1、热备(hot backup) 在线备份，数据库处于运行状态，这种备份方法依赖于数据库的日志文件 - 对应用基本无影响(但是性能还是会有下降,所以尽量不要在主上做备份,在从库上做) ####2、冷备(cold backup) 备份数据文件,需要停机，是在关闭数据库的时候进行的 - 备份 datadir 目录下的所有文件 ####3、温备(warm backup) - 针对myisam的备份(myisam不支持热备),备份时候实例只读不可写，数据库锁定表格（不可写入但可读）的状态下进行的 - 对应用影响很大 - 通常加一个【读锁】 2.逻辑备份： 备份的是建表、建库、插入等操作所执行SQL语句（DDL DML DCL），适用于中小型数据库，效率相对较低。 3.物理和逻辑备份的区别 | | 逻辑备份 | 物理备份 | ---------- | ------------------------------ | ---------------------- | 备份方式 | 备份数据库建表、建库、插入sql语句 | 备份数据库物理文件 | 优点 | 备份文件相对较小,只备份表中的数据与结构 | 恢复速度比较快 | 缺点 | 恢复速度较慢(需要重建索引,存储过程等) | 备份文件相对较大(备份表空间,包含数据与索引) | 对业务影响 | I/O负载加大 | I/O负载加大 | 代表工具 | mysqldump | ibbackup、xtrabackup，mysqlbackup
5、MySQL 备份工具
1、ibbackup - 官方备份工具 - 收费 - 物理备份 2、xtrabackup - 开源社区备份工具 - 开源免费,上面的免费版本(老版本有问题,备份出来的数据可能有问题) - 物理备份 3、mysqldump - 官方自带备份工具 开源免费 - 逻辑备份(速度慢) 4、mysqlbackup - mysql 官方备份工具 - innodb 引擎的表mysqlbackup可以进行热备 - 非innodb表mysqlbackup就只能温备 - 物理备份，备份还原速度快 - 适合大规模数据使用
2.物理备份的方式
1.完全备份-----完整备份： 每次对数据进行完整的备份，即对整个数据库的备份、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是差异备份与增量备份的基础。 优点：备份与恢复操作简单方便，恢复时一次恢复到位，恢复速度快 缺点：占用空间大，备份速度慢
2.增量备份: 每次备份上一次备份到现在产生的新数据 只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。以上次完整备份或上次的增量备份的时间为时间点，仅备份这之间的数据变化. 特点：因而备份的数据量小，占用空间小，备份速度快。但恢复时，需要从上一次的完整备份起按备份时间顺序，逐个备份版本进行恢复，恢复时间长，如中间某次的备份数据损坏，将导致数据的丢失。 ------------------------------------------------- 不是针对数据或者SQL指令进行备份: 是针对mysql服务器的日志文件进行备份 增量备份: 指定时间段开始进行备份., 备份数据不会重复, 而且所有的操作都会备份(大项目都用增量备份)
3.差异备份:只备份跟完整备份不一样的 备份那些自从第一次次完全备份之后被修改过的所有文件，备份的时间起点是从第一次次完整备份起，且以后每次备份都是和【第一次】完整备份进行比较（注意是第一次，不是上一次），备份自第一次完整备份以来所有的修改过的文件。备份数据量会越来越大。 特点：占用空间比增量备份大，比完整备份小，恢复时仅需要恢复第一个完整版本和最后一次的差异版本，恢复速度介于完整备份和增量备份之间。
简单的讲，完整备份就是不管三七二十一，每次都把指定的备份目录完整的复制一遍，不管目录下的文件有没有变化；增量备份就是每次将之前（第一次、第二次、直到前一次）做过备份之后有变化的文件进行备份；差异备份就是每次都将第一次完整备份以来有变化的文件进行备份。
3、percona-xtrabackup 物理备份
Xtrabackup是开源免费的支持MySQL 数据库热备份的软件，在 Xtrabackup 包中主要有 Xtrabackup 和 innobackupex 两个工具。其中 Xtrabackup 只能备份 InnoDB 和 XtraDB 两种引擎; innobackupex则是封装了Xtrabackup，同时增加了备份MyISAM引擎的功能。它不暂停服务创建Innodb热备份；
1、安装xtrabackup
安装xtrabackup # wget http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm # rpm -ivh percona-release-0.1-4.noarch.rpm [root@mysql-server yum.repos.d]# vim percona-release.repo
修改如下内容：将原来的1改为0
[root@mysql-server yum.repos.d]# yum -y install percona-xtrabackup-24.x86_64
注意
如果安装不上报错： 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提供的方式：
======第一种======== [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 7DV3BACj1cBwCf8tckWsvFtQfCP4CiBB50Ku49MU2Nfwq7durfIiePF4IIYRDZgg kHKSfP3oUZBGJx00BujtTobERraaV7lIRIwETZao76MqGt9K1uIqw4NT/jAbi9ce rFaOmAkaujbcB11HYIyjtkAGq9mXxaVqCC3RPWGr+fqAx/akBLQ2UGVyY29uYSBN eVNRTCBEZXZlbG9wbWVudCBUZWFtIDxteXNxbC1kZXZAcGVyY29uYS5jb20+iGAE ExECACAFAksm3aECGwMGCwkIBwMCBBUCCAMEFgIDAQIeAQIXgAAKCRAcTL3NzS79 Kpk/AKCQKSEgwX9r8jR+6tAnCVpzyUFOQwCfX+fw3OAoYeFZB3eu2oT8OBTiVYu5 Ag0ESybdoRAIAKKUV8rbqlB8qwZdWlmrwQqg3o7OpoAJ53/QOIySDmqy5TmNEPLm lHkwGqEqfbFYoTbOCEEJi2yFLg9UJCSBM/sfPaqb2jGP7fc0nZBgUBnFuA9USX72 O0PzVAF7rCnWaIz76iY+AMI6xKeRy91TxYo/yenF1nRSJ+rExwlPcHgI685GNuFG chAExMTgbnoPx1ka1Vqbe6iza+FnJq3f4p9luGbZdSParGdlKhGqvVUJ3FLeLTqt caOn5cN2ZsdakE07GzdSktVtdYPT5BNMKgOAxhXKy11IPLj2Z5C33iVYSXjpTelJ b2qHvcg9XDMhmYJyE3O4AWFh2no3Jf4ypIcABA0IAJO8ms9ov6bFqFTqA0UW2gWQ 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
[root@mysql-server yum.repos.d]# innobackupex --version innobackupex version 2.3.10 Linux (x86_64) (revision id: bd0d4403f36)
======第二种======== 百度搜吧，飞哥这边就是用的这种方式 访问以下链接： https://zhuanlan.zhihu.com/p/140414143
下载完成，把包上传至服务器
2.完全备份流程:
创建备份目录： [root@mysql-server ~]# mkdir /xtrabackup/full -p 备份之前，进入数据库，存入些数据 [root@mysql-server ~]# mysql -uroot -p'qf123' mysql> create database youngfit; mysql> use youngfit; Database changed mysql> create table t1(id int); 备份： [root@mysql-server ~]# innobackupex --user=root --password='qf123' /xtrabackup/full
可以查看一下: [root@mysql-server ~]# cd /xtrabackup/full/ [root@mysql-server full]# ls 2020-12-07_14-31-13 ==================================================================================== 完全备份恢复流程 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/2019-08-20_11-47-49
3.确认数据库目录： 恢复之前需要确认配置文件内有数据库目录指定，不然xtrabackup不知道恢复到哪里 # cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql 4.恢复数据： [root@mysql-server ~]# innobackupex --copy-back /xtrabackup/full/2019-08-20_11-47-49
5.修改权限： [root@mysql-server ~]# chown mysql.mysql /var/lib/mysql -R 启动数据库: [root@mysql-server ~]# systemctl start mysqld 6.确认数据是否恢复 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | youngfit | +--------------------+ 5 rows in set (0.00 sec) mysql> use youngfit; Database changed mysql> show tables; +--------------------+ | Tables_in_youngfit | +--------------------+ | t1 | +--------------------+ 1 row in set (0.00 sec) ===可以看到数据已恢复===
3.增量备份流程
原理：每次备份上一次备份到现在产生的新数据
1.在数据库上面创建一个测试的库
1.完整备份:周一
[root@mysql-server ~]# rm -rf /xtrabackup/* [root@mysql-server ~]# innobackupex --user=root --password='qf123' /xtrabackup [root@mysql-server ~]# cd /xtrabackup/ [root@mysql-server xtrabackup]# ls 2019-08-20_14-51-35 [root@mysql-server xtrabackup]# cd 2019-08-20_14-51-35/ [root@mysql-server 2019-08-20_14-51-35]# ls backup-my.cnf ib_buffer_pool mysql sys testdb xtrabackup_info company ibdata1 performance_schema test xtrabackup_checkpoints xtrabackup_logfile
2、增量备份：周二 —— 周三
在数据库中插入周二的数据: mysql> insert into testdb.t1 values(2); #模拟周二 [root@mysql-server ~]# innobackupex --user=root --password='qf123' --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2019-08-20_14-51-35/ --incremental-basedir:基于哪个增量 [root@mysql-server ~]# cd /xtrabackup/ [root@mysql-server xtrabackup]# ls 2019-08-20_14-51-35 2019-08-20_15-04-29 ---相当于周二的增量备份
在数据库中插入周三的数据: mysql> insert into testdb.t1 values(3); #模拟周三 [root@mysql-server ~]# innobackupex --user=root --password='qf123' --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2019-08-20_15-04-29/ #基于前一天的备份为目录 [root@mysql-server ~]# cd /xtrabackup/ [root@mysql-server xtrabackup]# ls 2019-08-20_14-51-35 2019-08-20_15-04-29 2019-08-20_15-10-56 ---相当于周三的增量备份
查看一下备份目录: [root@mysql-server ~]# ls /xtrabackup/ 2019-08-20_14-51-35 2019-08-20_15-04-29 2019-08-20_15-10-56 全备周一 增量周二 增量周三
增量备份恢复流程 1. 停止数据库 2. 清理环境 3. 依次重演回滚redo log－－> 恢复数据 4. 修改权限 5. 启动数据库
[root@mysql-server ~]# systemctl stop mysqld [root@mysql-server ~]# rm -rf /var/lib/mysql/* 依次重演回滚redo log: [root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2019-08-20_14-51-35 周二 --- 周三 [root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2019-08-20_14-51-35 --incremental-dir=/xtrabackup/2019-08-20_15-04-29 --incremental-dir：增量目录 [root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2019-08-20_14-51-35 --incremental-dir=/xtrabackup/2019-08-20_15-10-56/ 恢复数据: [root@mysql-server ~]# innobackupex --copy-back /xtrabackup/2019-08-20_14-51-35/ 修改权限 [root@mysql-server ~]# chown -R mysql.mysql /var/lib/mysql [root@mysql-server ~]# systemctl start mysqld 登陆上去看一下:
4、差异备份流程
清理备份的环境:
[root@mysql-server ~]# rm -rf /xtrabackup/* 登陆数据库，准备环境 mysql> delete from testdb.t1; mysql> insert into testdb.t1 values(1); #插入数据1，模拟周一 mysql> select * from testdb.t1; +------+ | id | +------+ | 1 | +------+ mysql> \q 查看时间: [root@mysql-server ~]# date Tue Aug 20 15:39:59 CST 2019 1、完整备份：周一 [root@mysql-server ~]# innobackupex --user=root --password='qf123' /xtrabackup 2、差异备份：周二 —— 周三 语法: # innobackupex --user=root --password=888 --incremental /xtrabackup --incremental-basedir=/xtrabackup/完全备份目录（周一） 3.修改时间： [root@mysql-server ~]# date 08211543 Wed Aug 21 15:43:00 CST 2019 4.在登陆mysql： mysql> insert into testdb.t1 values(2); #插入数据2，模拟周二 差异备份周二的 [root@mysql-server ~]# innobackupex --user=root --password='qf123' --incremental /xtrabackup --incremental-basedir=/xtrabackup/2019-08-20_15-42-02/ #备份目录基于周一的备份 5.再次登陆mysql mysql> insert into testdb.t1 values(3); #插入数据，模拟周三 6.在次修改时间 [root@mysql-server ~]# date 08221550 Thu Aug 22 15:50:00 CST 2019 7.在次差异备份 [root@mysql-server ~]# innobackupex --user=root --password='qf123' --incremental /xtrabackup --incremental-basedir=/xtrabackup/2019-08-20_15-42-02/ #还是基于周一的备份 8.延申到周四 mysql> insert into testdb.t1 values(4); 9.修改时间 [root@mysql-server ~]# date 08231553 Fri Aug 23 15:53:00 CST 2019 10.差异备份周四 [root@mysql-server ~]# innobackupex --user=root --password='qf123' --incremental /xtrabackup --incremental-basedir=/xtrabackup/2019-08-20_15-42-02/ #还是基于周一的备份 11.查看一下备份目录 [root@mysql-server ~]# ls /xtrabackup/ 2019-08-20_15-42-02 2019-08-21_15-46-53 2019-08-22_15-51-15 2019-08-23_15-53-28 周一 周二 周三 周四
差异备份恢复流程 1. 停止数据库 2. 清理环境 3. 重演回滚redo log（周一，某次差异）－－> 恢复数据 4. 修改权限 5. 启动数据库 停止数据库 [root@mysql-server ~]# systemctl stop mysqld [root@mysql-server ~]# rm -rf /var/lib/mysql/* 1.恢复全量的redo log 语法: # innobackupex --apply-log --redo-only /xtrabackup/完全备份目录（周一） [root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2019-08-20_15-42-02/ 2.恢复差异的redo log 语法:# innobackupex --apply-log --redo-only /xtrabackup/完全备份目录（周一）--incremental-dir=/xtrabacku/某个差异备份 这里我们恢复周三的差异备份 [root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2019-08-20_15-42-02/ --incremental-dir=/xtrabackup/2019-08-22_15-51-15/ #我们恢复周三的差异备份 3.恢复数据 语法:# innobackupex --copy-back /xtrabackup/完全备份目录（周一） [root@mysql-server ~]# innobackupex --copy-back /xtrabackup/2019-08-20_15-42-02/ 修改权限： [root@mysql-server ~]# chown -R mysql.mysql /var/lib/mysql [root@mysql-server ~]# systemctl start mysqld
登陆mysql查看一下:
只有123.因为我们恢复的是周三的差异备份。
4.mysqldump逻辑备份 ---- 推荐优先使用
mysqldump 是 MySQL 自带的逻辑备份工具。可以保证数据的一致性和服务的可用性。
如何保证数据一致?在备份的时候进行锁表会自动锁表。锁住之后在备份。
本身为客户端工具: 远程备份语法: # mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql 本地备份语法: # mysqldump -u用户名 -p密码 数据库名 > 备份文件.sql
1.常用备份选项
-A, --all-databases #备份所有库 -B, --databases #备份多个数据库 -F, --flush-logs #备份之前刷新binlog日志 --default-character-set #指定导出数据时采用何种字符集，如果数据表不是采用默认的latin1字符集的话，那么导出时必须指定该选项，否则再次导入数据后将产生乱码问题。 --no-data，-d #不导出任何数据，只导出数据库表结构。 --lock-tables #备份前，锁定所有数据库表 --single-transaction #保证数据的一致性和服务的可用性 -f, --force #即使在一个表导出期间得到一个SQL错误，继续。
注意
使用 mysqldump 备份数据库时避免锁表: 对一个正在运行的数据库进行备份请慎重！！ 如果一定要 在服务运行期间备份，可以选择添加 --single-transaction选项， 类似执行： mysqldump --single-transaction -u root -p123456 dbname > mysql.sql
2.备份表
语法: # mysqldump -u root -p1 db1 t1 > /db1.t1.bak [root@mysql-server ~]# mkdir /home/back #创建备份目录 [root@mysql-server ~]# mysqldump -uroot -p'qf123' company employee5 > /home/back/company.employee5.bak 备份多个表： 语法: mysqldump -u root -p1 db1 t1 t2 > /db1.t1_t2.bak [root@mysql-server ~]# mysqldump -uroot -p'qf123' company new_t1 new_t2 > /home/back/company.new_t1_t2.bak
3、备份库
备份一个库：相当于将这个库里面的所有表全部备份。 语法: # mysqldump -u root -p1 db1 > /db1.bak [root@mysql-server ~]# mysqldump -uroot -p'qf123' company > /home/back/company.bak 备份多个库： 语法：mysqldump -u root -p1 -B db1 db2 db3 > /db123.bak [root@mysql-server ~]# mysqldump -uroot -p'qf123' -B company testdb > /home/back/company_testdb.bak 备份所有的库： 语法：# mysqldump -u root -p1 -A > /alldb.bak [root@mysql-server ~]# mysqldump -uroot -p'qf123' -A > /home/back/allbase.bak
到目录下面查看一下：
4、恢复数据库和表
为保证数据一致性，应在恢复数据之前停止数据库对外的服务,停止binlog日志 因为binlog使用binlog日志恢复数据时也会产生binlog日志。
为实验效果先将刚才备份的数据库和表删除了。登陆数据库：
[root@mysql-server ~]# mysql -uroot -pqf123 mysql> show databases;
mysql> drop database company; mysql> \q
5、恢复库
登陆mysql创建一个库 mysql> create database company; 恢复： [root@mysql-server ~]# mysql -uroot -p'qf123' company < /home/back/company.bak
6、恢复表
登陆到刚才恢复的库中将其中的一个表删除掉 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; #停止binlog日志 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.备份表结构： 语法：mysqldump -uroot -p123456 -d database table > dump.sql [root@mysql-server ~]# mysqldump -uroot -p'qf123' -d company employee5 > /home/back/emp.bak 恢复表结构： 登陆数据库创建一个库 mysql> create database t1; 语法：# mysql -u root -p1 -D db1 < db1.t1.bak [root@mysql-server ~]# mysql -uroot -p'qf123' -D t1 < /home/back/emp.bak
登陆数据查看：
8、数据的导入导出,没有表结构。
表的导出和导入只备份表内记录，不会备份表结构，需要通过mysqldump备份表结构，恢复时先恢复表结构，再导入数据。
mysql> show variables like "secure_file_priv"; ----查询导入导出的目录。
修改安全文件目录： 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.导出数据 登陆数据查看数据 mysql> show databases; #找到test库 mysql> use test #进入test库 mysql> show tables; #找到它t3表 mysql> select * from t3 into outfile '/sql/test.t3.bak';
2.数据的导入 先将原来表里面的数据清除掉，只保留表结构 mysql> delete from t3; mysql> load data infile '/sql/test.t3.bak' into table t3; 如果将数据导入别的表，需要创建这个表并创建相应的表结构。
5、通过binlog恢复
开启binlog日志：
[root@mysql-server ~]# vim /etc/my.cnf log-bin=/var/log/sql-bin/mylog server-id=1
创建目录并修改权限
[root@mysql-server ~]# mkdir /var/log/sql-bin [root@mysql-server ~]# chown mysql.mysql /var/log/sql-bin [root@mysql-server ~]# systemctl restart mysqld
mysql> flush logs; #刷新binlog日志会截断产生新的日志文件 mysql> create table testdb.t3(id int); #创建一个表
根据位置恢复
找到要恢复的sql语句的起始位置、结束位置
[root@mysql-server sql-bin]# mysqlbinlog mylog.000002
测试
[root@mysql-server ~]# mysql -uroot -p'qf123' 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'qf123' mysql: [Warning] Using a password on the command line interface can be insecure.
查看：
Day05
十一、mysql优化
三大范式和事务回滚
1原子性:字段不可拆分；【年级/班级； 上课时间/下课时间】 2唯一性：每条记录唯一【比如补考，及格不及格添加流水号；】 3关联性：每列都与主键有关联关系，存在直接传递依赖； START TRANSACTION; 开启事务 COMMIT 提交 ROLLBACK 回滚 Show variables like ‘autocommit’; 显示自动控制； autocommit 自动提交 set autocommit = off/0; 取消自动提交 —————————————————————————————————— 事务操作原理: 事务开启之后, 所有的操作都会临时保存到事务日志, 事务日志只有在得到commit命令才会同步到数据表,其他任何情况都会清空(rollback, 断电, 断开连接) 回滚点: 在某个成功的操作完成之后, 后续的操作有可能成功有可能失败, 但是不管成功还是失败,前面操作都已经成功: 可以在当前成功的位置, 设置一个点: 可以供后续失败操作返回到该位置, 而不是返回所有操作, 这个点称之为回滚点. 设置回滚点语法: savepoint [回滚点名字]p1; 回到回滚点语法: rollback to p1[回滚点名字];
引擎： 查看引擎： 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.数据的应用--怎样取数据，sql语句的优化 3.mysql服务优化--内存的使用，磁盘的使用 4.操作系统的优化--内核 5.升级硬件设备 6.创建索引提升查询速度
mysql常用命令： mysql> show warnings 查看最近一个sql语句产生的错误警告，看其他的需要看.err日志 mysql> show processlist 显示系统中正在运行的所有进程。 mysql> show errors 查看最近一个sql语句产生的错误信息
字符集设置 临时： 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%';
当连接数的数值过小会经常出现ERROR 1040: Too many connections错误。 这是是查询数据库当前设置的最大连接数 mysql> show variables like '%max_connections%'; 强制限制mysql资源设置: # vim /etc/my.cnf max_connections = 1024 并发连接数，根据实际情况设置连接数。 connect_timeout= 5 单位秒 ----超时时间，默认30秒
innodb引擎： innodb-buffer-pool-size //缓存 InnoDB 数据和索引的内存缓冲区的大小 innodb-buffer-pool-size=# ----值 这个值设得越高,访问表中数据需要得磁盘 I/O 越少。在一个专用的数据库服务器上,你可以设置这个参数达机器物理内存大小的 80%。 # vim /etc/my.cnf innodb-buffer-pool-size=2G
4.5.5 索引的使用原则 1. 选择性高的列：对于具有大量唯一值或不重复值的列，索引能够极大地提高查询性能。这种情况下，索引可以帮助数据库快速定位所需数据 2. 经常用作过滤条件的列：对于经常用于 WHERE 子句、JOIN 条件或其他过滤条件的列，建立索引可以提高查询速度。 3. 排序和分组：如果某列经常用于 ORDER BY、GROUP BY 或其他排序和分组操作，为该列创建索引可以提高这些操作的性能。 4. 外键列：如果表之间有外键关系，在外键列上创建索引可以提高连接查询的性能。 5. 覆盖索引：尽量创建包含查询所需所有列的索引，这样查询可以直接在索引中获得所需数据，无需回表查询。这称为“覆盖索引”。 6. 索引维护成本：索引不仅会占用存储空间，还会在插入、更新和删除操作时产生额外的维护成本。因此，在创建索引时要权衡查询性能和维护成本。 7. 避免冗余索引：尽量避免创建重复或相似的索引。冗余索引不仅浪费存储空间，还会增加维护成本。可以定期审查索引并删除不必要的索引。 8. 低选择性列：对于具有较低选择性的列（即重复值较多的列），索引的效果可能不明显。在这种情况下，可以考虑使用其他查询优化方法。 9. 分析查询计划：分析查询计划，以确保数据库实际使用了预期的索引。如果没有使用预期索引，可以调整查询语句或调整索引策略。 10. 适时更新统计信息：统计信息对于数据库优化器选择正确索引至关重要。定期更新统计信息以确保数据库优化器作出正确的索引选择。 4.5.6 索引的意义 主要意义如下： 1. 提高查询速度：通过使用索引，数据库可以快速定位到需要的数据，而无需扫描整个表。这可以大大减少数据查询所需的时间和系统资源。 2. 优化排序和分组：索引可以帮助数据库在执行排序（ORDER BY）和分组（GROUP BY）操作时更高效地处理数据。 3. 加速连接操作：在具有外键关系的表之间进行连接查询时，索引可以显著提高查询性能。 4. 维护数据一致性：使用唯一索引可以确保表中的某列或某些列的唯一性，从而维护数据的完整性和一致性。 5. 改善查询计划：数据库查询优化器依赖于索引和统计信息来生成高效的查询计划。正确使用索引可以帮助查询优化器生成更好的查询计划，从而提高查询性能。 —
十二、AB复制
## GTID主从复制步骤 ``` 准备两台机器：主机器ip：192.168.200.100 从机器ip：192.168.200.200 安装mysql并启动 wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm yum -y install mysql-community-server systemctl start mysqld 主机器操作步骤： vim /etc/my.cnf server-id=1 #定义server id log-bin = mylog #开启binlog日志 gtid_mode = ON #开启gtid enforce_gtid_consistency=1 #强制gtid 重启mysql 创建授权用户 grant replication slave on *.* to 'su'@'%' identified by '1'; flush privileges; 从机器操作步骤： vim /etc/my.cnf server-id=2 gtid_mode = ON enforce_gtid_consistency=1 master-info-repository=TABLE relay-log-info-repository=TABLE 重启mysql并登录mysql \e change master to master_host='主机器ip', #主ip 地址 最好用域名 master_user='su', #主服务上面创建的用户 master_password='1', #用户密码 master_auto_position=1; start salve; 启动slave角色 show slave status\G #查看状态，验证sql和IO是不是yes。 ``` ### binlog日志方式步骤 ``` 准备两台机器：主机器ip：192.168.200.100 从机器ip：192.168.200.200 安装mysql并启动 wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm yum -y install mysql-community-server systemctl start mysqld 主机器操作步骤： vim/etc/my.cnf [mysqld] log-bin=/var/log/mysql/mysql-bin server-id=1 创建目录并修改权限 mkdir /var/log/mysql chown mysql.mysql /var/log/mysql -R 重启mysql 创建主从同步的用户 grant replication slave on *.* to 'su'@'%' identified by '1'; flush privileges; show master status\G 查看binlog日志和节点位置 从机器操作步骤： vim /etc/my.cnf server-id=2 重启mysql \e CHANGE MASTER TO MASTER_HOST='主机器ip', #主服务器ip MASTER_USER='su', #主服务器用户 MASTER_PASSWORD='1', #用户密码 MASTER_LOG_FILE='mysql-bin.000001', #binlog日志文件名称 MASTER_LOG_POS=849; #日志位置 start slave; 启动slave角色 show slave status\G #查看状态，验证sql和IO是不是yes。
一、什么是主从复制?
1、主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库；主数据库一般是准实时的业务数据库。
2、主从复制的作用
1.做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。 2.架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率,提高单个机器的I/O性能。 3.读写分离，使数据库能支撑更大的并发。 1--在从服务器可以执行查询工作(即我们常说的读功能)，降低主服务器压力;（主库写，从库读，降压） 2--在从服务器进行备份，避免备份期间影响主服务器服务;（确保数据安全）
二、主从复制原理
原理： 实现整个主从复制，需要由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 负责写数据
步骤一：主库db的更新事件(update、insert、delete)被写到binlog 步骤二：从库发起连接，连接到主库 步骤三：此时主库创建一个binlog dump thread线程，把binlog的内容发送到从库 步骤四：从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log. 步骤五：还会创建一个SQL线程，从relay log里面读取内容，将更新内容写入到slave的db.
面试： 1.主从复制延迟大比较慢原因： 主服务器配置高，从服务器的配置低。 并发量大导致主服务器读的慢。从服务器写的慢 网络延迟比较高 从服务器的读写速度慢 2.从数据库的读的延迟问题了解吗？如何解决？ 解决方法： 半同步复制—解决数据丢失的问题 并行复制—-解决从库复制延迟的问题
三、M-S 架构GTID 基于事务ID复制
1、什么是GTID？
全局事务标识：global transaction identifiers 是用来代替传统复制的方法，GTID复制与普通复制模式的最大不同就是不需要指定二进制文件名和位置。
2、GTID工作原理
1、master更新数据时，会在事务前产生GTID，一同记录到binlog日志中。 2、slave端的i/o 线程将变更的binlog，写入到本地的relay log中。 3、sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录。 4、如果有记录，说明该GTID的事务已经执行，slave会忽略。 5、如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog。
3、部署主从复制
1、架构：
2、准备环境两台机器，关闭防火墙和selinux。---两台机器环境必须一致。时间也得一致
192.168.246.129 mysql-master 192.168.246.128 mysql-slave
两台机器安装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 配置并修改密码 略....
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 [root@mysql-master ~]# systemctl restart mysqld #重启 主服务器创建账户： mysql> grant replication slave,reload,super on *.* to 'slave'@'%' identified by 'Qf@12345！'; #注:生产环境中密码采用高级别的密码，实际生产环境中将'%'换成slave的ip mysql> flush privileges; 注意:如果不成功删除以前的binlog日志 replication slave：拥有此权限可以查看从服务器，从主服务器读取二进制日志。 super权限：允许用户使用修改全局变量的SET语句以及CHANGE MASTER语句 reload权限：必须拥有reload权限，才可以执行flush [tables | logs | privileges]
slave操作： [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 [root@mysql-slave ~]# systemctl restart mysqld [root@mysql-slave ~]# mysql -uroot -p'qf123' #登陆mysql mysql> \e change master to master_host='master1', #主ip 地址 最好用域名 master_user='授权用户', #主服务上面创建的用户 master_password='授权密码', master_auto_position=1; -> ; Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave; #启动slave角色 Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G #查看状态，验证sql和IO是不是yes。
测试
在slave上面查看一下有没有同步过去：
[root@mysql-slave ~]# mysql -uroot -p'qf123' mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ mysql> use test mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | t1 | +----------------+ mysql> select * from t1; +------+ | id | +------+ | 1 | +------+
主从同步完成。
注意: 在关闭和启动mysql服务的时候按顺序先启动master。
可以测试，先将slave库停掉，再停止master库。启动先启动master库，再启动slave库，如果数据没发生改变，也就意味着binlog日志位置没有变化，主从理论上不会失效
面试题
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地址，测试应用连接是否正常
四、主从复制binlog日志方式
192.168.246.135 mysql-master 192.168.246.136 mysql-slave 准备两台机器，关闭防火墙和selinux。---两台机器环境必须一致。时间也得一致
两台机器配置hosts解析 192.168.246.135 mysql-master 192.168.246.136 mysql-slave 两台机器安装mysql # wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm 略... [root@mysql-master ~]# systemctl start mysqld [root@mysql-master ~]# systemctl enable mysqld
开始配置主服务
1、在主服务器上，必须启用二进制日志记录并配置唯一的服务器ID。需要重启服务器。
编辑主服务器的配置文件 my.cnf，添加如下内容
添加配置 [mysqld] log-bin=/var/log/mysql/mysql-bin server-id=1
创建日志目录并赋予权限
[root@mysql-master ~]# mkdir /var/log/mysql [root@mysql-master ~]# chown mysql.mysql /var/log/mysql
重启服务
[root@mysql-master ~]# systemctl restart mysqld
查找密码
[root@mysql-master ~]# grep pass /var/log/mysqld.log
修改密码
[root@mysql-master ~]# mysqladmin -uroot -p'Ns0_3jgPIM*5' password 'Qf@12345!' 创建主从同步的用户： mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' identified by 'Qf@12345!'; mysql> flush privileges;
在主服务器上面操作
mysql> show master status\G
在从服务上面操作：
my.cnf配置文件
[mysqld] server-id=2 重启服务 [root@mysql-slave ~]# systemctl restart mysqld 设置密码 [root@mysql-slave ~]# grep pass /var/log/mysqld.log [root@mysql-slave ~]# mysqladmin -uroot -p'ofeUcgA)4/Yg' password 'Qf@12345!' 登录mysql [root@mysql-slave ~]# mysql -uroot -p'Qf@12345!' mysql> \e CHANGE MASTER TO MASTER_HOST='mysql-master', MASTER_USER='repl', MASTER_PASSWORD='Qf@12345!', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=849; -> ; mysql> start slave; mysql> show slave status\G
参数解释： CHANGE MASTER TO MASTER_HOST='master2.example.com', #主服务器ip MASTER_USER='replication', #主服务器用户 MASTER_PASSWORD='password', #用户密码 MASTER_PORT=3306, #端口 MASTER_LOG_FILE='master2-bin.001', #binlog日志文件名称 MASTER_LOG_POS=4, #日志位置
在master上面执行：
mysql> create database testdb; #创建一个库 Query OK, 1 row affected (0.10 sec) mysql> \q
故障排错
#### UUID一致，导致主从复制I/O线程不是yes > 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并重启服务
作业
一主双从（gtid方式），binlog日志方式：
在准备一台虚拟机做为slave2，关闭防火墙和selinux 192.168.246.130 #mysql-slave2
1.由于刚才测试已经有了数据，需要先将master服务上面的数据备份出来。导入slave2的mysql中。这样才能保证集群中的机器环境一致。 在master操作： [root@mysql-master ~]# mysqldump -uroot -p'qf123' --set-gtid-purged=OFF test > test.sql [root@mysql-master ~]# ls test.sql [root@mysql-master ~]# scp test.sql 192.168.246.130:/root/ #拷贝到slave2
#### 开启 GTID 后的导出导入数据的注意点 > Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events 意思是： 当前数据库实例中开启了 GTID 功能, 在开启有 GTID 功能的数据库实例中, 导出其中任何一个库, 如果没有显示地指定--set-gtid-purged参数, 都会提示这一行信息. 意思是默认情况下, 导出的库中含有 GTID 信息, 如果不想导出包含有 GTID 信息的数据库, 需要显示地添加--set-gtid-purged=OFF参数. mysqldump -uroot -p --set-gtid-purged=OFF --all-databases > alldb.db 导入数据是就可以相往常一样导入了。
slave2操作： 安装mysql5.7 [root@mysql-slave2 ~]# wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm 安装略... [root@mysql-slave2 ~]# systemctl start mysqld [root@mysql-slave2 ~]# systemctl enable mysqld 登陆mysql创建一个test的库 [root@mysql-slave2 ~]# mysql -uroot -p'Qf@12345!' mysql> create database test; [root@mysql-slave2 ~]# mysql -uroot -p'Qf@12345!' test < test.sql #将数据导入。
开始配置slave2 [root@mysql-slave2 ~]# vim /etc/my.cnf server-id=3 #每台机器的id不一样 gtid_mode = ON enforce_gtid_consistency=1 master-info-repository=TABLE relay-log-info-repository=TABLE [root@mysql-slave2 ~]# systemctl restart mysqld [root@mysql-slave2 ~]# mysql -uroot -p'Qf@12345!' mysql> \e change master to master_host='192.168.246.129', master_user='slave', master_password='123', master_auto_position=1; -> ; mysql> start slave; #将slave启动起来 mysql> show slave status\G #查看一下状态
测试：
在master上面在创建一个库：
[root@mysql-master ~]# mysql -uroot -p'qf123' mysql> create database qfedu; mysql> create table qfedu.t1(id int); mysql> insert into qfedu.t1 values (1); mysql>
两台slave
主从同步完成！
注意: 在关闭和启动mysql服务的时候按顺序先启动master。
十三、读写分离
1.什么是读写分离
-
在数据库集群架构中，让主库负责处理写入操作，而从库只负责处理select查询，让两者分工明确达到提高数据库整体读写性能。当然，主数据库另外一个功能就是负责将数据变更同步到从库中，也就是写操作。
2. 读写分离的好处
1. 分摊服务器压力，提高机器的系统处理效率 2. 在写入不变，大大分摊了读取，提高了系统性能。另外，当读取被分摊后，又间接提高了写入的性能。所以，总体性能提高了。 3. 增加冗余，提高服务可用性，当一台数据库服务器宕机后可以调整另外一台从库以最快速度恢复服务
Mycat 数据库中间件
Mycat 是一个开源的数据库系统，但是由于真正的数据库需要存储引擎，而 Mycat 并没有存 储引擎，所以并不是完全意义的数据库系统。 那么 Mycat 是什么？Mycat 是数据库中间件，就是介于数据库与应用之间，进行数据处理与交互的中间服 务是实现对主从数据库的读写分离、读的负载均衡。
常见的数据库中间件:
MyCAT 是使用 JAVA 语言进行编写开发，使用前需要先安装 JAVA 运行环境(JRE),由于 MyCAT 中使用了 JDK7 中的一些特性，所以要求必须在 JDK7 以上的版本上运行。
准备一台新的主机放到master的前面做代理 192.168.246.133 mysql-mycat 并将三台机器互做本地解析
架构
这里是在mysql主从复制实现的基础上，利用mycat做读写分离，架构图如下
部署环境：
安装jdk
下载jdk账号： 账号：liwei@xiaostudy.com 密码：OracleTest1234 将jdk上传到服务器中， [root@mycat ~]# tar xzf jdk-8u221-linux-x64.tar.gz -C /usr/local/ [root@mycat ~]# cd /usr/local/ [root@mycat local]# mv jdk1.8.0_221/ java 设置环境变量 [root@mycat local]# vim /etc/profile #添加如下内容， JAVA_HOME=/usr/local/java PATH=$JAVA_HOME/bin:$PATH export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar [root@mycat local]# source /etc/profile
部署mycat
下载 [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
认识配置文件 MyCAT 目前主要通过配置文件的方式来定义逻辑库和相关配置: /usr/local/mycat/conf/server.xml #定义用户以及系统相关变量，如端口等。其中用户信息是前端应用程序连接 mycat 的用户信息。 /usr/local/mycat/conf/schema.xml #定义逻辑库，表、分片节点等内容。
配置server.xml
以下为代码片段
下面的用户和密码是应用程序连接到 MyCat 使用的，可以自定义配置
而其中的schemas 配置项所对应的值是逻辑数据库的名字，也可以自定义，但是这个名字需要和后面 schema.xml 文件中配置的一致。
[root@mycat ~]# cd /usr/local/mycat/conf/ [root@mycat conf]# vim server.xml ... <!--下面的用户和密码是应用程序连接到 MyCat 使用的.schemas 配置项所对应的值是逻辑数据库的名字,这个名字需要和后面 schema.xml 文件中配置的一致。--> <user name="root" defaultAccount="true"> <property name="password">Qf@12345!</property> <property name="schemas">testdb</property> <!-- 表级 DML 权限设置 --> <!-- <privileges check="false"> <schema name="TESTDB" dml="0110" > <table name="tb01" dml="0000"></table> <table name="tb02" dml="1111"></table> </schema> </privileges> --> </user> <!-- <!--下面是另一个用户，并且设置的访问 TESTED 逻辑数据库的权限是 只读。可以注释掉 <user name="user"> <property name="password">user</property> <property name="schemas">TESTDB</property> <property name="readOnly">true</property> </user> --> </mycat:server>
== 上面的配置中，假如配置了用户访问的逻辑库，那么必须在 schema.xml 文件中也配置这个逻辑库，否则报错，启动 mycat 失败 ==
飞哥特意更换的配置：
配置schema.xml
以下是配置文件中的每个部分的配置块儿
逻辑库和分表设置
<schema name="testdb" // 逻辑库名称,与server.xml的一致 checkSQLschema="false" // 不检查sql sqlMaxLimit="100" // 最大连接数 dataNode="dn1"> // 数据节点名称 <!--这里定义的是分表的信息--> </schema>
数据节点
<dataNode name="dn1" // 此数据节点的名称 dataHost="localhost1" // 主机组虚拟的 database="testdb" /> // 真实的数据库名称
主机组
<dataHost name="localhost1" // 主机组 maxCon="1000" minCon="10" // 连接 balance="0" // 负载均衡 writeType="0" // 写模式配置 dbType="mysql" dbDriver="native" // 数据库配置 switchType="1" slaveThreshold="100"> <!--这里可以配置关于这个主机组的成员信息，和针对这些主机的健康检查语句--> </dataHost>
balance 属性 负载均衡类型,目前的取值有 3 种: 1. balance="0", 不开启读写分离机制,所有读操作都发送到当前可用的 writeHost 上。 2. balance="1", 全部的 readHost 与 writeHost 参与 select 语句的负载均衡,简单的说,当双主双从模式(M1->S1,M2->S2,并且 M1 与 M2 互为主备),正常情况下,M2,S1,S2 都参与 select 语句的负载均衡。 3. balance="2", 所有读操作都随机的在 writeHost、readhost 上分发。 4. balance="3", 所有读请求随机的分发到 wiriterHost 对应的 readhost 执行,writerHost 不负担读压力, #注意 balance=3 只在 1.4 及其以后版本有,1.3 没有。 writeType 属性 负载均衡类型 1. writeType="0", 所有写操作发送到配置的第一个 writeHost,第一个挂了切换到还生存的第二个writeHost,重新启动后已切换后的为准. 2. writeType="1",所有写操作都随机的发送到配置的 writeHost,#版本1.5 以后废弃不推荐。
健康检查
<heartbeat>select user()</heartbeat> #对后端数据进行检测，执行一个sql语句，user()内部函数
读写配置
<writeHost host="hostM1" url="192.168.246.135:3306" user="mycat" password="Qf@12345!"> <!-- can have multi read hosts --> <readHost host="hostS2" url="192.168.246.136:3306" user="mycat" password="Qf@12345!" /> </writeHost>
以下是组合为完整的配置文件，适用于一主一从的架构
[root@mycat ~]# cd /usr/local/mycat/conf/ [root@mycat conf]# cp schema.xml schema.xml.bak [root@mycat conf]# vim schema.xml <?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="testdb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"> </schema> <dataNode name="dn1" dataHost="localhost1" database="testdb" /> <dataHost name="localhost1" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <!-- can have multi write hosts --> <writeHost host="mysql-master" url="mysql-master:3306" user="mycat" password="Qf@1234!"> <!-- can have multi read hosts --> <readHost host="mysql-slave" url="mysql-slave:3306" user="mycat" password="Qf@1234!" /> </writeHost> </dataHost> </mycat:schema>
飞哥特意更换的配置，因为我这边是两从节点，所以配置了两个读节点：
在真实的 master 数据库上给用户授权
mysql> grant all on testdb.* to mycat@'%' identified by 'Qf@456789'; mysql> flush privileges;
在mycat的机器上面测试mycat用户登录：
安装mysql的客户端： # yum install -y mysql # mysql -umycat -p'Qf@456789' -h mysql-master
启动Mycat
启动之前需要调整JVM
在wrapper.conf中添加 [root@mycat mycat]# cd conf/ [root@mycat conf]# vim wrapper.conf #在设置JVM哪里添加如下内容 wrapper.startup.timeout=300 //超时时间300秒 wrapper.ping.timeout=120 启动: [root@mycat conf]# /usr/local/mycat/bin/mycat start #需要稍微等待一会 Starting Mycat-server... [root@mycat ~]# jps #查看mycat是否启动 13377 WrapperSimpleApp 13431 Jps [root@mycat ~]# netstat -lntp | grep java
测试mycat
将master当做mycat的客户端 [root@mysql-master ~]# mysql -uroot -h mysql-mycat -p'Qf@456789' -P 8066
如果在show table报错： mysql> show tables; ERROR 3009 (HY000): java.lang.IllegalArgumentException: Invalid DataSource:0
解决方式： 登录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权限为*.*
truncate 与 delete、drop
delete语句是DML语言，操作后会放在 rollback segement 中，事物提交后才生效，如果有相应的触发器(trigger)，执行时将被触发，可回滚。truncate、drop 是DDL语言，执行后会自动提交立即生效，原数据不会放到 rollback中，不能回滚，操作不会触发trigger。 Delete 语句每次删除一行，并在事务日志中为所删除的每行记录一项。Truncate Table 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。 truncate和 delete 只删除表的数据(定义)，表结构及其约束、索引等保持不变；drop语句将删除表的结构、被依赖的约束(constrain)、触发器 (trigger)、索引(index)，依赖于该表的存储过程/函数将保留，但是变为invalid状态。 truncate后会使表和索引所占用的空间会恢复到初始大小；delete操作不会减少表或索引所占用的空间，drop语句将表所占用的空间全释放掉。 Truncate Table 在功能上与不带 Where子句的 Delete 语句相同：二者均删除表中的全部行，但 Truncate Table 比 Delete 速度快，且使用的系统和事务日志资源少。 truncate只能作用于表；delete，drop可作用于表、视图。truncate 清空表中所有数据；drop一般用于删除整体性数据 ，如表、模式、索引、视图、完整性限制等；delete用于删除局部性数据 如表中的某一元组。 在安全性方面，要谨慎使用 drop、truncate，特别是没有做备份的情况下：如何表数据完全不需要时可以用truncate；如果想删除部分数据可使用 delete 需要带上 where子句，回滚段要足够大；如果想删除表可以用 drop；如果想保留表而将所有数据删除且和事务无关，用truncate即可；如果和事物有关，或者想触发 trigger，则使用delete；如果是整理表内部的碎片，可以用 truncate 跟上 reuse stroage，再重新导入、插入数据
delete语句删除数据的原理（delete属于DML语句！！！） 表中的数据被删除了，但是这个数据在硬盘上的真实存储空间不会被释放！！！ 这种删除缺点是：删除效率比较低。 这种删除优点是：支持回滚，后悔了可以再恢复数据！！！ 1.2 使用truncate删除 这种删除效率比较高，表被一次截断，物理删除。 这种删除缺点：不支持回滚。 这种删除优点：快速。 大表非常大，有上亿条记录 删除的时候，使用delete，也许需要执行1个小时才能删除完！效率较低。 可以选择使用truncate删除表中的数据。只需要不到1秒钟的时间就删除结束。效率较高。 但是使用truncate之前，必须仔细询问客户是否真的要删除，并警告删除之后不可恢复！ truncate是删除表中的数据，表还在；
扫一扫
4557
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
