MySQL备份和还原

3t4342

                      说明：如果要备份，请确保 mysql 打开 log-bin，有了 binarylog，mysql 才可以在必要的时候做完整恢复，或基于时间点的恢复，或基于位置的恢复。
一、环境介绍：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

[iyunv@test ~]# /data/mysql/bin/mysql -uroot -p3306 -h127.0.0.1 -p mysql> show variables like '%datadir%'; +---------------+--------------------+ | Variable_name | Value              | +---------------+--------------------+ | datadir       | /data/mysql/data1/ | +---------------+--------------------+ 1 row in set (0.00 sec) [iyunv@test ~]# cat /data/mysql/etc/my.cnf | grep bin-log-mysqld1 log-bin = /data/mysql/binlogs/bin-log-mysqld1   log-bin-index = /data/mysql/binlogs/bin-log-mysqld1.index   [iyunv@test ~]# [iyunv@test binlogs]# ll bin-log-mysqld1* -rw-rw---- 1 mysql mysql 126 Jan 22 14:03 bin-log-mysqld1.000001 -rw-rw---- 1 mysql mysql 126 Jan 22 14:04 bin-log-mysqld1.000002 -rw-rw---- 1 mysql mysql 126 Jan 22 14:10 bin-log-mysqld1.000003 -rw-rw---- 1 mysql mysql 126 Jan 22 14:19 bin-log-mysqld1.000004 -rw-rw---- 1 mysql mysql 294 Jan 23 09:45 bin-log-mysqld1.000005 -rw-rw---- 1 mysql mysql 156 Jan 23 09:47 bin-log-mysqld1.000006 -rw-rw---- 1 mysql mysql 107 Jan 23 09:47 bin-log-mysqld1.000007 -rw-rw---- 1 mysql mysql 301 Jan 23 09:47 bin-log-mysqld1.index [iyunv@test binlogs]#

二、准备测试数据

1 2 3 4 5

mysql> create database mydb; Query OK, 1 row affected (0.00 sec) mysql> use mydb; Database changed mysql>

# 创建 myisam 引擎的表

1 2 3 4 5 6 7

CREATE TABLE myisam_tbl( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(50), PRIMARY KEY(id) ) ENGINE=myisam DEFAULT charset=utf8; INSERT INTO myisam_tbl(name) VALUES('a'),('aa'),('aaa'),('aaaa'),('aaaaa'),('aaaaaa'); INSERT INTO myisam_tbl(name) SELECT name FROM myisam_tbl;

#创建innodb引擎的表

1 2 3 4 5 6 7

CREATE TABLE innodb_tbl( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(50), PRIMARY KEY(id) ) engine = innodb DEFAULT charset=utf8; # 把myisam_tbl的数据弄到innodb_tbl表 INSERT INTO innodb_tbl(name) SELECT name FROM myisam_tbl;

三、直接拷贝数据库文件（文件系统备份工具 cp），物理备份（适合小型数据库）
标准流程：锁表->刷新表到磁盘->停止服务->拷贝文件->解锁
冷备份步骤：
备份：
1.停掉 mysql 服务，在操作系统级别备份 mysql 的数据文件。
2.重启 mysql 服务，备份重启以后生成的 binlog。
恢复：
1.停掉 mysql 服务，在操作系统级别恢复 mysql 的数据文件。
2.重启 mysql 服务，使用 mysqlbinlog 恢复自备份以来的 binlog。
##在终端1
刷新并打开读锁:

1	mysql> FLUSH TABLES WITH READ LOCK;

##在终端2
创建备份目录:

1	[iyunv@test ~]# mkdir /mnt/mysql-$(date +%F)

以归档模式拷贝所有的数据文件:

1	[iyunv@test ~]# cp -a /data/mysql/data1/* /mnt/mysql-2015-01-23/

##回到终端1
解锁：

1	mysql> UNLOCK TABLES;

模拟数据库损坏，删除原来的所有数据文件：

1	[iyunv@test ~]# rm -rf /data/mysql/data1/*

然后关闭Mysql服务：

1	[iyunv@test ~]# kill -9 pid

进行数据库初始化:

1 2	[iyunv@test mysql]# scripts/mysql_install_db --basedir=/data/mysql --datadir=/data/mysql/data1 --user=mysql [iyunv@test mysql]# /data/mysql/init.d/mysqld_multi.server start 1

登录查看mydb已经不存在：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

[iyunv@test ~]# /data/mysql/bin/mysql -uroot -p3306 -h127.0.0.1 -p Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.14-log Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | performance_schema | | test               | +--------------------+ 4 rows in set (0.00 sec) mysql>

