创建备份账号:
使用 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
-
模拟一些数据操作
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
image.pngstart-position 为全备sql中的
MASTER_LOG_POS
的值,database表示想要恢复那个数据库 ,mysql-bin.000003 为全备sql中MASTER_LOG_FILE
的值
- 获取两个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
网友评论