一、MySQL基础

常见数据库（从高到低）

Oracle关系型
MySQL关系型
Microsoft SQL Server关系型
PostgreSQL关系型
MongoDB文档数据库
IBM Db2关系型
Redis键值对数据库
Elasticsearch搜索和数据分析引擎
SQLite关系型
Microsoft Access关系型

重置mysql密码方法（忘记密码）

在/etc/my.cnf配置文件下添加 skip-grant-tables,重启服务
然后进入mysql，直接回车，不需要输密码

清空密码

1 2	update mysql.user set authentication_string='' where user='root'; flush privileges;

将刚才在配置文件中添加的那一行删除，重启服务，重新进mysql，不用输密码

修改密码

1 2	alter user 'root'@'%' identified by '123456'; flush privileges;

然后退出去重新用密码登录就好了。

SQL的四种结构化语言

DDL(Data Definition Language) 数据定义语言，create
DML(Data Manipulation Language) 数据操作语言，insert，delete，update
DQL(Data Query Language) 数据查询语言，select
DCL(Data Control Language) 数据控制语言，用来创建用户，控制数据库访问权限，alter

drop, truncate, delete三者的区别

drop用来删除数据库和表
truncate用来一次性删除表中数据（保留表结构）
delete可以用来删除表中单行数据或者全部数据（保留表结构）

mysql的日志文件

查询日志文件：show variables like ‘%log_error%’;

错误日志log_error，记录服务的错误信息，路径一般在/var/log/mysqld.log
二进制日志log_bin，记录对数据库进行操作的所有语句以及数据，以二进制保存，可用于恢复数据，其支持三个选项。

statement 日志小，记录sql语句，对数据的修改都会记录，但是对一些特殊函数会记录不准
row 日志大，记录更准确，性能消耗大
mixed 两者的结合，默认statement，在需要的时候自动用row

查询日志general，默认关闭，记录查询语句
慢查询日志slow_query_log,记录查询耗时长的语句，时间可自定义，查询日志和慢查询都在配置文件中定义

二、备份

备份类别

全量备份-将数据库的所有数据进行备份
增量备份-在全量备份的基础上，记录有变动的数据，且是链式备份，下一次的增量是依托于上一次的增量备份，这就导致如果前一次的增备出问题了，后一次就没用了
差异备份-和增备类似，区别就是每次差备都是单独存在

备份实操

逻辑备份 mysqldump

通过导出数据库文件来实现
msyqldump -h 192.168.146.133 -uroot -p –all-databases > ~/

xtrabackup物理备份

介绍

xtrabackup是一款开源的mysql热备工具，目前有2.4.x和8.0.x两个版本，对应mysql的5.x和8.x

实操

先下载工具，在使用rpm包安装前，先下载对应密钥，否则会报错(如图)

dnf install -y libev lz4 perl-DBD-MySQL rsync zstd
rpm -ivh percona-xtrabackup-80-8.0.35-34.1.el9.x86_64.rpm

创建备份目录并修改权限为mysql，然后进行备份

mkdir /var/xtrabackup
chown -R mysql.mysql /var/xtrabackup
#全备
xtrabackup -uroot -p123456 --backup --target-dir=/var/xtrabackup

#增备
xtrabackup -uroot -p123456 --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/full

#差备
xtrabackup -uroot -p123456 --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/full

模拟故障进行恢复

systemctl stop msyqld
rm -rf /var/lib/mysql
#全量回复,合并数据文件和日志文件
xtrabackup --prepare --apply-log-only --target-dir=/var/xtrabackup

#还原数据
xtrabackup --copy-back --target-dir=/var/xtrabackup
chown -R mysql.mysql /var/lib/mysql

gtid主从同步

原理

在两台服务器上实现，主要原理就是主库的二进制文件，然后从库有两个线程，IOThread,SQLThread,IO负责读取主库的binlog，然后生成中继日志replay.log, SQL进行执行写入。一般主库负责写，从库负责读

实现方法

