MySQL 8 教程

全局变量持久化的基本概念

• 运行时值：当前MySQL实例中全局变量的实际值，可以通过SET GLOBAL或SET PERSIST命令修改
• 持久化值：存储在mysqld-auto.cnf文件中的全局变量值，MySQL服务重启时会读取并应用这些值
• 配置文件值：存储在MySQL配置文件（如my.cnf或my.ini）中的全局变量值
• 默认值：MySQL内置的全局变量默认值

MySQL服务启动时，会按照以下优先级应用全局变量的值：

1. 首先使用默认值初始化所有全局变量
2. 然后读取配置文件（如my.cnf）中的值，覆盖默认值
3. 最后读取mysqld-auto.cnf文件中的持久化值，覆盖之前的值

SET PERSIST命令的基本语法

SET PERSIST variable_name = value;
SET PERSIST `variable_name` = value;  -- 如果变量名包含特殊字符或关键字，需要使用反引号

SET PERSIST命令的示例

-- 示例1：设置max_connections变量并持久化
SET PERSIST max_connections = 2000;

-- 示例2：设置innodb_buffer_pool_size变量并持久化
SET PERSIST innodb_buffer_pool_size = 4294967296;  -- 4GB，注意单位是字节

-- 示例3：设置default_storage_engine变量并持久化
SET PERSIST default_storage_engine = InnoDB;

-- 示例4：设置character_set_server变量并持久化
SET PERSIST character_set_server = utf8mb4;

-- 示例5：设置collation_server变量并持久化
SET PERSIST collation_server = utf8mb4_unicode_ci;

-- 示例6：设置log_error变量并持久化
SET PERSIST log_error = '/var/log/mysql/error.log';

-- 示例7：设置slow_query_log变量并持久化
SET PERSIST slow_query_log = ON;

-- 示例8：设置long_query_time变量并持久化
SET PERSIST long_query_time = 2.0;  -- 慢查询日志记录超过2秒的查询

-- 示例9：设置tmp_table_size变量并持久化
SET PERSIST tmp_table_size = 67108864;  -- 64MB

-- 示例10：设置max_heap_table_size变量并持久化
SET PERSIST max_heap_table_size = 67108864;  -- 64MB

-- 示例11：设置join_buffer_size变量并持久化
SET PERSIST join_buffer_size = 2097152;  -- 2MB

-- 示例12：设置sort_buffer_size变量并持久化
SET PERSIST sort_buffer_size = 2097152;  -- 2MB

-- 示例13：设置read_buffer_size变量并持久化
SET PERSIST read_buffer_size = 1048576;  -- 1MB

-- 示例14：设置read_rnd_buffer_size变量并持久化
SET PERSIST read_rnd_buffer_size = 2097152;  -- 2MB

-- 示例15：设置key_buffer_size变量并持久化
SET PERSIST key_buffer_size = 33554432;  -- 32MB

SET PERSIST_ONLY命令的基本语法

SET PERSIST_ONLY variable_name = value;
SET PERSIST_ONLY `variable_name` = value;  -- 如果变量名包含特殊字符或关键字，需要使用反引号

SET PERSIST_ONLY命令的示例

-- 示例1：持久化innodb_data_file_path变量，但不修改当前值
-- 注意：innodb_data_file_path变量需要重启才能生效
SET PERSIST_ONLY innodb_data_file_path = 'ibdata1:12M;ibdata2:12M:autoextend';

-- 示例2：持久化basedir变量，但不修改当前值
SET PERSIST_ONLY basedir = '/usr/local/mysql';

-- 示例3：持久化datadir变量，但不修改当前值
SET PERSIST_ONLY datadir = '/var/lib/mysql';

-- 示例4：持久化port变量，但不修改当前值
SET PERSIST_ONLY port = 3307;

-- 示例5：持久化socket变量，但不修改当前值
SET PERSIST_ONLY socket = '/tmp/mysql.sock';

-- 示例6：持久化pid_file变量，但不修改当前值
SET PERSIST_ONLY pid_file = '/var/run/mysqld/mysqld.pid';

-- 示例7：持久化server_id变量，但不修改当前值
SET PERSIST_ONLY server_id = 100;

