8.19 MySQL

**▶ 忘记MySQL的root密码重置方法：**

1.修改/etc/my.cnf文件，最后一行添加：skip-grant-tables

2.重启MySQL服务

3.登录MySQL，提示输入密码的时候，直接回车登录MySQL

4.update mysql.user set authentication_string='' where user='root';

flush privileges; #将密码重置为空

5.删除my.cnf文件最后添加的一行，重启mysql，使用空密码登录

6.修改密码：

alter user 'root'@'%' identified by '密码';

flush privileges;

7.使用新密码登录

**▶ 数据类型：**

• tinyint：小整型，1B，8位 0-256

• int：整型，8B，42亿左右个数字

• decimal：小数值

• char varchar

• date time year datetime timestamp(最多到2038-01-19 03:14:07)

**▶ 如何配置MySQL密码策略（长度4位，等级LOW）并永久生效？**

• 临时生效（登录MySQL）

set global validate_password.length=4;

set global validate_password.policy=LOW;

• 永久生效：

1.备份配置文件：cp /etc/my.cnf{,-bak};

2.在/etc/my.cnf添加：

validate_password.length=4

validate_password.policy=LOW

3.重启服务

• select语句执行顺序：

from--->where--->select--->group by/order by--->limit

**▶ DCL数据控制语言：**

• 查询当前可以登录mysql的用户信息：

select * from mysql.user;

• 切换到mysql数据库：

use mysql;

• 创建用户：

create user '用户名'@'%' identified by '密码' 创建远程登录用户%

• 修改用户密码：

alter user '用户名'@'%' identified with mysql_native_password by '密码';

• 查询用户权限：

show grants for '用户名'@'%';

• 授权：

grant all on *.* to '用户名'@'%';

**▪ mysql中账号权限说明：**

all：所有权限 select：查询权限

insert：插入数据权限 update：修改数据权限

delete：删除数据权限 alter：修改表权限

drop：删除数据库/表/视图权限 create：创建数据库/表权限

• 撤销权限：

revoke all on *.* from '用户名'@'%';

• 删除用户：

drop user '用户名'@'%';

**▶ MySQL的日志文件:(错误日志 二进制日志 查询日志 慢查询日志)**

**• 错误日志：（默认开启）**

查看错误日志文件的位置：

show variables like '%log_error%';

**• 二进制日志：（默认开启）**

▪ 查看二进制日志文件的位置：

show variables like '%log_bin%';

▪ 查看二进制日志文件记录的格式：

show variables like '%binlog_format%';

▪ 二进制日志格式说明：

**STATEMENT** ：基于SQL语句的日志记录，对数据进行修改的SQL都会记录在日志文件中

**ROW** ：基于行的日志记录，记录的是每一行的数据变更（默认）

**MIXED** ：混合了以上两种格式，默认采用STATEMENT，在某些特殊情况下会自动切换为ROW进行记录

▪ 查看二进制日志信息：

mysqlbinlog [参数选项] 文件名

-d：指定数据库名称

-o：忽略掉日志中的前n行命令

-v：将行事件（数据变更）重构为SQL语句

-vv：将行事件重构为SQL语句，并输出注释信息

mysqlbinlog --database db01 --base64-output=decode-rows binlog.000002 -v

**• 查询日志：（默认关闭）**

▪ 查看查询日志是否配置了：

show variables like '%general%';

▪ 配置/etc/my.cnf开启查询日志：

general_log=1 0表示关闭，1表示开启

general_log_file=mysql_query.log #指定日志文件名，默认文件名位host_name.log

开启了查询日志后，在MySQL的/var/lib/mysq目录下就会出现日志文件

**• 慢查询日志：（默认关闭）记录了所有执行时间超过参数设置值的SQL语句**

▪ 开启慢查询日志：(/etc/my.cnf)

slow_query_log=1

long_query_time=2 #设定执行时间参数

**▶ 索引**

• 索引基本操作：

▪ create index 索引名 on 表名(字段名); # 普通索引

▪ create unique index 索引名 on 表名(字段名); # 唯一索引

▪ create index 索引名 on 表名(字段1，字段2); #联合索引

▪ drop index 索引名 on 表名; #删除索引

▪ show index from 表名; #查看表的索引

▪ explain select语句 #查看SQL是否走索引

• 索引优化技巧：

▪ 高选择性列优先建索引

▪ 频繁查询条件建索引

▪ 覆盖索引能减少回表，提高性能

▪ 避免过多索引，过多会影响增删改性能

**▶ 多表查询**

• 左连接：left join 左表所有行，右表不匹配的填充NULL

• 右连接：right join 右表所有行，左表不匹配的填充NULL

• 内连接：inner join 返回两个表中匹配的行（交集）

**▶ 数据备份**

• mysqldump：较大数据备份和恢复较慢，数据50G是一个门槛

▪ 命令格式：mysqldump -h主机名 -P端口 -u用户名 -p密码 -databases数据库名 > 文件名.sql

▪ 备份所有数据库：

mysqldump -uroot -pAa123qwe --all-databases >allbackup.sql

▪ 备份指定数据库：

mysqldump -uroot -pAa123qwe --databases db01 > db01.sql

▪ 恢复所有数据库：

mysql -uroot -pAa123qwe < allbackup.sql

▪ 恢复指定数据库：

mysql -uroot -pAa123qwe <db01.sql

**▶ 主从复制**

**• 主库配置（master）：**

1.修改配置文件/etc/my.cnf：

server-id=1

binglog_format=ROW

gtid_mode=ON #开启GTID复制

enforce_gtid_consistency=ON #GTID强一致性，不安全SQL直接报错不能执行