恢复过程，复制完全备份的数据文件到数据目录
# \cp  转义别名，不然复制时老是提醒是否覆盖

1	[iyunv@test ~]# \cp -a /mnt/mysql-2015-01-23/* /data/mysql/data1/

重启mysql查看mydb已经恢复:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

[iyunv@test ~]# /data/mysql/init.d/mysqld_multi.server restart 1 [iyunv@test ~]# /data/mysql/bin/mysql -uroot -p3306 -h127.0.0.1 -p Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.5.14-log Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mydb               | | mysql              | | performance_schema | | test               | +--------------------+ 5 rows in set (0.00 sec) mysql>

四、mysqldump逻辑备份数据库（完全备份+增加备份，速度相对较慢，适合中小型数据库）（MyISAM是温备份，InnoDB是热备份）
逻辑备份步骤：
备份：
选择在系统空闲时，比如在夜间，使用 mysqldump -F(flush-logs)备份数据库。
# mysqldump -uroot -p1234 mydb -F > mydb.sql
并备份 mysqldump 开始以后生成的 binlog。

恢复:
停掉应用，执行 mysql 导入备份文件.
mysql -uroot -p1234 mydb < mydb.sql
使用 mysqlbinlog 恢复自 mysqldump 备份以来的 binlog。
mysqlbinlog $basedir/binlogs/bin-log-mysqld1.00000* | mysql -uroot -p1234

mysqldump -u -h -p--all-databases  --databases <dbname>   
--events            #备份事件
--flush-logs    #在数据库导出之前先执行FLUSH LOGS
--lock-all-tables   #锁定所有表
--lock-tables    #锁定某些表
--master-data=n    #(指定备份点:log file name and position)
--opt      #(PITR 精确恢复，时间点恢复)
--routines    #(备份存储过程、存储函数)
--triggers    #(备份触发器)
--single-transaction#(为事务性数据库提供备份)
--where    #(指定过滤条件，只备份符合条件数据)
由于mysqldump针对不同的引擎有所差异，所以务必清楚引擎类型。
温备:
    在使用MyISAM引擎中，只能使用温备份，这时候要防止数据的写入，所以先加上读锁。这时候可以进入数据库手动加读锁。这样比较麻烦，在mysqldump工具中直接有一个加锁的选项
# mysqldump --databases mydb --lock-all-tables --flush-logs> /tmp/backup-`date +%F-%H-%M`.sql
### 备份所有库（完全备份）
# mysqldump --all-databases --lock-all-tables --routines --triggers --events --master-data=2 --flush-logs > /root/mybackup/`date +%F-%H-%M`.full.sql
如果是针对某张表备份，只要在数据库名称后面加上表名称就行了
# mysqldump -uroot  -p mydb course > /root/mydb.sql备份表course
# mysqldump -uroot  -p --databases mydb dnsdata > /root/mydb.sql  同时备份两个数据库
这里注意，要实现时间点的恢复，加上--flush-logs选项，在使用备份文件恢复后，然后再基于二进制日志进行时间点的恢复。
热备：
如果使用的是InnoDB引擎，就不必进行对数据库加锁的操作，加一个选项既可以进行热备份：--single-transaction
# mysqldump --databases mydb --single-transaction  --flush-logs --master-data=2 > /tmp/backup-`date +%F-%H-%M`.sql

实验：
备份前的binlog日志:

1 2 3 4 5 6 7 8

mysql> show master status; +------------------------+----------+--------------+------------------+ | File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------------+----------+--------------+------------------+ | bin-log-mysqld1.000009 |      107 |              |                  | +------------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) mysql>

备份所有库（完全备份）

1 2	[iyunv@test ~]# mkdir -p /root/mybackup [iyunv@test ~]# mysqldump -uroot -p --all-databases --lock-all-tables --routines --triggers --events --master-data=2 --flush-logs > /root/mybackup/$(date +%F-%H-%M).full.sql

1 2 3

[iyunv@test ~]# ll mybackup/ total 480 -rw-r--r-- 1 root root 486319 Jan 23 14:13 2015-01-23-14-13.full.sql

查看备份后的binlog，主要是--flush-logs选项起到作用。

1 2 3 4 5 6 7 8

mysql> show master status; +------------------------+----------+--------------+------------------+ | File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------------+----------+--------------+------------------+ | bin-log-mysqld1.000010 |      107 |              |                  | +------------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) mysql>

向myisam_tbl表插入几条新数据：

1	mysql> INSERT INTO myisam_tbl(name) VALUES('val1'), ('val2'), ('val3');

