mysql备份恢复实验

浏览数：19 / 时间：2015年06月12日

内容大纲：

一 mysqldump备份

二 sql语句备份

三 mysqlbinlog备份恢复

一 mysqldump数据文件备份恢复

mysql> use cw_d;

Database changed

mysql> select * from cw;

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

| id | name | number |

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

| 1 | cw1 | 1 |

| 2 | cw2 | 2 |

| 3 | cw3 | 3 |

| 4 | cw4 | 4 |

| 5 | cw5 | 5 |

| 6 | cw6 | 6 |

| 7 | cw7 | 7 |

| 8 | cw8 | 8 |

| 9 | cw9 | 9 |

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

9 rows in set (0.00 sec)

mysql>

备份数据库cw_d

[root@69 ~]# mysqldump -u root -p cw_d>cw.sql

Enter password:

[root@69 ~]#

删除数据库cw_d，然后查询记录

mysql> drop table cw;

Query OK, 0 rows affected (0.02 sec)

mysql> select * from cw;

ERROR 1146 (42S02): Table ‘cw_d.cw‘ doesn‘t exist

执行恢复：

[root@69 ~]# mysql -u root -p cw_d <cw.sql

Enter password:

[root@69 ~]#

验证语句，如果能查询到结果，说明已经成功了。

mysql> select * from cw;

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

| id | name | number |

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

| 1 | cw1 | 1 |

| 2 | cw2 | 2 |

| 3 | cw3 | 3 |

| 4 | cw4 | 4 |

| 5 | cw5 | 5 |

| 6 | cw6 | 6 |

| 7 | cw7 | 7 |

| 8 | cw8 | 8 |

| 9 | cw9 | 9 |

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

9 rows in set (0.00 sec)

mysql>

二 SQL语句备份恢复

mysql> select * from cw;

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

| id | name | number |

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

| 1 | cw1 | 1 |

| 2 | cw2 | 2 |

| 3 | cw3 | 3 |

| 4 | cw4 | 4 |

| 5 | cw5 | 5 |

| 6 | cw6 | 6 |

| 7 | cw7 | 7 |

| 8 | cw8 | 8 |

| 9 | cw9 | 9 |

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

9 rows in set (0.00 sec)

mysql> select * into outfile ‘/root/cw_sql.sql‘ from cw;

ERROR 1 (HY000): Can‘t create/write to file ‘/root/cw_sql.sql‘ (Errcode: 13)

mysql> select * into outfile ‘/root/cw.sql‘ from cw;

ERROR 1 (HY000): Can‘t create/write to file ‘/root/cw.sql‘ (Errcode: 13) //权限不够，需要mysql能够写入的目录。

mysql> select * from cw;

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

| id | name | number |

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

| 1 | cw1 | 1 |

| 2 | cw2 | 2 |

| 3 | cw3 | 3 |

| 4 | cw4 | 4 |

| 5 | cw5 | 5 |

| 6 | cw6 | 6 |

| 7 | cw7 | 7 |

| 8 | cw8 | 8 |

| 9 | cw9 | 9 |

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

9 rows in set (0.00 sec)

mysql> select * into outfile ‘/tmp/cw.sql‘ from cw; //备份语句

Query OK, 9 rows affected (0.00 sec)

mysql> delete from cw;

Query OK, 9 rows affected (0.00 sec)

mysql> select * from cw;

Empty set (0.00 sec)

mysql> load data infile ‘/tmp/cw.sql‘ into table cw; //恢复语句

Query OK, 9 rows affected (0.01 sec)

Records: 9 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from cw; //验证成功，能够查询到语句，说明恢复成功。

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

| id | name | number |

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

| 1 | cw1 | 1 |

| 2 | cw2 | 2 |

| 3 | cw3 | 3 |

| 4 | cw4 | 4 |

| 5 | cw5 | 5 |

| 6 | cw6 | 6 |

| 7 | cw7 | 7 |

