数据库基础及安装-1 数据库1.数据库原理1.1.E-R模型E-R图模型的组成是由实体，属性和联系三部份组成。实体：( Entity ) 实体是数据的使用者，代表软件系统中客观存在的生活中的实物，同一类实体构成实体集。 在ER图中，实体用矩形表示。 属性：( Attribute ) 实体中的所有特性称为属性，每个属性描述的是实体的单个特性。在ER图中，属性用椭圆形表示。 联系：( Relationship ) 描述了实体的属性之间的关联规则。在ER图中，联系用菱形表示。实体间的联系有三种类型：一对一联系 ( 1:1 )：例如,一个学号只能分配给一个同学,每个同学都有一个学号，则学号与同学的联系是一对一。 一对多联系 ( 1:n )：例如,一个老师可以教授多门课程,每门课程只能有一个老师教授,则老师与课程的联系是一对多。 多对多联系 ( m:n )：例如,一个同学可以报名多门课程，每门课程下有多个同学，则同学与课程的联系是多对多。1.2.设计数据库的基本原则--三大范式第一范式 1NF (确保每列保持原子性) 第二范式 2NF (确保表中的每列都和主键相关) 第三范式 3NF (确保每列都和主键列直接相关，而不是间接相关)1.3.MySQL执行查询语句执行流程MySQL数据库中的SQL查询语句执行流程1 接收查询语句： 用户通过客户端或编程语言框架里面的驱动模块，经由专门的通信协议，向MySQL服务器发送SQL查询语句。 如果是mysql服务端，这里的通信协议端口是 3306 数据库服务端内部的连接器接收查询请求后，首先到本地的缓存记录中检索信息 如果有信息，则直接返回查询结果 如果没有信息，则将信息直接传递给后端的数据库解析器来进行处理2 词法分析和语法分析： MySQL服务器首先会对接收到的查询语句进行词法分析，将其分解成一系列的词法单元。 接着，进行语法分析，检查这些词法单元是否符合SQL语法规则。如果语法错误，MySQL会立即返回错误信息。3 语义分析： 在语法分析之后，MySQL会进行语义分析，检查查询语句中的表、列、函数等是否存在，以及用户是否有足够的权限执行该查询。 语义分析还会检查查询语句中的数据类型是否匹配，以及是否存在违反数据库完整性的约束。4 查询优化： MySQL的查询优化器会对查询语句进行优化，决定最优的执行查询计划 -- 也就是怎么走会效率更快。 优化过程包括选择最佳的访问路径、索引、连接顺序等，以最小化查询的执行时间和资源消耗。 优化器还会考虑统计信息，如表的行数、索引的分布等，以做出更明智的决策。5 查询执行： 优化后的查询计划被传递给执行器进行执行。执行器根据查询计划访问存储引擎，获取数据。 存储引擎将文件系统里面的数据库文件数据，加载到内存，然后在内存中做一次聚合 然后，临时放到查询缓存中，便于下次遇到相同的查询语句重复过来。 在执行过程中，MySQL可能会使用缓存来加速查询的执行。从MySQL 8.0开始，查询缓存已被移除。6 结果集返回： 执行器将查询结果集返回给客户端。结果集可以包含零行或多行数据，具体取决于查询的内容。 如果查询涉及聚合函数（如SUM、AVG等）或分组操作（如GROUP BY），执行器还会在返回结果之前对这些数据进行相应的处理。7 错误处理和日志记录： 如果在执行过程中遇到任何错误（如权限不足、表不存在等），MySQL会返回相应的错误信息给客户端。 同时，MySQL还会记录查询的执行日志，以便进行性能分析和故障排查。主要区别：MySQL 5.7 及之前版本查询缓存常被使用，但存在一些问题（如命中率低、维护开销大）。 MySQL 8.0 弃用查询缓存，避免相关开销。-- 查询2023年每个城市中，购买了“电子产品”类别商品，且订单总金额大于1000元的客户信息 -- 以及他们对应的订单信息和购买的商品明细 SELECT c.customer_name, c.customer_city, o.order_id, o.order_date, o.total_amount, p.product_name, oi.quantity, oi.unit_price FROM customers c -- 连接orders表，获取客户的订单信息 JOIN orders o ON c.customer_id = o.customer_id -- 连接order_items表，获取订单中的商品明细信息 JOIN order_items oi ON o.order_id = oi.order_id -- 连接products表，获取商品的名称等信息 JOIN products p ON oi.product_id = p.product_id -- 连接categories表，获取商品所属类别信息 JOIN categories cat ON p.category_id = cat.category_id WHERE -- 筛选出2023年的订单 YEAR(o.order_date) = 2023 AND -- 筛选出商品类别为“电子产品”的记录 cat.category_name = '电子产品' AND -- 筛选出订单总金额大于1000元的订单 o.total_amount > 1000 GROUP BY -- 按照客户姓名、城市、订单ID、订单日期、订单总金额、商品名称进行分组 c.customer_name, c.customer_city, o.order_id, o.order_date, o.total_amount, p.product_name ORDER BY -- 按照客户所在城市升序、订单总金额降序排序 c.customer_city ASC, o.total_amount DESC;对于这个大的流程来说，专用协议之后的功能，全部由数据库软件来实现，而我们需要做的是前面的各种sql语句的编写。2.Mysql安装方式mysql主流的四种安装方式安装方式说明包管理器进行安装配置好mysql仓库员，用包管理器进行在线安装二进制包本地安装下载已编译完成的压缩包，展开至特定路径，并经过简单配置后即可使用源码编译安装1下载指定版本源码在本地进行编译安装源码编译安装2下载指定版本源码【已编译完毕生成可执行文件】在本地进行环境初始化即可3.关于软件mysql和mariadb安装文件前后对比注意：两个环境安装不同的mysql和mariadb 配置文件的名称和位置都不一样 但是，配置内容，基本上都是一样。 mysql 是 多线程工作模式MysqlRockyubuntu软件名mysql-servermysql-server服务名mysqld.servicemysql.service入口文件/etc/my.cnf/etc/mysql/my.cnf配置目录/etc/my.cnf.d/etc/mysql/mysql.conf.d服务配置/etc/my.cnf.d/mysql-server.cnf/etc/mysql/mysql.conf.d/mysqld.cnf端口:::3306 :::33060127.0.0.1:3306 127.0.0.1:33060MariadbRockyubuntu软件名mariadb-servermariadb-server服务名mariadb.servicemariadb.service入口文件/etc/my.cnf/etc/mysql/my.cnf配置目录/etc/my.cnf.d/etc/mysql/mariadb.conf.d服务配置/etc/my.cnf.d/mariadb-server.cnf/etc/mysql/mariadb.conf.d/50-server.cnf端口:::3306127.0.0.1:33064.包管理进行安装4.1.光盘镜像Rocky9 系列光盘镜像自带 mysql-server 8.0 和 mariadb-server 10.3挂载镜像 [root@Rocky9-12 ~]# mount /dev/cdrom /opt/ mount: /opt: WARNING: source write-protected, mounted read-only. [root@Rocky9-12 ~]# ls /opt/ AppStream BaseOS EFI images isolinux LICENSE media.repo 查看数据库软件 [root@Rocky9-12 ~]# ls /opt/AppStream/Packages/m/mysql-ser* /opt/AppStream/Packages/m/mysql-server-8.0.36-1.el9_3.x86_64.rpm [root@Rocky9-12 ~]# ls /opt/AppStream/Packages/m/mariadb-server-1* /opt/AppStream/Packages/m/mariadb-server-10.11.6-1.module+el9.4.0+20012+a68bdff7.x86_64.rpm /opt/AppStream/Packages/m/mariadb-server-10.5.22-1.el9_2.x86_64.rpm其他信息：CentOS7 系列光盘镜像自带 mariadb-server 5.5 CentOS6 系列光盘镜像自带 mysql-server 5.1[21:37:13 root@centos7 ~]# mount /dev/cdrom /opt mount: /dev/sr0 is write-protected, mounting read-only [21:37:27 root@centos7 ~]# ls /opt/ CentOS_BuildTag EULA images LiveOS repodata RPM-GPG-KEY-CentOS-Testing-7 EFI GPL isolinux Packages RPM-GPG-KEY-CentOS-7 TRANS.TBL 查看数据库软件 [21:38:19 root@centos7 ~]# ls /opt/Packages/mariadb-server-5.5.68-1.el7.x86_64.rpm /opt/Packages/mariadb-server-5.5.68-1.el7.x86_64.rpm4.2.官方yum源源配置地址mariadb 官方源配置页面https://mariadb.org/download/?t=repo-configmysql 官方源配置页面https://dev.mysql.com/downloads阿里云 mysql 源配置页面https://mirrors.aliyun.com/mysql/阿里云 mariadb 源配置页面https://mirrors.aliyun.com/mariadb/清华 mysql 源配置页面https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/清华 mariadb 源配置页面https://mirrors.tuna.tsinghua.edu.cn/mariadb/yum/[root@Rocky9-12 ~]# yum list "mysql" mysql.x86_64 8.0.41-2.el9_5 [root@Rocky9-12 ~]# yum list "mariadb" mariadb.x86_64 3:10.5.27-1.el9_5 4.3.mariadb源如何获取最新的mariadb软件源，参考mariadb 官方源配置页面，选择版本和系统发行版本 注意：目前版本最新为 11.8 官网：https://mariadb.org/download/4.4.mysql源如何获取最新的mysql软件源，参考mysql官方源配置页面 官网：http://dev.mysql.com/downloads/点击需要使用功能的源类型，然后下载对应的包就可以了 MySQL Community Downloads 即 MySQL 社区版下载General Availability (GA) Releases：意思是 “通用版本发布” 通常指软件经过全面测试、达到稳定状态，可供广大用户在生产环境等正式场景使用的版本，按钮为橙色文字。 Archives：意为 “存档” 这里一般指软件过往旧版本的集合，供有特定需求（如兼容性需求）的用户下载使用，按钮为蓝色文字。apt源中版本最新版本是8.34-1yum源版本最新版本是8.0.4x版本结果显示： 社区版，在 APT 源中，目前主流的是8版本 在其他的下载页面，虽然存在9.1.0版本，但是LTS依然是8版本5.Rocky9中安装mysql8.05.1.软件信息获取mysql软件源 [root@Rocky9-12 ~]# yum list mysql mysql-server mysql.x86_64 8.0.41-2.el9_5 appstream mysql-server.x86_64 8.0.41-2.el9_5 appstream获取mariadb的软件源 [root@Rocky9-15 ~]# yum list mariadb mariadb-server mariadb.x86_64 3:10.5.27-1.el9_5 appstream mariadb-server.x86_64 3:10.5.27-1.el9_5 appstream5.2.安装软件安装 mysql-server，会自动安装客户端包【安装客户端不会安装服务端软件】[root@Rocky9-12 ~]# yum install -y mysql-server服务状态 [root@Rocky9-12 ~]# systemctl status mysqld.service ○ mysqld.service - MySQL 8.0 database server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; preset: disabled) Active: inactive (dead)启动服务 [root@Rocky9-12 ~]# systemctl enable --now mysqld Created symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /usr/lib/systemd/system/mysqld.service.再次查看 [root@Rocky9-12 ~]# systemctl status mysqld.service ● mysqld.service - MySQL 8.0 database server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; preset: disabled) Active: active (running) 5.3.环境查看检测mysql的进程状态多线程模式 [root@Rocky9-12 ~]# pstree | grep mysql |-mysqld---36*[{mysqld}]检测其他信息自动创建的账户 [root@Rocky9-12 ~]# getent passwd mysql mysql:x:27:27:MySQL Server:/var/lib/mysql:/sbin/nologin查看mysql真正的家目录 [root@Rocky9-12 ~]# ll /var/lib/mysql total 91604 -rw-r----- 1 mysql mysql 56 May 7 21:58 auto.cnf -rw-r----- 1 mysql mysql 157 May 7 21:58 binlog.000001 -rw-r----- 1 mysql mysql 16 May 7 21:58 binlog.index -rw------- 1 mysql mysql 1705 May 7 21:58 ca-key.pem -rw-r--r-- 1 mysql mysql 1112 May 7 21:58 ca.pem -rw-r--r-- 1 mysql mysql 1112 May 7 21:58 client-cert.pem -rw------- 1 mysql mysql 1705 May 7 21:58 client-key.pem -rw-r----- 1 mysql mysql 196608 May 7 22:00 '#ib_16384_0.dblwr' -rw-r----- 1 mysql mysql 8585216 May 7 21:58 '#ib_16384_1.dblwr' -rw-r----- 1 mysql mysql 6238 May 7 21:58 ib_buffer_pool -rw-r----- 1 mysql mysql 12582912 May 7 21:58 ibdata1 -rw-r----- 1 mysql mysql 12582912 May 7 21:58 ibtmp1 drwxr-x--- 2 mysql mysql 4096 May 7 21:58 '#innodb_redo' drwxr-x--- 2 mysql mysql 187 May 7 21:58 '#innodb_temp' drwxr-x--- 2 mysql mysql 143 May 7 21:58 mysql -rw-r----- 1 mysql mysql 26214400 May 7 21:58 mysql.ibd srwxrwxrwx 1 mysql mysql 0 May 7 21:58 mysql.sock -rw------- 1 mysql mysql 5 May 7 21:58 mysql.sock.lock -rw-r--r-- 1 mysql mysql 7 May 7 21:58 mysql_upgrade_info srwxrwxrwx 1 mysql mysql 0 May 7 21:58 mysqlx.sock -rw------- 1 mysql mysql 5 May 7 21:58 mysqlx.sock.lock drwxr-x--- 2 mysql mysql 8192 May 7 21:58 performance_schema -rw------- 1 mysql mysql 1705 May 7 21:58 private_key.pem -rw-r--r-- 1 mysql mysql 452 May 7 21:58 public_key.pem -rw-r--r-- 1 mysql mysql 1112 May 7 21:58 server-cert.pem -rw------- 1 mysql mysql 1705 May 7 21:58 server-key.pem drwxr-x--- 2 mysql mysql 28 May 7 21:58 sys -rw-r----- 1 mysql mysql 16777216 May 7 22:00 undo_001 -rw-r----- 1 mysql mysql 16777216 May 7 22:00 undo_002查看数据库的数据文件 [root@Rocky9-12 ~]# ls /var/lib/mysql/mysql general_log_213.sdi general_log.CSM general_log.CSV slow_log_214.sdi slow_log.CSM slow_log.CSV 注意：因为这些文件，不是普通的文件系统，它是数据库专用的文件，只能通过数据库的统一接口到这些文件中，进行数据的查找。查看监听端口，默认3306，33060 是8.0版本中的特性 [root@Rocky9-12 ~]# ss -tnulp | grep mysql tcp LISTEN 0 151 *:3306 *:* users:(("mysqld",pid=2520,fd=24)) tcp LISTEN 0 70 *:33060 *:* users:(("mysqld",pid=2520,fd=21)) 注意：X Protocol 是一种新的通信协议，旨在提供更高效、灵活的数据交互方式，让客户端与服务器通信更顺畅。 在 MySQL 8.0 中，33060 端口是 X Protocol 协议的端口 。5.4.登录测试客户端连接: 默认用户名 root，默认密码为空连接到数据库里面 [root@Rocky9-12 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.41 Source distribution mysql>查看当前数据库的版本信息 mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.41 | +-----------+ 1 row in set (0.00 sec) mysql> exit; # 退出数据库的连接客户端连接: 直接使用用户和密码 [root@Rocky9-12 ~]# mysql -uroot -p -P3306 Enter password: # 因为没有密码，所以这里直接Enter即可 Server version: 8.0.41 Source distribution mysql>mysql> \s # 显示当前 MySQL 服务器会话状态的快捷命令 -------------- mysql Ver 8.0.41 for Linux on x86_64 (Source distribution) Connection id: 9 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 8.0.41 Source distribution Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 UNIX socket: /var/lib/mysql/mysql.sock Binary data as: Hexadecimal Uptime: 8 min 35 sec Threads: 2 Questions: 8 Slow queries: 0 Opens: 120 Flush tables: 3 Open tables: 36 Queries per second avg: 0.015 --------------6.在Rocky9中安装mariadb6.1.软件安装查看mariadb软件 [root@Rocky9-15 ~]# yum list mariadb mariadb.x86_64 3:10.5.27-1.el9_5 appstream安装软件 -- 会自动安装客户端 [root@Rocky9-15 ~]# yum -y install mariadb-server启动服务 [root@Rocky9-15 ~]# systemctl enable --now mariadb Created symlink /etc/systemd/system/mysql.service → /usr/lib/systemd/system/mariadb.service. Created symlink /etc/systemd/system/mysqld.service → /usr/lib/systemd/system/mariadb.service. Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.查看状态 [root@Rocky9-15 ~]# systemctl status mariadb ● mariadb.service - MariaDB 10.5 database server Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; preset: disabled) Active: active (running)监听端口 [root@Rocky9-15 ~]# ss -tunlp | grep 3306 tcp LISTEN 0 80 *:3306 *:* users:(("mariadbd",pid=3169,fd=19)) # 服务名字变成了 mariadb 注意： mariadb里面没有33060的端口 MariaDB 的设计初衷是保持与 MySQL 高度兼容，主要继承 MySQL 5.5 及之前版本的特性。 它专注于提供稳定、性能良好且与旧版 MySQL 兼容的数据库服务，面向大多数传统数据库应用场景，这些场景不需 要通过 33060 端口使用 X Protocol。多线程状态 [root@Rocky9-15 ~]# pstree | grep maria |-mariadbd---7*[{mariadbd}]查看家目录文件结构 [root@Rocky9-15 ~]# ll /var/lib/mysql/ total 122920 -rw-rw---- 1 mysql mysql 24576 May 7 22:10 aria_log.00000001 -rw-rw---- 1 mysql mysql 52 May 7 22:10 aria_log_control -rw-rw---- 1 mysql mysql 972 May 7 22:10 ib_buffer_pool -rw-rw---- 1 mysql mysql 12582912 May 7 22:10 ibdata1 -rw-rw---- 1 mysql mysql 100663296 May 7 22:10 ib_logfile0 -rw-rw---- 1 mysql mysql 12582912 May 7 22:10 ibtmp1 -rw-rw---- 1 mysql mysql 0 May 7 22:10 multi-master.info drwx------ 2 mysql mysql 4096 May 7 22:10 mysql srwxrwxrwx 1 mysql mysql 0 May 7 22:10 mysql.sock -rw-rw---- 1 mysql mysql 16 May 7 22:10 mysql_upgrade_info drwx------ 2 mysql mysql 20 May 7 22:10 performance_schema检查客户端连接命令 -- 本质上是 mariadb文件 [root@Rocky9-15 ~]# ll /usr/sbin/mysqld lrwxrwxrwx 1 root root 19 Feb 4 19:21 /usr/sbin/mysqld -> /usr/libexec/mysqld [root@Rocky9-15 ~]# ll /usr/libexec/mysqld lrwxrwxrwx 1 root root 8 Feb 4 19:21 /usr/libexec/mysqld -> mariadbd6.2.连接登录客户端连接 [root@Rocky9-15 ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 3 Server version: 10.5.27-MariaDB MariaDB Server MariaDB [(none)]> select version(); +-----------------+ | version() | +-----------------+ | 10.5.27-MariaDB | +-----------------+ 1 row in set (0.000 sec) MariaDB [(none)]> exit Bye7.Ubuntu24 安装mysql8.07.1.安装软件安装 mysql-server，会自动安装客户端包【安装客户端不会安装服务端软件】 [root@ubuntu24-13:~]# apt install mysql-server -y再次查看状态信息 [root@ubuntu24-13:~]# systemctl status mysql.service ● mysql.service - MySQL Community Server Loaded: loaded (/usr/lib/systemd/system/mysql.service; enabled; preset: enabled) Active: active (running) 注意：如果是第一次安装mysql-server出现服务启动不起来，而且没有提前做任何的配置，那一般情况下，是本地主机环境已经被历史残留的数据信息破坏了。检测mysql的进程状态多线程模式 [root@ubuntu24-13:~]# pstree | grep mysql |-mysqld---36*[{mysqld}] 自己创建的账户 [root@ubuntu24-13:~]# getent passwd mysql mysql:x:110:110:MySQL Server,,,:/nonexistent:/bin/false查看mysql真正的家目录 [root@ubuntu24-13:~]# ll /var/lib/mysql total 91612 -rw-r----- 1 mysql mysql 196608 May 7 22:19 '#ib_16384_0.dblwr' -rw-r----- 1 mysql mysql 8585216 May 7 22:17 '#ib_16384_1.dblwr' drwxr-x--- 2 mysql mysql 4096 May 7 22:17 '#innodb_redo'/ drwxr-x--- 2 mysql mysql 4096 May 7 22:17 '#innodb_temp'/ drwx------ 7 mysql mysql 4096 May 7 22:17 ./ drwxr-xr-x 50 root root 4096 May 7 22:17 ../ -rw-r----- 1 mysql mysql 56 May 7 22:17 auto.cnf -rw-r----- 1 mysql mysql 180 May 7 22:17 binlog.000001 -rw-r----- 1 mysql mysql 404 May 7 22:17 binlog.000002 -rw-r----- 1 mysql mysql 157 May 7 22:17 binlog.000003 -rw-r----- 1 mysql mysql 48 May 7 22:17 binlog.index -rw------- 1 mysql mysql 1705 May 7 22:17 ca-key.pem -rw-r--r-- 1 mysql mysql 1112 May 7 22:17 ca.pem -rw-r--r-- 1 mysql mysql 1112 May 7 22:17 client-cert.pem -rw------- 1 mysql mysql 1705 May 7 22:17 client-key.pem -rw-r--r-- 1 root root 0 May 7 22:17 debian-5.7.flag -rw-r----- 1 mysql mysql 3425 May 7 22:17 ib_buffer_pool -rw-r----- 1 mysql mysql 12582912 May 7 22:17 ibdata1 -rw-r----- 1 mysql mysql 12582912 May 7 22:17 ibtmp1 drwxr-x--- 2 mysql mysql 4096 May 7 22:17 mysql/ -rw-r----- 1 mysql mysql 26214400 May 7 22:17 mysql.ibd drwxr-x--- 2 mysql mysql 4096 May 7 22:17 performance_schema/ -rw------- 1 mysql mysql 1709 May 7 22:17 private_key.pem -rw-r--r-- 1 mysql mysql 452 May 7 22:17 public_key.pem -rw-r--r-- 1 mysql mysql 1112 May 7 22:17 server-cert.pem -rw------- 1 mysql mysql 1705 May 7 22:17 server-key.pem drwxr-x--- 2 mysql mysql 4096 May 7 22:17 sys/ -rw-r----- 1 mysql mysql 5 May 7 22:17 ubuntu24-13.pid -rw-r----- 1 mysql mysql 16777216 May 7 22:19 undo_001 -rw-r----- 1 mysql mysql 16777216 May 7 22:19 undo_002查看数据库的数据文件 [root@ubuntu24-13:~]# ls /var/lib/mysql/mysql general_log.CSM general_log.CSV general_log_213.sdi slow_log.CSM slow_log.CSV slow_log_214.sdi 注意：因为这些文件，不是普通的文件系统，它是数据库专用的文件，只能通过数据库的统一接口到这些文件中，进行数据的查找。查看监听端口，默认3306，33060 是8.0版本中的特性 [root@ubuntu24-13:~]# netstat -tunlp | grep mysql tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 8524/mysqld tcp 0 0 127.0.0.1:33060 0.0.0.0:* LISTEN 8524/mysqld 结果显示：默认监听的是本地主机的IP地址7.2.登录连接客户端连接: 默认用户名 root，默认密码为空连接到数据库里面 [root@ubuntu24-13:~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.42-0ubuntu0.24.04.1 (Ubuntu) # 查看当前数据库的版本信息 mysql> select version(); +-------------------------+ | version() | +-------------------------+ | 8.0.42-0ubuntu0.24.04.1 | +-------------------------+ 1 row in set (0.00 sec) mysql> exit # 退出数据库的连接 Bye客户端连接: 直接使用用户和密码 [root@ubuntu24-13:~]# mysql -uroot -p Enter password: # 因为没有密码，所以这里直接Enter即可，也可以随便乱写密码 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.42-0ubuntu0.24.04.1 (Ubuntu) Copyright (c) 2000, 2025, Oracle and/or its affiliates. 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> \s # 显示当前 MySQL 服务器会话状态的快捷命令 -------------- mysql Ver 8.0.42-0ubuntu0.24.04.1 for Linux on x86_64 ((Ubuntu)) Connection id: 9 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 8.0.42-0ubuntu0.24.04.1 (Ubuntu) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 UNIX socket: /var/run/mysqld/mysqld.sock Binary data as: Hexadecimal Uptime: 7 min 11 sec Threads: 2 Questions: 8 Slow queries: 0 Opens: 119 Flush tables: 3 Open tables: 38 Queries per second avg: 0.018 --------------8.Ubuntu24中安装mysql8.0---二进制包安装8.1.如何获取二进制包文件传统的二进制包安装需要进行三步：configure --- make --- make install 而mysql的二进制包是指己经编译完成【也就是说，make已经做过了】，以压缩包提供下载的文件，下载到本 地之后释放到自定义目录，再进行配置即可。二进制包的下载位置 -- Download Archives 官网：https://dev.mysql.com/downloads选择 Download Archives选择第一个 Mysql Community Server选择第一个，然后点击右侧的下载就可以了 https://downloads.mysql.com/archives/get/p/23/file/mysql-8.4.0-linux-glibc2.28-x86_64.tar.xz以同样的逻辑下载mysql的最新版本9.2.0选择第一个，然后点击右侧的下载就可以了 https://downloads.mysql.com/archives/get/p/23/file/mysql-9.2.0-linux-glibc2.28-x86_64.tar.xz8.2.基础环境安装依赖 [root@ubuntu24-16:~]# apt -y install libaio-dev numactl libnuma-dev libncurses-dev注意：ubuntu24系统没有libaio1的包，需要单独去下载安装 curl -O http://launchpadlibrarian.net/646633572/libaio1_0.3.113-4_amd64.deb dpkg -i libaio1_0.3.113-4_amd64.deb8.3.用户环境[root@ubuntu24-16:~]# groupadd -r mysql [root@ubuntu24-16:~]# useradd -r -g mysql -s /sbin/nologin mysql8.4.软件安装获取软件 [root@ubuntu24-16:~]# mkdir -p /data/softs [root@ubuntu24-16:~]# cd /data/softs [root@ubuntu24-16:softs]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.4.0-linux-glibc2.28-x86_64.tar.xz解压至指定目录，这个目录只能写 /usr/local/ [root@ubuntu24-16:softs]# mkdir /usr/local/mysql [root@ubuntu24-16:softs]# tar xf mysql-8.4.0-linux-glibc2.28-x86_64.tar.xz [root@ubuntu24-16:softs]# mv mysql-8.4.0-linux-glibc2.28-x86_64 /usr/local/mysql查看文件 [root@ubuntu24-16:softs]# ls /usr/local/mysql LICENSE README bin docs include lib man share support-files [root@ubuntu24-16:softs]# ls /usr/local/mysql/bin/ ibd2sdi myisamchk mysql_config mysql_tzinfo_to_sql mysqld mysqldump mysqlslap innochecksum myisamlog mysql_config_editor mysqladmin mysqld-debug mysqldumpslow perror my_print_defaults myisampack mysql_migrate_keyring mysqlbinlog mysqld_multi mysqlimport myisam_ftdump mysql mysql_secure_installation mysqlcheck mysqld_safe mysqlshow8.5.环境变量设定-可选创建环境变量 [root@ubuntu24-16:softs]# echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh [root@ubuntu24-16:softs]# source /etc/profile.d/mysql.sh [root@ubuntu24-16:softs]# echo $PATH /usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/snap/bin8.6.环境配置创建主配置文件 [root@ubuntu24-16:~]# mkdir /usr/local/mysql/etc [root@ubuntu24-16:~]# vim /usr/local/mysql/etc/my.cnf [root@ubuntu24-16:~]# cat /usr/local/mysql/etc/my.cnf [mysql] port = 3306 socket = /usr/local/mysql/data/mysql.sock [mysqld] port = 3306 mysqlx_port = 33060 mysqlx_socket = /usr/local/mysql/data/mysqlx.sock basedir = /usr/local/mysql datadir = /usr/local/mysql/data socket = /usr/local/mysql/data/mysql.sock pid-file = /usr/local/mysql/data/mysqld.pid log-error = /usr/local/mysql/log/error.log注意： 大家在互联网上经常看到的认证属性已经被移除了，但是，该密码插件选项依然很管用。 甚至默认的密码插件在集群操作的时候，不能用。 default-authentication-plugin = mysql_native_passwordmysqlx： 传统的 MySQL 客户端 - 服务器通信主要基于经典的 SQL 接口。随着现代应用开发需求的变化，如对JSON 数据类型的更好支持、更高效的文档存储和检索、以及更适合现代编程语言的 API 等，MySQL 引入了X DevAPI。而 mysqlx 就是与这个新 API 相关的重要部分，它提供了新的通信协议和客户端库，使得开发 者可以更方便地使用 MySQL 数据库。 使用 mysqlx 命令行客户端来测试和执行基于 X DevAPI 的操作。例如，通过该客户端可以使用新的语法进行数据库查询和操作。注意：配置文件中涉及到的配置目录，必须存在，否则无法运行创建数据目录创建依赖目录 [root@ubuntu24-16:~]# mkdir /usr/local/mysql/{data,log} 更改文件属性 [root@ubuntu24-16:~]# chown -R mysql:mysql /usr/local/mysql如果我们采用的是软连接的方式，上面赋权的时候，命令应该是： chown -R mysql:mysql /usr/local/mysql* 注意：mysql后面有一个 *，如果不加，会导致很多的权限问题，比如 2024-12-08T06:32:09.439274Z 0 [ERROR] [MY-010187] [Server] Could not open file '/data/server/mysql/logs/mysql.log' for error logging: Permission denied 2024-12-08T06:32:09.439380Z 0 [ERROR] [MY-013236] [Server] The designated data directory /data/server/mysql/data/ is unusable. You can remove all files that the server added to it. 2024-12-08T06:32:09.439412Z 0 [ERROR] [MY-010119] [Server] Aborting8.7.环境初始化8.7.1.密码初始化初始化,本地root用户 - 使用密码如果使用 --initialize 选项会生成随机密码，要去 /data/mysql/mysql.log中查看 如果使用 --initialize-insecure -选项会生成空密码[root@ubuntu24-16:~]# cd /usr/local/mysql/ [root@ubuntu24-16:mysql]# bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data确认登录密码 [root@ubuntu24-16:mysql]# grep 'temporary password' /usr/local/mysql/log/error.log 2025-05-07T14:55:15.598967Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: god&ia6haBrC8.7.2.空密码初始化初始化,本地root用户 - 使用空密码清理历史文件 [root@ubuntu24-16:~]# rm -rf /usr/local/mysql/data/* [root@ubuntu24-16:~]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data确认登录密码 [root@ubuntu24-16:~]# tail -f /usr/local/mysql/log/error.log ...... 2025-05-07T14:58:05.616373Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option. 2025-05-07T14:58:07.889872Z 0 [System] [MY-015018] [Server] MySQL Server Initialization - end. 结果提示：使用的是空密码确认文件 [root@ubuntu24-16:~]# ls /usr/local/mysql/data/ '#ib_16384_0.dblwr' auto.cnf client-key.pem mysql.ibd public_key.pem undo_001 '#ib_16384_1.dblwr' ca-key.pem ib_buffer_pool mysql_upgrade_history server-cert.pem undo_002 '#innodb_redo' ca.pem ibdata1 performance_schema server-key.pem '#innodb_temp' client-cert.pem mysql private_key.pem sys8.8.服务脚本定制启动脚本该脚本不是systemd风格的脚本，但是可以被 systemd兼容 [root@ubuntu24-16:~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld重载配置文件 [root@ubuntu24-16:~]# systemctl daemon-reload 启动mysqld服务 [root@ubuntu24-16:~]# /etc/init.d/mysqld start Starting mysqld (via systemctl): mysqld.service查看自动生成的服务管理文件 [root@ubuntu24-16:~]# systemctl cat mysqld.service # /run/systemd/generator.late/mysqld.service # Automatically generated by systemd-sysv-generator [Unit] Documentation=man:systemd-sysv-generator(8) SourcePath=/etc/init.d/mysqld Description=LSB: start and stop MySQL After=network-online.target After=remote-fs.target After=ypbind.service After=nscd.service After=ldap.service After=ntpd.service After=xntpd.service Wants=network-online.target [Service] Type=forking Restart=no TimeoutSec=5min IgnoreSIGPIPE=no KillMode=process GuessMainPID=no RemainAfterExit=yes SuccessExitStatus=5 6 ExecStart=/etc/init.d/mysqld start ExecStop=/etc/init.d/mysqld stop ExecReload=/etc/init.d/mysqld reload检测服务状态 [root@ubuntu24-16:~]# systemctl status mysqld ● mysqld.service - LSB: start and stop MySQL Loaded: loaded (/etc/init.d/mysqld; generated) Active: active (running) 查看端口连接 [root@ubuntu24-16:~]# netstat -tunlp | grep mysql tcp6 0 0 :::33060 :::* LISTEN 7349/mysqld tcp6 0 0 :::3306 :::* LISTEN 7349/mysqld8.9.连接测试客户端连接[root@ubuntu24-16:~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.4.0 MySQL Community Server - GPL 查看当前的数据库版本 mysql> select version(); +-----------+ | version() | +-----------+ | 8.4.0 | +-----------+ 1 row in set (0.00 sec)修改密码 mysql> alter user root@'localhost' identified by 'Magedu'; Query OK, 0 rows affected (0.01 sec) mysql> exit Byemysql用户账号由两部分组成： 'USERNAME'@'HOST' dange@'10.0.0.100' dange@'10.0.0.%' dange@'%' 说明：HOST限制此用户可通过哪些远程主机连接mysql服务器确认修改密码效果默认情况下，无法登录了 [root@ubuntu24-16:~]# mysql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)使用用户名和密码，可以正常登录 [root@ubuntu24-16:~]# mysql -uroot -pMagedu 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 11 Server version: 8.4.0 MySQL Community Server - GPL8.10.定制服务管理文件cat >/usr/lib/systemd/system/mysqld.service<<EOF [Unit] Description=MySQL Community Server Documentation=mysqld.service After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/etc/my.cnf ExecReload=/bin/kill -s HUP $MAINPID ExecStop=/bin/kill -s TERM $MAINPID LimitNOFILE = 10000 EOF重载配置 [root@ubuntu24-16:~]# systemctl daemon-reload 启动服务 [root@ubuntu24-16:~]# systemctl start mysqld检测效果 [root@ubuntu24-16:~]# systemctl start mysqld.service [root@ubuntu24-16:~]# netstat -tunlp | grep 3306 tcp6 0 0 :::33060 :::* LISTEN 8503/mysqld tcp6 0 0 :::3306 :::* LISTEN 8503/mysqld [root@ubuntu24-16:~]# systemctl status mysqld ● mysqld.service - MySQL Community Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; preset: enabled) Active: active (running) since Thu 2025-05-08 10:07:35 CST; 21s ago Main PID: 8503 (mysqld) Tasks: 36 (limit: 4552) Memory: 429.7M (peak: 443.2M) CPU: 2.465s CGroup: /system.slice/mysqld.service └─8503 /usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/etc/my.cnf May 08 10:07:35 ubuntu24-16 systemd[1]: Started mysqld.service - MySQL Community Server.9.Ubuntu中安装mysql9.2---二进制包安装9.1.基础环境安装依赖 [root@ubuntu24-13:~]# apt -y install libaio-dev numactl libnuma-dev libncurses-dev注意：ubuntu24系统没有libaio1的包，需要单独去下载安装 [root@ubuntu24-13:~]# curl -O http://launchpadlibrarian.net/646633572/libaio1_0.3.113-4_amd64.deb [root@ubuntu24-13:~]# dpkg -i libaio1_0.3.113-4_amd64.deb 否则环境初始化的时候，会发生如下报错 root@ubuntu24:mysql# bin/mysqld --initialize --user=mysql -- basedir=/usr/local/mysql --datadir=/usr/local/mysql/data bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory9.2.用户环境创建组和用户 [root@ubuntu24-13:~]# groupadd -r mysql [root@ubuntu24-13:~]# useradd -r -g mysql -s /sbin/nologin mysql9.3.软件环境获取软件 [root@ubuntu24-13:~]# mkdir -p /data/softs [root@ubuntu24-13:~]# cd /data/softs [root@ubuntu24-13:softs]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-9.2.0-linux-glibc2.28-x86_64.tar.xz解压至指定目录，这个目录只能写 /usr/local/ [root@ubuntu24-13:softs]# ls mysql-9.2.0-linux-glibc2.28-x86_64.tar.xz [root@ubuntu24-13:softs]# tar xf mysql-9.2.0-linux-glibc2.28-x86_64.tar.xz [root@ubuntu24-13:softs]# mv mysql-9.2.0-linux-glibc2.28-x86_64 /usr/local/mysql查看文件 [root@ubuntu24-13:softs]# ls /usr/local/mysql LICENSE README bin docs include lib man share support-files [root@ubuntu24-13:softs]# ls /usr/local/mysql/bin/ ibd2sdi myisamchk mysql_config mysql_tzinfo_to_sql mysqld mysqldump mysqlslap innochecksum myisamlog mysql_config_editor mysqladmin mysqld-debug mysqldumpslow perror my_print_defaults myisampack mysql_migrate_keyring mysqlbinlog mysqld_multi mysqlimport myisam_ftdump mysql mysql_secure_installation mysqlcheck mysqld_safe mysqlshow9.4.环境变量设定-可选创建环境变量 [root@ubuntu24-13:~]# echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh [root@ubuntu24-13:~]# source /etc/profile.d/mysql.sh [root@ubuntu24-13:~]# echo $PATH /usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/snap/bin9.5.环境配置创建主配置文件 [root@ubuntu24-13:~]# mkdir /usr/local/mysql/etc [root@ubuntu24-13:~]# vim /usr/local/mysql/etc/my.cnf [root@ubuntu24-13:~]# cat /usr/local/mysql/etc/my.cnf [mysql] port = 3306 socket = /usr/local/mysql/data/mysql.sock [mysqld] port = 3306 mysqlx_port = 33060 mysqlx_socket = /usr/local/mysql/data/mysqlx.sock basedir = /usr/local/mysql datadir = /usr/local/mysql/data socket = /usr/local/mysql/data/mysql.sock pid-file = /usr/local/mysql/data/mysqld.pid log-error = /usr/local/mysql/log/error.log注意： 大家在互联网上经常看到的认证属性已经被移除了，但是，该密码插件选项依然很管用。 甚至默认的密码插件在集群操作的时候，不能用。 default-authentication-plugin = mysql_native_password注意：配置文件中涉及到的配置目录，必须存在，否则无法运行创建数据目录创建依赖目录 [root@ubuntu24-13:~]# mkdir /usr/local/mysql/{data,log} 更改文件属性 [root@ubuntu24-13:~]# chown -R mysql:mysql /usr/local/mysql/如果我们采用的是软连接的方式，上面赋权的时候，命令应该是： chown -R mysql:mysql /usr/local/mysql* 注意：mysql后面有一个 *，如果不加，会导致很多的权限问题，比如 2025-05-08T06:32:09.439274Z 0 [ERROR] [MY-010187] [Server] Could not open file '/data/server/mysql/logs/mysql.log' for error logging: Permission denied 2025-05-08T06:32:09.439380Z 0 [ERROR] [MY-013236] [Server] The designated data directory /data/server/mysql/data/ is unusable. You can remove all files that the server added to it. 2025-05-08T06:32:09.439412Z 0 [ERROR] [MY-010119] [Server] Aborting9.6.环境初始化9.6.1.密码初始化初始化,本地root用户 - 使用密码如果使用 --initialize 选项会生成随机密码，要去 /data/mysql/mysql.log中查看 如果使用 --initialize-insecure -选项会生成空密码[root@ubuntu24-13:~]# cd /usr/local/mysql/ [root@ubuntu24-13:mysql]# bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data确认登录密码 [root@ubuntu24-13:mysql]# grep 'temporary password' /usr/local/mysql/log/error.log 2025-05-08T02:27:47.810610Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: msgQ:*jwk1Kb9.6.2.空密码初始化初始化,本地root用户 - 使用空密码清理历史文件 [root@ubuntu24-13:mysql]# rm -rf /usr/local/mysql/data/* [root@ubuntu24-13:mysql]# bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data确认登录密码 [root@ubuntu24-13:mysql]# tail -f /usr/local/mysql/log/error.log ...... 2025-05-08T02:30:16.497848Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option. 2025-05-08T02:30:18.762028Z 0 [System] [MY-015018] [Server] MySQL Server Initialization - end. 结果提示：使用的是空密码确认文件 [root@ubuntu24-13:~]# ls /usr/local/mysql/data/ | xargs -n 3 printf "%-30s %-30s %-30s\n" #ib_16384_0.dblwr #ib_16384_1.dblwr #innodb_redo #innodb_temp auto.cnf ca-key.pem ca.pem client-cert.pem client-key.pem ib_buffer_pool ibdata1 mysql mysql.ibd mysql_upgrade_history performance_schema private_key.pem public_key.pem server-cert.pem server-key.pem sys undo_001 undo_0029.7.服务脚本定制启动脚本该脚本不是systemd风格的脚本，但是可以被 systemd兼容 [root@ubuntu24-13:~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld 重载配置文件 [root@ubuntu24-13:~]# systemctl daemon-reload 启动mysqld服务 [root@ubuntu24-13:~]# /etc/init.d/mysqld start Starting mysqld (via systemctl): mysqld.service.查看自动生成的服务管理文件 [root@ubuntu24-13:~]# systemctl cat mysqld.service # /run/systemd/generator.late/mysqld.service # Automatically generated by systemd-sysv-generator [Unit] Documentation=man:systemd-sysv-generator(8) SourcePath=/etc/init.d/mysqld Description=LSB: start and stop MySQL After=network-online.target After=remote-fs.target After=ypbind.service After=nscd.service After=ldap.service After=ntpd.service After=xntpd.service Wants=network-online.target [Service] Type=forking Restart=no TimeoutSec=5min IgnoreSIGPIPE=no KillMode=process GuessMainPID=no RemainAfterExit=yes SuccessExitStatus=5 6 ExecStart=/etc/init.d/mysqld start ExecStop=/etc/init.d/mysqld stop ExecReload=/etc/init.d/mysqld reload检测服务状态 [root@ubuntu24-13:~]# systemctl status mysqld.service ● mysqld.service - LSB: start and stop MySQL Loaded: loaded (/etc/init.d/mysqld; generated) Active: active (running) 查看端口连接 [root@ubuntu24-13:~]# netstat -tunlp | grep mysql tcp6 0 0 :::33060 :::* LISTEN 8114/mysqld tcp6 0 0 :::3306 :::* LISTEN 8114/mysqld9.8.连接测试客户端连接 [root@ubuntu24-13:~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 9.2.0 MySQL Community Server - GPL Copyright (c) 2000, 2025, Oracle and/or its affiliates. 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>查看当前的数据库版本 mysql> select version(); +-----------+ | version() | +-----------+ | 9.2.0 | +-----------+ 1 row in set (0.00 sec) 修改密码 mysql> alter user root@'localhost' identified by 'Magedu'; Query OK, 0 rows affected (0.01 sec) mysql> exit Bye确认修改密码效果默认情况下，无法登录了 [root@ubuntu24-13:~]# mysql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) 使用用户名和密码，可以正常登录 [root@ubuntu24-13:~]# mysql -uroot -pMagedu 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 11 Server version: 9.2.0 MySQL Community Server - GPL Copyright (c) 2000, 2025, Oracle and/or its affiliates. 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>9.9.定制服务管理文件cat >/usr/lib/systemd/system/mysqld.service<<EOF [Unit] Description=MySQL Community Server Documentation=mysqld.service After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/etc/my.cnf ExecReload=/bin/kill -s HUP $MAINPID ExecStop=/bin/kill -s TERM $MAINPID LimitNOFILE = 10000 EOF重载配置 [root@ubuntu24-13:~]# systemctl daemon-reload 启动服务 [root@ubuntu24-13:~]# systemctl start mysqld 查看端口情况 [root@ubuntu24-13:~]# netstat -tunlp | grep 3306 tcp6 0 0 :::33060 :::* LISTEN 8114/mysqld tcp6 0 0 :::3306 :::* LISTEN 8114/mysqld查看服务状态信息 [root@ubuntu24-13:~]# systemctl status mysqld.service ● mysqld.service - MySQL Community Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; preset: enabled) Active: active (running) 10.Rocky9中安装mysql8.0---二进制包安装10.1.基础环境安装依赖 [root@Rocky9-12 ~]# yum -y install libaio numactl-libs ncurses-compat-libs10.2.用户环境创建组和用户 [root@Rocky9-12 ~]# groupadd -r mysql [root@Rocky9-12 ~]# useradd -r -g mysql -s /sbin/nologin mysql10.3.软件环境获取软件 [root@Rocky9-12 ~]# mkdir -p /data/softs [root@Rocky9-12 ~]# cd /data/softs [root@Rocky9-12 softs]#wget https://downloads.mysql.com/archives/get/p/23/file/mysql-9.2.0-linux-glibc2.28-x86_64.tar.xzz解压至指定目录，这个目录只能写 /usr/local/ [root@Rocky9-12 softs]# ls mysql-9.2.0-linux-glibc2.28-x86_64.tar.xz [root@Rocky9-12 softs]# tar xf mysql-9.2.0-linux-glibc2.28-x86_64.tar.xz [root@Rocky9-12 softs]# mv mysql-9.2.0-linux-glibc2.28-x86_64 /usr/local/mysql[root@Rocky9-12 softs]# ls /usr/local/mysql bin docs include lib LICENSE man README share support-files [root@Rocky9-12 softs]# ls /usr/local/mysql/bin/ | xargs -n 3 printf "%-30s %-30s %-30s\n" ibd2sdi innochecksum myisamchk myisam_ftdump myisamlog myisampack my_print_defaults mysql mysqladmin mysqlbinlog mysqlcheck mysql_config mysql_config_editor mysqld mysqld-debug mysqld_multi mysqld_safe mysqldump mysqldumpslow mysqlimport mysql_migrate_keyring mysql_secure_installation mysqlshow mysqlslap10.4.环境变量设定--可选创建环境变量 [root@Rocky9-12 ~]# echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh [root@Rocky9-12 ~]# source /etc/profile.d/mysql.sh [root@Rocky9-12 ~]# echo $PATH /usr/local/mysql/bin:/root/.local/bin:/root/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin10.5.环境配置创建主配置文件 [root@Rocky9-12 ~]# mkdir /usr/local/mysql/etc [root@Rocky9-12 ~]# vim /usr/local/mysql/etc/my.cnf [root@Rocky9-12 ~]# cat /usr/local/mysql/etc/my.cnf [mysql] port = 3306 socket = /usr/local/mysql/data/mysql.sock [mysqld] port = 3306 mysqlx_port = 33060 mysqlx_socket = /usr/local/mysql/data/mysqlx.sock basedir = /usr/local/mysql datadir = /usr/local/mysql/data socket = /usr/local/mysql/data/mysql.sock pid-file = /usr/local/mysql/data/mysqld.pid log-error = /usr/local/mysql/log/error.log创建数据目录创建依赖目录 [root@Rocky9-12 ~]# mkdir /usr/local/mysql/{data,log} 更改文件属性 [root@Rocky9-12 ~]# chown -R mysql:mysql /usr/local/mysql/10.6.环境初始化10.6.1.密码初始化[root@Rocky9-12 ~]# cd /usr/local/mysql/ [root@Rocky9-12 mysql]# bin/mysqld -initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data 确认登录密码 [root@Rocky9-12 mysql]# grep 'temporary password' /usr/local/mysql/log/error.log 2025-05-08T02:59:08.664100Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: cZYk#4#T)2k710.6.2.空密码初始化初始化,本地root用户 - 使用空密码清理历史文件 [root@Rocky9-12 mysql]# rm -rf /usr/local/mysql/data/* [root@Rocky9-12 mysql]# bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data确认登录密码 [root@Rocky9-12 mysql]# tail -f /usr/local/mysql/log/error.log ...... 2025-05-08T03:00:45.561141Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option. 2025-05-08T03:00:47.872042Z 0 [System] [MY-015018] [Server] MySQL Server Initialization - end. 结果显示：为空密码确认文件 [root@Rocky9-12 ~]# ls /usr/local/mysql/data/ | xargs -n 3 printf "%-30s %-30s %-30s\n" auto.cnf ca-key.pem ca.pem client-cert.pem client-key.pem #ib_16384_0.dblwr #ib_16384_1.dblwr ib_buffer_pool ibdata1 #innodb_redo #innodb_temp mysql mysql.ibd mysql_upgrade_history performance_schema private_key.pem public_key.pem server-cert.pem server-key.pem sys undo_001 undo_00210.7.定制服务管理文件cat >/usr/lib/systemd/system/mysqld.service<<EOF [Unit] Description=MySQL Community Server Documentation=mysqld.service After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/etc/my.cnf ExecReload=/bin/kill -s HUP $MAINPID ExecStop=/bin/kill -s TERM $MAINPID LimitNOFILE = 10000 EOF重载配置 [root@Rocky9-12 ~]# systemctl daemon-reload 启动mysql [root@Rocky9-12 ~]# systemctl start mysqld.service 查看端口连接 [root@Rocky9-12 ~]# netstat -tunlp | grep 3306 tcp6 0 0 :::33060 :::* LISTEN 2052/mysqld tcp6 0 0 :::3306 :::* LISTEN 2052/mysqld10.8.连接测试[root@Rocky9-12 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 9.2.0 MySQL Community Server - GPL Copyright (c) 2000, 2025, Oracle and/or its affiliates. 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> select version(); +-----------+ | version() | +-----------+ | 9.2.0 | +-----------+ 1 row in set (0.00 sec)修改密码 mysql> alter user root@'localhost' identified by 'Magedu'; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye默认情况下，无法登录了 [root@Rocky9-12 ~]# mysql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)使用用户名和密码，可以正常登录 [root@Rocky9-12 ~]# mysql -uroot -pMagedu 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 11 Server version: 9.2.0 MySQL Community Server - GPL Copyright (c) 2000, 2025, Oracle and/or its affiliates. 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 Bye11.Rocky9-12二进制格式安装 MySQL 5.7.4411.1.基础环境安装依赖 [root@Rocky9-12 ~]# yum -y install libaio numactl-libs ncurses-compat-libs11.2.准备用户创建组和用户 [root@Rocky9-12 softs]# groupadd -r mysql [root@Rocky9-12 softs]# useradd -r -g mysql -s /sbin/nologin mysql11.3.软件环境获取软件 [root@Rocky9-12 ~]# mkdir -p /data/softs [root@Rocky9-12 ~]# cd /data/softs [root@Rocky9-12 softs]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz解压至指定目录，这个目录只能写 /usr/local/ [root@Rocky9-12 softs]# ls mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz [root@Rocky9-12 softs]# tar xf mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz [root@Rocky9-12 softs]# mv mysql-5.7.44-linux-glibc2.12-x86_64 /usr/local/mysql11.4.环境变量设定-可选[root@Rocky9-12 softs]# echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh [root@Rocky9-12 softs]# source /etc/profile.d/mysql.sh [root@Rocky9-12 softs]# echo $PATH /usr/local/mysql/bin:/root/.local/bin:/root/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin11.5.环境配置创建主配置文件 [root@Rocky9-12 softs]# mkdir /usr/local/mysql/etc [root@Rocky9-12 softs]# vim /usr/local/mysql/etc/my.cnf [root@Rocky9-12 softs]# cat /usr/local/mysql/etc/my.cnf [mysqld] port=3306 socket = /usr/local/mysql/data/mysql.sock user=mysql basedir = /usr/local/mysql datadir = /usr/local/mysql/data skip_name_resolve = 1 pid-file = /usr/local/mysql/data/mysqld.pid log-error = /usr/local/mysql/log/error.log [client] socket = /usr/local/mysql/data/mysql.sock11.6.创建数据目录，建议使用逻辑卷创建依赖目录 [root@Rocky9-12 softs]# mkdir /usr/local/mysql/{data,log} 更改文件属性 [root@Rocky9-12 softs]# chown -R mysql:mysql /usr/local/mysql/11.7.环境初始化11.7.1.密码初始化[root@Rocky9-12 ~]# cd /usr/local/mysql/bin [root@Rocky9-12 bin]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data确认登录密码 [root@Rocky9-12 bin]# grep 'temporary password' /usr/local/mysql/log/error.log 2025-05-08T11:36:50.323892Z 1 [Note] A temporary password is generated for root@localhost: p>(D,oCOv1;U11.7.2.空密码初始化初始化,本地root用户 - 使用空密码清理历史文件 [root@Rocky9-12 bin]# rm -rf /usr/local/mysql/data/* [root@Rocky9-12 bin]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data确认登录密码 [root@Rocky9-12 bin]# tail /usr/local/mysql/log/error.log ...... 2025-05-08T11:38:56.196531Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option. 结果显示：为空密码确认文件 [root@Rocky9-12 bin]# ls /usr/local/mysql/data/ | xargs -n 3 printf "%-30s %-30s %-30s\n" auto.cnf ca-key.pem ca.pem client-cert.pem client-key.pem ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 mysql performance_schema private_key.pem public_key.pem server-cert.pem server-key.pem sys11.8.定制服务管理文件cat >/usr/lib/systemd/system/mysqld.service<<EOF [Unit] Description=MySQL Community Server Documentation=mysqld.service After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/etc/my.cnf ExecReload=/bin/kill -s HUP $MAINPID ExecStop=/bin/kill -s TERM $MAINPID LimitNOFILE = 10000 EOF重载配置 [root@Rocky9-12 bin]# systemctl daemon-reload 启动mysql [root@Rocky9-12 bin]# systemctl start mysqld.service 查看端口连接 [root@Rocky9-12 bin]# netstat -tunlp | grep 3306 tcp6 0 0 :::3306 :::* LISTEN 1935/mysqld11.9.连接测试[root@Rocky9-12 bin]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.44 MySQL Community Server (GPL) Copyright (c) 2000, 2023, Oracle and/or its affiliates. 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>查看当前数据库版本 mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.44 | +-----------+ 1 row in set (0.00 sec)修改密码 mysql> alter user root@'localhost' identified by 'Magedu'; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye使用用户名和密码进行登录 [root@Rocky9-12 bin]# mysql -uroot -pMagedu mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.44 MySQL Community Server (GPL) Copyright (c) 2000, 2023, Oracle and/or its affiliates. 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 Bye12.ubuntu24-13二进制格式安装 MySQL 5.7.4412.1.基础环境安装依赖 [root@ubuntu24-13:~]# apt -y install libaio-dev12.2.准备用户创建组和用户 [root@ubuntu24-13:~]# groupadd -r mysql [root@ubuntu24-13:~]# useradd -r -g mysql -s /sbin/nologin mysql12.3.软件环境获取软件 [root@ubuntu24-13:~]# mkdir -p /data/softs [root@ubuntu24-13:~]# cd /data/softs [root@ubuntu24-13:softs]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz解压至指定目录，这个目录只能写 /usr/local/ [root@ubuntu24-13:softs]# ls mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz [root@ubuntu24-13:softs]# tar xf mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz [root@ubuntu24-13:softs]# mv mysql-5.7.44-linux-glibc2.12-x86_64 /usr/local/mysql12.4.环境变量设定-可选[root@ubuntu24-13:softs]# echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh [root@ubuntu24-13:softs]# source /etc/profile.d/mysql.sh [root@ubuntu24-13:softs]# echo $PATH /usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/snap/bin12.5.环境配置创建主配置文件 [root@ubuntu24-13:softs]# mkdir /usr/local/mysql/etc [root@ubuntu24-13:softs]# vim /usr/local/mysql/etc/my.cnf [root@ubuntu24-13:softs]# cat /usr/local/mysql/etc/my.cnf [mysqld] port=3306 user=mysql character-set-server=utf8 collation-server=utf8_general_ci default-storage-engine=INNODB # Only allow connections from localhost bind-address = 0.0.0.0 #sql_mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" sql_mode="STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" max_connections=100 #skip-name-resolve socket = /usr/local/mysql/data/mysql.sock basedir = /usr/local/mysql datadir = /usr/local/mysql/data #日志 #日志时间,默认是UTC log_timestamps=SYSTEM #错误日志 log-error = /usr/local/mysql/log/error.log #开启二进制日志 log-bin=/usr/local/mysql/mysql-bin server-id=1 #开启通用查询日志 #general_log=ON #general_log_file=/usr/local/mysql/mysql-query.log #慢查询日志 slow_query_log=ON slow_query_log_file=/usr/local/var/log/mysql/mysql-slow.log long_query_time=1 #记录不使用索引查询的语句 log_queries_not_using_indexes=ON #修改密码校验等级 plugin-load-add=validate_password.so validate-password=FORCE_PLUS_PERMANENT validate_password_policy=0 validate_password_length=4 #设置导入导出的安全目录 secure_file_priv="" skip_name_resolve = 1 pid-file = /usr/local/mysql/data/mysqld.pid [mysql] default-character-set=utf8 auto-rehash [client] port=3306 socket = /usr/local/mysql/data/mysql.sock其中log_error文件需要事先创建，同时注意文件权限为mysql12.6.创建数据目录，建议使用逻辑卷创建依赖目录 [root@ubuntu24-13:softs]# mkdir /usr/local/mysql/{data,log} 更改文件属性 [root@ubuntu24-13:softs]# chown -R mysql:mysql /usr/local/mysql/12.7.环境初始化密码初始化[root@ubuntu24-13:~]# cd /usr/local/mysql/bin [root@ubuntu24-13:~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data确认登录密码 [root@ubuntu24-13:~]# grep 'temporary password' /usr/local/mysql/log/error.log 2025-05-09T13:33:21.997935+08:00 1 [Note] A temporary password is generated for root@localhost: ,?ssSMZ)z419报错: [root@ubuntu24-13:~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data /usr/local/mysql/bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory 解决问题： 这是由于系统安装的版本过高。通过建立软连接方式解决 查看自己系统下对应的版本，建立软连接 [root@ubuntu24-13:~]# ldd /usr/local/mysql/bin/mysqld | grep libaio libaio.so.1 => not found [root@ubuntu24-13:~]# find / -name libaio.so* /usr/lib/x86_64-linux-gnu/libaio.so.1t64 /usr/lib/x86_64-linux-gnu/libaio.so.1t64.0.2 /usr/lib/x86_64-linux-gnu/libaio.so # 创建软链接 [root@ubuntu24-13:~]# ln -s /usr/lib/x86_64-linux-gnu/libaio.so /usr/lib/x86_64-linux-gnu/libaio.so.1 # 检查库是否存在 [root@ubuntu24-13:~]# ll /lib/x86_64-linux-gnu/libaio.so.1 lrwxrwxrwx 1 root root 35 May 9 13:25 /lib/x86_64-linux-gnu/libaio.so.1 -> /usr/lib/x86_64-linux-gnu/libaio.so # 验证动态链接器能否找到库 [root@ubuntu24-13:~]# ldd /usr/local/mysql/bin/mysqld | grep libaio libaio.so.1 => /lib/x86_64-linux-gnu/libaio.so.1 (0x00007c61e38ec000)报错： [root@ubuntu24-13:~]# mysql mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory [root@ubuntu24-13:~]# ln -s /usr/lib/x86_64-linux-gnu/libncurses.so.6.4 /usr/lib/x86_64-linux-gnu/libncurses.so.5 [root@ubuntu24-13:~]# ln -s /usr/lib/x86_64-linux-gnu/libtinfo.so.6.4 /usr/lib/x86_64-linux-gnu/libtinfo.so.5确认文件 [root@ubuntu24-13:~]# ls /usr/local/mysql/data/ | xargs -n 3 printf "%-30s %-30s %-30s\n" auto.cnf ca-key.pem ca.pem client-cert.pem client-key.pem ib_buffer_pool ib_logfile0 ib_logfile1 ibdata1 mysql performance_schema private_key.pem public_key.pem server-cert.pem server-key.pem sys12.8.定制服务管理文件cat >/usr/lib/systemd/system/mysqld.service<<EOF [Unit] Description=MySQL Community Server Documentation=mysqld.service After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/etc/my.cnf ExecReload=/bin/kill -s HUP $MAINPID ExecStop=/bin/kill -s TERM $MAINPID LimitNOFILE = 10000 EOF重载配置 [root@ubuntu24-13:~]# systemctl daemon-reload 启动mysql [root@ubuntu24-13:~]# systemctl start mysqld.service 查看端口连接 [root@ubuntu24-13:~]# netstat -tunlp | grep 3306 tcp6 0 0 :::3306 :::* LISTEN 1935/mysqld12.9.连接测试[root@ubuntu24-13:~]# mysql -uroot -p",?ssSMZ)z419" 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.44-log Copyright (c) 2000, 2023, Oracle and/or its affiliates. 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>查看当前数据库版本 mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.44 | +-----------+ 1 row in set (0.00 sec)修改密码并授予权限 mysql> alter user root@'localhost' identified by 'Mysql.123456'; Query OK, 0 rows affected (0.01 sec) mysql> grant all privileges on *.* to root@'%' identified by 'Mysql.123456'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye[root@ubuntu24-13:~]# mysql -uroot -p'Mysql.123456' mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.44-log MySQL Community Server (GPL) Copyright (c) 2000, 2023, Oracle and/or its affiliates. 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 Bye13.通用二进制格式安装 MySQL 5.6.5013.1.基础环境安装依赖 [root@Rocky9-12 ~]# yum install -y perl-Data-Dumper autoconf libaio perl-Sys-Hostname ncurses-compat-libs常见错误：缺少安装依赖，会报如下错误 [root@Rocky9-12 mysql]# ./scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data FATAL ERROR: please install the following Perl modules before executing ./scripts/mysql_install_db: Sys::Hostname[root@Rocky9-12 ~]# mysql mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory [root@Rocky9-12 ~]# mysql -uroot -p mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory 在 Rocky Linux 9 系统中，可以使用 dnf 命令安装 libncurses5 相关的包。不过 Rocky Linux 9 中可能没有直接提供 libncurses.so.5 版本，你可以尝试安装兼容的 ncurses 包： sudo dnf install ncurses-compat-libs 这个包通常会包含 libncurses.so.5 或者提供其兼容的版本。13.2.准备用户创建组和用户 [root@Rocky9-12 ~]# groupadd -r mysql [root@Rocky9-12 ~]# useradd -r -g mysql -s /sbin/nologin mysql13.3.软件环境获取软件 [root@Rocky9-12 ~]# mkdir -p /data/softs [root@Rocky9-12 ~]# cd /data/softs [root@Rocky9-12 softs]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.50-linux-glibc2.12-x86_64.tar.gz解压至指定目录，这个目录只能写 /usr/local/ [root@Rocky9-12 softs]# ls mysql-5.6.50-linux-glibc2.12-x86_64.tar.gz [root@Rocky9-12 softs]# tar xf mysql-5.6.50-linux-glibc2.12-x86_64.tar.gz [root@Rocky9-12 softs]# mv mysql-5.6.50-linux-glibc2.12-x86_64 /usr/local/mysql13.4.环境变量设定--可选[root@Rocky9-12 softs]# echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh [root@Rocky9-12 softs]# source /etc/profile.d/mysql.sh [root@Rocky9-12 softs]# echo $PATH /usr/local/mysql/bin:/root/.local/bin:/root/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin13.5.环境配置创建主配置文件 [root@Rocky9-12 softs]# mkdir /usr/local/mysql/etc [root@Rocky9-12 softs]# vim /usr/local/mysql/etc/my.cnf [root@Rocky9-12 softs]# cat /usr/local/mysql/etc/my.cnf [mysqld] port=3306 socket = /usr/local/mysql/data/mysql.sock user=mysql basedir = /usr/local/mysql datadir = /usr/local/mysql/data innodb_file_per_table=on skip_name_resolve = on symbolic-links=0 [client] socket = /usr/local/mysql/data/mysql.sock [mysqld_safe] pid-file = /usr/local/mysql/data/mysqld.pid log-error = /usr/local/mysql/log/error.log13.6.创建数据目录，建议使用逻辑卷创建依赖目录 [root@Rocky9-12 softs]# mkdir /usr/local/mysql/{data,log} 更改文件属性 [root@Rocky9-12 softs]# chown -R mysql:mysql /usr/local/mysql/13.7.环境初始化[root@Rocky9-12 softs]# cd /usr/local/mysql [root@Rocky9-12 mysql]# ./scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data FATAL ERROR: please install the following Perl modules before executing ./scripts/mysql_install_db: Sys::Hostname确认文件 [root@Rocky9-12 mysql]# ll /usr/local/mysql/data/ total 110600 -rw-rw---- 1 mysql mysql 12582912 May 8 20:06 ibdata1 -rw-rw---- 1 mysql mysql 50331648 May 8 20:06 ib_logfile0 -rw-rw---- 1 mysql mysql 50331648 May 8 20:06 ib_logfile1 drwx------ 2 mysql mysql 4096 May 8 20:06 mysql drwx------ 2 mysql mysql 4096 May 8 20:06 performance_schema drwxr-xr-x 2 mysql mysql 20 May 8 19:56 test13.8.定制服务管理文件cat >/usr/lib/systemd/system/mysqld.service<<EOF [Unit] Description=MySQL Community Server Documentation=mysqld.service After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/etc/my.cnf ExecReload=/bin/kill -s HUP $MAINPID ExecStop=/bin/kill -s TERM $MAINPID LimitNOFILE = 10000 EOF重载配置 [root@Rocky9-12 bin]# systemctl daemon-reload 启动mysql [root@Rocky9-12 bin]# systemctl start mysqld.service 查看端口连接 [root@Rocky9-12 bin]# netstat -tunlp | grep 3306 tcp6 0 0 :::3306 :::* LISTEN 1935/mysqld13.9.连接测试[root@Rocky9-12 ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.50 MySQL Community Server (GPL) Copyright (c) 2000, 2020, 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>查看当前版本信息 mysql> select version(); +-----------+ | version() | +-----------+ | 5.6.50 | +-----------+ 1 row in set (0.01 sec)修改密码 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> set password for 'root'@'localhost'=password('123456'); Query OK, 0 rows affected (0.01 sec) mysql> exit Bye-- 刷新权限表 FLUSH PRIVILEGES; -- 修改 root 用户在 localhost 上的密码 SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');使用用户名和密码进行登录 [root@Rocky9-12 ~]# mysql -uroot -p123456 Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.50 MySQL Community Server (GPL) Copyright (c) 2000, 2020, 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 Bye13.10.初始化脚本提高安全性范例: 针对MySQL5.6前版本进行安全加固运行脚本：mysql_secure_installation 设置数据库管理员root口令 禁止root远程登录 删除anonymous用户帐号 删除test数据库[root@Rocky9-12 ~]# mysql -uroot -pMagedu 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 4 Server version: 5.6.50 MySQL Community Server (GPL) Copyright (c) 2000, 2020, 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 | | test | +--------------------+ 4 rows in set (0.00 sec) mysql> select user,host from mysql.user; +------+-----------+ | user | host | +------+-----------+ | root | 127.0.0.1 | | root | ::1 | | | localhost | | root | localhost | | | rocky9-12 | | root | rocky9-12 | +------+-----------+ 6 rows in set (0.00 sec) mysql> exit Bye[root@Rocky9-12 ~]# file `which mysql_secure_installation` /usr/local/mysql/bin/mysql_secure_installation: Perl script text executable做个软链接，默认文件为/tmp/mysql.sock，否则会报如下错误 下面直接指定socket文件路径，也不行 [root@Rocky9-12 ~]# ln -s /usr/local/mysql/data/mysql.sock /tmp/mysql.sock [root@Rocky9-12 ~]# ll /tmp/mysql.sock lrwxrwxrwx 1 root root 32 May 8 21:08 /tmp/mysql.sock -> /usr/local/mysql/data/mysql.sock 错误： [root@Rocky9-12 ~]# /usr/local/mysql/bin/mysql_secure_installation --socket=/usr/local/mysql/data/mysql.sock NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! In order to log into MySQL to secure it, we'll need the current password for the root user. If you've just installed MySQL, and you haven't set the root password yet, the password will be blank, so you should just press enter here. Enter current password for root (enter for none): ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) Enter current password for root (enter for none): ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) Enter current password for root (enter for none): ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) Unable to connect to the server as root user, giving up. Cleaning up...[root@Rocky9-12 ~]# /usr/local/mysql/bin/mysql_secure_installation NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! In order to log into MySQL to secure it, we'll need the current password for the root user. If you've just installed MySQL, and you haven't set the root password yet, the password will be blank, so you should just press enter here. Enter current password for root (enter for none): OK, successfully used password, moving on... Setting the root password ensures that nobody can log into the MySQL root user without the proper authorisation. You already have a root password set, so you can safely answer 'n'. Change the root password? [Y/n] y New password: Re-enter new password: Password updated successfully! Reloading privilege tables.. ... Success! By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? [Y/n] y ... Success! Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? [Y/n] y ... Success! By default, MySQL comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? [Y/n] y - Dropping test database... ... Success! - Removing privileges on test database... ... Success! Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? [Y/n] y ... Success! All done! If you've completed all of the above steps, your MySQL installation should now be secure. Thanks for using MySQL! Cleaning up...14.常见的报错信息14.1.报错信息[root@Rocky9-12 ~]#echo $PATH /root/.local/bin:/root/bin:/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin [root@Rocky9-12 ~]# whereis mysqld mysqld: /usr/local/mysql/bin/mysqld [root@Rocky9-12 ~]# ll /usr/local/mysql/bin/mysqld -rwxr-xr-x 1 mysql mysql 211661577 Oct 11 2023 /usr/local/mysql/bin/mysqld [root@Rocky9-12 ~]# mysqld -bash: /usr/local/mysql/bin/mysqld: No such file or directory文件存在，但提示文件一直提示找不到14.2.分析问题当你使用 whereis 命令能找到 mysqld 的路径，并且通过 ll 命令能看到该文件确实存在，但执行 mysqld 时却提示 “No such file or directory”，这通常不是文件本身不存在，而是因为缺少该程序运行所需的依赖库或者动态链接库路径配置有误。下面为你详细分析可能的原因及解决办法：1. 缺少 32 位兼容库 如果 mysqld 是 32 位程序，而你的系统是 64 位的，可能会因为缺少 32 位兼容库而导致找不到依赖的动态链接器。 解决办法： 在 Rocky Linux 9 系统中，你可以使用以下命令安装 32 位兼容库： dnf install -y glibc.i6862. 动态链接库路径配置问题 mysqld 程序可能依赖于某些特定的动态链接库，而这些库的路径没有被正确配置到系统的动态链接库搜索路径中。 解决办法： 临时解决：使用 LD_LIBRARY_PATH 环境变量来临时指定动态链接库的搜索路径。例如，如果 mysqld 依赖的库在 /usr/local/mysql/lib 目录下，可以执行以下命令： export LD_LIBRARY_PATH=/usr/local/mysql/lib:$LD_LIBRARY_PATH mysqld 永久解决：创建一个新的.conf文件，将动态链接库的路径添加到该文件中，然后更新动态链接库缓存。 echo "/usr/local/mysql/lib" > /etc/ld.so.conf.d/mysql.conf ldconfig3. 文件损坏 mysqld 文件本身可能已经损坏，导致系统无法正确执行。4. SELinux 或防火墙限制 SELinux 或者防火墙可能会阻止 mysqld 程序的执行。 解决办法 SELinux：可以临时禁用 SELinux 来进行测试： setenforce 0 如果禁用 SELinux 后 mysqld 能够正常执行，你可以修改 SELinux 的策略来允许 mysqld 运行。 防火墙：确保防火墙没有阻止 mysqld 所需的端口（默认是 3306）。可以使用以下命令开放端口： firewall-cmd --zone=public --add-port=3306/tcp --permanent firewall-cmd --reload14.3.解决问题按照以上步骤操作，应该能够解决 mysqld 执行时提示 “No such file or directory” 的问题。 最后发现自己下载的第一个32位软件 自己下错版本信息了 最后下载64位安装就成功了，再也没有提示错误了endl