2.重启服务

3.创建复制用户：

在主库执行：

cteate user 'repl'@'%' identified with mysql_native_password by '123456';

grant replication slave on *.* to 'repl'@'%';

replication salve：允许从库连接主库，拉取binlog日志

replication client：允许执行show master status、show slave status，方便运维查看状态

flush privileges;

4.导出数据（初始化从库）

如果主库已有数据，导出：

mysqldump -uroot -p --all-databases --routines --events --set-gtid-pured=on --lock-all-tables > /root/db.sql

tips：--set-gtid-purged=on #关键配置项，确保从库的gtid信息跟主库一致，避免从库和主库数据不一致

**• 从库配置**

1.修改配置文件/etc/my.cnf：

server-id=2

gtid_mode=ON

enforce_gtid_consistency=ON

2.重启MySQL

3.导入主库数据：

mysql -uroot -p </root/dbdump.sql

4.配置GTID复制：

mysql> change replication source to source_host='主库ip',

-> source_user='repl',

-> source_password='123456',

-> source_auto_position=1;

5.启动复制：

start replica;

6.检查：

show replica status\G;

确认:

Replica_IO_Running: Yes

Replica_SQL_Running: Yes

Auto_Position: 1

**▶ MySQL数据库引擎**

InnnoDB（默认）：

支持事务，外键，行级锁，崩溃恢复优秀，ACID兼容，存储限制64TB，全文索引

A原子性 C一致性 I隔离性 D持久性

**如何选择存储引擎：**

1.需要事务支持---------->InnoDB

2.大量select查询-------->MyISAM

3.临时数据处理---------->MEMORY

4.历史数据归档---------->ARCHIVE

5.数据交换需求---------->CSV

• 查看和设置存储引擎：

show engines; #查看支持的引擎

show table status like '表名'; #查看表的引擎

create table 表名 (...) engine=InnoDB; #创建表时指定引擎

alter table 表名 engine=InnoDB; #修改现有表的引擎

**▶ mysql性能优化**

**• 架构设计优化**

1.读写分离

▪ 主库负责写，从库负责读，分散压力

▪ 使用中间件(如ProxySQL、MyCat)或程序层实现读写分离

2.分库分表

▪ 垂直分库，水平分表

▪ 建立缓存层（高频读操作通过缓存减少压力，如Redis）

3.异步处理

非实时任务（如日志记录、消息通知）通过消息队列异步处理

**• SQL查询优化**

1.避免低效查询

▪ 禁止select *（不走索引）

▪ 避免复杂子查询，改用join或视图

▪ 较少like '%keyword%'模糊查询，优先使用全文索引

2.使用explain分析执行计划

3.优化join操作

▪ 确保join字段有索引，小表作为驱动表

left：左表为驱动表

right：右表为驱动表

inner：由驱动器自动选择

▪ 避免笛卡尔积（无条件的join）

4.批量操作替代循环

▪ 用insert into ... values (...),(...)替代多次单条插入

▪ 用update ... where in(...)替代逐条更新

**• 索引优化**

1.合理创建索引

▪ 高频查询字段创建索引

▪ 避免滥用索引

▪ 覆盖索引，查询字段全部在索引中，无需回表查询，性能高

2.定期维护索引

**• 配置参数优化**

1.内存相关参数

▪ innodb_buffer_pool_size：设置为物理内存的70%-80%

2.连接与线程

▪ max_connections：根据业务负载调整最大连接数，避免浪费资源

▪ thread_cache_size：缓存空闲线程，减少线程创建开销

3.日志与持久化

▪ innodb_flush_log_at_trx_commit：

值为1（默认，强一致，每次提交刷盘，性能低）

值为2（每秒刷盘，性能高但可能都是1秒数据）

▪ sync_binlog：控制二进制日志刷盘策略

4.其他关键参数

▪ query_cache_type：在高并发场景下建议关闭查询缓存（设为0）

▪ innodb_io_capacity：根据磁盘性能调整（如SSD可设为20000）

**• 硬件与存储优化**

1.使用SSD替代HDD

2.RAID配置

3.内存扩容

**• 监控与维护**

1.慢查询日志开启，定期分析并优化耗时SQL

2.性能监控工具

▪ show status：查看QPS（每秒查询数）、TPS（每秒任务数）、连接数等

▪ show processlst：查看当前活跃线程

3.定期维护

▪ 清理历史数据

▪ 优化表结构

**• 高级优化技术**

1.使用列式存储引擎

针对分析性场景，使用列式存储（如ClickHouse、TiDB）

2.并行查询

MySQL 8.0+支持并行查询，适用于大表扫描

3.自适应哈希索引

InnoDB自动为高频访问的索引页创建哈希索引（通过innodb_adaptive_hash_index控制）

优化步骤总结

1. 定位瓶颈：通过监控工具分析慢查询、CPU、I/O、锁竞争。

2. 优化 SQL 和索引：解决80%的性能问题。

3. 调整配置参数：根据硬件和负载优化内存、连接等参数。

4. 架构升级：读写分离、分库分表、引入缓存。

5. 持续监控：优化是一个迭代过程，需长期跟踪。

**▶ MySQL主从切换**

1.检查从库状态：show slave status\G;

2.提升从库为主库

在从库中执行：

stop slave;

reset slave all;

reset master; #清空binlog，生成新主binlog

3.修改配置/etc/my.cnf

server-id=2

gtid_mode=ON

enforce_gtid_consistency=ON

log_bin=mysql-bin

4.在旧主库上执行：

change master to

master_host='新主库IP',

master_user='repl',

master_password='123456',

master_auto_position=1;

start slave;

5.验证：show slave status\G;