MySQL 备份和恢复（一）mysqldump

MariaDB [hellodb]> SELECT * FROM scores;

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

| ID | StuID | CourseID | Score |

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

|  1 |     1 |        2 |    77 |

|  2 |     1 |        6 |    93 |

|  3 |     2 |        2 |    47 |

|  4 |     2 |        5 |    97 |

|  5 |     3 |        2 |    88 |

|  6 |     3 |        6 |    75 |

|  7 |     4 |        5 |    71 |

|  8 |     4 |        2 |    89 |

|  9 |     5 |        1 |    39 |

| 10 |     5 |        7 |    63 |

| 11 |     6 |        1 |    96 |

| 12 |     7 |        1 |    86 |

| 13 |     7 |        7 |    83 |

| 14 |     8 |        4 |    57 |

| 15 |     8 |        3 |    93 |

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

15 rows in set (0.00 sec)

MariaDB [hellodb]> DELETE FROM scores WHERE ID>10 AND ID<=14;

Query OK, 4 rows affected (0.08 sec)

MariaDB [hellodb]> SELECT * FROM hellodb.scores;

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

| ID | StuID | CourseID | Score |

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

|  1 |     1 |        2 |    77 |

|  2 |     1 |        6 |    93 |

|  3 |     2 |        2 |    47 |

|  4 |     2 |        5 |    97 |

|  5 |     3 |        2 |    88 |

|  6 |     3 |        6 |    75 |

|  7 |     4 |        5 |    71 |

|  8 |     4 |        2 |    89 |

|  9 |     5 |        1 |    39 |

| 10 |     5 |        7 |    63 |

| 15 |     8 |        3 |    93 |

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

11 rows in set (0.00 sec)

/* 这里把二进制日志关掉插入些数据，下面会把二进制再打开，再插入些数据 */

MariaDB [hellodb]> SET sql_log_bin=0;

Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> SHOW VARIABLES LIKE '%SQL_LOG_BIN%';

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

| Variable_name | Value |

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

| sql_log_bin   | OFF   |

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

1 row in set (0.00 sec)

MariaDB [hellodb]> SHOW MASTER STATUS;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000006 |      495 |              |                  |

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

MariaDB [hellodb]> INSERT INTO scores (StuID,CourseID,Score) VALUES (3,3,3),(4,4,4),(5,5,5);

Query OK, 3 rows affected (0.06 sec)

Records: 3  Duplicates: 0  Warnings: 0

/* 这里顺带看下二进制日志位置在495 */

MariaDB [hellodb]> SHOW MASTER STATUS;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000006 |      495 |              |                  |

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

1 row in set (0.00 sec)

MariaDB [hellodb]> SET sql_log_bin=1;

Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> SHOW VARIABLES LIKE 'sql_log_bin';

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

| Variable_name | Value |

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

| sql_log_bin   | ON    |

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

1 row in set (0.00 sec)

/* 把binlog打开后再插入一些数据 */

MariaDB [hellodb]> INSERT INTO scores (StuID,CourseID,Score) VALUES (10,11,12),(13,14,15);

Query OK, 2 rows affected (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 0

/* LAST_INSERT_ID()这个函数可以查看有AUTO_INCREMENT属性的字段上次长到哪里了 */

MariaDB [hellodb]> SELECT LAST_INSERT_ID();

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

| LAST_INSERT_ID() |

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

|               19 |

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

1 row in set (0.00 sec)

/* 仔细看一下下面的查询结果我们新插入的数据并没有按顺序在ID=15后面，而是依次被存入到之前删除的行释放后的存储空间里了 */

MariaDB [hellodb]> SELECT * FROM scores;

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

| ID | StuID | CourseID | Score |

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

|  1 |     1 |        2 |    77 |

|  2 |     1 |        6 |    93 |

|  3 |     2 |        2 |    47 |

|  4 |     2 |        5 |    97 |

|  5 |     3 |        2 |    88 |

|  6 |     3 |        6 |    75 |

|  7 |     4 |        5 |    71 |

|  8 |     4 |        2 |    89 |

|  9 |     5 |        1 |    39 |

| 10 |     5 |        7 |    63 |

| 19 |    10 |       11 |    12 |

| 18 |     5 |        5 |     5 |

| 17 |     4 |        4 |     4 |

| 16 |     3 |        3 |     3 |

| 15 |     8 |        3 |    93 |

| 20 |    13 |       14 |    15 |

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

16 rows in set (0.00 sec)

/* 删除hellodb数据库，模拟数据库崩溃 */

MariaDB [(none)]> DROP DATABASE hellodb;

Query OK, 9 rows affected (0.47 sec)

MariaDB [(none)]> SHOW DATABASES;

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

| Database           |

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

| information_schema |

| mydb               |

| mysql              |

| performance_schema |

| test               |

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

5 rows in set (0.01 sec)

/* 因为全备时用了--flush-logs选项，所以只要备份最后一个二进制日志就可以了 */

cp /mydata/data/mysql-bin.000006 /backup/

[root@node1 data]# cd /backup/

/* 因为执行了DROP DATABASE语句，所以恢复时把这个语句之前的binlog应用就可以了 */

[root@node1 backup]# mysqlbinlog 'mysql-bin.000006' | grep 'DROP DATABASE' -B 15

#140729 13:59:59 server id 1  end_log_pos 594   Intvar

SET INSERT_ID=19/*!*/;

# at 594

#140729 13:59:59 server id 1  end_log_pos 730   Query   thread_id=25    exec_time=0 error_code=0

SET TIMESTAMP=1406613599/*!*/;

INSERT INTO scores (StuID,CourseID,Score) VALUES (10,11,12),(13,14,15)

/*!*/;

# at 730

#140729 13:59:59 server id 1  end_log_pos 802   Query   thread_id=25    exec_time=0 error_code=0

SET TIMESTAMP=1406613599/*!*/;

COMMIT

/*!*/;

# at 802

#140729 14:15:57 server id 1  end_log_pos 889   Query   thread_id=26    exec_time=0 error_code=0

SET TIMESTAMP=1406614557/*!*/;

DROP DATABASE hellodb

/* 找到位置后把需要的日志导出来 */

[root@node1 backup]# mysqlbinlog 'mysql-bin.0000061' --stop-position=730 > /backup/inr-001.sql

/* 恢复完全备份,这里直接是全库备份了，当然也可以根据需要对单库单表备份 */

[root@node1 backup]# mysql < /backup/full_db_2014-07-29.sql