主库：192.168.146.130
从库：192.168.146.133
mysql版本：8x

在主库和从库的配置文件my.cnf中开启gtid(记得重启服务)

1
2
3

server-id =1 #主从的配置文件中这个字段不能相同，必须唯一
gtid_mode =on
enforce_gtid_consistency=on #强制开启

导出所有数据库信息，并传给从库

1 2	mysqldump -uroot -p --all-databases --routines --events --set-gtid-purged=on --lock-all-tables > db.sql #set-git-purged=on，关键配置项，确保从库的信息和主库一致

创建同步账号

1
2
3

create user 'repl'@'192.168.146.133' identified with mysql_native_password by 'repl_pass';
grant replication slave on *.* to 'repl'@'192.168.146.133';
flush privileges;

从库导入数据, 并开启gtid复制

mysql -uroot -p < db.sql

change replication source to source_host='192.168.146.130',source_user='repl',
source_password='repl_pass',source_auto_position=1

启动replica并查看是否成功

start replica;
show replica status\G;

#确保Replica_IO_Running=yes,Replica_SQL_Running=yes,auto_position=1

测试，在主库创建一个数据库，然后看从库是否也同步了

三、MGR集群

作用

MGR集群是实现mysql高可用的一种方式，至少三台起，一主多从，实现数据同步，从（slave）还能作为其他服务器的主。
当主（master）挂了之后，会自动选新主，选主原则是Raft-like majority + GTID 排序 + server_id 来选主
选新主的方式有：1.根据server-id最小的来，2.超半数节点存活才能选出，3.gtid最大原则，4. 投票过半数原则
单主模式下，主负责写，从负责读

搭建过程

MGR port:33061
master:192.168.146.130
slave1:192.168.146.133
slave2:192.168.146.134

开启mgr服务，在配置文件中修改，修改完需要重启服务生效

#MGR集群
server_id=130 #根据不同服务器更改，不能相同
log_bin=binlog
binlog_format=ROW
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64
#组名，每个mgr集群必须唯一，用于识别组
loose-group_replication_group_name="aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"
#loose-前缀允许不报错，off表示mysql启动时不自动加入组
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address="master:33061" #修改为当前主机名或者IP，这里写master是因为在/etc/hosts下做了IP映射
loose-group_replication_group_seeds="master:33061,slave1:33061,slave2:33061"
loose-group_replication_bootstrap_group=OFF
loose-group_replication_single_primary_mode=ON
#用于多主模式冲突检查。单主模式下设置off
loose-group_replication_enforce_update_everywhere_checks=OFF
#加载group_replication插件, 否则无法复制
plugin_load_add='group_replication.so'

在master下创建复制账号并首次启动

create user 'mgruser'@'%' identified with mysql_native_password by '123456';
grant replication slave,replication client on *.* to 'mgruser'@'%';
flush privileges;

#临时引导集群，后续谁是新主就不是自己能干涉的
set global group_replication_bootstrap_group=on;
start group_replication;
set global group_replication_bootstrap_group=off;

slave节点，清理残留状态（如果之前有尝试加入）

1 2	stop group_replication; reset slave all for channel 'group_replication_recovery';

slave: 两台slave都配置恢复通道账号

1
2
3

change replication source to source_user='mgruser',source_password='123456' for channel 'group_replication_recovery';

start group_replication;

查询验证集群状态

select member_host,member_state,menber_role from performance_schema.replication_group_members;

#预期输出
+-------------+--------------+-------------+
| member_host | member_state | member_role |
+-------------+--------------+-------------+
| slave2      | ONLINE       | SECONDARY   |
| master      | ONLINE       | PRIMARY     |
| slave1      | ONLINE       | SECONDARY   |
+-------------+--------------+-------------+

故障模拟，测试集群是否正常

systemctl stop mysqld #停止master服务，看是否会选新主
systemctl start mysqld #开启master服务，重新加入集群
start group_replication;
#理论上应该启动后加入成功并且状态为online，但在我操作过程中，启动后变成了recovery，一直online不了

