MySQL知识总结

MySQL5.7与MySQL8.0的区别

1. 的是MySQL8.0的性能相比于MySQL5.7获得了很大的提升

2. MySQL8.0与MySQL5.7使用加密算法不同,MySQL8.0的加密算法是ssh-256,MySQL5.7用的是ssh-1

3. 新引用了原生数据字典

MySQL的约束

在 MySQL 里，约束用于规定表中数据的规则，保障数据的准确性和一致性。以下为你详细介绍 MySQL 中常见的约束类型及其使用方法。

1. 主键约束（PRIMARY KEY）

主键约束用于唯一标识表中的每一行记录，一个表只能有一个主键，且主键列的值不能为 NULL，也不能重复。

创建逐渐约束(primary key)

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

复合主键

如果需要使用多个列来唯一标识记录，可以定义复合主键。

CREATE TABLE orders (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);
-- 这里 order_id 和 product_id 组合起来构成复合主键。

2. 唯一约束（UNIQUE）

唯一约束确保列中的值是唯一的，但可以为 NULL。一个表可以有多个唯一约束。

创建表时定义唯一约束

CREATE TABLE employees (
    employee_id INT UNIQUE,
    email VARCHAR(100) UNIQUE,
    name VARCHAR(50)
);
-- 在这个表中，employee_id 和 email 列的值都必须是唯一的。

修改表时添加唯一约束

alter table employees add unique (phone_number);

3. 非空约束（NOT NULL）

非空约束规定列的值不能为 NULL。

创建表时定义非空约束

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    address VARCHAR(200)
);
-- 在这个表中，name 列的值不能为 NULL。

修改表时添加非空约束

ALTER TABLE customers MODIFY COLUMN phone_number VARCHAR(20) NOT NULL;

4. 外键约束（FOREIGN KEY）

外键约束用于建立两个表之间的关联关系，确保一个表中的数据引用另一个表中存在的数据。

创建表时定义外键约束

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
-- 在这个例子中，orders 表的 customer_id 列是外键，它引用了 customers 表的 customer_id 列。

修改表时添加外键约束

ALTER TABLE order_items ADD FOREIGN KEY (product_id) REFERENCES products(product_id);

5. 检查约束（CHECK）

检查约束用于限制列中的值必须满足指定的条件。不过，MySQL 从 8.0.16 版本开始才对 CHECK 约束提供原生支持，之前的版本中 CHECK 约束会被解析但不会生效。

创建表时定义检查约束

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    age INT,
    CHECK (age >= 18)
);
-- 在这个表中，age 列的值必须大于等于 18。

修改表时添加检查约束

ALTER TABLE products ADD CHECK (price > 0);

这会为 products 表的 price 列添加检查约束，确保价格大于 0。

-- 创建 students 表，包含主键约束
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

-- 创建 employees 表，包含唯一约束和非空约束
CREATE TABLE employees (
    employee_id INT UNIQUE,
    email VARCHAR(100) UNIQUE,
    name VARCHAR(50) NOT NULL
);

-- 创建 orders 表，包含外键约束
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- 创建 employees 表，包含检查约束
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    age INT,
    CHECK (age >= 18)
);    

MySQL的rpm包一键部署

#!/bin/bash
#
# 自动部署MySQL
yum remove -y mariadb-libs
yum install -y net-tools
yum install -y perl
cd /tmp

rpm -ivh mysql-community-common-5.7.40-1.el7.x86_64.rpm mysql-community-libs-5.7.40-1.el7.x86_64.rpm mysql-community-libs-compat-5.7.40-1.el7.x86_64.rpm mysql-community-client-5.7.40-1.el7.x86_64.rpm mysql-community-server-5.7.40-1.el7.x86_64.rpm 

#创建MySQL日志文件位置
mkdir /var/log/mysqld
chown mysql:mysql /var/log/mysqld
chmod 740 /var/log/mysqld

echo "开始配置MySQL文件..."
cat > /etc/my.cnf << EOF
[mysqld]
user=mysql
port=3306
symbolic-links=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#pid-file=/usr/local/mysql5.7/mysqld.pid
max_connections=300
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
skip-name-resolve
server-id=$(echo $(ip a | grep ens33 | awk -F"." '{print $4}' | cut -c1-2))

general_log=ON
log_output=file
general_log_file=/var/log/mysqld/general.log

slow_query_log=ON
log_output=file
slow_query_log_file=/var/log/mysqld/slow.log
long_query_time=3.000000

log_bin = /var/log/mysqld/mysql-bin
binlog_format=ROW
expire_logs_days = 2
EOF
echo "配置文件写入完成..."

echo "启动数据库..."
systemctl start mysqld

# 获取 MySQL 初始密码
tmp_password=$(grep 'temporary password' /var/log/mysqld.log | awk '{print $NF}')
echo "MySQL 初始密码: $tmp_password"

# 修改 root 密码
mysql -uroot -p"${tmp_password}" --connect-expired-password <<EOF
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Jnutter12_';
FLUSH PRIVILEGES;
EOF

if [ $? -eq 1 ]; then
    echo "初始化数据库失败..."
    exit 1
fi

echo "MySQL 部署完成!"
echo "MySQL开机自启....."
systemctl start mysqld
systemctl enable mysqld

MySQL源码安装

一. 环境准备

1. 先配置MySQL的yum源,方便后面安装MySQL的依赖

# 基础工具
yum install -y cmake  gcc-c++  openssl-devel  ncurses-devel (教案)
yum -y install wget cmake gcc gcc-c++ make ncurses-devel bison perl

# 加密支持（可选但推荐）
yum -y install openssl-devel

# 压缩支持（ZSTD用于日志压缩）
yum -y install zstd-devel

1. 创建 MySQL 用户和目录

groupadd -r mysql
useradd -r -g mysql -s /sbin/nologin mysql
mkdir -p /usr/local/mysql/{data,log,tmp}
chown -R mysql:mysql /usr/local/mysql

二. 下载并解压源码

官网链接:https://downloads.mysql.com/archives/community/

# 下载MySQL源码（替换为最新版本）
wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.33.tar.gz

# 解压并进入目录
tar -zxvf mysql-8.0.33.tar.gz
cd mysql-8.0.33

三. 编译配置

cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \        # 安装路径
-DMYSQL_DATADIR=/usr/local/mysql/data \            # 数据目录
-DSYSCONFDIR=/etc \                               # 配置文件目录（生成my.cnf）
-DMYSQL_TCP_PORT=3306 \                            # 端口
-DMYSQL_UNIX_ADDR=/usr/local/mysql/tmp/mysql.sock \ # Socket文件路径
-DWITH_INNOBASE_STORAGE_ENGINE=1 \                 # 启用InnoDB引擎
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DENABLED_LOCAL_INFILE=1 \                          # 启用本地文件导入
-DDEFAULT_CHARSET=utf8mb4 \                        # 默认字符集
-DDEFAULT_COLLATION=utf8mb4_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DWITH_BOOST=boost/boost_1_79_0 \                  # Boost库（MySQL 8+需要）
-DWITH_SSL=system \                                # 启用SSL
-DWITH_ZSTD=1 \                                    # 启用ZSTD压缩（提升日志性能）
-DMYSQL_USER=mysql \                               # 运行用户
-DENABLE_DOWNLOADS=1 \
-DCMAKE_CXX_FLAGS="-O2 -g" \                        # 编译优化选项
-DMYSQL_SERVER_SUFFIX=""

• 若提示boost相关错误，需先下载 Boost 库并解压到当前目录（如上述-DWITH_BOOST路径）

• 生产环境建议添加-DMYSQL_MAINTAINER_MODE=1开启调试模式

四. 编译与安装

#编译
make -j4 (可以根据cpu核心数加速,如当前为4核)

#安装(需要root权限)
sudo make install

五. 初始化数据库

# 切换到MySQL用户
su - mysql

# 初始化数据目录（生成初始密码）
/usr/local/mysql/bin/mysqld \
--initialize-insecure \               # 不生成随机密码（生产环境用--initialize）
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data

重要：

• --initialize

  会生成随机 root 密码，需通过日志查找：

  grep '临时密码' /usr/local/mysql/log/mysqld.log
  
  #就是
  
  tail /usr/local/mysql/log/mysqld.log | grep password

• --initialize-insecure用于测试环境，初始密码为空

六. 配置系统服务

1. 复制配置文件

# 生成my.cnf（根据需求修改）
cp support-files/my-default.cnf /etc/my.cnf
vim /etc/my.cnf  # 添加以下内容（示例）：

注意事项

1. Boost 库依赖：

   • MySQL 8.0 + 必须使用 Boost 1.59+，建议通过官网下载对应版本

   • 解压路径需与-DWITH_BOOST参数一致（如上述boost/boost_1_79_0）

2. 编译时间：

   • 编译过程可能持续 10-30 分钟，取决于服务器性能，建议使用-jN（N 为 CPU 核心数）加速

3. 生产环境优化：

   • 添加innodb_buffer_pool_size（建议为物理内存的 50-70%）

   • 启用binlog（log_bin=mysql-bin.log）和gtid_mode=ON（如需主从复制）

4. 防火墙设置：

firewall-cmd --add-port=3306/tcp --permanent
firewall-cmd --reload

MySQL的二进制安装

官网链接:https://downloads.mysql.com/archives/community/

1.解压二进制安装包

