觉哥技术团队mysql生产实战操作指南(涵盖所有常见操作)

时间：2024-10-13 08:11:16

以下为觉哥技术团队开源项目实战参考文档

一.下载与解压mysql8 linux版本

1.1下载

配置hosts文件

先ifconfig查看ip 找到eth0 服务器ip为10.0.1.118

echo "10.0.1.118" juegedb01 >> /etc/hosts

mysql-8.0.35：百度网盘 请输入提取码

提取码：8206

1.2解压 创建文件夹及

mkdir /usr/local/mysql

将上面下载好的压缩包上传到此目录

安装 mysql8.0
cd usr/local/mysql
tar -xvf mysql-8.0.15-linux-glibc2.12-x86_64.tar
rm -f mysql-8.0.15-linux-glibc2.12-x86_64.tar
xz -d mysql-8.0.15-linux-glibc2.12-x86_64.
tar -xvf mysql-8.0.15-linux-glibc2.12-x86_64.tar
mv mysql-8.0.15-linux-glibc2.12-x86_64 /usr/local/
mv /usr/local/mysql-8.0.15-linux-glibc2.12-x86_64 /usr/local/mysql

创建需要目录

mkdir /var/lib/mysql

mkdir -p /usr/local/mysql/log

mkdir /usr/local/mysql/data
创 建 mysql 用 户 和 组 ，目 录 授 权
groupadd mysql
useradd -r -g mysql -s /bin/false mysql
chown -R mysql:mysql /usr/local/mysql
chown -R mysql:mysql /var/lib/mysql

1.3修改环境变量

问题：修改环境变量的作用是？

vi  ~/.bashrc

export JAVA_HOME=/juege/jdk/openjdk-17.0.9
export PATH=$JAVA_HOME/bin:/usr/local/mysql/mysql8/bin:$PATH

刷新环境变量

source ~/.bashrc

使用命令which mysql 检测环境变量是否配置生效

[root@kafka-broker3 mysql]# which mysql
/usr/local/mysql/bin/mysql

1.4配置参数文件/etc/

先加权限 chown -R mysql:mysql /etc/

先删除 vi /etc/文件里的内容

:%d

解释：

  • : 进入命令行模式。
  • % 表示对文件中的所有行进行操作。
  • d 是删除命令。

再复制如下内容粘贴 vi /etc/ 问题：重要参数有哪些 含义是

[mysql]
default-character-set=utf8mb4
socket=/var/lib/mysql/
[mysqld]
port = 3306
socket=/var/lib/mysql/
basedir=/usr/local/mysql
character-set-server=utf8mb4
default-storage-engine=INNODB
innodb_buffer_pool_size = 200M
max_allowed_packet=16M
explicit_defaults_for_timestamp=1
log-output=FILE
general_log = 0
general_log_file=/usr/local/mysql/log/
slow_query_log = ON
slow_query_log_file=/usr/local/mysql/log/
long_query_time=10
log-error=/usr/local/mysql/log/
default-authentication-plugin=mysql_native_password

1.5初始化

卸载主机自带的mysql避免起冲突

rpm -qa | grep mysql
rpm -e --nodeps *mysql*
rpm -qa | grep mariadb
rpm -e --nodeps mariadb*

执行如下命令初始化

/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/
到如下目录看看是否初始化成功 然后可以看到初始化的密码
cd /usr/local/mysql/log
tail -100f  可以看到如下内容有生成一个密码 说明初始化成功

1.6配置 Centos Linux 启动和停止脚本

vi /usr/lib/systemd/system/
内容如下
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=/doc/refman/en/
After=
After=
[Install]
WantedBy=
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/
LimitNOFILE = 65536
LimitNPROC = 65536

1.7配置 systemctl 启动和停止方法

systemctl daemon-reload
systemctl stop mysqld
systemctl start mysqld
systemctl enable mysqld
systemctl status mysqld
手要启动和停止 MySQL 方法
nohup /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/ &
mysqladmin -uroot -p shutdown -S /var/lib/mysql/

1.8修改密码+允许 root 远程登录

并且密码设置为了root 这里自己设置的话记得改

