MySQL - 运维与复制

MySQL 运维与复制

安装与升级
MySQL 复制
MySQL Cluster
应急处理
常用管理命令与技巧

安装与升级

安装方法比较：

安装方式	优点	缺点
RPM 包	安装简单，适合标准环境	路径固定，灵活性差
二进制包	解压即用，不需编译	不能自定义编译选项
源码编译	可自定义功能和优化	编译耗时，需要编译环境

RPM 安装：

rpm -ivh MySQL-server-5.5.rpm
rpm -ivh MySQL-client-5.5.rpm

二进制安装：

groupadd mysql
useradd -g mysql mysql
tar zxvf mysql-5.5-linux-x86_64.tar.gz -C /usr/local/
cd /usr/local/
ln -s mysql-5.5-linux-x86_64 mysql
cd mysql
chown -R mysql .
chgrp -R mysql .
scripts/mysql_install_db --user=mysql
chown -R root .
chown -R mysql data
bin/mysqld_safe --user=mysql &

源码编译安装的性能考虑：

去掉不需要的模块，减少二进制大小
只选择要使用的字符集
使用 pgcc 编译器（如果支持）可以获得更好的性能
使用静态编译以提高性能

MySQL 升级：升级前务必做好完整备份，建议先在测试环境验证。

MySQL 降级：降级比升级风险更大，可能存在数据格式不兼容问题。

MySQL 复制

MySQL 复制是将主数据库的数据异步复制到一个或多个从数据库，用于负载均衡（读写分离）、数据备份、高可用等。

复制概述：MySQL 复制基于主服务器记录 binlog，从服务器读取并重放 binlog 来实现。

安装配置主从复制：

主服务器配置（my.cnf）：

[mysqld]
log-bin=mysql-bin
server-id=1
binlog_format=MIXED

在主服务器上创建复制用户：

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

查看主服务器状态：

SHOW MASTER STATUS;
-- 记录 File 和 Position 的值

从服务器配置（my.cnf）：

[mysqld]
server-id=2
relay-log=relay-bin

在从服务器上设置主服务器信息并启动复制：

CHANGE MASTER TO
    MASTER_HOST='192.168.1.100',
    MASTER_PORT=3306,
    MASTER_USER='repl',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000003',
    MASTER_LOG_POS=107;

START SLAVE;

检查从服务器状态：

SHOW SLAVE STATUS\G
-- 重点关注以下两项是否为 Yes：
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes

日常管理维护：

-- 停止复制
STOP SLAVE;

-- 启动复制
START SLAVE;

-- 重置从服务器（清除复制配置和中继日志）
RESET SLAVE ALL;

-- 跳过出错的 SQL（谨慎使用）
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

经常查看 slave 状态：确保 Slave_IO_Running 和 Slave_SQL_Running 都为 Yes，Seconds_Behind_Master 在合理范围内。

怎样强制主服务器阻塞更新直到从服务器同步：

-- 在主服务器上
FLUSH TABLES WITH READ LOCK;
-- 等待从服务器追上
-- 释放锁
UNLOCK TABLES;

多主复制时自动增长变量的冲突问题：多主复制时，需要设置不同的 auto_increment_increment 和 auto_increment_offset 避免自增列冲突。

MySQL Cluster

MySQL Cluster 是一种无共享架构的集群方案，将数据分布在多个数据节点上，提供高可用性和可扩展性。

Cluster 架构包含三种节点：

管理节点（MGM）：管理整个集群的配置和状态
数据节点（NDB）：存储数据，数据在各节点间冗余
SQL 节点（API）：提供 SQL 接口，应用连接的入口

Cluster 的启动顺序：管理节点 → 数据节点 → SQL 节点。

Cluster 的关闭顺序：在管理节点上执行关闭命令。

数据备份和恢复：Cluster 支持在线备份。

应急处理

一般处理流程：

确认问题现象和影响范围
查看错误日志定位原因
采取临时措施恢复服务
分析根本原因
实施永久修复

忘记 root 密码：

# 1. 停止 MySQL
mysqladmin shutdown

# 2. 跳过权限验证启动
mysqld_safe --skip-grant-tables &

# 3. 无密码登录并修改密码
mysql -u root
mysql> UPDATE mysql.user SET Password=PASSWORD('newpassword') WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> exit

# 4. 重启 MySQL
mysqladmin shutdown
mysqld_safe &

表损坏如何处理：

-- 检查表
CHECK TABLE products;

-- 修复 MyISAM 表
REPAIR TABLE products;

-- 命令行修复
myisamchk -r /var/lib/mysql/db/products.MYI

MyISAM 表超过 4G 无法访问：MyISAM 表默认有大小限制，需要修改 myisam_data_file_size 和 myisam_max_sort_file_size 参数。

数据目录磁盘空间不足：

清理不必要的日志文件
将非关键表迁移到其他磁盘（使用符号链接）
使用 OPTIMIZE TABLE 回收碎片空间
增加磁盘空间

如何禁止 DNS 反向解析：MySQL 默认会对连接的 IP 进行 DNS 反向解析，可能导致连接慢。在 my.cnf 中添加 skip-name-resolve 禁用。

常用管理命令与技巧

参数设置方法：

-- 运行时临时修改（重启后失效）
SET GLOBAL key_buffer_size = 256*1024*1024;
SET SESSION long_query_time = 5;

-- 永久修改需在 my.cnf 中配置

mysql.sock 丢失后怎么连接数据库：

# 如果 mysql.sock 丢失，可以通过 TCP 连接
mysql -u root -p -h 127.0.0.1 --protocol=tcp

# 或重启 MySQL 自动重建 sock 文件

同一台机器运行多个 MySQL：

# 使用不同的配置文件、端口、数据目录
mysqld_safe --defaults-file=/etc/my1.cnf &
mysqld_safe --defaults-file=/etc/my2.cnf &

查看用户权限：

SHOW GRANTS;
SHOW GRANTS FOR 'user'@'host';

修改用户密码：

-- 方法1
ALTER USER 'user'@'host' IDENTIFIED BY 'newpassword';

-- 方法2
SET PASSWORD FOR 'user'@'host' = PASSWORD('newpassword');

-- 方法3
mysqladmin -u user -p password "newpassword"

不进入 mysql 怎样运行 SQL 语句：

# 执行单条 SQL
mysql -u root -p -e "SELECT * FROM customers LIMIT 5"

# 执行 SQL 文件
mysql -u root -p < script.sql

# 将结果输出到文件
mysql -u root -p -e "SELECT * FROM customers" > output.txt

客户端怎么访问内网数据库：

使用 SSH 隧道：ssh -L 3306:localhost:3306 user@remote_host
使用 VPN 连接到内网
配置 MySQL 代理转发

SQL Mode 简介

SQL Mode 定义了 MySQL 执行 SQL 语句的严格程度。

-- 查看当前 SQL Mode
SELECT @@sql_mode;

-- 设置 SQL Mode
SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

-- 常用 SQL Mode：
-- STRICT_TRANS_TABLES：严格模式，数据超长或不合法会报错而非截断
-- NO_ENGINE_SUBSTITUTION：不支持的引擎不允许自动替换
-- ONLY_FULL_GROUP_BY：GROUP BY 查询必须包含所有非聚合列
-- ANSI_QUOTES：使用 ANSI 引号

SQL Mode 与可移植性：不同数据库系统的 SQL Mode 不同，设置合适的 SQL Mode 可以提高 SQL 在不同数据库间的可移植性。SQL Mode 与数据校验：严格模式可以防止非法数据写入，提高数据质量。

MySQL - 运维与复制