分类目录归档：数据库

mysql数据管理

发表于 2010 年 7 月 31 日由惜分飞

本人的兴趣一直都是oracle数据库的管理，最近公司上电子商务系统，现在还处于测试状态，不出意外应该没有大问题，因为程序是 php+mysql的，这就涉及到程序的维护、数据库的维护和管理。虽然mysql数据库在一般情况下，直接复制数据库下的data文件夹下面的数据库名的文件夹可以备份

如上图，这里我选中的每一文件夹其实是mysql数据库的一个数据库，只要直接复制就可以备份相应的数据库了，但是考虑到系统实时性可能比较高，如果这样备份，肯定会导致数据的丢失，还有就是因为系统里面的数据库有60多张表，一张表又有三个文件，如果数据量大的时候，备份起来时间比较长

所有决定写一个脚本使用命令mysqldump+windows系统的任务自动定时执行备份，并实现为了方便转移，打包压缩功能

@echo off
echo 批发网中文版和英文版数据库备份
echo.
echo 程序启动时间：%date% %time%
echo.
set "Name=%date:~,4%%date:~5,2%%date:~8,2%_%random%"
set "Path=e:\MySql_BackUp\%Name%"
md %Path%
echo 保存备份数据库文件路径为：%Path%
echo ***********************************************************
echo.
echo %time%：开始备份db_glasses_en数据库……
echo.
e:\AppServ\MySQL\bin\mysqldump --opt -u root --password=4020894 -F -B --default-character-set=utf8 db_glasses_en_bak > %Path%\db_glasses_en_bak.sql
echo 文件：%Path%\db_glasses_en_bak.sql
echo %time%：db_glasses_en数据库备份完成
echo.
echo ***********************************************************
echo.
echo %time%：开始备份db_glasses_cn数据库……
echo.
e:\AppServ\MySQL\bin\mysqldump --opt -u root --password=4020894 -F -B --default-character-set=utf8 db_glasses_cn > %Path%\db_glasses_cn_bak.sql
echo 文件：%Path%\db_glasses_cn_bak.sql
echo %time%：db_glasses_cn数据库备份完成
echo.
echo ***********************************************************
echo.
echo 批发网中英文数据库备份完成……
echo.
echo ***********************************************************
echo.
echo %time%：备份数据库进行压缩……
cd C:\Program Files\WinRAR
WinRAR.exe a -m5 e:\MySql_BackUp\%Name%.rar %Path%
echo.
echo %time%:e:\MySql_BackUp\%Name%.rar压缩完成
echo 请检查文件并做好数据转移……
echo.
echo 程序结束时间：%date% %time%
echo.
echo ***********************************************************
pause

通过上面的脚本实现数据库的备份，结果如下图：

上图即为执行备份后的结果，至于mysql的备份命令mysqldump的相关参数请见mysql的相关文档。

补充说明：

在 mssql里面日志是自动启动的；在oracle归档日志默认情况下是关闭的，可以在mount模式下，使用alter database archivelog开启重做日志归档模式；在mysql中，日志默认情况下也是关闭的，但是它是通过修改my.ini配置文件实现修改的，具体参数如下：

log-bin=F:\AppServ\MySQL\log\log_bin.log
log=F:\AppServ\MySQL\log\log.log
log-update= F:\AppServ\MySQL\log\update.log
log-slow-queries=F:\AppServ\MySQL\log\mysql-slow.log
log-error=F:\AppServ\MySQL\log\error.log

各日志参数说明：

错误日志：   -log-err
查询日志：   -log
慢查询日志:     -log-slow-queries
更新日志:     -log-update
二进制日志：   -log-bin

注：如果日志参数后面直接跟文件名，那日志文件会放置在mysql/data文件夹下

发表在 MySQL | 评论关闭

服务器遭攻击，oracle分析日志

发表于 2010 年 7 月 3 日由惜分飞

今天公司服务器出现问题，经过启动日志功能，分析日志，大概确定是dos攻击

部分日志如下：