mysql -u root -p
use mysql;

这里报错

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

我们执行修改密码命令

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';

use mysql;

select host,user from user where user='root';
create USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';
grant all privileges on *.* to 'root'@'%' with grant option;
exit;

使用如下方式测试root账号远程连接

mysql -uroot -p -h10.0.1.118

连不通的话 执行下一步 防火墙的相关命令

1.9防火墙关闭

firewall-cmd --state
systemctl stop
systemctl disable

如果是用的云服务器记得开放对应端口

1.10使用工具测试连接 

测试连接成功

1.11卸载

就把如下用到的几个文件跟目录删除 然后重启服务器就行

rm -rf /usr/local/mysql
rm -rf /var/lib/mysql
rm -rf /etc/

二.参数文件管理及基本功能

在我们使用工具连接后可以看到mysql自带的几个库

2.1各个自带库的介绍

mysql库:

mysql核心数据库 主要存储数据库的用户 权限设置等 包含服务器运行所需的所有系统信息

sys库:

提供一组便捷的视图、函数和存储过程，以便更容易地访问 performance_schema 数据库中的性能数据和 information_schema 数据库中的元数据

information_schema:

提供关于数据库元数据的信息，比如数据库、表、列、权限等

performance_schema:

用于监控 MySQL 服务器性能。它提供低开销的方式来收集服务器性能数据

2.2查看参数的方式

##显示mysql的所有参数 无隐含参数
show variables;   
##参数模糊查询
show variables like "innodb_%";
##查看各作用域下是否自动提交的配置 session 库 全局
select @@;
select @@autocommit;
select @@global.autocommit;

2.3业务系统比较慢 尝试修改某参数使得性能提升

##？需求：目前业务系统比较慢 发现服务器内存充足 怀疑mysql的内存参数比较低 准备修改
## 需要做哪几步 

## 1.找模糊查询 然后查询结果中找到相关变量 innodb_buffer_pool_size
SHOW VARIABLES LIKE "innodb_buffer_pool%";

## 2.通过监控系统看到当前服务器最高峰阶段 内存仍有 2G内存剩余
## 所以我们直接调整到1G 应该问题不大 然后以字节为单位
SET @@global.innodb_buffer_pool_size=1073741824

SET PERSIST innodb_buffer_pool_size= 1073741824;

## 3.查看是否修改成功
SHOW VARIABLES LIKE "innodb_buffer_pool%";

## 4.发现修改成功后 看看当前连接到此实例的性能是否有提升

## 5.如果有效果说明 修改这个参数就是有用的 但是这种更改是临时的，
## 只在当前 MySQL 实例的生命周期内有效。也就是说，如果 MySQL 服务器重启了，
## 这个设置将会丢失，并恢复到配置文件中的值

## 6.在中设置参数 然后重启mysql(高峰期重启要悠着点) 再用第三步检查

##另外记得备份之前的版本 使用 mv .20240519
innodb_buffer_pool_size=1024M

2.4mysql日常维护相关日志

mysql在日常维护过程中 因为系统出现各种问题 所以需要收集各种日志文件 服务器各种状态信息

然后进行分析

##错误日志:

可以看到路径就是我之前配置的

show variables like '%log_error%'

##二进制日志:
SHOW VARIABLES LIKE '%log_bin%'

##慢查询日志:
SHOW VARIABLES LIKE '%slow_query%'
SHOW VARIABLES LIKE '%long_query%'

##全局日志:
SHOW VARIABLES LIKE '%general%'

##中继日志:
SHOW VARIABLES LIKE '%relay%'

2.5查看存储引擎相关信息

## 查看存储引擎
show engines


## 查看引擎插件 比如二进制日志插件 密码插件等
show plugins


##查看引擎状态
show engine innodb status

##查看进程数

show processlist

2.6查看字符集及比较规则

##查看字符集
show variables like '%character_set%'


##查看比较规则
SHOW VARIABLES LIKE '%collation%'

2.7查看主从库状态
show master status
show slave status

三.mysql用户权限与安全管理

3.1查看帮助

查看帮助后可以看到一些常见的相关命令