tar -xf xxxxxxxxxxxx -C /usr/local/
#解压到/usr/local下

2.编写配置文件

[mysql]
port=3306
socket=/var/lib/mysql/mysql.sock

[mysqld]
user=mysql
port=3306
symbolic-links=0
basedir=/usr/local/mysql5.7
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
skip-name-resolve

3.创建用户和文件,目录,授权

groupadd  -g27 mysql
useradd  -u27 -g27 -M -d/var/lib/mysql -s/bin/false  mysql
mkdir  /var/lib/mysql
mkdir  /var/log/mysqld
mkdir  /var/run/mysqld
chown mysql:mysql   /var/lib/mysql/
chown mysql:mysql   /var/log/mysqld/
chown mysql:mysql   /var/run/mysqld/

4.编写tmpfiles

vim /usr/lib/tmpfiles.d/mysql.conf

d  /var/run/mysqld  0755  mysql  mysql  -

5.发布mysql bin目录 PATH环境

vim /etc/profile.d/mysql.sh

#! /bin/bash
export  PATH=${PATH}:/usr/local/mysql5.7/bin

#刷新
source /etc/profile

6.编写systemd服务脚本

cp /usr/local/mysql5.7/support-files/mysql.server   /etc/init.d/mysqld

7.初始化数据库

/usr/local/mysql5.7/bin/mysqld --defaults-file=/etc/my.cnf --initialize   --user=mysql --basedir=/usr/local/mysql5.7  --datadir=/var/lib/mysql

#无密码初始化 --initialize-insecure

/usr/local/mysql5.7/bin/mysqld --defaults-file=/etc/my.cnf --initialize-insecure --user=mysql --basedir=/usr/local/mysql5.7 --datadir=/var/lib/mysql

启动MySQL

/etc/init.d/mysqld [start|stop|restart|reload|force-reload|status]

8.在开机启动时自动建立目录

#第一种(推荐)
vim /etc/my.cnf
pid-file=/usr/local/mysql5.7/mysqld.pid

#第二种方式
#在/usr/lib/tmpfiles.d或者/etc/tmpfiles.d/ 创建子配置文件
vim /usr/lib/tmpfile.d/mysql.conf
#d目录  /var/run/mysql  权限   用户    组

d /var/run/mysql 0755 mysql mysql -

MySQL二进制一键安装脚本

#!/bin/bash

# 检查是否以 root 权限运行
if [ "$EUID" -ne 0 ]; then
    echo "请以 root 权限运行此脚本。"
    exit 1
fi

# 解压 MySQL 安装包
tar -zxvf /tmp/mysql-5.7.40-linux-glibc2.12-x86_64.tar.gz -C /usr/local/

# 重命名 MySQL 目录
mv /usr/local/mysql-5.7.40-linux-glibc2.12-x86_64 /usr/local/mysql5.7

# 写入 my.cnf 配置文件
cat > /etc/my.cnf << EOF
[mysql]
port=3306
socket=/var/lib/mysql/mysql.sock

[mysqld]
user=mysql
port=3306
symbolic-links=0
basedir=/usr/local/mysql5.7
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
skip-name-resolve
EOF

# 创建用户和目录
groupadd -g27 mysql
useradd -u27 -g27 -M -d/var/lib/mysql -s/bin/false mysql
mkdir -p /var/lib/mysql
mkdir -p /var/log/mysqld
mkdir -p /var/run/mysqld
chown mysql:mysql /var/lib/mysql/
chown mysql:mysql /var/log/mysqld/
chown mysql:mysql /var/run/mysqld/

# 写入 tmpfiles 配置
cat << EOF > /usr/lib/tmpfiles.d/mysql.conf
d  /var/run/mysqld  0755  mysql  mysql  -
EOF

# 写入 PATH 配置
cat << EOF > /etc/profile.d/mysql.sh
#!/bin/bash
export PATH=\${PATH}:/usr/local/mysql5.7/bin
EOF

# 使环境变量生效
source /etc/profile

# 复制 systemd 服务文件
cp /usr/local/mysql5.7/support-files/mysql.server /etc/init.d/mysqld

# 初始化 MySQL
/usr/local/mysql5.7/bin/mysqld --defaults-file=/etc/my.cnf --initialize-insecure --user=mysql --basedir=/usr/local/mysql5.7 --datadir=/var/lib/mysql

# 启动 MySQL
/etc/init.d/mysqld start

MySQL二进制一键安装的剧本

---
- name: deployMysql
  hosts: mysql
  tasks:
    - name: copy file
      unarchive:
        src: /tmp/mysql-5.7.40-linux-glibc2.12-x86_64.tar.gz
        dest: /usr/local/

    - name: rename mysql file
      shell: mv mysql-5.7.40-linux-glibc2.12-x86_64 mysql5.7
      args:
        chdir: /usr/local/

    - name: write my.cnf
      copy:
        dest: /etc/my.cnf
        content: |
          [mysql]
          port=3306
          socket=/var/lib/mysql/mysql.sock

          [mysqld]
          user=mysql
          port=3306
          symbolic-links=0
          basedir=/usr/local/mysql5.7
          datadir=/var/lib/mysql
          socket=/var/lib/mysql/mysql.sock
          log-error=/var/log/mysqld/mysqld.log
          pid-file=/var/run/mysqld/mysqld.pid
          character-set-server=utf8mb4
          collation-server=utf8mb4_general_ci
          skip-name-resolve

    - name: create user
      shell: |
        groupadd  -g27 mysql
        useradd  -u27 -g27 -M -d/var/lib/mysql -s/bin/false  mysql
        mkdir  /var/lib/mysql
        mkdir  /var/log/mysqld
        mkdir  /var/run/mysqld
        chown mysql:mysql   /var/lib/mysql/
        chown mysql:mysql   /var/log/mysqld/
        chown mysql:mysql   /var/run/mysqld/

    - name: write tmpfiles
      copy:
        dest: /usr/lib/tmpfiles.d/mysql.conf
        content: |
          d  /var/run/mysqld  0755  mysql  mysql  -

    - name: write PATH
      copy:
        dest: /etc/profile.d/mysql.sh
        content: |
          #! /bin/bash
          export  PATH=${PATH}:/usr/local/mysql5.7/bin

    - name: source path
      shell: source /etc/profile

    - name: cp systemd
      shell: cp /usr/local/mysql5.7/support-files/mysql.server   /etc/init.d/mysqld

    - name: initialize mysql
      shell: /usr/local/mysql5.7/bin/mysqld --defaults-file=/etc/my.cnf --initialize-insecure --user=mysql --basedir=/usr/local/mysql5.7 --datadir=/var/lib/mysql

    - name: start mysql
      shell: /etc/init.d/mysqld start

my.cnf默认配置

[mysqld]
# 基础配置
datadir= /var/lib/mysql          # 数据文件存储路径
socket= /var/lib/mysql/mysql.sock  # 本地套接字文件
pid-file= /var/run/mysqld/mysqld.pid  # PID文件
user= mysql                  # 运行用户
port= 3306                   # 端口号
default-storage-engine= InnoDB            # 默认存储引擎（5.5+默认InnoDB）
character-set-server = utf8             # 默认字符集（部分版本为utf8mb4，如8.0）
collation-server= utf8_general_ci        # 字符集排序规则

# 连接与性能（基础默认值，适合小规模应用）
max_connections= 151                    # 最大连接数
table_open_cache= 4096                   # 表缓存数量
thread_cache_size= 50                     # 线程缓存大小（5.7默认值，8.0可能不同）
query_cache_type= 1                      # 查询缓存开关（5.7默认启用，8.0已移除）
query_cache_size= 128M                   # 查询缓存大小（8.0无此配置）

# InnoDB 存储引擎配置（默认存储引擎）
innodb_buffer_pool_size= 128M                   # InnoDB缓冲池大小（小内存默认值）
innodb_log_file_size= 50M                    # Redo日志文件大小
innodb_log_buffer_size= 8M                     # 日志缓冲大小
innodb_flush_log_at_trx_commit= 1           # 事务提交时同步日志（强一致性）
innodb_file_per_table= 1                      # 表数据单独存储（默认启用）

# 日志配置（部分默认注释，需手动启用）
# log_bin= /var/log/mysql/mysql-bin.log  # 二进制日志（默认注释）
# server_id= 1                          # 主从复制服务器ID（默认注释）
slow_query_log= 0                        # 慢查询日志（默认禁用，5.7可能默认启用）
# slow_query_log_file= /var/log/mysql/slow.log     # 慢查询日志路径（默认注释）

[mysqld_safe]
log-error= /var/log/mysqld/error.log  # 错误日志路径
socket= /var/lib/mysql/mysql.sock  # 套接字文件

[client]
default-character-set= utf8                   # 客户端默认字符集

[mysql]
default-character-set= utf8                   # mysql命令行客户端字符集

PS:查看进程端口是否存在 netstat -nlpt

1. 启动MySQL,第一次启动会自动生成默认数据文件/var/lib/mysql

如果配置失败,需要删除这些文件才能配置

1. 初始化数据库,设置新密码

   两种方法;第一种
   先查看刚刚上面配置的日志文件log_error,可以查看到临时密码
   然后用临时密码登录MySQL然后,再使用
   alter user 'root'@'localhost' identified by "密码";
   
   第二种:更安全的方式
   通过MySQL自带的mysql_secure_installation命令
   
   
   重置密码的方法:
   mysqladmin -uroot -p原密码 password新密码

