文档章节

备份与还原(mysqldump)(九)

雁南飞丶
 雁南飞丶
发布于 2014/09/19 12:30
字数 1463
阅读 31
收藏 0

MySQL的备份和还原

    备份:副本
    RAID1,RAID10:保证硬件损坏而不会业务中止;
        DROP TABLE mydb.tb1;

    备份类型:
        热备份、温备份和冷备份
            热备份:读、写不受影响;
            温备份:仅可以执行读操作;
            冷备份:离线备份;读、写操作均中止;

        物理备份和逻辑备份
            物理备份:复制数据文件;
            逻辑备份:将数据导出至文本文件中;

        完全备份、增量备份和差异备份;
            完全备份:备份全部数据;
            增量备份:仅备份上次完全备份或增量备份以后变化的数据;
            差异备份:仅备份上次完全备份以来变化的数据;

    在线:物理完全备份

还原:

        备份什么:
            数据、配置文件、二进制日志、事务日志

热备份:
    MyISAM: 温备份
    InnoDB: xtrabackup, mysqldump

MySQL --> 从:

物理备份:速度快
逻辑备份:速度慢、丢失浮点数精度;方便使用文本处理工具直接对其处理、可移植能力强;


备份策略:完全+增量;完全+差异
    
MySQL备份工具:
mysqldump: 逻辑备份工具、MyISAM(温)、InnoDB(热备份)
mysqlhotcopy:物理备份工具、温备份

文件系统工具:
cp:冷备
lv: 逻辑卷的快照功能,几乎热备;
    mysql> FLUSH TABLES;
    mysql> LOCK TABLES

    创建快照:释放锁,而后复制数据

    InnoDB:

第三组工具:
    ibbackup: 商业工具
    xtrabackup: 开源工具

mysqldump: 逻辑备份
    mysqldump(完全备份)+ 二进制日志
    完全+增量:

备份单个数据库,或库中特定表
    mysqldump DB_NAME [tb1] [tb2]

用mysqldump备份与还原

先锁定表

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

备份完解锁

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)



备份单个数据库,或指定的数据库的某张表

[root@localhost ~]# mysqldump -uroot -p jiaowu >/tmp/jiaowu.sql
[root@localhost ~]# mysql < /tmp/jiaowu.sql 
ERROR 1046 (3D000) at line 22: No database selected
[root@localhost ~]# mysqladmin create leo
[root@localhost ~]# mysql leo < /tmp/jiaowu.sql  导入指定的数据库
mysql> use leo;
Database changed
mysql> show tables;
+---------------+
| Tables_in_leo |
+---------------+
| courses       |
| scores        |
| sct           |
| students      |
| tutors        |
+---------------+
5 rows in set (0.00 sec)

如果锁定表以后有新插入的数据应该滚动日志

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.18 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000003 |       126 |
| mysql-bin.000004 |       150 |
| mysql-bin.000005 |       126 |
| mysql-bin.000006 |     15455 |
| mysql-bin.000007 |       126 |
| mysql-bin.000008 |       374 |
| mysql-bin.000009 |       150 |
| mysql-bin.000010 |      5503 |
| mysql-bin.000011 |       107 |
+------------------+-----------+
9 rows in set (0.00 sec)
最后一个mysql-bin.0000011就是备份以后产生二进制日志

--master-data={0|1|2}
    0: 不记录二进制日志文件及路位置;
    1:以CHNAGE MASTER TO的方式记录位置,可用于恢复后直接启动从服务器;
    2:以CHANGE MASTER TO的方式记录位置,但默认为被注释;

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



[root@localhost ~]# cat leo-2014-09-19-11-25-37.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=107;
表示记录了二进制文件的位置和文件名

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

--flush-logs: 执行日志flush;

如果指定库中的表类型均为InnoDB,可使用--single-transaction启动热备; 这个选项不要和--lock-all-table一块使用,

备份多个库:
    --all-databases: 备份所有库
    --databases DB_NAME,DB_NAME,...: 备份指定库

[root@localhost ~]# mysqldump -uroot -predhat --lock-tables --flush-logs --all-databases >/root/all.sql
如果出现警告
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
 这是因为mysqldump默认是不备份事件表的,只有加了--events 才会,解决办法:

加上--events --ignore-table=mysql.events参数即可; 
[root@localhost ~]# mysqldump -uroot -predhat --lock-tables --flush-logs --all-databases --events --ignore-table=mysql.events  >/root/all2.sql

[root@localhost ~]# mysqldump -uroot -predhat --lock-tables --flush-logs --all-databases --events --ignore-table=mysql.events --master-data=2  >/root/all2.sql

加上--master-data=2表示记录事件位置

--events
    --routines
    --triggers

备份策略:每周完全+每日增量
    完全备份:mysqldump
    增量备份:备份二进制日志文件(flush logs)

[root@localhost ~]# mysqldump -uroot -predhat --master-data=2 --flush-logs --all-databases --lock-all-tables --events > /opt/alldata.sql
 备份完就可以删除之前的二进制文件
