MYSQL 详解

一. 什么是 mysql ?

mysql 是一种关系型数据库。它是 C/S 架构，所以有客户端和服务端。操作 系统不同，mysql 安装目录也不同。若是unix类系统通过包管理软件安装，路径一般为 /usr/bin/mysql ;手动安装地址 /usr/local/mysql。

1. 部署 mysql

1.1 Ubuntu 包管理安装 mysql

使用 Ubuntu 的包管理器 apt 或者 apt-get 安装。

sudo apt update
sudo apt install mysql

1.2 二进制部署 mysql

description	目录
系统版本	Ubuntu 22.04.5 LTS x86_64
mysql 版本	8.0.36
mysql 家目录	/usr/local/mysql
mysql data 目录	/usr/local/mysql/data
mysql socket 文件	/var/lib/mysql/mysql.sock
mysql error log	/usr/local/mysql/data/error.log
mysql binlog 目录	/usr/local/mysql/data/binlog
mysql 配置文件	/etc/my.cnf
mysql 服务文件	/usr/lib/systemd/system/mysqld.service

1.2.1 下载二进制文件

mysql 官网下载地址 ，选择 (x86, 64-bit), Compressed TAR Archive 版本的下载。需注意：服务器上的 ==glibc== 版本需要高于包名上标注的版本，使用 ldd -version 查看 glibc 版本。

1.2.2 部署 mysql

[[shell 脚本一键部署 mysql]]

解压文件

# 解压文件
tar xvf mysql-8.0.36-linux-glibc2.28-x86_64.tar.xz
# 移动 mysql 到安装目录
sudo mv mysql-8.0.36-linux-glibc2.28-x86_64 /usr/local/mysql
# sudo chown -R root:root /usr/local/mysql
cd /usr/local/mysql/bin
# 查看有没有缺依赖，没有输出就是没有缺。如果缺了依赖就 apt search 依赖名，看看有没有对应的包，没有就问 gpt
ldd mysqld mysql | grep "not found"

创建 mysql 用户和用户组

# 创建 mysql 用户组
sudo groupadd mysql
# 创建 mysql 用户
sudo useradd -r -g mysql -s /bin/false mysql
# 修改 mysql 家目录下的文件所属
sudo chown -R mysql:mysql /usr/local/mysql
sudo mkdir -p /var/lib/mysql /usr/local/mysql/data/binlog
sudo chown -R mysql:mysql /var/lib/mysql

设置环境变量

# 设置环境变量 bash 或者 sh 用这个
sudo sh -c "cat >> /etc/profile << EOF
export PATH=/usr/local/mysql/bin:$PATH
EOF
"
source /etc/profile

# zsh 用这个
sudo sh -c "cat >> /etc/zsh/zshenv << EOF
export PATH=/usr/local/mysql/bin:$PATH
EOF
"
source /etc/zsh/zshenv

写入配置文件 my.cnf

sudo sh -c "cat > /etc/my.cnf << EOF
[client]
default_character_set=utf8
socket=/var/lib/mysql/mysql.sock 
[mysqld]
collation_server=utf8mb4_general_ci
character_set_server=utf8mb4
default-time-zone='+08:00'
# 跳过登录验证
#skip-grant-tables
#server_id=101
socket=/var/lib/mysql/mysql.sock
log_error=/usr/local/mysql/data/error.log
log_bin=/usr/local/mysql/data/binlog
binlog-ignore-db=mysql
log-bin=mall-mysql-bin
binlog_cache_size=1M
# binlog_format=mixed    8.0 has deprecated.
# expire_logs_days=7    8.0 has deprecated，use next line instead
binlog_expire_logs_seconds=604800
# slave_skip_errors=1062     8.0 has deprecated，use next line instead
replica_skip_errors=1062

#--innodb-buffer-pool-size=512M
slow_query_log=1
long_query_time=2

##限制mysql的内存占用，内存占用过多时可以使用
#skip-host-cache
#skip-name-resolve
#检测的表对象的最大数目
#performance_schema_max_table_instances=400
#表定义缓存中表的个数
#table_definition_cache=400
#表文件描述符的缓存大小
#table_open_cache=256
#performance_schema=off
EOF
"

初始化 mysql。初始化密码在最后一行末尾，若没有则执行 cat /usr/local/mysql/data/error.log 查看