2. 因为我们的pid可以选择放在run目录下,而run目录又是一个临时的目录,所以我们要在

   /etc/tmpfiles.d 创建一个配置文件,能在开机的时候自动创建这个目录

   这就能实现我们MySQL的开机自启,当然我们也可以将我们的pid文件夹放在我们的MySQL目录下,就不用解决这个问题

   vim /etc/tmpfiles.d
   
   d /var/run/mysqld 0755 mysql msyql -

MySQL的架构

请简述MySQL的架构

MySQL是介于应用与数据之间的数据库软件,通过中间层设计管理数据

它采用的分层架构,Server层和存储引擎层解耦,通过函数接口交互

Server层的功能:

连接管理:处理网络连接

分析器:检查语法错误

优化器:选择索引并生成执行计划(优化我们的sql语句)

执行器:调用存储引擎接口执行操作

存储引擎层(InnoDB)

具体执行我们的sql命令,实现我们的数据存储

内存结构:

buffer pool:缓存数据页,减少磁盘io

自适应哈希索引:加快搜索节点的速度

change buffer:暂存辅助索引的更新,延迟合并到磁盘

redo log buffer:事务提交前存储redo日志

undo log:记录数据旧版本,支持事务回滚

磁盘结构:

.idb文件:存储表

redo_log:存储事务操作,防止崩溃修复,保证数据一致性

undo_log:用于回滚事务,存储历史数据版本

数据页:拆分大表数据,加速部分读写

索引与b+树

主键索引

数据按照主键排序,形成b+树结构,快速定位目标页

事务与日志机制

redo log:记录事务中所有数据变更,保证原子性和持久性

事务提交时刷盘,崩溃后通过重放恢复数据

bing log:用于灾难恢复,记录所有的

历史变更操作,与redo log的区别,bing log是逻辑日志,redo log是物理日志

undo log:存储数据旧版本,用于回滚错误

数据读写的流程

查询操作:

1. 检查buffer pool是否命中,如果命中,直接返回所需数据,若没有命中

   则从磁盘加载数据页到buffer pool,如果这个数据是热点数据的话,还要加到自适应索引中

更新操作

1. 更新buffer pool中的数据页,生成redo log,和undo log

   辅助索引更新暂存到change buffer,延迟合并到磁盘

infromation-shchem库解析

基本概念

information_schema 是 MySQL 中的一个特殊数据库，它并不存储实际的数据，而是存储了 MySQL 服务器的元数据信息。这些元数据信息描述了数据库的结构、表、列、索引、用户权限等，为数据库管理员和开发者提供了一种便捷的方式来查询和了解数据库的内部结构。

主要用途

• 数据库结构查询：可以查询数据库中所有表、视图、列等的定义信息，方便了解数据库的架构。

• 权限管理：查看用户的权限信息，有助于进行权限审计和管理。

• 性能优化：通过查询索引信息、表统计信息等，为数据库的性能优化提供依据。

• 自动化脚本编写：在编写自动化脚本时，可以利用 information_schema 中的信息来动态生成 SQL 语句。

重要表及用途

SCHEMATA 表(schemata)

存储了所有数据库的信息，包括数据库名称、默认字符集等。

SELECT * FROM information_schema.SCHEMATA;

通过这个查询可以获取所有数据库的基本信息。

TABLES 表(tables)

记录了数据库中所有表和视图的信息，如所属数据库、表名、表类型（基表或视图）、行数等。

SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_database_name';

该查询可以获取指定数据库中所有表和视图的详细信息。

COLUMNS 表(columns)

包含了数据库中所有列的信息，如列名、所属表、数据类型、是否可为空等。

SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

此查询可以获取指定表中所有列的详细信息。

STATISTICS 表(statistics)

提供了关于表索引的信息，包括索引名称、索引类型、索引列等。

SELECT * FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

该查询可以获指定表的所有索引信息。

USER_PRIVILEGES 表(user_privileges)

存储了用户的全局权限信息。

SELECT * FROM information_schema.USER_PRIVILEGES WHERE GRANTEE = "'your_user'@'your_host'";

此查询可以获取指定用户的全局权限信息。

注意事项

• 只读访问：information_schema 是只读的，不能对其中的数据进行插入、更新或删除操作。

• 性能影响：某些查询 information_schema 的操作可能会对性能产生一定影响，尤其是在大型数据库中，因为这些查询可能需要扫描大量的元数据信息。

• 版本差异：不同版本的 MySQL 中，information_schema 的表结构和内容可能会有所不同，在使用时需要注意版本兼容性。

sql语句

DDL

1. create 语句

create 语句 create database [库名]; 创建库

create table [表名]; 创建表	

#复制表的结构
CREATE TABLE new_table LIKE old_table;

#复制表的数据
insert into new_table select * from other_table;

#复制表结构和数据
CREATE TBALE new_table AS SELECT * FROM orginal_table;

#创建索引
CREATE INDEX index_name ON table_name(column1,column2,...);
create table t1(
	column1 datatype;
    column2 datatype;
    index index_name(column1,column2)
);

#对users表的last_name字段建立索引名为idx_lastname
CREATE INDEX idx_lastname ON users(last_name);


#创建视图
CREATE VIEW view_name AS
SELECT id,name
FROM stu
WHERE id > 20;
#查询视图
SELECT * FROM view_name;

#创建用户
create user 'admin'@'%' indentified by 'password';

用户权限密码操作

create user 'it01'@'%' indentified by 'password';
grant all privileges on mydb.* to indentified by 'password';
-- 授权用户 'it02'@'%'  在 mydb库中的 * 所有表有所有权限
FLUSH PRIVILEGES;

grant update,insert,delete  on mysql.user  to  'it02'@'%';
-- 对已经存在的 it02@% 账号 增加 mysql.user 表的授权
FLUSH PRIVILEGES;

grant select on  mydb.*  to  'it03'@'%';
-- 授权 it03@% 用户在 mydb.* 的权限
FLUSH PRIVILEGES;
flush privileges;

revoke insert,update,delete on  *.* from 'it04'@'%';
-- 移除 用户 it04@% 的 insert update delete 权限

revoke grant option on  *.* from 'it04'@'%';
-- 移除用户 it04@% 的 为他人授权的 grant 的权限

权限存储与加载原理

mysql 有 user级、库级、表级权限，分别存在 myslq.user、mysql.db、mysql.tables_priv表中，启动时分别加载到 information_schema.USER_PRIVILEGES、information_schema.SCHEMA_PRIVILEGES、information_schema.TABLE_PRIVILEGES 便于给所有用户 高效权限验证。

drop user jack;

drop user 'jack'@'192.168.50.%';

delete from mysql.user where user = 'test' and host = '%';
-- 删除用户

update mysql.user set  authentication_string=password("Uplooking_123")
where user='test' and host='%';
flush privileges;

alter user 'it04'@'%' indentified by 'password';
flush privileges;

注意事项

如果在修改已有用户权限时使用了 IDENTIFIED BY 子句，这实际上会修改该用户的密码。例如：

GRANT SELECT ON mydb.table1 TO 'it02'@'%' IDENTIFIED BY 'NewPassword';

上面这个语句除了给 'it02'@'%' 用户授予在 mydb.table1 表上的 SELECT 权限外，还会将该用户的密码修改为 NewPassword。

通过系统mysqladmin命令重置密码

mysqladmin  -uroot   -pold_passwd   password [new-password]

忘记了root密码无法登录数据库

systemctl stop mysqld
#停止服务
mysqld --skip -grant-tables &

mysql -uroot

进入mysql

flush privileges;
alter user 'root'@'localhost' indentified by 'newpassword';
flush privileges;
quit;

pkill -9 mysql
systemctl start mysqld
mysql -uroot -pnewpassword

2. ALTER 语句

#ALTER 修改表名
ALTER TABLE oldname RENAME TO newname;

#增加字段
ALTER TABLE tb1 ADD email VARCHAR(30);

#修改字段类型
ALTER TABLE key ALTER COLUMN;
ALTER TABLE tb1 MODIFY COLUMN email char(30);

#ALTER修改字段默认值
ALTER TABLE tb1 ALTER COLUMN email SET DEFAULT 'aaa@111.com'

#ALTER修改字段集
ALTER TABLE tb1 CONVERT TO CHARACTER SET utf8mb4;

#ALTER修改用户密码
alter user 'it03'@'localhost' identified by 'Uplooking_123';

3. dorp 语句

#DROP 删除database
drop database mysql;

#drop 删除表
drop table tb1;

#drop 删除索引
drop index tb1;

#drop 删除视图
drop view myview;

#drop 删除用户
drop user 'jack'@'localhost';

#truncate 清空表数据
truncate table table_name

DML

#insert 插入数据
insert into table(字段1, 字段2) values(......);

insert into table values(......);

#update
update table_name set 字段值 = 值
where 条件

DQL

DQL执行顺序

1. FROM 子句：首先执行 FROM 子句，确定要从哪些表或视图中检索数据。它会将多个表进行关联操作，生成一个虚拟的结果集。

2. WHERE 子句：在 FROM 子句生成的结果集中，使用 WHERE 子句筛选出满足指定条件的行。不符合条件的行将被排除在结果集之外。

3. GROUP BY 子句：根据 GROUP BY 子句中指定的列对数据进行分组。将具有相同值的行划分为同一个组，以便后续对每个组进行聚合操作。

