MySQL高可用架构实战备份恢复、主从复制、读写分离与MHA一、MySQL备份与恢复1.1 备份分类物理备份直接复制数据库文件冷备、热备、温备。逻辑备份导出SQL语句如mysqldump。增量备份基于二进制日志。1.2 冷备份物理冷备systemctl stop mysqldtarczf /backup/mysql-backup-$(date%F).tar.gz /var/lib/mysql systemctl start mysqld# 恢复时停止服务解压覆盖修改权限后启动1.3 逻辑备份mysqldump# 备份单个数据库mysqldump-uroot-pschool/backup/school.sql# 备份多个数据库mysqldump-uroot-p--databasesschool mysql/backup/school_mysql.sql# 备份所有数据库mysqldump-uroot-p--all-databases/backup/all.sql# 备份指定表mysqldump-uroot-pschool student/backup/student.sql恢复mysql -u root -p school /backup/school.sql或在mysql内source /backup/student.sql1.4 增量备份基于binlog启用binloglog-binmysql-bin完整备份后刷新日志mysqladmin flush-logs后续所有变更记录在新binlog中。恢复时先恢复完整备份再使用mysqlbinlog重放增量binlog。mysqlbinlog mysql-bin.000003|mysql-uroot-p二、主从复制2.1 核心原理binlog主库记录所有变更。IO线程从库请求并接收binlog写入relay log。SQL线程从库重放relay log。2.2 配置步骤基于5.7主库[mysqld] server-id1 log-binmaster-bin log-slave-updates创建复制用户GRANTREPLICATIONSLAVEON*.*TOmyslave192.168.%IDENTIFIEDBY123456;FLUSHPRIVILEGES;SHOWMASTERSTATUS;-- 记录File和Position从库[mysqld] server-id2 relay-logrelay-log-bin relay-log-indexslave-relay-bin.index执行CHANGE MASTERTOMASTER_HOST主库IP,MASTER_USERmyslave,MASTER_PASSWORD123456,MASTER_LOG_FILEmaster-bin.000001,MASTER_LOG_POS604;STARTSLAVE;SHOWSLAVESTATUS\G;-- 查看两个YES三、读写分离Amoeba中间件3.1 原理应用连接Amoeba代理端口8066。Amoeba解析SQL写操作INSERT/UPDATE/DELETE发往主库池读操作SELECT发往从库池并轮询负载均衡。3.2 配置要点安装JDK解压Amoeba。修改conf/amoeba.xml设置客户端认证账号、defaultPool、writePool、readPool。修改conf/dbServers.xml定义后端MySQL节点主库和从库地址、用户名密码、库名。启动Amoeba/usr/local/amoeba/bin/amoeba start客户端连接Amoebamysql -u amoeba -p123456 -h amoeba_ip -P 8066四、MHA高可用4.1 MHA概述解决MySQL主从复制中主库单点故障实现自动故障转移0-30秒。组成Manager管理节点 Node数据节点。原理监控主库宕机后从候选从库中选举新主库并让其他从库指向新主库。4.2 实验环境一主两从主机名IP角色mysql-master192.168.108.131主库安装nodemysql-slave01192.168.108.132从库候选主安装nodemysql-slave02192.168.108.133从库安装nodemha192.168.108.130管理节点安装nodemanager4.3 配置步骤配置主从复制如上。所有节点安装MHA nodemanager节点安装MHA manager。配置SSH免密登录manager到所有节点所有节点之间互相免密。准备虚拟IP脚本master_ip_failover设置VIP如192.168.108.200。创建MHA配置文件/etc/masterha/app1.cnf指定用户、密码、复制用户、日志路径、VIP脚本等。检查SSH和复制状态masterha_check_ssh--conf/etc/masterha/app1.cnf masterha_check_repl--conf/etc/masterha/app1.cnf手动在主库绑定VIPifconfig ens33:1 192.168.108.200/24启动MHAnohupmasterha_manager--conf/etc/masterha/app1.cnf\--remove_dead_master_conf--ignore_last_failover\/dev/null/var/log/masterha/app1/manager.log214.4 故障模拟与恢复停止主库MySQLsystemctl stop mysqlMHA自动切换VIP漂移到候选从库slave01其他从库重新指向新主库。原主库恢复后重新配置为从库指向新主库。五、补充MySQL 5.7源码编译安装安装依赖gcc cmake ncurses-devel bison等。下载MySQL源码和boost库。cmake指定安装路径、字符集、存储引擎、boost路径等。make make install初始化数据库mysqld --initialize-insecure --usermysql配置systemd服务文件设置环境变量。
MySQL高可用架构实战：备份恢复、主从复制、读写分离与MHA
MySQL高可用架构实战备份恢复、主从复制、读写分离与MHA一、MySQL备份与恢复1.1 备份分类物理备份直接复制数据库文件冷备、热备、温备。逻辑备份导出SQL语句如mysqldump。增量备份基于二进制日志。1.2 冷备份物理冷备systemctl stop mysqldtarczf /backup/mysql-backup-$(date%F).tar.gz /var/lib/mysql systemctl start mysqld# 恢复时停止服务解压覆盖修改权限后启动1.3 逻辑备份mysqldump# 备份单个数据库mysqldump-uroot-pschool/backup/school.sql# 备份多个数据库mysqldump-uroot-p--databasesschool mysql/backup/school_mysql.sql# 备份所有数据库mysqldump-uroot-p--all-databases/backup/all.sql# 备份指定表mysqldump-uroot-pschool student/backup/student.sql恢复mysql -u root -p school /backup/school.sql或在mysql内source /backup/student.sql1.4 增量备份基于binlog启用binloglog-binmysql-bin完整备份后刷新日志mysqladmin flush-logs后续所有变更记录在新binlog中。恢复时先恢复完整备份再使用mysqlbinlog重放增量binlog。mysqlbinlog mysql-bin.000003|mysql-uroot-p二、主从复制2.1 核心原理binlog主库记录所有变更。IO线程从库请求并接收binlog写入relay log。SQL线程从库重放relay log。2.2 配置步骤基于5.7主库[mysqld] server-id1 log-binmaster-bin log-slave-updates创建复制用户GRANTREPLICATIONSLAVEON*.*TOmyslave192.168.%IDENTIFIEDBY123456;FLUSHPRIVILEGES;SHOWMASTERSTATUS;-- 记录File和Position从库[mysqld] server-id2 relay-logrelay-log-bin relay-log-indexslave-relay-bin.index执行CHANGE MASTERTOMASTER_HOST主库IP,MASTER_USERmyslave,MASTER_PASSWORD123456,MASTER_LOG_FILEmaster-bin.000001,MASTER_LOG_POS604;STARTSLAVE;SHOWSLAVESTATUS\G;-- 查看两个YES三、读写分离Amoeba中间件3.1 原理应用连接Amoeba代理端口8066。Amoeba解析SQL写操作INSERT/UPDATE/DELETE发往主库池读操作SELECT发往从库池并轮询负载均衡。3.2 配置要点安装JDK解压Amoeba。修改conf/amoeba.xml设置客户端认证账号、defaultPool、writePool、readPool。修改conf/dbServers.xml定义后端MySQL节点主库和从库地址、用户名密码、库名。启动Amoeba/usr/local/amoeba/bin/amoeba start客户端连接Amoebamysql -u amoeba -p123456 -h amoeba_ip -P 8066四、MHA高可用4.1 MHA概述解决MySQL主从复制中主库单点故障实现自动故障转移0-30秒。组成Manager管理节点 Node数据节点。原理监控主库宕机后从候选从库中选举新主库并让其他从库指向新主库。4.2 实验环境一主两从主机名IP角色mysql-master192.168.108.131主库安装nodemysql-slave01192.168.108.132从库候选主安装nodemysql-slave02192.168.108.133从库安装nodemha192.168.108.130管理节点安装nodemanager4.3 配置步骤配置主从复制如上。所有节点安装MHA nodemanager节点安装MHA manager。配置SSH免密登录manager到所有节点所有节点之间互相免密。准备虚拟IP脚本master_ip_failover设置VIP如192.168.108.200。创建MHA配置文件/etc/masterha/app1.cnf指定用户、密码、复制用户、日志路径、VIP脚本等。检查SSH和复制状态masterha_check_ssh--conf/etc/masterha/app1.cnf masterha_check_repl--conf/etc/masterha/app1.cnf手动在主库绑定VIPifconfig ens33:1 192.168.108.200/24启动MHAnohupmasterha_manager--conf/etc/masterha/app1.cnf\--remove_dead_master_conf--ignore_last_failover\/dev/null/var/log/masterha/app1/manager.log214.4 故障模拟与恢复停止主库MySQLsystemctl stop mysqlMHA自动切换VIP漂移到候选从库slave01其他从库重新指向新主库。原主库恢复后重新配置为从库指向新主库。五、补充MySQL 5.7源码编译安装安装依赖gcc cmake ncurses-devel bison等。下载MySQL源码和boost库。cmake指定安装路径、字符集、存储引擎、boost路径等。make make install初始化数据库mysqld --initialize-insecure --usermysql配置systemd服务文件设置环境变量。