sudo /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
# 初始化失败则删除 /usr/local/mysql/data 目录即可

创建守护进程

sudo sh -c "cat > /usr/lib/systemd/system/mysqld.service << EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql

# Have mysqld write its state to the systemd notify socket
Type=notify

# Disable service start and stop timeout logic of systemd for mysqld service.
TimeoutSec=0

# Start main service
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf $MYSQLD_OPTS 

# Use this to switch malloc implementation
EnvironmentFile=-/etc/sysconfig/mysql

# Sets open_files_limit
LimitNOFILE = 10000

Restart=on-failure

RestartPreventExitStatus=1

# Set environment variable MYSQLD_PARENT_PID. This is required for restart.
Environment=MYSQLD_PARENT_PID=1

PrivateTmp=false
EOF
"

启动 mysql

sudo systemctl daemon-reload
sudo systemctl enable mysqld --now

修改 mysql 密码

/usr/local/mysql/bin/mysql -uroot -p
ALTER user 'root'@'localhost' IDENTIFIED BY 'qweasdzxc';

允许远程访问

USE mysql;
UPDATE user SET Host='%' WHERE User='root';
FLUSH PRIVILEGES;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

在不直接修改systemd配置文件的情况下为mysqld指定启动参数。

# 设置 mysqld 的启动参数。--skip-grant-tables 跳过密码启动
sudo systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"

# 清空设置 mysqld 的启动参数
sudo systemctl unset-environment MYSQLD_OPTS

[!notice] 这里的 MYSQLD_OPTS 对应的是 /usr/lib/systemd/system/mysqld.service 文件中 ExecStart 最后面跟的变量名。

1.2.3 卸载 mysql

[[shell 脚本卸载 mysql]]

2. bin目录下的文件

mysql bin目录下部分可执行文件如下所示：

.
├── mysql
├── mysql.server -> ../support-files/mysql.server
├── mysqladmin
├── mysqlbinlog
├── mysqlcheck
├── mysqld
├── mysqld_multi
├── mysqld_safe
├── mysqldump
├── mysqlimport
├── mysqlpump
... (省略其他文件)
0 directories, 40 files

2.1.1 mysqld

mysqld是mysql数据库的服务端进程，但不经常用，原因还需往下看!运行mysqld会启动一个带有log输出窗口的mysql服务进程。

2.1.2 mysqld_safe

mysqld_safe 是启动脚本，会间接调用 mysqld ，并启动一个监控进程，在 mysqld 故障时进行重启。直接运行 mysqld 时 log 会输出到启动窗口，而 mysql_safe 的 log 会重定向到一个文件中。

2.1.3 mysql.server

mysql.server 同样是启动脚本，会间接调用 mysqld_safe 。这个 mysql.server 文件其实是一个链接文件，它的实际文件是 ../support-files/mysql.server。执行 mysql.server 时可以在后面跟命令，如： mysqld.server start 。

2.1.4 mysqld_multi

以上命令都是启动一个实例进程，而 mysqld_multi 是启动多个实例，可以对每一个服务器进程的启动或停止进行监控。

2.1.5 mysqlbinlog

mysqlbinlog 是查看 binlog 日志文件的命令。binlog 文件以二进制方式存储，不能直接查看，需要使用 mysqlbinlog 命令来查看。

2.2 mysql 与 mysqld "相爱相杀"!

2.2.1 mysql 简单语法

mysql 中，-u这类参数是短参数，后面可以直接跟值，也可以空格后再跟值，例: -uroot 和 -u root 等效。短参数前面用单个短划线 - 。 -p 参数特例，参数和值之间不能有空格！！！ 有短参数也有长参数，--user 就是 -u 的完整形式，完整参数的赋值格式为 --user=VALUE 中间不能有空格。长参数前面用两个短划线 --。 mysql 各个参数之间没有顺序要求，可以随意摆放各参数。

2.2.2 Link stared!

mysql -hHOST -uUSER -pPASSWORD -Pport
# for example
mysql -hmoloom.com -uroot -p123456 -P7777

[!notice] 注意：连接上 mysql 后，msql的命令都要以分号 ;结尾！

