企业官网建设流程全解析

mysql的备份与恢复

数据备份的重要性

在生产环境中，数据的安全性至关重要

任何数据的丢失都可能产生严重的后果

造成数据丢失的原因

• 程序错误
• 人为操作错误
• 运算错误
• 磁盘故障
• 灾难（如火灾、地震）和盗窃

数据库备份的分类

从物理与逻辑的角度，备份可分为

物理备份：对数据库操作系统的物理文件（如数据文件、日志文件等）的备份

物理备份方法

• 冷备份（脱机备份）：是在关闭数据库的时候进行的
• 热备份（联机备份）：数据库处于运行状态，依赖于数据库的日志文件
• 温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作

逻辑备份：对数据库逻辑组件（如：表等数据库对象）的备份

常见的备份方法

物理冷备

备份时数据库处于关闭状态，直接打包数据库文件

备份速度快，恢复时也是最简单的

专用备份工具mydump或mysqlhotcopy

mysqldump常用的逻辑备份工具

mysqlhotcopy仅拥有备份MylSAM和ARCHIVE表

启用二进制日志进行增量备份

进行增量备份，需要刷新二进制日志

第三方工具备份

免费的MySQL热备份软件PerconaXtraBackup

冷备份

# 备份（推荐写法）# 先停止服务#systemctl stop 停止系统服务[root@mysql01 ~]# systemctl stop mysqld#进入数据目录#cd 切换工作目录[root@mysql01 ~]# cd /usr/local/mysql/data#创建备份目录#mkdir 创建目录[root@mysql01 data]# mkdir /mysql_bak#打包当前目录所有文件为以日期命名的压缩包#tar czf 打包并用gzip压缩[root@mysql01 data]# tar czf /mysql_bak/mysql-backup-$(date +%F).tar.gz *# 备份完成后启动服务#systemctl start 启动系统服务[root@mysql01 data]# systemctl start mysqld#测试服务正常[root@mysql01 ~]# mysql -uroot -phuaweimysql:[Warning]Using a password on thecommandline interface can be insecure. Welcome to the MySQL monitor. Commands end with;or\g. Your MySQL connectionidis3Server version:5.7.17 Source distribution Copyright(c)2000,2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type'help;'or'\h'forhelp. Type'\c'toclearthe current input statement. mysql># 删除数据# 先停止服务#systemctl stop 停止系统服务[root@mysql01 ~]# systemctl stop mysqld# 清空数据目录（谨慎操作！）#rm 删除文件/目录#-rf 递归强制删除(谨慎)[root@mysql01 ~]# rm -rf /usr/local/mysql/data/*#再次开启测试，发现数据库坏了#systemctl start 启动系统服务[root@mysql01 ~]# systemctl start mysqld[root@mysql01 ~]# mysql -uroot -phuaweimysql:[Warning]Using a password on thecommandline interface can be insecure. ERROR1045(28000): Access deniedforuser'root'@'localhost'(using password: YES)#停止数据库，恢复数据库#systemctl stop 停止系统服务[root@mysql01 ~]# systemctl stop mysqld#解压备份包到数据目录#tar xzf 解压gzip压缩包#-C 解压到指定目录[root@mysql01 ~]# tar xzf /mysql_bak/mysql-backup-2026-09-30.tar.gz -C /usr/local/mysql/data/# 恢复权限#chown 修改文件属主#-R 递归修改目录下所有文件[root@mysql01 ~]# chown -R mysql:mysql /usr/local/mysql/data# 启动服务#systemctl start 启动系统服务[root@mysql01 ~]# systemctl start mysqld#再次测试，发现数据库恢复了[root@mysql01 ~]# mysql -uroot -phuaweimysql:[Warning]Using a password on thecommandline interface can be insecure. Welcome to the MySQL monitor. Commands end with;or\g. Your MySQL connectionidis3Server version:5.7.17 Source distribution Copyright(c)2000,2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type'help;'or'\h'forhelp. Type'\c'toclearthe current input statement. mysql>

