使用mysqldump命令进行数据库备份和恢复

作者

一、环境准备

1、创建存放备份的目录以及存放二进制日志的目录

   # mkdir /backup
   # chown -R mysql:mysql /backup
   # mkdir /binlogdir
   # chown mysql:mysql /binlogdir

2、修改二进制文件存放路径为新建目录下，并设定innodb每表一文件

 # vim /etc/my.cnf
   log-bin=/binlogdir/mysql-bin   #二进制日志文件存放位置
   innodb_file_per_table = 1      #启用InnoDB表每表一文件，默认所有库使用一个表空间

3、启动服务并创建数据库

   # service mysqld start
   # mysql -uroot -ptsb-net
   mysql> create database testdb;
   mysql> use testdb;
   mysql> CREATE TABLE tb1 (ID TINYINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, 
   Name CHAR(30) NOT NULL UNIQUE KEY, Age TINYINT UNSIGNED);
   mysql> insert into tb1 values (1,'Huchong',24);
   mysql> insert into tb1 values (2,'Huafeng',24);
   mysql> insert into tb1 values (3,'Jim',21);
   mysql> insert into tb1 values (4,'Tom',25);

二、进行备份（完全备份+增量备份+节点恢复）

1、完全备份

# mysqldump -uroot -ptsb-net --lock-all-tables --flush-logs --events --routines --master-data=2 --all-databases > /backup/fulldata-`date +%F`.sql
    --single-transaction: 基于此选项能实现热备InnoDB表；mysql> show table status\G；可以查看表的类型，如果表的类型不是全部为InnoDB，则需要使用--lock-all-tables；
    --events：备份数据的同时，备份事件调度器代码；
    --master-data=2：在备份文件中记录当前二进制日志的位置
    --all-databases：备份所有库
    --flush-logs：刷新二进制日志，产生一个新的二进制日志文件

2、模拟增量备份并进行增量备份

# mysql -uroot -ptsb-net
mysql> use testdb;
mysql> delete from tb1 where id = 4;  #模拟增量备份
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      297 |              |                  |
+------------------+----------+--------------+------------------+
mysql> flush logs;
mysql> quit;
# mysqlbinlog /binlogdir/mysql-bin.000002 > /backup/cactidb-`date +%F`.sql #增量备份

三、产生新的二进制事件并模拟数据库损坏

# mysql -uroot -ptsb-net
mysql> use testdb;
mysql> delete from tb1 where id = 3;
mysql> drop database testdb;    #模拟数据库损坏

四、数据库恢复

1、恢复前的准备

mysql> set global sql_log_bin=0;                  #不需要记录二进制日志
mysql> show global variables like 'sql_log_bin';  #查看二进制日志状态
mysql> flush logs;            #滚动日志
mysql> show master status;

+——————+———-+————–+——————+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+——————+———-+————–+——————+

| mysql-bin.000004 |      107 |              |                  |

+——————+———-+————–+——————+

2、导入完全备份及增量备份

# mysql -u root -ptsb-net < /backup/fulldata-2015-07-17.sql
# mysql -u root -ptsb-net < /backup/cactidb-2015-07-17.sql

3、节点恢复（目前日志文件是mysql-bin.000004，所以我们需要恢复的文件是mysql-bin.000003）

# mysqlbinlog  /binlogdir/mysql-bin.000003    #查看日志文件

# mysqlbinlog --stop-position=297 /binlogdir/mysql-bin.000003 > /backup/last.sql
# mysql -u root -ptsb-net < /backup/last.sql 
# mysql -u root -ptsb-net
mysql> use testdb;
mysql> select * from tb1;

五、收尾工作

mysql> set global sql_log_bin=1;    #开启二进制日志记录
mysql> quit;
# service mysqld restart

总结：mysqldump工具对InnoDB存储引擎的表可实现热备，备份时可以不锁定表，否则在备份前需要锁定表，只能实现温备。当备份的数据 量较大时，恢复时间会很长，因为恢复是把一条一条的sql语句读取后在mysql中执行，是写入操作。所以建议在数据量不大时可以使用此工具进行备份操 作，但当随着数据增长此工具将不适合进行mysql的备份可恢复操作，应选取其他更优的数据备份恢复方案。