详细讲解西软FOXHIS增量备份与恢复方法
<p>如何为西软数据做增量备份及恢复</p>
<p>
西软在实施阶段时，会设置好几个linux shell的自动任务，把数据每天全库备份两次，并且并把数据通过ftp拷至备份库，其实这样做存在非常大的安全隐患，数据库服务器如果给ko了，您酒店只有当天的两次备份，数据损失将是12个小时来计算，对酒店经营非常不利。如果通过sybase和中标的高可用集群配置将带来成本的高额上升，可能大部分酒店总经理都不会批准这个方案，前段时间做了一个方案，并在我们集团的某酒店数据库中实现了，过程非常简单，就看各位edp有没有心思去做。这样的做的好处是可以帮您把数据损失量控制在一个小时之内。</p>
<p>
提醒各位edp，这个方案不太适合服务器性能较低的酒店，差异备份虽然数据量不大，但是还会稍微影响生产数据库的io性能的。</p>
<p>
方案总体概述：(这个办法可以有效避免复杂的crontab重命名文件的操作，但是在写脚本的时候有点累赘)<br>
预备：准备工作设置</p>
<p>
1. 编写简单的linux shell文件，作用是调用sql脚本文件；<br>
2. 编写sql备份脚本文件；<br>
3. 设置linux crontab任务，让差异备份自己每小时进行；<br>
4. 通过windows 批处理文件，从linux ftp中把数据定时拉出来；<br>
5. 备份恢复。</p>
<p>
预备：设置sybase数据sp_dboption参数。</p>
<p>
1.进入命令行界面<br><br>
2.输入：sybase 密码：sybase<br><br>
3.输入：isql -usa 密码为空按回车<br><br>
4.输入：sp_dboption foxhis,trunc,false //关闭truncation，保证增量备份可以在database online的情况下使用。<br><br>
5.首先执行全库备份：<br>
dump database foxhis to 'xx/xx/xx/full_full.dat' 6点一次</p>
<p>
<strong>操作完以上工作后再进行下面的操作</strong></p>
<p>
<strong>一、编写简单的linux shell文件，作用是调用sql脚本文件</strong><br>
首先需要用sybase用户进入linux系统，在/home/sybase目录下建立一个您的脚本文件夹</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterbash" id="highlighter_463639">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="bash plain">-</code><code class="bash functions">bash</code><code class="bash plain">-3.2$ </code><code class="bash functions">mkdir</code> <code class="bash plain">hotelbackup </code><code class="bash plain">//</code><code class="bash plain">新建脚本文件夹 </code>
</div>
<div class="line number2 index1 alt1">
<code class="bash plain">-</code><code class="bash functions">bash</code><code class="bash plain">-3.2$ </code><code class="bash functions">cd</code> <code class="bash plain">hotelbackup </code><code class="bash plain">//</code><code class="bash plain">来到刚刚新建的脚本文件夹里 </code>
</div>
<div class="line number3 index2 alt2">
<code class="bash plain">-</code><code class="bash functions">bash</code><code class="bash plain">-3.2$ </code><code class="bash functions">vi</code> <code class="bash plain">00.sh </code><code class="bash plain">//</code><code class="bash plain">用</code><code class="bash functions">vi</code><code class="bash plain">新建一个空白的shell文件然后在</code><code class="bash functions">vi</code><code class="bash plain">的状态下，按一下字母“a”启动</code><code class="bash functions">vi</code><code class="bash plain">的编辑模式，然后输入： </code>
</div>
<div class="line number4 index3 alt1">
<code class="bash preprocessor bold">#!/bin/sh </code>
</div>
<div class="line number5 index4 alt2">
<code class="bash plain">/home/sybase/bin/</code><code class="bash plain">.</code><code class="bash plain">/isql</code> <code class="bash plain">-usa -p -i</code><code class="bash plain">/home/sybase/hotelbackup/00</code><code class="bash plain">.sql </code><code class="bash plain">//</code><code class="bash plain">不要直接写isql，一定要写全路径，避免isql启动失败！ </code>
</div>
<div class="line number6 index5 alt1">
<code class="bash plain">:wq </code><code class="bash plain">//</code><code class="bash plain">输入完成后，按下“esc”然后输入“:wq”是保存退出。</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
这样第一个shell脚本就编写完成，具体意思就是说：启动isql命令输入用户名和密码，并在isql状态下运行00.sql这个脚本的sql语句。</p>
<p>
<strong> 二、编写sql备份脚本文件；</strong></p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_519256">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">dump tran foxhis </code><code class="sql keyword">to</code> <code class="sql string">'/home/sybase/hotelbackupfile/00.log'</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql plain">go //把差异备份到以上目录</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
1. 我们的备份策略是每12小时做一次全库备份，每小时做一次差异备份。上面的语句是做差异备份，文件名“00”可以自定义，我这里的00就是0点的意思，各位酒店edp可以随心所欲地命名。</p>
<p>
2. 接下来我们设置全库备份语句：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_123733">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">dump </code><code class="sql keyword">database</code> <code class="sql plain">foxhis </code><code class="sql keyword">to</code> <code class="sql string">'home/sybase/hotelbackupfile/06.bak'</code>
</div>
<div class="line number2 index1 alt1">
<code class="sql plain">go //把全库备份拷到以上目录</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
3.一天又24个小时，为了少写一些crontab的语句，我们建议各位酒店的edp同事做24个sh文件和24个sql文件，这样保证不会有错误，并且会自动覆盖昨天的备份，基本起到全自动的备份目的，00.sh/00.sql、01.sh/01.sql .....23.sh/23.sql。也就是说，06和18的sql脚本就用第2点的语句，其它时候就用第1点的语句。把着一对对的文件放到hotelbackup文件后，我们继续第三大点crontab的设置。</p>
<p>
<strong>三、编写自动运行crontab自动运行脚本。</strong><br>
1. 首先用sybase用户登录，切忌不要用root。<br>
2. 然后输入以下语句：</p>
<p>
<span>-bash-3.2$ crontab -e </span></p>
<p>
//启动crontab编辑模式，编辑完成完成后按"esc"并输入":wq"保存退出</p>
<p>
3. 我们在后面添加如下语句：</p>
<p>
<img title="详细讲解西软FOXHIS增量备份与恢复方法" alt="详细讲解西软FOXHIS增量备份与恢复方法" src="https://zhuji.jb51.net/uploads/img/202305/728115061fb7dd638f6ed5976d854197.jpg"></p>
<p>
意思很明显每天的1点、2点.....6点30分......18点30分自动执行sh的命名，刚刚大家看到sh文件就是调用sql文件，所以备份当您设置完这个crontab后，按下”esc“再输入“wq”保存退出后，数据库就会自动开始帮您自动做增量备份了，每天都数据会自动自己覆盖，无需担心备份爆慢的情况出现。</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterplain" id="highlighter_173778">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
<div class="line number13 index12 alt2">
13</div>
<div class="line number14 index13 alt1">
14</div>
<div class="line number15 index14 alt2">
15</div>
<div class="line number16 index15 alt1">
16</div>
<div class="line number17 index16 alt2">
17</div>
<div class="line number18 index17 alt1">
18</div>
<div class="line number19 index18 alt2">
19</div>
<div class="line number20 index19 alt1">
20</div>
<div class="line number21 index20 alt2">
21</div>
<div class="line number22 index21 alt1">
22</div>
<div class="line number23 index22 alt2">
23</div>
<div class="line number24 index23 alt1">
24</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="plain plain">0 1 * * * sh /home/sybase/hotelbackup/01.sh </code>
</div>
<div class="line number2 index1 alt1">
<code class="plain plain">0 2 * * * sh /home/sybase/hotelbackup/02.sh </code>
</div>
<div class="line number3 index2 alt2">
<code class="plain plain">0 3 * * * sh /home/sybase/hotelbackup/03.sh </code>
</div>
<div class="line number4 index3 alt1">
<code class="plain plain">0 4 * * * sh /home/sybase/hotelbackup/04.sh </code>
</div>
<div class="line number5 index4 alt2">
<code class="plain plain">0 5 * * * sh /home/sybase/hotelbackup/05.sh </code>
</div>
<div class="line number6 index5 alt1">
<code class="plain plain">30 6 * * * sh /home/sybase/hotelbackup/06.sh </code>
</div>
<div class="line number7 index6 alt2">
<code class="plain plain">0 7 * * * sh /home/sybase/hotelbackup/07.sh </code>
</div>
<div class="line number8 index7 alt1">
<code class="plain plain">0 8 * * * sh /home/sybase/hotelbackup/08.sh </code>
</div>
<div class="line number9 index8 alt2">
<code class="plain plain">0 9 * * * sh /home/sybase/hotelbackup/09.sh </code>
</div>
<div class="line number10 index9 alt1">
<code class="plain plain">0 10 * * * sh /home/sybase/hotelbackup/10.sh </code>
</div>
<div class="line number11 index10 alt2">
<code class="plain plain">0 11 * * * sh /home/sybase/hotelbackup/11.sh </code>
</div>
<div class="line number12 index11 alt1">
<code class="plain plain">0 12 * * * sh /home/sybase/hotelbackup/12.sh </code>
</div>
<div class="line number13 index12 alt2">
<code class="plain plain">0 13 * * * sh /home/sybase/hotelbackup/13.sh </code>
</div>
<div class="line number14 index13 alt1">
<code class="plain plain">0 14 * * * sh /home/sybase/hotelbackup/14.sh </code>
</div>
<div class="line number15 index14 alt2">
<code class="plain plain">0 15 * * * sh /home/sybase/hotelbackup/15.sh </code>
</div>
<div class="line number16 index15 alt1">
<code class="plain plain">0 16 * * * sh /home/sybase/hotelbackup/16.sh </code>
</div>
<div class="line number17 index16 alt2">
<code class="plain plain">0 17 * * * sh /home/sybase/hotelbackup/17.sh </code>
</div>
<div class="line number18 index17 alt1">
<code class="plain plain">30 18 * * * sh /home/sybase/hotelbackup/18.sh </code>
</div>
<div class="line number19 index18 alt2">
<code class="plain plain">0 19 * * * sh /home/sybase/hotelbackup/19.sh </code>
</div>
<div class="line number20 index19 alt1">
<code class="plain plain">0 20 * * * sh /home/sybase/hotelbackup/20.sh </code>
</div>
<div class="line number21 index20 alt2">
<code class="plain plain">0 21 * * * sh /home/sybase/hotelbackup/21.sh </code>
</div>
<div class="line number22 index21 alt1">
<code class="plain plain">0 22 * * * sh /home/sybase/hotelbackup/22.sh </code>
</div>
<div class="line number23 index22 alt2">
<code class="plain plain">0 23 * * * sh /home/sybase/hotelbackup/23.sh </code>
</div>
<div class="line number24 index23 alt1">
<code class="plain plain">0 24 * * * sh /home/sybase/hotelbackup/00.sh</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
四、通过windows 批处理文件，从linux ftp中把数据定时拉出来；（待更新）<br>
五、 备份恢复。<br>
回复备份就非常简单，如果在数据在20点30分担时候挂掉了，也就是说我们损失了半个小时的数据，操作方法如下：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterplain" id="highlighter_132416">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="plain plain">load database from foxhis(databasename) 'home/sybase/hotelbackupfile/18.bak' </code>
</div>
<div class="line number2 index1 alt1">
<code class="plain plain">load tran from 'home/sybase/hotelbackupfile/19.log' </code>
</div>
<div class="line number3 index2 alt2">
<code class="plain plain">load tran from 'home/sybase/hotelbackupfile/20.log' </code>
</div>
<div class="line number4 index3 alt1">
<code class="plain plain">go </code>
</div>
<div class="line number5 index4 alt2">
<code class="plain plain">online database foxhis</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
只要这简单的几个语句就可以把数据恢复过来，非常简单。</p>
頁:
[1]