MySQL 备份与还原详解
发布时间：2022-03-29 11:16:12 所属栏目：MySql教程 来源：互联网
导读：注：系统版本 CentOS6.4 X86_64，MySQL版本 MySQL 5.5.32，相关软件下载 http://yunpan.cn/QnymShsCMzGg9 一、MySQL备份类型 1.热备份、温备份、冷备份 （根据服务器状态） 热备份：读、写不受影响； 温备份：仅可以执行读操作； 冷备份：离线备份；读、写
|
注：系统版本 CentOS6.4 X86_64，MySQL版本 MySQL 5.5.32，相关软件下载 http://yunpan.cn/QnymShsCMzGg9 一、MySQL备份类型 1.热备份、温备份、冷备份 （根据服务器状态） · 热备份：读、写不受影响； · 温备份：仅可以执行读操作； · 冷备份：离线备份；读、写操作均中止； 2.物理备份与逻辑备份 （从对象来分） · 物理备份：复制数据文件； · 逻辑备份：将数据导出至文本文件中； 3.完全备份、增量备份、差异备份 （从数据收集来分） · 完全备份：备份全部数据； · 增量备份：仅备份上次完全备份或增量备份以后变化的数据； · 差异备份：仅备份上次完全备份以来变化的数据； 4.逻辑备份的优点： · 在备份速度上两种备份要取决于不同的存储引擎 · 物理备份的还原速度非常快。但是物理备份的最小力度只能做到表 · 逻辑备份保存的结构通常都是纯ASCII的，所以我们可以使用文本处理工具来处理 · 逻辑备份有非常强的兼容性，而物理备份则对版本要求非常高 · 逻辑备份也对保持数据的安全性有保证 5.逻辑备份的缺点： · 逻辑备份要对RDBMS产生额外的压力，而裸备份无压力 · 逻辑备份的结果可能要比源文件更大。所以很多人都对备份的内容进行压缩 · 逻辑备份可能会丢失浮点数的精度信息 6.增量备份与差异备份区别 说明，差异备份要比增量备份占用的空间大，但恢复时比较方便！但我们一般都用增量备份！ 二、MySQL备份都备份什么？ 我们备份，一般备份以下几个部分： 1.数据文件 2.日志文件（比如事务日志，二进制日志） 3.存储过程，存储函数，触发器 4.配置文件（十分重要，各个配置文件都要备份） 5.用于实现数据库备份的脚本，数据库自身清理的Croutab等…… 三、MySQL备份工具 上面的所有备份工具对比，下面我们就来说一下，常用的备份工具， 1.Mysql自带的备份工具 · mysqldump 逻辑备份工具，支持所有引擎，MyISAM引擎是温备，InnoDB引擎是热备，备份速度中速，还原速度非常非常慢，但是在实现还原的时候，具有很大的操作余地。具有很好的弹性。 · mysqlhotcopy 物理备份工具，但只支持MyISAM引擎，基本上属于冷备的范畴，物理备份，速度比较快。 2.文件系统备份工具 · cp 冷备份，支持所有引擎，复制命令，只能实现冷备，物理备份。使用归档工具，cp命令，对其进行备份的，备份速度快，还原速度几乎最快，但是灵活度很低，可以跨系统，但是跨平台能力很差。 · lvm 几乎是热备份，支持所有引擎，基于快照(LVM，ZFS)的物理备份，速度非常快，几乎是热备。只影响数据几秒钟而已。但是创建快照的过程本身就影响到了数据库在线的使用，所以备份速度比较快，恢复速度比较快，没有什么弹性空间，而且LVM的限制：不能对多个逻辑卷同一时间进行备份，所以数据文件和事务日志等各种文件必须放在同一个LVM上。而ZFS则非常好的可以在多逻辑卷之间备份。 3.其它工具 · ibbackup 商业工具 MyISAM是温备份，InnoDB是热备份 ，备份和还原速度都很快，这个软件它的每服务器授权版本是5000美元。 · xtrabackup 开源工具 MyISAM是温备份，InnoDB是热备份 ，是ibbackup商业工具的替代工具。 四、MySQL备份策略 1.策略一：直接拷贝数据库文件（文件系统备份工具 cp）（适合小型数据库，是最可靠的） 当你使用直接备份方法时，必须保证表不在被使用。如果服务器在你正在拷贝一个表时改变它，拷贝就失去意义。保证你的拷贝完整性的最好方法是关闭服务器，拷贝文件，然后重启服务器。如果你不想关闭服务器，要在执行表检查的同时锁定服务器。如果服务器在运行，相同的制约也适用于拷贝文件，而且你应该使用相同的锁定协议让服务器“安静下来”。当你完成了备份时，需要重启服务器(如果关闭了它)或释放加在表上的锁定(如果你让服务器运行)。要用直接拷贝文件把一个数据库从一台机器拷贝到另一台机器上，只是将文件拷贝到另一台服务器主机的适当数据目录下即可。要确保文件是MyIASM格式或两台机器有相同的硬件结构，否则你的数据库在另一台主机上有奇怪的内容。你也应该保证在另一台机器上的服务器在你正在安装数据库表时不访问它们。 2.策略二：mysqldump备份数据库（完全备份+增加备份，速度相对较慢，适合中小型数据库）（MyISAM是温备份，InnoDB是热备份） mysqldump 是采用SQL级别的备份机制，它将数据表导成 SQL 脚本文件，在不同的 MySQL 版本之间升级时相对比较合适，这也是最常用的备份方法。mysqldump 比直接拷贝要慢些。对于中等级别业务量的系统来说，备份策略可以这么定：第一次完全备份，每天一次增量备份，每周再做一次完全备份，如此一直重复。而对于重要的且繁忙的系统来说，则可能需要每天一次全量备份，每小时一次增量备份，甚至更频繁。为了不影响线上业务，实现在线备份，并且能增量备份，最好的办法就是采用主从复制机制(replication)，在 slave 机器上做备份。 3.策略三：lvs快照从物理角度实现几乎热备的完全备份，配合二进制日志备份实现增量备份，速度快适合比较烦忙的数据库 前提： · 数据文件要在逻辑卷上； · 此逻辑卷所在卷组必须有足够空间使用快照卷； · 数据文件和事务日志要在同一个逻辑卷上； 步骤： (1).打开会话，施加读锁，锁定所有表； 2 mysql> FLUSH TABLES WITH READ LOCK; mysql> FLUSH LOGS; mysql -uroot -p -e 'SHOW MASTER STATUSG' > /path/to/master.info (3).创建快照卷 lvcreate -L # -s -p r -n LV_NAME /path/to/source_lv (4).释放锁 mysql> UNLOCK TABLES; (5).挂载快照卷，备份 mount cp (6).删除快照卷； (7).增量备份二进制日志； 4.策略四：xtrabackup 备份数据库，实现完全热备份与增量热备份（MyISAM是温备份，InnoDB是热备份），由于有的数据在设计之初，数据目录没有存放在LVM上，所以不能用LVM作备份，则用xtrabackup代替来备份数据库 说明：Xtrabackup是一个对InnoDB做数据备份的工具，支持在线热备份(备份时不影响数据读写)，是商业备份工具InnoDB Hotbackup或ibbackup的一个很好的替代品。 Xtrabackup有两个主要的工具：xtrabackup、innobackupex · xtrabackup 只能备份InnoDB和XtraDB两种数据表，而不能备份MyISAM数据表。 · innobackupex 是参考了InnoDB Hotbackup的innoback脚本修改而来的.innobackupex是一个perl脚本封装，封装了xtrabackup。主要是为了方便的 同时备份InnoDB和MyISAM引擎的表，但在处理myisam时需要加一个读锁。并且加入了一些使用的选项。如slave-info可以记录备份恢复后作为slave需要的一些信息，根据这些信息，可以很方便的利用备份来重做slave。 特点： · 备份过程快速、可靠； · 备份过程不会打断正在执行的事务； · 能够基于压缩等功能节约磁盘空间和流量； · 自动实现备份检验； · 还原速度快； 5.策略五：主从复制（replication）实现数据库实时备份（集群中常用） 6.总结 单机备份是完全备份（所有数据库文件）+增量备份（备份二进制日志）相结合！ 集群中备份是完全备份（所有数据库文件）+增量备份（备份二进制日志）+主从复制（replication）相结合的方法！ 五、备份准备工作 1.查看服务器状态， mysql> s -------------- mysql Ver 14.14 Distrib 5.5.32, for Linux (x86_64) using readline 5.1 Connection id: 1 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.5.32-log Source distribution Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /tmp/mysql.sock Uptime: 2 min 0 sec Threads: 1 Questions: 4 Slow queries: 0 Opens: 33 Flush tables: 1 Open tables: 26 Queries per second avg: 0.033 2.查看数据目录存放位置 mysql> show variables like '%datadir%'; +---------------+---------------+ | Variable_name | Value | +---------------+---------------+ | datadir | /mydata/data/ | +---------------+---------------+ 1 row in set (0.01 sec) 3.修改二进制日志的存放位置 (1).建立一目录用于存放二进制日志 [root@mysql ~]# mkdir /mybinlog [root@mysql ~]# chown mysql:mysql /mybinlog [root@mysql /]# ll | grep mybinlog drwxr-xr-x 2 mysql mysql 4096 7月 22 14:39 mybinlog (2).修改my.cnf [root@mysql ~]# vim /etc/my.cnf log-bin=/mybinlog/mysql-bin #二进制日志目录及文件名前缀 innodb_file_per_table = 1 #启用InnoDB表每表一文件，默认所有库使用一个表空间 (3).重新启动mysqld [root@mysql ~]# service mysqld restart 4.查看新生成的binlog日志 [root@mysql ~]# ls /mybinlog/ mysql-bin.000001 mysql-bin.index 5.准备一个test库，里面有两张表，t1表和t2表！ mysql> show table status from testG #查看两张表的状态 *************************** 1. row *************************** Name: t1 Engine: MyISAM Version: 10 Row_format: Fixed Rows: 167772160 Avg_row_length: 7 Data_length: 1174405120 Max_data_length: 1970324836974591 Index_length: 1024 Data_free: 0 Auto_increment: NULL Create_time: 2013-07-21 19:37:44 Update_time: 2013-07-21 19:52:48 Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: *************************** 2. row *************************** Name: t2 Engine: InnoDB Version: 10 Row_format: Compact Rows: 20971797 Avg_row_length: 31 Data_length: 667942912 Max_data_length: 0 Index_length: 0 Data_free: 4194304 Auto_increment: NULL Create_time: 2013-07-21 20:00:29 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 2 rows in set (0.01 sec) 第一张t1表，使用的是MyISAM引擎，其中有1亿多行数据，第二张t2表,使用的是INNODB引擎，其中有2千多万行数据！有博友会问了，你是在做测试怎么会有这么多数据的，下面我就的大家说一下，快速插入1亿条数据的方法！具体操作如下， t1表， mysql>use test; #使用 test数据库 mysql>create table t1 ( #创建一个简单的t1表，里面只有一个字段 id id int(10) default null )engine=myisam default charset=utf8; mysql> show create table t1; #查看创建的表 mysql>insert into t1 values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10); #先插入十个数据 mysql>insert into t1 select * from t1; #重复多次便能插入1亿条数据 mysql>select count(*) from t1; #查看插入数据的总数 t2表， mysql>create table t2 ( id int(10) default null （编辑：包头站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