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;