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)
未完..待續
