mysql数据库备份 dump_mysqldump备份和恢复MySQL数据库

简单介绍：

通过MySQLdump命令可以将指定的库和表全部导出为sql脚本，可以在不同版块的Mysql上面使用。例如需要升级Mysql数据库可以使用mysqldump先备份所有数据库，然后直接在升级后的Mysql数据库导入即可。

基本操作：

备份单个数据库，或者库中的特定表(库名后面加表名)

mysqldump备份jiaowu库

[root@localhost ~]# mysqldump -uroot -p jiaowu > /root/jiaowu.sql

删除jiaowu的数据库

mysql> DROP DATABASE jiaowu;

删除之后导入备份文件说是没有jiaowu数据库

注意：mysqldump备份出来的数据库都是插入语句，还原的时候没有办法创建数据库需要手动创建数据库

[root@localhost ~]# mysql < jiaowu.sql

ERROR 1046 (3D000) at line 22: No database selected

手动创建jiaowu的数据库

mysql> CREATE DATABASE jiaowu;

还原jiaowu数据库

[root@localhost ~]# mysql jiaowu < jiaowu.sql

如果是生产环境备份时需要锁定所有表，不然在备份的时候有用户写入数据，会造成时间点不一样

锁定所有表

mysql> FLUSH TABLES WITH READ LOCK;

释放锁

mysql> UNLOCK TABLES;

参数说明：

--master-data={0|1|2}

0: 不记录二进制日志文件记录位置；

1：以CHNAGE MASTER TO的方式记录位置，可用于恢复后直接启动从服务器；

2：以CHANGE MASTER TO的方式记录位置，但默认为被注释；

备份jiaowu数据库

[root@localhost ~]# mysqldump -uroot -p --master-data=2 jiaowu > /root/jiaowu-`date +%F-%H-%M-%S`.sql

查看二进制日志记录位置

[root@localhost ~]# vim jiaowu-2014-11-27-17-02-38.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=9749;

--lock-all-tables：锁定所有表

--flush-logs: 执行日志滚动

如果指定库中的表类型均为InnoDB，可使用--single-transaction启动热备，不要和--lock-all-tables一块使用

备份多个库： 备份的时候自动创建库名，还原的时候不需要手动创建库

--all-databases: 备份所有库

--databases DB_NAME,DB_NAME,...: 备份指定库

--events 备份事件调度器的

--routines 备份存储过程和存储函数的

--triggers 备份触发器

模拟实验：备份所有库，并且所有库坏掉了如何还原

使用root用户备份所有库，滚动日志文件，记录二进制文件位置和路径，同时锁定所有库

[root@localhost ~]# mysqldump -uroot -p --lock-all-table --flush-logs --all-databases --master-data=2 > /root/alldatabases.sqlEnter password:

查看备份的数据库发现滚动的日志到了000007了

[root@localhost ~]# less alldatabases.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=107;

删除以前的滚动日志文件(生产环境建议先复制然后再删除)

mysql> PURGE BINARY LOGS TO 'mysql-bin.000007';

Query OK, 0 rows affected (0.19 sec)

mysql> SHOW BINARY LOGS;

+------------------+-----------+

| Log_name        | File_size |

+------------------+-----------+

| mysql-bin.000007 |      107 |

+------------------+-----------+

查看tutors表中数据

mysql> USE jiaowu;

Database changed

mysql> SELECT * FROM tutors;

+-----+--------------+--------+------+

| TID | Tname        | Gender | Age  |

+-----+--------------+--------+------+

|  1 | HongQigong  | M      |  93 |

|  2 | HuangYaoshi  | M      |  63 |

|  3 | Miejueshitai | F      |  72 |

|  4 | OuYangfeng  | M      |  76 |

|  5 | YiDeng      | M      |  90 |

|  6 | YuCanghai    | M      |  56 |

|  7 | Jinlunfawang | M      |  67 |

|  8 | HuYidao      | M      |  42 |