2010-07-02 10:55:22 W3SVC689347672 192.168.10.222 GET /index.shtml -  80 - 121.204.33.120  Mozilla/4.0(compatible;+MSIE+7.0;+Windows+NT+5.1;+SV1) 200 0 64
2010-07-02  10:55:22 W3SVC689347672 192.168.10.222 GET /index.shtml - 80 -  114.41.216.107 Mozilla/4.0(compatible;+MSIE+7.0;+Windows+NT+5.1;+SV1)  200 0 64
2010-07-02 10:55:22 W3SVC689347672 192.168.10.222 GET  /index.shtml - 80 - 221.15.37.5  Mozilla/4.0(compatible;+MSIE+7.0;+Windows+NT+5.1;+SV1) 200 0 64
2010-07-02  10:55:22 W3SVC689347672 192.168.10.222 GET /index.shtml - 80 -  114.41.216.107 Mozilla/4.0(compatible;+MSIE+7.0;+Windows+NT+5.1;+SV1)  200 0 64
2010-07-02 10:55:22 W3SVC689347672 192.168.10.222 GET  /index.shtml - 80 - 114.41.216.107  Mozilla/4.0(compatible;+MSIE+7.0;+Windows+NT+5.1;+SV1) 200 0 64
2010-07-02  10:55:22 W3SVC689347672 192.168.10.222 GET /index.shtml - 80 -  114.41.216.107 Mozilla/4.0(compatible;+MSIE+7.0;+Windows+NT+5.1;+SV1)  200 0 64
2010-07-02 10:55:22 W3SVC689347672 192.168.10.222 GET  /index.shtml - 80 - 114.41.216.107  Mozilla/4.0(compatible;+MSIE+7.0;+Windows+NT+5.1;+SV1) 200 0 64
2010-07-02  10:55:22 W3SVC689347672 192.168.10.222 GET /index.shtml - 80 -  222.81.14.4 Mozilla/4.0(compatible;+MSIE+7.0;+Windows+NT+5.1;+SV1) 200 0  64
2010-07-02 10:55:22 W3SVC689347672 192.168.10.222 GET  /index.shtml - 80 - 180.126.191.98  Mozilla/4.0(compatible;+MSIE+7.0;+Windows+NT+5.1;+SV1) 200 0 64
2010-07-02  10:55:22 W3SVC689347672 192.168.10.222 GET /index.shtml - 80 -  117.15.210.226 Mozilla/4.0(compatible;+MSIE+7.0;+Windows+NT+5.1;+SV1)  200 0 64
2010-07-02 10:55:22 W3SVC689347672 192.168.10.222 GET  /index.shtml - 80 - 121.204.33.120  Mozilla/4.0(compatible;+MSIE+7.0;+Windows+NT+5.1;+SV1) 200 0 64
2010-07-02  10:55:22 W3SVC689347672 192.168.10.222 GET /index.shtml - 80 -  112.122.131.60 Mozilla/4.0(compatible;+MSIE+7.0;+Windows+NT+5.1;+SV1)  200 0 64
2010-07-02 10:55:22 W3SVC689347672 192.168.10.222 GET  /index.shtml - 80 - 118.77.181.47  Mozilla/4.0(compatible;+MSIE+7.0;+Windows+NT+5.1;+SV1) 200 0 64

日志文件实在太多了，最后几分钟都就有20m左右，我们肉眼看上去，没有什么规律可寻的，我决定采用oracle数据帮我们分析

建表如下：

get varchar2（3000）–保存日志中的一条记录

ip varchar2(15)–保存日志中请求服务器的ip地址（分析的重点）

gettime varchar2(20) –保存时间（本来有date类型的，不知道为什么，和后面的proc中的程序有的冲突，我也不想改存储过程了，就先改了这个，有时间再想想程序）

第一步：通过net把.log 的日志文件中的数据导入到db的dos_gj中的get列中，程序如下：

string txt = Show_page("log.log");
string[] aba = txt.Replace("\r\n", "@").Split('@');
for (int i = 0; i < aba.Length; i++)
{
OracleHelper.ExecuteNonQuery(OracleHelper.
ConnectionStringLocalTransaction,
System.Data.CommandType.Text,
"insert into dos_gj(get) values (:abc)", new OracleParameter("abc", aba[i]));
}

//读取文件的 Show_page（）函数就不放出来了

大概半个多小时，20m文件的数据，全部分条导入到数据中，统计，一共近20w条

第二步：使用proc对这些数据进行分析：

proc如下 :

create or replace
procedure dos_fx
is
cursor c1 is
select get from dos_gj;
begin
for c2 in c1 loop
update dos_gj set
ip=REGEXP_SUBSTR( get, '(\d{1,2}|1\d\d|2[0-4]\d|25[0-5])(\.(\d{1,2}|1\d\d|2[0-4]\d|25[0-5])){3} '),
gettime=REGEXP_SUBSTR( get, '\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}') where get=c2.get;
end loop;
commit;
end;
exec  dos_fx;

不知道什么原因，执行了快两个小时了，现在还是在执行中，我现在怀疑在oracle中cursor +正则表达式+update，执行效率正的很低啊，才20w条数据，执行了2个小时，还没有结果，郁闷，在等待中……

