首页 > 数据库 > mysql > 使用mysqldump命令进行数据库备份和恢复
2015
07-22

使用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);

显示创建内容.png

二、进行备份(完全备份+增量备份+节点恢复)

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    #查看日志文件

QQ截图20150722111819.png

# 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;

QQ截图20150722112110.png

五、收尾工作

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

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




最后编辑:
作者:1084471221
这个作者貌似有点懒,什么都没有留下。
捐 赠您的支持是博主写作最大的动力,如果您喜欢我的文章,感觉我的文章对您有帮助,请狠狠点击

留下一个回复