|  9 | NingZhongze  | F      |  49 |

+-----+--------------+--------+------+

删除年龄大于80的行

mysql> DELETE FROM tutors WHERE Age>80;

Query OK, 2 rows affected (0.00 sec)

然后一天过去了，要做增量备份

滚动日志

mysql> FLUSH LOGS;

Query OK, 0 rows affected (0.01 sec)

mysql> SHOW BINARY LOGS;

+------------------+-----------+

| Log_name        | File_size |

+------------------+-----------+

| mysql-bin.000007 |      343 |

| mysql-bin.000008 |      107 |

+------------------+-----------+

备份日志文件

[root@localhost ~]# cd /mydate/date/

[root@localhost date]# cp mysql-bin.000007 /root/

第二天往表中插入一行数据

mysql> INSERT INTO tutors (Tname) VALUES ('zhangsan');

删除数据库，但是把二进制日志文件复制出去，假设日志文件和数据不在同一目录存储，不然如果连二进制日志文件也删除了就没有办法做时间点恢复了

[root@localhost date]# cp mysql-bin.000008 /root/

[root@localhost date]# rm -rf ./*

这时就会发现MySQL停止不了了，所以只要关闭进程

[root@localhost date]# service mysqld stop

MySQL server PID file could not be found!                  [失败]

[root@localhost date]# killall mysqld

初始化MySQL数据库

[root@localhost date]# cd /usr/local/mysql/

[root@localhost mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydate/date/

首先还原完全备份的数据库文件

[root@localhost ~]# mysql -uroot -p < alldatabases.sql

这时数据就恢复到了完全备份之前

被删除的大于80岁的两个用户还存在

mysql> USE jiaowu;

Database changed

mysql> SELECT * FROM tutors;

+-----+--------------+--------+------+

| TID | Tname        | Gender | Age  |

+-----+--------------+--------+------+

|  1 | HongQigong  | M      |  93 |

|  2 | HuangYaoshi  | M      |  63 |

|  3 | Miejueshitai | F      |  72 |

|  4 | OuYangfeng  | M      |  76 |

|  5 | YiDeng      | M      |  90 |

|  6 | YuCanghai    | M      |  56 |

|  7 | Jinlunfawang | M      |  67 |

|  8 | HuYidao      | M      |  42 |

|  9 | NingZhongze  | F      |  49 |

+-----+--------------+--------+------+

9 rows in set (0.00 sec)

将第一次和第二次备份的二进制文件转化为sql文件，然后导入第一次的增量备份和第二次的增量备份

[root@localhost ~]# mysqlbinlog mysql-bin.000007 > diyici.sql

[root@localhost ~]# mysqlbinlog mysql-bin.000008 > dierci.sql

[root@localhost ~]# mysql -uroot -p < diyici.sql

Enter password:

[root@localhost ~]# mysql -uroot -p < dierci.sql

Enter password:

这时数据就恢复了

mysql> SELECT * FROM tutors;

+-----+--------------+--------+------+

| TID | Tname        | Gender | Age  |

+-----+--------------+--------+------+

|  2 | HuangYaoshi  | M      |  63 |

|  3 | Miejueshitai | F      |  72 |

|  4 | OuYangfeng  | M      |  76 |

|  6 | YuCanghai    | M      |  56 |

|  7 | Jinlunfawang | M      |  67 |

|  8 | HuYidao      | M      |  42 |

|  9 | NingZhongze  | F      |  49 |

|  10 | zhangsan    | M      | NULL |

+-----+--------------+--------+------+

注意：生产环境：在恢复数据库的时候关闭二进制日志文件，不然就产生很多没有用的记录，数据恢复完成后开启记录二进制日志文件

临时性关闭二进制日志记录

mysql> SET sql_log_bin=0;

Query OK, 0 rows affected (0.00 sec)

开启二进制日志文件记录

mysql> SET sql_log_bin=1;

Query OK, 0 rows affected (0.00 sec)