使用lvm 备份和恢复mysql数据库

案列中查询用到的命令：

show engines;   ---现在已提供什么存储引擎

show variables like '%storage_engine%';  -----当前默认的存储引擎

设置InnoDB为默认引擎：在配置文件my.cnf中的 [mysqld] 下面加入default-storage-engine=INNODB 一句，也可以再加上  innodb_file_per_table=1  #让innodb的每个表文件单独存储

show master status ;  --查看节点状态

show binlog  events  in 'mysql-bin.000001';   ---查询执行mysql二进制事件查询

set  sql_log_bin=0;     --关闭mysql的二进制同步 0表示关闭 1表示开启

select @@sql_log_bin;  ---查询二进制查询开启状态

mysql> \. /root/zzxtbl.sql   --- 当前mysql会话中导入zzxtbl.sql脚本

select * into outfile'/opt/zzxtbl.txt'  form zzxtbl;   ----查询zzxtbl表并导出到/opt/zzxtbl.txt中

create table zzxtbl  like zztbls;

desc zzxtbl ,zzxtbl;    -------desc 显示表结构

drop table  zzxtbl;  ------ 删除表

truncate table zzxtbl;  ------清空表数据

mysqlbinlog /data/mysql-bin.000001  ----查看mysql的二进制文件内容

mysqlbinlog --start-positsion=100/mysql-bin.000001 > /root/zzxtbl.sql  ---查看节点数是100的，并导出sql脚本

mysql -e 'show master status\G' > /opt/master-`data+%F`.info  ----查看保存备份的二进制文件

show variables like '%dir%';   ----查mysql的目录存放

一、 mysql 使用select  into outfile 将数据库中的表文件到如到文本（.txt）中，然后进行数据库内容恢复

语法： select * into outfile '/tmp/zzx.txt' form zzx;

显示的导出数据的结构

操作步骤如下：

（1）  将a表的表结构复制成b表后，查看两表中的数据结构dsca\b;

   

（2）  将a表删除后，用loaddata infile '/tmp/zzx.txt' into table b ,中恢复 a表中的数据。

二、mysql使用二进制文件恢复数据库

这里提示可以根据你的mysql 服务器上的binlog日志查方式，首先查看show master status；主节点的信息。然后在查看该节点中show  binlog  events in  'mysql-bin.000001';

binlog日志根据开始的节点数运行成sql脚本

mysqlbinlog--start-position="319" /var/lib/mysql/mysql-bin.000005>/root/zzx.sql

 

1.清空表数据 (truncate   table  zzxs;)

2.关闭二进制文件同步（setsql_log_bin=0;），恢复成功后开启 mysql的二进制开关（set_log_bin）.

3.mysql 服务器上线

 恢复成功了

三、使用lvm在线备份mysql数据库

注：提前将mysql数据目录放到我创建好的逻辑卷上。在此该卷组上要足够的空间用来备份使用，在此mysql数据库和binlog二进制文件都要在该卷组上。

步骤：

1.在MySQL的当前的会话中，要锁定当前的所有的表；

mysql> flush    tables with   read  lock;

mysql> flush  logs;

这时事务提交

2.通过另一个终端，保存二进制日志文件的相应的位置信息。

3.创建快照卷

4.释放锁

mysq>unlock tables;

释放当前的表锁。该事物就自动提交了提交了

5.挂载快照卷，备份

lvcreate -L 50M -s -p r -n mydata-snap/dev/zzxtbl/mydata

mount /dev/zzxtbl/mydata-snap /mnt -o ro

拷贝当前的lvm卷备份的数据到/opt

6.删除快照卷

卸载当前的逻辑卷

umount  /mnt

删除卷组lvremove --force /dev/zzxtbl/mydata-snap

四、模拟mysql误删和增量恢复

1.删除当前卷组上的mysql数据库

停止mysqld的服务

2.拷贝之前备份的mysql的数据库

3.还原增量的数据

mysqlbinlog --start-datetime='2015-08-26 23:45:57' mysql-bin.000008 > /opt/zengliang-`date +%F-%H-%M-%S`.sql

导入当前mysql数据库中。数据就恢复了。开启二进制同步开关 set sql_log_bin=1;

转载于:https://blog.51cto.com/zzxtbl/1688943