mysql> help account management
You asked for help about help category: "Account Management"
For more information, type 'help <item>', where <item> is one of the following
topics:
   ALTER RESOURCE GROUP
   ALTER USER
   CREATE RESOURCE GROUP
   CREATE ROLE
   CREATE USER
   DROP RESOURCE GROUP
   DROP ROLE
   DROP USER
   GRANT
   RENAME USER
   REVOKE
   SET DEFAULT ROLE
   SET PASSWORD
   SET RESOURCE GROUP
   SET ROLE

3.2mysql库用户表

记得针对于下面的操作授权后要刷新权限落盘 因为操作只是在内存中的更新

flush privileges

这个表的字段非常多 select * from user

以下是每一列的含义

列名 描述 示例或值类型
Host 用户可以从哪个主机连接到 MySQL 服务器 localhost, %, 192.168.1.100
User MySQL 用户名 root, admin
Password 用户的密码（已废弃） 加密后的密码字符串
Select_priv 用户是否有 SELECT 权限 Y, N
Insert_priv 用户是否有 INSERT 权限 Y, N
Update_priv 用户是否有 UPDATE 权限 Y, N
Delete_priv 用户是否有 DELETE 权限 Y, N
Create_priv 用户是否有 CREATE 权限 Y, N
Drop_priv 用户是否有 DROP 权限 Y, N
Reload_priv 用户是否有 RELOAD 权限 Y, N
Shutdown_priv 用户是否有 SHUTDOWN 权限 Y, N
Process_priv 用户是否有 PROCESS 权限 Y, N
File_priv 用户是否有 FILE 权限 Y, N
Grant_priv 用户是否有 GRANT 权限 Y, N
References_priv 用户是否有 REFERENCES 权限 Y, N
Index_priv 用户是否有 INDEX 权限 Y, N
Alter_priv 用户是否有 ALTER 权限 Y, N
Show_db_priv 用户是否有 SHOW DATABASES 权限 Y, N
Super_priv 用户是否有 SUPER 权限 Y, N
Create_tmp_table_priv 用户是否有 CREATE TEMPORARY TABLE 权限 Y, N
Lock_tables_priv 用户是否有 LOCK TABLES 权限 Y, N
Execute_priv 用户是否有 EXECUTE 权限 Y, N
Repl_slave_priv 用户是否有 REPLICATION SLAVE 权限 Y, N
Repl_client_priv 用户是否有 REPLICATION CLIENT 权限 Y, N
Create_view_priv 用户是否有 CREATE VIEW 权限 Y, N
Show_view_priv 用户是否有 SHOW VIEW 权限 Y, N
Create_routine_priv 用户是否有 CREATE ROUTINE 权限 Y, N
Alter_routine_priv 用户是否有 ALTER ROUTINE 权限 Y, N
Create_user_priv 用户是否有 CREATE USER 权限 Y, N
Event_priv 用户是否有 EVENT 权限 Y, N
Trigger_priv 用户是否有 TRIGGER 权限 Y, N
Create_tablespace_priv 用户是否有 CREATE TABLESPACE 权限 Y, N
ssl_type 定义用户连接所需的 SSL 类型 空字符串, ANY, X509, SPECIFIED
ssl_cipher 定义 SSL 密码套件 加密套件
x509_issuer 定义 SSL 证书颁发者 颁发者信息
x509_subject 定义 SSL 证书主题 主题信息
max_questions 用户每小时最大查询次数 整数
max_updates 用户每小时最大更新次数 整数
max_connections 用户每小时最大连接次数 整数
max_user_connections 用户并发连接最大数 整数
plugin 用户身份验证插件 插件名称（如 mysql_native_password）
authentication_string 用于身份验证的凭证字符串 加密后的密码或其他身份验证信息
password_expired 用户密码是否已过期 Y, N
password_last_changed 用户密码上次更改的时间 时间戳
password_lifetime 用户密码的有效期（天数） 整数
account_locked 用户账户是否被锁定 Y, N
Create_role_priv 用户是否有 CREATE ROLE 权限 Y, N
Drop_role_priv 用户是否有 DROP ROLE 权限 Y, N
password_reuse_history 密码重用历史的数量 整数
password_reuse_time 密码重用的时间间隔 整数
password_require_current 更改密码时是否要求输入当前密码 Y, N
User_attributes 用户属性的 JSON 文档 JSON 文档

