利用 mysqldump 实现数据库的备份与恢复
创建备份账号：
使用 mysqldump 进行数据备份，得有一个至少拥有 select，reload，lock tables，replication client，show view，process 权限的账号
- 创建一个专门用于备份账号:
create user 'backup'@'localhost' identified by '123456' - 给该账号进行授权：
grant select,reload,lock tables,replication client,show view,event,process on *.* to 'backup'@'localhost'
mysqldump 常用的一些导出命令
-
mysqldump 导出整个数据库实例
mysqldump -ubackup -p --single-transaction --master-data=2 --events --routines --triggers --all-databases>alldatabase.sql -
mysqldump 导出某个数据库
mysqldump -ubackup -p --single-transaction --master-data=2 --events --routines --triggers --databases dbName >dbName.sql -
mysqldump 导出某个数据库下的某个表
mysqldump -ubackup -p --single-transaction --master-data=2 --events --routines --triggers dbName tableName >dbName_tableName.sql -
mysqldump 使用 --tab 导出某个数据库
mysqldump -ubackup -p --single-transaction --master-data=2 --events --routines --triggers dbName --tab=/var/lib/mysql-files/ -
mysqldump 使用 --where 导出某个数据库下某个表中满足where条件的数据
mysqldump -ubackup -p --single-transaction --master-data=2 --events --routines --triggers dbName tableName --where="id=1">where.sql
mysqldump 全备恢复
- linux 命令行下：
mysql -uroot -p db_name < 全备.sql - mysql 命令行下
source 全备.sql - 对于使用 --tab 导出的数据库
1.先使用某一个表的sql建表 source tableName.sql
2.然后使用 load data infile 命令来导入
load data into table tableName infile '/${path}/tableName.txt'
mysqldump 利用bin log 实现数据的增量恢复
前提条件：具有指定时间点前的mysqldump的全备以及既有全备到指定时间点的 MySQL 二进制日志
-
测试数据如下
testdata.png
模拟过程：
- 对backupdata数据库进行一次全备，命令如下
mysqldump -ubackup -p --single-transaction --master-data=2 --events --routines --triggers --databases backupdata >backupdata.sql
backupdata.sql的文件内容如下图
image1.png
image2.png
-
模拟一些数据操作
INSERT INTO `tb_bakdata` VALUES (5,'zhang',23),(6,'ming',24),(7,'cao',23),(8,'jia',23);
INSERT INTO `tb_bakdata2` VALUES (66,'liu',24),(77,'jia',23),(88,'yi',24);DELETE FROM tb_bakdata WHERE id IN (1,2);
DELETE FROM tb_bakdata2 WHERE id IN (33,44,55);
我们想要恢复delete操作之前的数据：
- 先利用全备数据进行恢复
source /home/db_backup/backupdata.sql; - 利用 mysqlbinlog 命令查找删除命令之前的bin log的点，结果如下图，可以看出 ，删除命令之前的 bin log 的点为 8889
mysqlbinlog --base64-output=decode-rows -vv --start-position=8066 --database=backupdata mysql-bin.000003|grep -B3 DELETE | more
start-position 为全备sql中的
MASTER_LOG_POS的值,database表示想要恢复那个数据库 ，mysql-bin.000003 为全备sql中MASTER_LOG_FILE的值
image.png
- 获取两个bin log 点之间的信息，并保持到文件中
mysqlbinlog --start-position=8066 --stop-position=8889 --database=backupdata mysql-bin.000003 > backupdata_diff.sql
-然后导入 diff.sql 文件
mysql -uroot -p backupdata < backupdata_diff.sql
怎样备份 bin log
既然bin log 是如此的重要，那么怎样备份bin log 呢？
在 MySQL5.6 之后，可以实时备份 Binlog 文件，步骤如下：
- 创建一个用于备份的账号并赋予
replication slave权限
grant replication slave on *.* to 'repl'@'localhost' identified by '123456' - 然后执行备份命令
mysqlbinlog --raw --read-from-remote-server --stop-never --host localhost --port 3306 -urepl -p123456 mysql-bin.000001
