mysql dump备份恢复，binlog基于时间点的恢复

简单的mysql dump备份和恢复

备份

# mysqldump -uroot -poracle -B test --table tablename > xxxx.sql

                                                   哪个库          哪张表            备份文件名

恢复

# mysql -uroot -p test < xxxx.sql

或者进入mysql

source xxxx.sql

这里有一个mysqldump的自动备份脚本：  from：http://www.linuxidc.com/Linux/2016-01/127976.htm

自动备份，自动清除过期的备份文件

#!/bin/sh

cd /mysql/backup/bakmysql

echo "You are in bakmysql directory"

mv /mysql/backup/bakmysql/* /mysql/backup/bakmysqlold

echo "Old databases are moved to bakmysqlold folder"

Now=$(date +"%d-%m-%Y")

File=bak-$Now.sql

mysqldump -uroot -p'oracle' test > $File

echo "Your database backup successfully completed"

SevenDays=$(date -d -7day +"%d-%m-%Y")

if [ -f /mysql/backup/bakmysqlold/bak-$SevenDays.sql ]

then

rm -rf /mysql/backup/bakmysqlold/bak-$SevenDays.sql

echo "You have delete the backup files which are 7 days ago"

else

echo "7-day backup files not exist"

fi

binlog基于时间点的恢复

模拟环境

新建数据库：

create database test1;

使用test1库：

use test1;

新建一张表：

create table t2 (

       id int unsigned not null auto_increment,

       name char(20) not null,

       sex enum('f','m') not null default 'm',

       address varchar(30) not null,

       primary key(id)

       );

插入一些数据：

insert into t2 (name,sex,address)values('daiiy','m','guangzhou');

insert into t2 (name,sex,address)values('tom','f','shanghai');

insert into t2 (name,sex,address)values('liany','m','beijing');

insert into t2 (name,sex,address)values('lilu','m','zhuhai');

查看一下：

mysql> select * from t2;

+----+-------+-----+-----------+

| id | name  | sex | address   |

+----+-------+-----+-----------+

|  1 | daiiy | m   | guangzhou |

|  2 | tom   | f   | shanghai  |

|  3 | liany | m   | beijing   |

|  4 | lilu  | m   | zhuhai    |

+----+-------+-----+-----------+

4 rows in set (0.00 sec)

模拟数据丢失：

mysql> delete from t2;

Query OK, 4 rows affected (0.04 sec)

mysql> select * from t2;

Empty set (0.00 sec)

查看一下mysql的binlog日志：

mysql> show master status;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000002 |     1852 |              |                  |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

分析二进制日志内容：

[root@linux1 ~]# mysqlbinlog -v /mysql/data/mysql-bin.000002 --base64-output=DECODE-ROWS

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#161214 16:55:56 server id 1203306  end_log_pos 120 CRC32 0x29a080cb    Start: binlog v 4, server v 5.6.28-log created 161214 16:55:56 at startup

ROLLBACK/*!*/;

BINLOG '

nHlRWA9qXBIAdAAAAHgAAAAAAAQANS42LjI4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAACceVFYEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAcuA

oCk=

'/*!*/;

# at 120

#161214 17:08:49 server id 1203306  end_log_pos 217 CRC32 0xd7370136    Query   thread_id=1 exec_time=0      error_code=0

SET TIMESTAMP=1481735329/*!*/;

SET @@session.pseudo_thread_id=1/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;

SET @@session.sql_mode=1075838976/*!*/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;

/*!\C utf8 *//*!*/;

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

create database test1

/*!*/;

# at 217

#161214 17:10:17 server id 1203306  end_log_pos 505 CRC32 0xb4045928    Query   thread_id=1 exec_time=0      error_code=0

use `test1`/*!*/;

SET TIMESTAMP=1481735417/*!*/;

create table t2 (

       id int unsigned not null auto_increment,

       name char(20) not null,

       sex enum('f','m') not null default 'm',

       address varchar(30) not null,

       primary key(id)

       )

/*!*/;

# at 505

#161214 17:10:43 server id 1203306  end_log_pos 578 CRC32 0xde600fe2    Query   thread_id=1 exec_time=0      error_code=0

SET TIMESTAMP=1481735443/*!*/;

BEGIN

/*!*/;

# at 578

#161214 17:10:43 server id 1203306  end_log_pos 633 CRC32 0x0a098f3f    Table_map: `test1`.`t2` mapped to number 70

# at 633

#161214 17:10:43 server id 1203306  end_log_pos 690 CRC32 0xa8dfc032    Write_rows: table id 70 flags: STMT_END_F

BINLOG '

E31RWBNqXBIANwAAAHkCAAAAAEYAAAAAAAEABXRlc3QxAAJ0MgAEA/7+Dwb+PPcBWgAAP48JCg==

E31RWB5qXBIAOQAAALICAAAAAEYAAAAAAAEAAgAE//ABAAAABWRhaWl5AglndWFuZ3pob3UywN+o

'/*!*/;

### INSERT INTO `test1`.`t2`

### SET

###   @1=1

###   @2='daiiy'

###   @3=2

###   @4='guangzhou'

# at 690

#161214 17:10:43 server id 1203306  end_log_pos 721 CRC32 0x6d0d1dba    Xid = 15