3.3db表

如果说上面是全局的权限 那么这个db表就是针对于具体库的用户权限

跟上面字段基本差不多 所以不做解释 SELECT * FROM db

3.4tables_priv

针对表的权限比如可以设置某人只能查看某张表不能更新 select * from tables_priv

3.5columns_priv

针对列的权限这个一般不咋用select * from columns_priv

3.6procs_priv

针对存储过程的权限select * from procs_priv

3.7proxies_priv

针对于代理用户的权限这个跟角色相关后面会说 select * from proxies_priv

四.mysql常见概念详解

4.1中继日志（Relay Log）

是 MySQL 复制架构中从库（Slave）用于保存从主库（Master）接收到的二进制日志事件（Binary Log Events）的一种日志文件。中继日志的主要作用是确保从库能够正确地重放和应用主库上的所有变更操作，从而保持与主库的数据一致性。

中继日志的工作原理

  1. 主库写入二进制日志：

    • 主库将所有数据更改操作记录到二进制日志（Binary Log）中。这些操作包括 INSERT、UPDATE、DELETE 等。

  2. 从库读取二进制日志：

    • 从库上的 I/O 线程从主库的二进制日志中读取日志事件，并将其写入从库的中继日志中。

  3. 从库重放中继日志：

    • 从库上的 SQL 线程读取中继日志中的日志事件，并将其应用到从库的数据库中，从而实现数据的同步。

中继日志的详细过程

  1. I/O 线程：

    • 从库启动后，I/O 线程连接到主库并开始读取主库的二进制日志事件。
    • I/O 线程将读取到的事件写入从库的中继日志文件中。

  2. SQL 线程：

    • SQL 线程负责读取中继日志文件中的事件，并按顺序执行这些事件，以便将从库的数据库状态更新为与主库一致。

中继日志的用途

  • 数据同步：确保从库能够精确地重现主库上的所有数据变更，从而保持与主库的数据一致性。
  • 断点续传：如果从库的连接中断，中继日志可以帮助从库在重新连接后从断开的地方继续同步数据，而无需从头开始。
  • 故障恢复：在从库重启或故障恢复后，可以使用中继日志确保所有未完成的事务能够被正确应用，从而避免数据不一致。

中继日志的管理

MySQL 提供了多种配置和管理中继日志的选项，例如：

  • relay_log：用于指定中继日志文件的名称和位置。
  • relay_log_purge：控制是否自动清理已应用的中继日志文件。
  • relay_log_recovery：启用自动中继日志恢复，在从库崩溃后确保数据一致性。

相关命令

  • 查看中继日志状态：

    SHOW SLAVE STATUS\G
    sql

    复制代码

    SHOW SLAVE STATUS\G

    该命令可以查看从库的复制状态，包括中继日志文件的位置和应用进度。

  • 重置中继日志：

    RESET SLAVE;
    sql

    复制代码

    RESET SLAVE;

    该命令用于清除所有中继日志文件，并重新开始复制过程。

结论

中继日志在 MySQL 复制架构中扮演着关键角色，通过记录和重放主库的二进制日志事件，从库能够保持与主库的一致性，从而实现高可用性和数据冗余。

五.mysql sql实战调优\

题1 跟我说下建立索引你一般怎样建立 取哪些字段 按怎样的顺序

开放性思考

题2 如下查询是否走索引 

索引

KEY `idx_shopid_created_status` (`shop_id`, `created_at`, `order_status`)
SQL语句

select * from _order where shop_id = 1 and created_at > '2021-01-01 00:00:00' and order_status = 10

题3:你现在是我公司员工 现在数据库性能不行 我告诉你修改下innodb_buffer_pool_size参数

你会怎么做（ 操作在上面） 然后说下修改这个参数的作用

1. 缓存数据页