逻辑备份

#备份数据库#先启动mysqld服务#systemctl start 启动系统服务[root@mysql01 ~]# systemctl start mysqld#使用mysqldump把school库备份为school.sql#-u 指定登录用户名#-p 提示输入密码[root@mysql01 ~]# mysqldump -u root -p school > /mysql_bak/school.sqlEnter password:#输入数据库密码#查看备份文件是否生成#ls 列出目录内容[root@mysql01 ~]# ls /mysql_bak/school.sqlmysql_bak/school.sql#查看备份SQL内容#cat 查看文件内容[root@mysql01 ~]# cat /mysql_bak/school.sql#备份多个数据库#同时备份school和mysql两个库#mysqldump 逻辑备份工具#-u 指定登录用户名#-p 提示输入密码#--databases 指定要备份的库名(可多个)[root@mysql01 ~]# mysqldump -u root -p --databases school mysql > /mysql_bak/school-mysql.sqlEnter password:#输入数据库密码#cat 查看文件内容[root@mysql01 ~]# cat /mysql_bak/school-mysql.sql#备份所有数据库#--opt启用优化选项，--all-databases备份全部库#mysqldump 逻辑备份工具#-u 指定登录用户名#-p 提示输入密码[root@mysql01 ~]# mysqldump -u root -p --opt --all-databases > /mysql_bak/all.sqlEnter password:#输入数据库密码#cat 查看文件内容[root@mysql01 ~]# cat /mysql_bak/all.sql#备份整个表#只备份school库中的info表#mysqldump 逻辑备份工具#-u 指定登录用户名#-p 提示输入密码[root@mysql01 ~]# mysqldump -u root -p school info > /mysql_bak/info.sqlEnter password:#输入数据库密码#cat 查看文件内容[root@mysql01 ~]# cat /mysql_bak/info.sql

恢复数据

#登录mysql，-u 指定登录用户名，-p 提示输入密码[root@mysql01 ~]# mysql -u root -pEnter password:#输入数据库密码#进入school库#USE 切换到指定数据库mysql>use school#查看表#SHOW TABLES 列出当前库所有表mysql>show tables;+------------------+|Tables_in_school|#表头(列名)+------------------+|info|+------------------+1rowinset(0.00sec)#查询结果行数#删除info表测试恢复#DROP TABLE 删除表结构及数据mysql>drop table info;Query OK,0rows affected(0.01sec)#执行成功#用source命令导入备份SQL恢复表mysql>source/mysql_bak/info.sql#再次查看表，info已恢复#SHOW TABLES 列出当前库所有表mysql>show tables;+------------------+|Tables_in_school|#表头(列名)+------------------+|info|+------------------+1rowinset(0.00sec)#查询结果行数

增量备份

