MySQL管理使用小技巧（一）

1.檢視各個表的碎片情況：

select TABLE_SCHEMA,TABLE_NAME,engine,

round((data_length+index_length+Data_free)/1024/1024/1024,2) as tablesize,

round(Data_free/1024/1024/1024,2) as datafree

from information_schema.tables

order by datafree desc limit 20;

2.伺服器連接配接分組排序：

select left(host,12),user,count(*) as cnt

from information_schema.processlist

group by left(host,12),user

order by cnt desc;

3.遠端導入檔案

發現mysql遠端導入檔案時，在mysql client和server端都需配置或者打開local-infile=1,否則會報錯：The used command is not allowed with this MySQL version

官方手冊說明：

"If LOAD DATA LOCAL INFILE is disabled, either in the server or the client, a client that attempts to issue such a statement receives the following error message:

ERROR 1148: The used command is not allowed with this MySQL version"

在用戶端可以在/etc/my.cnf中添加如下：

[client]

local-infile=1

server端保證如下為on狀态：

mysql>show variables like "%local%";

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| local_infile | ON |

+---------------+-------+

/ROOT/server/mysql/bin/mysql -uyubowei -p -h10.10.67.23 -P 3301 DB_YU --local-infile=1 -e"load data local infile '/home/mysql/aa.sql' into table aa;"

注意：完成上述操作需要開啟服務端的local_infile=ON

注意：load data local infile遠端導入，隻需要給使用者開啟insert權限即可，不需要File權限。伺服器端導入和select Into outfile需要File權限。

遠端導入資料檔案，必須要保證/tmp目錄夠大，否則要導入的檔案會将其寫滿而導緻報錯（這個目錄不能調整，隻能是/tmp）：

• If LOCAL is specified, the file is read by the client program on the client host and sent to the server. The file can be given as a full path name to specify its exact location. If given as a relative path name, the name is interpreted relative to the directory in which the client program was started.

When using LOCAL with , a copy of the file is created in the server's temporary directory. This is not the directory determined by the value of or , but rather the operating system's temporary directory, and is not configurable in the MySQL Server. (Typically the system temporary directory is /tmp on Linux systems and C:\WINDOWS\TEMP on Windows.) Lack of sufficient space for the copy in this directory can cause the statement to fail.

4.表不存在主鍵判斷：

select c.TABLE_SCHEMA,c.TABLE_NAME

from information_schema.COLUMNS c,information_schema.TABLES t

where c.TABLE_SCHEMA='db58_wcdb'

and c.TABLE_SCHEMA=t.TABLE_SCHEMA

and c.TABLE_NAME=t.TABLE_NAME

and t.TABLE_TYPE='BASE TABLE'

group by c.TABLE_SCHEMA,c.TABLE_NAME having sum(if(COLUMN_KEY='PRI',1,0)) = 0;