innodb_buffer_pool 用于缓存从磁盘读取的表和索引数据页。通过将数据缓存到内存中，可以显著减少磁盘 I/O 操作，从而提高查询性能。大多数读取操作可以直接从缓存中获取数据，而不必每次都从磁盘读取。

2. 缓存写操作

InnoDB 使用延迟写的策略，将修改过的数据页缓存在 innodb_buffer_pool 中，而不是立即写入磁盘。这种策略可以将多个写操作合并成一次磁盘写操作，从而提高写性能。

3. 缓解磁盘 I/O 瓶颈

通过将常用数据缓存到内存中，innodb_buffer_pool 大大减少了磁盘 I/O 操作。这在处理大量并发查询时尤为重要，因为磁盘 I/O 通常是性能瓶颈。

4. 提升事务性能

由于 innodb_buffer_pool 缓存了大部分数据，事务在读取和修改数据时可以直接在内存中操作，这大大提升了事务的性能。

题4：不满足索引最左前缀前缀匹配原则一定不走索引么？请给我列出两种情况

不一定答案分多种情况 这里举出两种

(1)我有一个表有A,B,C,D,E...几个字段，我创建了一个联合索引A,B,C，请问SELECT B,C, FROM TABLE WHERE B= XX AND C =XX会走索引吗？

这题涉及性能分析

(2)我有一个表有A,B,C,D,E,F....多个字段，我创建了一个联合索引,B,C，请问SELECT A,B,C,D,E,F... FROM TABLE WHERE B= XX AND C =XX 会走索引吗？

优化之后

SELECT A,B,C,D,E,F... FROM TABLE WHERE A=1 B= XX AND C =XX 

union

SELECT A,B,C,D,E,F... FROM TABLE WHERE  A=0  B= XX AND C =XX

这题涉及对基数/散列度 +性能分析 举例A字段为性别字段

在数据更改或者删除新增达到一定百分比时 会更新索引中的字段的基数

题5：数据类型的谨慎考虑 举例 安全第一 能整不字 能小不大 能定不变 

建表时IP地址 你会定义成什么类型

把 IP 地址存成 UNSIGNED INT
        很多程序员都会创建一个 VARCHAR(15) 字段来存放字符串形式的 IP 而不是
整形的 IP。如果你用整形来存放，只需要 4 个字节，并且你可以有定长的字
段。而且，这会为你带来查询上的优势，尤其是当你需要使用这样的 WHERE 条
件：IP between ip1 and ip2。
        我们必需要使用 UNSIGNED INT，因为 IP 地址会使用整个 32 位的无符号整
形。
        而你的查询，你可以使用 INET_ATON() 来把一个字符串 IP 转成一个整形，
并使用 INET_NTOA() 把一个整形转成一个字符串 IP。在 PHP 中，也有这样的函
数 ip2long() 和 long2ip()。

  • 节省存储空间：

    • UNSIGNED INT 只需要 4 个字节，而 VARCHAR(15) 需要最多 15 个字节。
    • 大量存储 IP 地址时，空间节省非常明显。

  • 提高查询性能：

    • 整数比较比字符串比较更快。
    • 可以更高效地进行范围查询（例如，使用 BETWEEN）。

  • 数据完整性：

    • 使用 UNSIGNED INT 可以确保存储的值始终是有效的 IP 地址范围（0 到 4294967295）。
    • 减少了存储无效 IP 地址的可能性。

题6 能非空 不空

当查询条件包含   name IS NOT NULL 的条件时

如果我们的name 字段在建表的时候没有定义为not null也就是非空的话 那么这个查询条件会生效并且浪费时间

因为我们插入数据的时候name都是有值的

如果表创建的时候定义了not null 那么 查询的时候优化器会把这个条件直接去除

题7：当我要删除或者插入大量的数据时我应该咋做

        如果你需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT 查询，
你需要非常小心，要避免你的操作让你的整个网站停止相应。因为这两个操作
是会锁表的，表一锁住了，别的操作都进不来了。
        如果你把你的表锁上一段时间，比如 30 秒钟，那么对于一个有很高访问量
的站点来说，这 30 秒所积累的访问进程/线程，数据库链接，打开的文件数，
可能不仅仅会让你泊 WEB 服务 Crash，还可能会让你的整台服务器马上掛了
        所以，如果你有一个大的处理，你定你一定把其拆分，使用 LIMIT 条件是