Mysql数据库 @TOC1.范例: 针对一个电商项目创建项目的管理员用户mysql> create database eshop; Query OK, 1 row affected (0.00 sec) mysql> create user eshop@'10.0.0.%' identified by '123456'; Query OK, 0 rows affected (0.01 sec) mysql> grant all on eshop.* to eshop@'10.0.0.%'; Query OK, 0 rows affected (0.01 sec) ALTER USER 'eshop'@'10.0.0.%' IDENTIFIED WITH mysql_native_password BY '123456';2.mysqldump 备份工具2.1.mysqldump 常见通用选项：:star::star::star::star::star::star:-u, --user=name User for login if not current user -p, --password[=name] Password to use when connecting to server -A, --all-databases #备份所有数据库，含create database -B, --databases db_name… #指定备份的数据库，包括create database语句 -E, --events： #备份相关的所有event scheduler -R, --routines： #备份所有存储过程和自定义函数 --triggers： #备份表相关触发器，默认启用,用--skip-triggers，不备份触发器 --default-character-set=utf8 #指定字符集 --master-data[=#]：#注意：MySQL8.0.26版以后，此选项变为--source-data #此选项须启用二进制日志 #1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1，适合于主从复制多机使用 #2：记录为被注释的#CHANGE MASTER TO语句，适合于单机使用,适用于备份还原 #此选项会自动关闭--lock-tables功能，自动打开-x | --lock-all-tables功能（除非开启--single-transaction） -F, --flush-logs #备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件， #配合-A 或 -B 选项时，会导致刷新多次数据库。 #建议在同一时刻执行转储和日志刷新，可通过和--single-transaction或-x，--master-data 一起使用实现， #此时只刷新一次二进制日志 --compact #去掉注释，适合调试，节约备份占用的空间,生产不使用 -d, --no-data #只备份表结构,不备份数据,即只备份create table -t, --no-create-info #只备份数据,不备份表结构,即不备份create table -n,--no-create-db #不备份create database，可被-A或-B覆盖 --flush-privileges #备份mysql或相关时需要使用 -f, --force #忽略SQL错误，继续执行 --hex-blob #使用十六进制符号转储二进制列，当有包括BINARY,VARBINARY,BLOB，BIT的数据类型的列时使用，避免乱码 -q, --quick #不缓存查询，直接输出，加快备份速度2.2.生产环境实战备份策略2.2.1.InnoDB建议备份策略:star::star::star::star::star::star:--master-data[=#]：#注意：MySQL8.0.26版以后，此选项变为--source-datamysqldump -uroot -p123456 -A -F -E -R --triggers --single-transaction --master-data=2 --flush-privileges --default-character-set=utf8 --hex-blob >${BACKUP}/fullbak_${BACKUP_TIME}.sql #新版8.0.26以上 mysqldump -uroot -p123456 -A -F -E -R --triggers --single-transaction --source-data=2 --flush-privileges --default-character-set=utf8 --hex-blob >${BACKUP}/fullbak_${BACKUP_TIME}.sql2.2.2.MyISAM建议备份策略:star::star::star::star::star::star:mysqldump -uroot -p123456 -A -F -E -R -x --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob >${BACKUP}/fullbak_${BACKUP_TIME}.sql2.2.3.实战案例：特定数据库的备份脚本vim mysql_backup.sh#!/bin/bash TIME=`date +%F_%H-%M-%S` DIR=/backup DB=hellodb PASS=123456 [ -d $DIR ] || mkdir $DIR mysqldump -uroot -p "$PASS" -F -E -R --triggers --single-transaction --master-data=2 --default-character-set=utf8 -q -B $DB | gzip >${DIR}/${DB}_${TIME}.sql.gz2.3.安装数据库脚本【在线离线下载版】#!/bin/bash #MySQL5.7 Download URL: #https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz #https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz #https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz #MySQL8.0 Download URL: #https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.28-linux-glibc2.12-x86_64.tar.xz #https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.39-linux-glibc2.12-x86_64.tar.xz . /etc/init.d/functions SRC_DIR=`pwd` #MYSQL='mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz' #URL='https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz' MYSQL='mysql-8.0.39-linux-glibc2.12-x86_64.tar.xz' URL='https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.39-linux-glibc2.12-x86_64.tar.xz' COLOR='echo -e \E[01;31m' END='\E[0m' #MYSQL_ROOT_PASSWORD=magedu MYSQL_ROOT_PASSWORD=123456 check (){ if [ $UID -ne 0 ]; then action "当前用户不是root,安装失败" false exit 1 fi cd $SRC_DIR if [ ! -e $MYSQL ];then $COLOR"缺少${MYSQL}文件"$END $COLOR"正在在线下载，请稍后......"$END rpm -q wget || yum -y install wget wget ${URL} elif [ -e /usr/local/mysql ];then action "数据库已存在，安装失败" false exit else return fi if [ ! -e $MYSQL ];then $COLOR"缺少${MYSQL}文件"$END $COLOR"未下载成功,请将相关软件放在${SRC_DIR}目录下"$END exit fi } install_mysql(){ $COLOR"开始安装./.."$END yum -y -q install libaio numactl-libs ncurses-compat-libs &>/dev/null cd $SRC_DIR tar xf $MYSQL -C /usr/local/ MYSQL_DIR=`echo $MYSQL| sed -nr 's/^(.*[0-9]).*/\1/p'` ln -s /usr/local/$MYSQL_DIR /usr/local/mysql chown -R root.root /usr/local/mysql/ id mysql &> /dev/null || { useradd -s /sbin/nologin -r mysql ; action "创建mysql用户"; } echo 'PATH=/usr/local/mysql/bin/:$PATH' > /etc/profile.d/mysql.sh . /etc/profile.d/mysql.sh ln -s /usr/local/mysql/bin/* /usr/bin/ cat > /etc/my.cnf <<-EOF [mysqld] server-id=1 log-bin datadir=/data/mysql socket=/data/mysql/mysql.sock log-error=/data/mysql/mysql.log pid-file=/data/mysql/mysql.pid character-set-server=utf8mb4 [client] user=root password=123456 socket=/data/mysql/mysql.sock default-character-set=utf8mb4 EOF [ -d /data ] || mkdir /data mysqld --initialize --user=mysql --datadir=/data/mysql cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld chkconfig --add mysqld chkconfig mysqld on service mysqld start [ $? -ne 0 ] && { $COLOR"数据库启动失败，退出!"$END;exit; } sleep 3 MYSQL_OLDPASSWORD=`awk '/A temporary password/{print $NF}' /data/mysql/mysql.log` mysqladmin -uroot -p$MYSQL_OLDPASSWORD password $MYSQL_ROOT_PASSWORD &>/dev/null action "数据库安装完成" } check install_mysql3.mysql备份和恢复3.1.二进制日志记录三种格式二进制日志记录三种格式基于"语句"记录：statement，记录语句，默认模式（ MariaDB 10.2.3 版本以下 ），日志量较少基于"行"记录：row，记录数据，日志量较大，更加安全，建议使用的格式,MySQL8.0默认格式混合模式：mixed, 让系统自行判定该基于哪种方式进行，默认模式（ MariaDB 10.2.4及版本以上）3.3.切换日志文件mysql> FLUSH LOGS;3.4.生成二进制日志binlog[root@Rocky8 ~]#mysqladmin flush-binary-log [root@Rocky8 ~]#mysqladmin flush-logs [root@Rocky8 ~]#mysql MariaDB [hellodb]> flush logs;生成二进制日志文件的情况restart mysqld 1G 满了 mysql -e 'flush logs' mysqladmin flush-logs mysqldump -F3.5.启用二进制日志（备份）#MySQL 8.0 默认使用ROW方式 #基于"行"记录：row，记录数据，日志量较大，更加安全，建议使用的格式,MySQL8.0默认格式 mysql> show variables like 'binlog_format'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | ROW | +---------------+-------+ 1 row in set (0.00 sec)mysql> select @@sql_log_bin; +---------------+ | @@sql_log_bin | +---------------+ | 1 | +---------------+ 1 row in set (0.00 sec) mysql> select @@log_bin; +-----------+ | @@log_bin | +-----------+ | 1 | +-----------+ 1 row in set (0.00 sec)3.6.实战案例：恢复误删除的表【案例】:warning:案例说明：每天2：30做完全备份，早上10：00误删除了表students，10：10才发现故障，现需要将数据库还原到10：10的状态，且恢复被删除的scores表3.6.1.安装Mysql，启用二进制日志，并导入测试文件# 安装mysql yum -y install mysql-server mysql # 检查是否启用二进制日志（备份） mysql> select @@sql_log_bin; mysql> select @@log_bin; mysql> show variables like 'binlog_format'; # 修改root密码 mysql> select user,host from mysql.user; mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword'; # 导入测试文件 mysql <hellodb_innodb.sql3.6.2.检查二进制日志ll /var/lib/mysql3.6.3.备份所有数据库mkdir /backup mysqldump -uroot -p123456 -A --source-data=2 >/backup/all.sql3.6.4.删表---【模拟数据库破坏】:warning:mysql> use hellodb; mysql> drop tables scores;3.6.5.增加数据---【模拟数据库破坏】:warning:mysql> insert into teachers (name,age,gender)values('a',18,'M'),('b',20,'F');3.6.6.模拟数据库恢复【备份文件+二进制日志】head /backup/all.sql -n 303.6.7.检查二进制日志ll /var/lib/mysql3.6.8.导出二进制日志文件mysqlbinlog --start-position=21693 /var/lib/mysql/binlog.000001 >/backup/logbin.sql grep -ni drop /backup/logbin.sql #36行 #awk 'NR==36' /backup/logbin.sql #删除这行 sed -i.bak '/^DROP TABLE `scores`/d' /backup/logbin.sql sed -Ei.bak '/^DROP TABLE `scores`/s/^/#/' /backup/logbin.sql3.6.9.拷贝到测试机进行还原【测试机还原】:warning:测试机需要相同的mysql版本环境rsync -avz /backup/logbin.sql /backup/all.sql root@10.0.0.38:yum -y install mysql-server mysql -uroot # 临时关闭二进制日志 mysql> set sql_log_bin=0; Query OK, 0 rows affected (0.00 sec) mysql> source all.sql; mysql> source logbin.sql; # 临时开启二进制日志 mysql> set sql_log_bin=1; Query OK, 0 rows affected (0.00 sec)3.6.10.检查是否还原【success】3.6.11.导入生产环境进行恢复单表备份数据恢复 3.7.保留备份--模拟删除数据库yum -y install mysql-server # 备份数据 mysqldump -uroot -p123456 -A -F --single-transaction --source-data=2 >/backup/all.sql systemctl stop mysqld rm -rf /data/mysql/* systemctl start mysqld #数据库恢复 mysql -uroot </backup/all.sql #刷新权限 mysql> flush privileges;4.xtrabackup 备份工具XtraBackup：https://www.percona.com/downloads5.Mysql主从复制5.1.主从复制架构及原理:star::star::star::star::star::star:主从复制相关线程：主节点：dump Thread : 为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events从节点：I/O Thread ：向Master请求二进制日志事件，并保存于中继日志中SQL Thread : 从中继日志中读取日志事件，在本地完成重放主从复制的基本原理：主从复制基于二进制日志（Binary Log, binlog）和中继日志（Relay Log）来实现。主服务器（Master）：记录所有对数据库进行更改的 SQL 语句（如 INSERT、UPDATE、DELETE 等）到二进制日志（binlog）中。将 binlog 发送给从服务器。从服务器（Slave）：接收主服务器发送的 binlog，并将其写入中继日志（relay log）。从中继日志中读取 SQL 语句，并在从服务器上执行这些语句，从而保持与主服务器数据的一致性。5.2.实现主从复制配置【实验】【Mysql.8.0.36】:star::star:yum -y install mysql-serverreset slave all;#删除同步信息5.2.1.主节点配置5.2.1.1.启用二进制日志5.2.1.2.为当前节点设置一个全局惟一的ID号vim /etc/my.cnf [mysqld] server-id=8 log-bin=/data/mysql/logbin/mysql-bin# 存储二进制日志文件夹 mkdir -p /data/mysql/logbin/ chown -R mysql.mysql /data/mysql/ # 开机启动 systemctl enable --now mysqld5.2.1.3.查看从二进制日志的文件和位置开始进行复制mysql> show master status;5.2.1.4.创建有复制权限的用户账号# MySQL8.0 分成两步实现 # 创建账号 mysql> create user repluser@'10.0.0.%' identified by '123456'; # 授权 mysql> grant replication slave on *.* to repluser@'10.0.0.%';5.2.2.从节点配置5.2.2.1.启用二进制日志5.2.2.2.为当前节点设置一个全局惟一的ID号vim /etc/my.cnf [mysqld] server-id=18 #为当前节点设置一个全局惟的ID号 read_only #设置数据库只读，针对supper user无效 log-bin=/data/mysql/logbin/mysql-bin# 存储二进制日志文件夹 mkdir -p /data/mysql/logbin/ chown -R mysql.mysql /data/mysql/ # 开机启动 systemctl enable --now mysqld5.2.2.3.查看主从同步状态，设置从库读取主库的服务器配置mysql> help change master to mysql> show slave status; CHANGE MASTER TO MASTER_HOST='10.0.0.8', MASTER_USER='repluser', MASTER_PASSWORD='123456', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=157, MASTER_CONNECT_RETRY=2;主从复制命令参数说明: master_host : 主数据库的IP地址; master_port : 主数据库的运行端口; master_user : 在主数据库创建的用于同步数据的用户账号; master_password : 在主数据库创建的用于同步数据的用户密码; master_log_file : 指定从数据库要复制数据的日志文件，通过查看主数据的状态，获取File参数; master_log_pos : 指定从数据库从哪个位置开始复制数据，通过查看主数据的状态，获取Position参数; master_connect_retry : 连接失败重试的时间间隔，单位为秒。5.2.2.4.开启线程，查看状态mysql> show processlist; mysql> start slave; mysql> show slave status\G;5.3.主从复制【模拟企业环境--备份】5.3.1.主节点【先整体备份数据库】vim /etc/my.cnf [mysqld] server-id=8 log-bin=/data/mysql/logbin/mysql-bin# 存储二进制日志文件夹 mkdir -p /data/mysql/logbin/ chown -R mysql.mysql /data/mysql/ # 开机启动 systemctl enable --now mysqldmysql <hellodb_innodb.sql # MySQL8.0 分成两步实现 # 创建账号 mysql> create user repluser@'10.0.0.%' identified by '123456'; # 授权 mysql> grant replication slave on *.* to repluser@'10.0.0.%'; mysql> show master status; # 数据库备份 mysqldump -uroot -A -F --single-transaction --source-data=1 >/data/full_back.sql #拷贝到从节点上 scp /data/full_back.sql 10.0.0.18:/data/5.3.2.从节点vim /etc/my.cnf [mysqld] server-id=18 read_only log-bin=/data/mysql/logbin/mysql-bin# 存储二进制日志文件夹 mkdir -p /data/mysql/logbin/ chown -R mysql.mysql /data/mysql/ # 开机启动 systemctl enable --now mysqld5.3.3.从节点还原数据库临时将二进制日志关闭，恢复数据后再开启可以将change master to放在修改文件里也可以直接进入mysql，先还原数据库，再change maseter CHANGE MASTER TO MASTER_HOST='10.0.0.8', MASTER_USER='repluser', MASTER_PASSWORD='123456', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=157, MASTER_CONNECT_RETRY=2;# 临时关闭二进制日志 mysql> set sql_log_bin=0; mysql> source /data/full_back.sql; # 检查工作 mysql> show slave status\G; # 开启线程 mysql> start slave; mysql> show processlist; mysql> show slave status\G; # 开启二进制日志 mysql> set sql_log_bin=1;5.4.下载mysql-server及依赖包到本地mkdir mysql-server-packages-8.0.36 cd mysql-server-packages-8.0.36 yum install yum-utils -y yumdownloader --resolve mysql-server # 安装 yum -y install *5.5.三台主机实现级联复制需要在中间的从服务器启用以下配置 ，实现中间slave节点能将master的二进制日志在本机进行数据库更新，并且也同时更新本机的二进制，从而实现级联复制 同前面#在10.0.0.8充当master #在10.0.0.18充当级联slave #在10.0.0.28充当slave18机器vim /etc/my.cnf [mysqld] server-id=18 read_only log-bin=/data/mysql/logbin/mysql-bin #级联复制中间节点的必选项,MySQL8.0此为默认值,可以不要人为添加,其它版本默认不开启 log_slave_updates mysqldump -uroot -A -F --single-transaction --source-data=1 >/data/all.sql scp /data/all.sql 10.0.0.28:28机器CHANGE MASTER TO MASTER_HOST='10.0.0.18', MASTER_USER='repluser', MASTER_PASSWORD='123456', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=157, MASTER_CONNECT_RETRY=2;mysql> set sql_log_bin=0; mysql> source all.sql; mysql> show slave status\G; mysql> start slave; mysql> show slave status\G; mysql> set sql_log_bin=1;5.6.主主复制【会发生冲突】:warning::warning::warning:在一主一从基础上机器修改都一样vim /etc/my.cnf [mysqld] server-id=18 #read_only log-bin=/data/mysql/logbin/mysql-bin #log_slave_updates8机器，需要在18机器查看下日志位置CHANGE MASTER TO MASTER_HOST='10.0.0.18', MASTER_USER='repluser', MASTER_PASSWORD='123456', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=465;18机器，查位置8机器测试添加记录是否同步mysql> insert into teachers (name,age,gender)values('Libai',20,'M'); mysql> insert into teachers (name,age,gender)values('王五',50,'F'); mysql> select * from teachers;5.7.实现半同步复制【主从复制基础上】【Rocky8】:one:主服务器安装插件8#主服务器配置: mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; #永久安装插件 mysql> UNINSTALL PLUGIN rpl_semi_sync_master ; #删除插件 mysql> SHOW GLOBAL VARIABLES LIKE '%semi%'; mysql> SHOW PLUGINS; #查看插件 mysql> SHOW GLOBAL STATUS LIKE '%semi%'; #查看有几个从节点，客户端:two:从服务器安装插件18#从服务器配置: mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; #永久安装插件 mysql> UNINSTALL PLUGIN rpl_semi_sync_slave ; #删除插件 mysql> SHOW GLOBAL VARIABLES LIKE '%semi%'; mysql> SHOW PLUGINS; #查看插件:three:主服务器8vim /etc/my.cnf [mysqld] server-id=8 log-bin=/data/mysql/logbin/mysql-bin rpl_semi_sync_master_enabled #修改此行,需要先安装semisync_master.so插件后,再重启,否则无法启动 rpl_semi_sync_master_timeout=3000 #设置3s内无法同步，也将返回成功信息给客户端 systemctl restart mysqld mysql> select @@rpl_semi_sync_master_enabled;:four:从服务器18vim /etc/my.cnf [mysqld] server-id=18 log-bin=/data/mysql/logbin/mysql-bin rpl_semi_sync_slave_enabled #修改此行,需要先安装semisync_slave.so插件后,再重启,否则无法启动 systemctl restart mysqld mysql> select @@rpl_semi_sync_slave_enabled;:five:从服务器28vim /etc/my.cnf [mysqld] server-id=28 log-bin=/data/mysql/logbin/mysql-binmkdir -p /data/mysql/logbin/ chown -R mysql.mysql /data/mysql/ systemctl start mysqld安装插件，更改配置，重启服务#从服务器配置: mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; #永久安装插件 mysql> UNINSTALL PLUGIN rpl_semi_sync_slave ; #删除插件 mysql> SHOW GLOBAL VARIABLES LIKE '%semi%'; mysql> SHOW PLUGINS; #查看插件vim /etc/my.cnf [mysqld] server-id=28 log-bin=/data/mysql/logbin/mysql-bin rpl_semi_sync_slave_enabled systemctl restart mysqld mysql> select @@rpl_semi_sync_slave_enabled;CHANGE MASTER TO MASTER_HOST='10.0.0.8', MASTER_USER='repluser', MASTER_PASSWORD='123456', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=157, MASTER_CONNECT_RETRY=2;6.复制的问题和解决方案:star::star::star::star::star:6.1.数据损坏或丢失Master：MHA + semisync replicationSlave： 重新复制6.2.不惟一的 server id解决方法: 重新复制6.3.复制延迟升级到MySQL5.7以上版本(5.7之前的版本，没有开GTID之前，主库可以并发事务，但是dump传输时是串行)利用GTID(MySQL5.6需要手动开启,MySQL5.7以上默认开启)支持并发传输binlog及并行多个SQL线程减少大事务,将大事务拆分成小事务减少锁sync_binlog=1 加快binlog更新时间,从而加快日志复制需要额外的监控工具的辅助多线程复制：对多个数据库复制一从多主：Mariadb10 版后支持6.4.MySQL 主从数据不一致6.4.1.造成主从不一致的原因主库binlog格式为Statement，同步到从库执行后可能造成主从不一致。主库执行更改前有执行set sql_log_bin=0，会使主库不记录binlog，从库也无法变更这部分数据。从节点未设置只读，误操作写入数据主库或从库意外宕机，宕机可能会造成binlog或者relaylog文件出现损坏，导致主从不一致主从实例版本不一致，特别是高版本是主，低版本为从的情况下，主数据库上面支持的功能，从数据库上面可能不支持该功能主从sql_mode 不一致MySQL自身bug导致6.4.2.主从不一致修复方法:one:将从库重新实现虽然这也是一种解决方法，但是这个方案恢复时间比较慢，而且有时候从库也是承担一部分的查询操作的，不能贸然重建。:two:使用percona-toolkit工具辅助PT工具包中包含pt-table-checksum和pt-table-sync两个工具，主要用于检测主从是否一致以及修复数据不一致情况。这种方案优点是修复速度快，不需要停止主从辅助，缺点是需要知识积累，需要时间去学习，去测试，特别是在生产环境，还是要小心使用关于使用方法，可以参考下面链接：https://www.cnblogs.com/feiren/p/7777218.html:three:手动重建不一致的表在从库发现某几张表与主库数据不一致，而这几张表数据量也比较大，手工比对数据不现实，并且重做整个库也比较慢，这个时候可以只重做这几张表来修复主从不一致这种方案缺点是在执行导入期间需要暂时停止从库复制，不过也是可以接受的:four:范例：A,B,C这三张表主从数据不一致1、从库停止Slave复制 mysql> stop slave; 2、在主库上dump这三张表，并记录下同步的binlog和POS点 mysqldump -uroot -pmagedu -q --single-transaction --master-data=2 testdb A B C >/backup/A_B_C.sql 3、查看A_B_C.sql文件，找出记录的binlog和POS点 head A_B_C.sql 例如:MASTERLOGFILE='mysql-bin.888888', MASTERLOGPOS=666666; #以下指令是为了保障其他表的数据不丢失，一直同步直到那个点结束，A,B,C表的数据在之前的备份已 经生成了一份快照，只需要导入进入，然后开启同步即可 4、把A_B_C.sql拷贝到Slave机器上，并做指向新位置 mysql> start slave until MASTERLOGFILE='mysql-bin.888888',MASTERLOGPOS=666666; 5、在Slave机器上导入A_B_C.sql mysql -uroot -pmagedu testdb mysql> set sql_log_bin=0; mysql> source /backup/A_B_C.sql mysql> set sql_log_bin=1; 6、导入完毕后，从库开启同步即可。 mysql> start slave;6.4.3.如何避免主从不一致主库binlog采用ROW格式主从实例数据库版本保持一致主库做好账号权限把控，不可以执行set sql_log_bin=0从库开启只读，不允许人为写入定期进行主从一致性检验7.实现MHA实战案例项目我在工作中设置了mysql主从，设置了半同步，设置了GTD，并且我又添加了MHA 环境：四台主机ip版本角色10.0.0.7Centos7MHA管理端10.0.0.8Rocky8MySQL8.0 Master10.0.0.18Rocky8MySQL8.0 Slave110.0.0.28Rocky8MySQL8.0 Slave27.1.在管理节点上安装两个包mha4mysql-manager和mha4mysql-node【manager】说明:mha4mysql-manager-0.58-0.el7.centos.noarch.rpm 只支持CentOS7上安装, 不支持在CentOS8安装,支持MySQL5.7和MySQL8.0 ,但和CentOS8版本上的Mariadb-10.3.17不兼容 mha4mysql-manager-0.56-0.el6.noarch.rpm 不支持CentOS 8，只支持CentOS7及以下版本两个安装包：mha4mysql-manager mha4mysql-node #下载 https://github.com/yoshinorim/mha4mysql-manager/wiki/Downloads https://github.com/yoshinorim/mha4mysql-node/releases/tag/v0.58 https://github.com/yoshinorim/mha4mysql-node/releases/tag/v0.58案例：yum -y install epel-release yum -y install mha4mysql-manager-0.58-0.el7.centos.noarch.rpm mha4mysql-node-0.58-0.el7.centos.noarch.rpmrpm -ql mha4mysql-manager7.2.在所有MySQL服务器上安装mha4mysql-node包此包支持CentOS 8，7，6yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm7.3.在所有节点实现基于 ssh-key 的免密登录#生成密钥对，并在当前主机完成C/S校验 [root@mha-manager ~]# ssh-keygen [root@mha-manager ~]# ssh-copy-id 127.0.0.1 [root@mha-manager ~]# ll .ssh #分发 [root@mha-manager ~]# rsync -av .ssh 10.0.0.8:/root/ [root@mha-manager ~]# rsync -av .ssh 10.0.0.18:/root/ [root@mha-manager ~]# rsync -av .ssh 10.0.0.28:/root/ #测试ssh连接 ......7.4.在 mha-manager 节点创建相关配置文件mkdir /etc/mastermha/ vim /etc/mastermha/app1.cnf [server default] user=mhauser #用于远程连接MySQL所有节点的用户,需要有管理员的权限 password=123456 manager_workdir=/data/mastermha/app1/ #目录会自动生成,无需手动创建 manager_log=/data/mastermha/app1/manager.log #当前集群的日志 remote_workdir=/data/mastermha/app1/ #mysql 节点mha 工作目录，会自动创建 ssh_user=root #用于实现远程ssh基于KEY的连接,访问二进制日志 repl_user=repluser #主从复制的用户信息 repl_password=123456 ping_interval=1 #健康性检查的时间间隔，manager节点对于master节点的心跳检测时间间隔 master_ip_failover_script=/usr/local/bin/master_ip_failover #切换VIP的perl脚本,不支持跨网络,也可用Keepalived实现 report_script=/usr/local/bin/sendmail.sh #当执行报警脚本 check_repl_delay=0 #默认值为1,表示如果slave中从库落后主库relay log超过100M，主库不会选择这个从库为新的master， #因为这个从库进行恢复需要很长的时间.通过设置参数check_repl_delay=0，mha触发主从切换时会忽略复制的延时， #对于设置candidate_master=1的从库非常有用，这样确保这个从库一定能成为最新的master master_binlog_dir=/data/mysql/logbin/ #指定二进制日志存放的目录,mha4mysql-manager-0.58必须指定,之前版本不需要指定 [server1] hostname=10.0.0.8 port=3306 candidate_master=1 [server2] hostname=10.0.0.18 port=3306 [server3] hostname=10.0.0.28 port=3306 candidate_master=1 #设置为优先候选master，即使不是集群中事件最新的slave,也会优先当master[server default] user=mhauser password=123456 manager_workdir=/data/mastermha/app1/ manager_log=/data/mastermha/app1/manager.log remote_workdir=/data/mastermha/app1/ ssh_user=root repl_user=repluser repl_password=123456 ping_interval=1 master_ip_failover_script=/usr/local/bin/master_ip_failover report_script=/usr/local/bin/sendmail.sh check_repl_delay=0 master_binlog_dir=/data/mysql/logbin/ [server1] hostname=10.0.0.8 port=3306 candidate_master=1 master_binlog_dir="/data/mysql/" [server2] hostname=10.0.0.18 port=3306 master_binlog_dir="/data/mysql/" [server3] hostname=10.0.0.28 port=3306 candidate_master=1 master_binlog_dir="/data/mysql/"说明: 主库宕机谁来接管新的master提升 slave 节点为 master 节点的策略 1. 所有从slave 节点日志都是一致的，默认会以配置文件的顺序去选择一个新主 2. 从节点日志不一致，自动选择数据量最接近于主库的从库充当新主，将其提升为master 节点。 3. 如果对于某节点设定了权重（candidate_master=1），权重节点会优先选择。但是此节点日志量落后主库超过100M日志的话，也不会被选择。 4. 可以配合check_repl_delay=0，关闭日志量的检查，强制选择候选节点7.5.发送邮件脚本:star::star::star::star::star:# 告警消息脚本 [root@mha-manager ~]# cat /usr/local/bin/sendmail.sh #!/bin/bash echo "MHA is failover!" | mail -s "MHA Warning" 649352141@qq.com [root@mha-manager ~]# chmod +x /usr/local/bin/sendmail.sh # 安装邮件服务 [root@mha-manager ~]# yum install mailx postfix # 邮件服务配置 [root@mha-manager ~]# vim /etc/mail.rc # 加在最下面 #发件箱 set from="2636775731@qq.com" # 配置的第三方smtp服务器的地址及端口 set smtp=smtp://smtp.qq.com:587 #发件人 set smtp-auth-user="2636775731@qq.com" #授权码 set smtp-auth-password=ttnvcrlfgywididb # 认证方式 set smtp-auth=login #开启ssl set ssl-verify=ignore set smtp-use-starttls=yes #证书目录，下方为centos系统证书默认位置，也自行生成证书并指定 set nss-config-dir=/etc/pki/nssdb --说明 from：对方收到邮件时显示的发件人 smtp：指定第三方发邮件的smtp服务器地址,云服务器必须使用465端口默认25端口被禁用 set smtp-auth-user：第三方发邮件的用户名 set smtp-auth-password： 邮箱的授权码注意不是密码 #echo "测试邮件" | mail -s "发送成功" 2636775731@qq.com &>/dev/null # 重新启动postfix [root@mha-manager ~]# systemctl restart postfix.service # 测试告警邮件 [root@mha-manager ~]# sendmail.sh[root@mha-manager ~]# vim /usr/local/bin/master_ip_failover #!/usr/bin/env perl # Copyright (C) 2011 DeNA Co.,Ltd. # # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by # the Free Software Foundation; either version 2 of the License, or # (at your option) any later version. # # This program is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # GNU General Public License for more details. # # You should have received a copy of the GNU General Public License # along with this program; if not, write to the Free Software # Foundation, Inc., # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA ## Note: This is a sample script and is not complete. Modify the script based on your environment. use strict; use warnings FATAL => 'all'; use Getopt::Long; use MHA::DBHelper; my ( $command, $ssh_user, $orig_master_host, $orig_master_ip, $orig_master_port, $new_master_host, $new_master_ip, $new_master_port, $new_master_user, $new_master_password ); # 修改内容 my $vip = '10.0.0.100/24'; #virtually IP，此IP会在不同的MySQL节点漂移 my $key = "1"; my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip"; #在网卡上添加IP，确保每台 MySQL 节点网卡名一样 my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down"; GetOptions( 'command=s' => \$command, 'ssh_user=s' => \$ssh_user, 'orig_master_host=s' => \$orig_master_host, 'orig_master_ip=s' => \$orig_master_ip, 'orig_master_port=i' => \$orig_master_port, 'new_master_host=s' => \$new_master_host, 'new_master_ip=s' => \$new_master_ip, 'new_master_port=i' => \$new_master_port, 'new_master_user=s' => \$new_master_user, 'new_master_password=s' => \$new_master_password, ); exit &main(); sub main { if ( $command eq "stop" || $command eq "stopssh" ) { # $orig_master_host, $orig_master_ip, $orig_master_port are passed. # If you manage master ip address at global catalog database, # invalidate orig_master_ip here. my $exit_code = 1; eval { # updating global catalog, etc $exit_code = 0; }; if ($@) { warn "Got Error: $@\n"; exit $exit_code; } exit $exit_code; } elsif ( $command eq "start" ) { # all arguments are passed. # If you manage master ip address at global catalog database, # activate new_master_ip here. # You can also grant write access (create user, set read_only=0, etc) here. my $exit_code = 10; eval { print "Enabling the VIP - $vip on the new master - $new_master_host \n"; &start_vip(); &stop_vip(); $exit_code = 0; }; if ($@) { warn $@; exit $exit_code; } exit $exit_code; } elsif ( $command eq "status" ) { print "Checking the Status of the script.. OK \n"; `ssh $ssh_user\@$orig_master_host \" $ssh_start_vip \"`; exit 0; } else { &usage(); exit 1; } } sub start_vip() { `ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`; } # A simple system call that disable the VIP on the old_master sub stop_vip() { `ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`; } sub usage { print "Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n"; }# 加上执行权限 chmod +x /usr/local/bin/master_ip_failover7.6.实现Master# 在 master 节点配置 VIP，此IP会在不同 MySQL 节点上漂移 ifconfig eth0:1 10.0.0.100/24 yum -y install mysql-server vim /etc/my.cnf [mysqld] server-id=8 log-bin=/data/mysql/mysql-bin mkdir -p /data/mysql/ chown -R mysql.mysql /data/mysql/ systemctl restart mysqld[root@master ~]#mysql mysql> show master logs; # 如果是MySQL8.0执行下面操操作 #创建主从同步账号并授权 mysql> create user repluser@'10.0.0.%' identified by '123456'; mysql> grant replication slave on *.* to repluser@'10.0.0.%'; #创建 mha-manager 使用的账号并授权 mysql> create user mhauser@'10.0.0.%' identified by '123456'; mysql> grant all on *.* to mhauser@'10.0.0.%'; # 如果是MySQL8.0以前版本执行下面操操作 mysql> grant replication slave on *.* to repluser@'10.0.0.%' identified by '123456'; mysql> grant all on *.* to mhauser@'10.0.0.%' identified by '123456';7.7.实现slave1yum -y install mysql-server vim /etc/my.cnf [mysqld] server-id=18 read-only log-bin=/data/mysql/mysql-bin mkdir -p /data/mysql/ chown -R mysql.mysql /data/mysql/ systemctl restart mysqld[root@slave1 ~]#mysql mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.8', MASTER_USER='repluser', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=157; mysql> start slave; mysql> show slave status\G;# 重置slave mysql> stop slave; mysql> reset slave all;7.8.实现slave2yum -y install mysql-server vim /etc/my.cnf [mysqld] server-id=28 #不同节点此值各不相同 read-only log-bin=/data/mysql/mysql-bin mkdir -p /data/mysql/ chown -R mysql.mysql /data/mysql/ systemctl restart mysqld[root@slave1 ~]#mysql mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.8', MASTER_USER='repluser', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=157; mysql> start slave; mysql> show slave status\G;# 重置slave mysql> stop slave; mysql> reset slave all;7.9.检查MHA的环境#检查环境 #配置和 SSH 连接检查 vim /etc/mastermha/app1.cnf #主从复制检查，会在mysql节点自动创建 remote_workdir=/data/mastermha/app1/ masterha_check_ssh --conf=/etc/mastermha/app1.cnf masterha_check_repl --conf=/etc/mastermha/app1.cnf #查看当前mysql 集群状态 masterha_check_status --conf=/etc/mastermha/app1.cnf7.10.启动MHA#开启MHA,默认是前台运行,生产环境一般为后台执行，并且与终端分离 nohup masterha_manager --conf=/etc/mastermha/app1.cnf --remove_dead_master_conf --ignore_last_failover &> /dev/null #测试环境： #masterha_manager --conf=/etc/mastermha/app1.cnf --remove_dead_master_conf --ignore_last_failover #如果想停止后台执行的MHA,可以执行下面命令 masterha_stop --conf=/etc/mastermha/app1.cnf Stopped app1 successfully. #查看状态 masterha_check_status --conf=/etc/mastermha/app1.cnf #查看生成的文件 tree /data/mastermha/app1/ #查看日志 cat /data/mastermha/app1/manager.log#开启 master 节点通用日志 mysql> set global general_log=1; Query OK, 0 rows affected (0.01 sec)tail -f /var/lib/mysql/Rocky8.log7.11.排错日志tail /data/mastermha/app1/manager.log7.12.模拟故障#模拟故障 [root@master ~]#systemctl stop mysqld #当 master down机后,mha管理程序自动退出 [root@mha-manager ~]#masterha_manager --conf=/etc/mastermha/app1.cnf[root@mha-manager ~]#cat /data/mastermha/app1/manager.log [root@mha-manager ~]#masterha_check_status --conf=/etc/mastermha/app1.cnf #验证VIP漂移至新的Master上 [root@slave1 ~]#ip a #自动修改manager节点上的配置文件,将master剔除 [root@mha-manager ~]#cat /etc/mastermha/app1.cnf [server2] candidate_master=1 hostname=10.0.0.18 master_binlog_dir="/data/mysql/" port=3306 [server3] hostname=10.0.0.28 master_binlog_dir="/data/mysql/" port=3306注意: 如果出错,需要删除下面文件再执行MHA[root@mha-manager ~]#rm -f /data/mastermha/app1/app1.failover.error7.13.修复主从修复故障的主库,保证数据同步 修复主从,手工新故障库加入新的主,设为为从库 修复manager的配置文件 清理相关目录 检查ssh互信和replication的复制是否成功 检查VIP,如果有问题,重新配置VIP 重新运行MHA,查询MHA状态,确保运行正常7.14.如果再次运行MHA,需要先删除下面文件MHA只能漂移一次，如果多次使用必须删除以下文件，要不MHA不可重用[root@mha-manager ~]#rm -rf /data/mastermha/app1/ # mha_master自己的工作路径 [root@mha-manager ~]#rm -rf /data/mastermha/app1/manager.log #m ha_master自己的日志文件 [root@master ~]#rm -rf /data/mastermha/app1/ # 每个远程主机即三个节点的的工作目录endl

