首页 > 代码库 > MySQL 备份和恢复（一）mysqldump
MySQL 备份和恢复（一）mysqldump
备份方式：
热备：不中断服务
温备：备份过程中不可写，要加读锁
冷备：需要停掉服务
备份策略一：mysqldump + binlog 完全备份 + 增量，用mysqldump定期全备，每天（根据实际情况来）备份二进制日志.
mysqldump类似于一个mysql的客户端，所以用mysqldump备份时，mysql服务要启动起来，常用参数：
-u username | --user=username 指定备份的用户
-p password | --password=password
-h host | --host=host
-p port | --port=port
-A --all-databases 备份所有数据库
-B --databases db_name 选择要备份的数据库
-C --compress 启用压缩
-d --no-data 备份空库，不备份数据
-E --events 同时备份事件调度器代码
-F --flush-logs 备份前先滚动日志
--ignore-tables db_name.tb_name 指定哪些表不必备份
--lock-tables 要备份一张表时，备份前先锁定表
-x --lock-all-tables 备份前锁定所有库的所有表
--single-transaction 为备份创建一个大事务，基于快照原理实现MVCC对InnoDB热备，开启此选项则不用-x，只对InnoDB支持事务的表才可保证一致性
--master-data = http://www.mamicode.com/0|1|2 在复制架构中，指定如何处理配置主从服务器的语句如CHANGE MASTER TO statement，值为2记录但是是注释掉，应用时不生效，值为1记录不注释，应用时生效, 0为不启用。
过程简单演示：
[root@node1 ~]# mysqldump -uroot -pmypass --all-databases --master-data=http://www.mamicode.com/2 --lock-all-tables --flush-logs > /backup/full_db_`date +%F`.sql>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/* 恢复后可以看到hellodb已经恢复回来，数据内容也都在了 */ MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | | mydb | | mysql | | performance_schema | | test | +--------------------+ 6 rows in set (0.00 sec) MariaDB [(none)]> USE hellodb; Database changed 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) /* 上面删除数据的时候忘备份一份二进制日志了，图省事，手动造一下 */ [root@node1 backup]# mysqlbinlog ‘mysql-bin.000006‘ | grep --color ‘DELETE FROM‘ -C 5 /*!*/; # at 316 #140729 11:17:42 server id 1 end_log_pos 423 Query thread_id=25 exec_time=0 error_code=0 use `hellodb`/*!*/; SET TIMESTAMP=1406603862/*!*/; DELETE FROM scores WHERE ID>10 AND ID<=14 /*!*/; # at 423 #140729 11:17:42 server id 1 end_log_pos 495 Query thread_id=25 exec_time=0 error_code=0 SET TIMESTAMP=1406603862/*!*/; COMMIT [root@node1 backup]# mysqlbinlog ‘mysql-bin.000006‘ --stop-position=423 > /backup/inc-001.sql [root@node1 backup]# mysqlbinlog ‘mysql-bin.000006‘ --start-position=423 --stop-position=730 > /backup/inc-002.sql [root@node1 backup]# mysql < inc-001.sql /* 恢复到时间点一，ID 11到14被删除 */ MariaDB [(none)]> 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) /* 恢复到时间点二，插入了一些数据后，哎我去，咋的我插入了那老些数据就剩这一点了捏，这正是sql-bin-log变量起作用了，因为当时有几条数据是关闭了二进制日志做的操作，所以未被记录到binlog，自然恢复过来的数据中也就木有了，这点在做恢复时是非常有必要的，因为恢复时的操作都是之前做过的，关闭二进制日志可以避免不必要的开销。*/ [root@node1 backup]# mysql < inc-002.sql MariaDB [(none)]> 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 | | 20 | 13 | 14 | 15 | | 19 | 10 | 11 | 12 | | 15 | 8 | 3 | 93 | +----+-------+----------+-------+ 13 rows in set (0.00 sec)
本文出自 “不懂IT的中医不是好IT” 博客，请务必保留此出处http://zhishen.blog.51cto.com/1612050/1532279
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。