Posts tagged ‘default-character-set’

my.cnf的[mysql]组放些什么

my.cnf里面有很多组,[mysqld],[client],[mysql],[mysqldump]等等。
mysql算是我们用的最多的工具,管理,数据订正,查看状态。。。
而每次mysql客户端启动的时候都会读取[mysql]的参数。大家都是怎么设置的列?
先说一下我的:
no-auto-rehash
prompt=”\\u@\\h : \\d \\R:\\m:\\s> ”
#tee=”/tmp/query.log”
#pager=”less -i -n -S”
max_allowed_packet = 4M
show-warnings
default-character-set = UTF8

no-auto-rehash是为了不让MySQL自动提示,这样对MySQL客户端有点慢。
#tee=”/tmp/query.log”是把你输入的SQL等导入到/tmp/query.log文件,如果你不怕这个文件太大可以设置这个参数
#pager=”less -i -n -S”是设置MySQL显示分页数据的工具,如果你安装了maatkit的visual EXPLAIN工具,可以设置为它,正常的情况下它表现和less一样,如果是显示查询计划,它就与众不同了。
max_allowed_packet = 4M就不说了

其他的三个我想特别说一下:
prompt=”\\u@\\h : \\d \\R:\\m:\\s> ” 这个是设置提示符用的,让你随时可以知道你在那个机器,那个库,时间等等,比如这个prompt对应的就是:root@localhost : mysql 10:21:24>。
show-warnings 可以让MySQL执行SQL出现warning的时候,把warning也打印出来,这个在你执行多条语句,但是第一条语句就有warning的时候非常有效。这个参数也可以减轻你的劳动力,每次warning出现的时候,你不用自己手工去输入命令show warnings;
default-character-set = UTF8 其实在client可能我们大部分同志会加这个。但是为什么在这里还要加一个列。mysqlbinlog不认default-character-set这个参数,有时候我们没有办法,只好注释掉default-character-set,如果mysqlbinlog查询binlog之后不记得恢复,那么你下次打开MySQL客户端的时候,你的客户端字符集就有可能没有设置。(这个问题也可以用loose_default-character-set来解决),但是在[mysql]里面加了这个以后,我们就没有这个担心了。这个参数我建议加到其他的客户端组比如[mysqldump]等。

may your success.

loose- my.cnf参数prefix

发现一个有意思的参数prefix: –loose-
我们平常使用mysqlbinlog经常会出现错误:
mysqlbinlog: unknown variable ‘default-character-set=utf8’
说不认识这个参数,网上找了一下,发现只要修改my.cnf的client组里面的default-character-set = utf8为loose_default-character-set = utf8,mysqlbinlog就可以正常工作。MySQL5.0和5.1都适用。
这个prefix其实就是让读取该参数的程序不要那么死心眼,如果这个参数自己不解析,那么就当它不存在吧。

拷贝一下MySQL的reference:
4.2.3.2. Program Option Modifiers

Some options are “boolean” and control behavior that can be turned on or off. For example, the mysql client supports a –column-names option that determines whether or not to display a row of column names at the beginning of query results. By default, this option is enabled. However, you may want to disable it in some instances, such as when sending the output of mysql into another program that expects to see only data and not an initial header line.

To disable column names, you can specify the option using any of these forms:

–disable-column-names
–skip-column-names
–column-names=0

The –disable and –skip prefixes and the =0 suffix all have the same effect: They turn the option off.

The “enabled” form of the option may be specified in any of these ways:

–column-names
–enable-column-names
–column-names=1

If an option is prefixed by –loose, a program does not exit with an error if it does not recognize the option, but instead issues only a warning:

shell> mysql –loose-no-such-option
mysql: WARNING: unknown option ‘–no-such-option’

The –loose prefix can be useful when you run programs from multiple installations of MySQL on the same machine and list options in an option file, An option that may not be recognized by all versions of a program can be given using the –loose prefix (or loose in an option file). Versions of the program that recognize the option process it normally, and versions that do not recognize it issue a warning and ignore it.

mysqld enables a limit to be placed on how large client programs can set dynamic system variables. To do this, use a –maximum prefix with the variable name. For example, –maximum-query_cache_size=4M prevents any client from making the query cache size larger than 4MB.