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

一、环境介绍:

[root@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)
[root@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  
[root@test ~]# 
[root@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
[root@test binlogs]#

二、准备测试数据

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

# 创建 myisam 引擎的表

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引擎的表

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

刷新并打开读锁:

mysql> FLUSH TABLES WITH READ LOCK;

##在终端2

创建备份目录:

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

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

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

##回到终端1

解锁:

mysql> UNLOCK TABLES;

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

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

然后关闭Mysql服务:

[root@test ~]# kill -9 pid

进行数据库初始化:

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

登录查看mydb已经不存在:

[root@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  转义别名,不然复制时老是提醒是否覆盖

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

重启mysql查看mydb已经恢复:

[root@test ~]# /data/mysql/init.d/mysqld_multi.server restart 1
[root@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日志:

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>

备份所有库(完全备份)

[root@test ~]# mkdir -p /root/mybackup
[root@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
[root@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选项起到作用。

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表插入几条新数据:

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

查看binlog:

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(增量备份):

[root@test ~]# cp /data/mysql/binlogs/bin-log-mysqld1.000010 /root/mybackup/$(date +%F-%H-%M).bin-log-mysqld1.000010
[root@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
[root@test ~]#

模拟误操作(删除myisam_tbl表):

mysql> drop table myisam_tbl;

恢复:

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

mysql> set sql_log_bin=0;  -- 关闭binlog
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>
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>

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

[root@test ~]# mysqlbinlog --no-defaults /root/mybackup/2015-01-23-14-29.bin-log-mysqld1.000010 | mysql -uroot -p mydb
mysql> set sql_log_bin=1;
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语句插入数据,而恢复时候插入数据的日志没有意义。