参数	解释
-h	指定要连接的主机地址，可以是域名和ip地址。如果连接本地mysql可以省略。
-u	指定要登录的用户名
-p	用户名的密码

如果要断开客户端与服务端的连接，在 mysql 控制台输入 quit,exit,\q 中的任意一个命令即可退出客户端。

1.2.3 mysql 和 mysqld 是怎么"交流"的?

要使用 mysql 数据库，必须要连接它的服务端，发生了"连接"这个动作，那就涉及到了通信。mysql 支持 TCP\IP、pipe和共享内存、Unix套接字文件 三种通信方式，默认方式是 TCP\IP 。

1.2.3.1 TCP\IP

mysql 服务默认监听 3306 端口。客户端连接服务端时，如果没有指定主机和端口，默认连接本地地址 127.0.0.1 的 3306 端。

#这两条命令等效
mysql -h 127.0.0.1 -P 3306 -uroot -p
mysql -uroot -p

1.2.3.2 pipe和共享内存

命名管道和共享内存是Windows操作系统中的两种进程间通信方式。 - 使用命名管道来进行进程间通信 需要在启动服务器程序的命令中加上 --enable-named-pipe 参数，然后在启动客户端程序的命令中加入 --pipe 或者 --protocol=pipe 参数。

• 使用共享内存来进行进程间通信 需要在启动服务器程序的命令中加上 --shared-memory 参数，在成功启动服务器后， 共享内存便成为本地 客户端程序的默认连接方式，不过我们也可以在启动客户端程序的命令中加入--protocol=memory 参数来显 式的指定使用共享内存进行通信。（共享内存方式因为是服务端与客户端使用内存来沟通，所以服务端和客户端必须在同一台主机上！）

1.2.3.3 Unix域套接字文件

如果我们在启动客户端程序的时候指定的主机名为 localhost ，或者指定了 --protocol=socket 的启动参数，那服务器程序和客户端程序之间就可以通过 Unix 域套接字文件来进行通信了。 发行版不同，系统不同，套接字文件 socket 所在的路径也不一样，/var/lib/mysql/mysql.sock 和 /tmp/mysql.sock 都可能存在。服务端可以通过启动参数 mysqld --socket=/path/xxx 来指定套接字文件，客户端同样需要使用 --socket=/path/xxx 来指定Unix 域套接字文件进行通信。

[!notice] Unix域套接字文件方式也同样要求客户端与服务端在同一台机器上，且该种方式用于 Linux 系统。

1.3 什么是存储引擎?

二. mysql 的配置

. MYSQL备份与还原

备份可划分为热备份、冷备份和温备份。 - 热备份：读写不受影响 - 冷备份：也叫离线备份，读写都被中止后再备份 - 温备份：仅可执行读操作

备份还可划分为物理备份和逻辑备份 - 物理备份：复制数据文件到其他硬盘 - 逻辑备份：将数据导出至文本文件中

备份还分为完全备份、增量备份和差异备份 - 完全备份：完全备份全部数据 - 增量备份： - 差异备份：

1.

binlog 日志

binglog 日志记录了所有 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，不包括查询语句。binlog 以二进制形式存储，不可直接查看，需要使用 mysqlbinlog 命令查看 binlog 日志。

通过 show variables like '%log_bin%'; 语句查看有关 binlog 的配置

.1 日志格式

mysql 提供了多种格式来记录二进制日志： - statement 基于 SQL 语句的日志记录，记录的是 SQL 语句，对数据进行修改的 SQL 语句都会记录到日志中。优点：日志文件通常较小，复制效率较高。缺点：在某些情况下，由于数据库环境的差异（如表结构、字符集等），在从服务器上重放这些 SQL 语句可能会导致不一致的结果。例如，获取当前时间的函数或存储过程等，可能会导致数据不一致。出现在 MySQL 5.1 之前。 - row 基于行的日志记录，记录的是数据变更前后的实际数据内容。优点：能够精确地记录数据的变化，避免了 statement 格式中的环境依赖问题，提供了更强的一致性保证。缺点：日志文件可能会比 statement 格式大，因为记录了每一行的详细变化。此外，row 格式的日志在进行大量数据更新时可能会导致更高的 I/O 开销。MySQL 5.1 版本出现。 - mixed 混合了 statement 和 row 两种格式，它默认采用 statement，在某些特殊情况下会自动切换为 row 格式。优点：结合了 statement 和 row 格式的优点，能够在保证一致性的同时尽可能地优化日志大小和复制性能。缺点：由于混合使用了两种格式，可能需要更复杂的管理和监控。在某些特定情况下，MmixedIXED 格式可能无法达到最优的性能或一致性。