mysql数据库服务版本升级 @TOC1.MySQL企业应用的版本分析:star::star::star::star::star:官网: https://downloads.mysql.com/archives/community/序号大版本应用小版本应用01MySQL 5.65.6.36 5.6.38 5.6.40 5.6.46 (GA 6-12月)2021 01月 常规/扩展服务全部停止02MySQL 5.7 （应用更加广泛）5.7.20 5.7.22 5.7.24 5.7.26 ..... 5.7.40 (GA最新双数版本)2023 10月 常规/扩展服务全部停止03MySQL 8.0 (属于最新版本)8.0.11 8.0.17+ 8.0.18 8.0.26 8.0.32 (GA最新双数版本)MySQL企业应用的发布版本：C : 表示为社区版本，属于开源免费版本E : 表示为企业版本，属于开源盈利版本2.数据库服务下载安装2.1.官网下载官网: https://downloads.mysql.com/archives/community/2.2.下载安装Mysql8.0.26:star::star:2.2.1.系统环境准备cat /etc/redhat-release CentOS Linux release 7.9.2009 (Core) ip a 10.0.0.8 vi /etc/hosts 10.0.0.8 master systemctl is-active firewalld -- 防火墙服务关闭 unknown systemctl is-enabled firewalld disabled getenforce -- selinux关闭 Disabled rpm -qa|grep mariadb --卸载与mysql冲突的软件包 yum remove -y mariadb-libs yum install -y libaio-devel -- 解决依赖2.2.2.软件安装部署tar xf mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz ln -s mysql-8.0.26-linux-glibc2.12-x86_64 mysql #配置环境变量： vi /etc/profile export PATH=$PATH:/usr/local/mysql/bin source /etc/profile # 可能报错，解决方法 # ln -s /usr/lib64/libtinfo.so.6 /usr/lib64/libtinfo.so.5 mysql -V #创建数据库存储目录 mkdir -p /data/3306/data #创建数据库服务管理用户 groupadd -r mysql useradd -r -g mysql -s /sbin/nologin mysql id mysql chown -R mysql.mysql /data/3306/data 2.2.3.软件初始化过程mariadb -- mysql -- show databases -- mysql -- use mysql -- show tables -- user mysqld --initialize-insecure --user=mysql --datadir=/data/3306/data --basedir=/usr/local/mysql --initialize-insecure:不安全初始化 #表示数据库启动后，没有密码信息 --initialize：安全初始化 #表示数据库启动后，会有默认随机密码初始化失败常见错误：1）依赖软件没有安装yum install -y libaio-devel2）数据库无法应用初始化参数信息书写有问题数据目录不是空目录3）用户是否创建/数据目录是否创建/程序目录路径是否指定准确2.2.4.编写配置文件cat > /etc/my.cnf <<EOF [mysql] socket=/tmp/mysql.sock [mysqld] user=mysql basedir=/usr/local/mysql datadir=/data/3306/data socket=/tmp/mysql.sock EOF2.2.5.启动数据库服务cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld /etc/init.d/mysqld start netstat -lntup | grep mysql # 进入数据库 mysql2.2.6.数据库安装或启动失败排错方法方式一：查看错误日志文件 ll /data/3306/data/master.err 方式二：没有错误日志文件 查看配置文件信息 方式三：进程或端口信息冲突2.2.7.报错信息[14:17:03 root@Rocky8 bin]# mysql --version mysql: error while loading shared libraries: libtinfo.so.5: cannot open shared object file: No such file or directory2.3.下载安装Mysql5.6.48:star::star:2.3.1.下载软件程序包cd /usr/local/ wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.48-linux-glibc2.12-x86_64.tar.gz2.3.2.解压安装软件程序tar xf mysql-5.6.48-linux-glibc2.12-x86_64.tar.gz ln -s mysql-5.6.48-linux-glibc2.12-x86_64 mysql56 #配置环境变量： vi /etc/profile export PATH=$PATH:/usr/local/mysql56/bin source /etc/profile # 可能报错，解决方法 # ln -s /usr/lib64/libncurses.so.6.1 /usr/lib64/libncurses.so.5 mysql -V #创建数据库存储目录 mkdir -p /data/3356/data #创建数据库服务管理用户 groupadd -r mysql useradd -r -g mysql -s /sbin/nologin mysql id mysql chown -R mysql.mysql /data/3356/data2.3.3.初始化数据库服务/usr/local/mysql56/scripts/mysql_install_db --user=mysql --datadir=/data/3356/data --basedir=/usr/local/mysql56/2.3.4.编写配置文件cat >/data/3356/data/my.cnf<<EOF [mysqld] server_id=3356 port=3356 user=mysql basedir=/usr/local/mysql56 datadir=/data/3356/data socket=/tmp/mysql3356.sock EOF2.3.5.启动数据库服务cp /usr/local/mysql56/support-files/mysql.server /etc/init.d/mysqld /etc/init.d/mysqld start或者cat >/usr/lib/systemd/system/mysqld3356.service<<EOF [Unit] Description=MySQL Server Documentation=mysqld.service After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/usr/local/mysql56/bin/mysqld --defaults-file=/etc/my.cnf LimitNOFILE = 5000 EOF systemctl daemon-reload systemctl start mysqld3356 netstat -lntup | grep mysqlmysql mysql -uroot -S /tmp/mysql3356.sock2.4.下载安装Mysql5.7.30:star::star:2.4.1.下载软件程序包cd /usr/local/ wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz2.4.2.解压安装软件程序tar xf mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz ln -s mysql-5.7.30-linux-glibc2.12-x86_64 mysql57 #配置环境变量： vi /etc/profile export PATH=$PATH:/usr/local/mysql57/bin source /etc/profile # 可能报错，解决方法 # ln -s /usr/lib64/libncurses.so.6 /usr/lib64/libncurses.so.5 # ln -s /usr/lib64/libtinfo.so.6 /usr/lib64/libtinfo.so.5 mysql -V #创建数据库存储目录 mkdir -p /data/3357/data #创建数据库服务管理用户 groupadd -r mysql useradd -r -g mysql -s /sbin/nologin mysql id mysql chown -R mysql.mysql /data/3357/data2.4.3.初始化数据库服务/usr/local/mysql57/bin/mysqld --initialize-insecure --user=mysql --datadir=/data/3357/data --basedir=/usr/local/mysql572.4.4.编写配置文件cat >/data/3357/data/my.cnf <<EOF [mysqld] server_id=3357 port=3357 user=mysql basedir=/usr/local/mysql57 datadir=/data/3357/data socket=/tmp/mysql3357.sock EOF2.4.5.启动数据库服务cat >/usr/lib/systemd/system/mysqld3357.service<<EOF [Unit] Description=MySQL Server Documentation=mysqld.service After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/usr/local/mysql57/bin/mysqld --defaults-file=/data/3357/data/my.cnf LimitNOFILE = 5000 EOF systemctl daemon-reload systemctl start mysqld3357 netstat -lntup | grep mysqlmysql mysql -uroot -S /tmp/mysql3357.sock2.6.生成测试数据create database company; use company; -- 创建部门表 DROP TABLE IF EXISTS dept; CREATE TABLE `dept` ( `deptno` int(11) PRIMARY KEY NOT NULL COMMENT '部门编号', `dname` varchar(14) NOT NULL COMMENT '部门名称', `loc` varchar(13) NOT NULL COMMENT '部门所在地' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='部门表'; -- 向部门表插入数据 INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK'), (20,'RESEARCH','DALLAS'), (30,'SALES','CHICAGO'), (40,'OPERATIONS','BOSTON'); -- 创建员工表 DROP TABLE IF EXISTS emp; CREATE TABLE `emp` ( `empno` int(11) PRIMARY KEY NOT NULL COMMENT '员工编号', `ename` varchar(10) DEFAULT NULL COMMENT '员工姓名', `job` varchar(9) DEFAULT NULL COMMENT '工作岗位', `mgr` int(11) DEFAULT NULL COMMENT '直属领导', `hiredate` date DEFAULT NULL COMMENT '入职时间', `sal` double DEFAULT NULL COMMENT '工资', `comm` double DEFAULT NULL COMMENT '奖金', `deptno` int(11) DEFAULT NULL COMMENT '所属部门' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工表'; -- 向员工表插入数据 INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,"1980-12-17",800,NULL,20), (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30), (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30), (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20), (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30), (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30), (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10), (7788,'SCOTT','ANALYST',7566,'1987-07-03',3000,NULL,20), (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10), (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30), (7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20), (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30), (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20), (7934,'MILLER','CLERK',7782,'1981-01-23',1300,NULL,10); -- 创建工资等级表 DROP TABLE IF EXISTS salgrade; CREATE TABLE `salgrade` ( `grade` int(11) DEFAULT NULL COMMENT '等级', `losal` double DEFAULT NULL COMMENT '最低工资', `hisal` double DEFAULT NULL COMMENT '最高工资' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='工资等级表'; -- 向工资等级表插入数据 INSERT INTO salgrade VALUES (1,700,1200),(2,1201,1400),(3,1401,2000),(4,2001,3000),(5,3001,9999);3.数据库版本升级介绍5.6.48 -- 5.7.30 -- 8.0.265.6.48 -- 5.7.30 -- 8.0.263.1.数据库版本升级方法：Inplace-就地 风险更高 单台数据库服务器 数据不能有损坏 升级过程中不能影响业务 :one: 在同一台服务器中，需要部署更高版本数据库服务实例 :two: 低版本数据库中的数据进行备份迁移，迁移到高版本服务中（物理备份方式--停止服务） :three: 运行启动高版本数据库服务实例，进行测试 :four: 停止低版本数据库服务，将业务迁移到新版数据库Mergeing-迁移 安全性更高 多台数据库服务器 :one: 在不同服务器中，可以部署更高版本数据库服务实例 :two: 低版本数据库中的数据进行备份迁移，迁移到高版本服务中（逻辑备份方式/主从同步） :three: 运行启动高版本数据库服务实例，进行测试 :four: 停止低版本数据库服务，将业务迁移到新版数据库3.2.数据库升级规则数据库服务官方参考资料: https://dev.mysql.com/doc/refman/8.0/en/upgrade-paths.html数据库版本升级规则：:one: 数据库服务版本升级时，只支持在GA（General Availability）版本之间进行升级:two: 数据库服务版本升级时，支持从数据库5.6到5.7再到8.0，跨版本升级，但是需要先将5.6升级到最新小版本，在进行跨版本升级:three: 数据库服务版本升级时，需要提前考虑好版本回退的方案，最好升级前做好数据备份（特别是向8.0版本升级）:four: 数据库服务版本升级时，制定的升级方案和升级步骤，需要尽可能降低数据库服务停机的时间3.3.数据库版本升级流程：:one: 数据库服务数据备份保存（可以采用热备和冷备两种方案，冷备是需要停止业务后备份，热备是无需停止业务备份）:two: 数据库服务最新程序安装（最新版本数据库服务安装过程时，无需停止原有数据库旧版服务）:three: 数据库服务原有程序关闭（网站显示维护页面）:four: 数据库服务最新程序启动（加载原有程序数据实现挂库升级，并采用跳过授权表和跳过网络方式启动）:five: 数据库服务升级数据结构（数据库服务升级程序后，还需要升级数据系统结构信息，因此升级时间和数据量无关）:six: 数据库服务可以正常重启（数据库服务升级完毕后，确认数据库服务是可以正常完成重启操作）:seven: 数据库服务功能测试验证（反复核实验证与数据库服务相关的各项功能是否正常）:eight: 数据库服务升级工作完毕（取消网站维护页面，恢复正常网站线上运营业务）4.数据库服务升级过程实战4.1.数据库版本升级步骤一：数据库服务器最新程序安装4.2.企业数据库实战练习一：演示 5.6.48 -> 5.7.30 本地升级4.2.1.进行数据库服务备份操作物理备份 逻辑备份-mysqldump 主从同步 4.2.2.将原有数据信息导入到新版数据库服务迁移恢复数据4.2.3.进行前期新版数据库功能测试web服务器（程序代码）--- 5.7数据库服务（业务数据） -- 浏览器访问测试4.2.4.停止旧版数据库服务 （网站维护页通知）systemctl stop mysqld3356.service 4.2.5.备份原有数据信息 :star::star:物理备份：cp -a /data/3356/data/ /backup/4.2.6.实现数据挂库升级 :star::star::star:cat /data/3356/data/my.cnf [mysqld] server_id=3356 port=3356 user=mysql basedir=/usr/local/mysql57 # 新版程序加载 datadir=/data/3356/data socket=/tmp/mysql3356.sock #配置环境变量： vi /etc/profile export PATH=$PATH:/usr/local/mysql57/bin source /etc/profile # 安全模式启动新版数据库服务 /usr/local/mysql57/bin/mysqld_safe --defaults-file=/data/3356/data/my.cnf --skip-grant-tables --skip-networking & # 实现对原有数据库中数据信息的挂库升级操作 /usr/local/mysql57/bin/mysql_upgrade -S /tmp/mysql3356.sock --force挂库升级：将数据目录结构调整将授权表结构调整4.2.7.重新正常启动新版数据库服务pkill mysql vim /usr/lib/systemd/system/mysqld3356.service ExecStart=/usr/local/mysql57/bin/mysqld --defaults-file=/data/3356/data/my.cnf systemctl daemon-reload systemctl start mysqld3356.service4.2.8.测试完毕后，撤销网站维护页面，恢复网站业务4.3.企业数据库实战练习二：演示 5.7.30 -> 8.0.26 本地升级4.3.1.进行数据库服务备份操作物理备份 逻辑备份-mysqldump 主从同步 4.3.2.将原有数据信息导入到新版数据库服务迁移恢复数据4.3.3.进行前期新版数据库功能测试web服务器（程序代码）--- 5.7数据库服务（业务数据） -- 浏览器访问测试4.3.4.停止旧版数据库服务 （网站维护页通知）systemctl stop mysqld33564.3.5.备份原有数据信息物理备份：cp -a /data/3356/data/ /backup/4.3.6.实现数据挂库升级cat /data/3356/data/my.cnf [mysqld] server_id=3356 port=3356 user=mysql basedir=/usr/local/mysql datadir=/data/3356/data socket=/tmp/mysql3356.sock #配置环境变量： vi /etc/profile export PATH=$PATH:/usr/local/mysql/bin source /etc/profile # 安全模式启动新版数据库服务 mysqld_safe --defaults-file=/data/3356/data/my.cnf --skip-grant-tables --skip-networking &挂库升级：将数据目录结构调整将授权表结构调整4.3.7.重新正常启动新版数据库服务pkill mysql vim /usr/lib/systemd/system/mysqld3356.service ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3356/data/my.cnf systemctl daemon-reload systemctl start mysqld3356.service4.4.升级失败，进行数据库升级回滚；4.4.1.停止数据库服务4.4.2.恢复备份数据目录cp -a /backup/data /data/3356/4.4.3.修改配置文件信息cat /data/3356/data/my.cnf -- 升级后配置文件 [mysqld] server_id=3356 port=3356 user=mysql basedir=/usr/local/mysql datadir=/data/3356/data socket=/tmp/mysql3356.sock cat /data/3356/data/my.cnf -- 升级失败回滚配置文件 [mysqld] server_id=3356 port=3356 user=mysql basedir=/usr/local/mysql57 datadir=/data/3356/data socket=/tmp/mysql3356.sock4.4.4.修改启动文件vim /usr/lib/systemd/system/mysqld3356.service -- 升级后调整 ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3356/data/my.cnf -- 回滚后 ExecStart=/usr/local/mysql/bin/mysqld57 --defaults-file=/data/3356/data/my.cnf4.4.5.恢复原本数据库服务systemctl daemon-reload systemctl start mysqld3356.serviceendl

