請問有否方法可以對於每個DB 進行大小容量設定限制(即Quota)嗎 ?
时间：2010-08-05
来源：互联网
各位大大您们好,
如题,可以协助一下吗?
系统是Linux FC6 的预设 DB Server...
感谢!
如题,可以协助一下吗?
系统是Linux FC6 的预设 DB Server...
感谢!
作者: edwardleung 发布时间: 2010-08-05
在建立 table 的时候设吧
作者: micmic3 发布时间: 2010-08-05
您好,
不好意思, 请问是否在MySQL 建立Database 时设定呢?
如何设定才对呢 ? 有否图示可以参考一下吗 ?
谢谢!
不好意思, 请问是否在MySQL 建立Database 时设定呢?
如何设定才对呢 ? 有否图示可以参考一下吗 ?
谢谢!
作者: edwardleung 发布时间: 2010-08-05
作者: micmic3 发布时间: 2010-08-05
作者: edwardleung 发布时间: 2010-08-05
引用自: micmic3 於 2010-08-05 15:30
这个方法赞～
但是，如果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 ）:
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.
作者: wenlien 发布时间: 2010-08-06
作者: bunko 发布时间: 2010-08-06
引用
但是，如果DB换帐号密码，那php里面的设定就要一定改～
所以，可以把逻辑写成Stored Procedure，然后用MySQL的event来执行。
上述的问题就可以解决!
所以，可以把逻辑写成Stored Procedure，然后用MySQL的event来执行。
上述的问题就可以解决!
1, 请问php 那部份需要改 ?
2, 如何可以写成Stored Procedure ?
谢谢!
作者: edwardleung 发布时间: 2010-08-07
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 程式:
----------------------------------------------------------------
计算结果
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的总和.
-------------------------------------------------------------------
计算结果
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.
**************************************************************************
未完...待续
======================================
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----------------------------------------------------------------
计算结果
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------------------------------------------------------------------
计算结果
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.
**************************************************************************
未完...待续
作者: bunko 发布时间: 2010-08-07
引用自: edwardleung 於 2010-08-07 00:25
引用
但是，如果DB换帐号密码，那php里面的设定就要一定改～
所以，可以把逻辑写成Stored Procedure，然后用MySQL的event来执行。
上述的问题就可以解决!
所以，可以把逻辑写成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
作者: wenlien 发布时间: 2010-08-07
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)
未完..待续
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 发布时间: 2010-08-08
wenlien大,
你交代的任务先完成一部分,会再接下去继续...
lol
Bunko
你交代的任务先完成一部分,会再接下去继续...
lol
Bunko
作者: bunko 发布时间: 2010-08-08
引用自: bunko 於 2010-08-08 19:37
wenlien大,
你交代的任务先完成一部分,会再接下去继续...
lol
Bunko
你交代的任务先完成一部分,会再接下去继续...
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;
...
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大大，并期待你后续的分享～
作者: wenlien 发布时间: 2010-08-09
Dear wenlien,
1. 关於权限控管部份,您的建议很好,将原本的权限也纳入考虑,这方面是要再思考一下,怎样做的更好.
其实最早的想法是比较偏向监控database size是否超过两个限制值,
超过soft limit时发出警告,超过hard limit时才进行锁住.不过在著手进行时,发觉这样会让说明文件变复杂
一些,所以就先把限制的功能作出来,后续再来改良.
2. 关於dbsize()里面的改用直接select into . 是的,这样会更快更容易懂.后续的资料里面会进行修正.
3. 原本是想都写好后再post上来,后来想先post给大家看看提出改进意见,果然先post上来是对的,获得很好的改进意见.
目前所做到的限制功能还比较粗糙,还有需要改进的地方.有什么好的想法,请不要客气尽管提出来,谢谢!
bunko
1. 关於权限控管部份,您的建议很好,将原本的权限也纳入考虑,这方面是要再思考一下,怎样做的更好.
其实最早的想法是比较偏向监控database size是否超过两个限制值,
超过soft limit时发出警告,超过hard limit时才进行锁住.不过在著手进行时,发觉这样会让说明文件变复杂
一些,所以就先把限制的功能作出来,后续再来改良.
2. 关於dbsize()里面的改用直接select into . 是的,这样会更快更容易懂.后续的资料里面会进行修正.
3. 原本是想都写好后再post上来,后来想先post给大家看看提出改进意见,果然先post上来是对的,获得很好的改进意见.
目前所做到的限制功能还比较粗糙,还有需要改进的地方.有什么好的想法,请不要客气尽管提出来,谢谢!
bunko
作者: bunko 发布时间: 2010-08-09
很抱歉拖了这么久,把相关的资料整理好后压缩放在:
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
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
作者: bunko 发布时间: 2010-08-22
相关阅读 更多
热门阅读
-
office 2019专业增强版最新2021版激活秘钥/序列号/激活码推荐 附激活工具
阅读：74
-
如何安装mysql8.0
阅读：31
-
Word快速设置标题样式步骤详解
阅读：28
-
20+道必知必会的Vue面试题（附答案解析）
阅读：37
-
HTML如何制作表单
阅读：22
-
百词斩可以改天数吗？当然可以，4个步骤轻松修改天数！
阅读：31
-
ET文件格式和XLS格式文件之间如何转化？
阅读：24
-
react和vue的区别及优缺点是什么
阅读：121
-
支付宝人脸识别如何关闭?
阅读：21
-
腾讯微云怎么修改照片或视频备份路径?
阅读：28