-- 示例8：持久化log_bin变量，但不修改当前值
SET PERSIST_ONLY log_bin = ON;

-- 示例9：持久化binlog_format变量，但不修改当前值
SET PERSIST_ONLY binlog_format = ROW;

-- 示例10：持久化innodb_file_per_table变量，但不修改当前值
SET PERSIST_ONLY innodb_file_per_table = ON;

-- 示例11：持久化sql_mode变量，但不修改当前值
SET PERSIST_ONLY sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- 示例12：持久化lower_case_table_names变量，但不修改当前值
-- 注意：lower_case_table_names变量需要在初始化时设置，重启时修改可能会导致问题
SET PERSIST_ONLY lower_case_table_names = 1;

-- 示例13：持久化default_time_zone变量，但不修改当前值
SET PERSIST_ONLY default_time_zone = '+8:00';

-- 示例14：持久化event_scheduler变量，但不修改当前值
SET PERSIST_ONLY event_scheduler = ON;

-- 示例15：持久化max_binlog_size变量，但不修改当前值
SET PERSIST_ONLY max_binlog_size = 1073741824;  -- 1GB

查看持久化的全局变量

-- 查看所有已持久化的全局变量
SELECT * FROM performance_schema.persisted_variables;

-- 查看特定的持久化全局变量
SELECT * FROM performance_schema.persisted_variables
WHERE VARIABLE_NAME = 'max_connections';

-- 搜索名称包含特定字符串的持久化全局变量
SELECT * FROM performance_schema.persisted_variables
WHERE VARIABLE_NAME LIKE '%buffer%';

-- 按变量名排序查看持久化全局变量
SELECT * FROM performance_schema.persisted_variables
ORDER BY VARIABLE_NAME;

-- 统计持久化全局变量的数量
SELECT COUNT(*) AS persisted_variable_count
FROM performance_schema.persisted_variables;

-- 查看当前运行时的全局变量值和持久化值的对比
SELECT 
    p.VARIABLE_NAME,
    p.VARIABLE_VALUE AS persisted_value,
    g.VARIABLE_VALUE AS global_value
FROM performance_schema.persisted_variables p
JOIN GLOBAL_VARIABLES g ON p.VARIABLE_NAME = g.VARIABLE_NAME
ORDER BY p.VARIABLE_NAME;

RESET PERSIST命令的基本语法

-- 删除特定的持久化全局变量
RESET PERSIST variable_name;

-- 删除所有的持久化全局变量
RESET PERSIST;

RESET PERSIST命令的示例

-- 示例1：删除特定的持久化全局变量
RESET PERSIST max_connections;

-- 示例2：删除名称包含特殊字符的持久化全局变量
-- 注意：如果变量名包含特殊字符或关键字，需要使用反引号
RESET PERSIST `innodb_buffer_pool_size`;

-- 示例3：删除多个持久化全局变量
RESET PERSIST max_connections;
RESET PERSIST innodb_buffer_pool_size;
RESET PERSIST default_storage_engine;

-- 示例4：删除所有的持久化全局变量
RESET PERSIST;

-- 示例5：删除持久化全局变量后，验证删除结果
-- 先查看当前持久化的全局变量
SELECT * FROM performance_schema.persisted_variables;

-- 删除特定的持久化全局变量
RESET PERSIST max_connections;

-- 再次查看持久化的全局变量，确认已删除
SELECT * FROM performance_schema.persisted_variables;

-- 示例6：删除所有持久化全局变量后，重启MySQL服务
-- 注意：在生产环境中，请谨慎执行此操作
RESET PERSIST;
-- 重启MySQL服务（根据操作系统不同，命令可能有所不同）
-- sudo systemctl restart mysqld  -- 对于使用systemd的Linux系统
-- service mysql restart          -- 对于使用SysV init的Linux系统

查看mysqld-auto.cnf文件

-- 步骤1：确定MySQL的数据目录
SHOW VARIABLES LIKE 'datadir';

-- 步骤2：使用操作系统命令查看mysqld-auto.cnf文件
-- 在Linux/Mac系统中：
-- cat /path/to/datadir/mysqld-auto.cnf

