十一、MySQL 第 4-7 章

第 4 章 MySQL5.x 源码安装4.1 源码安装概述源码安装特点优点高度自定义可以自定义编译参数、自定义功能、自定义安装路径适合深度定制。缺点编译耗时长依赖库多排错难度大升级、维护麻烦生产优先选择 RPM/YUM 二进制包。编译工具cmakeMySQL5.5 以后废弃 configure全部使用 cmake 做编译配置。依赖包需要安装开发库gcc gcc‑c cmake ncurses‑devel bison openssl‑devel。4.2 编译安装完整流程创建 mysql 系统用户不允许登录系统groupadd mysql useradd -r -g mysql -s /sbin/nologin mysql创建安装目录、数据目录mkdir -p /usr/local/mysql mkdir -p /data/mysql chown -R mysql:mysql /data/mysql解压源码包tar -xf mysql‑5.x.x.tar.gz cd mysql‑5.x.xcmake 编译配置关键编译选项cmake . \ ‑DCMAKE_INSTALL_PREFIX/usr/local/mysql \ ‑DMYSQL_DATADIR/data/mysql \ ‑DSYSCONFDIR/etc \ ‑DWITH_INNOBASE_STORAGE_ENGINE1 \ ‑DWITH_MYISAM_STORAGE_ENGINE1 \ ‑DDEFAULT_CHARSETutf8mb4 \ ‑DDEFAULT_COLLATIONutf8mb4_general_ci \ ‑DWITH_SSLsystem常见 cmake 参数说明CMAKE_INSTALL_PREFIX程序安装路径MYSQL_DATADIR数据存放目录SYSCONFDIR配置文件 my.cnf 存放目录存储引擎WITH_INNOBASE_STORAGE_ENGINE编译 InnoDB 引擎字符集设置默认字符集与排序规则编译 安装make -j4 #‑j 多线程编译CPU核心数编译时间较长 make install初始化数据库MySQL5.7 使用 mysqld‑initializecd /usr/local/mysql chown -R mysql:mysql . #5.6及以前 scripts/mysql_install_db --usermysql --datadir/data/mysql #5.7 bin/mysqld --initialize --usermysql --datadir/data/mysql⚠️5.7 初始化会生成临时 root 密码屏幕输出记录临时密码。配置 my.cnf 配置文件/etc/my.cnf[mysqld] basedir/usr/local/mysql datadir/data/mysql usermysql port3306 socket/tmp/mysql.sock [mysqld_safe] log‑error/data/mysql/mysql‑error.log pid‑file/data/mysql/mysql.pid配置 systemd 服务或者拷贝 sysv 启动脚本cp support‑files/mysql.server /etc/init.d/mysqld chmod x /etc/init.d/mysqld chkconfig mysqld on启动服务/etc/init.d/mysqld start修改 root 密码/usr/local/mysql/bin/mysql_secure_installation4.3 源码安装常见故障cmake 报错缺少依赖包yum 安装对应 devel 开发库。make 编译报错内存不足虚拟机增加内存。启动失败目录权限不对my.cnf 参数错误端口被占用。初始化失败datadir 目录必须为空不能有旧数据文件。4.4 源码 vs RPM 二进制包对比表格方式优点缺点适用场景源码编译高度自定义编译选项自定义路径编译慢依赖多维护复杂定制化需求学习研究RPM/YUM 二进制包安装简单稳定官方测试好自定义程度低生产环境推荐第 5 章 MySQL 备份与恢复5.1 备份分类按备份数据范围全量备份备份整个数据库所有数据。增量备份只备份上一次备份之后变化的数据。差异备份备份自上一次全量备份之后变化的数据。按备份实现方式逻辑备份备份 SQL 语句工具mysqldump。优点跨平台跨版本可编辑 SQL 文本兼容性好。缺点速度慢需要 MySQL 运行读取数据库锁表大库耗时久。物理备份裸文件备份直接拷贝磁盘上的数据文件。工具xtrabackup(Percona‑XtraBackup)优点速度快直接拷贝磁盘块适合大数据库。缺点受版本、存储引擎限制跨平台兼容性差。按锁机制热备份数据库正常读写不锁库业务不停机InnoDB 支持热备。温备份读可以写会阻塞锁表。冷备份数据库完全关闭拷贝文件业务停止。5.2 逻辑备份工具 mysqldumpmysqldump 是 MySQL 自带逻辑备份工具输出 SQL 文本。常用语法#备份单个数据库 mysqldump -uroot -p dbname dbname.sql #备份多个数据库 mysqldump -uroot -p --databases db1 db2 multi.sql #备份全部所有数据库 mysqldump -uroot -p --all‑databases all.sql #只备份某些表 mysqldump -uroot -p dbname t1 t2 table.sql重要参数‑‑single‑transactionInnoDB 快照热备不加锁InnoDB 必备通过 MVCC 实现一致性快照。‑‑lock‑tablesMyISAM 引擎锁表备份。‑‑no‑data只备份表结构不备份数据。‑‑routines备份存储过程、函数。‑‑triggers备份触发器。mysqldump 恢复两种恢复方式#方式1 shell重定向 mysql -uroot -p dbname dbname.sql #方式2 mysql客户端source命令 mysql use dbname; mysql source /root/dbname.sql;注意--databases导出的文件自带create database不加 --databases 导出恢复需要手动先创建数据库。5.3 物理备份 Percona‑XtraBackupInnoDB 热备份Percona 开源工具专门针对 InnoDB不停机热备份不需要锁表。核心工具xtrabackup备份 InnoDB 数据文件不备份 MyISAM。innobackupex封装脚本同时支持 InnoDBMyISAM。备份三步流程备份 → prepare 预准备应用 redo log把数据文件达成一致 → 恢复拷贝回数据目录。#全量备份 innobackupex --userroot --passwordxxx /backup #prepare 预准备redo log回滚数据文件一致性 innobackupex --apply‑log /backup/时间目录 #恢复停止mysql清空datadir拷贝文件修改mysql权限 innobackupex --copy‑back /backup/时间目录 chown -R mysql:mysql /data/mysql增量备份基于上一次全量备份备份变化的数据页。5.4 binlog 二进制日志备份时间点恢复 point‑in‑timebinlog 记录所有 DML/DDL 修改数据的 SQL可以实现时间点恢复误删除数据找回。 开启 binlogmy.cnf配置log‑binmysql‑bin。#查看binlog事件 mysqlbinlog mysql‑bin.000001 #恢复binlog日志 mysqlbinlog mysql‑bin.000001 | mysql -uroot -p组合方案mysqldump全量备份 binlog增量日志可以恢复到任意时间点。5.5 备份策略最佳实践中小库mysqldump --single‑transaction做全量备份开启 binlog。大库Percona‑XtraBackup 物理全量 增量配合 binlog。备份文件需要异地保存定期做恢复演练只备份不做恢复演练等于没有备份。第 6 章 MySQL 主从复制与读写分离6.1 主从复制概念主库 Master 负责写从库 Slave 复制主库数据保持数据同步。用途1读写分离读请求分摊到从库减轻主库压力2数据备份3故障切换主库宕机提升从库为主库。原理基于 binlog 二进制日志完成数据同步。6.2 主从复制三大线程Master dump 线程主库有从库连接dump 线程读取 binlog发送给从库。Slave IO 线程从库连接主库接收 binlog 日志写入本地 relay‑log 中继日志。Slave SQL 线程从库读取 relay‑log 中继日志执行 SQL把数据应用到从库。流程Master 写操作记录 binlog → dump 线程发送 → Slave IO 线程接收写入 relay‑log → Slave SQL 线程重放 relay‑log完成同步。6.3 主库 Master 配置 my.cnf[mysqld] server‑id1 #集群内id必须唯一 log‑binmysql‑bin #开启二进制binlog日志 binlog_formatROW #行模式推荐记录行数据变化 #binlog_do_db 可选只同步指定库binlog_ignore_db忽略库重启主库创建复制账号从库用来连接主库。CREATE USER repl% IDENTIFIED BY Repl123456; GRANT REPLICATION SLAVE ON *.* TO repl%; FLUSH PRIVILEGES; #锁表记录show master status输出File和Position记录binlog文件名和偏移量 FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;拿到 File、Position解锁UNLOCK TABLES;。6.4 从库 Slave 配置 my.cnf[mysqld] server‑id2 #不能和主库重复 relay‑logrelay‑bin #开启中继日志重启从库导入主库全量备份数据保证主从初始数据一致。 从库执行 change master to 配置主库信息CHANGE MASTER TO MASTER_HOST192.168.108.128, MASTER_USERrepl, MASTER_PASSWORDRepl123456, MASTER_LOG_FILEmysql‑bin.000001, MASTER_LOG_POS154; START SLAVE; --启动复制 #查看复制状态重点看Slave_IO_Running:Yes Slave_SQL_Running:Yes SHOW SLAVE STATUS\G故障判断两个线程必须全部 Yes任意 No 代表复制异常。6.5 binlog 三种格式STATEMENT 语句模式记录执行的 SQL 语句节省日志部分函数主从会产生数据不一致。ROW 行模式推荐记录每一行数据修改前后不会主从数据不一致日志体积大。MIXED 混合模式MySQL 自动选择 statement 或者 row。6.6 主从常见故障IO 线程 No网络不通账号密码错误IP、端口防火墙server‑id 重复。SQL 线程 No数据不一致从库有手动修改数据主键冲突。临时跳过错误set global sql_slave_skip_counter1;应急不建议长期使用主从延迟大事务从库硬件性能差大 DDL 语句。6.7 读写分离原理写操作全部路由到 Master 主库查询读请求分发到多个 Slave 从库分担主库压力。实现方案方案 1应用层代码实现程序内区分读写简单维护成本在开发。方案 2中间件代理层代理工具MyCat、ProxySQL、MaxScale。代理接收 SQL自动路由读写到对应数据库节点。注意主从存在同步延迟业务要处理延迟带来的数据读取不一致问题。第 7 章 MHA 高可用Master High Availability7.1 MHA 介绍MHAMaster High Availability开源 MySQL 主从故障切换高可用工具。组成两部分MHA Manager 管理节点 MHA Node部署在每一台 MySQL 数据库节点。作用监控主库状态主库故障自动故障转移挑选最优从库提升为新主库其余从库自动指向新主库实现数据库故障自动切换。MHA 本身不提供虚拟 IP需要配合 vip 脚本业务连接虚拟 IP切换时 vip 漂移。 适用环境基于传统 MySQL 主从复制架构支持 5.5/5.6/5.7。7.2 MHA 架构角色Manager 管理节点独立机器运行mha_manager监控程序不部署 MySQL。负责检测 master 故障执行故障切换。Node 节点所有 MySQL 主、从库都必须安装 MHA‑node 组件。做日志保存、binlog/relay‑log 处理故障切换时数据补齐。MySQL 集群1 主 N 从全部开启主从复制。VIP 虚拟 IP业务访问 VIP故障发生时脚本完成 vip 漂移。7.3 MHA 工作流程正常状态Manager 定期 ping 探测 Master 存活状态。Master 主库故障Manager 检测到主库不可达进入故障切换流程。选主算法挑选数据最完整的 Slave 作为候选新 Master。补数据各个从库应用剩余中继日志把各个从库数据追到一致状态。提升把候选从库提升为新主库执行 vip 漂移脚本绑定 vip 到新主库。其余所有旧从库自动 change master to 指向新主库开启复制。旧主库修复上线后自动加入集群作为新主库的从库。MHA 不会修复已经宕机的旧主库旧主库恢复后需要重新加入集群。7.4 mha 配置文件/etc/mha.cnf[server default] userroot passwordRoot123 repl_userrepl repl_passwordRepl123 ping_interval1 manager_workdir/var/log/mha manager_log/var/log/mha/mha.log ssh_userroot #vip漂移脚本 master_ip_failover_script/usr/local/bin/master_ip_failover [server1] hostname192.168.108.131 [server2] hostname192.168.108.132 [server3] hostname192.168.108.1337.5 MHA 常用命令#检查ssh互信是否正常 masterha_check_ssh --conf/etc/mha.cnf #检查mysql主从复制状态 masterha_check_repl --conf/etc/mha.cnf #启动mha manager管理监控 nohup masterha_manager --conf/etc/mha.cnf #查看mha状态 masterha_check_status --conf/etc/mha.cnf #手动故障切换主库已经宕机手动执行切换 masterha_master_switch --conf/etc/mha.cnf --master_statedead前提MHA 所有节点之间配置 ssh 免密互信。7.6 MHA 优缺点✅优点基于原生 MySQL 主从复制不需要修改 MySQL 内核自动选主自动补齐从库数据其余从库自动重定向新主库开源免费。❌缺点Manager 是单点故障需要做 Manager 高可用。不自带 VIP需要自己编写漂移脚本。主从复制本身有延迟故障切换会存在少量数据丢失风险。不支持 MGR8.0 之后社区维护活跃度下降。7.7 MHA 对比其他高可用方案MHA传统主从复制故障切换适合 5.x。MySQL‑MGRMySQL 组复制内置高可用强一致性。Orchestrator开源主从管理切换工具。