mysqldump实现逻辑完全备份 + binlog
优势:1 自动记录position位置。 (show master status\G;)
2 可用性,一致性 (锁表机制。)
语法:
mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sq
关于数据库名:-A, --all-databases 所有库
school 数据库名
school stu_info t1 是指school数据库的表stu_info、t1
-B, --databases bbs test mysql 多个数据库
关于其它参数说明:
--single-transaction #InnoDB 一致性 服务可用性
-E, --events #备份事件调度器代码
--opt #同时启动各种高级选项
-R, --routines #备份存储过程和存储函数
-F, --flush-logs #备份之前刷新日志,截断日志。备份之后新binlog。
--triggers #备份触发器
- -master-data=1|2 #该选项将会记录binlog的日志位置与文件名并追加到文件中,或添加注释
帮助:mysqldump --help
例子:
创建一个库
mysqldump -p'QianFeng@123' \
--all-databases --single-transaction \ //所有库一致性
--routines --triggers --master-data=2 \ //存储过程、函数,和主数据库位置
--flush-logs \ //刷新日志,形成新的日志
> /backup/`date +%F-%H`-mysql-all.sql //大于号,打到哪个备份文件里面去
停库,删库:
systemctl stop mysqld
rm -rf /var/lib/mysql/*
systemctl start mysqld
grep password /var/log/mysqd.log
mysqladmin -u root -p'23edeswdr23' password 'Qwer@123'
备份实战:
准备两套root密码: 密码1 Qwer@123 配置到当前数据库中
密码2 Qwer@1234 备用
准备库1:
testdb1.t1:
mysql> select * from testdb1.t1;
+------+
|id |
+------+
| 1 |
| 2 |
| 3 |
+------+
执行备份:mysqldump -p'Qwer@123' \
--all-databases --single-transaction \
--routines --triggers --master-data=2 \ //master-data=2 代表注释掉日志记录
--flush-logs \
> /backup/`date +%F-%H`-mysql-all.sql
观察备份细节:
vim /backup/2018-11-25-14-mysql-all.sql
LOCK TABLES `user` WRITE; 观察各种锁机制,用来保证数据一致性
22 CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.000004', MASTER_LOG_POS=154; 二进制日志截断位置。第22行
备份后数据变更行为:在testdb2.t2 中插入数据1;创testdb3库(观察二进制日志文件)重启数据库(再次观察二进制日志文件)
在testdb2.t2 中插入数据2;
删testdb3库
在testdb2.t2 中插入数据3;
恢复实战:
1 备份二进制日志文件:cp /var/lib/mysql/*bin* ~
2. 停止数据库:systemctl stop mysqld
3. 清理环境:rm -rf /var/lib/mysql/*
4.启动数据库:systemctl start mysqld
grep 'password' /var/log/mysqld.log找密码,再改一下密码。
5.mysql恢复数据: mysql -p'Qwer@1234' < /backup/2018-12-08-04-mysql-all.sql
mysql -p'Qwer@1234' -e 'flush privileges'
ll /var/lib/mysql/ (恢复的数据库一个)
6.二进制日志恢复: 观察二进制截取记录
vim /backup/2018-11-25-14-mysql-all.sql
CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.000002', MASTER_LOG_POS=154;
注意后续有多少日志,要跟多少日志名字。
mysqlbinlog localhost-bin.000002 localhost-bin.000003 --start-position=154 | mysql -p'Qwer@123'
7.观察数据是否完整
8.请思考误删除的问题
9.关于数据恢复时的多余日志。
方法一:
原因:每次还原数据库都会增加日志的体积。但这些都是还原操作。恢复数据库的日志占用了存储空间。
mysql> set sql_log_bin =0
再mysql> source /backup/*.sql
观察二进制日志并没有发生变化。
方法二:在备份文件中,加入关闭二进制日志。
记录的导出和导入:
SELECT... INTO OUTFILE 导出文本文件: (mysql> SELECT * FROM testdb1.t1 INTO OUTFILE '/backup/testdb1.t1.txt';) ( FIELDS TERMINATED BY '---' //定义字段分隔符)
mysql 命令导出文本文件:
mysql -uroot -p'Qwer@123' -e 'select * from testdb1.t1' > /backup/testdb1.t2.txt
mysql -u root -p'Qwer@123' --xml -e 'select * from testdb1.t1' > /backup/testdb1.t3.txt
mysql -u root -p'Qwer@123' --html -e 'select * from testdb1.t1' > /backup/index.html
LOAD DATA INFILE 导入文本文件:
(注意目录权限):vim /etc/my.cnf
secure-file-priv=/backup //mysql不信任该目录
chown mysql.mysql /backup //mysql用户没有权限
(DELETE FROM testdb1.t1; ) //删除表的内容,表的导出和导入只备份表记录,不会备分表结构。因此需要通过mysqldump备份表结构,恢复时先恢复表结构,再导入数据。
(LOAD DATA INFILE '/backup/testdb1.t1.txt'
INTO TABLE testdb1.t1;) //读取记录备份文件
( INTO TABLE testdb1.t1; ) //输入到表中。
(select * from testdb1.t1;)
结束