欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

mysqldump 数据库备份简记

程序员文章站 2024-03-21 09:25:22
...

为了在误操作时恢复数据,对数据库备份是非常必要的,MySQL 自带了 mysqldump 命令来帮助我们实现简单的数据库备份。

数据库备份可以分为物理备份和逻辑备份,按照备份的数据内容页可以分为全量备份和增量备份,mysqldump 是用来进行逻辑备份的命令。

一. mysqldump 全量备份

最简单的 mysqldump 命令使用方法如下:

mysqldump -uroot -p demo_db > demo_db.bkp.1117.sql

上面命令表示将 demo_db 数据库 导出到 demo_db.bkp.1117.sql 文件中,实现了一次全量的备份,-u 表示用户名,-p 表示输入密码。下面是 mysqldump 命令的一些常用的参数

1. mysqldump 常用参数

  • -u: 指定用户
  • -p: 指定密码
  • –single-transaction: 确保事务性操作,只对 innodb 有效,保证备份期间没有 DDL 操作
  • -l (–lock-table): 对于非 Innodb 引擎的备份进行锁表,只能进行读操作。与 single-transaction 互斥
  • -x,–lock-all-table: 给实例下的所有数据库的表进行加锁,保证一致性,该参数会导致在备份过程中数据库只读,不可写
  • -d: 只备份表结构,不备份数据
  • –master-data: 有两个值: 1 和 2。1 时只记录change master 语句,为 2 时change master 会注释掉,建议设置为 2
  • –all-database: 备份 MySQL 实例下的所有数据库
  • –database: 指定对应的数据库进行备份
  • -R, -routines: 备份所有的存储过程
  • –tiggers: 备份触发器
  • -E, –events: 备份数据库中的调度时间
  • –hex-blob: 将对 blog/binary 等格式的数据转为 16 进制形式进行保存
  • -tab=path: 在指定路径下分别生成结构文件和数据文件,会对每个表分别生成一个记录表结构的 sql 文件和记录数据的 txt 文件
  • -w,–where= 过滤条件,对于单表进行过滤条件的备份

2. 数据库的全量备份

看了上面的参数,下面我们拓展下上面的语句,执行一次对 demo_db 的全量备份,命令如下:

mysqldump -uroot -p --master-data=2 --single-transaction --routines --triggers --events demo_db > demo_db.bkp.1117.sql

上面语句在备份了数据库结构和数据的同时,还会备份存储过程,触发器和调度事件,并且在备份的时候不允许写操作,–master-data=2 参数会注释 change master 语句, 语句内容如下,记录了记录备份操作的二进制日志文件和时间点,对于之后使用 mysqlbinlog 进行增量备份非常有用。

 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=834501307;

3. 备份数据库中的表

有时候并不需要对整个数据库进行备份,只需要备份其中的表即可,这时可以直接在数据库后面跟表名即可,下面是备份 demo_db 中的 user 表的命令:

mysqldump -uroot -p --master-data=2 --single-transaction --routines --triggers --events demo_db user > demo_db.bkp.user.1117.sql

4. 使用 where 条件过滤进行单表备份

where 语句可以用来指定过滤条件,从而限定要备份的数据,不过只适用于单表备份, 下面是使用 where 进行备份的实例,在 demo_db 中有一张 用户订单关系表,我们要备份 user_id 为 1 到 999 的关系数据

mysqldump -uroot -p --master-data=2 --single-transaction --where "user_id>0 and user_id < 1000" demo_db user_order_ship > demo_db.bkp.user_order_ship.sql  

5. 使用 –tab 指定备份的目录

通过 –tab 指定备份的目录,会在目录下会分别生成结构文件和数据文件, 下面语句,这里我们指定的目录为 /tmp/backup_db 目录,mysqldump 命令会将 demo_db 中的每张表都导出为一个记录表结构的 sql 文件和记录数据的 txt 文件。

mysqldump -uroot -p --master-data=2 --single-transaction -R -E --triggers --tab='/tmp/backup_db' demo_db
注意
  • 因为需要数据库对外写数据,因此需要授予 file 权限,授予语句如下:
grant file, select, reload, lock tables, replication client, show view, event, process on *.* to 'root'@'localhost';