COMMIT/*!*/;

# at 721

#161214 17:10:51 server id 1203306  end_log_pos 794 CRC32 0x04f5091a    Query   thread_id=1 exec_time=0      error_code=0

SET TIMESTAMP=1481735451/*!*/;

BEGIN

/*!*/;

# at 794

#161214 17:10:51 server id 1203306  end_log_pos 849 CRC32 0x6ace01f0    Table_map: `test1`.`t2` mapped to number 70

# at 849

#161214 17:10:51 server id 1203306  end_log_pos 903 CRC32 0xa3d06906    Write_rows: table id 70 flags: STMT_END_F

BINLOG '

G31RWBNqXBIANwAAAFEDAAAAAEYAAAAAAAEABXRlc3QxAAJ0MgAEA/7+Dwb+PPcBWgAA8AHOag==

G31RWB5qXBIANgAAAIcDAAAAAEYAAAAAAAEAAgAE//ACAAAAA3RvbQEIc2hhbmdoYWkGadCj

'/*!*/;

### INSERT INTO `test1`.`t2`

### SET

###   @1=2

###   @2='tom'

###   @3=1

###   @4='shanghai'

# at 903

#161214 17:10:51 server id 1203306  end_log_pos 934 CRC32 0x582028b7    Xid = 16

COMMIT/*!*/;

# at 934

#161214 17:11:02 server id 1203306  end_log_pos 1007 CRC32 0x1eff5bfb   Query   thread_id=1 exec_time=0      error_code=0

SET TIMESTAMP=1481735462/*!*/;

BEGIN

/*!*/;

# at 1007

#161214 17:11:02 server id 1203306  end_log_pos 1062 CRC32 0x7b23f10f   Table_map: `test1`.`t2` mapped to number 70

# at 1062

#161214 17:11:02 server id 1203306  end_log_pos 1117 CRC32 0x1aa1de8e   Write_rows: table id 70 flags: STMT_END_F

BINLOG '

Jn1RWBNqXBIANwAAACYEAAAAAEYAAAAAAAEABXRlc3QxAAJ0MgAEA/7+Dwb+PPcBWgAAD/Ejew==

Jn1RWB5qXBIANwAAAF0EAAAAAEYAAAAAAAEAAgAE//ADAAAABWxpYW55AgdiZWlqaW5njt6hGg==

'/*!*/;

### INSERT INTO `test1`.`t2`

### SET

###   @1=3

###   @2='liany'

###   @3=2

###   @4='beijing'

# at 1117

#161214 17:11:02 server id 1203306  end_log_pos 1148 CRC32 0xb0648be4   Xid = 17

COMMIT/*!*/;

# at 1148

#161214 17:11:13 server id 1203306  end_log_pos 1221 CRC32 0x58830b02   Query   thread_id=1 exec_time=0      error_code=0

SET TIMESTAMP=1481735473/*!*/;

BEGIN

/*!*/;

# at 1221

#161214 17:11:13 server id 1203306  end_log_pos 1276 CRC32 0xa1d79b68   Table_map: `test1`.`t2` mapped to number 70

# at 1276

#161214 17:11:13 server id 1203306  end_log_pos 1329 CRC32 0x18558022   Write_rows: table id 70 flags: STMT_END_F

BINLOG '

MX1RWBNqXBIANwAAAPwEAAAAAEYAAAAAAAEABXRlc3QxAAJ0MgAEA/7+Dwb+PPcBWgAAaJvXoQ==

MX1RWB5qXBIANQAAADEFAAAAAEYAAAAAAAEAAgAE//AEAAAABGxpbHUCBnpodWhhaSKAVRg=

'/*!*/;

### INSERT INTO `test1`.`t2`

### SET

###   @1=4

###   @2='lilu'

###   @3=2

###   @4='zhuhai'

# at 1329

#161214 17:11:13 server id 1203306  end_log_pos 1360 CRC32 0x884d51a4   Xid = 18

COMMIT/*!*/;

# at 1360

#161214 17:17:53 server id 1203306  end_log_pos 1383 CRC32 0x58aa734e   Stop

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

黄色 是SQL语句 包括建库建表 还有DML的语句 

绿色 表示事务的开始和结束  还有时间点 

可以看到 insert语句是从 161214 17:10:43 开始 到161214 17:17:53结束

那我们就从这里开始恢复被删除的数据

先进入binlog的存放目录 cd /mysql/data

[root@linux1 data]# mysqlbinlog  mysql-bin.000002 --start-datetime='2016-12-14 17:10:43' --stop-datetime='2016-12-14 17:17:53' -d test1 | mysql -uroot -p

回车

再进入mysql中查看：

mysql> select * from t2;

+----+-------+-----+-----------+

| id | name  | sex | address   |

+----+-------+-----+-----------+

|  1 | daiiy | m   | guangzhou |

|  2 | tom   | f   | shanghai  |

|  3 | liany | m   | beijing   |

|  4 | lilu  | m   | zhuhai    |

+----+-------+-----+-----------+

4 rows in set (0.00 sec)

数据恢复成功~~~~~~~~~~~^_^

另外也可以进行基于位置的恢复

上文binlog中 蓝色的数字 表示事务的position

只要确定--start-position和--stop-position就可以进行恢复

方法和时间点相同~