-- 在Windows系统中：
-- type C:\path\to\datadir\mysqld-auto.cnf

-- 示例输出（JSON格式）：
/*
{
  "Version": 1,
  "mysql_server_uuid": "c1a2b3d4-e5f6-7890-ab12-c3d4e5f6a7b8",
  "Variables": {
    "max_connections": {
      "Value": "2000",
      "Metadata": {
        "Timestamp": 1623456789012
      }
    },
    "innodb_buffer_pool_size": {
      "Value": "4294967296",
      "Metadata": {
        "Timestamp": 1623456789013
      }
    },
    "default_storage_engine": {
      "Value": "InnoDB",
      "Metadata": {
        "Timestamp": 1623456789014
      }
    }
  }
}
*/

mysqld-auto.cnf文件的结构说明

• Version：文件格式的版本号，目前为1
• mysql_server_uuid：MySQL服务器的唯一标识符
• Variables：包含所有持久化的全局变量的对象
  • 变量名：作为键，对应的值是一个对象
  • Value：变量的持久化值
  • Metadata：元数据，包含一个Timestamp字段，表示变量被持久化的时间戳

全局变量持久化的常见使用场景

1. 动态调整数据库参数：在不重启MySQL服务的情况下，动态调整数据库参数，并确保这些调整在重启后仍然有效
2. 临时调整与持久化调整分离：使用SET GLOBAL进行临时调整，使用SET PERSIST进行持久化调整
3. 参数实验与验证：先使用SET GLOBAL测试参数调整的效果，确认有效后再使用SET PERSIST持久化
4. 多实例环境管理：在多实例环境中，每个实例可以有自己的持久化参数，避免混淆
5. 自动化运维：在自动化运维脚本中使用全局变量持久化功能，确保参数调整的一致性和持久性
6. 快速恢复配置：当需要回滚到之前的配置时，可以通过删除或修改mysqld-auto.cnf文件来实现
7. 减少配置文件修改错误：使用命令行工具修改持久化参数，减少直接编辑配置文件可能带来的错误

在PHP中设置持久化全局变量

<?php
// 连接到MySQL数据库
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

$conn = new mysqli($servername, $username, $password, $dbname);

// 检查连接是否成功
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

// 设置持久化全局变量
function setPersistGlobalVariable($conn, $variable_name, $value) {
    // 使用预处理语句防止SQL注入
    $sql = "SET PERSIST ? = ?";
    $stmt = $conn->prepare($sql);
    
    // 注意：对于SET语句中的变量名，不能直接作为参数绑定
    // 这里我们需要手动构建SQL语句，但仍然对值进行参数绑定
    $sql = "SET PERSIST `$variable_name` = ?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("s", $value);
    
    if ($stmt->execute()) {
        echo "变量 '$variable_name' 已成功设置为 '$value' 并持久化\n";
        return true;
    } else {
        echo "设置变量 '$variable_name' 失败: " . $stmt->error . "\n";
        return false;
    }
    
    $stmt->close();
}

// 示例：设置几个持久化全局变量
setPersistGlobalVariable($conn, "max_connections", "2000");
setPersistGlobalVariable($conn, "innodb_buffer_pool_size", "4294967296");
setPersistGlobalVariable($conn, "default_storage_engine", "InnoDB");

// 查看已持久化的全局变量
function getPersistedVariables($conn, $variable_name = null) {
    if ($variable_name) {
        $sql = "SELECT * FROM performance_schema.persisted_variables WHERE VARIABLE_NAME = ?";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("s", $variable_name);
    } else {
        $sql = "SELECT * FROM performance_schema.persisted_variables";
        $stmt = $conn->prepare($sql);
    }
    
    $stmt->execute();
    $result = $stmt->get_result();
    
    $variables = array();
    while ($row = $result->fetch_assoc()) {
        $variables[$row["VARIABLE_NAME"]] = $row["VARIABLE_VALUE"];
    }
    
    $stmt->close();
    return $variables;
}

// 示例：查看所有已持久化的全局变量
$persisted_vars = getPersistedVariables($conn);
echo "\n已持久化的全局变量：\n";
foreach ($persisted_vars as $name => $value) {
    echo "$name = $value\n";
}