| 8 | cw8 | 8 |

| 9 | cw9 | 9 |

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

9 rows in set (0.00 sec)

mysql>

三 mysqlbinlog备份恢复

1.清理下二进制文件

mysql> flush logs;

Query OK, 0 rows affected (0.01 sec)

mysql> show master logs;

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

| Log_name | File_size |

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

| mysql-bin.000001 | 21817 |

| mysql-bin.000002 | 752122 |

| mysql-bin.000003 | 45418387 |

| mysql-bin.000004 | 126 |

| mysql-bin.000005 | 6615020 |

| mysql-bin.000006 | 6708657 |

| mysql-bin.000007 | 29665204 |

| mysql-bin.000008 | 753 |

| mysql-bin.000009 | 307620 |

| mysql-bin.000010 | 107 |

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

10 rows in set (0.00 sec)

mysql>

现在已经是已mysql-bin.000010 开始了。

mysql> use cw_d;

Database changed

mysql> create table cw( id int auto_increment not null primary key, name varchar(20), number int(12));

Query OK, 0 rows affected (0.04 sec)

mysql>

mysql> select * from cw;

Empty set (0.00 sec)

mysql> insert into cw(name,number) values(‘cw1‘,0001);

Query OK, 1 row affected (0.00 sec)

mysql> insert into cw(name,number) values(‘cw2‘,0002);

Query OK, 1 row affected (0.01 sec)

mysql> insert into cw(name,number) values(‘cw3‘,0003);

Query OK, 1 row affected (0.01 sec)

mysql> select * from cw;

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

| id | name | number |

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

| 1 | cw1 | 1 |

| 2 | cw2 | 2 |

| 3 | cw3 | 3 |

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

3 rows in set (0.00 sec)

mysql> show master logs;

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

| Log_name | File_size |

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

| mysql-bin.000001 | 21817 |

| mysql-bin.000002 | 752122 |

| mysql-bin.000003 | 45418387 |

| mysql-bin.000004 | 126 |

| mysql-bin.000005 | 6615020 |

| mysql-bin.000006 | 6708657 |

| mysql-bin.000007 | 29665204 |

| mysql-bin.000008 | 753 |

| mysql-bin.000009 | 307620 |

| mysql-bin.000010 | 7676 |

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

10 rows in set (0.00 sec)

mysql> flush logs;

Query OK, 0 rows affected (0.01 sec)

mysql> show master logs;

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

| Log_name | File_size |

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

| mysql-bin.000001 | 21817 |

| mysql-bin.000002 | 752122 |

| mysql-bin.000003 | 45418387 |

| mysql-bin.000004 | 126 |

| mysql-bin.000005 | 6615020 |

| mysql-bin.000006 | 6708657 |

| mysql-bin.000007 | 29665204 |

| mysql-bin.000008 | 753 |

| mysql-bin.000009 | 307620 |

| mysql-bin.000010 | 7719 |

| mysql-bin.000011 | 710 |

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

11 rows in set (0.00 sec)

mysql>

mysql> insert into cw(name,number) values(‘cw4‘,0004);

Query OK, 1 row affected (0.01 sec)

mysql> insert into cw(name,number) values(‘cw5‘,0005);

Query OK, 1 row affected (0.00 sec)

mysql> insert into cw(name,number) values(‘cw6‘,0006);

Query OK, 1 row affected (0.01 sec)

mysql> insert into cw(name,number) values(‘cw7‘,0007);

Query OK, 1 row affected (0.01 sec)

mysql> delete from cw where number=0005;

Query OK, 1 row affected (0.00 sec)

mysql> select * from cw;

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

| id | name | number |

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

| 1 | cw1 | 1 |

| 2 | cw2 | 2 |

| 3 | cw3 | 3 |

| 4 | cw4 | 4 |

| 6 | cw6 | 6 |

| 7 | cw7 | 7 |

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

6 rows in set (0.00 sec)