故障处理

故障描述：在我模拟故障后重启，master一直处于recovery状态，不管怎么刷新状态都没用，等了一会也是，部分人也是这种情况
出现原因：当master没有停止之前，他为主，其他两台slave为从，slave是同步的主的数据，然后master停止后，新主为其他机器，当我重新启动master并加入集群后，在停止的这段期间，新主会产生新的数据和事物，这就导致master的gtid和新主的gtid不一致，让master一直无法连接集群。

解决方法：既然原因已经知道了，那么解决起来也就变得简单了，将新主的gtid赋值给recovery的机器就好

#先停止recovery的group_replication
新主：select @@global.gtid_executed;
recovery: set global gtid_purged='新主的gtid'

#赋值完gtid后，重新配置恢复通道启动即可

四、MySQL性能优化（熟记）

优化步骤总结

定位瓶颈：通过监控工具分析慢查询、CPU、IO、锁竞争
优化SQL和索引：解决80%的性能问题
调整配置参数：根据硬件和负载优化内存，连接等参数
架构升级：读写分离、分库分表、引入缓存
持续监控：优化是一个迭代过程，需要长期跟踪

展开细说

架构设计优化

读写分离

主库负责写操作，从库负责读操作，分散压力
使用中间件（入ProxySQL）或程序层实现读写分离

分库分表

垂直分库：按业务拆分数据库（如订单库，用户库）
水平分表：按规则（如哈希，时间）拆分大表
使用分片中间件（如ShardingSphere,Vitess）
缓存层：高频读操作通过缓存（如redis，memcached）减少数据库压力，合理设置缓存失效策略，避免缓存穿透/雪崩

异步处理

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

SQL查询优化

避免低效查询

禁止select *，仅查询需要的字段
避免复杂子查询，该用join或临时表
减少like模糊查询，优先使用全文索引

使用explain分析执行计划

通过explain或explain analyze查看查询是否走索引、扫描行数，是否全表扫描

优化join操作

确保join字段有索引，小表作为驱动表
避免笛卡尔积（无条件的join，要加on）

批量操作替代循环

用insert into … values(…),(…),…替代多次单条插入
用update … where in(…) 替代逐条更新

索引优化

合理创建索引

高频出现在where,order by,join后的字段添加索引
避免索引滥用，单表最多不超过5个

定期维护索引，删除冗余索引，修复碎片化索引

analyze table users; –更新统计信息
optimize table users; –重建表并优化索引

配置参数优化

内存相关参数

innodb_buffer_pool_size: 设置为物理缓存的70-80%，缓存数据和索引
key_buffer_size: MyISAM引擎的索引缓存，非myisam表建议设较小值

连接与线程

max_connections: 根据业务负载调整最大连接数
thread_cache-size: 缓存空闲线程，减少线程创建开销

日志与持久化

innodb_flush_log_at_trx_commit:1或2 # 1：默认，强一致，性能低，2：每秒刷盘，性能高但可能就是一秒数据
sync_binlog: 控制二进制日志刷盘策略

其他关键参数

query_cache_type: 在高并发写场景下建议关闭查询缓存，设为0
innodb_io_capacity: 根据磁盘性能调整，ssd可设为20000

硬件存储优化

使用SSD替代HDD，提升磁盘IO性能，尤其是随机读写场景
RAID配置，RAID10提供高读写性能和数据冗余
内存扩容，增大内存减少磁盘IO，尤其是innodb_buffer_pool_size

监控与维护

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

set global slow_query_log=on;
set global long_query_time=2; #超过两秒的记录

性能监控工具

show status：查看QPS（每秒最大查询数），TPS（每秒最大任务数）、连接数等
show processlist：查看当前活跃线程
外部工具： Prometheus+Grafana, Percona Monitoring and Management(PMM)

定期维护

清理历史数据（如归档旧表）
优化表结构（如字段类型、拆分大字段）

Day13-Mysql