mysql备份份与恢复
日志的种类:查询日志、慢查询日志、错误日志、 二进制日志、 中继日志、 事务日志
服务器变量:
SHOW BINARY|MASTER LOGS;
SHOW MASTER STATUS;
SHOW BINLOG EVENTS IN '' FROM pos;
mysqlbinlog:
--start-position=
--stop-position
--start-datetime
--stop-datetime
备份:
完全备份、增量备份、差异备份
物理备份、逻辑备份
完全备份、部分备份
热备、温备、冷备
备份工具:
mysqldump
cp,tar
mysqlhotcopy
lvm2
xtrabackup
基于mysqldump的备份与恢复:
mysqldump命令: 客户端工具,通过mysql协议连接至mysqld服务器
mysqldump [options] [db_name [table1_name table2_name]]
备份的数据集
-A, --all-databases
-B, db_name, ...
--databases db_name,...
mysqldump [options] --databases db_name ...
mysqldump [options] --all-databases
mysqldump --all-databases --lock-all-tables > all.sql
MyISAM: 支持温备、锁定备份库,而后启动备份操作
锁定方法:
--lock-all-tables
--lock-tables: 对于每个单独的数据库,在启动备份之前锁定期所有表
对InnoDB表一样生效,实现温备
InnoDB:支持热备
--single-transcation
其它选项:
-E, --events 备份指定数据库相关的所有event scheduler
-R, 被备份存储函数和存储过程
--traiggers: 备份表相关的触发器
--master-data[=#]
#=1 --> 记录CHANGE MASTER TO 语句,此语句不备注释
#=2 --> 上述记录为注释格式
--flush-logs
锁定标完成后,执行flush logs命令,滚动二进制日志
实例:
node1全量备份及重放二进制日志实现时间点还原
node1 (datadir = /data/mysql,所以二进制日志也在此目录下)
mysql> SHOW DATABASES
+---------------------+
| Database |
+---------------------+
| information_schema |
| HAHA |
| heiheihei |
| hellodb |
| #mysql50#lost+found |
| mysql |
| performance_schema |
| test |
| wolaile |
| xixi |
+---------------------+
在命令行执行mysqldump复制数据
~]# mysqldump --all-databases --lock-all-tables > all.sql
mysql> show master status
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql_bin.000002 | 812 | | |
+------------------+----------+--------------+------------------+
在mysql客户端执行
mysql> DROP DATABASE hellodb;
mysql> SHOW DATABASES;
+---------------------+
| Database |
+---------------------+
| information_schema |
| HAHA |
| heiheihei |
| #mysql50#lost+found |
| mysql |
| performance_schema |
| test |
| wolaile |
| xixi |
+---------------------+
~]# mysqlbinlog --start-position=812 /data/mysql/mysql_bin.000002 > drop.sql
将all.sql和drop.sql传送至node2主机
node2
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| shopxx |
| test |
+--------------------+
~]# mysql < all.sql
mysql> show databases;
+---------------------+
| Database |
+---------------------+
| information_schema |
| HAHA |
| heiheihei |
| hellodb |
| #mysql50#lost+found |
| mysql |
| performance_schema |
| shopxx |
| test |
| wolaile |
| xixi |
+---------------------+
hellodb数据库依然存在
~]# mysql < drop.sql
mysql > SHOW DATABASES;
+---------------------+
| Database |
+---------------------+
| information_schema |
| HAHA |
| heiheihei |
| #mysql50#lost+found |
| mysql |
| performance_schema |
| shopxx |
| test |
| wolaile |
| xixi |
+---------------------+
这时 hellodb 数据库已然消失.
基于lvm2的备份与恢复
提前将mysql的datadir指向挂载lvm的目录下
(1) 请求锁定所有表
mysql> FLUSH TABLES WITH READ LOCK; (加读锁,可以读)
(2) 记录二进制日志以及事件位置
mysql> FLUSH LOGS; 先滚动二进制日志
mysql> SHOW MASTER STATUS;
(3) 创建快照
(4) 释放锁
UNLOCK TABLES;
(5) 挂载快照卷,执行数据备份
(6) 备份完成后,删除快照卷
(7) 制订好策略,通过原卷备份二进制日志,而非快照卷;