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');

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/1ea2dba8d6f7d88d572c03cfb3d2b7fa.html