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)

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

執行恢複：

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

驗證語句，如果能查詢到結果，說明已經成功了。

二 SQL語句備份恢複

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 * into outfile '/tmp/cw.sql' from cw;    //備份語句

Query OK, 9 rows affected (0.00 sec)

mysql> delete 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;     //驗證成功，能夠查詢到語句，說明恢複成功。

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-bin.000010 開始了。

2.

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> 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);

3 rows in set (0.00 sec)

| mysql-bin.000010 |      7676 |

| mysql-bin.000010 |      7719 |

| mysql-bin.000011 |       710 |

11 rows in set (0.00 sec)

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

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

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

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

mysql> delete from cw where number=0005;

6 rows in set (0.00 sec)

| mysql-bin.000011 |      4560 |

| mysql-bin.000012 |       107 |

12 rows in set (0.00 sec)

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

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

二 恢複過程

由于有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;         //檢查可以查詢到表了，說明恢複成功了。

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/*!*/;

執行恢複表資料後

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

分析另外2個二進制檔案：

# 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

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

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

# at 1750

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

# 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/*!*/;

# 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

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/*!*/;

# 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

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

# at 2817

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

# 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

本文轉自 woshiwei201 51CTO部落格，原文連結:http://blog.51cto.com/chenwei/1382718