这些对oracle有点无语了，害的我现在还不能睡觉，等在电脑面前，早知道就用ms sql了，听说 2005也支持正则功能了。继续等待（2010年7月3日2:04:58），这个效率，这个速度有点无语哦，听说oracle速度很快，我还得好好学习哦。等了两个多小时就是学艺不精的后果。

最后想想这个update table的思路不行，新建了一张，使用了新的存储过程（insert）

create or replace procedure dos_fx
is
cursor c1 is
select get from dos_gj;
begin
for c2 in c1 loop

insert into dos_gj_1(ip,gettime,get)values(REGEXP_SUBSTR( c2.get,  '(\d{1,2}|1\d\d|2[0-4]\d|25[0-5])(\.(\d{1,2}|1\d\d|2[0-4]\d|25[0-5])){3}  '),
REGEXP_SUBSTR( c2.get, '^(\d{4})-(\d{2})-(\d{2}) (\d{2}:\d{2}:\d{2})'),c2.get);
end loop;
commit;
end;

同上表，然后执行insert操作，考虑到sql dev占用内存太大，这次使用了pl/sql dev操作，结果使用了1分钟37秒就完成了近20w的数据的操作，这才是oracle的本色啊，O(∩_∩)O哈哈~。

现在想想，应该是刚刚前面的update那种操作导致了数据库死锁导致无限的等待。

好了，数据从log文件，到数据库里面，已经数据提取成功，剩下的就是相关统计的oracle了

统计ip攻击情况

不到一个小时的攻击的结果大概如上图，任务大概完成，先睡觉去，明天继续分析

发表在 Oracle | 评论关闭

通过sql server 数据库中的sql语句实现项目需求

发表于 2010 年 6 月 13 日由惜分飞

最近都关注oracle，今天没有办法，因为项目使用的是sql server数据库，许多东西都忘记的差不多了。需要实现一个功能，本来可以使用程序去实现，但是我不爽，一定想通过数据库本身来解决这个问题。问题大概是：

项目中是要实现在别人寻找到宝贝后，要回答一个问题，这个问题暂时定为有四个选项，其中一个正确的，在数据库中使用两张表来实现，一张是记录问题的题目和答案（表一），另一张表专门用来记录每个题目中的选项（表二），表的sql脚本如下：

表一：

USE [xb_new]
GO
/****** 对象:  Table [dbo].[Question]    脚本日期: 06/13/2010 21:17:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Question](
[id] [int] IDENTITY(1,1) NOT NULL,
[iss_id] [int] NULL,
[answer] [nvarchar](50) NULL,
[question] [nvarchar](150) NULL,
CONSTRAINT [PK_Question_] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH  (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,  ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

表中数据如下：

表二：

USE [xb_new]
GO
/****** 对象:  Table [dbo].[Qusetion_Answer]    脚本日期: 06/13/2010 21:18:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Qusetion_Answer](
[id] [int] IDENTITY(1,1) NOT NULL,
[qid] [int] NOT NULL,
[qtext] [nvarchar](50) NOT NULL,
[qvalue] [nvarchar](2) NULL
) ON [PRIMARY]

表中数据如下

现在需求是：把一个题目的四个选项合并到一起，并为了在网页上换行，显示题目，显示答案。

其中难道是行列转换，以前在orcal中都是使用decode来实现的，现在郁闷的sql server中竟然不支持这个函数，只能我在oracle中比较讨厌的case（主要要多写很多when、then、end我不喜欢，而且还容易忘记格式）来实现。实现sql脚本如下：

SELECT a,q,aa+'<br>'+bb+'<br>'+cc+'<br>'+dd result FROM
(SELECT DISTINCT answer a,Question.Question q,
aa=MAX(CASE qvalue WHEN 'A' THEN '(A)'+qtext END),
bb=max(CASE qvalue WHEN 'B' THEN '(B)'+qtext END),
cc=max(CASE qvalue WHEN 'C' THEN '(C)'+qtext END),
dd=max(CASE qvalue WHEN 'D' THEN '(D)'+qtext END)
FROM dbo.Question,dbo.Qusetion_Answer
WHERE qid=dbo.Question.id GROUP BY dbo.Question.answer,Question.Question) temp;

结果如下：

发表在 SQL Server | 评论关闭

17813235971

QQ 咨询

分类目录归档：数据库

mysql数据管理

服务器遭攻击，oracle分析日志

通过sql server 数据库中的sql语句实现项目需求

文章分类

最近发表

分类目录归档：数据库

mysql数据管理

服务器遭攻击，oracle分析日志

通过sql server 数据库中的sql语句实现项目需求

标签云

文章分类

最近发表