MySQL数据库学习记录

索引

EXPLAIN

使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句

1、使用方法

Explain+SQL 语句或者 Explain+ tablename

2、返回信息

image.png

id：select 查询的序列号,包含一组数字，表示查询中执行 select 子句或操作表的顺序。
id相同，执行顺序由上至下
id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。
有相同也有不同，id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行衍生=DERIVED。
注：id号每个号码，表示一趟独立的查询。一个sql的查询趟数越少越好

select_type：查询的类型，主要是用于区别普通查询，联合查询，子查询等的复杂查询。

SIMPLE：单表查询
explain select * from t1;
PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为Primary
explain select * from (select t1.content from t1 ) a;
DERIVED：在FROM列表中包含的子查询被标记为DERIVED(衍生)，MySQL会递归执行这些子查询，把结果放到临时表中。
explain select * from (select t1.content from t1 ) a;
SUBQUERY：在SELECT或WHERE列表中进行了子查询
explain select t2.id from t2 where t2.id = (select t3.id from t3 where t3.id =2);
DEPENDENT SUBQUERY ：在 SELECT 或 WHERE 列表中包含了子查询,子查询基于外层。
explain select t2.id from t2 where t2.id in (select t3.id from t3 where t3.id =2);
都是where后的条件，subquery是单个值，dependent subquery是一组值。
UNCACHEABLE SUBQUREY ：当使用了@@来引用系统变量的时候，不会使用缓存。
UNION：若第二个 SELECT 出现在 UNION 之后，则被标记为 UNION；若 UNION 包含在 FROM 子句的子查询中,外层
SELECT将被标记为： DERIVED。

数据库基础知识

1、DDL、DML、DQL、DCL 分别是什么？

DDL（Data Definition Language）语句：数据定义语言，主要是进行定义/改变表的结构、数据类型、表之间的链接等操作。常用的语句关键字有 CREATE、DROP、ALTER 等。
DML（Data Manipulation Language）语句：数据操纵语言，主要是对数据进行增加、删除、修改操作。常用的语句关键字有 INSERT、UPDATE、DELETE 等。
DQL （Data Query Language）语句：数据查询语言，主要是对数据进行查询操作。常用关键字有 SELECT、FROM、WHERE 等。
DCL（Data Control Language）语句：数据控制语言，主要是用来设置/更改数据库用户权限。常用关键字有 GRANT、REVOKE 等。

mysql集群搭建

1、配置Ubuntu环境

安装ubuntu以后需要手动安装ssh，否则xshell连接不上22端口

sudo apt update：
sudo apt install openssh-server

ubuntu安装mysql

添加 MySQL 5.7 /8.0 APT 仓库

sudo apt-get install software-properties-common
sudo apt-key adv --fetch-keys https://repo.mysql.com/RPM-GPG-KEY-mysql
sudo sh -c 'echo "deb http://repo.mysql.com/apt/ubuntu/ $(lsb_release -cs) mysql-5.7" >> /etc/apt/sources.list.d/mysql.list'
# sudo sh -c 'echo "deb http://repo.mysql.com/apt/ubuntu/ $(lsb_release -cs) mysql-8.0" >> /etc/apt/sources.list.d/mysql.list'
更新apt缓存
sudo apt-get update
安装 MySQL 服务器
sudo apt-get install mysql-server
验证安装状态
sudo systemctl status mysql
登录mysql
sudo mysql -u root -p

mysql密码不对问题：ERROR 1698 (28000): Access denied for user 'root'@'localhost'

首先去查找系统给生成的用户名密码：

sudo cat /etc/mysql/debian.cnf

image.png

查看user表中的信息

use mysql;
select user,plugin,authentication_string from user;

如果root用户的plugin 为auth_socket；修改用户密码格式。

update user set plugin='mysql_native_password' where user='root'; # 修改其密码格式
select user,plugin from user; # 查询其用户
flush privileges;#刷新权限
# 修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'your_new_password';
# 重启mysql
sudo systemctl restart mysql

mysql创建新用户并授权

# 创建远程用户
CREATE USER 'remote_user'@'%' IDENTIFIED BY 'password';
# 授权用户访问所有数据库
GRANT ALL PRIVILEGES ON *.* TO 'remote_user'@'%';
# 授权用户访问特定数据库mydatabase
# GRANT ALL PRIVILEGES ON mydatabase.* TO 'remote_user'@'%';
# 刷新权限
FLUSH PRIVILEGES;
# 查询权限
select user,plugin,host from user;

远程连接mysql时报错，“Public Key Retrieval is not allowed”

在使用MySQL数据库时，如果你在连接数据库时遇到“Public Key Retrieval is not allowed”的错误，这通常是因为你在尝试使用mysql_native_password插件以外的密码认证插件（例如caching_sha2_password），而没有正确地配置客户端来处理这种情况。
原因
从MySQL 8.0开始，默认的密码认证插件是caching_sha2_password，而一些旧的客户端库（如Python的mysql-connector-python）默认不兼容这个插件。当你试图使用这些客户端库连接到MySQL 8.0或更高版本时，就会遇到“Public Key Retrieval is not allowed”的错误。

方法1：更改MySQL用户的密码认证插件

你可以将用户的密码认证插件改回mysql_native_password，这通常是与旧客户端库兼容的选项。
登录到MySQL服务器。

# 更改用户的密码认证插件：
ALTER USER 'yourusername'@'yourhost' IDENTIFIED WITH mysql_native_password BY 'yourpassword';
# 刷新权限：
FLUSH PRIVILEGES;

方法2：在客户端配置中允许公钥检索

如果你不想更改密码认证插件，你可以在连接数据库时允许公钥检索。这可以通过设置连接参数来实现。
对于Java JDBC连接：
在连接字符串中添加allowPublicKeyRetrieval=true：