4. HAVING 子句：HAVING 子句用于在分组后对组进行筛选。它基于聚合函数的结果或分组列的条件，筛选出满足特定条件的组。只有满足 HAVING 条件的组才会包含在最终结果集中。

5. SELECT 子句：在经过前面的筛选和分组操作后，SELECT 子句用于选择要返回的列。它可以选择原始表中的列，也可以通过表达式、函数等对列进行计算和转换，生成新的列。

6. DISTINCT 关键字：如果使用了 DISTINCT 关键字，它会在 SELECT 子句返回的结果集中去除重复的行，确保结果集中的每一行都是唯一的。

7. ORDER BY 子句：根据 ORDER BY 子句中指定的列对结果集进行排序。可以按照升序（ASC）或降序（DESC）排列，如果不指定排序方式，默认是升序。

8. LIMIT 子句：最后，LIMIT 子句用于限制结果集返回的行数。它可以指定要返回的行数范围，常用于分页查询或获取指定数量的记录。

SELECT DISTINCT column1, column2, COUNT(*) AS count
FROM table_name
WHERE condition
GROUP BY column1, column2
HAVING COUNT(*) > 10
ORDER BY column1 DESC
LIMIT 100;

首先执行 FROM 子句确定数据源，然后 WHERE 子句筛选行，接着 GROUP BY 子句分组，HAVING 子句筛选组，SELECT 子句选择列并进行聚合计算，DISTINCT 去除重复行，ORDER BY 对结果排序，最后 LIMIT 限制返回 100 行。

DCL

DLC(数据控制语言): grant ,revoke,commit,bollback等

1. GRANT 语句

此语句的作用是给用户或角色授予特定的权限。

-- 给用户 'test_user' 授予对 'orders' 表的 SELECT 权限
GRANT SELECT ON orders TO 'test_user'@'localhost';

-- 给用户 'test_user' 授予对 'customers' 表的 SELECT、INSERT、UPDATE、DELETE 权限
GRANT SELECT, INSERT, UPDATE, DELETE ON customers TO 'test_user'@'localhost';

-- 授予用户创建数据库的权限
GRANT CREATE ON *.* TO 'test_user'@'localhost';

2. REVOKE 语句

该语句用于撤销用户或角色已有的特定权限。

-- 撤销用户 'test_user' 对 'orders' 表的 SELECT 权限
REVOKE SELECT ON orders FROM 'test_user'@'localhost';

-- 撤销用户 'test_user' 对 'customers' 表的所有权限
REVOKE ALL PRIVILEGES ON customers FROM 'test_user'@'localhost';

事务控制

事务控制可确保数据库操作的原子性(A)、一致性(C)、隔离性(I)和持久性(D)（ACID 特性），主要使用 COMMIT、ROLLBACK 和 SET TRANSACTION 等语句。

1. COMMIT 语句

此语句用于提交当前事务，将事务中所有的操作永久保存到数据库中。

-- 开启一个事务
START TRANSACTION;
-- 执行一系列操作，如插入、更新或删除数据
INSERT INTO orders (order_id, customer_id, order_amount) VALUES (101, 1, 100.00);
-- 提交事务
COMMIT;

2. ROLLBACK 语句

该语句用于回滚当前事务，撤销事务中所有未提交的操作。

-- 开启一个事务
START TRANSACTION;
-- 执行一系列操作
UPDATE customers SET customer_name = 'New Name' WHERE customer_id = 1;
-- 发现错误，回滚事务
ROLLBACK;

3. SET TRANSACTION 语句

该语句用于设置事务的隔离级别，不同的隔离级别会影响事务之间的并发性能和数据一致性。

-- 设置事务的隔离级别为可重复读
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 开启一个事务
START TRANSACTION;
-- 执行数据库操作
SELECT * FROM orders;
-- 提交事务
COMMIT;

MySQL的join语句

1.内连接

select column_name(s) from table1
inner join table2
on table1.column_name = table2.column_name;

假设有两个表 customers 和 orders，customers 表包含客户信息，orders 表包含订单信息，它们通过 customer_id 关联。

-- 创建 customers 表
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50)
);

-- 插入示例数据
INSERT INTO customers (customer_id, customer_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

-- 创建 orders 表
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_amount DECIMAL(10, 2)
);

-- 插入示例数据
INSERT INTO orders (order_id, customer_id, order_amount) VALUES
(101, 1, 100.00),
(102, 2, 200.00);

-- 内连接查询
SELECT customers.customer_name, orders.order_amount
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;

2.左连接(返回左表)

返回左表的所有行,以及右表匹配的行,右表匹配左表

select column_name(s) from table1
left join table2
on table1.column_name = table2.column_name;

3.右连接(返回右表)

右连接与左连接相反，它返回右表中的所有行，以及左表中匹配的行。如果左表中没有匹配的行，则结果集中左表的列将显示为 NULL。

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

4.全连接(左表和右表中的所有行)

全连接返回左表和右表中的所有行。如果某一行在另一个表中没有匹配的行，则对应表的列将显示为 NULL。

SELECT column_name(s)
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name;

5.自连接

自连接是指将一个表与其自身进行连接。通常用于处理表中存在层次关系或关联关系的数据。

-- 创建 employees 表
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    manager_id INT
);

-- 插入示例数据
INSERT INTO employees (employee_id, employee_name, manager_id) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 1);

-- 自连接查询，查找每个员工及其上级的信息
SELECT e1.employee_name AS employee, e2.employee_name AS manager
FROM employees e1
LEFT JOIN employees e2
ON e1.manager_id = e2.employee_id;

MySQL的sql执行过程

1. 客户发送查询请求到数据库

2. 服务器先查缓存,如果命中,则立即返回

3. 服务器对sql进行解析和预处理,再由优化器生成对应的执行计划

4. mysql根据优化器生成执行计划,再调用存储引擎生成对应的执行计划

5. 将结果返回给客户端

更详细流程:

1.客户端发送请求

客户端MySQL服务器发送请求

2.服务器接受请求并进行连接管理

• 连接管理:MySQL有一个连接服务器,负责处理客户端的连接请求.当客户端发送连接请求时,连接管理器会验证客户端的身份信息(密码,账号等),若验证通过则建立连接.之后,客户端发送的sql语句会通过这个连接传输到服务器

• 线程分配:对于每个成功连接的客户端,服务器会为这个客户创建一个专门的线程来处理客户的请求,这个线程负责整个SQL语句的执行过程

3. 查询缓存

• 缓存检查：在解析 SQL 语句之前，MySQL 会先检查查询缓存。如果查询缓存中已经存在相同 SQL 语句的执行结果，并且该结果仍然有效（即相关数据没有发生变化），那么服务器会直接从缓存中返回结果，而无需执行后续的解析、优化和执行步骤，这样可以显著提高查询性能。

• 缓存失效：一旦表中的数据发生了变化（如执行了 INSERT、UPDATE、DELETE 等操作），与该表相关的所有查询缓存都会失效，以确保查询结果的准确性。

4. SQL 解析器

• 语法检查：如果查询缓存中没有命中，SQL 语句会被传递给解析器。解析器会对 SQL 语句进行语法分析，检查语句是否符合 MySQL 的语法规则。如果语法存在错误，解析器会抛出错误信息并终止执行。

• 生成解析树：在语法检查通过后，解析器会将 SQL 语句转换为一棵解析树（Parse Tree），这是一种树形结构的数据表示，用于描述 SQL 语句的逻辑结构，方便后续的处理。

5. 预处理器

• 语义检查：预处理器会对解析树进行语义分析，检查语句中的表名、列名、函数名等是否存在，以及用户是否具有相应的访问权限。例如，如果 SQL 语句中引用了一个不存在的表，预处理器会抛出错误。

• 权限验证：预处理器会验证当前用户是否具有执行该 SQL 语句所需的权限。如果用户没有足够的权限，会返回权限不足的错误信息。

• 解析树优化：预处理器还会对解析树进行一些简单的优化，如展开视图、处理常量表达式等。

6. 查询优化器

• 生成执行计划：查询优化器的主要任务是根据解析树生成多个可能的执行计划，并从中选择一个最优的执行计划。执行计划描述了如何从数据库中获取数据，包括表的访问顺序、使用的索引、连接方式等。

• 成本估算：优化器会根据数据库的统计信息（如表的行数、索引的选择性等）对每个执行计划的成本进行估算，选择成本最低的执行计划。例如，在多表连接查询中，优化器会考虑不同的连接顺序，以减少数据的扫描量和计算量。

7. 执行引擎

• 调用存储引擎：执行引擎根据查询优化器生成的执行计划，调用相应的存储引擎（如 InnoDB、MyISAM 等）来执行具体的操作。存储引擎负责实际的数据存储和检索，它会根据执行计划的要求从磁盘或内存中读取数据。

• 数据处理：执行引擎会按照执行计划的步骤依次执行，如扫描表、过滤数据、连接表、排序数据等，最终生成查询结果。

8. 返回结果

• 结果集返回：执行引擎将查询结果返回给客户端。如果查询结果比较大，可能会采用分页的方式逐步返回。

• 关闭连接：当查询执行完毕后，客户端可以选择关闭与服务器的连接，释放相关资源。

MySQL常用的函数

1. 字符串函数

• CONCAT(str1, str2, ...)：用于将多个字符串连接成一个字符串。