这里我们授予了 file, select 等权限,保证 mysqldump 可以顺利执行

  • 在MySQL 5.7 中, secure_file_priv 变量确定了 MySQL 可以往外写文件的路径,默认是 NULL ,表示不允许对外写文件,设置为 “” 后表示可以对外的任意路径写文件,也可以指定路径进行授予写权限,一般在 my.cnf 文件中指定为 “” 即可。在使用 –tab 时 尤其要注意,指定的目录是被 MySQL 拥有写权限的

6. 恢复

备份完数据后就可以在之前数据出问题的时候进行一次恢复了, mysqldump 后的恢复是单线程的,其性能完全取决于服务器的 IO 性能和 MySQL 实例的性能,当数据量
很大时可能需要执行很长时间

【1】两种基本的恢复语句

下面两条命令均可以将备份好的数据导入到对应的数据库中

 在 mysql 下调用: mysql> source demo_db.bkp.1117.sql
 在命令行执行: mysql -uroot -p demo_db < demo_db.bkp.1117.sql
【2】对误操作的一些恢复方法

通过联结表查出备份数据库和误操作数据库数据的差别,将相差的数据读取出来在写入到误操作的数据库中

【3】 –tab 备份后的单表恢复

将表结构导入到数据库中

mysql -uroot -p demo_db < user.sql;

在数据库中执行加载数据文件命令,将数据导入到指定表中

 mysql> LOAD DATA INFILE "/tmp/backup_db/user.txt" into table user

二. binlog 增量备份

一般来说全量备份虽然可靠,但是当数据量过大时会非常耗时,因此更常见的做法时在指定的时间点做全量备份,在两次全量备份之间做增量备份,此时需要用到 MySQL 的二进制文件,下面是一次误删数据后利用二进制文件进行恢复的操作过程。

1. 首先是一次全备操作
mysqldump -uroot -p --master-data=2 --single-transaction --routines --triggers --events demo_db > demo_db.bkp.1117.sql
2. 全备后的数据删除

假设我们的数据库中有一张 message 记录消息的表,我们误删了其中的 100 条数据

DELETE FROM message limit 100;
3. 发现错误后,首先进行一次全量恢复

发现数据库操作错误后,我们首先将数据恢复到上次全量备份时的数据

mysql -uroot -p demo_db < demo_db.bkp.1117.sql
4. 查看备份文件中的 change master 语句,

获取记录的二进制日志和时间点,记录的二进制文件为 mysql-bin.000006,时间节点为 834501307

 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=834501307;
5. mysqlbinlog 分析二进制日志文件

我们通过 mysqlbinlog 命令查看日志,找到最近的一次删除的时间点。关于 mysqlbinlog 的使用请自行查阅 MySQL 的官方文档,这里不再赘述。

mysqlbinlog --base64-output=decode-rows -vv --start-position=667610711 --database=demo_db mysql-bin.000006 | grep -B3 DELETE | more

获取的内容如下:

mysqldump 数据库备份简记

这里假定上次删除的时间节点为 11112222, 那么我们接下来的任务就是分析日志,获取上次全量备份到本次误删除之间所有的数据库操作的 sql

6. 导出两次节点之间的操作 sql

备份时的节点为 667610711,误删除时的节点为 11112222,现在只需要将两个节点之前的所有写数据的操作导出即可,命令如下

mysqlbinlog --start-position=667610711 --stop-position=11112222, --database=demo_db mysql-bin.000006 > demo_dbbinlog_backup_1117.sql
7. 数据恢复

获取到 sql 文件后就可以进行恢复操作了,直接执行 sql 文件即可

mysql -uroot -p gravity_1115 < mysql_binlog_backup_1115.sql

上面就是整个的增量恢复的过程,要做到这一点就需要要对二进制文件进行实时的备份,其实就是对二进制文件的一个复制过程,命令如下:

# 授权语句
grant replication slave on *.* to 'root'@'localhost' identify by 111111;
# 二进制实时备份命令
mysqlbinlog --raw --read-from-remote-server --stop-never --host localhost --port 3306 -uroot -p mysql-bin.000006

参数说明:

  • –raw 输出的是一个 raw 格式的二进制日志
  • –read-from-remote-server 从 MySQL 服务器读取日志
  • –stop-never 表示不会停止,一直在后台进行实时备份

在进行二进制日志恢复的时候可能会遇到一个问题,如果在该段时间点内有新建表的话,在全量恢复时并不会删除新建的表,在进行增量恢复时可能会报错,这里需要自行将新建的表进行删除,具体的做法因人而宜,这里不再赘述,以后工作学习中碰到的话再做研究。以上就是 mysqldump 命令备份与恢复的基本操作了。