mysql> flush logs;

Query OK, 0 rows affected (0.01 sec)

mysql> show master logs;

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

| Log_name | File_size |

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

| mysql-bin.000001 | 21817 |

| mysql-bin.000002 | 752122 |

| mysql-bin.000003 | 45418387 |

| mysql-bin.000004 | 126 |

| mysql-bin.000005 | 6615020 |

| mysql-bin.000006 | 6708657 |

| mysql-bin.000007 | 29665204 |

| mysql-bin.000008 | 753 |

| mysql-bin.000009 | 307620 |

| mysql-bin.000010 | 7719 |

| mysql-bin.000011 | 4560 |

| mysql-bin.000012 | 107 |

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

12 rows in set (0.00 sec)

mysql>

mysql> insert into cw(name,number) values(‘cw8‘,0008);

Query OK, 1 row affected (0.01 sec)

mysql> insert into cw(name,number) values(‘cw9‘,0009);

Query OK, 1 row affected (0.01 sec)

mysql> drop table cw;

Query OK, 0 rows affected (0.04 sec)

mysql> select * from cw;

ERROR 1146 (42S02): Table ‘cw_d.cw‘ doesn‘t exist

mysql>

二恢复过程

由于有3个日志，且每个日志都有其它数据库写入，所以需要手工先分析日志，具体分析过程如下：

mysqlbinlog ./mysql-bin.000010

1.分析日志：

create database cw_d

/*!*/;

# at 4411

#140324 2:19:06 server id 1 end_log_pos 4568 Query thread_id=32914 exec_time=0 error_code=0

use `cw_d`/*!*/;

SET TIMESTAMP=1395641946/*!*/;

create table cw( id int auto_increment not null primary key, name varchar(20), number int(12))

/*!*/;

# at 4568

--stop-position=4568 --start-position=4411

恢复表：

mysqlbinlog --stop-position=4568 --start-position=4411 ./mysql-bin.000010

恢复数据1：

mysqlbinlog --stop-position=7073 --start-position=6473 ./mysql-bin.000010

执行恢复表后：

[root@69 mysql]# mysqlbinlog --stop-position=4568 --start-position=4411 ./mysql-bin.000010 | mysql -u root -p1807099411

mysql> select * from cw;

ERROR 1146 (42S02): Table ‘cw_d.cw‘ doesn‘t exist

mysql> select * from cw; //检查可以查询到表了，说明恢复成功了。

Empty set (0.00 sec)

mysql>

2.分析日志：

mysqlbinlog ./mysql-bin.000011

#140324 2:18:59 server id 1 end_log_pos 4411 Query thread_id=32914 exec_time=0 error_code=0

SET TIMESTAMP=1395641939/*!*/;

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

create database cw_d

/*!*/;

# at 4411

#140324 2:19:06 server id 1 end_log_pos 4568 Query thread_id=32914 exec_time=0 error_code=0

use `cw_d`/*!*/;

SET TIMESTAMP=1395641946/*!*/;

create table cw( id int auto_increment not null primary key, name varchar(20), number int(12))

/*!*/;

# at 4568

执行恢复表数据后

[root@69 mysql]# mysqlbinlog --stop-position=7073 --start-position=6473 ./mysql-bin.000010 |mysql -u root -p1807099411

mysql> select * from cw;

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

| id | name | number |

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

| 1 | cw1 | 1 |

| 2 | cw2 | 2 |

| 3 | cw3 | 3 |

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

3 rows in set (0.00 sec)

mysql>

分析另外2个二进制文件：

mysqlbinlog ./mysql-bin.000011

# at 1381

#140324 2:21:20 server id 1 end_log_pos 1409 Intvar

SET INSERT_ID=4/*!*/;

# at 1409

#140324 2:21:20 server id 1 end_log_pos 1518 Query thread_id=32914 exec_time=0 error_code=0

use `cw_d`/*!*/;

