文章目录mysql的备份与恢复冷备份逻辑备份恢复数据增量备份mysql的备份与恢复数据备份的重要性在生产环境中数据的安全性至关重要任何数据的丢失都可能产生严重的后果造成数据丢失的原因程序错误人为操作错误运算错误磁盘故障灾难如火灾、地震和盗窃数据库备份的分类从物理与逻辑的角度备份可分为物理备份对数据库操作系统的物理文件如数据文件、日志文件等的备份物理备份方法冷备份脱机备份是在关闭数据库的时候进行的热备份联机备份数据库处于运行状态依赖于数据库的日志文件温备份数据库锁定表格不可写入但可读的状态下进行备份操作逻辑备份对数据库逻辑组件如表等数据库对象的备份常见的备份方法物理冷备备份时数据库处于关闭状态直接打包数据库文件备份速度快恢复时也是最简单的专用备份工具mydump或mysqlhotcopymysqldump常用的逻辑备份工具mysqlhotcopy仅拥有备份MylSAM和ARCHIVE表启用二进制日志进行增量备份进行增量备份需要刷新二进制日志第三方工具备份免费的MySQL热备份软件PerconaXtraBackup冷备份# 备份推荐写法# 先停止服务#systemctl stop 停止系统服务[rootmysql01 ~]# systemctl stop mysqld#进入数据目录#cd 切换工作目录[rootmysql01 ~]# cd /usr/local/mysql/data#创建备份目录#mkdir 创建目录[rootmysql01 data]# mkdir /mysql_bak#打包当前目录所有文件为以日期命名的压缩包#tar czf 打包并用gzip压缩[rootmysql01 data]# tar czf /mysql_bak/mysql-backup-$(date %F).tar.gz *# 备份完成后启动服务#systemctl start 启动系统服务[rootmysql01 data]# systemctl start mysqld#测试服务正常[rootmysql01 ~]# 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. Typehelp;or\hforhelp. Type\ctoclearthe current input statement. mysql# 删除数据# 先停止服务#systemctl stop 停止系统服务[rootmysql01 ~]# systemctl stop mysqld# 清空数据目录谨慎操作#rm 删除文件/目录#-rf 递归强制删除(谨慎)[rootmysql01 ~]# rm -rf /usr/local/mysql/data/*#再次开启测试发现数据库坏了#systemctl start 启动系统服务[rootmysql01 ~]# systemctl start mysqld[rootmysql01 ~]# mysql -uroot -phuaweimysql:[Warning]Using a password on thecommandline interface can be insecure. ERROR1045(28000): Access deniedforuserrootlocalhost(using password: YES)#停止数据库恢复数据库#systemctl stop 停止系统服务[rootmysql01 ~]# systemctl stop mysqld#解压备份包到数据目录#tar xzf 解压gzip压缩包#-C 解压到指定目录[rootmysql01 ~]# tar xzf /mysql_bak/mysql-backup-2026-09-30.tar.gz -C /usr/local/mysql/data/# 恢复权限#chown 修改文件属主#-R 递归修改目录下所有文件[rootmysql01 ~]# chown -R mysql:mysql /usr/local/mysql/data# 启动服务#systemctl start 启动系统服务[rootmysql01 ~]# systemctl start mysqld#再次测试发现数据库恢复了[rootmysql01 ~]# 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. Typehelp;or\hforhelp. Type\ctoclearthe current input statement. mysql逻辑备份#备份数据库#先启动mysqld服务#systemctl start 启动系统服务[rootmysql01 ~]# systemctl start mysqld#使用mysqldump把school库备份为school.sql#-u 指定登录用户名#-p 提示输入密码[rootmysql01 ~]# mysqldump -u root -p school /mysql_bak/school.sqlEnter password:#输入数据库密码#查看备份文件是否生成#ls 列出目录内容[rootmysql01 ~]# ls /mysql_bak/school.sqlmysql_bak/school.sql#查看备份SQL内容#cat 查看文件内容[rootmysql01 ~]# cat /mysql_bak/school.sql#备份多个数据库#同时备份school和mysql两个库#mysqldump 逻辑备份工具#-u 指定登录用户名#-p 提示输入密码#--databases 指定要备份的库名(可多个)[rootmysql01 ~]# mysqldump -u root -p --databases school mysql /mysql_bak/school-mysql.sqlEnter password:#输入数据库密码#cat 查看文件内容[rootmysql01 ~]# cat /mysql_bak/school-mysql.sql#备份所有数据库#--opt启用优化选项--all-databases备份全部库#mysqldump 逻辑备份工具#-u 指定登录用户名#-p 提示输入密码[rootmysql01 ~]# mysqldump -u root -p --opt --all-databases /mysql_bak/all.sqlEnter password:#输入数据库密码#cat 查看文件内容[rootmysql01 ~]# cat /mysql_bak/all.sql#备份整个表#只备份school库中的info表#mysqldump 逻辑备份工具#-u 指定登录用户名#-p 提示输入密码[rootmysql01 ~]# mysqldump -u root -p school info /mysql_bak/info.sqlEnter password:#输入数据库密码#cat 查看文件内容[rootmysql01 ~]# cat /mysql_bak/info.sql恢复数据#登录mysql-u 指定登录用户名-p 提示输入密码[rootmysql01 ~]# mysql -u root -pEnter password:#输入数据库密码#进入school库#USE 切换到指定数据库mysqluse school#查看表#SHOW TABLES 列出当前库所有表mysqlshow tables;------------------|Tables_in_school|#表头(列名)------------------|info|------------------1rowinset(0.00sec)#查询结果行数#删除info表测试恢复#DROP TABLE 删除表结构及数据mysqldrop table info;Query OK,0rows affected(0.01sec)#执行成功#用source命令导入备份SQL恢复表mysqlsource/mysql_bak/info.sql#再次查看表info已恢复#SHOW TABLES 列出当前库所有表mysqlshow tables;------------------|Tables_in_school|#表头(列名)------------------|info|------------------1rowinset(0.00sec)#查询结果行数增量备份#编辑配置文件开启二进制日志#vim 编辑文件[rootmysql01 ~]# vim /etc/my.cnf------------------------[mysqld]#开启binlog二进制日志段末加这一行log-binmysql-bin#这段下面最后加一行#重启服务使配置生效#systemctl start 启动系统服务[rootweb-server ~]# systemctl restart mysqld.service#查看数据目录已生成mysql-bin.000001二进制日志#ls 列出目录内容[rootmysql01 ~]# 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 提示输入密码[rootmysql01 ~]# mysqldump -uroot -p school /opt/school.sqlEnter password:#输入数据库密码#日志刷新生效切割出新的binlog#mysqladmin MySQL管理工具#-u 指定登录用户名#-p 提示输入密码#flush-logs 刷新并切割二进制日志[rootmysql01 ~]# mysqladmin -uroot -p flush-logsEnter password:#输入数据库密码#新产生的mysql-bin.000002只记录上次刷新后的操作#ls 列出目录内容[rootmysql01 ~]# 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 提示输入密码[rootmysql01 ~]# mysql -uroot -pEnter password:#输入数据库密码----------------------------------------#USE 切换到指定数据库mysqluse school;Reading table informationforcompletion of table andcolumnnames#正在读取表结构缓存You can turn off this feature to get a quicker startup with-A#提示可用-A参数加速启动Database changed#已切换到目标库#SELECT 查询表数据mysqlselect* from info;-----------------------------------|id|name|score|address|hobby|#表头(列名)-----------------------------------|1|唐三|90.00|广州|1||2|叶凡|91.00|伦敦|2||4|曹操|66.50|合肥|4|-----------------------------------3rowsinset(0.00sec)#查询结果行数#再次插入数据生产增量备份#INSERT 插入数据行mysqlinsert into info(name,score,address,hobby)values(美猴王,75,武汉,1);Query OK,1row affected(0.00sec)#执行成功mysqlselect* 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客户端mysqlexitBye#已退出mysql#再次刷新日志切割出新的binlog#mysqladmin MySQL管理工具#-u 指定登录用户名#-p 提示输入密码#flush-logs 刷新并切割二进制日志[rootmysql01 ~]# mysqladmin -uroot -p flush-logEnter password:#输入数据库密码#新产生mysql-bin.000003日志记录insert操作#ls 列出目录内容[rootmysql01 ~]# 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 提示输入密码[rootmysql01 ~]# mysql -uroot -pEnter password:#输入数据库密码---------------------------------------------------------------#USE 切换到指定数据库mysqluse school;Reading table informationforcompletion of table andcolumnnames#正在读取表结构缓存You can turn off this feature to get a quicker startup with-A#提示可用-A参数加速启动Database changed#已切换到目标库#再插入一条超人记录#INSERT 插入数据行mysqlinsert into info(name,score,address,hobby)values(超人,83,上海,2);Query OK,1row affected(0.00sec)#执行成功#SELECT 查询表数据mysqlselect* 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客户端mysqlexitBye#已退出mysql#刷新日志生效#mysqladmin MySQL管理工具#-u 指定登录用户名#-p 提示输入密码#flush-logs 刷新并切割二进制日志[rootmysql01 ~]# mysqladmin -uroot -p flush-logEnter password:#输入数据库密码#环境准备#mysql 命令行客户端#-u 指定登录用户名#-p 提示输入密码[rootmysql01 ~]# mysql -uroot -pEnter password:#输入数据库密码--------------------------------------------------------------#USE 切换到指定数据库mysqluse 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 过滤删除条件mysqldelete from info whereid6;Query OK,1row affected(0.00sec)#执行成功#删除id为5的美猴王记录#DELETE 删除表记录#WHERE 过滤删除条件mysqldelete from info whereid5;Query OK,1row affected(0.01sec)#执行成功#SELECT 查询表数据mysqlselect* from info;-----------------------------------|id|name|score|address|hobby|#表头(列名)-----------------------------------|1|唐三|90.00|广州|1||2|叶凡|91.00|伦敦|2||4|曹操|66.50|合肥|4|-----------------------------------3rowsinset(0.00sec)#查询结果行数#exit 退出mysql客户端mysqlexitBye#已退出mysql#查看数据目录已有000004日志#ls 列出目录内容[rootmysql01 ~]# 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-outputdecode-rows 将行事件解码为SQL#-v 以注释形式显示行事件还原[rootmysql01 ~]# mysqlbinlog --no-defaults --base64-outputdecode-rows -v /usr/local/mysql/data/mysql-bin.000003#解码查看000004日志内容#mysqlbinlog 二进制日志解析工具#--no-defaults 不读取任何配置文件#--base64-outputdecode-rows 将行事件解码为SQL#-v 以注释形式显示行事件还原[rootmysql01 ~]# mysqlbinlog --no-defaults --base64-outputdecode-rows -v /usr/local/mysql/data/mysql-bin.000004#恢复操作恢复时如果被拒绝是有其他mysql进程占用了#把000003日志重放给mysql以恢复其中的insert操作#mysqlbinlog 二进制日志解析工具#--no-defaults 不读取任何配置文件[rootmysql01 ~]# mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000003 | mysql -uroot -pEnter password:#输入数据库密码#验证#mysql 命令行客户端#-u 指定登录用户名#-p 提示输入密码[rootweb-server ~]# mysql -uroot -pEnter password:#输入数据库密码#USE 切换到指定数据库mysqluse school;#SELECT 查询表数据mysqlselect* 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)#查询结果行数