作者 主題: 請問有否方法可以對於每個DB 進行大小容量設定限制(即Quota)嗎 ?  (閱讀 11036 次)

0 會員 與 1 訪客 正在閱讀本文。

edwardleung

  • 俺是博士!
  • *****
  • 文章數: 1135
    • 檢視個人資料
各位大大您們好,

如題,可以協助一下嗎?
系統是Linux FC6 的預設 DB Server...

感謝!

micmic3

  • 俺是博士!
  • *****
  • 文章數: 1692
    • 檢視個人資料
在建立 table 的時候設吧

edwardleung

  • 俺是博士!
  • *****
  • 文章數: 1135
    • 檢視個人資料
您好,

不好意思, 請問是否在MySQL 建立Database 時設定呢?
如何設定才對呢 ? 有否圖示可以參考一下嗎 ?

謝謝!

micmic3

  • 俺是博士!
  • *****
  • 文章數: 1692
    • 檢視個人資料
就是在建 table 時設好每個欄位大小

不然就用下面的方法

http://blog.wu-boy.com/2009/10/09/1716/

edwardleung

  • 俺是博士!
  • *****
  • 文章數: 1135
    • 檢視個人資料
http://blog.wu-boy.com/2009/10/09/1716/

請問這個方式是否針對MYSQL 內每個 Database 都同時進行 Scan 呢 ?

wenlien

  • 憂鬱的高中生
  • ***
  • 文章數: 119
  • 性別: 男
    • 檢視個人資料
    • Open or not open, that is the stupid question.
就是在建 table 時設好每個欄位大小

不然就用下面的方法

http://blog.wu-boy.com/2009/10/09/1716/


這個方法讚~
但是,如果DB換帳號密碼,那php裡面的設定就要一定改~
所以,可以把邏輯寫成Stored Procedure,然後用MySQL的event來執行。
上述的問題就可以解決!

另一個問題時,這樣的狀況,會有lag。(端看schedule設定的時間長短而定)
再來就是permission何時生效的問題了~
http://dev.mysql.com/doc/refman/5.1/en/privilege-changes.html
如果,如果是放進mysql.db的設定,要等到下次user 使用'use db_name'(或是重新連線)的時候,
權限才會生效。

(我猜,並沒有試過,有興趣的人可以試試看,再請跟大家分享)
我猜,以下的方式(延續之前quota table的概念),
應該有機會可以解決上述的問題(先不論performance impact  :P):
1. create stored procedure for check quota for check quota, flush privillege, if check fail, also raise error.
2. create before trigger for table.
3. call stored_procedure in trigger.
regards,

Stanley Huang


edwardleung

  • 俺是博士!
  • *****
  • 文章數: 1135
    • 檢視個人資料
引用
但是,如果DB換帳號密碼,那php裡面的設定就要一定改~
所以,可以把邏輯寫成Stored Procedure,然後用MySQL的event來執行。
上述的問題就可以解決!

1, 請問php 那部份需要改 ?
2, 如何可以寫成Stored Procedure ?

謝謝!

bunko

  • 懷疑的國中生
  • **
  • 文章數: 67
    • 檢視個人資料
MySQL Database size limit
======================================
1. 基本的檔案大小計算
in mysample
# pwd
/var/lib/mysql/mysample
# ls -l *.MYD | awk '{sum += $5} END {print sum}'
37750
# ls -l *.MYI | awk '{sum += $5} END {print sum}'
38912

所以我們計算得到76662 bytes.
注意:這個數字不包含 *.frm (Table Cache), *.TRN (Trigger), *.TRG (Function)等各式檔案.
僅是MyISAM的 data/index 的檔案.

然後我們寫了以下的php 程式:
----------------------------------------------------------------
代碼: (php) [選擇]
#!/usr/bin/php -q
<?php
mysql_connect
("localhost","mysample","mysample");

$result mysql_query("SHOW TABLE STATUS FROM mysample;");

$sum 0;

