MySQL的备份

MySQL备份与恢复

  • mysql出现主从同步延迟的原因有哪些？如何解决
  • 数据读写分离的软件，mysql-proxy，amoeba
  • mysql-mmm高可用软件
  • mysql半同步运用，xtarbackup物理备份
  • mysql+heartbeat+drbd高可用

备份工具

社区版安装包中备份工具

  • msyqldump（逻辑的备份，只能全量备份）重点
    • 企业版和社区版都包含
    • 本质上使用SQL语句描述数据库及数据并导出
    • 在MYSISAM引擎上锁表，innodb引擎上锁行。
    • 数据量大不推荐使用
  • mysqlhotcopy（物理备份工具）
    • 企业版和社区版都包含
    • perl写的一个脚本，本质上是使用锁表语句之后再进行拷贝数据
    • 只支持MYSISAM数据引擎

企业版安装包中备份工具

  • mysqlbackup
    • 在线备份
    • 增量备份
    • 部分备份
    • 再某个特定的时间一致性状态备份

第三方备份工具

  • XtraBackup和innobackupex（物理备份）
    • XtraBackup是对innodb引擎做数据备份的工具，支持==在线热备（备份时不影响数据的读写），是商业备份工具innodb Hotbackup是一个很好的替代品。
    • XtraBackup主要有两个工具XtraBackup，innobackupex
    • XtraBackup只能备份innodb和XtraDB两种数据表，不能备份myisam类型的表
    • innobackupex是将XtraBackup进行封装的perl脚本，所以能够同时处理innodb和myisam的存储引擎，但是再处理myisam需要加锁
  • mydumper（逻辑备份，备份SQL语句）

多线程备份工具

备份的方法

  • 完全备份（全备）
  • 增量备份（增量备份是基于全量备份）

mysql的逻辑备份部分

  • 本质：导出sql语句
  • 优点：无论什么引擎，都可以备份
  • 缺点：速度慢，导入的时候可能会出现不兼容的突发状况==无法直接做增量备份
  • 提供三种级别备份：表级,库级,全库级

基本语法

#表级备份
mysqldunp 【选项】 database 【tables】
#库级别的备份
mysqldunp 【选项】 --database 【options】 DB1【DB2 DB3 ...】

#全库级别的备份
mysqldunp 【选项】 --all-database 【options】

将坦克表备份

表备份
#表级别的备份
[root@localhost ~]# mysqldump kings tanks -S /my_mysql/3306/mysql.sock -p123456  > /tmp/sqlbackup/table/tanks-$(date +%F).sql 
Warning: Using a password on the command line interface can be insecure.
[root@localhost ~]# ls -l /tmp/sqlbackup/table/tanks-2022-08-24.sql 
-rw-r--r--. 1 root root 2874 8月  24 08:50 /tmp/sqlbackup/table/tanks-2022-08-24.sql

#表级别的还原
#第一种方式
mysql 数据库名字 参数 < sql备份的地方
mysql kings -p123456 -S /my_mysql/3306/mysql.sock < /tmp/sqlbackup/table/tanks-2022-08-24.sql 
#第二种方式，进入到mysql中
mysql> use 数据库;
mysql> source sql的备份路径;
库备份

语法

mysqldump --databases kings -p123456 -S /my_mysql/3306/mysql.sock > /tmp/sqlbackup/database/kings-$(date +%F).sql

[root@localhost ~]# mysqldump --databases kings -p123456 -S /my_mysql/3306/mysql.sock > /tmp/sqlbackup/database/kings-$(date +%F).sql 
Warning: Using a password on the command line interface can be insecure.
[root@localhost ~]# ls -l /tmp/sqlbackup/database/kings-2022-08-24.sql 
-rw-r--r--. 1 root root 3014 8月  24 09:54 /tmp/sqlbackup/database/kings-2022-08-24.sql

还原

#库还原
#先将数据库中的表删除之后再进行恢复
[root@localhost ~]# mysql -p123456 -S /my_mysql/3306/mysql.sock  < /tmp/sqlbackup/database/kings-2022-08-24.sql 
Warning: Using a password on the command line interface can be insecure.