一个好的方法。下面是一个示例：
最好低谷区进行操作

题8:请把下面的子查询优化为连接查询 并创建合适索引 并说说这样做的好处

子查询在select语句中时,实际上mysql自己也会优化 但是你直接写好的话省了他分析优化的时间

考察知识点主要有：sql查询顺序分析 性能分析 主次表区分 无效查询避免 转换条件

    

  1. 

    

    
SELECT 
    

    2. 

  2. 

    

        c.customer_id,
    

    3. 

  3. 

    

        c.customer_name,
    

    4. 

  4. 

    

        (
    

    5. 

  5. 

    

            SELECT MAX(order_date)
    

    6. 

  6. 

    

            FROM orders o
    

    7. 

  7. 

    

            WHERE o.customer_id = c.customer_id
    

    8. 

  8. 

    

        ) AS latest_order_date
    

    9. 

  9. 

    

    
FROM customers c;
    

    10.
优化后 
    

  1. 

    

    
SELECT 
    

    2. 

  2. 

    

        c.customer_id,
    

    3. 

  3. 

    

        c.customer_name,
    

    4. 

  4. 

    

        o.latest_order_date
    

    5. 

  5. 

    

    
FROM 
    

    6. 

  6. 

    

        customers c
    

    7. 

  7. 

    

    
JOIN 
    

    8. 

  8. 

    

        (
    

    9. 

  9. 

    

            SELECT 
    

    10. 

  10. 

    

                customer_id,
    

    11. 

  11. 

    

                MAX(order_date) AS latest_order_date
    

    12. 

  12. 

    

            FROM 
    

    13. 

  13. 

    

                orders
    

    14. 

  14. 

    

            GROUP BY 
    

    15. 

  15. 

    

                customer_id
    

    16. 

  16. 

    

        ) o
    

    17. 

  17. 

    

    
ON 
    

    18. 

  18. 

    

        c.customer_id = o.customer_id;
    

    19.

  • 性能优化：

    • 连接查询通常比子查询更高效。子查询可能会在每个外部查询的行上执行一次，而连接查询则可以一次性处理所有行，减少了查询的次数和负担。
    • 连接查询更容易优化，数据库优化器可以更好地利用索引和其他优化技术来执行连接操作。

  • 可读性提升：

    • 连接查询更直观，更易于理解和维护。它清晰地表达了查询的目的，将不同表之间的关系展现出来，使查询语句更加清晰易懂。

  • 可扩展性增强：

    • 连接查询更容易扩展，可以轻松地添加更多的连接条件和逻辑。
    • 当查询需要涉及多个表时，使用连接查询可以使查询语句结构更清晰，更易于添加新的表和条件。

  • 更容易优化：

    • 连接查询可以直接利用数据库引擎的优化机制，如索引、执行计划等，使得优化过程更加简单和直接。

题9:如果用查询条件 in (b,a,d,c,c,e,f) 怎样优化

select .... from A where  xx in (a,b,c,d,c,e,f)

index(xx)

题10：什么情况建立索引只需要字段的前面部分几个字符就行

姓名是个典型的例子 大部分只需要前面部分几个字即可

当进行模糊搜索时，可能只需要索引字段的前几个字符来提高查询效率。例如，当在一个包含大量用户姓名的表中进行姓名搜索时，可以只对姓名的前几个字符建立索引，以加速查询

题11:在 MySQL 中，如果我想要配置如下三项：

  1. 开启慢查询日志
  2. 将超过 5 秒的查询视为慢查询
  3. 记录未使用索引的查询的日志

在 MySQL 配置文件（如 或 ）中，可以这样配置：

    

  1. 

    

    [mysqld]
    

    2. 

  2. 

    

    slow_query_log = ON

    

    3. 

  3. 

    

    slow_query_log_file = /var/log/mysql/
    

    4. 

  4. 

    

    long_query_time = 5

    

    5. 

  5. 

    

    log_queries_not_using_indexes = ON

    

    6.

相关文章