SELECT CONCAT('Hello', ' ', 'World'); 
-- 输出: Hello World

• SUBSTRING(str, start, length)：从指定字符串中提取子字符串。

SELECT SUBSTRING('HelloWorld', 1, 5); 
-- 输出: Hello

• UPPER(str) 和 LOWER(str)：分别将字符串转换为大写和小写。

SELECT UPPER('hello'); 
-- 输出: HELLO
SELECT LOWER('WORLD'); 
-- 输出: world

• TRIM(str)：去除字符串两端的空格。

SELECT TRIM('  Hello  '); 
-- 输出: Hello

2. 数值函数

• ROUND(num, decimals)：对数值进行四舍五入，decimals 指定保留的小数位数。

SELECT ROUND(3.14159, 2); 
-- 输出: 3.14

• CEIL(num) 和 FLOOR(num)：CEIL 函数返回大于或等于指定数值的最小整数，FLOOR 函数返回小于或等于指定数值的最大整数。

SELECT CEIL(3.2); 
-- 输出: 4
SELECT FLOOR(3.8); 
-- 输出: 3

• ABS(num)：返回数值的绝对值。

SELECT ABS(-5); 
-- 输出: 5

3. 日期和时间函数

• CURDATE() 和 CURTIME()：分别返回当前日期和当前时间。

SELECT CURDATE(); 
-- 输出当前日期，如 2024-01-01
SELECT CURTIME(); 
-- 输出当前时间，如 12:30:00

• NOW()：返回当前日期和时间。

SELECT NOW(); 
-- 输出当前日期和时间，如 2024-01-01 12:30:00

• DATE_FORMAT(date, format)：将日期按照指定的格式进行格式化。

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d'); 
-- 输出当前日期，格式为 2024-01-01

• DATEDIFF(date1, date2)：计算两个日期之间的天数差。

SELECT DATEDIFF('2024-01-10', '2024-01-01'); 
-- 输出: 9

4. 聚合函数

• COUNT(\*) 或 COUNT(column)：统计记录的数量，COUNT(*) 统计所有记录，COUNT(column) 统计指定列非空值的数量。

SELECT COUNT(*) FROM employees; 
-- 统计 employees 表中的记录数量

• SUM(column)：计算指定列的数值总和。

SELECT SUM(salary) FROM employees; 
-- 计算 employees 表中所有员工的工资总和

• AVG(column)：计算指定列的数值平均值。

SELECT AVG(salary) FROM employees; 
-- 计算 employees 表中员工的平均工资

• MAX(column) 和 MIN(column)：分别返回指定列的最大值和最小值。

SELECT MAX(salary) FROM employees; 
-- 找出 employees 表中工资的最大值
SELECT MIN(salary) FROM employees; 
-- 找出 employees 表中工资的最小值

5. 条件函数

• IF(condition, value_if_true, value_if_false)：根据条件判断返回不同的值。

SELECT IF(1 > 0, 'True', 'False'); 
-- 输出: True

• CASE 语句：用于实现多条件判断。

SELECT 
    CASE 
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 70 THEN 'C'
        ELSE 'D'
    END AS grade
FROM students;

MySQL变量

系统变量

系统变量由 MySQL 服务器维护，用于控制服务器的行为和配置。它又可细分为全局变量和会话变量。

全局变量

对整个 MySQL 服务器的运行状态产生影响，所有客户端连接都能访问。

• 查看全局变量:

SHOW GLOBAL VARIABLES; -- 查看所有全局变量
SHOW GLOBAL VARIABLES LIKE 'max_connections'; -- 查看指定全局变量

• 设置全局变量:

set global max_connections = 200;

要注意，设置全局变量需要有相应的权限，并且新设置的值只会对新的客户端连接生效，当前已有的连接不受影响。

会话变量

仅对当前客户端有效,不同的客户端连接可以有不同的会话变量值

• 查看会话变量

show session variables; -- 查看所有会话变量
show session variabkes like 'sql_mode'; -- 查看指定会话变量

• 设置会话变量:

set session sql_mode = 'STRICT_TRANS_TABLES';
-- 或者
set sql_mode = 'STRCTI_TRANS_TABLES';

用户定义变量

由用户自行定义和使用，其作用域为当前客户端连接。用户定义变量以 @ 符号开头。

set @var_name = value;

例如:

set @age = 25

在查询语句中直接赋值:

select @name := 'John';

使用用户变量

select @age;

set @price = 100;
set @discount = 0.2;
select @price * (1 - @discount) AS 最终价格;

局部变量

在存储过程,函数或触发器等数据库对象里定义和使用,其作用域仅限于定义他的代码块

定义和使用局部变量

delimiter

create procedure calculate_sum()
begin
	-- 定义局部变量
	delare num1 int default 5;
	delare num2 int default 3;
	delare result int;
	
	-- 计算和
	set result = num1 + num2;
	
	-- 输出结果
	select result;
end

delimiter

-- 调用存储过程
CALL calculate_sum();

MySQL字符编码

查看所有支持的字符编码

show character set;

查看默认的字符编码

show global variables like "%character%";

查看现有database的编码

show create database mysql;

查看现有某表的字符编码

show create table mydb.t1;

修改现有表的字符编码

alter table your_table [[default] chaaracter set charset_name] [collate  collation_name];

创建database时指定字符编码

create database mydb default character set utf8mb4 collate utf8mb4_general_ci;

或者修改my.cnf配置文件

略

mysql主从复制

同步两边的数据库

方法一

冷备份:(物理备份)

1. 先停下数据库

   systemctl stop mysqld

2. 备份数据库的文件再远程拷贝过去覆盖文件

方法二

温备份:(逻辑备份)

mysqldump -u指定用户 -p密码 -p服务器端口  --quick备份大表时加快备份速度 -l 备份是加锁,备份结束后自动解锁 -h指定主机,远程备份
-A备份所有数据库
--databases 库名
--tables 表名


实例:
mysqldump -uroot -pJnutter12 -A -l --quick > /opt/backup.sql
使用备份恢复的方法

法一 进入MySQL中使用 source /opt/backup.sql

法二 mysql -uroot -pJnutter12 [可选-h 加主机ip] < /opt/backup.sql

传统主从复制

1. 配置Master节点

#vim /etc/my.cnf

[mysqld]
server-id = 1  # 主库的唯一标识符，取值范围为 1 - 2^32 - 1
log-bin = mysql-bin  # 开启二进制日志
#binlog-do-db = your_database_name指定需要同步的数据库，如果不指定则同步所有数据库

重启mysql服务

systemctl restart mysql

创建远程账户,让另一个数据库能够有用于同步的账号

mysql -uroot -pJnutter12 << EOF
create user 'repl_user'@'%' identified by "Jnutter12_";
grant replication slave on *.* to 'repl_user'@'%';
flush privileges;
EOF

查看主库状态

SHOW MASTER STATUS;

从库配置

vim /etc/my.cnf
server-id=2 (不得重复)
#中断日志 是用来保证主从的从可以同步
relay_log=
##中断日志文件索引
#relay_log_index=
是否自动清除relay log文件
relay_log_purge=on

进入数据库,连接主库

#####忘记怎么连接时可以使用 ? change master to
CHANGE MASTER TO
  MASTER_HOST='192.168.50.11',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='Jnutter12_',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='log_bin.000001',
  MASTER_LOG_POS=398,
  MASTER_CONNECT_RETRY=10;

start slave; 启动主从服务

stop slave;停止主从服务

reset slave;移除主从

show slave status\G; 查看服务

purge binary logs to 'mysql_bin_000003'清理这个日志文件之前的所有日志文件

mysql的gtid主从复制

GTID:全局事务标志,GTID是对一个 已提交事务的编号,并且是一个全局唯一的编号

一个GTID在服务器上只执行一次,避免重复执行导致数据混乱

mysql5.6.5才开始支持gtid,5.6.10后才完善的

GTID的原理

在原本的基于日志的复制中,需要主库告诉从库从那个偏移量增量开始同步,如果指定错误会造成数据的遗漏,从而造成数据的不一致.而基于GTID的复制中,主库会告知从库已经执行的GTID的值,然后主库会将所有未执行的事务的GTID列表返回给从库,并且可以保证同一个事务只在指定的从库执行一次,通过全局的事务ID确认从库要从哪里开始同步

GTID的好处

1. 更简单的实现故障转移(failover),不用一起那样在需要找log_file和log_pos

2. 更简单的搭建主从复制

3. 比传统的复制更加安全

4. GTID是连续的没有空洞的,保证数据一致性,零丢失

GTID模式复制局限性

1. 不能使用create table_name as select * from table_name模式的语句

2. 在一个事务中既包含事务表的操作有包含非事务表

3. 不支持CREATE TEMPORARY(创建临时表) or DROP TEMPORARY TABLE语句

4. 使用GTID复制从库跳过错误时,不支持sql_slave_skip_counter(从复制中用于跳过错误事务的一个参数)

如何部署GTID主从复制

1. 配置Master节点

#vim /etc/my.cnf
#######找到并开启,没有则添加
....
server-id = 1 #主库唯一标识符
log-bin = mysql-bin #开启二进制日志
#binlog-do-db = your-database_name #可选,指定需要同步的库,不填则全部
gtid_mode = on #开启GTID模式
enforce_gtid_consistency = on #强制GTID一致性
.....

#重启服务
sudo systemctl restart mysql