mysql> select * from kings.tanks;
+----+--------------+-----------------------------------+-----------------+-------+-----------+---------+--------------+
| id | name         | skills                            | summoner_skills | price | camp      | pic_url | introduction |
+----+--------------+-----------------------------------+-----------------+-------+-----------+---------+--------------+
|  1 | 鲁班         | 二技能全图，被动扫射              | flush           |  5888 | NULL      | NULL    |              |
|  2 | 甄姬         | 技能会冰冻敌人                    | flush           |  2888 | NULL      | NULL    |              |
|  3 | 阿古朵       | 其在一个球上                      | ghost           | 13888 | 野兽      | NULL    |              |
|  4 | 鲁班大师     | 可以把队友吸过来                  | flush           | 13888 | 机器      | NULL    |              |
|  5 | 猪八戒       | 攻击敌人回血                      | flush           | 19999 | 野兽      | NULL    |              |
|  6 | 雅典娜       | 被打死之后可以全图跑动            | ghost           | 18888 | 神仙派    | NULL    |              |
|  7 | 钟馗         | 钩子特别恶心                      | flush           | 15555 | 灵魂      | NULL    |              |
|  8 | 钟馗         | 钩子特别恶心                      | flush           | 15555 | 灵魂      | NULL    |              |
|  9 | 梦奇         | 泡泡无限护盾                      | flush           |  7555 | 灵魂      | NULL    |              |
| 10 | 刘邦         | 大招全图支援                      | flush           |  8555 | 神仙派    | NULL    |              |
| 11 | 亚瑟         | 人在塔在                          | flush           |  9225 | 神仙派    | NULL    |              |
+----+--------------+-----------------------------------+-----------------+-------+-----------+---------+--------------+
11 rows in set (0.00 sec)
全库备份

全库备份需要开启二进制日志

#全库级别备份
#在库级备份！！！在此一定要开机二进制日志！！
[mysqld]
。。。。
server-id=10
log-bin=/my_mysql/3306/binlog

#备份
[root@localhost ~]# ls -l /tmp/sqlbackup/alldatabase/all-2022-08-24.sql 
-rw-r--r--. 1 root root 719750 8月  24 10:15 /tmp/sqlbackup/alldatabase/all-2022-08-24.sql
[root@localhost ~]# mysqldump --all-databases --master-data --single-transaction -p123456 -S /my_mysql/3306/mysql.sock > /tmp/sqlbackup/alldatabase/all-$(date +%F).sql
Warning: Using a password on the command line interface can be insecure.
[root@localhost ~]# ls -l /tmp/sqlbackup/alldatabase/
总用量 704
-rw-r--r--. 1 root root 719750 8月  24 10:16 all-2022-08-24.sql
#删除数据库
mysql> drop database  kings;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
#备份恢复
[root@localhost ~]# mysql -p123456 -S /my_mysql/3306/mysql.sock < /tmp/sqlbackup/alldatabase/all-2022-08-24.sql 
Warning: Using a password on the command line interface can be insecure.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kings              |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

mysql> select * from kings.tanks
    -> ;
+----+--------------+-----------------------------------+-----------------+-------+-----------+---------+--------------+
| id | name         | skills                            | summoner_skills | price | camp      | pic_url | introduction |
+----+--------------+-----------------------------------+-----------------+-------+-----------+---------+--------------+
|  1 | 鲁班         | 二技能全图，被动扫射              | flush           |  5888 | NULL      | NULL    |              |
|  2 | 甄姬         | 技能会冰冻敌人                    | flush           |  2888 | NULL      | NULL    |              |
|  3 | 阿古朵       | 其在一个球上                      | ghost           | 13888 | 野兽      | NULL    |              |
|  4 | 鲁班大师     | 可以把队友吸过来                  | flush           | 13888 | 机器      | NULL    |              |
|  5 | 猪八戒       | 攻击敌人回血                      | flush           | 19999 | 野兽      | NULL    |              |
|  6 | 雅典娜       | 被打死之后可以全图跑动            | ghost           | 18888 | 神仙派    | NULL    |              |
|  7 | 钟馗         | 钩子特别恶心                      | flush           | 15555 | 灵魂      | NULL    |              |
|  8 | 钟馗         | 钩子特别恶心                      | flush           | 15555 | 灵魂      | NULL    |              |
|  9 | 梦奇         | 泡泡无限护盾                      | flush           |  7555 | 灵魂      | NULL    |              |
| 10 | 刘邦         | 大招全图支援                      | flush           |  8555 | 神仙派    | NULL    |              |
| 11 | 亚瑟         | 人在塔在                          | flush           |  9225 | 神仙派    | NULL    |              |
+----+--------------+-----------------------------------+-----------------+-------+-----------+---------+--------------+
11 rows in set (0.01 sec)
备份的参数
常用参数 描述说明
--flush-logs,-F 开始备份前刷新日志
--flush-privileges 备份mysql包含数据库时刷新授权表刷新用户授权信息
--lock-all-tables,-x 锁表，暂时停止增删改查，mylsam一致性，服务可用性（针对所有库所有表）
--lock-all-tables,-l 备份前锁表（针对要备份的库）
--single-transaction 适用与innodb引擎，要保证一致性，服务可用性，只要库里面有innodb引擎，一定要加
--master-data=2 表示二进制日志位置和文件名写入到备份文件并在dump文件中注释掉这一行
--master-data= 表示二进制日志位置和文件名写入到备份文件并在dump文件中不注释掉这一行