// 示例：查看特定的已持久化全局变量
$max_connections = getPersistedVariables($conn, "max_connections");
echo "\nmax_connections = " . (isset($max_connections["max_connections"]) ? $max_connections["max_connections"] : "未设置") . "\n";

// 删除持久化全局变量
function resetPersistGlobalVariable($conn, $variable_name = null) {
    if ($variable_name) {
        $sql = "RESET PERSIST `$variable_name`";
    } else {
        $sql = "RESET PERSIST";
    }
    
    if ($conn->query($sql) === TRUE) {
        if ($variable_name) {
            echo "变量 '$variable_name' 的持久化设置已删除\n";
        } else {
            echo "所有持久化的全局变量设置已删除\n";
        }
        return true;
    } else {
        echo "删除持久化设置失败: " . $conn->error . "\n";
        return false;
    }
}

// 示例：删除特定的持久化全局变量
// resetPersistGlobalVariable($conn, "max_connections");

// 示例：删除所有持久化全局变量（谨慎使用）
// resetPersistGlobalVariable($conn);

// 关闭连接
$conn->close();
?>

在Python中设置持久化全局变量

# 导入MySQL连接器
import mysql.connector

# 连接到MySQL数据库
db = mysql.connector.connect(
    host="localhost",
    user="username",
    password="password",
    database="myDB"
)

# 创建游标对象
cursor = db.cursor()

# 设置持久化全局变量
def set_persist_global_variable(cursor, variable_name, value):
    try:
        # 构建并执行SET PERSIST语句
        # 注意：对于变量名，我们需要手动构建SQL语句
        # 对于值，我们使用参数化查询来防止SQL注入
        sql = f"SET PERSIST `{variable_name}` = %s"
        cursor.execute(sql, (str(value),))
        print(f"变量 '{variable_name}' 已成功设置为 '{value}' 并持久化")
        return True
    except mysql.connector.Error as error:
        print(f"设置变量 '{variable_name}' 失败: {error}")
        return False

# 示例：设置几个持久化全局变量
set_persist_global_variable(cursor, "max_connections", 2000)
set_persist_global_variable(cursor, "innodb_buffer_pool_size", 4294967296)
set_persist_global_variable(cursor, "default_storage_engine", "InnoDB")

# 查看已持久化的全局变量
def get_persisted_variables(cursor, variable_name=None):
    try:
        if variable_name:
            sql = "SELECT * FROM performance_schema.persisted_variables WHERE VARIABLE_NAME = %s"
            cursor.execute(sql, (variable_name,))
        else:
            sql = "SELECT * FROM performance_schema.persisted_variables"
            cursor.execute(sql)
        
        # 获取查询结果
        result = cursor.fetchall()
        
        # 将结果转换为字典
        variables = {}
        for row in result:
            variables[row[0]] = row[1]
        
        return variables
    except mysql.connector.Error as error:
        print(f"查询持久化变量失败: {error}")
        return {}

# 示例：查看所有已持久化的全局变量
persisted_vars = get_persisted_variables(cursor)
print("\n已持久化的全局变量：")
for name, value in persisted_vars.items():
    print(f"{name} = {value}")

# 示例：查看特定的已持久化全局变量
max_connections = get_persisted_variables(cursor, "max_connections")
print(f"\nmax_connections = " + (max_connections["max_connections"] if "max_connections" in max_connections else "未设置"))

# 删除持久化全局变量
def reset_persist_global_variable(cursor, variable_name=None):
    try:
        if variable_name:
            sql = f"RESET PERSIST `{variable_name}`"
        else:
            sql = "RESET PERSIST"
        
        cursor.execute(sql)
        
        if variable_name:
            print(f"变量 '{variable_name}' 的持久化设置已删除")
        else:
            print("所有持久化的全局变量设置已删除")
        
        return True
    except mysql.connector.Error as error:
        print(f"删除持久化设置失败: {error}")
        return False

# 示例：删除特定的持久化全局变量
# reset_persist_global_variable(cursor, "max_connections")

# 示例：删除所有持久化全局变量（谨慎使用）
# reset_persist_global_variable(cursor)

# 关闭游标和连接
cursor.close()
db.close()