mysql备份恢复实验-阿里云开发者社区

开发者社区> 数据库> 正文

mysql备份恢复实验

简介:

内容大纲:

一 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 开始了。

2.

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>



本文转自 woshiwei201 51CTO博客,原文链接:http://blog.51cto.com/chenwei/1382718



版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章