--master-data参数其他说明

  • 恢复时执行，默认是1
  • 需要reload privilege并且打开二进制文件
  • 这个选项会自动打开--lock-all-tables，关闭--lock-all-tables

多表备份

mysqldump -p123456 -S /my_mysql/3306/mysql.sock  数据库 表1 表2 > 备份地方
小结
  • mysqldump工具备份的是==SQL语句，故备份不需要停止服务
  • 使用备份文件恢复时，要保证==数据处于运行状态
  • 只能实现全库，指定库，表级别的备份==某一时刻的备份，本身不能增量备份
  • 适用于中小型数据库
mysqldump实现增量备份
  • 搞明白什么是增量备份
  • 有全量的，继续增删改数据，当我们再次需要备份时，只需要备份binlog日志文件即可（因为binlog日志，记录了增删改操作所有的sql语句）
  • 步骤
    • 准备数据
    • 开启二进制，然后全量备份（全库）
    • 继续对数据库进行增删改
    • 突然发生了硬件故障，数据库丢失了
    • 恢复全量备份导出数据（不完整，可能只有90%）
    • 备份二进制日志，根据其他信息（导入剩余的10%）
    • 完成
思路
  • 先做全量（mysqldump）
  • 刷新binlog
  • 对数据库进行修改
  • 备份二进制文件
  • 模拟故障
  • 数据恢复
  • 测试验证
#读二进制文件
[root@localhost 3306]# mysqlbinlog ./mysql-bin.00009
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
mysqlbinlog: File './mysql-bin.00009' not found (Errcode: 2 - No such file or directory)
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

本文作者：迷茫的28岁

本文链接：https://www.cnblogs.com/zheng520/p/16670905.html

版权声明：本作品采用知识共享署名-非商业性使用-禁止演绎 2.5 中国大陆许可协议进行许可。

posted @   迷茫的28岁  阅读(13)  评论(0)  编辑  收藏  举报
点击右上角即可分享
微信分享提示
AI IDE Trae
💬
评论
📌
收藏
💗
关注
👍
推荐
🚀
回顶
➖
收起
🔑
  1. 1 岁月神偷 金玟岐
岁月神偷 - 金玟岐
00:00 / 00:00
An audio error has occurred.

作词 : 金玟岐

作曲 : 金玟岐

编曲 : 薛琳可

能够握紧的就别放了

能够拥抱的就别拉扯

时间着急的冲刷着

剩下了什么

原谅走过的那些曲折

原谅走过的那些曲折

原来留下的都是真的

纵然似梦啊半醒着

笑着哭着都快活 谁让~

时间是让人猝不及防的东西

时间是让人猝不及防的东西

晴时有风阴有时雨

争不过朝夕又念着往昔

偷走了青丝却留住一个你

岁月是一场有去无回的旅行

岁月是一场有去无回的旅行

好的坏的都是风景

别怪我贪心只是不愿醒

因为你只为你愿和我一起

看云淡风轻

能够握紧的就别放了

能够握紧的就别放了

能够拥抱的就别拉扯

时间着急的冲刷着

剩下了什么

原谅走过的那些曲折

原谅走过的那些曲折

原来留下的都是真的

纵然似梦啊半醒着

笑着哭着都快活 谁让~

时间是让人猝不及防的东西

时间是让人猝不及防的东西

晴时有风阴有时雨

争不过朝夕又念着往昔

偷走了青丝却留住一个你

岁月是一场有去无回的旅行

岁月是一场有去无回的旅行

好的坏的都是风景

别怪我贪心只是不愿醒

因为你只为你愿和我一起

看云淡风轻

时间是让人猝不及防的东西

时间是让人猝不及防的东西

晴时有风阴有时雨

争不过朝夕又念着往昔

偷走了青丝却留住一个你

岁月是一场有去无回的旅行

岁月是一场有去无回的旅行

好的坏的都是风景

别怪我贪心只是不愿醒

因为你只为你愿和我一起

看云淡风轻