内容大纲：

一 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>