SET TIMESTAMP=1395642080/*!*/;

insert into cw(name,number) values(‘cw4‘,0004)

/*!*/;

# at 1518

#140324 2:21:20 server id 1 end_log_pos 1545 Xid = 5378105

COMMIT/*!*/;

# at 1545

#140324 2:21:28 server id 1 end_log_pos 1613 Query thread_id=32914 exec_time=0 error_code=0

SET TIMESTAMP=1395642088/*!*/;

BEGIN

/*!*/;

# at 1613

#140324 2:21:28 server id 1 end_log_pos 1641 Intvar

SET INSERT_ID=5/*!*/;

# at 1641

#140324 2:21:28 server id 1 end_log_pos 1750 Query thread_id=32914 exec_time=0 error_code=0

SET TIMESTAMP=1395642088/*!*/;

insert into cw(name,number) values(‘cw5‘,0005)

/*!*/;

# at 1750

#140324 2:21:28 server id 1 end_log_pos 1777 Xid = 5378131

COMMIT/*!*/;

# at 1777

#140324 2:21:34 server id 1 end_log_pos 1845 Query thread_id=32914 exec_time=0 error_code=0

SET TIMESTAMP=1395642094/*!*/;

BEGIN

/*!*/;

# at 1845

#140324 2:21:34 server id 1 end_log_pos 1873 Intvar

SET INSERT_ID=6/*!*/;

# at 1873

#140324 2:21:34 server id 1 end_log_pos 1982 Query thread_id=32914 exec_time=0 error_code=0

SET TIMESTAMP=1395642094/*!*/;

insert into cw(name,number) values(‘cw6‘,0006)

/*!*/;

# at 1982

# at 2612

#140324 2:21:49 server id 1 end_log_pos 2680 Query thread_id=32914 exec_time=0 error_code=0

SET TIMESTAMP=1395642109/*!*/;

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

BEGIN

/*!*/;

# at 2680

#140324 2:21:49 server id 1 end_log_pos 2708 Intvar

SET INSERT_ID=7/*!*/;

# at 2708

#140324 2:21:49 server id 1 end_log_pos 2817 Query thread_id=32914 exec_time=0 error_code=0

use `cw_d`/*!*/;

SET TIMESTAMP=1395642109/*!*/;

insert into cw(name,number) values(‘cw7‘,0007)

/*!*/;

# at 2817

#140324 2:21:49 server id 1 end_log_pos 2844 Xid = 5378286

COMMIT/*!*/;

# at 2844

根据上面的语句生成日志：

[root@69 mysql]# mysqlbinlog --stop-position=2077 --start-position=1982 ./mysql-bin.000011 |mysql -u root -p1807099411

[root@69 mysql]# mysqlbinlog --stop-position=2844 --start-position=2612 ./mysql-bin.000011 |mysql -u root -p1807099411

恢复日志

mysqlbinlog --stop-position=1777 --start-position=1381 ./mysql-bin.000012 |mysql -u root -p1807099411

mysql> select * from cw;

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

| id | name | number |

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

| 1 | cw1 | 1 |

| 2 | cw2 | 2 |

| 3 | cw3 | 3 |

| 4 | cw4 | 4 |

| 5 | cw5 | 5 |

| 6 | cw6 | 6 |

| 7 | cw7 | 7 |

| 8 | cw8 | 8 |

| 9 | cw9 | 9 |

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

9 rows in set (0.00 sec)

mysql>

本文出自 “痞子厨子戏子” 博客，请务必保留此出处http://chenwei.blog.51cto.com/32183/1382718

mysql备份恢复实验,古老的榕树,5-wow.com

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

mysql备份恢复实验

标签： mysqlmysql

mysql备份恢复实验

相关文章

随机文章

您可能还喜欢

您可能还喜欢

最新图文

您可能还喜欢

您可能还喜欢

文摘排行

文章排行

推荐文章

图文排行

推荐图文