while(
$array mysql_fetch_array($result)) {
$total $array[&#39;Data_length&#39;] + $array[&#39;Index_length&#39;];
   
$sum += $total;
print "--------------------------\n";
printf("Table:%s\n"$array[&#39;Name&#39;]);
printf("Data Size:%d\n"$array[&#39;Data_length&#39;]);
printf("Index Size:%d\n"$array[&#39;Index_length&#39;]);
printf("Total Size:%d\n"$total);
printf("Total Rows:%d\n"$array[&#39;Rows&#39;]);
printf("Engine:%s\n"$array[&#39;Engine&#39;]);
printf("Avg Size Per Row:%d\n"$array[&#39;Avg_row_length&#39;]);
print "--------------------------\n";
}

print 
"*********************\n";
printf("The Sum of all Tables:%d bytes\n"$sum);
printf("The Size of database in K bytes:%f\n"$sum/1024);
?>
----------------------------------------------------------------
計算結果
The Sum of all Tables:93046 bytes
The Size of database in K nytes:90.865234

因為在這個mysample還有table是Engine:InnoDB,因為InnoDB是Table Space的方式,與MyISAM分別用檔案的方式不同.
我們修改一下上面的程式.只計算Engine:MyISAM的Table與Index的總和.

-------------------------------------------------------------------
代碼: (php) [選擇]
#!/usr/bin/php -q
<?php
# MySQL database size summary version 2
# Only count MyISAM tables

mysql_connect("localhost","mysample","mysample");

$result mysql_query("SHOW TABLE STATUS FROM mysample WHERE Engine=&#39;MyISAM&#39;;");

$sum 0;

while(
$array mysql_fetch_array($result)) {
$total $array[&#39;Data_length&#39;] + $array[&#39;Index_length&#39;];
$sum += $total;
  
print "--------------------------\n";
printf("Table:%s\n"$array[&#39;Name&#39;]);
printf("Data Size:%d\n"$array[&#39;Data_length&#39;]);
printf("Index Size:%d\n"$array[&#39;Index_length&#39;]);
printf("Total Size:%d\n"$total);
printf("Total Rows:%d\n"$array[&#39;Rows&#39;]);
printf("Engine:%s\n"$array[&#39;Engine&#39;]);
printf("Avg Size Per Row:%d\n"$array[&#39;Avg_row_length&#39;]);
print "--------------------------\n";
}

print 
"*********************\n";
printf("The Sum of all Tables:%d bytes\n"$sum);
printf("The Size of database in K bytes:%f\n"$sum/1024);
?>
------------------------------------------------------------------
計算結果
The Sum of all Tables:76662 bytes
The Size of database in K bytes:74.865234

與上面計算檔案的方式結果相同.

小結:因為InnoDB的方式不同,我們就針對MyISAM的data file,index file計算.可以使用
SHOW TABLE STATUS FROM mysample WHERE Engine='MyISAM' 的語法.

*************************************************************************************
2. 使用function來計算

剛才我們是使用mysql client裡面的 show table status 來計算,但是要使用function的話,要用
cursor,不能使用show table status.但是不用擔心,在 information_schema.tables 裡面有需要的資訊.

將mysample各個MyISAM的Table列出table_name,data_length,index_length

select table_name,data_length,index_length
from information_schema.tables
where table_schema = 'mysample'
and table_type = 'BASE TABLE'
and engine = 'MyISAM';


計算總和:

select sum(data_length)+sum(index_length) as totsize
from information_schema.tables
where table_schema = 'mysample'
and table_type = 'BASE TABLE'
and engine = 'MyISAM';

執行結果:
+---------+
| totsize |
+---------+
|   76662 |
+---------+

跟上面的資料相符.

接下來就寫一個 dbszie function.
----------------------------------------------------------
DELIMITER $$

DROP FUNCTION IF EXISTS `mysample`.`dbsize`$$
CREATE FUNCTION `mysample`.`dbsize` (in_dbname varchar(64)) RETURNS BIGINT UNSIGNED
BEGIN
DECLARE rtnSize BIGINT UNSIGNED;
DECLARE c CURSOR FOR select sum(data_length)+sum(index_length) as totsize
   from information_schema.tables
   where table_schema = in_dbname
   and table_type = 'BASE TABLE'
   and engine = 'MyISAM';

open c;
fetch c into rtnSize;
close c;

RETURN rtnSize;

END$$

DELIMITER ;
-----------------------------------------------------------

我們測試一下

mysql> select dbsize('mysample');
+--------------------+
| dbsize('mysample') |
+--------------------+
|              76662 |
+--------------------+
1 row in set (0.07 sec)

mysql> select dbsize('test');
+----------------+
| dbsize('test') |
+----------------+
|           2200 |
+----------------+
1 row in set (0.00 sec)

可以看到可以依照輸入的database name計算出使用的size.
**************************************************************************
未完...待續  :)
« 上次編輯: 2010-08-07 11:08 由 bunko »

wenlien

  • 憂鬱的高中生
  • ***
  • 文章數: 119
  • 性別: 男
    • 檢視個人資料
    • Open or not open, that is the stupid question.
引用
但是,如果DB換帳號密碼,那php裡面的設定就要一定改~
所以,可以把邏輯寫成Stored Procedure,然後用MySQL的event來執行。
上述的問題就可以解決!

1, 請問php 那部份需要改 ?
2, 如何可以寫成Stored Procedure ?

謝謝!

1. change here:
代碼: [選擇]
/*
 * Settings
 */
 
$mysql_host  = 'localhost';
$mysql_user  = 'root'; // Do NOT change, root-access is required
$mysql_pass  = '';
$mysql_db    = 'quotadb'; // Not the DB to check, but the db with the quota table
$mysql_table = 'quota';

2.  第二問題,恕小弟看不懂你的問題,
     a. 邏輯的部份,請參閱php source code.
     b. 建立Stored Procedure的語法, 請參閱MySQL Manual.
http://dev.mysql.com/doc/refman/5.1/en/stored-programs-views.html
regards,

Stanley Huang

bunko

  • 懷疑的國中生
  • **
  • 文章數: 67
    • 檢視個人資料
3. MySQL metadata 的存放空間 information_schema 在database size上的應用

user:mysample 有權限的database有三個,其中information_schema是系統metadata的存放空間,
我們就可以存取他來獲得一些資訊.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysample           |
| test               |
+--------------------+

我們可以執行下面的指令:
select table_schema,sum(data_length),sum(index_length),sum(data_length)+sum(index_length) as totsize
from information_schema.tables
where table_type='BASE TABLE'
and engine='MyISAM'
group by table_schema;
+--------------+------------------+-------------------+---------+
| table_schema | sum(data_length) | sum(index_length) | totsize |
+--------------+------------------+-------------------+---------+
| mysample     |            37750 |             38912 |   76662 |
| test         |              152 |              2048 |    2200 |
+--------------+------------------+-------------------+---------+
2 rows in set (0.01 sec)

結果均相符,因為來源都一樣. 這裡可以看到MySQL方便的地方,我們不需要將information_schema排除.
到目前都是以user:mysample來作例子.當我們要以整個instance為範圍來管理就需要用root權限登入了.
以root的視野來看:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| auth               |
| bookmarks          |
| books              |
| impexp_text        |
| joomla             |
| joomla2            |
| mail               |
| mysample           |
| mysql              |
| pandora            |
| pureftpd           |
| simple_blog        |
| test               |
| test2              |
+--------------------+

mysql> select table_schema,sum(data_length),sum(index_length),sum(data_length)+sum(index_length) as totsize
    -> from information_schema.tables
    -> where table_type='BASE TABLE'
    -> and engine='MyISAM'
    -> group by table_schema;
+--------------+------------------+-------------------+---------+
| table_schema | sum(data_length) | sum(index_length) | totsize |
+--------------+------------------+-------------------+---------+
| auth         |              231 |              4096 |    4327 |
| bookmarks    |              152 |              3072 |    3224 |
| books        |             6094 |             10240 |   16334 |
| joomla       |           105223 |            118784 |  224007 |
| joomla2      |           108588 |            118784 |  227372 |
| mail         |             3545 |              4096 |    7641 |
| mysample     |            37750 |             38912 |   76662 |
| mysql        |           492362 |             72704 |  565066 |
| pureftpd     |               88 |              3072 |    3160 |
| simple_blog  |               44 |              3072 |    3116 |
| test         |              152 |              2048 |    2200 |
| test2        |              476 |              7168 |    7644 |
+--------------+------------------+-------------------+---------+


有沒有發覺到上面 show databases的結果扣掉 information_schema,共有14個databses;
而我們依據 information_schema.tables 計算的資料卻只有12筆.

因為有些database建立了,但是都沒有在裡面建立資料庫物件(table/view等),所以在
information_schema.tables 裡面就沒有資料.
接下來我們看一下 information_schema.schemata

mysql> select distinct schema_name
    -> from information_schema.schemata;
+--------------------+
| schema_name        |
+--------------------+
| information_schema |
| auth               |
| bookmarks          |
| books              |
| impexp_text        |
| joomla             |
| joomla2            |
| mail               |
| mysample           |
| mysql              |
| pandora            |
| pureftpd           |
| simple_blog        |
| test               |
| test2              |
+--------------------+
15 rows in set (0.00 sec)

mysql> select distinct table_schema
    -> from information_schema.tables;
+--------------------+
| table_schema       |
+--------------------+
| information_schema |
| auth               |
| bookmarks          |
| books              |
| joomla             |
| joomla2            |
| mail               |
| mysample           |
| mysql              |
| pureftpd           |
| simple_blog        |
| test               |
| test2              |
+--------------------+
13 rows in set (0.03 sec)

現在應該很清楚的看到了 information_schema.schemata, information_schema.tables
與show databases, show table status, show tables的關係了.

***********************************************************************************
4. 建立控制用database及其附屬table/view
接下來我們建立一個database 來作控制之用.

login as root

mysql> create database quotadb;

mysql> use quotadb;
Database changed

CREATE TABLE quota (
dbname CHAR(64) NOT NULL PRIMARY KEY,
limitbyte BIGINT NOT NULL,
exceeded ENUM('Y','N') DEFAULT 'N' NOT NULL);

insert into quota
select distinct schema_name,20971520,'N'
from information_schema.schemata
where schema_name != 'information_schema'
and schema_name != 'mysql'
and schema_name != 'quotadb';

mysql> select * from quota;
+-------------+-----------+----------+
| dbname      | limitbyte | exceeded |
+-------------+-----------+----------+
| auth        |  20971520 | N        |
| bookmarks   |  20971520 | N        |
| books       |  20971520 | N        |
| impexp_text |  20971520 | N        |
| joomla      |  20971520 | N        |
| joomla2     |  20971520 | N        |
| mail        |  20971520 | N        |
| mysample    |  20971520 | N        |
| pandora     |  20971520 | N        |
| pureftpd    |  20971520 | N        |
| simple_blog |  20971520 | N        |
| test        |  20971520 | N        |
| test2       |  20971520 | N        |
+-------------+-----------+----------+
13 rows in set (0.00 sec)

我們需要將mysql這個系統database排除,information_schema系統metadata也需要排除,
quotadb自己也要排除,以免限制住自己.

接下來建立一個view以方便獲取database size,並且在view中事先將mysql,quotadb排除.

create view v_dbsize as
select table_schema as dbname,sum(data_length) as data_size,sum(index_length) as index_size,sum(data_length)+sum(index_length) as totsize
from information_schema.tables
where table_schema != 'mysql'
and table_schema != 'quotadb'
and table_type='BASE TABLE'
and engine='MyISAM'
group by table_schema;

mysql> select * from v_dbsize;
+-------------+-----------+------------+---------+
| dbname      | data_size | index_size | totsize |
+-------------+-----------+------------+---------+
| auth        |       231 |       4096 |    4327 |
| bookmarks   |       152 |       3072 |    3224 |
| books       |      6094 |      10240 |   16334 |
| joomla      |    105223 |     118784 |  224007 |
| joomla2     |    108588 |     118784 |  227372 |
| mail        |      3545 |       4096 |    7641 |
| mysample    |     37750 |      38912 |   76662 |
| pureftpd    |        88 |       3072 |    3160 |
| simple_blog |        44 |       3072 |    3116 |
| test        |       152 |       2048 |    2200 |
| test2       |       476 |       7168 |    7644 |
+-------------+-----------+------------+---------+
11 rows in set (0.03 sec)

ps:因為這個instance裡面有兩個database impexp_text與pandora目前都是沒有任何資料的,
所以quota table有13筆record, v_dbsize view裡面有11筆record,是正常的.
運用quota與v_dbsize作一下比較.

select q.dbname, q.limitbyte, d.totsize, q.limitbyte - d.totsize as diff, d.totsize / q.limitbyte as percent
from quota q, v_dbsize d
where q.dbname = d.dbname;
+-------------+-----------+---------+----------+---------+
| dbname      | limitbyte | totsize | diff     | percent |
+-------------+-----------+---------+----------+---------+
| auth        |  20971520 |    4327 | 20967193 |  0.0002 |
| bookmarks   |  20971520 |    3224 | 20968296 |  0.0002 |
| books       |  20971520 |   16334 | 20955186 |  0.0008 |
| joomla      |  20971520 |  224007 | 20747513 |  0.0107 |
| joomla2     |  20971520 |  227372 | 20744148 |  0.0108 |
| mail        |  20971520 |    7641 | 20963879 |  0.0004 |
| mysample    |  20971520 |   76662 | 20894858 |  0.0037 |
| pureftpd    |  20971520 |    3160 | 20968360 |  0.0002 |
| simple_blog |  20971520 |    3116 | 20968404 |  0.0001 |
| test        |  20971520 |    2200 | 20969320 |  0.0001 |
| test2       |  20971520 |    7644 | 20963876 |  0.0004 |
+-------------+-----------+---------+----------+---------+

***********************************************************************************
5.開發控制用的stored procedure
接下來我們開始進行database size限制的功能.以databe:test,database:test2進行測試.
修改limitbye跟exceeded.

log in as root
mysql> use quotadb
mysql> UPDATE quota SET limitbyte=2100, exceeded='Y'
    -> WHERE dbname='test';
mysql> UPDATE quota SET limitbyte=7600, exceeded='Y'
    -> WHERE dbname='test2';
mysql> select * from quota where exceeded='Y';
+--------+-----------+----------+
| dbname | limitbyte | exceeded |
+--------+-----------+----------+
| test   |      2100 | Y        |
| test2  |      7600 | Y        |
+--------+-----------+----------+

接下來我們建立一個 view,將要控制且超過大小限制的database列出來.
CREATE VIEW v_dbsize_over AS
SELECT q.dbname
FROM quota q, v_dbsize d
WHERE q.dbname = d.dbname
AND q.exceeded='Y'
AND q.limitbyte-d.totsize < 0;

mysql> select * from v_dbsize_over;
+--------+
| dbname |
+--------+
| test   |
| test2  |
+--------+

再來建立一個view,列出有控制但大小未超過限制的database以及設定為未控制的database.
CREATE VIEW v_db_unlock AS
SELECT q.dbname
FROM quota q, v_dbsize d
WHERE q.dbname = d.dbname
AND q.exceeded='N'
UNION
SELECT q.dbname
FROM quota q, v_dbsize d
WHERE q.dbname = d.dbname
AND q.exceeded='Y'
AND q.limitbyte-d.totsize >= 0;

將database:test2的限制改為7800.

mysql> UPDATE quota SET limitbyte=7800, exceeded='Y'
    -> WHERE dbname='test2';

mysql> select * from v_db_unlock;
+-------------+
| dbname      |
+-------------+
| auth        |
| bookmarks   |
| books       |
| joomla      |
| joomla2     |
| mail        |
| mysample    |
| pureftpd    |
| simple_blog |
| test2       |
+-------------+

mysql> select * from v_dbsize_over;
+--------+
| dbname |
+--------+
| test   |
+--------+

可以看到兩個view均能發揮功效.
接下來我們寫一個stored porcedure,來控制Insert/Create權限
---------------------------------------------------
DELIMITER $$

DROP PROCEDURE IF EXISTS `quotadb`.`quota_ctrl`$$
CREATE PROCEDURE `quotadb`.`quota_ctrl` ()
BEGIN
# -------------------------------------------------------------
# MySQL Database Size limit control procedure
# Version 1.0
# Use Cursor, we can develop use another way in Version 2
# Guess the method in V2 (^.^)
# Arthor : Bunko
# -------------------------------------------------------------   
DECLARE ctrldb varchar(64);
DECLARE no_more_db int;

DECLARE overdb CURSOR FOR
  SELECT dbname FROM v_dbsize_over;
DECLARE unlockdb CURSOR FOR
  SELECT dbname FROM v_db_unlock;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_db=1;

SET no_more_db=0;
OPEN overdb;
over_loop:WHILE(no_more_db=0) DO
   FETCH overdb INTO ctrldb;
   IF no_more_db=1 THEN
      LEAVE over_loop;
   END IF;
   UPDATE mysql.db SET Insert_Priv='N',Create_priv='N' where db=ctrldb;
   END WHILE over_loop;
CLOSE overdb;

SET no_more_db=0;
OPEN unlockdb;
unlock_loop:WHILE(no_more_db=0) DO
   FETCH unlockdb INTO ctrldb;
   IF no_more_db=1 THEN
      LEAVE unlock_loop;
   END IF;
   UPDATE mysql.db SET Insert_Priv='Y',Create_priv='Y' where db=ctrldb;
END WHILE unlock_loop;
CLOSE unlockdb;

FLUSH PRIVILEGES;

END$$

DELIMITER ;

------------------------------------------------------------------------
我們先看一下測試用的兩個databse的權限.

mysql> select db,Insert_priv, Create_priv from mysql.db where db='test' or db='test2';
+-------+-------------+-------------+
| db    | Insert_priv | Create_priv |
+-------+-------------+-------------+
| test  | Y           | Y           |
| test2 | Y           | Y           |
+-------+-------------+-------------+
2 rows in set (0.00 sec)

mysql> call quota_ctrl();
Query OK, 0 rows affected (0.05 sec)

mysql> select db,Insert_priv, Create_priv from mysql.db where db='test' or db='test2';
+-------+-------------+-------------+
| db    | Insert_priv | Create_priv |
+-------+-------------+-------------+
| test  | N           | N           |
| test2 | Y           | Y           |
+-------+-------------+-------------+
2 rows in set (0.00 sec)

可以看到test已經被改變權限,無法再Insert/Create了.

接下來再進行一些改變.
改變前,
mysql> select q.dbname, q.limitbyte, q.exceeded, d.totsize, q.limitbyte - d.totsize as diff
    -> from quota q, v_dbsize d
    -> where q.dbname = d.dbname
    -> and (q.dbname='test' or q.dbname='test2');
+--------+-----------+----------+---------+------+
| dbname | limitbyte | exceeded | totsize | diff |
+--------+-----------+----------+---------+------+
| test   |      2100 | Y        |    2200 | -100 |
| test2  |      7800 | Y        |    7644 |  156 |
+--------+-----------+----------+---------+------+

test 應該要被控制,test2還未超過,不需要控制. quota_ctrl()也依照需要進行控制了.
進行改變,
UPDATE quota SET exceeded='N' where dbname='test';

UPDATE quota SET limitbyte=7500 where dbname='test2';

select q.dbname, q.limitbyte, q.exceeded, d.totsize, q.limitbyte - d.totsize as diff
from quota q, v_dbsize d
where q.dbname = d.dbname
and (q.dbname='test' or q.dbname='test2');
+--------+-----------+----------+---------+------+
| dbname | limitbyte | exceeded | totsize | diff |
+--------+-----------+----------+---------+------+
| test   |      2100 | N        |    2200 | -100 |
| test2  |      7500 | Y        |    7644 | -144 |
+--------+-----------+----------+---------+------+

現在test應該要改為解開,而test2要變為受控.

mysql>  select db,Insert_priv, Create_priv from mysql.db where db='test' or db='test2';
+-------+-------------+-------------+
| db    | Insert_priv | Create_priv |
+-------+-------------+-------------+
| test  | Y           | Y           |
| test2 | N           | N           |
+-------+-------------+-------------+
2 rows in set (0.00 sec)

未完..待續  :)

bunko

  • 懷疑的國中生
  • **
  • 文章數: 67
    • 檢視個人資料
wenlien大,
你交代的任務先完成一部分,會再接下去繼續...  ;)
lol
Bunko

wenlien

  • 憂鬱的高中生
  • ***
  • 文章數: 119
  • 性別: 男
    • 檢視個人資料
    • Open or not open, that is the stupid question.
wenlien大,
你交代的任務先完成一部分,會再接下去繼續...  ;)
lol
Bunko

Hi Bunko大大:
感謝你的分享,應該請版主大大來把這篇文章至頂才是~

看過你的Stored Procedure之後,突然發現一個小弟之前沒思考周慮到的地方:
1. 權限控管的部份,分成五個在做控管:
user, db, tables_priv, columns_priv, procs_priv.
http://dev.mysql.com/doc/refman/5.1/en/grant-table-structure.html
所以,要確定裡面都沒有insert的權限,才可以避免user insert data。
如果可以當然是從程式下手,已達到完美,
再不然,我會建議,從管理的角度下手,儘量使用單一的設定權限方法(資料表)來作。
以你的程式為例,只控管mysql.db這個資料表。

2. 你在procedure中,直接使用update。
代碼: [選擇]
...
UPDATE mysql.db SET Insert_Priv='N',Create_priv='N' where db=ctrldb;
...
UPDATE mysql.db SET Insert_Priv='Y',Create_priv='Y' where db=ctrldb;
...
如果考慮到權限控管,不見得一開始每一個user都有insert的權限。
ex.
User 1: select/insert/update table A.
User 2: select table A only.

所以,如果可以有一個資料表將最原始的權限都記錄下來,就更完美了~ (我覺得這個應該可以算是optional)
ex.
代碼: [選擇]
[create backup permission table]
-- create permission table:
create table test.db select * from mysql.db where user is NULL;

[setting insert block]
-- backup permission.
insert into test.db select * from mysql.db where db='test';
-- update permission.
update mysql.db set ...;
-- flush privileges.
flush privileges;

[release insert block]
-- stored permission.
delete from mysql.db where db='test';
insert into mysql.db select * from test.db where db='test';
-- delete backup permission.
delete from test.db where db='test';
-- flush privileges.
flush privileges;


此外,小弟發現你有一個計算size的function,是用cursoe來實作。
利用cursor應該會耗用較多的資源,且如果將來有人要接手source code,
其門檻也會變高,所以,我認為用一般的select就可以達到你要的結果:
代碼: [選擇]
DELIMITER $$

DROP FUNCTION IF EXISTS `test`.`dbsize`$$
CREATE FUNCTION `test`.`dbsize` (in_dbname varchar(64)) RETURNS BIGINT UNSIGNED
BEGIN
DECLARE rtnSize BIGINT UNSIGNED;
select sum(data_length)+sum(index_length) into rtnSize
   from information_schema.tables
   where table_schema = in_dbname
   and table_type = 'BASE TABLE'
   and engine = 'MyISAM';

RETURN rtnSize;

END$$

DELIMITER ;

最後,再感謝一次Bunko大大,並期待你後續的分享~  :)
regards,

Stanley Huang

bunko

  • 懷疑的國中生
  • **
  • 文章數: 67
    • 檢視個人資料
Dear wenlien,
1. 關於權限控管部份,您的建議很好,將原本的權限也納入考慮,這方面是要再思考一下,怎樣做的更好.
    其實最早的想法是比較偏向監控database size是否超過兩個限制值,
    超過soft limit時發出警告,超過hard limit時才進行鎖住.不過在著手進行時,發覺這樣會讓說明文件變複雜
    一些,所以就先把限制的功能作出來,後續再來改良.
   
2. 關於dbsize()裡面的改用直接select into . 是的,這樣會更快更容易懂.後續的資料裡面會進行修正.

3. 原本是想都寫好後再post上來,後來想先post給大家看看提出改進意見,果然先post上來是對的,獲得很好的改進意見.
    目前所做到的限制功能還比較粗糙,還有需要改進的地方.有什麼好的想法,請不要客氣儘管提出來,謝謝!
bunko
   

bunko

  • 懷疑的國中生
  • **
  • 文章數: 67
    • 檢視個人資料
很抱歉拖了這麼久,把相關的資料整理好後壓縮放在:

http://www.filesonic.com/file/17520903/mysqlquotabunko.rar
壓縮檔是比較詳細的說明.
使用時請務必小心.
應該會有更好的方法,只是想說先把目前想到的先整理放上來,提供參考.
以下是建立相關table/view/function/procdure的sql檔內容:
------------------------------------------------------------------
# --------------------------------------------------------
# MySQL MyISAM Databse Quota Control Set up
# Date : 2010/08/22
# Arthor : Bunko
# use this sql with root
# GRANT Privilege part in the end, modify by your case
# --------------------------------------------------------

# *************************************************************
# How to use it
# mysql -u root -p < quotactrl.sql
# mysql -u monitor -p
# use monitor
#  and just set up the monitor_db
# dbname <== the target db
# sizelimit <== limit in bytes
# warn_percent <== soft warn enable when the sizelimit x warn_percent %
# lock_percent <== lock db when the db size over sizelimit x lock_percent %
# lock_enable <== set to 'Y' to enable lock function
#
# and create a shell like under ; call it dbctrl.sh
# modify the MYSQL_PATH fit your environment
# ------ shell script start -------------
# #!/bin/bash
# ---- Basic info set up ----
# MYSQL_PATH="/opt/mysql/5.1.48/bin/mysql"
# MYSQL_USER="monitor"
# MYSQL_PASS="monitor"
# MYSQL_DB="monitor"
# ---- Chnage the basic info for your environment

# Query MySQL database function
# query() {
#  echo "$1" | ${MYSQL_PATH} -s --user=${MYSQL_USER} --password=${MYSQL_PASS} ${MYSQL_DB}
#}
# sql="CALL lock_ctrl()"
# RESULT=$(query "${sql}")
# ----------shell script end -------------

# set up the crontab to call the dbctrl.sh
# modify the frequency to fit your environment
# */10 * * * * /yourpath/dbctrl.sh >> /dev/null 2>&1
# ----------------------------------------
# check these 2 tables to see the real time db quota staus and the process log
# select * from v_lock
# select * from lock_status
# ----------------------------------------
# the monitor user can change the monitor_db to control
# the probe user just for check the status
# be careful, back up mysql.db first!
# **************************************************************


CREATE DATABASE monitor;

USE monitor

DELIMITER $$

DROP FUNCTION IF EXISTS `monitor`.`sys_dbsize`$$
CREATE FUNCTION `monitor`.`sys_dbsize` (in_dbname varchar(64)) RETURNS BIGINT UNSIGNED
READS SQL DATA SQL SECURITY DEFINER
BEGIN
RETURN(select sum(data_length)+sum(index_length)
   from information_schema.tables
   where table_schema = in_dbname
   and table_type = 'BASE TABLE'
   and engine = 'MyISAM');
END$$

DELIMITER ;


DELIMITER $$

DROP FUNCTION IF EXISTS `monitor`.`sys_dbfree`$$
CREATE FUNCTION `monitor`.`sys_dbfree` (in_dbname varchar(64)) RETURNS BIGINT UNSIGNED
READS SQL DATA SQL SECURITY DEFINER
BEGIN
RETURN(select sum(data_free)
   from information_schema.tables
   where table_schema = in_dbname
   and table_type = 'BASE TABLE'
   and engine = 'MyISAM');
END$$

DELIMITER ;

DELIMITER $$

DROP FUNCTION IF EXISTS `monitor`.`isbigger`$$
CREATE FUNCTION `monitor`.`isbigger` (a int, b int) RETURNS CHAR NO SQL
BEGIN
DECLARE rtn CHAR(1);
IF a >= b THEN
  SET rtn='Y';
ELSE
  SET rtn='N';
END IF;
RETURN rtn;
END$$

DELIMITER ;



DELIMITER $$

DROP FUNCTION IF EXISTS `monitor`.`bothyes`$$
CREATE FUNCTION `monitor`.`bothyes` (a char(1), b char(1)) RETURNS char
NO SQL
BEGIN
DECLARE rtn CHAR(1);

IF a='Y' and b='Y' THEN
   SET rtn='Y';
ELSE
   SET rtn='N';
END IF;
RETURN rtn;
END$$

DELIMITER ;

create table monitor_db(
dbname VARCHAR(64) NOT NULL PRIMARY KEY,
sizelimit BIGINT UNSIGNED NOT NULL,
warn_percent TINYINT UNSIGNED NOT NULL,
lock_percent tinyint unsigned NOT NULL,
lock_enable ENUM('Y','N') NOT NULL DEFAULT 'N'
);

DELIMITER $$

DROP TRIGGER IF EXISTS `monitor`.`monitor_db_bi`$$
CREATE TRIGGER `monitor`.`monitor_db_bi` BEFORE INSERT ON monitor_db FOR EACH ROW
BEGIN
IF NEW.sizelimit < 1024 THEN
   SET NEW.sizelimit=1024;
END IF;
IF NEW.warn_percent > 100 THEN
   SET NEW.warn_percent=100;
END IF;
IF NEW.warn_percent <= 0 THEN
   SET NEW.warn_percent=1;
END IF;

IF NEW.lock_percent > 100 THEN
   SET NEW.lock_percent=100;
END IF;
IF NEW.lock_percent <= 0 THEN
   SET NEW.lock_percent=1;
END IF;
IF NEW.lock_enable = 'y' THEN
   SET NEW.lock_enable = 'Y';
END IF;
IF NEW.lock_enable <> 'Y' THEN
   SET NEW.lock_enable = 'N';
END IF;


END$$

DELIMITER ;

DELIMITER $$

DROP TRIGGER IF EXISTS `monitor`.`monitor_db_bu`$$
CREATE TRIGGER `monitor`.`monitor_db_bu` BEFORE UPDATE ON monitor_db FOR EACH ROW
BEGIN
IF NEW.sizelimit < 1024 THEN
   SET NEW.sizelimit=1024;
END IF;
IF NEW.warn_percent > 100 THEN
   SET NEW.warn_percent=100;
END IF;
IF NEW.warn_percent <= 0 THEN
   SET NEW.warn_percent=1;
END IF;

IF NEW.lock_percent > 100 THEN
   SET NEW.lock_percent=100;
END IF;
IF NEW.lock_percent <= 0 THEN
   SET NEW.lock_percent=1;
END IF;
IF NEW.lock_enable = 'y' THEN
   SET NEW.lock_enable = 'Y';
END IF;
IF NEW.lock_enable <> 'Y' THEN
   SET NEW.lock_enable = 'N';
END IF;

END$$

DELIMITER ;

CREATE VIEW v_lock AS
SELECT dbname,round(sizelimit/1048576,2) as limitmg ,warn_percent as warnpcnt,round(sys_dbsize(dbname)/1048576,2) as curmg, round(sys_dbsize(dbname)/sizelimit*100) as crpcnt,isbigger(round(sys_dbsize(dbname)/sizelimit*100) ,warn_percent) as warn, lock_percent,isbigger(round(sys_dbsize(dbname)/sizelimit*100) ,lock_percent) as lock_warn,lock_enable, bothyes(isbigger(round(sys_dbsize(dbname)/sizelimit*100) ,lock_percent) ,lock_enable) as belocking
from monitor_db;

CREATE TABLE lock_status(
dbname varchar(64),
limitmg decimal(23,2),
warnpcnt tinyint unsigned,
curmg decimal(23,2),
crpcnt decimal(24,0),
warn char(1),
lock_percent tinyint unsigned,
lock_warn char(1),
lock_enable char(1),
belocking  char(1),
stamp timestamp);

CREATE TABLE privlog(
host char(60) NOT NULL,
db char(64) NOT NULL,
user char(16) NOT NULL,
insert_priv enum('N','Y') default 'N',
create_priv enum('N','Y') default 'N',
stamp timestamp,
PRIMARY KEY(host,db,user)
);

DELIMITER $$

DROP PROCEDURE IF EXISTS `monitor`.`lock_ctrl`$$
CREATE PROCEDURE `monitor`.`lock_ctrl` ()
MODIFIES SQL DATA SQL SECURITY DEFINER  
BEGIN

DECLARE lockdbname CHAR(64);
DECLARE belock CHAR(1);
DECLARE logcount int;
DECLARE no_more_db int;


DECLARE lockdb CURSOR FOR
  SELECT dbname,belocking FROM v_lock;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_db=1;

-- start --
-- save status to lock_status
INSERT INTO lock_status(dbname, limitmg, warnpcnt, curmg, crpcnt, warn, lock_percent, lock_warn, lock_enable, belocking)
SELECT dbname, limitmg, warnpcnt, curmg, crpcnt, warn, lock_percent, lock_warn, lock_enable, belocking
FROM v_lock;
-- open cursor for belocking db --
SET no_more_db=0;
OPEN lockdb;
lock_loop:WHILE(no_more_db=0) DO
   FETCH lockdb INTO lockdbname,belock;
   IF no_more_db=1 THEN
     LEAVE lock_loop;
   END IF;
-- process
   SELECT COUNT(1) INTO logcount FROM privlog WHERE db=lockdbname;
   IF belock='Y' THEN
     IF logcount=0 THEN
        -- FIRST LOCK and backup privleges to monitor.privlog from mysql.db by dbname
        INSERT INTO monitor.privlog (host,db,user,insert_priv,create_priv)
           SELECT Host,Db,User,Insert_Priv,Create_Priv
            from mysql.db where Db=lockdbname;
     END IF;
      -- Lock it!!
      UPDATE mysql.db SET Insert_Priv='N',Create_priv='N' where Db=lockdbname;
   ELSE  
         -- unlock
      IF logcount<>0 THEN
           
         UPDATE mysql.db as t
   LEFT JOIN monitor.privlog AS s ON t.Db=s.db
   SET t.Insert_Priv=s.insert_priv, t.Create_Priv=s.create_priv
   WHERE t.Host = s.host
   AND t.User = s.user;

      END IF;
   END IF;

END WHILE lock_loop;
CLOSE lockdb;

FLUSH PRIVILEGES;

END$$

DELIMITER ;

# ----------------------------------------

GRANT ALL ON monitor.* to 'monitor'@'localhost' IDENTIFIED BY 'monitor';

GRANT SELECT ON monitor.v_lock to 'probe'@'localhost' IDENTIFIED BY 'probe';

GRANT SELECT ON monitor.lock_status to 'probe'@'localhost';

FLUSH PRIVILEGES;
-------------------------------------------------------------

Bunko
« 上次編輯: 2010-08-23 19:46 由 bunko »