Mycat核心教程(mycat实战应用) @TOC)一、MyCat概述1.1.Mycat 是数据库中间件数据库中间件中间件：是一类连接软件组件和应用的计算机软件，以便于软件各部件之间的沟通。例子：Tomcat，web中间件。数据库中间件：连接java应用程序和数据库1.2.为什么要用Mycat①Java与数据库紧耦合②高访问量高并发对数据库的压力③读写请求数据不一致1.3.数据库中间件对比①Cobar属于阿里B2B事业群，始于2008年，在阿里服役3年多，接管3000+个MySQL数据库的schema,集群日处理在线SQL请求50亿次以上。由于Cobar发起人的离职，Cobar停止维护。②Mycat是开源社区在阿里cobar基础上进行二次开发，解决了cobar存在的问题，并且加入了许多新的功能在其中。1.4.Mycat的官网1.4.1.Mycat的官网:http://www.mycat.org.cn/1.4.2.右上角下载里面有个文件下载服务，点进去发现无法访问1.5.MyCat功能1.5.1.读写分离1.5.2.数据分片：垂直拆分（分库）、水平拆分（分表）、垂直+水平拆分（分库分表）1.5.3.多数据源整合1.6.MyCat原理Mycat 的原理中最重要的一个动词是“拦截”，它拦截了用户发送过来的 SQL 语句，首先对 SQL 语句做了一些特定的分析：如分片分析、路由分析、读写分离分析、缓存分析等，然后将此 SQL 发往后端的真实数据库，并将返回的结果做适当的处理，最终再返回给用户。这种方式把数据库的分布式从代码中解耦出来，程序员察觉不出来后台使用Mycat 还是MySQL。二、MyCat_V1.0安装、启动、登录2.1.安装2.1.1.mycat安装方式-- 解压后，即可以使用rpm 方式，按安装顺序yum 方式，需要联网解压后，编译安装解压后，即可以使用上传到：/opt/mycat （放安装包）解压：tar -zxvf解压后的，拷贝到：/usr/local/mycat（cp -r ）三个配置文件： -- ①.schema.xml：定义 逻辑库、表、分片节点等内容。 -- ②.rule.xml：定义分片规则。 -- ③.server.xml：定义用户，以及系统相关变量，如端口等。2.1.2.上传到：/opt/mycat （放安装包）cd /opt/ tar -zxvf Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz2.1.3.解压后的，拷贝到：/usr/local/mycatcp -r mycat /usr/local ll /usr/local/ cd /usr/local/mycat/2.1.4.三个配置文件①schema.xml：定义逻辑库、表、分片节点等内容②rule.xml：定义分片规则③server.xml：定义用户，以及系统相关变量，如端口等2.2.启动2.2.1.修改配置文件server.xml修改用户信息，与MySQL区分 vi /usr/local/mycat/conf/server.xml2.2.2.修改配置文件schema.xmlvi /usr/local/mycat/conf/schema.xml删除标签间的表信息，标签只留一个，标签只留一个， 只留一对<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <!-- 虚拟逻辑库配置 与server.xml 中的数据库对应 ,TESTDB是逻辑数据库，指向映射的实际数据库--> <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"> <!-- 逻辑表配置 --> </schema> <!-- 数据节点配置 --> <!-- <dataNode>标签的database属性值必须是配置的真实数据库testdb，这个数据库可以是读主机中所有已有的数据库 --> <dataNode name="dn1" dataHost="host1" database="testdb" /> <!-- 节点主机配置 --> <dataHost name="host1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <!-- heartbeat标签内指明用于和后端数据库进行心跳检查的语句 --> <!--heartbeat:心跳，mycat一直检测mysql的user表（心跳），当接收不到心跳时，就会认为这台mysql宕机了，就不会在对该节点做增删改查等操作--> <heartbeat>select user()</heartbeat> <!-- 可以拥有多个写主机，url是写主机的IP地址和端口号，user和password是登录写主机的mysql的用户名和密码 --> <writeHost host="hostM1" url="192.168.147.128:3306" user="root" password="Mysql.123456"> <!-- 可以拥有多个读主机，url是读主机的IP地址和端口号，user和password是登录读主机的mysql的用户名和密码 --> <readHost host="hostS1" url="192.168.147.129:3306" user="root" password="Mysql.123456" /> </writeHost> </dataHost> </mycat:schema>在运行原理上，MyCat并不直接指向实际的物理库，而是有一个逻辑库的概念。 我们通过<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>定义了逻辑库的数据节点， 但是逻辑库dataNode="dn1"最终其实还是要指向实际的物理库， 所以也要通过<dataNode name="dn1" dataHost="host1" database="testdb" />来指向实际的物理库。 需要建库建表2.2.3.放行3306端口systemctl status firewalld # --add-port=3306/tcp，表示添加指定3306端口，格式为：端口/通讯协议 # --permanent表示设置为持久 # --zone 表示作用域 firewall-cmd --zone=public --add-port=3306/tcp --permanent # --remove-port表示移除指定端口 firewall-cmd --zone=public --remove-port=3306/tcp --permanent # 重新加载防火墙 firewall-cmd --reload # 再次查询开放端口 firewall-cmd --list-ports # 查看已配置规则 firewall-cmd --list-all2.2.4.验证Mysql数据库的访问情况Mycat 作为数据库中间件要和数据库部署在不同机器上，所以要验证远程访问情况。mysql -uroot -pMysql.123456 -h 192.168.147.128 -P 3306 mysql -uroot -pMysql.123456 -h 192.168.147.129 -P 3306 #如远程访问报错，请建对应用户 grant all privileges on *.* to root@'缺少的host' identified by 'Mysql.123456';2.2.5.分别在读写主机上建库建表mysql -uroot -pMysql.123456 -h 192.168.147.128 -P 3306 mysql -uroot -pMysql.123456 -h 192.168.147.129 -P 3306 show databases; CREATE DATABASE IF NOT EXISTS testdb CHARACTER SET utf8mb4; use testdb; DROP TABLE IF EXISTS user; CREATE TABLE `user` ( `id` int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '主键', `name` varchar(50) NOT NULL COMMENT '姓名' ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户表'; # ALTER TABLE user AUTO_INCREMENT = 1; INSERT INTO user (name) VALUES('zhangsan'),('lisi'),('lily'); select * from user;2.2.6.启动程序cd /usr/local/mycat/bin ./mycat console①控制台启动 ：去mycat/bin 目录下执行 ./mycat console②后台启动 ：去mycat/bin 目录下 ./mycat start2.2.7. 将MyCat配置到环境变量中vim /etc/profile# MyCat环境变量配置 export MYCAT_HOME=/usr/local/mycat export PATH=$PATH:$MYCAT_HOME/bin# 输入下面命令让设置的环境变量生效 source /etc/profile2.2.8.MyCat常用操作命令mycat start mycat stop #前台运行MyCat带控制台输出 mycat console mycat restart #暂停MyCat mycat pause #查看启动状态 mycat status2.3.登录2.3.1.登录后台管理窗口【9066】-- 用于管理维护Mycat# -u Mycat server.xml中配置逻辑库的用户mycat # -p Mycat server.xml中配置逻辑库的密码123456 # -P 后面是管理端口号。注意P是大写 # 9066用于查看MyCat的运行状态 # -h 后面是主机。 即当前Mycat安装的主机IP地址 # -D Mycat server.xml中配置逻辑库 # mysql -umycat -p -P 9066 -h 192.168.147.128 -DTESTDB mysql -umycat -p -P 9066 -h 192.168.147.128 123456 #常用命令如下： show database; show @@help;2.3.2.登录数据窗口【8066用于进行数据的CRUD操作】此登录方式用于通过 Mycat 查询数据，我们选择这种方式访问 Mycat# mysql -umycat -p -P 8066 -h 192.168.147.128 -DTESTDB mysql -umycat -p -P 8066 -h 192.168.147.128 1234562.3.3.报错处理：使用show tables找不到表mysql> show databases; +----------+ | DATABASE | +----------+ | TESTDB | +----------+ 1 row in set (0.00 sec) mysql> use TESTDB; Database changed mysql> show tables; ERROR 1184 (HY000): Invalid DataSource:0三、搭建读写分离通过 Mycat 和 MySQL 的主从复制配合搭建数据库的读写分离，实现 MySQL 的高可用性。3.1.搭建一主一从一个主机用于处理所有写请求，一台从机负责所有读请求，架构图如下3.1.1.搭建MySQL 数据库主从复制3.1.1.1.MySQL 主从复制原理--I/O具有延时性master主节点的bin log传到slave从节点后，被写道relay-log里，从节点的slave sql线程从relay-log里读取日志然后应用到slave从节点本地。从服务器I/O线程将主服务器的二进制日志读取过来记录到从服务器本地文件，然后SQL线程会读取relay-log日志的内容并应用到从服务器，从而使从服务器和主服务器的数据保持一致。中继日志是连接mastert(主服务器)和slave(从服务器)的信息，它是复制的核心，I/O线程将来自master的bin-log存储到中继日志中，中继日志充当缓冲，这样master不必等待slave执行完成就可以发送下一个bin-log。Master收到客户端请求语句，在语句结束之前向二进制日志写入一条记录，可能包含多个事件一个Slave连接到Master，Master的dump线程从bin-log读取日志并发送到Slave的IO线程。IO线程从master.info读取到上一次写入的最后的位置。IO线程写入日志到relay-log中继日志，如果超过指定的relay-log大小，写入轮换事件，创建一个新的relay-log。更新master.info的最后位置SQL线程从relay-log.info读取进上一次读取的位置SQL线程读取日志事件在数据库中执行sql更新relay-log.info的最后位置Slave记录自己的bin-log日志3.1.1.2.主机配置【192.168.147.128】修改配置文件 vim /etc/my.cnf # 主服务器唯一ID server-id=1 # 启用二进制日志 log-bin=mysql-bin # 设置不要复制的数据库(可设置多个) binlog-ignore-db=mysql binlog-ignore-db=information_schema #设置需要复制的数据库 binlog-do-db=需要复制的主数据库名字 #设置logbin格式 binlog_format=STATEMENT 3.1.1.3.从机配置【192.168.147.129】修改配置文件 vim /etc/my.cnf # 从服务器唯一ID server-id=2 # 启用中继日志 relay-log=mysql-relay 3.1.1.4.主机、从机重启MySQL 服务systemctl restart mysqld systemctl status mysqld3.1.1.5.主机从机都放行3306端口systemctl status firewalld # --add-port=3306/tcp，表示添加指定3306端口，格式为：端口/通讯协议 # --permanent表示设置为持久 # --zone 表示作用域 firewall-cmd --zone=public --add-port=3306/tcp --permanent # --remove-port表示移除指定端口 firewall-cmd --zone=public --remove-port=3306/tcp --permanent # 重新加载防火墙 firewall-cmd --reload # 再次查询开放端口 firewall-cmd --list-ports # 查看已配置规则 firewall-cmd --list-all3.1.1.6.在主机上建立帐户并授权 slavemysql -uroot -p # 在主机MySQL里执行授权命令 GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'Mysql.123123'; #查询master的状态 show master status; #记录下File和Position的值 #执行完此步骤后不要再操作主服务器MySQL，防止主服务器状态值变化3.1.1.7.在从机上配置需要复制的主机mysql -uroot -p #复制主机的命令 CHANGE MASTER TO MASTER_HOST='主机的IP地址', MASTER_USER='slave', MASTER_PASSWORD='Mysql.123123', MASTER_LOG_FILE='mysql-bin.具体数字',MASTER_LOG_POS=具体值; #启动从服务器复制功能 start slave; #查看从服务器状态 show slave status\G; #下面两个参数都是Yes，则说明主从配置成功！ # Slave_IO_Running: Yes # Slave_SQL_Running: Yes 3.1.1.8.解决问题：Slave_IO_Running: NoLast_IO_Error: 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.# 报错信息发现是mysql的server_uuid的原因 find / -name auto.cnf cat /var/lib/mysql/auto.cnf # 检查两个mysql实例的uuid发现主从的uuid一模一样 # 删除从库的auto.cnf文件并且重启从库实例问题解决 rm -rf /var/lib/mysql/auto.cnf systemctl restart mysqld # 检查从库复制状态 show slave status\G; 主从正常3.1.1.9.主机新建库、新建表、insert 记录，从机复制CREATE DATABASE IF NOT EXISTS testdb CHARACTER SET utf8mb4; use testdb; DROP TABLE IF EXISTS user; CREATE TABLE `user` ( `id` int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '主键', `name` varchar(50) NOT NULL COMMENT '姓名' ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户表'; INSERT INTO user (name) VALUES('zhangsan'),('lisi'),('lily'); select * from user;3.1.1.10.如何停止从服务复制功能--stop slavestop slave;3.1.1.11.之前搭建过主从复制，如何重新配置主从stop slave; reset master;3.1.1.2.修改Mycat 的配置文件schema.xml之前的配置已分配了读写主机，是否已实现读写分离？3.1.1.2.1.验证读写分离-- 在写主机插入 insert into user(name) values (@@hostname); -- 在写主机数据库表user中插入带系统变量数据，造成主从数据不一致-- 在Mycat里查询 select * from user;3.1.1.2.2.修改的balance属性，通过此属性配置读写分离的类型负载均衡类型，目前的取值有4 种：（1）balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的 writeHost 上。（2）balance="1"，全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，正常情况下，M2,S1,S2 都参与 select 语句的负载均衡。 【双主双从】（3）balance="2"，所有读操作都随机的在 writeHost、readhost 上分发。（4）balance="3"，所有读请求随机的分发到 readhost 执行，writerHost 不负担读压力。【单主单从】3.1.1.2.3.为了能看到读写分离的效果，把balance设置成2，会在两个主机间随机切换查询3.1.1.3.重新启动Mycatmycat consolemysql -umycat -p -P 8066 -h 192.168.147.128 1234563.1.1.4.验证读写分离：在Mycat里查询user表,可以看到查询语句在主从两个主机间切换3.2.搭建双主双从一个主机m1 用于处理所有写请求，它的从机s1 和另一台主机m2 还有它的从机 s2 负责所有读请求。当m1 主机宕机后，m2 主机负责写请求，m1、m2 互为备机。架构图如下编号角色IP地址机器名1Master1192.168.147.128host1282Slave1192.168.147.129host1293Master2192.168.147.130host1304Slave2192.168.147.131host1313.2.1.清除原来配置删除数据库testdb重新配置主从drop database testdb; stop slave; reset master;3.2.2.搭建MySQL 数据库主从复制【双主双从】3.2.2.1.双主机配置--Master1配置【host128】# 修改配置文件 vim /etc/my.cnf# 主服务器唯一ID server-id=1 # 启用二进制日志 log-bin=mysql-bin # 设置不要复制的数据库(可设置多个) binlog-ignore-db=mysql #binlog-ignore-db=information_schema # 设置需要复制的数据库 binlog-do-db=需要复制的主数据库名字 # 设置logbin格式 binlog_format=STATEMENT # 在作为从数据库的时候，有写入操作也要更新二进制日志文件 log-slave-updates # 表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1，取值范围是1 .. 65535 auto-increment-increment=2 # 表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1 .. 65535 auto-increment-offset=13.2.2.2.双主机配置--Master2配置【host130】# 修改配置文件 vim /etc/my.cnf# 主服务器唯一ID server-id=3 # 启用二进制日志 log-bin=mysql-bin # 设置不要复制的数据库(可设置多个) binlog-ignore-db=mysql #binlog-ignore-db=information_schema # 设置需要复制的数据库 binlog-do-db=需要复制的主数据库名字 # 设置logbin格式 binlog_format=STATEMENT # 在作为从数据库的时候，有写入操作也要更新二进制日志文件 log-slave-updates # 表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1，取值范围是1 .. 65535 auto-increment-increment=2 # 表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1 .. 65535 auto-increment-offset=23.2.2.3.双从机配置--Slave1配置【host129】# 修改配置文件 vim /etc/my.cnf# 从服务器唯一ID server-id=2 # 启用中继日志 relay-log=mysql-relay3.2.2.4.双从机配置--Slave2配置【host130】# 修改配置文件 vim /etc/my.cnf# 从服务器唯一ID server-id=4 # 启用中继日志 relay-log=mysql-relay3.2.2.5.双主机、双从机重启 mysql 服务systemctl restart mysqld systemctl status mysqld3.2.2.6.主机从机都放行3306端口systemctl status firewalld # --add-port=3306/tcp，表示添加指定3306端口，格式为：端口/通讯协议 # --permanent表示设置为持久 # --zone 表示作用域 firewall-cmd --zone=public --add-port=3306/tcp --permanent # --remove-port表示移除指定端口 firewall-cmd --zone=public --remove-port=3306/tcp --permanent # 重新加载防火墙 firewall-cmd --reload # 再次查询开放端口 firewall-cmd --list-ports # 查看已配置规则 firewall-cmd --list-all3.2.2.7.在两台主机上建立帐户并授权 slavemysql -uroot -p # 在主机MySQL里执行授权命令 GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'Mysql.123123'; use mysql; select host,user from user;3.2.2.8.查询master1的状态show master status; #记录下File和Position的值 #执行完此步骤后不要再操作主服务器MySQL，防止主服务器状态值变化3.2.2.9.查询master2的状态show master status; #记录下File和Position的值 #执行完此步骤后不要再操作主服务器MySQL，防止主服务器状态值变化3.2.2.10.在从机上配置需要复制的主机mysql -uroot -p #复制主机的命令 CHANGE MASTER TO MASTER_HOST='主机的IP地址', MASTER_USER='slave', MASTER_PASSWORD='Mysql.123123', MASTER_LOG_FILE='mysql-bin.具体数字',MASTER_LOG_POS=具体值; #启动两台从服务器复制功能 start slave; #查看从服务器状态 show slave status\G; #下面两个参数都是Yes，则说明主从配置成功！ # Slave_IO_Running: Yes # Slave_SQL_Running: Yes 3.2.2.11.slave1【host129】#slave1复制主机的命令 stop slave; reset master; CHANGE MASTER TO MASTER_HOST='192.168.147.128', MASTER_USER='slave', MASTER_PASSWORD='Mysql.123123', MASTER_LOG_FILE='mysql-bin.000005',MASTER_LOG_POS=154; #启动两台从服务器复制功能 start slave; #查看从服务器状态 show slave status\G; #下面两个参数都是Yes，则说明主从配置成功！ # Slave_IO_Running: Yes # Slave_SQL_Running: Yes ERROR 3021 (HY000): This operation cannot be performed with a running slave io thread; run STOP SLAVE IO_THREAD FOR CHANNEL '' first.3.2.2.12.slave2【host131】#slave2复制主机的命令 stop slave; CHANGE MASTER TO MASTER_HOST='192.168.147.130', MASTER_USER='slave', MASTER_PASSWORD='Mysql.123123', MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=435; #启动两台从服务器复制功能 start slave; #查看从服务器状态 show slave status\G; #下面两个参数都是Yes，则说明主从配置成功！ # Slave_IO_Running: Yes # Slave_SQL_Running: Yes 3.2.2.13.两个主机互相复制：Master2 复制Master1#Master2复制主机的命令 CHANGE MASTER TO MASTER_HOST='192.168.147.128', MASTER_USER='slave', MASTER_PASSWORD='Mysql.123123', MASTER_LOG_FILE='mysql-bin.000005',MASTER_LOG_POS=154; #启动两台主服务器复制功能 start slave; #查看从服务器状态 show slave status\G;3.2.2.14.两个主机互相复制：Master1 复制Master2#Master2复制主机的命令 CHANGE MASTER TO MASTER_HOST='192.168.147.130', MASTER_USER='slave', MASTER_PASSWORD='Mysql.123123', MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=435; #启动两台主服务器复制功能 start slave; #查看从服务器状态 show slave status\G;3.2.2.15.Master1 主机新建库、新建表、insert 记录，Master2 和从机复制CREATE DATABASE IF NOT EXISTS testdb CHARACTER SET utf8mb4; use testdb; DROP TABLE IF EXISTS user; CREATE TABLE `user` ( `id` int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '主键', `name` varchar(50) NOT NULL COMMENT '姓名' ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户表'; INSERT INTO user (name) VALUES('zhangsan'),('lisi'),('lily'); select * from user; select @@hostname;use testdb; select * from user; select @@hostname;3.2.2.16.如何停止从服务复制功能--stop slavestop slave;3.2.2.17.之前搭建过主从复制，如何重新配置主从stop slave; reset master;3.2.3.修改Mycat 的配置文件schema.xml修改的balance属性，通过此属性配置读写分离的类型负载均衡类型，目前的取值有4 种：（1）balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的 writeHost 上。（2）balance="1"，全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，正常情况下，M2,S1,S2 都参与 select 语句的负载均衡。 【双主双从】（3）balance="2"，所有读操作都随机的在 writeHost、readhost 上分发。（4）balance="3"，所有读请求随机的分发到 readhost 执行，writerHost 不负担读压力。【单主单从】3.2.3.1.双主双从读写分离balance设置为1<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <!-- 虚拟逻辑库配置 与server.xml 中的数据库对应 ,TESTDB是逻辑数据库，指向映射的实际数据库--> <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"> <!-- 逻辑表配置 --> </schema> <!-- 数据节点配置 --> <!-- <dataNode>标签的database属性值必须是配置的真实数据库testdb，这个数据库可以是读主机中所有已有的数据库 --> <dataNode name="dn1" dataHost="host1" database="testdb" /> <!-- 节点主机配置 --> <dataHost name="host1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <!-- heartbeat标签内指明用于和后端数据库进行心跳检查的语句 --> <heartbeat>select user()</heartbeat> <!-- 可以拥有多个写主机，url是写主机的IP地址和端口号，user和password是登录写主机的mysql的用户名和密码 --> <writeHost host="hostM1" url="192.168.147.128:3306" user="root" password="Mysql.123456"> <!-- 可以拥有多个读主机，url是读主机的IP地址和端口号，user和password是登录读主机的mysql的用户名和密码 --> <readHost host="hostS1" url="192.168.147.129:3306" user="root" password="Mysql.123456" /> </writeHost> <!-- 可以拥有多个写主机，url是写主机的IP地址和端口号，user和password是登录写主机的mysql的用户名和密码 --> <writeHost host="hostM2" url="192.168.147.130:3306" user="root" password="Mysql.123456"> <!-- 可以拥有多个读主机，url是读主机的IP地址和端口号，user和password是登录读主机的mysql的用户名和密码 --> <readHost host="hostS2" url="192.168.147.131:3306" user="root" password="Mysql.123456" /> </writeHost> </dataHost> </mycat:schema> # balance="1": 全部的readHost与stand by writeHost参与select语句的负载均衡。 # writeType="0": 所有写操作发送到配置的第一个writeHost，第一个挂了切到还生存的第二个 。 # writeType="1": 所有写操作都随机的发送到配置的 writeHost，1.5 以后废弃不推荐 。 # writeHost，重新启动后以切换后的为准，切换记录在配置文件中:dnindex.properties。 # switchType="1": 1 默认值，自动切换。 # -1 表示不自动切换 。 # 2 基于 MySQL 主从同步的状态决定是否切换。 3.2.3.2.验证读写分离-- 在写主机Master1插入 insert into user(name) values (@@hostname); -- 在写主机Master1数据库表user中插入带系统变量数据，造成主从数据不一致select @@hostname; select * from user;3.2.4.重新启动Mycatmycat console mysql -umycat -p -P 8066 -h 192.168.147.128 1234563.2.5.验证读写分离：在Mycat里查询user表-- 在Mycat里查询 select @@hostname; use TESTDB; select * from user;在Mycat里查询user表,可以看到查询语句在Slava1（host129）、Master2（host130）、Slava2（host131）主从三个主机间切换3.2.6.抗风险能力--停止数据库Master1【host128】select @@hostname; systemctl stop mysqld systemctl status mysqld cat /etc/hostname3.2.7.在Mycat里插入数据依然成功，Master2【host130】自动切换为写主机-- 在Mycat里插入数据 insert into user(name) values (@@hostname); select @@hostname; select * from user;3.2.8.启动数据库Master1【host128】systemctl start mysqld systemctl status mysqld3.2.9.在Mycat里查询user表看到查询语句在Master1（host128）、Slava1（host129）、Slava2（host131）主从三个主机间切换select @@hostname; select * from user;3.2.10.Master1、Master2 互做备机，负责写的主机宕机，备机切换负责写操作，保证数据库读写分离高可用性四、垂直拆分--分库一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，分布到不同的数据库上面，这样也就将数据或者说压力分担到不同的库上面，如下图：4.1.划分表注意：在两台主机上的两个数据库中的表，不可以关联查询。分库的原则：有紧密关联关系的表应该在一个库里，相互没有关联关系的表可以分到不同的库里。#客户表 rows:20万条 CREATE TABLE customer( id INT AUTO_INCREMENT, NAME VARCHAR(200), PRIMARY KEY(id) ) ENGINE=INNODB DEFAULT CHARSET=utf8; #订单表 rows:600万条 CREATE TABLE orders( id INT AUTO_INCREMENT, order_type INT, customer_id INT, amount DECIMAL(10,2), PRIMARY KEY(id) ) ENGINE=INNODB DEFAULT CHARSET=utf8; #订单详细表 rows:600万条 CREATE TABLE orders_detail( id INT AUTO_INCREMENT, detail VARCHAR(2000), order_id INT, PRIMARY KEY(id) ) ENGINE=INNODB DEFAULT CHARSET=utf8; #订单状态字典表 rows:20条 CREATE TABLE dict_order_type( id INT AUTO_INCREMENT, order_type VARCHAR(200), PRIMARY KEY(id) ) ENGINE=INNODB DEFAULT CHARSET=utf8;客户表分在一个数据库，另外三张都需要关联查询，分在另外一个数据库。垂直分库，不能在老的数据库上进行，需要在新的空数据库上进行。先在【host128，host129】两台机器上创建好2个数据库，然后通过Mycat创建好需要的空表（分布在两台机器上的数据库中）。最后将老的数据库上的数据插入到新的2个数据库上就可以了。4.2.实现分库4.2.1.修改schema配置文件<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <!-- 虚拟逻辑库配置 与server.xml 中的数据库对应 ,TESTDB是逻辑数据库，指向映射的实际数据库--> <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"> <!-- 逻辑表配置 指定当前表所在的指定的节点位置（即所在的数据库）--> <table name="customer" dataNode="dn2"></table> </schema> <!-- 数据节点配置 --> <!-- <dataNode>标签的database属性值必须是配置的真实数据库testdb，这个数据库可以是读主机中所有已有的数据库 --> <dataNode name="dn1" dataHost="host1" database="orders" /> <dataNode name="dn2" dataHost="host2" database="orders" /> <!-- 节点主机配置 --> <dataHost name="host1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <!-- heartbeat标签内指明用于和后端数据库进行心跳检查的语句 --> <heartbeat>select user()</heartbeat> <!-- 可以拥有多个写主机，url是写主机的IP地址和端口号，user和password是登录写主机的mysql的用户名和密码 --> <writeHost host="hostM1" url="192.168.147.128:3306" user="root" password="Mysql.123456"> </writeHost> </dataHost> <!-- 节点主机配置 --> <dataHost name="host2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <!-- heartbeat标签内指明用于和后端数据库进行心跳检查的语句 --> <heartbeat>select user()</heartbeat> <!-- 可以拥有多个写主机，url是写主机的IP地址和端口号，user和password是登录写主机的mysql的用户名和密码 --> <writeHost host="hostM2" url="192.168.147.129:3306" user="root" password="Mysql.123456"> </writeHost> </dataHost> </mycat:schema>4.2.2.新增两个数据库【orders】分库操作不是在原来的老数据库上进行操作，需要准备两台机器分别安装新的数据库#在数据节点 dn1【host128】、dn2【host129】 上分别创建数据库 orders mysql -uroot -p Mysql.123456 select @@hostname; CREATE DATABASE IF NOT EXISTS orders CHARACTER SET utf8mb4; use orders;4.2.3.重新启动Mycatmycat console4.2.4.访问Mycat 进行分库mysql -umycat -p -P 8066 -h 192.168.147.128 123456 #切换到TESTDB use TESTDB; #创建4 张表 #查看表信息，可以看到成功分库 use orders; show tables; select @@hostname;五、水平拆分--分表相对于垂直拆分，水平拆分不是将表做分类，而是按照某个字段的某种规则来分散到多个库之中，每个表中 包含一部分数据。简单来说，我们可以将数据的水平切分理解为是按照数据行的切分，就是将表中的某些行切分到一个数据库，而另外的某些行又切分到其他的数据库中，如图：5.1.实现分表5.1.1.选择要拆分的表MySQL 单表存储数据条数是有瓶颈的，单表达到 1000 万条数据就达到了瓶颈，会影响查询效率，需要进行水平拆分（分表）进行优化。例如：例子中的orders、orders_detail 都已经达到 600 万行数据，需要进行分表优化。5.1.2.分表字段以orders 表为例，可以根据不同自字段进行分表编号分表字段效果1id（主键、或创建时间）查询订单注重时效，历史订单被查询的次数少，如此分片会造成一个节点访问多，一个访问少，不平均。2customer_id（客户id）根据客户 id 去分，两个节点访问平均，一个客户的所有订单都在同一个节点5.1.3.修改配置文件schema.xmlvi /usr/local/mycat/conf/schema.xml #为orders 表设置数据节点为 dn1、dn2，并指定分片规则为 mod_rule（自定义的名字） <table name="orders" dataNode="dn1,dn2" rule="mod_rule" ></table> 5.1.4.修改配置文件rule.xmlvi /usr/local/mycat/conf/rule.xml #在rule 配置文件里新增分片规则mod_rule，并指定规则适用字段为 customer_id， #还有选择分片算法mod-long（对字段求模运算），customer_id 对两个节点求模，根据结果分片 #配置算法mod-long 参数 count 为 2，两个节点 <tableRule name="mod_rule"> <rule> <columns>customer_id</columns> <algorithm>mod-long</algorithm> </rule> </tableRule> <function name="mod-long" class="io.mycat.route.function.PartitionByMod"> <!-- how many data nodes --> <property name="count">2</property> </function>5.1.5.在数据节点dn2 【host129】上建orders 表【订单表】#订单表 rows:600万条 CREATE TABLE orders( id INT AUTO_INCREMENT, order_type INT, customer_id INT, amount DECIMAL(10,2), PRIMARY KEY(id) ) ENGINE=INNODB DEFAULT CHARSET=utf8;5.1.6.重启Mycat，让配置生效mycat console mysql -umycat -p -P 8066 -h 192.168.147.128 123456 #切换到TESTDB use TESTDB; show tables;5.1.7.访问Mycat 实现分片INSERT INTO orders VALUES(1,101,100,100100); #在mycat 里向orders 表插入数据，INSERT 字段不能省略 INSERT INTO orders(id,order_type,customer_id,amount) VALUES (1,101,100,100100),(2,101,100,100300),(3,101,101,120000), (4,101,101,103000),(5,102,101,100400),(6,102,100,100020); #在mycat、dn1【host128】、dn2【host129】中查看orders表数据，分表成功 5.2.Mycat 的分片 “join”Orders 订单表已经进行分表操作了，和它关联的 orders_detail 订单详情表如何进行 join 查询。 我们要对orders_detail 也要进行分片操作。Join 的原理如下图： 5.2.1.水平分表--ER表Mycat 借鉴了 NewSQL 领域的新秀 Foundation DB 的设计思路，Foundation DB 创新性的提出了 Table Group 的概念，其将子表的存储位置依赖于主表，并且物理上紧邻存放，因此彻底解决了 JION 的效率和性能问题，根据这一思路，提出了基于 E-R 关系的数据分片策略，子表的记录与所关联的父表记录存放在同一个数据分片上。5.2.1.1.修改schema.xml 配置文件 <!-- 虚拟逻辑库配置 与server.xml 中的数据库对应 ,TESTDB是逻辑数据库，指向映射的实际数据库--> <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"> <!-- 逻辑表配置 --> <table name="customer" dataNode="dn2"></table> <!-- 为orders 表设置数据节点为 dn1、dn2，并指定分片规则为 mod_rule（自定义的名字） --> <table name="orders" dataNode="dn1,dn2" rule="mod_rule" > <childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" /> </table> </schema> 5.2.1.2.在dn2【host129】创建orders_detail 表【订单详细表】#订单详细表 rows:600万条 CREATE TABLE orders_detail( id INT AUTO_INCREMENT, detail VARCHAR(2000), order_id INT, PRIMARY KEY(id) ) ENGINE=INNODB DEFAULT CHARSET=utf8;5.2.1.3.重启Mycat，让配置生效mycat console5.2.1.4.访问Mycat 向orders_detail 表插入数据mysql -umycat -p -P 8066 -h 192.168.147.128 123456 #切换到TESTDB use TESTDB;# mycat中不支持一次对子表插入多条记录 # 拆成单条SQL语句插入即可 INSERT INTO orders_detail(id,detail,order_id) values(1,'detail1',1); INSERT INTO orders_detail(id,detail,order_id) VALUES(2,'detail1',2); INSERT INTO orders_detail(id,detail,order_id) VALUES(3,'detail1',3); INSERT INTO orders_detail(id,detail,order_id) VALUES(4,'detail1',4); INSERT INTO orders_detail(id,detail,order_id) VALUES(5,'detail1',5); INSERT INTO orders_detail(id,detail,order_id) VALUES(6,'detail1',6); select * from orders_detail;mysql> INSERT INTO orders_detail(id,detail,order_id) values -> (1,'detail1',1),(2,'detail1',2),(3,'detail1',3), -> (4,'detail1',4),(5,'detail1',5),(6,'detail1',6); ERROR 1064 (HY000): ChildTable multi insert not provided mysql> 5.2.1.5.在mycat、dn1、dn2中运行两个表join语句Select o.* ,od.detail from orders o inner join orders_detail od on o.id=od.order_id;5.2.2.水平分表--全局表在分片的情况下，当业务表因为规模而进行分片以后，业务表与这些附属的字典表之间的关联，就成了比较棘手的问题，考虑到字典表具有以下几个特性：变动不频繁数据量总体变化不大数据规模不大，很少有超过数十万条记录鉴于此，Mycat 定义了一种特殊的表，称之为“全局表”，全局表具有以下特性：全局表的插入、更新操作会实时在所有节点上执行，保持各个分片的数据一致性全局表的查询操作，只从一个节点获取全局表可以跟任何一个表进行 JOIN 操作将字典表或者符合字典表特性的一些表定义为全局表，则从另外一个方面，很好的解决了数据JOIN 的难题。通过全局表+基于 E-R 关系的分片策略，Mycat 可以满足 80%以上的企业应用开发5.2.2.1.修改schema.xml 配置文件通过数据冗余实现拆分效果 <!-- 虚拟逻辑库配置 与server.xml 中的数据库对应 ,TESTDB是逻辑数据库，指向映射的实际数据库--> <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"> <!-- 逻辑表配置 --> <table name="customer" dataNode="dn2"></table> <!-- 为orders 表设置数据节点为 dn1、dn2，并指定分片规则为 mod_rule（自定义的名字） --> <table name="orders" dataNode="dn1,dn2" rule="mod_rule" > <childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" /> </table> <!-- 全局表 --> <table name="dict_order_type" dataNode="dn1,dn2" type="global" ></table> </schema>5.2.2.2.在dn2【host129】创建dict_order_type 表【订单状态字典表】#订单状态字典表 rows:20条 CREATE TABLE dict_order_type( id INT AUTO_INCREMENT, order_type VARCHAR(200), PRIMARY KEY(id) ) ENGINE=INNODB DEFAULT CHARSET=utf8;5.2.2.3.重启Mycat，让配置生效mycat console5.2.2.4.访问Mycat 向dict_order_type 表插入数据mysql -umycat -p -P 8066 -h 192.168.147.128 123456 #切换到TESTDB use TESTDB;# mycat中不支持一次对子表插入多条记录 # 拆成单条SQL语句插入即可 INSERT INTO dict_order_type(id,order_type) VALUES(101,'type1'); INSERT INTO dict_order_type(id,order_type) VALUES(102,'type2');5.2.2.5.在Mycat、dn1、dn2中查询表数据select * from dict_order_type;5.3.常用分片规则5.3.1.取模此规则为对分片字段求模运算。也是水平分表最常用规则。5.1.4.配置分表中，orders 表采用了此规则。5.3.2.分片枚举通过在配置文件中配置可能的枚举 id，自己配置分片，本规则适用于特定的场景，比如有些业务需要按照省份或区县来做保存，而全国省份区县固定的，这类业务使用本条规则。5.3.2.1.修改schema.xml配置文件<table name="orders_ware_info" dataNode="dn1,dn2" rule="sharding_by_intfile" ></table>5.3.2.2.修改rule.xml配置文件 <tableRule name="sharding_by_intfile"> <rule> <columns>areacode</columns> <algorithm>hash-int</algorithm> </rule> </tableRule> # columns：分片字段，algorithm：分片函数 <function name="hash-int" class="io.mycat.route.function.PartitionByFileMap"> <property name="mapFile">partition-hash-int.txt</property> <property name="type">1</property> <property name="defaultNode">0</property> </function> # mapFile：标识配置文件名称 # type：0为int型、非0为String # `areacode` VARCHAR(20) comment '区域编号' type为1 # defaultNode：默认节点:小于 0 表示不设置默认节点，大于等于 0 表示设置默认节点 # 设置默认节点如果碰到不识别的枚举值，就让它路由到默认节点，如不设置默认节点遇到不识别就报错 5.3.2.3.修改partition-hash-int.txt配置文件vi /usr/local/mycat/conf/partition-hash-int.txt110=0 120=1 # 不能有空格，0代表dn1,1代表dn25.3.2.4.重启Mycat，让配置生效mycat console5.3.2.5.访问Mycat创建表【订单归属区域信息表】mysql -umycat -p -P 8066 -h 192.168.147.128 123456 #切换到TESTDB use TESTDB;-- DROP TABLE IF EXISTS orders_ware_info; CREATE TABLE orders_ware_info( `id` INT AUTO_INCREMENT comment '编号', `order_id` INT comment '订单编号', `address` VARCHAR(200) comment '地址', `areacode` VARCHAR(20) comment '区域编号', PRIMARY KEY(id) ) ENGINE=INNODB DEFAULT CHARSET=utf8; select * from orders_ware_info;5.3.2.6.访问Mycat插入数据# mycat中不支持一次对子表插入多条记录 # 拆成单条SQL语句插入即可 INSERT INTO orders_ware_info(id,order_id,address,areacode) VALUES (1,1,'北京','110'); INSERT INTO orders_ware_info(id,order_id,address,areacode) VALUES (2,2,'上海','110'); INSERT INTO orders_ware_info(id,order_id,address,areacode) VALUES (3,3,'杭州','110'); INSERT INTO orders_ware_info(id,order_id,address,areacode) VALUES (4,4,'深圳','110'); INSERT INTO orders_ware_info(id,order_id,address,areacode) VALUES (5,5,'苏州','120'); INSERT INTO orders_ware_info(id,order_id,address,areacode) VALUES (6,6,'天津','120');5.3.2.7.查询Mycat、dn1、dn2可以看到数据分片效果select * from orders_ware_info; select @@hostname;5.3.3.范围约定此分片适用于，提前规划好分片字段某个范围属于哪个分片。5.3.3.1.修改schema.xml配置文件<!-- 范围约定算法 --> <table name="payment_info" dataNode="dn1,dn2" rule="auto_sharding_long" ></table>5.3.3.2.修改rule.xml配置文件 <tableRule name="auto_sharding_long"> <rule> <columns>order_id</columns> <algorithm>rang-long</algorithm> </rule> </tableRule> <function name="rang-long" class="io.mycat.route.function.AutoPartitionByLong"> <property name="mapFile">autopartition-long.txt</property> <property name="defaultNode">0</property> </function> # columns：分片字段，algorithm：分片函数 # mapFile：标识配置文件名称 # defaultNode：默认节点:小于 0 表示不设置默认节点，大于等于 0 表示设置默认节点， # 设置默认节点如果碰到不识别的枚举值，就让它路由到默认节点，如不设置不识别就报错5.3.3.3.修改autopartition-long.txt配置文件0-102=0 103-200=15.3.3.4.重启Mycat，让配置生效mycat console5.3.3.5.访问Mycat创建表【支付信息表】mysql -umycat -p -P 8066 -h 192.168.147.128 123456 #切换到TESTDB use TESTDB;#支付信息表 CREATE TABLE payment_info( `id` INT AUTO_INCREMENT comment '编号', `order_id` INT comment '订单编号', `payment_status` INT comment '支付状态', PRIMARY KEY(id) ) ENGINE=INNODB DEFAULT CHARSET=utf8;5.3.3.6.访问Mycat插入数据# mycat中不支持一次对子表插入多条记录 # 拆成单条SQL语句插入即可 INSERT INTO payment_info (id,order_id,payment_status) VALUES (1,101,0); INSERT INTO payment_info (id,order_id,payment_status) VALUES (2,102,1); INSERT INTO payment_info (id,order_id,payment_status) VALUES (3,103,0); INSERT INTO payment_info (id,order_id,payment_status) VALUES (4,104,1);5.3.3.7.查询Mycat、dn1、dn2可以看到数据分片效果select * from payment_info; select @@hostname;5.3.4.按日期（天）分片此规则为按天分片。设定时间格式、范围5.3.4.1.修改schema.xml配置文件<!-- 按日期（天）分片 --> <table name="login_info" dataNode="dn1,dn2" rule="sharding_by_date" ></table>5.3.4.2.修改rule.xml配置文件 <tableRule name="sharding_by_date"> <rule> <columns>login_date</columns> <algorithm>shardingByDate</algorithm> </rule> </tableRule> <function name="shardingByDate" class="io.mycat.route.function.PartitionByDate"> <property name="dateFormat">yyyy-MM-dd</property> <property name="sBeginDate">2019-01-01</property> <property name="sEndDate">2019-01-04</property> <property name="sPartionDay">2</property> </function> # columns：分片字段，algorithm：分片函数 # dateFormat ：日期格式 # sBeginDate ：开始日期 # sEndDate：结束日期,则代表数据达到了这个日期的分片后循环从开始分片插入 # sPartionDay ：分区天数，即默认从开始日期算起，分隔 2 天一个分区5.3.4.3.重启Mycat，让配置生效mycat console5.3.4.4.访问Mycat创建表【用户信息表】mysql -umycat -p -P 8066 -h 192.168.147.128 123456 #切换到TESTDB use TESTDB;#用户信息表 CREATE TABLE login_info ( `id` INT AUTO_INCREMENT comment '编号', `user_id` INT comment '用户编号', `login_date` date comment '登录日期', PRIMARY KEY(id) ) ENGINE=INNODB DEFAULT CHARSET=utf8;5.3.4.5.访问Mycat插入数据# mycat中不支持一次对子表插入多条记录 # 拆成单条SQL语句插入即可 INSERT INTO login_info(id,user_id,login_date) VALUES (1,101,'2019-01-01'); INSERT INTO login_info(id,user_id,login_date) VALUES (2,102,'2019-01-02'); INSERT INTO login_info(id,user_id,login_date) VALUES (3,103,'2019-01-03'); INSERT INTO login_info(id,user_id,login_date) VALUES (4,104,'2019-01-04'); INSERT INTO login_info(id,user_id,login_date) VALUES (5,103,'2019-01-05'); INSERT INTO login_info(id,user_id,login_date) VALUES (6,104,'2019-01-06');5.3.4.6.查询Mycat、dn1、dn2可以看到数据分片效果select * from login_info; select @@hostname;5.4.全局序列在实现分库分表的情况下，数据库自增主键已无法保证自增主键的全局唯一。为此，Mycat 提供了全局 sequence，并且提供了包含本地配置和数据库配置等多种实现方式5.4.1.本地文件此方式 Mycat 将 sequence 配置到文件中，当使用到 sequence 中的配置后，Mycat 会更下 classpath 中的 sequence_conf.properties 文件中 sequence 当前的值。优点：本地加载，读取速度较快缺点：抗风险能力差，Mycat 所在主机宕机后，无法读取本地文件。5.4.2.数据库方式利用数据库一个表来进行计数累加。但是并不是每次生成序列都读写数据库，这样效率太低。Mycat 会预加载一部分号段到 Mycat 的内存中，这样大部分读写序列都是在内存中完成的。 如果内存中的号段用完了 Mycat 会再向数据库要一次。问题：那如果Mycat 崩溃了 ，那内存中的序列岂不是都没了？是的。如果是这样，那么Mycat 启动后会向数据库申请新的号段，原有号段会弃用。也就是说如果Mycat 重启，那么损失是当前的号段没用完的号码，但是不会因此出现主键重复5.4.2.1.创建全局序列表脚本【dn1】#在dn1 上创建全局序列表 CREATE TABLE MYCAT_SEQUENCE (NAME VARCHAR(50) NOT NULL,current_value INT NOT NULL,increment INT NOT NULL DEFAULT 100, PRIMARY KEY(NAME)) ENGINE=INNODB; #创建全局序列所需函数 DELIMITER $$ CREATE FUNCTION mycat_seq_currval(seq_name VARCHAR(50)) RETURNS VARCHAR(64) DETERMINISTIC BEGIN DECLARE retval VARCHAR(64); SET retval="-999999999,null"; SELECT CONCAT(CAST(current_value AS CHAR),",",CAST(increment AS CHAR)) INTO retval FROM MYCAT_SEQUENCE WHERE NAME = seq_name; RETURN retval; END $$ DELIMITER ; DELIMITER $$ CREATE FUNCTION mycat_seq_setval(seq_name VARCHAR(50),VALUE INTEGER) RETURNS VARCHAR(64) DETERMINISTIC BEGIN UPDATE MYCAT_SEQUENCE SET current_value = VALUE WHERE NAME = seq_name; RETURN mycat_seq_currval(seq_name); END $$ DELIMITER ; DELIMITER $$ CREATE FUNCTION mycat_seq_nextval(seq_name VARCHAR(50)) RETURNS VARCHAR(64) DETERMINISTIC BEGIN UPDATE MYCAT_SEQUENCE SET current_value = current_value + increment WHERE NAME = seq_name; RETURN mycat_seq_currval(seq_name); END $$ DELIMITER ; #初始化序列表记录 INSERT INTO MYCAT_SEQUENCE(NAME,current_value,increment) VALUES ('ORDERS', 400000, 100); select * from MYCAT_SEQUENCE;5.4.2.2.修改Mycat 配置5.4.2.2.1.修改sequence_db_conf.propertiesvi /usr/local/mycat/conf/sequence_db_conf.properties#sequence stored in datanode GLOBAL=dn1 COMPANY=dn1 CUSTOMER=dn1 ORDERS=dn1 # ORDERS这个序列在dn1这个节点上，具体dn1节点是哪台机子，请参考schema.xml5.4.2.2.2.修改server.xmlvi /usr/local/mycat/conf/server.xml # 全局序列类型：0-本地文件，1-数据库方式，2-时间戳方式。此处应该修改成1。5.4.2.2.3.重启Mycat，让配置生效mycat console5.4.2.3.验证全局序列5.4.2.3.1.登录Mycat，插入数据mysql -umycat -p -P 8066 -h 192.168.147.128 123456 #切换到TESTDB use TESTDB; insert into orders(id,amount,customer_id,order_type) values(next value for MYCATSEQ_ORDERS,1000,101,102); insert into orders(id,amount,customer_id,order_type) values(next value for MYCATSEQ_ORDERS,1001,101,102);5.4.2.3.2.查询数据select * from orders;5.4.2.3.3.重启Mycat后，再次插入数据，再查询use TESTDB; insert into orders(id,amount,customer_id,order_type) values(next value for MYCATSEQ_ORDERS,1003,103,103); insert into orders(id,amount,customer_id,order_type) values(next value for MYCATSEQ_ORDERS,1004,104,104); select * from orders;5.4.3.时间戳方式全局序列ID = 64 位二进制 (42(毫秒)+5(机器 ID)+5(业务编码)+12(重复累加) 换算成十进制为 18 位数的 long 类型，每毫秒可以并发 12 位二进制的累加。优点：配置简单缺点：18 位ID 过长5.4.4.自主生成全局序列可在java 项目里自己生成全局序列，如下：根据业务逻辑组合可以利用 redis 的单线程原子性 incr 来生成序列但，自主生成需要单独在工程中用 java 代码实现，还是推荐使用Mycat 自带全局序列。六、基于HA 机制的Mycat 高可用在实际项目中，Mycat 服务也需要考虑高可用性，如果 Mycat 所在服务器出现宕机，或 Mycat 服务故障，需要有备机提供服务，需要考虑Mycat 集群。6.1.高可用方案我们可以使用HAProxy + Keepalived 配合两台 Mycat 搭起 Mycat 集群，实现高可用性。HAProxy 实现了 MyCat 多节点的集群高可用和负载均衡， 而 HAProxy 自身的高可用则可以通过Keepalived 来实现。编号角色IP地址机器名1Mycat1192.168.147.128host1282Mycat2192.168.147.129host1293HAProxy(master)192.168.147.130host1304Keepalived(master)192.168.147.130host1305HAProxy(backup)192.168.147.131host1316Keepalived(backup)192.168.147.131host1316.2.安装配置HAProxy6.2.1.准备好HAProxy安装包，传到/opt目录下官网：https://www.haproxy.org/cd /opt/ wget https://www.haproxy.org/download/1.5/src/haproxy-1.5.19.tar.gz6.2.2.解压到/usr/local/srctar -xzvf haproxy-1.5.19.tar.gz -C /usr/local/src/6.2.3.进入解压后的目录，查看内核版本，进行编译cd /usr/local/src/haproxy-1.5.19 # 查看内核版本 uname -r # 3.10.0-1160.71.1.el7.x86_64 # 执行Linux的make命令进行编译 make TARGET=linux310 PREFIX=/usr/local/haproxy ARCH=x86_64 # ARGET=linux310，内核版本，使用uname -r查看内核，如：3.10.0-1160.71.1.el7，此时该参数就为linux310 # ARCH=x86_64，系统位数 # PREFIX=/usr/local/haprpxy # /usr/local/haprpxy，为haprpxy安装路径。6.2.4.编译完成后，进行安装make install PREFIX=/usr/local/haproxy6.2.5.安装完成后，创建目录、创建HAProxy配置文件# 将解压后目录下的配置模版文件拷贝到程序运行目录的配置文件目录下 cp /usr/local/src/haproxy-1.5.19/examples/haproxy.cfg /usr/local/haproxy/haproxy.cfg # 创建目录 mkdir -p /usr/data/haproxy/ vim /usr/local/haproxy/haproxy.conf6.2.6.向配置文件中插入以下配置信息,并保存# 全局配置，定义haproxy进程的工作特性和全局配置 global # 定义全局的 syslog 服务器，最多可以定义两个 log 127.0.0.1 local0 #log 127.0.0.1 local1 notice #log loghost local0 info maxconn 4096 # chroot运行的路径，，当前目录为指定目录，设置超级用户权限启动进程，提高安全性 chroot /usr/local/haproxy # haproxy pid的存放位置 pidfile /usr/data/haproxy/haproxy.pid uid 99 gid 99 # 守护进程的方式在后台工作 daemon #debug #quiet # 默认配置 defaults # 日志继承全局配置段的设置 log global mode tcp option abortonclose option redispatch # 重试次数为3次，失败3次以后则表示服务不可用 retries 3 maxconn 2000 # 连接超时时间，表示客户端请求转发至服务器所等待的时长 timeout connect 5000 # 客户端超时时间，表示客户端非活跃状态的时间 timeout client 50000 # 服务器超时时间，表示客户端与服务器建立连接后，等待服务器的超时时间 timeout server 50000 listen proxy_status # bind后面必须有空格然后加冒号，否则会报错 bind :48066 mode tcp balance roundrobin server mycat_1 192.168.147.128:8066 check inter 10s server mycat_2 192.168.147.129:8066 check inter 10s frontend admin_stats bind :7777 mode http stats enable option httplog maxconn 10 stats refresh 30s stats uri /admin stats auth admin:123123 stats hide-version stats admin if TRUE # 控制台的用户名admin 密码1231236.2.7.放行8066端口systemctl status firewalld # --add-port=8066/tcp，表示添加指定8066端口，格式为：端口/通讯协议 # --permanent表示设置为持久 # --zone 表示作用域 firewall-cmd --zone=public --add-port=8066/tcp --permanent # --remove-port表示移除指定端口 firewall-cmd --zone=public --remove-port=8066/tcp --permanent # 重新加载防火墙 firewall-cmd --reload # 再次查询开放端口 firewall-cmd --list-ports # 查看已配置规则 firewall-cmd --list-all6.2.8.启动验证--启动HAProxy/usr/local/haproxy/sbin/haproxy -f /usr/local/haproxy/haproxy.conf 6.2.9.查看HAProxy进程ps -ef | grep haproxy 6.2.10.haproxy安装成系统服务# 创建脚本 vi /usr/lib/systemd/system/haproxy.service # 重新加载系统服务 systemctl daemon-reload systemctl start haproxy systemctl stop haproxy systemctl status haproxy[Unit] Description=HAProxy Load Balancer Documentation=https://www.haproxy.org/ After=network.target [Service] Type=forking PIDFile=/usr/data/haproxy/haproxy.pid ExecStart=/usr/local/haproxy/sbin/haproxy -f /usr/local/haproxy/haproxy.conf ExecReload=/bin/kill -s HUP $MAINPID ExecStop=/bin/kill -s TERM $MAINPID PrivateTmp=true [Install] WantedBy=default.target6.2.11.Mycat主备机均启动mycat console6.2.12.打开浏览器访问http://192.168.147.130:7777/admin #在弹出框输入用户名：admin密码：123123 6.2.13.验证负载均衡，通过HAProxy访问Mycatmysql -umycat -p -P 48066 -h 192.168.147.130 1234566.3.配置Keepalivedkeepalived官网地址：https://keepalived.org/6.3.1.准备好Keepalived安装包，传到/opt目录下cd /opt/ wget https://keepalived.org/software/keepalived-2.2.8.tar.gz6.3.2.解压到/usr/local/srctar -zxvf keepalived-2.2.8.tar.gz -C /usr/local/src6.3.3.安装依赖插件yum install -y gcc openssl-devel popt-devel libnl libnl-devel6.3.4.进入解压后的目录，进行配置，进行编译cd /usr/local/src/keepalived-2.2.8 ./configure --prefix=/usr/local/keepalived6.3.5.进行编译，完成后进行安装make && make install6.3.6.运行前配置cp /usr/local/src/keepalived-2.2.8/keepalived/etc/init.d/keepalived /etc/init.d/ mkdir /etc/keepalived cp /usr/local/keepalived/etc/keepalived/keepalived.conf.sample /etc/keepalived/keepalived.conf cp /usr/local/src/keepalived-2.2.8/keepalived/etc/sysconfig/keepalived /etc/sysconfig/ cp /usr/local/keepalived/sbin/keepalived /usr/sbin/ 6.3.7.修改配置文件vim /etc/keepalived/keepalived.conf#修改内容如下 ! Configuration File for keepalived global_defs { notification_email { coon@foxmail.com } notification_email_from keepalived@joy.com smtp_server 127.0.0.1 smtp_connect_timeout 30 router_id LVS_DEVEL vrrp_skip_check_adv_addr vrrp_garp_interval 0 vrrp_gna_interval 0 } vrrp_instance VI_1 { #主机配MASTER，备机配BACKUP state MASTER #所在机器网卡 interface ens33 virtual_router_id 51 #数值越大优先级越高 priority 100 advert_int 1 authentication{ auth_type PASS auth_pass 1111 } virtual_ipaddress { #虚拟IP 192.168.147.200 } } virtual_server 192.168.147.200 48066 { delay_loop 6 lb_algo rr lb_kind NAT persistence_timeout 50 protocol TCP # HAProxy主机 real_server 192.168.147.130 48066 { weight 1 TCP_CHECK { connect_timeout 3 retry 3 delay_before_retry 3 } } # HAProxy从机 real_server 192.168.147.131 48600 { weight 1 TCP_CHECK { connect_timeout 3 retry 3 delay_before_retry 3 } } }6.3.8.启动验证--启动Keepalivedsystemctl start keepalived systemctl stop keepalived systemctl status keepalived6.3.9.登录验证mysql -umycat -p -P 48066 -h 192.168.147.200 1234566.4.测试高可用6.4.1.关闭mycat6.4.2.通过虚拟ip查询数据mysql -umycat -p123456 -h 192.168.147.200 -P 48066七、Mycat 安全设置7.1.权限配置7.1.1.user 标签权限控制目前Mycat 对于中间件的连接控制并没有做太复杂的控制，目前只做了中间件逻辑库级别的读写权限控制。是通过server.xml 的user 标签进行配置。7.1.1.1.修改配置文件server.xmlvi /usr/local/mycat/conf/server.xml#server.xml配置文件user部分 <user name="mycat"> <property name="password">123456</property> <property name="schemas">TESTDB</property> </user> <user name="user"> <property name="password">user</property> <property name="schemas">TESTDB</property> <property name="readOnly">true</property> </user>7.1.1.2.配置说明标签属性说明name应用连接中间件逻辑库的用户名password该用户对应的密码TESTDB应用当前连接的逻辑库中所对应的逻辑表。schemas 中可以配置一个或多个readOnly应用连接中间件逻辑库所具有的权限。true 为只读，false 为读写都有，默认为 false7.1.2.测试案例一使用user用户，权限为只读（readOnly：true）验证是否可以查询出数据，验证是否可以写入数据7.1.2.1.用user用户登录，运行命令如下mysql -uuser -puser -h 192.168.147.128 -P80667.1.2.2.切换到TESTDB数据库，查询orders表数据use TESTDB; select * from orders;7.1.2.3.查询到数据，如图7.1.2.4.执行插入数据sqlinsert into orders(id,order_type,customer_id,amount) values(7,101,101,10000);7.1.2.5.可看到运行结果，插入失败，只有只读权限7.1.3.测试案例二使用mycat用户，权限为可读写（readOnly：false）验证是否可以查询出数据，验证是否可以写入数据7.1.3.1.用mycat用户登录，运行命令如下mysql -umycat -p123456 -h 192.168.147.128 -P8066 7.1.3.2.切换到TESTDB数据库，查询orders表数据use TESTDB; select * from orders;7.1.3.3.查询到数据7.1.3.4.执行插入数据sqlinsert into orders(id,order_type,customer_id,amount) values(7,101,101,10000); insert into orders(id,order_type,customer_id,amount) values(next value for MYCATSEQ_ORDERS,101,101,10000);7.1.3.5.看到运行结果，插入成功7.1.4.privileges 标签权限控制在user 标签下的privileges 标签可以对逻辑库（schema）、表（table）进行精细化的DML 权限控制。 privileges 标签下的check 属性，如为true 开启权限检查，为 false 不开启，默认为 false。 由于 Mycat 一个用户的 schemas 属性可配置多个逻辑库（schema） ，所以 privileges 的下级节点 schema 节点同样可配置多个，对多库多表进行细粒度的 DML 权限控制。7.1.4.1.修改server.xml配置文件privileges部分vi /usr/local/mycat/conf/server.xml#server.xml配置文件privileges部分 #配置orders表没有增删改查权限 <user name="mycat"> <property name="password">123456</property> <property name="schemas">TESTDB</property> <!-- 表级 DML 权限设置 --> <privileges check="true"> <schema name="TESTDB" dml="1111" > <table name="orders" dml="0000"></table> <!--<table name="tb02" dml="1111"></table>--> </schema> </privileges> </user> 7.1.4.2.配置说明DML 权限增加（insert）更新（update）查询（select）删除（select）0000禁止禁止禁止禁止0010禁止禁止可以禁止1110可以禁止禁止禁止1111可以可以可以可以7.1.5.测试案例一使用mycat用户，privileges配置orders表权限为禁止增删改查（dml="0000"）验证是否可以查询出数据，验证是否可以写入数据7.1.5.1.重启mycat，用mycat用户登录，运行命令mysql -umycat -p123456 -h 192.168.147.128 -P80667.1.5.2.切换到TESTDB数据库，查询orders表数据use TESTDB; select * from orders;7.1.5.3.禁止该用户查询数据7.1.5.4.执行插入数据sqlinsert into orders(id,order_type,customer_id,amount) values(8,101,101,10000); insert into orders(id,order_type,customer_id,amount) values(next value for MYCATSEQ_ORDERS,105,102,10000);7.1.5.5.可看到运行结果，禁止该用户插入数据7.1.6.测试案例二使用mycat用户，privileges配置orders表权限为禁止增删改查（dml="1111"）验证是否可以查询出数据，验证是否可以写入数据vi /usr/local/mycat/conf/server.xml7.1.6.1.重启mycat，用mycat用户登录，运行命令mysql -umycat -p123456 -h 192.168.147.128 -P80667.1.6.2.切换到TESTDB数据库，查询orders表数据use TESTDB; select * from orders;7.1.6.3.可以查询到数据，如图7.1.6.4.执行插入数据sqlinsert into orders(id,order_type,customer_id,amount) values(8,101,101,10000);7.1.6.5.运行结果，插入成功7.1.6.6.执行删除数据sqldelete from orders where id in (7,8);7.1.6.7.运行结果，删除成功7.2.SQL 拦截firewall 标签用来定义防火墙；firewall 下 whitehost 标签用来定义 IP 白名单 ，blacklist 用来定义 SQL 黑名单。7.2.1.白名单可以通过设置白名单，实现某主机某用户可以访问Mycat，而其他主机用户禁止访问。7.2.1.1.设置白名单vi /usr/local/mycat/conf/server.xml#设置白名单 #server.xml配置文件firewall标签 #配置只有192.168.140.129主机可以通过mycat用户访问 <firewall> <whitehost> <host host="192.168.147.129" user="mycat"/> </whitehost> </firewall> 7.2.1.2.重启Mycat后，192.168.147.129主机使用mycat用户访问mysql -umycat -p123456 -h 192.168.140.128 -P 80667.2.1.3.可以正常访问7.2.1.4.在此主机换user用户访问，禁止访问mysql -uuser -puser -h 192.168.147.128 -P80667.2.1.5.在192.168.147.130主机用mycat用户访问，禁止访问mysql -umycat -p -h 192.168.147.128 -P 8066 1234567.2.2.黑名单可以通过设置黑名单，实现Mycat 对具体 SQL 操作的拦截，如增删改查等操作的拦截。7.2.2.1.设置黑名单vi /usr/local/mycat/conf/server.xml#设置黑名单 #server.xml配置文件firewall标签 #配置禁止mycat用户进行删除操作 <firewall> <whitehost> <host host="192.168.147.129" user="mycat"/> </whitehost> <blacklist check="true"> <property name="deleteAllow">false</property> </blacklist> </firewall> 7.2.2.2.重启Mycat后，192.168.147.129主机使用mycat用户访问mysql -umycat -p -h 192.168.147.128 -P 8066 1234567.2.2.3.可以正常访问7.2.2.4.切换TESTDB数据库后，执行删除数据语句use TESTDB; select * from orders; delete from orders where id = 6;7.2.2.5.运行后发现已禁止删除数据7.2.3.可以设置的黑名单SQL 拦截功能列表配置项缺省值描述selelctAllowtrue是否允许执行 SELECT 语句deleteAllowtrue是否允许执行 DELETE 语句updateAllowtrue是否允许执行 UPDATE 语句insertAllowtrue是否允许执行 INSERT 语句createTableAllowtrue是否允许创建表setAllowtrue是否允许使用 SET 语法alterTableAllowtrue是否允许执行 Alter Table 语句dropTableAllowtrue是否允许修改表commitAllowtrue是否允许执行 commit 操作rollbackAllowtrue是否允许执行 roll back 操作八、 ZooKeeper集群搭建ZooKeeper官网下载地址：https://zookeeper.apache.org/releases.htmlapache下载：https://downloads.apache.org/8.1.ZooKeeper简介ZooKeeper是一个分布式的，开放源码的分布式应用程序协调服务，是Google的Chubby一个开源的实现，是Hadoop和Hbase的重要组件。ZooKeeper是一个分布式数据一致性的解决方案，分布式应用可以基于它实现发布订阅、负载均衡、命名服务、协调通知、集群管理、master选举、分布式锁和分布式队列的特性。8.2.数据复制的好处容错：一个节点出错，不至于让整个集群无法提供服务扩展性：通过增加服务器节点能提高 ZooKeeper 系统的负载能力，把负载分布到多个节点上高性能：客户端可访问本地 ZooKeeper 节点或者访问就近的节点，依次提高用户的访问速度8.3.Zookeeper设计目的Zookeeper的设计目的是提供高性能（简单的数据模型），高可用（构建集群），顺序一致性（严格顺序访问）的分布式协调服务，保证数据一致性。最终一致性：client不论连接到哪个Server，展示给它都是同一个视图，这是zookeeper最重要的性能。可靠性：具有简单、健壮、良好的性能，如果消息被到一台服务器接受，那么它将被所有的服务器接受。实时性：Zookeeper保证客户端将在一个时间间隔范围内获得服务器的更新信息，或者服务器失效的信息。但由于网络延时等原因，Zookeeper不能保证两个客户端能同时得到刚更新的数据，如果需要最新数据，应该在读数据之前调用sync()接口。等待无关（wait-free）：慢的或者失效的client不得干预快速的client的请求，使得每个client都能有效的等待。原子性：更新只能成功或者失败，没有中间状态。顺序性：包括全局有序和偏序两种：全局有序是指如果在一台服务器上消息a在消息b前发布，则在所有Server上消息a都将在消息b前被发布；偏序是指如果一个消息b在消息a后被同一个发送者发布，a必将排在b前面。8.4.zookeeper集群包括3种角色zookeeper集群包括3种角色：leader（核心）、follower、observer8.4.1.Leader角色Leader是整个 zookeeper 集群的核心，Leader 主要有三个功能：集群异常时从快照和日志中恢复数据维持与 Learner 的心跳，接收 Learner 请求并判断 Learner 的请求消息类型根据不同的消息类型，进行不同的处理。 Learner 的消息类型主要有：PING 消息：指Learner 的心跳信息REQUEST 消息：Follower 发送的提议信息，包括读写请求和同步请求ACK 消息：Follower 对提议的回复，超过半数的 Follower 通过，则 commit 该提议REVALIDATE 消息：用来延长 Session 有效时间8.4.2.Follower 角色在Zookeeper集群内部，Follower 主要有四个功能：向 Leader 发送请求（PING 消息、REQUEST 消息、ACK 消息、REVALIDATE 消息）；接收 Leader 消息并进行处理；接收 Client 的请求，如果为写请求，则转发给 Leader进行投票；返回 Client 结果。Leader 的消息有：PING 消息： 心跳消息；PROPOSAL 消息：Leader 发起的提案，要求 Follower 投票；COMMIT 消息：服务器端最新一次提案的信息；UPTODATE 消息：表明同步完成；REVALIDATE 消息：根据 Leader 的 REVALIDATE 结果，关闭待 revalidate 的 session 还是允许其接受消息；SYNC 消息：返回 SYNC 结果到客户端，这个消息最初由客户端发起，用来强制得到最新的更新。8.4.3.Observer 角色Observer 是 zookeeper3.3 开始引入的一个全新的服务器角色，观察者角色。观察 zookeeper 集群中的最新状态变化并将这些状态变化同步到 observer 服务器上。Observer 流程和 Follower 的唯一不同的地方就是 Observer 不会参加 Leader 发起的投票，也不会被选举为 Leader。8.5.zookeeper集群工作流程zookeeper集群只能有一个leader节点，由所有follower选举产生，只有leader节点才有权利发起修改数据的操作，而follower节点即使接收到了客户端发起的修改操作，也要将其转交给leader来处理，leader接收到修改数据的请求后，会向所有follower广播一条消息，让他们执行某项操作，follower 执行完后，便会向 leader 回复执行完毕。当 leader 收到半数以上的 follower 的确认消息，便会判定该操作执行完毕，然后向所有 follower 广播该操作已经生效。8.6.zookeeper集群节点数量为奇数，建议至少3个节点如果要搭建一个能够允许N台机器down掉的集群，那么就要部署2*N+1台服务器构成的 zookeeper 集群8.7.ZooKeeper 集群搭建确保关闭防火墙机器名称IP服务器角色环境要求host128192.168.147.128zk节点1CentOS7.9 + zk 3.8.3+JDK1.8host129192.168.147.129zk节点2CentOS7.9 + zk 3.8.3+JDK1.8host130192.168.147.130zk节点3CentOS7.9 + zk 3.8.3+JDK1.88.7.1.下载ZooKeeper安装包并解压apache下载：https://downloads.apache.org/ZooKeeper官网下载地址：https://zookeeper.apache.org/releases.htmlcd /opt/ wget https://downloads.apache.org/zookeeper/zookeeper-3.8.3/apache-zookeeper-3.8.3-bin.tar.gz tar -xzvf apache-zookeeper-3.8.3-bin.tar.gz -C /usr/local/src8.7.2.进入ZooKeeper解压后的配置目录（conf），复制配置文件并改名为zoo.cfg# 更名 cd /usr/local/src mv apache-zookeeper-3.8.3-bin zookeeper cd /usr/local/src/zookeeper/conf cp /usr/local/src/zookeeper/conf/zoo_sample.cfg /usr/local/src/zookeeper/conf/zoo.cfg8.7.3.修改zookeeper数据文件存放目录dataDir=/data/zookeeper8.7.4.修改zoo.cfg 配置文件内容为vi /usr/local/src/zookeeper/conf/zoo.cfg# tickTime这个时间是作为zookeeper服务器之间或客户端与服务器之间维持心跳的时间间隔为2ms,也就是说每隔tickTime时间就会发送一个心跳。 tickTime=2000 # initLimit这个配置项是用来配置zookeeper接受客户端 # （这里所说的客户端不是用户连接zookeeper服务器的客户端,而是zookeeper服务器集群中连接到leader的follower 服务器） # 初始化连接时最长能忍受多少个心跳时间间隔数。当初始化连接时间超过该值，则表示连接失败。 # 当已经超过10个心跳的时间（也就是tickTime）长度后 zookeeper 服务器还没有收到客户端的返回信息,那么表明这个客户端连接失败。 # 对于从节点最初连接到主节点时的超时时间，单位为tick值的倍数。总的时间长度就是 10*2000。即20ms initLimit=10 # syncLimit这个配置项标识leader与follower之间发送消息,请求和应答时间长度,最长不能超过多少个tickTime的时间长度 # 如果follower在设置时间内不能与leader通信，那么此follower将会被丢弃。 # 对于主节点与从节点进行同步操作时的超时时间，单位为tick值的倍数。总的时间长度就是5*2000。即10ms syncLimit=5 # dataDir就是zookeeper保存数据库数据快照的位置,默认情况下zookeeper将写数据的日志文件也保存在这个目录里 # 注意：不能使用 /tmp 路径，会被定期清除。使用专用的存储设备能够大大提高系统的性能 # dataDir=/tmp/zookeeper dataLogDir=/opt/zookeeper/logs # 数据文件存放目录 dataDir=/opt/zookeeper/data # clientPort这个端口就是客户端连接Zookeeper服务器的端口,Zookeeper会监听这个端口接受客户端的访问请求 clientPort=2181 # 客户端最大链接数 maxClientCnxns=60 # zookeeper在运行过程中会生成快照数据，默认不会自动清理，会持续占用硬盘空间 # 保存3个快照，即3个日志文件 autopurge.snapRetainCount=3 # 间隔1个小时执行一次清理 autopurge.purgeInterval=1 # server.A=B:C:D # 其中A是一个数字,表示这个是第几号服务器 # B是这个服务器的IP地址 # C第一个端口用来集群成员的信息交换,表示这个服务器与集群中的leader服务器交换信息的端口 # D是在leader挂掉时专门用来进行选举leader选举所用的端口 # 服务器=运行主机：心跳端口：选举端口 # zk集群 # 3888后面位置不能有空格，否则Address unresolved: 192.168.147.128:3888 server.1=192.168.147.128:2888:3888 server.2=192.168.147.129:2888:3888 server.3=192.168.147.130:2888:3888#创建相关目录，三台节点都需要 mkdir -p /opt/zookeeper/{logs,data} # 赋予权限 chmod 777 /opt/zookeeper/{logs,data}8.7.5.创建ServerID标识# 在三台服务器的主配置文件 zoo.cfg 中指定的 dataDir 目录下 新建myid文件，并写入对应的server.num 中的num数字 # 如：在192.168.147.128上将server.1中1写入myid # 如：在192.168.147.129上将server.2中2写入myid # 数据文件存放目录 dataDir=/opt/zookeeper/data echo 1 >/opt/zookeeper/data/myid cat /opt/zookeeper/data/myid8.7.6.添加到环境变量vim /etc/profile# zookeeper export ZK_HOME=/usr/local/src/zookeeper export PATH=$PATH:$ZK_HOME/bin# 输入下面命令让设置的环境变量生效 source /etc/profile8.7.7.ZooKeeper启动命令zkServer.sh start zkServer.sh status zkServer.sh stop zkServer.sh restart #以打印日志方式启动 zkServer.sh start-foreground # jps 是 Java Process Status Tool 的简称,它的作用是为了列出所有正在运行中的 Java 虚拟机进程 # 每一个 Java 程序在启动的时候都会为之创建一个Jvm 实例,通过jps可以查看这些进程的相关信息 # jps是Jdk提供的一个工具,它安装在 JAVA_HOME/bin下 jps8.7.8.ZooKeeper服务端口为2181，查看服务已经启动ps -aux | grep zookeeper netstat -ant | grep 21818.7.9.注意：Starting zookeeper … FAILED TO STARTzookeeper版本只要 >= 3.5.5 版本启动时启动错误提示“Starting zookeeper … FAILED TO START”问题原因：下载了错误的版本文件，Zookeeper 从3.5.5后开始拆分为两个版本，而且他们的结构还很类似。标准版本（Apache ZooKeeper x.y.z ）下载的文件名为：apache-zookeeper-x.y.z-bin.tar.gz`源码版本（Apache ZooKeeper x.y.z Source Release）下载的文件名为：apache-zookeeper-x.y.z.tar.gz在安装时下载标准版本安装使用即可正常使用8.7.10.Address unresolved# 查看日志 cat /usr/local/src/zookeeper/logs/zookeeper-root-server-host128.out[root@host128 conf]# zkServer.sh start ZooKeeper JMX enabled by default Using config: /usr/local/src/zookeeper/bin/../conf/zoo.cfg Starting zookeeper ... FAILED TO START [root@host128 logs]# cat zookeeper-root-server-host128.out 2024-02-26 21:01:09,066 [myid:] - INFO [main:o.a.z.s.q.QuorumPeerConfig@177] - Reading configuration from: /usr/local/src/zookeeper/bin/../conf/zoo.cfg 2024-02-26 21:01:09,075 [myid:] - INFO [main:o.a.z.s.q.QuorumPeerConfig@440] - clientPortAddress is 0.0.0.0:2181 2024-02-26 21:01:09,076 [myid:] - INFO [main:o.a.z.s.q.QuorumPeerConfig@444] - secureClientPort is not set 2024-02-26 21:01:09,076 [myid:] - INFO [main:o.a.z.s.q.QuorumPeerConfig@460] - observerMasterPort is not set 2024-02-26 21:01:09,076 [myid:] - INFO [main:o.a.z.s.q.QuorumPeerConfig@477] - metricsProvider.className is org.apache.zookeeper.metrics.impl.DefaultMetricsProvider 2024-02-26 21:01:09,086 [myid:] - ERROR [main:o.a.z.s.q.QuorumPeerMain@99] - Invalid config, exiting abnormally org.apache.zookeeper.server.quorum.QuorumPeerConfig$ConfigException: Address unresolved: 192.168.147.128:3888 at org.apache.zookeeper.server.quorum.QuorumPeer$QuorumServer.initializeWithAddressString(QuorumPeer.java:359) at org.apache.zookeeper.server.quorum.QuorumPeer$QuorumServer.<init>(QuorumPeer.java:280) at org.apache.zookeeper.server.quorum.QuorumPeer$QuorumServer.<init>(QuorumPeer.java:275) at org.apache.zookeeper.server.quorum.flexible.QuorumMaj.<init>(QuorumMaj.java:96) at org.apache.zookeeper.server.quorum.QuorumPeerConfig.createQuorumVerifier(QuorumPeerConfig.java:654) at org.apache.zookeeper.server.quorum.QuorumPeerConfig.createQuorumVerifier(QuorumPeerConfig.java:640) at org.apache.zookeeper.server.quorum.QuorumPeerConfig.parseDynamicConfig(QuorumPeerConfig.java:685) at org.apache.zookeeper.server.quorum.QuorumPeerConfig.setupQuorumPeerConfig(QuorumPeerConfig.java:659) at org.apache.zookeeper.server.quorum.QuorumPeerConfig.parseProperties(QuorumPeerConfig.java:487) at org.apache.zookeeper.server.quorum.QuorumPeerConfig.parse(QuorumPeerConfig.java:194) at org.apache.zookeeper.server.quorum.QuorumPeerMain.initializeAndRun(QuorumPeerMain.java:125) at org.apache.zookeeper.server.quorum.QuorumPeerMain.main(QuorumPeerMain.java:91) Invalid config, exiting abnormally 2024-02-26 21:01:09,086 [myid:] - INFO [main:o.a.z.a.ZKAuditProvider@42] - ZooKeeper audit is disabled. 2024-02-26 21:01:09,087 [myid:] - ERROR [main:o.a.z.u.ServiceUtils@48] - Exiting JVM with code 28.8.Zookeeper集群查看【host129为leader】zkServer.sh start zkServer.sh status ifconfig ens33机器名称IP服务器角色host128192.168.147.128followerhost129192.168.147.129leaderhost130192.168.147.130follower8.9.模拟集群故障--【host130为leader】zkServer.sh stop zkServer.sh status8.10.Zookeeper客户端验证Zookeeper自带客户端工具 zkCli.sh，可以实现连接服务、数据增删改查等功能zkCli.sh -server 192.168.147.128:2181 zkCli.sh -server 192.168.147.130:21818.11.zkCli客户端支持操作命令[zk: 192.168.147.128:2181(CONNECTED) 0] help ZooKeeper -server host:port -client-configuration properties-file cmd args addWatch [-m mode] path # optional mode is one of [PERSISTENT, PERSISTENT_RECURSIVE] - default is PERSISTENT_RECURSIVE addauth scheme auth close config [-c] [-w] [-s] connect host:port create [-s] [-e] [-c] [-t ttl] path [data] [acl] delete [-v version] path deleteall path [-b batch size] delquota [-n|-b|-N|-B] path get [-s] [-w] path getAcl [-s] path getAllChildrenNumber path getEphemerals path history listquota path ls [-s] [-w] [-R] path printwatches on|off quit reconfig [-s] [-v version] [[-file path] | [-members serverID=host:port1:port2;port3[,...]*]] | [-add serverId=host:port1:port2;port3[,...]]* [-remove serverId[,...]*] redo cmdno removewatches path [-c|-d|-a] [-l] set [-s] [-v version] path data setAcl [-s] [-v version] [-R] path acl setquota -n|-b|-N|-B val path stat [-w] path sync path version whoami Command not found: Command not found help九、Mycat 监控工具9.1.Mycat-web 简介Mycat-web 是Mycat 可视化运维的管理和监控平台，弥补了Mycat 在监控上的空白。帮助 Mycat 分担统计任务和配置管理任务。Mycat-web 引入了 ZooKeeper 作为配置中心，可以管理多个节点。 Mycat-web 主要管理和监控 Mycat 的流量、连接、活动线程和内存等，具备IP 白名单、邮件告警等模块，还可以统计SQL 并分析慢 SQL 和高频SQL 等。为优化 SQL 提供依据。9.2.Mycat-web 配置使用9.2.1.ZooKeeper 安装【上面有】9.2.2.Mycat-web 安装Mycat的官网：http://www.mycat.org.cn/9.2.2.1.下载安装包9.2.2.2.安装包拷贝到Linux系统/opt目录下，并解压tar -xzvf Mycat-web-1.0-SNAPSHOT-20170102153329-linux.tar.gz9.2.2.3.拷贝mycat-web文件夹到/usr/local目录下cp -r mycat-web /usr/local9.2.2.4.进入mycat-web的目录下运行启动命令cd /usr/local/mycat-web/ ./start.sh &9.2.2.5.Mycat-web服务端口为8082，查看服务已经启动netstat -ant | grep 80829.2.2.6.保证其它服务上线# 关闭防火墙 systemctl stop firewalld # 启动mysqld【host128、host129】 systemctl start mysqld systemctl status mysqld # 启动mycat【host128、host129】 mycat console # 启动haproxy【host130、host131】 systemctl start haproxy systemctl status haproxy # 启动Keepalived【host130、host131】 systemctl start keepalived systemctl status keepalived # 启动ZooKeeper【host128、host129、host130】 zkServer.sh start zkServer.sh status9.2.2.7.通过地址访问服务http://192.168.147.128:8082/mycat/9.2.3.Mycat-web 配置9.2.3.1.先在注册中心配置ZooKeeper地址，配置后刷新页面9.2.3.2.新增Mycat监控实例9.3.Mycat 性能监控指标在Mycat-web 上可以进行Mycat 性能监控，例如：内存分享、流量分析、连接分析、活动线程分析等等。endl