查看binlog：

1 2 3 4 5 6 7 8

mysql> show master status; +------------------------+----------+--------------+------------------+ | File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------------+----------+--------------+------------------+ | bin-log-mysqld1.000010 |      398 |              |                  | +------------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) mysql>

备份binlog（增量备份）：

1 2 3 4 5 6

[iyunv@test ~]# cp /data/mysql/binlogs/bin-log-mysqld1.000010 /root/mybackup/$(date +%F-%H-%M).bin-log-mysqld1.000010 [iyunv@test ~]# ll mybackup/ total 484 -rw-r--r-- 1 root root 486319 Jan 23 14:13 2015-01-23-14-13.full.sql -rw-r----- 1 root root    398 Jan 23 14:29 2015-01-23-14-29.bin-log-mysqld1.000010 [iyunv@test ~]#

模拟误操作（删除myisam_tbl表）：

1	mysql> drop table myisam_tbl;

恢复：
恢复前先关闭对恢复过程的二进制日志记录，因为记录恢复语句是毫无意义的。

1	mysql> set sql_log_bin=0;  -- 关闭binlog

1 2 3 4 5 6 7 8 9 10 11 12

mysql>  \. /root/mybackup/2015-01-23-14-13.full.sql mysql> use mydb; Database changed mysql> show tables; +----------------+ | Tables_in_mydb | +----------------+ | innodb_tbl     | | myisam_tbl     | +----------------+ 2 rows in set (0.00 sec) mysql>

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

mysql> SELECT * FROM myisam_tbl; +----+--------+ | id | name   | +----+--------+ |  1 | a      | |  2 | aa     | |  3 | aaa    | |  4 | aaaa   | |  5 | aaaaa  | |  6 | aaaaaa | |  7 | a      | |  8 | aa     | |  9 | aaa    | | 10 | aaaa   | | 11 | aaaaa  | | 12 | aaaaaa | +----+--------+ 12 rows in set (0.00 sec) mysql>

说明：现在已经恢复到完全备份时的状态，但我们最后插入的三条数据没有恢复。

1	[iyunv@test ~]# mysqlbinlog --no-defaults /root/mybackup/2015-01-23-14-29.bin-log-mysqld1.000010 | mysql -uroot -p mydb

1	mysql> set sql_log_bin=1;

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

mysql> SELECT * FROM myisam_tbl; +----+--------+ | id | name   | +----+--------+ |  1 | a      | |  2 | aa     | |  3 | aaa    | |  4 | aaaa   | |  5 | aaaaa  | |  6 | aaaaaa | |  7 | a      | |  8 | aa     | |  9 | aaa    | | 10 | aaaa   | | 11 | aaaaa  | | 12 | aaaaaa | | 13 | val1   | | 14 | val2   | | 15 | val3   | +----+--------+ 15 rows in set (0.00 sec) mysql>

可以看到已经恢复成功！

时间点的恢复：
如果今天上午11点发生了误操作，可以用以下语句使用备份和 binglog 将数据恢复到故障前：
mysqlbinlog --no-defaults --stop-date="2015-01-23 10:59:59" /var/log/mysql/bin.123456 | mysql -uroot -p
跳过故障时的时间点，继续执行后面的binlog，完成恢复：
mysqlbinlog --no-defaults --start-date="2015-01-23 11:01:00" /var/log/mysql/bin.123456 | mysql -u root -p
位置恢复：
和时间点恢复类似，但是更精确:
mysqlbinlog --no-defaults --start-date="2015-01-23 10:55:00" --stop-date="2015-01-23 11:05:00" /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql
该命令将在/tmp 目录创建小的文本文件，编辑此文件，找到出错语句前后的位置号，例如前后位置号分别是 10000和 20000：
恢复了以前的备份文件后，你应从命令行输入下面内容：
mysqlbinlog --no-defaults --stop-position="10000" /var/log/mysql/bin.123456 | mysql -u root -p
mysqlbinlog --no-defaults --start-position="20000" /var/log/mysql/bin.123456 | mysql -u root -p
上面的第 1 行将恢复到停止位置为止的所有事务。下一行将恢复从给定的起始位置直到二进制日志结束的所有事务。因为 mysqlbinlog 的输出包括每个 SQL 语句记录
之前的 SET TIMESTAMP 语句，恢复的数据和相关 MySQL 日志将反应事务执行的原时间。

注意点:
恢复的时刻关闭二进制日志
mysql>set sql_log_bin=0;
因为这是基于逻辑备份方式，在恢复日志时会执行sql语句插入数据，而恢复时候插入数据的日志没有意义。