两种日志格式的区别如下所示：

statement 格式的 binlog 日志记录的是 sql 语句，用 mysqlbinlog 日志查看工具可以明文查看。

而 row 格式下的日志就算是使用 mysqlbinlog 日志工mysqlbinlog -v 参数把日志重构为 sql ，才能看见 sql 语句，WHERE 段落中的是修改前的值，SET 后面的是修改的结果。如上图第二大段，以 “BINLOG” 开头的五行加密信息，后面的则是明文 sql 语句。

使用 show variables like '%binlog_format%'; 查看当前 mysql 的日志格式。

在 mysql 的配置文件中添加 binlog_format=STATEMENT 来修改日志格式

.2 查看日志

binlog 日志由二进制格式存储，需要用 mysql 自己提供的命令工具 mysqlbinlog 来查看。 参数： - -d 指定数据库名称 - -o 忽略掉日志中的前 n 行命令 - -v 将行事件（数据变更）重构为 SQL 语句 - -vv 将行事件（数据变更）重构为 SQL 语句，并输出注释信息

.3 清理日志

清理日志分手动清理和自动清理，手动清理通过如下命令来手动删除需要删除的日志，自动清理通过 mysql 的配置文件来设置

reset master;   # 删除全部 binlog 日志，之后编号从 1 开始
purge master logs to 'mall-mysql-bin.000004';   # 删除 4 号之前的日志（不包括 4 号）
purge master logs before 'yyyy-mm-dd hh:MM:ss'; # 删除 什么时间之前产生的日志

mysql 配置文件方式删除日志

[mysqld]
# 旧版本 5.x或 8.0早期版本，，deprecate！！
expire_logs_days = 7

# 保留 7 天的 binlog，单位 秒
binlog_expire_logs_seconds = 604800

查看清理日志设置

show variables like "%binlog_expire_logs_seconds%";

查询日志

binlog 日志只是把 DML 和 DDL 语句记录起来，不包括查询语句。mysql 有个查询日志，它会记录所有的操作语句，在配置中添加如下配置即可。

# 是否开启查询日志，1 开启；0关闭；
general_log=1
# 指定日志文件的存储名称，默认名称为 当前主机名.log
general_log_file=mysql.log

检查当前数据库是否开启查询日志，用 show variables like '%general%';

慢查询日志

慢查询日志把执行时间超过参数 "long_query_time" 设置值并且扫描记录数不小于 "min_examined_row_limit" 的所有 SQL 语句给记录下来。慢查询日志默认不开启，参数 "long_query_time" 默认为 10s，它的精度可以到微秒。

在 mysql 配置文件中添加如下选项来配置慢查询日志

# 慢查询日志
slow_query_log=1
# 执行时间参数
long_query_time=2
# 记录执行较慢的管理语句，默认不会记录管理语句
log_slow_admin_statements=1
# 记录未使用索引的语句，这个一开，只要未走索引都会记录，就算查询语句是毫秒级别，也同样会记录！
log_queries_not_using_indexes=1

. 锁

.1 全局锁

全局锁对整个数据库实例加锁，加锁后整个数据库处于只读状态，后续的 DML 语句、DDL 语句都要被阻塞。一般用于对全库做逻辑备份，对所有的表进行锁定，保证数据的完整性。

加锁操作语句 flush tables with read lock; ，解锁语句 unlock tables; 。

缺点： 1. 在加锁期间，只能读，但不能更新，所有业务基本瘫痪。 2. 在从库上备份，那么在备份期间从苦不能执行主库同步过来的二进制日志（binlog），会导致主从延迟。

[!tip] 在 InnoDB 引擎中，在备份时加上 --single-transaction 参数来完成不加锁的一致性数据备份。

.2 表级锁

表级锁可以分为以下仨类： 1. 表锁 2. 元数据锁 (meta data lock，MDL) 3. 意向锁

.2.1 表锁

