社区微信群开通啦,扫一扫抢先加入社区官方微信群
社区微信群
为了在误操作时恢复数据,对数据库备份是非常必要的,MySQL 自带了 mysqldump 命令来帮助我们实现简单的数据库备份。
数据库备份可以分为物理备份和逻辑备份,按照备份的数据内容页可以分为全量备份和增量备份,mysqldump 是用来进行逻辑备份的命令。
最简单的 mysqldump 命令使用方法如下:
mysqldump -uroot -p demo_db > demo_db.bkp.1117.sql
上面命令表示将 demo_db 数据库 导出到 demo_db.bkp.1117.sql 文件中,实现了一次全量的备份,-u 表示用户名,-p 表示输入密码。下面是 mysqldump 命令的一些常用的参数
看了上面的参数,下面我们拓展下上面的语句,执行一次对 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;
有时候并不需要对整个数据库进行备份,只需要备份其中的表即可,这时可以直接在数据库后面跟表名即可,下面是备份 demo_db 中的 user 表的命令:
mysqldump -uroot -p --master-data=2 --single-transaction --routines --triggers --events demo_db user > demo_db.bkp.user.1117.sql
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
通过 –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
grant file, select, reload, lock tables, replication client, show view, event, process on *.* to 'root'@'localhost';
这里我们授予了 file, select 等权限,保证 mysqldump 可以顺利执行
备份完数据后就可以在之前数据出问题的时候进行一次恢复了, mysqldump 后的恢复是单线程的,其性能完全取决于服务器的 IO 性能和 MySQL 实例的性能,当数据量
很大时可能需要执行很长时间
下面两条命令均可以将备份好的数据导入到对应的数据库中
在 mysql 下调用: mysql> source demo_db.bkp.1117.sql
在命令行执行: mysql -uroot -p demo_db < demo_db.bkp.1117.sql
通过联结表查出备份数据库和误操作数据库数据的差别,将相差的数据读取出来在写入到误操作的数据库中
将表结构导入到数据库中
mysql -uroot -p demo_db < user.sql;
在数据库中执行加载数据文件命令,将数据导入到指定表中
mysql> LOAD DATA INFILE "/tmp/backup_db/user.txt" into table user
一般来说全量备份虽然可靠,但是当数据量过大时会非常耗时,因此更常见的做法时在指定的时间点做全量备份,在两次全量备份之间做增量备份,此时需要用到 MySQL 的二进制文件,下面是一次误删数据后利用二进制文件进行恢复的操作过程。
mysqldump -uroot -p --master-data=2 --single-transaction --routines --triggers --events demo_db > demo_db.bkp.1117.sql
假设我们的数据库中有一张 message 记录消息的表,我们误删了其中的 100 条数据
DELETE FROM message limit 100;
发现数据库操作错误后,我们首先将数据恢复到上次全量备份时的数据
mysql -uroot -p demo_db < demo_db.bkp.1117.sql
获取记录的二进制日志和时间点,记录的二进制文件为 mysql-bin.000006,时间节点为 834501307
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=834501307;
我们通过 mysqlbinlog 命令查看日志,找到最近的一次删除的时间点。关于 mysqlbinlog 的使用请自行查阅 MySQL 的官方文档,这里不再赘述。
mysqlbinlog --base64-output=decode-rows -vv --start-position=667610711 --database=demo_db mysql-bin.000006 | grep -B3 DELETE | more
获取的内容如下:
这里假定上次删除的时间节点为 11112222, 那么我们接下来的任务就是分析日志,获取上次全量备份到本次误删除之间所有的数据库操作的 sql
备份时的节点为 667610711,误删除时的节点为 11112222,现在只需要将两个节点之前的所有写数据的操作导出即可,命令如下
mysqlbinlog --start-position=667610711 --stop-position=11112222, --database=demo_db mysql-bin.000006 > demo_dbbinlog_backup_1117.sql
获取到 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
参数说明:
在进行二进制日志恢复的时候可能会遇到一个问题,如果在该段时间点内有新建表的话,在全量恢复时并不会删除新建的表,在进行增量恢复时可能会报错,这里需要自行将新建的表进行删除,具体的做法因人而宜,这里不再赘述,以后工作学习中碰到的话再做研究。以上就是 mysqldump 命令备份与恢复的基本操作了。
如果觉得我的文章对您有用,请随意打赏。你的支持将鼓励我继续创作!