MySQL数据库练习【三】 @TOC一、建库建表-数据准备create database company; use company; -- 创建部门表 DROP TABLE IF EXISTS dept; CREATE TABLE `dept` ( `deptno` int(11) PRIMARY KEY NOT NULL COMMENT '部门编号', `dname` varchar(14) NOT NULL COMMENT '部门名称', `loc` varchar(13) NOT NULL COMMENT '部门所在地' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='部门表'; -- 向部门表插入数据 INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK'), (20,'RESEARCH','DALLAS'), (30,'SALES','CHICAGO'), (40,'OPERATIONS','BOSTON'); -- 创建员工表 DROP TABLE IF EXISTS emp; CREATE TABLE `emp` ( `empno` int(11) PRIMARY KEY NOT NULL COMMENT '员工编号', `ename` varchar(10) DEFAULT NULL COMMENT '员工姓名', `job` varchar(9) DEFAULT NULL COMMENT '工作岗位', `mgr` int(11) DEFAULT NULL COMMENT '直属领导', `hiredate` date DEFAULT NULL COMMENT '入职时间', `sal` double DEFAULT NULL COMMENT '工资', `comm` double DEFAULT NULL COMMENT '奖金', `deptno` int(11) DEFAULT NULL COMMENT '所属部门' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工表'; -- 向员工表插入数据 INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,"1980-12-17",800,NULL,20), (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30), (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30), (7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20), (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30), (7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30), (7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10), (7788,'SCOTT','ANALYST',7566,'1987-07-03',3000,NULL,20), (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10), (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30), (7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20), (7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30), (7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20), (7934,'MILLER','CLERK',7782,'1981-01-23',1300,NULL,10); -- 创建工资等级表 DROP TABLE IF EXISTS salgrade; CREATE TABLE `salgrade` ( `grade` int(11) DEFAULT NULL COMMENT '等级', `losal` double DEFAULT NULL COMMENT '最低工资', `hisal` double DEFAULT NULL COMMENT '最高工资' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='工资等级表'; -- 向工资等级表插入数据 INSERT INTO salgrade VALUES (1,700,1200),(2,1201,1400),(3,1401,2000),(4,2001,3000),(5,3001,9999);员工表emp部门表dept工资等级表salgradeempno：员工编号deptno：部门编号grade：等级ename：员工姓名dname：部门名称losal：最低工资job：工作岗位loc：部门所在地hisal：最高工资mgr：直属领导 hiredate：入职时间 sal：工资 comm：奖金 deptno：所属部门 empnoenamejobmgrhiredatesalcommdeptno7369SMITHCLERK79021980-12-17800NULL207499ALLENSALESMAN76981981-02-201600300307521WARDSALESMAN76981981-02-221250500307566JONESMANAGER78391981-04-022975NULL207654MARTINSALESMAN76981981-09-2812501400307698BLAKEMANAGER78391981-05-012850NULL307782CLARKMANAGER78391981-06-092450NULL107788SCOTTANALYST75661987-07-033000NULL207839KINGPRESIDENTNULL1981-11-175000NULL107844TURNERSALESMAN76981981-09-0815000307876ADAMSCLERK77881987-07-131100NULL207900JAMESCLERK76981981-12-03950NULL307902FORDANALYST75661981-12-033000NULL207934MILLERCLERK77821981-01-231300NULL10deptnodnameloc10ACCOUNTINGNEW YORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTONgradelosalhisal17001200212011400314012000420013000530019999二、动力节点—Mysql—经典34道习题1、查询每个部门最高薪水的人员名称 2、哪些人的薪水在部门的平均薪水之上 3、取得部门中（所有人的）平均的薪水等级 4、不准用组函数（Max），查询最高薪水 5、查询平均薪水最高的部门的部门编号 6、查询平均薪水最高的部门的部门名称 7、求平均薪水的等级最低的部门的部门名称 8、查询比普通员工(员工代表没有在 mgr 字段上出现的) 的最高薪水还要高的领导人姓名 9、查询薪水最高的前五名员工 10、查询薪水最高的第六到第十名员工 11、查询查询最后入职的 5 名员工 12、查询每个薪水等级有多少员工 13、面试题： 14、列出所有员工及领导的姓名 15、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称 16、列出部门名称和这些部门的员工信息, 同时列出那些没有员工的部门 17、列出至少有 5 个员工的所有部门 18、列出薪金比"SMITH" 多的所有员工信息 19、列出所有"CLERK"( 办事员) 的姓名及其部门名称, 部门的人数 20、列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数,按照工作岗位分组求最小值 21、列出在部门"SALES"< 销售部> 工作的员工的姓名, 假定不知道销售部的部门编号 22、列出薪金高于公司平均薪金的所有员工, 所在部门, 上级领导, 雇员的工资等级 23、列出与"SCOTT" 从事相同工作的所有员工及部门名称 24、列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金 25、列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金、部门名称 26、列出在每个部门工作的员工数量, 平均工资和平均服务期限 26、在mysql当中怎么计算两个日期的“年差”，差了多少年【知识点】 27、列出所有员工的姓名、部门名称和工资 28、列出所有部门的详细信息和人数 29、列出各种工作的最低工资及从事此工作的雇员姓名 30、列出各个部门的 MANAGER( 领导) 的最低薪金 31、列出所有员工的 年工资, 按 年薪从低到高排序 32、求出员工领导的薪水超过3000的员工名称与领导 33、求出部门名称中, 带'S'字符的部门员工的工资合计、部门人数 34、给任职日期超过 30 年的员工加薪 10%三、动力节点—Mysql—经典34道答案3.1、查询每个部门最高薪水的人员名称-- 第一步：取得每个部门最高薪水(按照部门编号分组，找出每一组最大值) select deptno, max(sal) from emp group by deptno;mysql> select -> deptno, -> max(sal) -> from emp -> group by deptno; +--------+----------+ | deptno | max(sal) | +--------+----------+ | 10 | 5000 | | 20 | 3000 | | 30 | 2850 | +--------+----------+ 3 rows in set (0.00 sec)-- 第二步：将以上的查询结果当做一张临时表t， -- t和emp表连接，条件：t.deptno = e.deptno and t.maxsal = e.sal select e.ename, t.* from emp e join (select deptno,max(sal) as maxsal from emp group by deptno) t on t.deptno = e.deptno and t.maxsal = e.sal;mysql> select -> e.ename, -> t.* -> from emp e -> join (select deptno,max(sal) as maxsal from emp group by deptno) t -> on t.deptno = e.deptno and t.maxsal = e.sal; +-------+--------+--------+ | ename | deptno | maxsal | +-------+--------+--------+ | BLAKE | 30 | 2850 | | SCOTT | 20 | 3000 | | KING | 10 | 5000 | | FORD | 20 | 3000 | +-------+--------+--------+ 4 rows in set (0.00 sec)select e1.ename, e1.sal, count(distinct e2.sal) rank from emp e1 join emp e2 on e1.deptno = e2.deptno and e1.sal <= e2.sal group by e1.empno order by rank;mysql> select -> e1.ename, -> e1.sal, -> count(distinct e2.sal) rank -> from emp e1 -> join emp e2 on e1.deptno = e2.deptno and e1.sal <= e2.sal -> group by e1.empno -> order by rank; +--------+------+------+ | ename | sal | rank | +--------+------+------+ | SCOTT | 3000 | 1 | | FORD | 3000 | 1 | | KING | 5000 | 1 | | BLAKE | 2850 | 1 | | JONES | 2975 | 2 | | ALLEN | 1600 | 2 | | CLARK | 2450 | 2 | | MILLER | 1300 | 3 | | TURNER | 1500 | 3 | | ADAMS | 1100 | 3 | | WARD | 1250 | 4 | | MARTIN | 1250 | 4 | | SMITH | 800 | 4 | | JAMES | 950 | 5 | +--------+------+------+ 14 rows in set (0.00 sec)select e1.ename, e1.sal, count(distinct e2.sal) rank from emp e1 join emp e2 on e1.deptno = e2.deptno and e1.sal <= e2.sal group by e1.empno having rank = 1 order by rank;mysql> select -> e1.ename, -> e1.sal, -> count(distinct e2.sal) rank -> from emp e1 -> join emp e2 on e1.deptno = e2.deptno and e1.sal <= e2.sal -> group by e1.empno -> having rank = 1 -> order by rank; +-------+------+------+ | ename | sal | rank | +-------+------+------+ | SCOTT | 3000 | 1 | | FORD | 3000 | 1 | | KING | 5000 | 1 | | BLAKE | 2850 | 1 | +-------+------+------+ 4 rows in set (0.01 sec)3.2、哪些人的薪水在部门的平均薪水之上-- 第一步：找出每个部门的平均薪水 select e.deptno, avg(e.sal) from emp e group by e.deptno;mysql> select -> e.deptno, -> avg(e.sal) -> from emp e -> group by e.deptno; +--------+--------------------+ | deptno | avg(e.sal) | +--------+--------------------+ | 10 | 2916.6666666666665 | | 20 | 2175 | | 30 | 1566.6666666666667 | +--------+--------------------+ 3 rows in set (0.00 sec)-- 第二步：将以上查询结果当做t表，t和emp表连接 -- 条件：部门编号相同，并且emp的sal大于t表的avgsal select e.ename, e.sal, t.deptno, round(t.avgsal,2) '平均薪资' from emp e join (select deptno,avg(sal) avgsal from emp group by deptno) t on e.deptno = t.deptno and e.sal > t.avgsal order by e.deptno;mysql> select -> e.ename, -> e.sal, -> t.deptno, -> round(t.avgsal,2) '平均薪资' -> from emp e -> join (select deptno,avg(sal) avgsal from emp group by deptno) t -> on e.deptno = t.deptno and e.sal > t.avgsal -> order by e.deptno; +-------+------+--------+--------------+ | ename | sal | deptno | 平均薪资 | +-------+------+--------+--------------+ | KING | 5000 | 10 | 2916.67 | | JONES | 2975 | 20 | 2175.00 | | SCOTT | 3000 | 20 | 2175.00 | | FORD | 3000 | 20 | 2175.00 | | ALLEN | 1600 | 30 | 1566.67 | | BLAKE | 2850 | 30 | 1566.67 | +-------+------+--------+--------------+ 6 rows in set (0.00 sec)3.3、查询部门中（所有人的）平均的薪水等级3.3.1.平均的薪水等级：先计算每一个薪水的等级，然后找出薪水等级的平均值。-- 第一步：找出每个人的薪水等级 -- emp e和salgrade s表连接 -- 连接条件：e.sal between s.losal and s.hisal select e.ename, e.sal, e.deptno, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;mysql> select -> e.ename, -> e.sal, -> e.deptno, -> s.grade -> from emp e -> join salgrade s on e.sal between s.losal and s.hisal; +--------+------+--------+-------+ | ename | sal | deptno | grade | +--------+------+--------+-------+ | SMITH | 800 | 20 | 1 | | ALLEN | 1600 | 30 | 3 | | WARD | 1250 | 30 | 2 | | JONES | 2975 | 20 | 4 | | MARTIN | 1250 | 30 | 2 | | BLAKE | 2850 | 30 | 4 | | CLARK | 2450 | 10 | 4 | | SCOTT | 3000 | 20 | 4 | | KING | 5000 | 10 | 5 | | TURNER | 1500 | 30 | 3 | | ADAMS | 1100 | 20 | 1 | | JAMES | 950 | 30 | 1 | | FORD | 3000 | 20 | 4 | | MILLER | 1300 | 10 | 2 | +--------+------+--------+-------+ 14 rows in set (0.00 sec)-- 第二步：基于以上的结果继续按照deptno分组，求grade的平均值 select e.deptno, avg(s.grade) from emp e join salgrade s on e.sal between s.losal and s.hisal group by e.deptno;mysql> select -> e.deptno, -> avg(s.grade) -> from emp e -> join salgrade s on e.sal between s.losal and s.hisal -> group by e.deptno; +--------+--------------+ | deptno | avg(s.grade) | +--------+--------------+ | 10 | 3.6667 | | 20 | 2.8000 | | 30 | 2.5000 | +--------+--------------+ 3 rows in set (0.00 sec)3.3.2.平均薪水的等级：先计算平均薪水，然后找出每个平均薪水的等级值select e.deptno, avg(e.sal) from emp e group by e.deptno;mysql> select -> e.deptno, -> avg(e.sal) -> from emp e -> group by e.deptno; +--------+--------------------+ | deptno | avg(e.sal) | +--------+--------------------+ | 10 | 2916.6666666666665 | | 20 | 2175 | | 30 | 1566.6666666666667 | +--------+--------------------+ 3 rows in set (0.00 sec)select t.deptno, round(t.avgsal,2), s.grade from (select deptno,avg(sal) avgsal from emp group by deptno) t join salgrade s on t.avgsal between s.losal and s.hisal;mysql> select -> t.deptno, -> round(t.avgsal,2), -> s.grade -> from (select deptno,avg(sal) avgsal from emp group by deptno) t -> join salgrade s on t.avgsal between s.losal and s.hisal; +--------+-------------------+-------+ | deptno | round(t.avgsal,2) | grade | +--------+-------------------+-------+ | 10 | 2916.67 | 4 | | 20 | 2175.00 | 4 | | 30 | 1566.67 | 3 | +--------+-------------------+-------+ 3 rows in set (0.00 sec)3.4、不准用组函数（Max），查询最高薪水select ename,sal from emp order by sal desc limit 1;mysql> select ename,sal from emp order by sal desc limit 1; +-------+------+ | ename | sal | +-------+------+ | KING | 5000 | +-------+------+ 1 row in set (0.00 sec)select max(sal) from emp;mysql> select max(sal) from emp; +----------+ | max(sal) | +----------+ | 5000 | +----------+ 1 row in set (0.01 sec)select ename, sal from emp where sal not in (select distinct a.sal from emp a join emp b on a.sal < b.sal);mysql> select -> ename, -> sal -> from emp -> where sal not in (select distinct a.sal from emp a join emp b on a.sal < b.sal); +-------+------+ | ename | sal | +-------+------+ | KING | 5000 | +-------+------+ 1 row in set (0.00 sec)3.5、查询平均薪水最高的部门的部门编号-- 第一步：找出每个部门的平均薪水 select deptno,round(avg(sal),2) avgsal from emp group by deptno;mysql> select deptno,round(avg(sal),2) avgsal from emp group by deptno; +--------+---------+ | deptno | avgsal | +--------+---------+ | 10 | 2916.67 | | 20 | 2175.00 | | 30 | 1566.67 | +--------+---------+ 3 rows in set (0.01 sec)-- 第二步：降序选第一个 select deptno,round(avg(sal),2) avgsal from emp group by deptno order by avgsal desc limit 1;mysql> select deptno,round(avg(sal),2) avgsal from emp group by deptno order by avgsal desc limit 1; +--------+---------+ | deptno | avgsal | +--------+---------+ | 10 | 2916.67 | +--------+---------+ 1 row in set (0.00 sec)select deptno, avg(sal) avgsal from emp group by deptno having avgsal = (select max(t.avgsal) from (select avg(sal) avgsal from emp group by deptno) t); -- 使用having语句过滤mysql> select -> deptno, -> avg(sal) avgsal -> from emp -> group by deptno -> having avgsal = (select max(t.avgsal) from (select avg(sal) avgsal from emp group by deptno) t); +--------+--------------------+ | deptno | avgsal | +--------+--------------------+ | 10 | 2916.6666666666665 | +--------+--------------------+ 1 row in set (0.00 sec)3.6、查询平均薪水最高的部门的部门名称select e1.deptno, d.dname, e1.avgsal from (select deptno,avg(sal) avgsal from emp group by deptno) e1 join dept d on e1.deptno = d.deptno having e1.avgsal = (select max(e.avgsal)from (select deptno,avg(sal) avgsal from emp group by deptno) e);mysql> select -> e1.deptno, -> d.dname, -> e1.avgsal -> from (select deptno,avg(sal) avgsal from emp group by deptno) e1 -> join dept d on e1.deptno = d.deptno -> having e1.avgsal = (select max(e.avgsal)from (select deptno,avg(sal) avgsal from emp group by deptno) e); +--------+------------+--------------------+ | deptno | dname | avgsal | +--------+------------+--------------------+ | 10 | ACCOUNTING | 2916.6666666666665 | +--------+------------+--------------------+ 1 row in set (0.00 sec)select d.dname, avg(e.sal) avgsal from emp e join dept d on e.deptno = d.deptno group by e.deptno order by avgsal desc limit 1;mysql> select -> d.dname, -> avg(e.sal) avgsal -> from emp e -> join dept d on e.deptno = d.deptno -> group by e.deptno -> order by avgsal desc -> limit 1; +------------+--------------------+ | dname | avgsal | +------------+--------------------+ | ACCOUNTING | 2916.6666666666665 | +------------+--------------------+ 1 row in set (0.00 sec)3.7、求平均薪水的等级最低的部门的部门名称select e.avgsal, s.grade from (select deptno,avg(sal) avgsal from emp group by deptno) e join salgrade s on e.avgsal between s.losal and s.hisal group by deptno;mysql> select -> e.avgsal, -> s.grade -> from (select deptno,avg(sal) avgsal from emp group by deptno) e -> join salgrade s on e.avgsal between s.losal and s.hisal -> group by deptno; +--------------------+-------+ | avgsal | grade | +--------------------+-------+ | 2916.6666666666665 | 4 | | 2175 | 4 | | 1566.6666666666667 | 3 | +--------------------+-------+ 3 rows in set (0.00 sec)select e.avgsal, s.grade, d.deptno, d.dname from (select deptno,avg(sal) avgsal from emp group by deptno) e join dept d on e.deptno = d.deptno join salgrade s on e.avgsal between s.losal and s.hisal group by deptno order by s.grade asc limit 1;mysql> select -> e.avgsal, -> s.grade, -> d.deptno, -> d.dname -> from (select deptno,avg(sal) avgsal from emp group by deptno) e -> join dept d on e.deptno = d.deptno -> join salgrade s on e.avgsal between s.losal and s.hisal -> group by deptno -> order by s.grade asc limit 1; +--------------------+-------+--------+-------+ | avgsal | grade | deptno | dname | +--------------------+-------+--------+-------+ | 1566.6666666666667 | 3 | 30 | SALES | +--------------------+-------+--------+-------+ 1 row in set (0.00 sec)3.8、查询比普通员工(员工代表没有在 mgr 字段上出现的) 的最高薪水还要高的领导人姓名-- 普通员工没有出现在mgr上，查找mgr select distinct mgr from emp where mgr is not null;mysql> select distinct mgr from emp where mgr is not null; +------+ | mgr | +------+ | 7902 | | 7698 | | 7839 | | 7566 | | 7788 | | 7782 | +------+ 6 rows in set (0.00 sec)员工编号没有在以上范围内的都是普通员工 -- 第一步：找出普通员工的最高薪水！ -- not in在使用的时候，后面小括号中记得排除NULL select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null);mysql> select max(sal) -> from emp -> where empno not in (select distinct mgr from emp where mgr is not null); +----------+ | max(sal) | +----------+ | 1600 | +----------+ 1 row in set (0.00 sec)-- 第二步：找出高于1600的,比普通员工最高薪水的经理人名字 select ename,sal from emp where sal > ( select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null));mysql> select ename,sal from emp -> where sal > ( -> select max(sal) -> from emp -> where empno not in (select distinct mgr from emp where mgr is not null)); +-------+------+ | ename | sal | +-------+------+ | JONES | 2975 | | BLAKE | 2850 | | CLARK | 2450 | | SCOTT | 3000 | | KING | 5000 | | FORD | 3000 | +-------+------+ 6 rows in set (0.00 sec)3.9、查询薪水最高的前五名员工select ename, sal from emp order by sal desc limit 5;mysql> select -> ename, -> sal -> from emp -> order by sal desc -> limit 5; +-------+------+ | ename | sal | +-------+------+ | KING | 5000 | | FORD | 3000 | | SCOTT | 3000 | | JONES | 2975 | | BLAKE | 2850 | +-------+------+ 5 rows in set (0.00 sec)mysql> select -> ename, -> sal -> from emp -> order by sal desc -> limit 0,5; +-------+------+ | ename | sal | +-------+------+ | KING | 5000 | | FORD | 3000 | | SCOTT | 3000 | | JONES | 2975 | | BLAKE | 2850 | +-------+------+ 5 rows in set (0.00 sec)3.10、查询薪水最高的第六到第十名员工select ename, sal from emp order by sal desc limit 5,5;mysql> select -> ename, -> sal -> from emp -> order by sal desc -> limit 5,5; +--------+------+ | ename | sal | +--------+------+ | CLARK | 2450 | | ALLEN | 1600 | | TURNER | 1500 | | MILLER | 1300 | | WARD | 1250 | +--------+------+ 5 rows in set (0.00 sec)3.11、查询最后入职的 5 名员工select ename, hiredate from emp order by hiredate desc limit 0,5;mysql> select -> ename, -> hiredate -> from emp -> order by hiredate desc limit 0,5; +-------+------------+ | ename | hiredate | +-------+------------+ | ADAMS | 1987-07-13 | | SCOTT | 1987-07-03 | | JAMES | 1981-12-03 | | FORD | 1981-12-03 | | KING | 1981-11-17 | +-------+------------+ 5 rows in set (0.00 sec)3.12、查询每个薪水等级有多少员工-- 第一步：找出每个员工的薪水等级 select e.ename, e.sal, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;mysql> select -> e.ename, -> e.sal, -> s.grade -> from emp e -> join salgrade s on e.sal between s.losal and s.hisal; +--------+------+-------+ | ename | sal | grade | +--------+------+-------+ | SMITH | 800 | 1 | | ALLEN | 1600 | 3 | | WARD | 1250 | 2 | | JONES | 2975 | 4 | | MARTIN | 1250 | 2 | | BLAKE | 2850 | 4 | | CLARK | 2450 | 4 | | SCOTT | 3000 | 4 | | KING | 5000 | 5 | | TURNER | 1500 | 3 | | ADAMS | 1100 | 1 | | JAMES | 950 | 1 | | FORD | 3000 | 4 | | MILLER | 1300 | 2 | +--------+------+-------+ 14 rows in set (0.00 sec)-- 第二步：继续按照grade分组统计数量 select s.grade, count(*) from emp e join salgrade s on e.sal between s.losal and s.hisal group by s.grade;mysql> select -> s.grade, -> count(*) -> from emp e -> join salgrade s on e.sal between s.losal and s.hisal -> group by s.grade; +-------+----------+ | grade | count(*) | +-------+----------+ | 1 | 3 | | 2 | 3 | | 3 | 2 | | 4 | 5 | | 5 | 1 | +-------+----------+ 5 rows in set (0.00 sec)3.13、面试题：有 3 个表 S(学生表)，C（课程表），SC（学生选课表）S（SNO，SNAME）代表（学号，姓名）C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）问题：1，找出没选过“黎明”老师的所有学生姓名。2，列出 2 门以上（含2 门）不及格学生姓名及平均成绩。3，即学过 1 号课程又学过 2 号课所有学生的姓名。3.14、列出所有员工及领导的姓名select e1.ename '员工', e2.ename '领导' from emp e1 join emp e2 on e1.mgr = e2.empno;mysql> select -> e1.ename '员工', -> e2.ename '领导' -> from emp e1 -> join emp e2 on e1.mgr = e2.empno; +--------+--------+ | 员工 | 领导 | +--------+--------+ | SMITH | FORD | | ALLEN | BLAKE | | WARD | BLAKE | | JONES | KING | | MARTIN | BLAKE | | BLAKE | KING | | CLARK | KING | | SCOTT | JONES | | TURNER | BLAKE | | ADAMS | SCOTT | | JAMES | BLAKE | | FORD | JONES | | MILLER | CLARK | +--------+--------+ 13 rows in set (0.00 sec)3.15、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称emp e1 员工表 emp e2 领导表 e1.mgr = e2.empno and e1.hiredate < e2.hiredate select e1.ename '员工', e1.hiredate, e2.ename '领导', e2.hiredate, d.dname '部门名称' from emp e1 join emp e2 on e1.mgr = e2.empno join dept d on e1.deptno = d.deptno where e1.hiredate < e2.hiredate;mysql> select -> e1.ename '员工', -> e1.hiredate, -> e2.ename '领导', -> e2.hiredate, -> d.dname '部门名称' -> from emp e1 -> join emp e2 on e1.mgr = e2.empno -> join dept d on e1.deptno = d.deptno -> where e1.hiredate < e2.hiredate; +--------+------------+--------+------------+--------------+ | 员工 | hiredate | 领导 | hiredate | 部门名称 | +--------+------------+--------+------------+--------------+ | SMITH | 1980-12-17 | FORD | 1981-12-03 | RESEARCH | | ALLEN | 1981-02-20 | BLAKE | 1981-05-01 | SALES | | WARD | 1981-02-22 | BLAKE | 1981-05-01 | SALES | | JONES | 1981-04-02 | KING | 1981-11-17 | RESEARCH | | BLAKE | 1981-05-01 | KING | 1981-11-17 | SALES | | CLARK | 1981-06-09 | KING | 1981-11-17 | ACCOUNTING | | MILLER | 1981-01-23 | CLARK | 1981-06-09 | ACCOUNTING | +--------+------------+--------+------------+--------------+ 7 rows in set (0.00 sec)3.16、 列出部门名称和这些部门的员工信息, 同时列出那些没有员工的部门select e.*, d.dname '部门名称' from dept d left join emp e on d.deptno = e.deptno;mysql> select -> e.*, -> d.dname '部门名称' -> from dept d -> left join emp e on d.deptno = e.deptno; +-------+--------+-----------+------+------------+------+------+--------+--------------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | 部门名称 | +-------+--------+-----------+------+------------+------+------+--------+--------------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | RESEARCH | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | SALES | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | SALES | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 | RESEARCH | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | SALES | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | SALES | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | ACCOUNTING | | 7788 | SCOTT | ANALYST | 7566 | 1987-07-03 | 3000 | NULL | 20 | RESEARCH | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 | ACCOUNTING | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | SALES | | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 | RESEARCH | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | SALES | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | RESEARCH | | 7934 | MILLER | CLERK | 7782 | 1981-01-23 | 1300 | NULL | 10 | ACCOUNTING | | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | OPERATIONS | +-------+--------+-----------+------+------------+------+------+--------+--------------+ 15 rows in set (0.00 sec)3.17、列出至少有 5 个员工的所有部门-- 每个部门的员工数量 select deptno,count(*) from emp group by deptno;mysql> select deptno,count(*) from emp group by deptno; +--------+----------+ | deptno | count(*) | +--------+----------+ | 10 | 3 | | 20 | 5 | | 30 | 6 | +--------+----------+ 3 rows in set (0.00 sec)-- 按照部门编号分组，计数，筛选出 >= 5 select deptno, count(*) from emp group by deptno having count(*) >= 5;mysql> select -> deptno, -> count(*) -> from emp -> group by deptno -> having count(*) >= 5; +--------+----------+ | deptno | count(*) | +--------+----------+ | 20 | 5 | | 30 | 6 | +--------+----------+ 2 rows in set (0.00 sec)3.18、列出薪金比"SMITH" 多的所有员工信息select sal from emp where ename = 'smith';mysql> select sal from emp where ename = 'smith'; +------+ | sal | +------+ | 800 | +------+ 1 row in set (0.00 sec)select ename, sal from emp where sal > (select sal from emp where ename = 'smith');mysql> select -> ename, -> sal -> from emp -> where sal > (select sal from emp where ename = 'smith'); +--------+------+ | ename | sal | +--------+------+ | ALLEN | 1600 | | WARD | 1250 | | JONES | 2975 | | MARTIN | 1250 | | BLAKE | 2850 | | CLARK | 2450 | | SCOTT | 3000 | | KING | 5000 | | TURNER | 1500 | | ADAMS | 1100 | | JAMES | 950 | | FORD | 3000 | | MILLER | 1300 | +--------+------+ 13 rows in set (0.00 sec)3.19、 列出所有"CLERK"( 办事员) 的姓名及其部门名称, 部门的人数select ename,job from emp where job = 'clerk';mysql> select ename,job from emp where job = 'clerk'; +--------+-------+ | ename | job | +--------+-------+ | SMITH | CLERK | | ADAMS | CLERK | | JAMES | CLERK | | MILLER | CLERK | +--------+-------+ 4 rows in set (0.00 sec)select e.ename, e.job, d.dname, d.deptno from emp e join dept d on e.deptno = d.deptno where job = 'clerk';mysql> select -> e.ename, -> e.job, -> d.dname, -> d.deptno -> from emp e -> join dept d on e.deptno = d.deptno -> where job = 'clerk'; +--------+-------+------------+--------+ | ename | job | dname | deptno | +--------+-------+------------+--------+ | SMITH | CLERK | RESEARCH | 20 | | ADAMS | CLERK | RESEARCH | 20 | | JAMES | CLERK | SALES | 30 | | MILLER | CLERK | ACCOUNTING | 10 | +--------+-------+------------+--------+ 4 rows in set (0.00 sec)select deptno,count(*) deptcount from emp group by deptno;mysql> select deptno,count(*) deptcount from emp group by deptno; +--------+-----------+ | deptno | deptcount | +--------+-----------+ | 10 | 3 | | 20 | 5 | | 30 | 6 | +--------+-----------+ 3 rows in set (0.00 sec)select t1.*, t2.deptcount from (select e.ename,e.job,d.dname,d.deptno from emp e join dept d on e.deptno = d.deptno where job = 'clerk') t1 join (select deptno,count(*) deptcount from emp group by deptno) t2 on t1.deptno = t2.deptno;mysql> select -> t1.*, -> t2.deptcount -> from (select e.ename,e.job,d.dname,d.deptno from emp e -> join dept d on e.deptno = d.deptno -> where job = 'clerk') t1 -> join (select deptno,count(*) deptcount from emp group by deptno) t2 on t1.deptno = t2.deptno; +--------+-------+------------+--------+-----------+ | ename | job | dname | deptno | deptcount | +--------+-------+------------+--------+-----------+ | SMITH | CLERK | RESEARCH | 20 | 5 | | ADAMS | CLERK | RESEARCH | 20 | 5 | | JAMES | CLERK | SALES | 30 | 6 | | MILLER | CLERK | ACCOUNTING | 10 | 3 | +--------+-------+------------+--------+-----------+ 4 rows in set (0.00 sec)3.20、列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数,按照工作岗位分组求最小值select job,min(sal) from emp group by job;mysql> select job,min(sal) from emp group by job; +-----------+----------+ | job | min(sal) | +-----------+----------+ | ANALYST | 3000 | | CLERK | 800 | | MANAGER | 2450 | | PRESIDENT | 5000 | | SALESMAN | 1250 | +-----------+----------+ 5 rows in set (0.00 sec)select job,count(*),min(sal) from emp group by job having min(sal) > 1500;mysql> select job,count(*),min(sal) from emp group by job having min(sal) > 1500; +-----------+----------+----------+ | job | count(*) | min(sal) | +-----------+----------+----------+ | ANALYST | 2 | 3000 | | MANAGER | 3 | 2450 | | PRESIDENT | 1 | 5000 | +-----------+----------+----------+ 3 rows in set (0.01 sec)3.21、列出在部门"SALES"< 销售部> 工作的员工的姓名, 假定不知道销售部的部门编号-- 部门编号 select deptno from dept where dname = 'sales';mysql> select deptno from dept where dname = 'sales'; +--------+ | deptno | +--------+ | 30 | +--------+ 1 row in set (0.00 sec)-- 员工姓名 select ename from emp where deptno = (select deptno from dept where dname = 'sales');mysql> select ename from emp where deptno = (select deptno from dept where dname = 'sales'); +--------+ | ename | +--------+ | ALLEN | | WARD | | MARTIN | | BLAKE | | TURNER | | JAMES | +--------+ 6 rows in set (0.00 sec)3.22、列出薪金高于公司平均薪金的所有员工, 所在部门, 上级领导, 雇员的工资等级select avg(sal) avgsal from emp;mysql> select avg(sal) avgsal from emp; +-------------------+ | avgsal | +-------------------+ | 2073.214285714286 | +-------------------+ 1 row in set (0.00 sec)select e1.ename '员工名字', d.dname '所在部门', e2.ename '上级领导', s.grade '工资等级' from emp e1 left join emp e2 on e1.mgr = e2.empno join dept d on e1.deptno = d.deptno join salgrade s on e1.sal between s.losal and s.hisal where e1.sal > (select avg(sal) from emp);mysql> select -> e1.ename '员工名字', -> d.dname '所在部门', -> e2.ename '上级领导', -> s.grade '工资等级' -> from emp e1 -> left join emp e2 on e1.mgr = e2.empno -> join dept d on e1.deptno = d.deptno -> join salgrade s on e1.sal between s.losal and s.hisal -> where e1.sal > (select avg(sal) from emp); +--------------+--------------+--------------+--------------+ | 员工名字 | 所在部门 | 上级领导 | 工资等级 | +--------------+--------------+--------------+--------------+ | JONES | RESEARCH | KING | 4 | | BLAKE | SALES | KING | 4 | | CLARK | ACCOUNTING | KING | 4 | | SCOTT | RESEARCH | JONES | 4 | | KING | ACCOUNTING | NULL | 5 | | FORD | RESEARCH | JONES | 4 | +--------------+--------------+--------------+--------------+ 6 rows in set (0.00 sec)3.23、 列出与"SCOTT" 从事相同工作的所有员工及部门名称select * from emp where ename = 'scott';mysql> select * from emp where ename = 'scott'; +-------+-------+---------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+---------+------+------------+------+------+--------+ | 7788 | SCOTT | ANALYST | 7566 | 1987-07-03 | 3000 | NULL | 20 | +-------+-------+---------+------+------------+------+------+--------+ 1 row in set (0.00 sec)select e.*, d.dname from emp e join dept d on e.deptno = d.deptno where e.job = (select job from emp where ename = 'scott') and e.ename <> 'scott';mysql> select -> e.*, -> d.dname -> from emp e -> join dept d on e.deptno = d.deptno -> where e.job = (select job from emp where ename = 'scott') and e.ename <> 'scott'; +-------+-------+---------+------+------------+------+------+--------+----------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | dname | +-------+-------+---------+------+------------+------+------+--------+----------+ | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | RESEARCH | +-------+-------+---------+------+------------+------+------+--------+----------+ 1 row in set (0.00 sec)3.24、列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金select * from emp where deptno = 30;mysql> select * from emp where deptno = 30; +-------+--------+----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+----------+------+------------+------+------+--------+ | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | +-------+--------+----------+------+------------+------+------+--------+ 6 rows in set (0.00 sec)select distinct sal from emp where deptno = 30;mysql> select distinct sal from emp where deptno = 30; +------+ | sal | +------+ | 1600 | | 1250 | | 2850 | | 1500 | | 950 | +------+ 5 rows in set (0.00 sec)select ename, sal from emp where sal in (select distinct sal from emp where deptno = 30) and deptno <> 30;mysql> select -> ename, -> sal -> from emp -> where sal in (select distinct sal from emp where deptno = 30) -> and deptno <> 30; Empty set (0.00 sec)3.25、列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金、部门名称select max(sal) from emp where deptno = 30;mysql> select max(sal) from emp where deptno = 30; +----------+ | max(sal) | +----------+ | 2850 | +----------+ 1 row in set (0.00 sec)select e.ename, e.sal, d.dname from emp e join dept d on e.deptno = d.deptno where e.sal > (select max(sal) from emp where deptno = 30);mysql> select -> e.ename, -> e.sal, -> d.dname -> from emp e -> join dept d on e.deptno = d.deptno -> where e.sal > (select max(sal) from emp where deptno = 30); +-------+------+------------+ | ename | sal | dname | +-------+------+------------+ | JONES | 2975 | RESEARCH | | SCOTT | 3000 | RESEARCH | | KING | 5000 | ACCOUNTING | | FORD | 3000 | RESEARCH | +-------+------+------------+ 4 rows in set (0.00 sec)3.26、列出在每个部门工作的员工数量, 平均工资和平均服务期限没有员工的部门，部门人数是0select d.deptno, count(e.ename), ifnull(avg(e.sal),0) as avgsal, ifnull(avg(timestampdiff(YEAR, hiredate, now())), 0) as avgservicetime from dept d left join emp e on d.deptno = e.deptno group by d.deptno;mysql> select -> d.deptno, -> count(e.ename), -> ifnull(avg(e.sal),0) as avgsal, -> ifnull(avg(timestampdiff(YEAR, hiredate, now())), 0) as avgservicetime -> from dept d -> left join emp e on d.deptno = e.deptno -> group by d.deptno; +--------+----------------+--------------------+----------------+ | deptno | count(e.ename) | avgsal | avgservicetime | +--------+----------------+--------------------+----------------+ | 10 | 3 | 2916.6666666666665 | 42.3333 | | 20 | 5 | 2175 | 39.8000 | | 30 | 6 | 1566.6666666666667 | 42.0000 | | 40 | 0 | 0 | 0.0000 | +--------+----------------+--------------------+----------------+ 4 rows in set (0.00 sec)3.26、在mysql当中怎么计算两个日期的“年差”，差了多少年【知识点】TimeStampDiff(间隔类型, 前一个日期, 后一个日期) timestampdiff(YEAR, hiredate, now()) 间隔类型： SECOND 秒， MINUTE 分钟， HOUR 小时， DAY 天， WEEK 星期 MONTH 月， QUARTER 季度， YEAR 年3.27、 列出所有员工的姓名、部门名称和工资select e.ename, d.dname, e.sal from emp e join dept d on e.deptno = d.deptno order by e.sal desc;mysql> select -> e.ename, -> d.dname, -> e.sal -> from emp e -> join dept d on e.deptno = d.deptno -> order by e.sal desc; +--------+------------+------+ | ename | dname | sal | +--------+------------+------+ | KING | ACCOUNTING | 5000 | | SCOTT | RESEARCH | 3000 | | FORD | RESEARCH | 3000 | | JONES | RESEARCH | 2975 | | BLAKE | SALES | 2850 | | CLARK | ACCOUNTING | 2450 | | ALLEN | SALES | 1600 | | TURNER | SALES | 1500 | | MILLER | ACCOUNTING | 1300 | | MARTIN | SALES | 1250 | | WARD | SALES | 1250 | | ADAMS | RESEARCH | 1100 | | JAMES | SALES | 950 | | SMITH | RESEARCH | 800 | +--------+------------+------+ 14 rows in set (0.00 sec)3.28、列出所有部门的详细信息和人数select d.*, count(e.ename) from dept d left join emp e on d.deptno = e.deptno group by d.deptno;mysql> select -> d.*, -> count(e.ename) -> from dept d -> left join emp e on d.deptno = e.deptno -> group by d.deptno; +--------+------------+----------+----------------+ | deptno | dname | loc | count(e.ename) | +--------+------------+----------+----------------+ | 10 | ACCOUNTING | NEW YORK | 3 | | 20 | RESEARCH | DALLAS | 5 | | 30 | SALES | CHICAGO | 6 | | 40 | OPERATIONS | BOSTON | 0 | +--------+------------+----------+----------------+ 4 rows in set (0.00 sec)3.29、列出各种工作的最低工资及从事此工作的雇员姓名select job,min(sal) from emp group by job;mysql> select job,min(sal) from emp group by job; +-----------+----------+ | job | min(sal) | +-----------+----------+ | ANALYST | 3000 | | CLERK | 800 | | MANAGER | 2450 | | PRESIDENT | 5000 | | SALESMAN | 1250 | +-----------+----------+ 5 rows in set (0.00 sec)select e.ename, t.job, t.minsal from emp e join (select job,min(sal) minsal from emp group by job) t on e.job = t.job and e.sal = t.minsal;mysql> select -> e.ename, -> t.job, -> t.minsal -> from emp e -> join (select job,min(sal) minsal from emp group by job) t -> on e.job = t.job and e.sal = t.minsal; +--------+-----------+--------+ | ename | job | minsal | +--------+-----------+--------+ | SMITH | CLERK | 800 | | WARD | SALESMAN | 1250 | | MARTIN | SALESMAN | 1250 | | CLARK | MANAGER | 2450 | | SCOTT | ANALYST | 3000 | | KING | PRESIDENT | 5000 | | FORD | ANALYST | 3000 | +--------+-----------+--------+ 7 rows in set (0.00 sec)3.30、列出各个部门的 MANAGER( 领导) 的最低薪金select deptno, min(sal) from emp where job = 'manager' group by deptno;mysql> select -> deptno, -> min(sal) -> from emp -> where job = 'manager' -> group by deptno; +--------+----------+ | deptno | min(sal) | +--------+----------+ | 10 | 2450 | | 20 | 2975 | | 30 | 2850 | +--------+----------+ 3 rows in set (0.00 sec)3.31、列出所有员工的 年工资, 按 年薪从低到高排序select ename, (sal + ifnull(comm,0) ) * 12 yearsal from emp order by yearsal asc;mysql> select -> ename, -> (sal + ifnull(comm,0) ) * 12 yearsal -> from emp -> order by yearsal asc; +--------+---------+ | ename | yearsal | +--------+---------+ | SMITH | 9600 | | JAMES | 11400 | | ADAMS | 13200 | | MILLER | 15600 | | TURNER | 18000 | | WARD | 21000 | | ALLEN | 22800 | | CLARK | 29400 | | MARTIN | 31800 | | BLAKE | 34200 | | JONES | 35700 | | SCOTT | 36000 | | FORD | 36000 | | KING | 60000 | +--------+---------+ 14 rows in set (0.01 sec)3.32、求出员工领导的薪水超过3000的员工名称与领导select e1.ename '员工', e1.sal '员工薪水', e2.ename '领导', e2.sal '领导薪水' from emp e1 join emp e2 on e1.mgr = e2.empno where e2.sal > 3000;mysql> select -> e1.ename '员工', -> e1.sal '员工薪水', -> e2.ename '领导', -> e2.sal '领导薪水' -> from emp e1 -> join emp e2 on e1.mgr = e2.empno -> where e2.sal > 3000; +--------+--------------+--------+--------------+ | 员工 | 员工薪水 | 领导 | 领导薪水 | +--------+--------------+--------+--------------+ | JONES | 2975 | KING | 5000 | | BLAKE | 2850 | KING | 5000 | | CLARK | 2450 | KING | 5000 | +--------+--------------+--------+--------------+ 3 rows in set (0.00 sec)3.33、求出部门名称中, 带'S'字符的部门员工的工资合计、部门人数select d.*, count(e.ename), ifnull(sum(e.sal),0) sumsal from dept d left join emp e on d.deptno = e.deptno where d.dname like '%s%' group by d.deptno;mysql> select -> d.*, -> count(e.ename), -> ifnull(sum(e.sal),0) sumsal -> from dept d -> left join emp e on d.deptno = e.deptno -> where d.dname like '%s%' -> group by d.deptno; +--------+------------+---------+----------------+--------+ | deptno | dname | loc | count(e.ename) | sumsal | +--------+------------+---------+----------------+--------+ | 20 | RESEARCH | DALLAS | 5 | 10875 | | 30 | SALES | CHICAGO | 6 | 9400 | | 40 | OPERATIONS | BOSTON | 0 | 0 | +--------+------------+---------+----------------+--------+ 3 rows in set (0.00 sec)3.34、给任职日期超过 30 年的员工加薪 10%-- 复制一张表 create table emp_bak as select * from emp;mysql> create table emp_bak as select * from emp; Query OK, 14 rows affected (0.01 sec) Records: 14 Duplicates: 0 Warnings: 0 mysql> select * from emp_bak; +-------+--------+-----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+-----------+------+------------+------+------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-07-03 | 3000 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1981-01-23 | 1300 | NULL | 10 | +-------+--------+-----------+------+------------+------+------+--------+ 14 rows in set (0.00 sec)-- 更新表 update emp set sal = sal * 1.1 where timestampdiff(YEAR, hiredate, now()) > 30;mysql> update emp set sal = sal * 1.1 where timestampdiff(YEAR, hiredate, now()) > 30; Query OK, 14 rows affected (0.01 sec) Rows matched: 14 Changed: 14 Warnings: 0 mysql> select * from emp; +-------+--------+-----------+------+------------+--------------------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+-----------+------+------------+--------------------+------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 880.0000000000001 | NULL | 20 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1760.0000000000002 | 300 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1375 | 500 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 3272.5000000000005 | NULL | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1375 | 1400 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 3135.0000000000005 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2695 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-07-03 | 3300.0000000000005 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5500 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1650.0000000000002 | 0 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1210 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 1045 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3300.0000000000005 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1981-01-23 | 1430.0000000000002 | NULL | 10 | +-------+--------+-----------+------+------------+--------------------+------+--------+ 14 rows in set (0.00 sec)endl