表锁顾名思义是对表(table)操作的锁，表锁又可分为以下两类： 1. 表共享读锁 read lock。读锁会阻塞所有会话的写操作和修改表结构操作，读操作在所有会话都能正常执行。 2. 表共享写锁 write lock。写锁只会允许当前加锁的会话执行写操作和修改表结构操作和读操作，其他会话的所有操作均被阻塞。

	锁	读	写 DDL	修改表 DML
加锁的会话	read	✅	❌	❌
其他会话	read	✅	❌	❌
加锁的会话	write	✅	✅	✅
其他会话	write	❌	❌	❌

表锁的加锁语句是 lock tables xxxx read/write;，解锁的语句为 unlock tables;。

.2.2 元数据锁 MDL

元数据锁 (meta data lock，MDL)

. MySQL 主从复制

MySQL 主从复制（Master-Slave Replication）是一种数据复制技术，用于在多个数据库服务器之间的数据同步。在主从复制架构中，一个服务器被设置为主服务器（Master），充当数据源，其他服务器被设置为从服务器（Slave），用来复制主服务器的数据。

.1 主从复制优点

mysql 主从复制优点 1. 高可用性：主库出问题能及时切换到从库，保证系统服务的持续可用性。 2. 提高整体性能和吞吐量：主从复制实现了读写分离，主库写，从库读，降低主库访问压力。主 3. 数据备份和恢复：通过主从复制，可以在从库上备份，避免影响主库的正常服务。在应对意外数据丢失、灾难恢复或误操作时，可以使用从库作为数据的备份源来进行数据恢复。

.2 主从复制原理

mysql 主从复制利用了 mysql 数据库的 binary log 日志的特点（binlog 日志会记录数据库的 DDL 和 DML 语句），把 binlog 日志传输到其他服务器，重新执行一遍 binlog 日志即可达到与主数据库数据一致的效果。

它的主要执行流程如下： 1. 主数据库接收到一个写操作（如 INSERT、UPDATE、DELETE）时，会将这个操作记录到二进制日志（Binary Log）中，将数据修改的操作按顺序记录下来。 2. 从数据库 IO 线程会自动连接主服务，从二进制中读取同步数据，记录到中继日志（Relay Log）中。 3. 从数据库的 SQL 线程会定期从中继日志中获取同步数据，写入到从数据库中。

mysql 支持一台主数据库同时向多台从库复制，从库也可以做为主库向其他从库复制，实现链状复制。

.3 实现

.3.1 master 端配置

修改配置文件 /etc/my.cnf，添加下面参数，修改配置文件后需要重启 mysql。

[mysqld]
# 设置服务器 ID，每个主服务器和从服务器都必须有唯一的 ID，取值范围为：1-2^(32)-1
server-id=1
# 是否只读，1 只读，0 读写
read-only=0
# 不需要同步的 db
#binlog-ignore-db=mysql
# 指定同步的 db
# binlog-do-db=db01

创建用于复制的用户

CREATE USER 'replicater'@'%' IDENTIFIED BY 'Root@1234';    # 注意：这里密码有强校验
GRANT REPLICATION SLAVE ON *.* TO 'replicater'@'%';

# 查看 maste 状态
show master status;

.3.2 slave 端配置

修改配置文件 /etc/my.cnf，添加下面参数，修改配置文件后需要重启 mysql。

[mysqld]
# 设置服务器 ID，每个主服务器和从服务器都必须有唯一的 ID，取值范围为：1-2^(32)-1
server-id=77
# 是否只读，1 只读，2 读写
read-only=1

建立从库与主库的联系

# 8.23 之前的配置
CHANGE REPLICATION SOURCE TO SOURCE_HOST='master_ip', SOURCE_USER='replicater', SOURCE_PASSWORD='Root@1234', SOURCE_LOG_FILE='binlog_file', SOURCE_LOG_POS=log_file_position;

# MASTER_LOG_FILE 和 MASTER_LOG_POS 的值通过 show master status; 填写

# 8.23 之后的配置
CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='replicater', MASTER_PASSWORD='Root@1234', MASTER_LOG_FILE='binlog_file', MASTER_LOG_POS=log_file_position;

启动从服务器的复制进程

# 8.23 之前的配置
start replica;
# 8.23 之后的配置
start slave;

# 查看 slave 状态
show slave status;