####此时进入MySQL后新事物提交后会出现GTID的相关
#查看命令
show master status;

1. 创建用于复制的用户

登录主库的mysql客户端,创建

CREATE UESR 'repl_user'@'%' INDENTIFIED BY 'Jnutter12_';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
FLUSH PRIVILEGES;

1. 从库配置

编写MySQL配置文件

server-id = 2 #不能重复
gtid_mod = ON
enforece_gitd_consistency = on

重启MySQL服务

systemctl restart mysql

配置从库连接主库

CHANGE MASTER TO
    MASTER_HOST='master_ip_address',
    MASTER_USER='repl_user',
    MASTER_PASSWORD='your_password',
    MASTER_AUTO_POSITION = 1;

启动从库复制

执行一下sql语句启动从库的复制进程

START SLAVE;

检查从库状态

执行以下sql语句检查从库的状态,确保Slave_IO_Running 和 Slave_SQL_Running 都为 Yes

SHOW SLAVE STATUS\G

---

• 注意事项:

  • 确保主从库的时间同步,可以使用NTP来同步时间

  • 在配置过程中,如果遇到问题,可以查看MySQL错误日志文件,一般位于/var/log/mysql/error.log

  • 如果需要添加更多的从库重复从库配置的步骤就可以

MHA主从高可用

MHA工作原理

整体架构:

MHA架构主要包括三个组件:MHA Manager节点和多个MHA Node节点.

MHA管理节点负责监控数据库集群的状态,在主库故障的时候进行故障检测和主从切换操作;MHA Node部署在每个MySQL服务器上,辅助MHA Manager完成数据服务,日志应用任务

工作流程

• 监控阶段

  • manager节点会定时向从节点发送心跳检测请求,检查MySQL服务器的状态

  • MHA Node 接收到心跳检测请求后,会返回自身的状态信息,包括数据库是否正常运行,复制是否正常

  • MHA manager节点会根据收到的信息判断主库和从库的健康状况

• 故障检测与确认

  • 当MHA检测到主库可能出现故障时,会通过多种方式进行确认,会重新尝试连接数据检测二进制日志的写入情况等

  • 如果确认主库确实发生故障,MHA会标记主库为不可用状态,并开始准备主从切换

• 保存最新的二进制日志

  • 在进行主从切换之前,MHA Manager会通知所有MHA Node保存最新的二进制日志

  • MHA Node会主库上未同步到从库的二进制日志进行保存,确保在主从切换过程中不会丢失数据

• 选择新的主库

  • MHA Manager会根据一定(比如从库的复制延迟,数据完整性等)从多个从库中选择一个最适合的从库作为新的主库

  • 通常会选择复制延迟最小,数据最完整的从库作为新的主库

• 提升新主库

  • MHA Manager会向新竹路所在的MHA Node发送指令,将该从库提升为新的主库

  • MHA Node会执行相应的操作

• 同步数据到新主库

  • MHA Manager会将之前保存的二进制日志应用到新的主库上,确保新竹路的数据是新的

  • 同时,MHA Manager会通知其他从库连接到新的主库,并开始从新主库进行数据同步

• 更新应用程序配置

  • 在主从切换完成后,需要更新应用程序的数据库连接配置,使其指向新的主库

  • 可以通过自动化脚本或手动方式完成应用程序配置的更新

MHA基本部署

环境准备

(至少三台机器)

每一台主机都要保证主机之间ssh免密登录,包括自己

安装依赖软件

sudo yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker

[可选]每个节点 变比relay日志自动清除,设置slave节点为read_only

如果设置,警告而已

master节点:

mysql> set global realay_log_pirge=0
mysql> show global variables like "relay_log%";

slave节点:

mysql> set global read_only=on;
mysql> show global variables like "read_only";

安装MHA

1. 下载MHA软件包

Manager : GitHub - yoshinorim/mha4mysql-manager: Development tree of Master High Availability Manager and tools for MySQL (MHA), Manager part

Node : GitHub - yoshinorim/mha4mysql-node: Development tree of Master High Availability Manager and tools for MySQL (MHA), Node (MySQL Server) part

文档： Home · yoshinorim/mha4mysql-manager Wiki · GitHub

2.在MHA Manager节点安装mha4mysql-manager

yum localinstall -y localinstall mha4mysql-manager-0.57-0.el7.noarch.rpm

3.在所有 MySQL 节点安装 mha4mysql-node

yum  localinstall -y mha4mysql-manager-0.57-0.el7.noarch.rpm 

4.在每一个节点授权manger管理MySQL的账号

mysql -uroot -pJnutter12_

set sql_log_bin=0;
create user 'admin'@'192.168.50.%' indentied by 'Jnutter12_';
grant all privileges on *.* to 'admin'@'192.168.50.%';
flush privileges;
set sql_log_bin=1;

5.在master管理节点创建相关目录

mkdir -p /etc/mha/cluster1
mkdir -p /var/lib/mha/cluster1

修改配置文件

vim /etc/mha/cluster1/mha.cnf

确保你的系统有 ifconfig 命令，没有 则yum install -y net-tools

[server default]
manager_workdir=/var/lib/mha/cluster1
manager_log=/var/lib/mha/cluster1/manager.log
#master_ip_failover_script=/usr/local/bin/master_ip_failover
#漂移脚本位置

#主库的 二进制日志目录
master_binlog_dir=/var/lib/mysql/
#mha管理所有节点的数据库的账号 密码
user=admin
password=Jnutter12_

ssh_user=root
#设置检查各节点服务状态的 时间频率(秒)
ping_interval=2

#依次指定mha集群的数据库服务节点
[server1]
#指定数据库的节点1,可以用主机名或ip
hostname=192.168.50.11
port=3306

[server2]
hostname=192.168.50.12
port=3306

[server3]
no_master=1
hostname=192.168.50.13
port=3306

检查配置文件

在 MHA Manager 节点上执行以下命令检查配置文件是否正确：

masterha_check_ssh --conf=/etc/mha/cluster1/mha.cnf
masterha_check_repl --conf=/etc/mha/cluster1/mha.cnf

节点互信检查(master)执行

masterha_check_ssh --conf=/etc/mha/cluster1/mha.cnf

结果提示: MySQL Replication Health is OK.

启动MHA

nohup masterha_manager --conf=/etc/mha/cluster1/mha.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null> /var/lib/mha/cluster1/manager.log  2>&1   & 

查看MHA状态

msterha_check_status --conf=/etc/mha/cluster1/mha.cnf

停止MHA

masterha_stop --conf=/etc/mha/cluster1/mha.cnf

简化MHA启动命令

方法一: 脚本简化

vim mha_management.sh

#!/bin/bash

# 定义配置文件路径
CONF_FILE="/etc/mha/cluster1/mha.cnf"

case "$1" in
    start)
        nohup masterha_manager --conf=$CONF_FILE --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/lib/mha/cluster1/manager.log 2>&1 &
        echo "MHA Manager started."
        ;;
    status)
        masterha_check_status --conf=$CONF_FILE
        ;;
    stop)
        masterha_stop --conf=$CONF_FILE
        echo "MHA Manager stopped."
        ;;
    restart)
        masterha_stop --conf=$CONF_FILE
        echo "MHA Manager stopped."
        sleep 2
        nohup masterha_manager --conf=$CONF_FILE --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/lib/mha/cluster1/manager.log 2>&1 &
        echo "MHA Manager restarted."
        ;;
    *)
        echo "Usage: $0 {start|status|stop|restart}"
        exit 1
        ;;
esac
    

chmod +x mha_management.sh

• 启动

./mha_management.sh.sh start

• 查看MHA状态

./mha_management.sh status

• 停止MHA

./mha_management.sh stop

• 重启MHA

./mha_management.sh restart

方法二: 使用systemd管理

vim /etc/systemd/system/mha.service

[Unit]
Description=MHA Manager Service
After=network.target

[Service]
ExecStart=/usr/bin/masterha_manager --conf=/etc/mha/cluster1/mha.cnf --remove_dead_master_conf --ignore_last_failover
ExecStop=/usr/bin/masterha_stop --conf=/etc/mha/cluster1/mha.cnf
ExecStatus=/usr/bin/msterha_check_status --conf=/etc/mha/cluster1/mha.cnf
Restart=always
RestartSec=5
StandardOutput=file:/var/lib/mha/cluster1/manager.log
StandardError=inherit

[Install]
WantedBy=multi-user.target    

重加载systemd服务

systemctl daemon-reload

• 启动 MHA：

systemctl start mha

• 查看 MHA 状态：

systemctl status mha

• 停止 MHA：

systemctl stop mha

• 重启 MHA：

systemctl restart mha

MHA的VIP高可用

生成一个VIP

ifconfig ens33:0 192.168.50.100/24
#如果需要停止VIP则,# ifconfig ens33:0 down

VIP漂移脚本:

vim /usr/local/bin/master_ip_failover

#!/usr/bin/env perl
use strict;
use warnings FATAL =>'all';
  
use Getopt::Long;

my (
    $command,          $ssh_user,        $orig_master_host, $orig_master_ip,
    $orig_master_port, $new_master_host, $new_master_ip,    $new_master_port
);

my $vip = '192.168.50.100/24';
my $key = "1";
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down";
my $exit_code = 0;

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,vi /usr/local/bin/master_ip_failover
    'new_master_host=s'  => \$new_master_host,
    'new_master_ip=s'    => \$new_master_ip,
    'new_master_port=i'  => \$new_master_port,
);

