mysql数据库的备份和恢复 mysql数据库备份及恢复案例

一 ，数据备份与恢复

步骤一：使用mysqldump进行逻辑备份

1）备份MySQL服务器上的所有库

将所有的库备份为mysql-all.sql文件：

1. [root@dbsvr1 ~]# mysqldump -u root -p --all-databases > /root/alldb.sql
2. Enter password:                                              //验证口令
3. [root@dbsvr1 mysql]# file /root/alldb.sql         //确认备份文件类型

查看备份文件alldb.sql的部分内容：

# grep -vE '^/|^-|^$' /root/alldb.sql  | head -20

2）只备份指定的某一个库

将userdb库备份为userdb.sql文件：

1. [root@dbsvr1 ~]# # mysqldump -u root -p jingcaiwang > jingcaiwang.sql
2. Enter password:                                 //验证口令

查看备份文件userdb.sql的部分内容：

3）同时备份指定的多个库

同时备份mysql、userdb库，保存为mysql+userdb.sql文件：

1. [root@dbsvr1 ~]# mysqldump -u root -p -B mysql userdb > mysql+test+userdb.sql
2. Enter password:                                 //验证口令

查看备份文件userdb.sql的部分内容：

1. [root@dbsvr1 ~]# grep '^CREATE DATA' /root/mysql+userdb.sql

 

 

步骤二：使用mysql命令从备份中恢复数据库、表

以恢复userdb库为例，可参考下列操作。通常不建议直接覆盖旧库，而是采用建立新库并导入逻辑备份的方式执行恢复，待新库正常后即可废弃或删除旧库。

1）创建名为userdb2的新库

mysql> create database userdb2;

 

2）导入备份文件，在新库中重建表及数据

1. [root@dbsvr1 ~]# mysql -u root -p userdb2 < /root/userdb.sql
2. Enter password:                                 //验证口令
eg:
# mysql -uroot -p jingcaiwang < /root/jingcaiwang-v2.sql                              //使用备份数据导入

3）确认新库正常，启用新库

1. mysql> USE userdb2;                             //切换到新库
2. mysql> show tables;

4）废弃或删除旧库

1. mysql> DROP DATABASE userdb;

-----------------------------------------------------------------------------------------------------------

二 ，使用binlog日志

步骤一：启用binlog日志

1）调整/etc/my.cnf配置，并重启服务

1. [root@dbsvr1 ~]# vim /etc/my.cnf
2. [mysqld]
3. .. ..
4. log-bin-index=mysql-bin                             //启用二进制日志，并指定前缀
5. server_id=1
6. binlog_format=STATEMENT
7. //在Mysql5.7中，binlog日志格式默认为ROW，但它不记录sql语句上下文相关信息。需要将binlog日志格式修改为STATEMENT
8. .. ..
9. [root@dbsvr1 ~]# systemctl restart mysqld.service

2）确认binlog日志文件

新启用binlog后，每次启动MySQl服务都会新生成一份日志文件：

1. [root@dbsvr1 ~]# ls /var/lib/mysql/mysql-bin.*
2. /var/lib/mysql/mysql-bin.000001 /var/lib/mysql/mysql-bin.index

 

步骤二：利用binlog日志重做数据库操作

1）执行数据库表添加操作

创建db1·库tb1表，表结构自定义：

1. mysql> CREATE DATABASE db1;
2.  
3. mysql> USE db1;
4. Database changed
5. mysql> CREATE TABLE tb1(
6. -> id int(4) NOT NULL,name varchar(24)
7. -> );

步骤三：通过binlog日志恢复表记录

binlog会记录所有的数据库、表更改操作，所以可在必要的时候重新执行以前做过的一部分数据操作，但对于启用binlog之前已经存在的库、表数据将不适用。

根据上述“恢复被删除的3条表记录”的需求，应通过mysqlbinlog工具查看相关日志文件，找到删除这些表记录的时间点，只要恢复此前的SQL操作（主要是插入那3条记录的操作）即可。

1）查看mysql-bin.000002日志内容

2） 执行指定Pos节点范围内的sql命令恢复数据

根据上述日志分析，只要恢复从2014.01.12 20:12:14到2014.01.12 20:13:50之间的操作即可。可通过mysqlbinlog指定时间范围输出，结合管道交给msyql命令执行导入重做：

1. [root@dbsvr1 ~]# mysqlbinlog \
2. --start-datetime="2017-04-12 12:06:55" \
3. --stop-datetime="2017-04-12 12:07:23" \
4. /var/lib/mysql/mysql-bin.000002 | mysql -u root -p
5. Enter password:                                  //验证口令