mysql> purge binary logs to 'mysql-bin.000057';
Query OK, 0 rows affected (0.09 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000057 |       107 |
+------------------+-----------+
1 row in set (0.00 sec)

然后操作数据读写,删除2行数据

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 |
|  10 | Tom          | F      |   30 |
|  11 | DingDian     | M      |   25 |
|  12 | HuFei        | M      |   31 |
|  13 | ZhangWuji    | M      |   20 |
|  14 | Xuzhu        | M      |   26 |
|  15 | kcw          | M      |   30 |
+-----+--------------+--------+------+
15 rows in set (0.00 sec)

mysql> delete from tutors where Age>80;
Query OK, 2 rows affected (0.13 sec)

增量备份

连接到MySQL数据库

mysql> flush logs;
Query OK, 0 rows affected (0.22 sec)

mysql-bin.000057
就是我们的增量备份

cp mysql-bin.000057 /root/ 即可

or

[root@localhost ~]# cd /mydata/data/
[root@localhost data]# ls
database  ib_logfile0  leo                        localhost.localdomain.pid  mysql             mysql-bin.000058  performance_schema  test
ibdata1   ib_logfile1  localhost.localdomain.err  mydb                       mysql-bin.000057  mysql-bin.index   setdents            yema
[root@localhost data]# cp mysql-bin.000057 /root/
[root@localhost data]# mysqlbinlog mysql-bin.000057  > zhouyi.sql

第二天操作

mysql> insert into tutors (Tname) value ('kang');
Query OK, 1 row affected (0.10 sec)

突然数据库挂掉数据丢失利用备份来还原

模拟操作

[root@localhost data]# cp mysql-bin.000058 /root/
[root@localhost data]# ls
database     ib_logfile1                localhost.localdomain.pid  mysql-bin.000057  performance_schema  yema
ibdata1      leo                        mydb                       mysql-bin.000058  setdents            zhouyi.sql
ib_logfile0  localhost.localdomain.err  mysql                      mysql-bin.index   test
[root@localhost data]# rm -rf *



[root@localhost data]# /etc/init.d/mysqld stop
MySQL server PID file could not be found!                  [FAILED]
[root@localhost data]# killall mysqld
初始化数据库
[root@localhost mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/mydata/data/
Installing MySQL system tables...
OK
Filling help tables...
OK
启动mysql
[root@localhost mysql]# /etc/init.d/mysqld start
Starting MySQL...                                          [  OK  ]
还原完全备份
[root@localhost ~]# mysql -uroot -p < /opt/alldata.sql 
Enter password:   默认空密码
[root@localhost ~]# mysql -uroot -p < /opt/zhouyi.sql  
 mysqlbinlog mysql-bin.000058 > kcw.sql
 mysql -uroot -p < kcw.sql
OR
 [root@localhost ~]# mysqlbinlog mysql-bin.000058 |mysql -uroot -p



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 |
|  10 | Tom          | F      |   30 |
|  11 | DingDian     | M      |   25 |
|  12 | HuFei        | M      |   31 |
|  13 | ZhangWuji    | M      |   20 |
|  14 | Xuzhu        | M      |   26 |
|  15 | kcw          | M      |   30 |
|  16 | kang         | M      | NULL |
+-----+--------------+--------+------+
16 rows in set (0.00 sec)



数据还原OK!及时点还原




© 著作权归作者所有

雁南飞丶
粉丝 37
博文 185
码字总数 246256
作品 0
西安
运维
私信 提问
命令行模式下备份、还原 MySQL 数据库的语句小结

为了安全起见,需要经常对数据库作备份,或者还原,学会在命令行模式下备份、还原数据库,还是很有必要 为了安全起见,需要经常对数据库作备份,或者还原。对于 MySQL 而言,最方便的方法可能...

Carl_
2015/02/02
110
0
MySQL管理工具

一、MySQL工具 二、mysqladmin工具 三、mysqldump工具(备份用) 一、MySQL工具 mysql是一个简单的命令行SQL工具,该工具支持交互式和非交互式运行 使用范例: 1、使用root账户连接服务器,默...

youshine
2015/07/14
0
0
Mysql数据库的备份及恢复参数详解

常见选项: --all-databases, -A: 备份所有数据库 --databases, -B: 用于备份多个数据库,如果没有该选项,mysqldump把第一个名字参数作为数据库名,后面的作为表名。使用该选项,mysqldu...

Myvtx
2018/07/18
0
0
Mysql数据库备份和还原常用的命令

备份MySQL数据库的命令 mysqldump -hhostname -uusername -ppassword databasename backupfile.sql 备份MySQL数据库为带删除表的格式 备份MySQL数据库为带删除表的格式,能够让该备份覆盖已有...

wilelm
2013/05/30
114
0
mysql数据库备份和还原常用的命令

备份MySQL数据库的命令 mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql 备份MySQL数据库为带删除表的格式 备份MySQL数据库为带删除表的格式,能够让该备份覆盖已...

Lofo
2014/12/11
78
0

没有更多内容

加载失败,请刷新页面

加载更多

CSS--列表

一、列表标识项 list-style-type none:去掉标识项 disc:默认实心圆 circle:空心圆 squire:矩形 二、列表项图片 list-style-img: 取值:url(路径) 三、列表项位置 list-style-position:...

wytao1995
今天
6
0
linux 命令-文本比较comm、diff、patch

本文原创首发于公众号:编程三分钟 今天学了三个文本比较的命令分享给大家。 comm comm 命令比较相同的文本 $ cat charabc$ cat chardiffadc 比如,我有两个文件char和chardiff如上,...

编程三分钟
今天
7
0
QML教程

https://blog.csdn.net/qq_40194498/article/category/7580030 https://blog.csdn.net/LaineGates/article/details/50887765...

shzwork
今天
5
0
HA Cluster之5

对于使用heartbeat v2版的CRM配置的集群信息都是保存在一个名为cib.xml的配置文件中,存放在/var/lib/heartbeat/crm/下。CIB:Cluster Information Base,由于xml文件配置不是那么方便,所以...

lhdzw
今天
6
0
玩转Redis-Redis基础数据结构及核心命令

  《玩转Redis》系列文章主要讲述Redis的基础及中高级应用,文章基于Redis5.0.4+。本文主要讲述Redis的数据结构String,《玩转Redis-Redis基础数据结构及核心命令》相关操作命令为方便对比...

zxiaofan666
今天
11
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部