String url = "jdbc:mysql://hostname:port/dbname?allowPublicKeyRetrieval=true&useSSL=false";

如果使用远程连接工具可以在驱动属性中将allowPublicKeyRetrieval（运输公钥检索）设置为true

image.png

2、搭建主从集群

1) 主库配置

1、修改主库配置文件 my.cnf

[mysqld]
server-id = 1
log-bin = mysql-bin  # 启用二进制日志
binlog-format = ROW

2、重启MySQL并进入mysql创建复制用户

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

3、查看主库状态

SHOW MASTER STATUS;
-- 记录 File（如 mysql-bin.000001）和 Position（如 1234）

2）从库配置

1、修改从库配置文件 my.cnf

[mysqld]
server-id = 2
relay-log = mysql-relay-bin
read-only = 1  # 从库只读

2、重启MySQL并配置复制

CHANGE MASTER TO
  MASTER_HOST='主库IP',
  MASTER_USER='repl',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=1234;
START SLAVE;

3、检查复制状态

SHOW SLAVE STATUS\G;
-- 确保 Slave_IO_Running 和 Slave_SQL_Running 均为 Yes

3、使用Keepalived实现主从自动切换

Keepalived通过虚拟IP（VIP）漂移实现主从切换，需在主从节点上安装并配置。

3.1 安装Keepalived

sudo apt update
sudo apt install keepalived -y

3.2 主节点Keepalived配置

sudo vim /etc/keepalived/keepalived.conf

#vrrp_script chk_mysql {
#  script "/usr/bin/pgrep mysqld"  # 检测MySQL进程是否存在
#  interval 2
#  weight 2
#}

vrrp_instance VI_1 {
  interface ens160         # 网卡名称（通过ip a查看）
  state MASTER
  virtual_router_id 51
  priority 100             # 主节点优先级更高
  advert_int 1

  virtual_ipaddress {
    192.168.1.100/24      # 虚拟IP
  }

  track_script {
    chk_mysql
  }
}

3.3 从节点Keepalived配置

sudo vim /etc/keepalived/keepalived.conf

vrrp_instance VI_1 {
  interface ens160
  state BACKUP
  virtual_router_id 51
  priority 50              # 从节点优先级较低
  advert_int 1

  virtual_ipaddress {
    192.168.1.100/24
  }

  track_script {
    chk_mysql
  }
}

3.4 启动Keepalived

sudo systemctl start keepalived
sudo systemctl enable keepalived

3.5 自动切换脚本（提升从库为主库）

当主库故障时，需自动将从库提升为新主库。编写脚本并集成到Keepalived检测逻辑中。
示例脚本：/usr/local/bin/mysql_failover.sh

#!/bin/bash

# 检查当前节点是否为从库
SLAVE_STATUS=$(mysql -u root -p'your_password' -e "SHOW SLAVE STATUS\G" | grep "Slave_IO_Running" | awk '{print $2}')

if [ "$SLAVE_STATUS" = "Yes" ]; then
  # 停止复制并重置为主库
  mysql -u root -p'your_password' -e "STOP SLAVE; RESET MASTER;"
  # 关闭只读模式
  mysql -u root -p'your_password' -e "SET GLOBAL read_only=OFF;"
  # 通知其他从库切换主库（需根据实际拓扑扩展）
  echo "$(date) - 从库已提升为主库" >> /var/log/mysql_failover.log
fi

3.6 在从库Keepalived配置中调用脚本

在配置文件头部增加

vrrp_script chk_mysql {
  script "/usr/local/bin/mysql_failover.sh"
  interval 2
  weight 2
}

3.7 编写主库服务器故障检测脚本

示例脚本：/etc/keepalived/check_mysql.sh

#!/bin/bash
if systemctl is-active --quiet mysql; then
    exit 0  # MySQL运行正常
else
    exit 1  # MySQL停止，触发切换
fi

3.8 在主库Keepalived配置中调用脚本

在配置文件头部增加

vrrp_script chk_mysql {
    script "/etc/keepalived/check_mysql.sh"
    interval 2   # 检测间隔（秒）
    weight -60    # 检测失败时降低优先级
}

3.9 测试故障切换

模拟主库故障：

sudo systemctl stop mysql

观察VIP漂移：

ip a show ens160  # 检查VIP是否转移到从库(主库查询不到虚拟ip，从库可以查询到虚拟ip)

验证新主库写入：

-- 通过VIP连接并执行写入操作
mysql -h 192.168.1.100 -u root -p -e "INSERT INTO your_table VALUES (1);"

MySQL数据库学习记录

索引

EXPLAIN

1、使用方法

2、返回信息

数据库基础知识

1、DDL、DML、DQL、DCL 分别是什么？

mysql集群搭建

1、配置Ubuntu环境

添加 MySQL 5.7 /8.0 APT 仓库

mysql密码不对问题：ERROR 1698 (28000): Access denied for user 'root'@'localhost'

查看user表中的信息

mysql创建新用户并授权

远程连接mysql时报错，“Public Key Retrieval is not allowed”

方法1：更改MySQL用户的密码认证插件

方法2：在客户端配置中允许公钥检索

2、搭建主从集群

1) 主库配置

2） 从库配置

3、使用Keepalived实现主从自动切换

3.1 安装Keepalived

3.2 主节点Keepalived配置

3.3 从节点Keepalived配置

3.4 启动Keepalived

3.5 自动切换脚本（提升从库为主库）

3.6 在从库Keepalived配置中调用脚本

3.7 编写主库服务器故障检测脚本

3.8 在主库Keepalived配置中调用脚本

3.9 测试故障切换

推荐阅读更多精彩内容

2）从库配置