#编辑配置文件开启二进制日志#vim 编辑文件[root@mysql01 ~]# vim /etc/my.cnf------------------------[mysqld]#开启binlog二进制日志，段末加这一行log-bin=mysql-bin#这段下面最后加一行#重启服务使配置生效#systemctl start 启动系统服务[root@web-server ~]# systemctl restart mysqld.service#查看数据目录，已生成mysql-bin.000001二进制日志#ls 列出目录内容[root@mysql01 ~]# ls /usr/local/mysql/data/auto.cnf client-cert.pem ibdata1 ibtmp1 mysql-bin.index public_key.pem server-key.pem ca-key.pem client-key.pem ib_logfile0 mysql performance_schema school sys ca.pem ib_buffer_pool ib_logfile1 mysql-bin.000001 private_key.pem server-cert.pem#先进行完整性备份#mysqldump 逻辑备份工具#-u 指定登录用户名#-p 提示输入密码[root@mysql01 ~]# mysqldump -uroot -p school > /opt/school.sqlEnter password:#输入数据库密码#日志刷新生效，切割出新的binlog#mysqladmin MySQL管理工具#-u 指定登录用户名#-p 提示输入密码#flush-logs 刷新并切割二进制日志[root@mysql01 ~]# mysqladmin -uroot -p flush-logsEnter password:#输入数据库密码#新产生的mysql-bin.000002只记录上次刷新后的操作#ls 列出目录内容[root@mysql01 ~]# ls /usr/local/mysql/data/auto.cnf client-cert.pem ibdata1 ibtmp1 mysql-bin.000002 private_key.pem server-cert.pem ca-key.pem client-key.pem ib_logfile0 mysql mysql-bin.index public_key.pem server-key.pem ca.pem ib_buffer_pool ib_logfile1 mysql-bin.000001 performance_schema school sys#登录mysql#-u 指定登录用户名#-p 提示输入密码[root@mysql01 ~]# mysql -uroot -pEnter password:#输入数据库密码----------------------------------------#USE 切换到指定数据库mysql>use school;Reading table informationforcompletion of table andcolumnnames#正在读取表结构缓存You can turn off this feature to get a quicker startup with-A#提示可用-A参数加速启动Database changed#已切换到目标库#SELECT 查询表数据mysql>select* from info;+----+--------+-------+---------+-------+|id|name|score|address|hobby|#表头(列名)+----+--------+-------+---------+-------+|1|唐三|90.00|广州|1||2|叶凡|91.00|伦敦|2||4|曹操|66.50|合肥|4|+----+--------+-------+---------+-------+3rowsinset(0.00sec)#查询结果行数#再次插入数据生产增量备份#INSERT 插入数据行mysql>insert into info(name,score,address,hobby)values('美猴王',75,'武汉',1);Query OK,1row affected(0.00sec)#执行成功mysql>select* from info;+----+-----------+-------+---------+-------+|id|name|score|address|hobby|+----+-----------+-------+---------+-------+|1|唐三|90.00|广州|1||2|叶凡|91.00|伦敦|2||4|曹操|66.50|合肥|4||5|美猴王|75.00|武汉|1|+----+-----------+-------+---------+-------+4rowsinset(0.00sec)#退出mysql#exit 退出mysql客户端mysql>exitBye#已退出mysql#再次刷新日志，切割出新的binlog#mysqladmin MySQL管理工具#-u 指定登录用户名#-p 提示输入密码#flush-logs 刷新并切割二进制日志[root@mysql01 ~]# mysqladmin -uroot -p flush-logEnter password:#输入数据库密码#新产生mysql-bin.000003日志记录insert操作#ls 列出目录内容[root@mysql01 ~]# ls /usr/local/mysql/data/auto.cnf client-cert.pem ibdata1 ibtmp1 mysql-bin.000002 performance_schema school sys ca-key.pem client-key.pem ib_logfile0 mysql mysql-bin.000003 private_key.pem server-cert.pem ca.pem ib_buffer_pool ib_logfile1 mysql-bin.000001 mysql-bin.index public_key.pem server-key.pem#再次登录mysql#-u 指定登录用户名#-p 提示输入密码[root@mysql01 ~]# mysql -uroot -pEnter password:#输入数据库密码---------------------------------------------------------------#USE 切换到指定数据库mysql>use school;Reading table informationforcompletion of table andcolumnnames#正在读取表结构缓存You can turn off this feature to get a quicker startup with-A#提示可用-A参数加速启动Database changed#已切换到目标库#再插入一条超人记录#INSERT 插入数据行mysql>insert into info(name,score,address,hobby)values('超人',83,'上海',2);Query OK,1row affected(0.00sec)#执行成功#SELECT 查询表数据mysql>select* from info;+----+-----------+-------+---------+-------+|id|name|score|address|hobby|#表头(列名)+----+-----------+-------+---------+-------+|1|唐三|90.00|广州|1||2|叶凡|91.00|伦敦|2||4|曹操|66.50|合肥|4||5|美猴王|75.00|武汉|1||6|超人|83.00|上海|2|+----+-----------+-------+---------+-------+5rowsinset(0.00sec)#查询结果行数#exit 退出mysql客户端mysql>exitBye#已退出mysql#刷新日志生效#mysqladmin MySQL管理工具#-u 指定登录用户名#-p 提示输入密码#flush-logs 刷新并切割二进制日志[root@mysql01 ~]# mysqladmin -uroot -p flush-logEnter password:#输入数据库密码#环境准备#mysql 命令行客户端#-u 指定登录用户名#-p 提示输入密码[root@mysql01 ~]# mysql -uroot -pEnter password:#输入数据库密码--------------------------------------------------------------#USE 切换到指定数据库mysql>use school;Reading table informationforcompletion of table andcolumnnames#正在读取表结构缓存You can turn off this feature to get a quicker startup with-A#提示可用-A参数加速启动Database changed#已切换到目标库#删除内容#删除id为6的超人记录#DELETE 删除表记录#WHERE 过滤删除条件mysql>delete from info whereid=6;Query OK,1row affected(0.00sec)#执行成功#删除id为5的美猴王记录#DELETE 删除表记录#WHERE 过滤删除条件mysql>delete from info whereid=5;Query OK,1row affected(0.01sec)#执行成功#SELECT 查询表数据mysql>select* from info;+----+--------+-------+---------+-------+|id|name|score|address|hobby|#表头(列名)+----+--------+-------+---------+-------+|1|唐三|90.00|广州|1||2|叶凡|91.00|伦敦|2||4|曹操|66.50|合肥|4|+----+--------+-------+---------+-------+3rowsinset(0.00sec)#查询结果行数#exit 退出mysql客户端mysql>exitBye#已退出mysql#查看数据目录，已有000004日志#ls 列出目录内容[root@mysql01 ~]# ls /usr/local/mysql/data/auto.cnf client-cert.pem ibdata1 ibtmp1 mysql-bin.000002 mysql-bin.index public_key.pem server-key.pem ca-key.pem client-key.pem ib_logfile0 mysql mysql-bin.000003 performance_schema school sys ca.pem ib_buffer_pool ib_logfile1 mysql-bin.000001 mysql-bin.000004 private_key.pem server-cert.pem#查看日志文件，vim看日志是乱码，需用mysqlbinlog解码#解码查看000003日志内容#--no-defaults 不读取任何配置文件#--base64-output=decode-rows 将行事件解码为SQL#-v 以注释形式显示行事件还原[root@mysql01 ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000003#解码查看000004日志内容#mysqlbinlog 二进制日志解析工具#--no-defaults 不读取任何配置文件#--base64-output=decode-rows 将行事件解码为SQL#-v 以注释形式显示行事件还原[root@mysql01 ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000004#恢复操作，恢复时如果被拒绝，是有其他mysql进程占用了#把000003日志重放给mysql以恢复其中的insert操作#mysqlbinlog 二进制日志解析工具#--no-defaults 不读取任何配置文件[root@mysql01 ~]# mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000003 | mysql -uroot -pEnter password:#输入数据库密码#验证#mysql 命令行客户端#-u 指定登录用户名#-p 提示输入密码[root@web-server ~]# mysql -uroot -pEnter password:#输入数据库密码#USE 切换到指定数据库mysql>use school;#SELECT 查询表数据mysql>select* from info;+----+--------+-------+---------+-------+|id|name|score|address|hobby|#表头(列名)+----+--------+-------+---------+-------+|1|唐三|90.00|广州|1||2|叶凡|91.00|伦敦|2||4|曹操|66.50|合肥|4||6|超人|83.00|上海|2|+----+--------+-------+---------+-------+4rowsinset(0.00sec)#查询结果行数