exit &main();

sub main {
    #print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";
    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 {
                print "\n\n\n***************************************************************\n";
                print "Disabling the VIP - $vip on old master: $orig_master_host\n";
                print "***************************************************************\n\n\n\n";
                &stop_vip();
                $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 "\n\n\n***************************************************************\n";
                print "Enabling the VIP - $vip on new master: $new_master_host \n";
                print "***************************************************************\n\n\n\n";
                &start_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;
    } 
}

# A simple system call that enable the VIP on the new master  
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";
}

systemctl restart mha

MHA切换时发送邮件报警

vim /etc/mha/cluster1/mha.cnf

添加指定脚本实现发送邮件

在[server default]添加

report_script=/usr/local/bin/send_report

systemctl stop sendmail
systemctl stop postfix

安装插件

yum install -y mailx

vim /etc/mail.rc

set from=jfklovelll@qq.com
set smtp=smtps://smtp.qq.com:465
set smtp-auth-user=jfklovelll@qq.com
set smtp-auth-password=lapnadlplwofdjdg
set smtp-auth=login
set ssl-verify=ignore
set nss-config-dir=/etc/pki/nssdb

echo "测试内容" | mailx -s "测试邮件" jfklovelll@qq.com

编写发送邮件脚本

vim /usr/local/bin/send_report

#!/bin/bash
# ==============================================================================
# MHA 故障切换日志报告脚本
# 功能：提取 MHA 日志中的关键信息，发送故障切换报告邮件
# ==============================================================================

# ================== 配置参数 ==================
LOG_FILE="/var/log/mha/mha_manager.log"       # MHA 日志文件路径（需根据实际环境修改）
MAIL_TO="jfklovelll@qq.com"                    # 收件人邮箱
FROM_EMAIL="jfklovelll@qq.com"                 # 发件人邮箱（与 mail.rc 一致）
MAIL_SUBJECT="【MHA故障切换报告】$(date +'%Y-%m-%d %H:%M:%S')"

# ================== 提取关键日志 ==================
# 提取最近 1 小时内的故障切换相关日志（可根据需求调整时间范围）
# 关键关键词：故障触发、节点切换、错误信息、切换结果
KEY_LOG=$(grep -E 'ERROR|CRITICAL|failover|completed|Master切换|New master is' "$LOG_FILE" \
  | awk -v RS='' -v since_hours=1 '$0 ~ /'$(date -d "1 hour ago" +'%Y-%m-%d %H:%M:%S')'/,/$(date +'%Y-%m-%d %H:%M:%S')/' \
  | sed 's/^/    /')   # 缩进日志内容，提升可读性

# 若未找到关键日志，直接退出（避免发送空邮件）
if [ -z "$KEY_LOG" ]; then
  echo "[$(date)] 无新的 MHA 故障切换日志，无需发送报告"
  exit 0
fi

# ================== 构造邮件内容 ==================
MAIL_BODY="$(cat <<EOF
MHA 故障切换报告（自动生成）

时间：$(date +'%Y-%m-%d %H:%M:%S')
日志文件：$LOG_FILE

------------------------ 关键事件 ------------------------
$KEY_LOG

------------------------ 完整日志链接 ------------------------
（如需查看完整日志，请登录服务器：/path/to/mha_logs）

EOF
)"

# ================== 发送邮件（使用 mailx） ==================
echo "$MAIL_BODY" | mailx -v -s "$MAIL_SUBJECT" "$MAIL_TO" <<< /dev/null

# ================== 错误检查 ==================
if [ $? -ne 0 ]; then
  echo "[$(date)] 邮件发送失败，错误码：$?"
  exit 1
else
  echo "[$(date)] 邮件发送成功"
  exit 0
fi

MGR集群

MySQL Group Replication（MGR）是 MySQL 官方提供的高可用、可扩展的集群解决方案，它基于复制技术实现多主或单主模式的集群

环境准备

• 服务器规划：准备至少 3 台服务器，建议为奇数台，以保证多数派协议正常工作。

• 操作系统：建议使用 Linux 系统，如 CentOS 或 Ubuntu。

• MySQL 版本：MySQL 5.7.17 及以上版本，推荐使用 MySQL 8.0 系列。

• 网络连通：确保所有服务器之间网络畅通，能够相互访问。

• 时钟同步：所有服务器的时钟必须同步，可以使用 NTP 服务进行时钟同步。

修改所有主机my.cnf

[mysql]
port=3306
socket=/var/lib/mysql/mysql.sock

[mysqld]
user=mysql
port=3306
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
skip-name-resolve
tmpdir=/tmp

server-id=11
log_bin=/var/lib/mysql/mysql_bin
log_bin_index=/var/lib/mysql/mysql_bin.index
expire_logs_days=30
max_binlog_size=512M
sync_binlog=0
binlog_format=ROW
binlog_checksum=NONE

relay_log_recovery = 1
# 必须将 relay_log_info 和 master_info 存储到TABLE 否则加载 group_replication.so插件失败
relay_log_info_repository=TABLE
master_info_repository=TABLE

log_slave_updates=ON
gtid-mode=ON
enforce-gtid-consistency=ON

binlog_ignore_db = sys
binlog_ignore_db = mysql
binlog_ignore_db = performance_schema
binlog_ignore_db = information_schema

#服务启动加载安装复制组插件
plugin_load_add='group_replication.so'

#指示Server必须为每个事务收集写集合，并使用XXHASH64哈希算法将其编码为散列
transaction_write_set_extraction=XXHASH64

#MGRP集群的UUID,复制组名称确保每个机器都一样，可以系统命令 uuidgen 生成
group_replication_group_name="5d9afa32-6990-4b62-a662-d3448caa3566"


#当前主机的主机名和复制组通信端口(33061自定义),注意区分 3306 数据读写端口(每个主机不一样的)
group_replication_local_address= "192.168.50.11:33061"

#复制组的成员信息,因为有A记录解析,因此可直接写主机名
group_replication_group_seeds= "192.168.50.11:33061,192.168.50.12:33061,192.168.50.13:33061"

#复制组白名单,同网段下可以不设置，如果跨网段必须设置
group_replication_ip_whitelist = '192.168.50.11,192.168.50.12,192.168.50.13'


#MySQL启动时是否自动启动组复制
group_replication_start_on_boot=OFF

#是否引导组服务，如果开启每次重启都会创建新的复制组，所以配置文件中一定要关闭
group_replication_bootstrap_group=OFF

#是否单主模式，默认是
group_replication_single_primary_mode=ON

#多主模式下,强制检查每一个实例是否允许写操作，默认关闭
#group_replication_enforce_update_everywhere_checks=OFF

sudo systemctl restart mysqld

创建用于集群的用户

在所有服务器上登录 MySQL 客户端，创建一个用于集群通信的用户，并授予相应的权限：

mysql -uroot -pJnutter12_

CREATE USER 'repl_user'@'%' IDENTIFIED BY 'Jnutter12_';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
FLUSH PRIVILEGES;

<所有节点>为组复制通道指定用于同步 授权的账号 和 密码

CHANGE MASTER TO MASTER_USER='repl_user', MASTER_PASSWORD='Jnutter12_' FOR CHANNEL 'group_replication_recovery';

select * from  slave_master_info\G      -- change mater的配置到该表中

初始化集群

进入MySQL的用户,server01节点

SET GLOBAL group_replication_single_primary_mode=ON;
-- 配置文件如果已经指定，无需开启

• ON单主模式（如果是单主模式则当前引导节点为master，其他加入节点为slave)。

• OFF 多主模式(所有节点都是master)

开启节点的引导模式，创建复制组并接受其他节点加入复制组

SET GLOBAL group_replication_bootstrap_group=ON;

开启组复制数据同步服务

START GROUP_REPLICATION;

将当前节点设置为引导节点后关闭

SET GLOBAL group_replication_bootstrap_group=OFF;

查询组成员

SELECT * FROM performance_schema.replication_group_members;

---

启动其余节点

START GROUP_REPLICATION;   -- 加入组复制

查询组成员

SELECT * FROM performance_schema.replication_group_members;

• 字段 MEMBER_STATE 有 5 种，分别是：ONLINE（在线）、OFFLINE（离线）、RECOVERING（恢复中）、ERROR（发生错误） 和 UNREACHABLE（无法通信）。

查看哪个节点是primary 主节点

show status like 'group_replication_primary_member';

show variables like 'server_uuid';    -- 查看当前服务器 uuid

---

故障维护

在 MySQL Group Replication（MGR）集群中，基于 多数派协议（Quorum），当故障节点数超过半数（即存活节点数不足总节点数的 50% + 1）时，剩余节点无法形成有效多数派，导致集群无法选主、停止处理写操作，甚至可能进入只读状态。

1. 停止所有剩余节点的集群服务

在存活节点上执行以下命令，停止 Group Replication：

STOP GROUP_REPLICATION;

2.对比GTID ，选择在GTID最大的节点执行引导

SHOW GLOBAL VARIABLES LIKE 'gtid_executed';

3.引导集群(新选出来的主节点)

set global group_replication_bootstrap_group=ON;
start group_replication;
set global group_replication_bootstrap_group=OFF;

4.其余节点加入集群

start group_replication;

MySQL的优化

MySQL安全优化

1.设置较高强度的密码

show variables like 'validate_password%';  -- 查看密码强度

临时修改（会话级别）

-- 设置密码强度策略为高
SET GLOBAL validate_password.policy = 2;
-- 设置密码最小长度为 12
SET GLOBAL validate_password.length = 12;

这种修改方式仅在当前会话中生效，当 MySQL 服务重启后，设置会恢复到之前的配置。

永久修改（配置文件级别）

/etc/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
validate_password.policy = 2
validate_password.length = 12

sudo systemctl restart mysql

设置密码过期时间

在 MySQL 5.7 及以上版本中，可以使用 ALTER USER 语句来设置密码过期时间。

-- 1. 创建一个新用户
CREATE USER 'test_user'@'%' IDENTIFIED BY 'your_password';

-- 2. 设置密码在 90 天后过期
ALTER USER 'test_user'@'%' PASSWORD EXPIRE INTERVAL 90 DAY;

-- 3. 若要设置密码立即过期，用户下次登录时必须修改密码
ALTER USER 'test_user'@'%' PASSWORD EXPIRE;

-- 4. 若要取消密码过期策略，可使用以下语句
ALTER USER 'test_user'@'%' PASSWORD EXPIRE NEVER;

配置全局默认密码过期时间

除了为单个用户设置密码过期时间，还可以通过修改 MySQL 配置文件来设置全局默认的密码过期时间。

/etc/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
default_password_lifetime = 90

systemctl restart mysql

采取ssl加密认证

[mysqld]
require_secure_transport=ON

查看是否开启 SSL 认证

show variables like '%require_secure_transport%';

mysql相关题目

二、填空题（每空 2 分，共 20 分）

1. MySQL 8.0 默认字符集为 utf8mb4。

2. 源码安装 MySQL 的核心步骤包括配置、编译和 安装（make install）。

3. 优化 DELETE 语句性能的写法是 DELETE FROM table_name WHERE condition;（避免全表扫描）。

4. 计算非空值数量的函数是 COUNT()。

5. 事务的四大特性（ACID）中，I 代表 隔离性（Isolation）。

6. InnoDB 默认的行锁机制依赖于 索引（若查询无索引，会退化为表锁）。

7. 查看当前数据库锁状态的命令是 SHOW ENGINE INNODB STATUS;。

8. 创建普通索引的语句是 CREATE INDEX index_name ON table_name(column);。

9. 记录所有连接和语句的日志是 一般日志（General Log）。

10. 主从复制中，从库获取主库日志的线程是 IO 线程。

三、简答题（每题 5 分，共 50 分）

1. MySQL 5.7 与 8.0 的主要区别？

   • 8.0 引入 CTE（公共表表达式）、窗口函数、原子 DDL、隐藏索引、JSON 原生支持等。

   • InnoDB 默认缓冲池大小调整，5.7 需手动配置，8.0 自动优化。

   • 8.0 弃用传统密码加密插件（sha1_password），默认使用 caching_sha2_password。

2. 简述 MySQL 源码安装步骤（以 Linux 为例）？

   1. 安装依赖：yum install -y gcc gcc-c++ cmake make bison-devel ncurses-devel

   2. 下载解压源码：tar -xf mysql-8.0.33.tar.gz && cd mysql-8.0.33

   3. 配置编译参数：cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/mysql -DSYSCONFDIR=/etc

   4. 编译安装：make && make install

   5. 初始化数据库：/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql

   6. 启动服务：systemctl start mysql

3. 事务的定义及 ACID 特性？

   • 事务是一组原子性的数据库操作，要么全部执行，要么全部回滚。

   • A（原子性）：操作不可分割；C（一致性）：数据状态合法；I（隔离性）：事务间互不干扰；D（持久性）：提交后数据永久保存。

4. 字符编码配置错误可能导致什么问题？如何全局配置 UTF-8？

   • 问题：数据乱码、排序异常、索引失效。

   • 配置：在

     my.cnf

     中添加：

     [mysqld]  
     character-set-server=utf8mb4  
     collation-server=utf8mb4_general_ci  

     重启服务后通过

     SHOW VARIABLES LIKE 'character_set%';

     验证。

5. 简述 InnoDB 的锁机制（行锁、表锁、锁类型）？

   • 行锁：基于索引，细粒度锁（仅锁定符合条件的行），支持并发。

   • 表锁：锁定整张表，并发性能差（如 MyISAM 默认表锁）。

   • 锁类型：共享锁（S 锁，允许多个读）、排他锁（X 锁，仅允许一个写）。

6. 索引的作用及常见数据结构（至少 2 种）？

   • 作用：加速查询、减少磁盘 I/O、保证数据唯一性（主键索引）。

   • 数据结构：

     • B + 树：范围查询高效，默认索引结构。

     • 哈希索引：等值查询快（O (1)），不支持范围查询。

7. 视图的优缺点及使用场景？

   • 优点：简化复杂查询、隐藏敏感字段、统一数据接口。

   • 缺点：性能可能下降（依赖底层表）、部分场景不支持更新。

   • 场景：给应用层提供定制化数据视图，或封装多表关联逻辑。

8. MySQL 日志分类及核心用途（至少 3 种）？

   • 错误日志：记录启动、运行、关闭时的错误信息（定位故障）。

   • 二进制日志（Binlog）：记录所有数据变更（用于主从复制、数据恢复）。

   • 慢查询日志：记录执行时间超过阈值的 SQL（用于性能优化）。

9. 物理备份与逻辑备份的区别？

   • 物理备份：复制数据文件（如.ibd、.frm），优点是恢复快，适合全量备份；缺点是跨版本兼容性差。

   • 逻辑备份：导出 SQL 语句（如mysqldump），优点是可读性强、跨版本兼容；缺点是恢复慢，适合增量备份。

10. 主从复制中 GTID 的优势？

    • GTID（全局事务 ID）唯一标识每个事务，避免手工指定日志文件名和位置。

    • 支持断点续传、故障恢复时自动定位事务，简化主从切换流程。

四、操作题（每题 10 分，共 60 分）

1. 创建数据库、表并插入数据：

   CREATE DATABASE test_db CHARACTER SET utf8mb4;  
   USE test_db;  
   CREATE TABLE products (  
       id INT PRIMARY KEY AUTO_INCREMENT,  
       name VARCHAR(50) NOT NULL,  
       price DECIMAL(10, 2),  
       quantity INT  
   );  
   INSERT INTO products (name, price, quantity) VALUES  
       ('Product A', 99.99, 10),  
       ('Product B', 199.99, 5),  
       ('Product C', 49.99, 20);  

2. 查询价格 > 100 的产品并按价格降序排列：

   sql

   SELECT * FROM products WHERE price > 100 ORDER BY price DESC;  

3. 基于二进制日志部署主从复制（步骤简述）：

   1. 主库（Master）配置：

      • 在

        my.cnf

        中启用 Binlog 并设置唯一

        server-id

        log_bin=master-bin  
        server-id=1  

      • 重启主库，创建复制用户：

        CREATE USER 'repl'@'%' IDENTIFIED BY 'password';  
        GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';  

      • 记录主库状态：SHOW MASTER STATUS;（获取File和Position）。

   2. 从库（Slave）配置：

      • 设置唯一server-id=2，关闭 Binlog（可选）。

      • 连接主库：

        sql

        CHANGE MASTER TO  
            MASTER_HOST='master_ip',  
            MASTER_USER='repl',  
            MASTER_PASSWORD='password',  
            MASTER_LOG_FILE='master-bin.000001',  
            MASTER_LOG_POS=100;  
        START SLAVE;  

      • 验证：SHOW SLAVE STATUS\G，确保Slave_IO_Running和Slave_SQL_Running均为Yes。

4. 部署 MHA 高可用（核心步骤）：

   1. 安装 MHA 工具包及依赖（mha4mysql-manager、mha4mysql-node）。

   2. 配置主从复制集群，确保节点间 SSH 互信。

   3. 编写 MHA 配置文件（app1.cnf），定义主库、从库列表及 VIP 信息。

   4. 启动 MHA 管理进程：nohup masterha_manager --conf=/etc/mha/app1.cnf &。

   5. 故障时 MHA 自动切换主库，更新应用连接信息。

5. 部署 MGR 集群（MySQL Group Replication）：

   1. 所有节点启用 GTID 和组复制插件：

      [mysqld]
      enforce_gtid_consistency=ON
      gtid_mode=ON
      plugin_load_add=group_replication.so

   2. 初始化主节点：

      SET SQL_LOG_BIN=0;  
      CREATE USER 'mgr'@'%' IDENTIFIED BY 'password';  
      GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'mgr'@'%';  
      SET SQL_LOG_BIN=1;  
      START GROUP_REPLICATION;  

   3. 其他节点加入集群：

      CHANGE MASTER TO MASTER_USER='mgr', MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery';  
      START GROUP_REPLICATION;  

   4. 验证集群状态：SELECT * FROM performance_schema.replication_group_members;

6. MySQL 优化思路（至少 3 点）：

   • 索引优化：分析慢查询，为高频查询字段添加索引，避免索引失效（如左前缀匹配）。

   • 参数调优：根据硬件配置调整innodb_buffer_pool_size（建议为物理内存的 50%-70%）、max_connections等。

   • 分库分表：对大表进行水平 / 垂直拆分，降低单表数据量。

   • 硬件升级：使用 SSD 加速磁盘 I/O，增加内存提升缓存命中率。