更新my.cnf中的变量 [英] Updating variables in my.cnf

问题描述

我在my.cnf文件中设置了lower_case_table_names = 2.设置变量后，我重新启动了mysql服务器，但是当我使用SHOW VARIABLES时，该值仍为0.

I set lower_case_table_names = 2 in my.cnf file. After setting the variable I restarted mysql server, but still the value is 0, when I use SHOW VARIABLES.

我正在使用MySQL 5.5.还有什么需要做的吗?

I'm using MySQL 5.5. Any Ideas what else needs to be done?

更新

my.cnf位于/etc和/etc/mysql(Ubuntu操作系统)

my.cnf is at /etc and /etc/mysql (ubuntu OS)

内容:-

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
#
# * Basic Settings
#
lower_case_table_names = 2
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        = 127.0.0.1
#
# * Fine Tuning
#
key_buffer      = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit   = 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries   = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id      = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size         = 100M
#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem



[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer      = 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

推荐答案

最明显的可能性:

• my.cnf 中的错误-我需要确切地查看您的my.cnf文件中的内容
• my.cnf 的位置错误
• 由于命令行设置，mysql服务器无法读取 my.cnf
• mysql服务器使用命令行设置覆盖 my.cnf 中的设置

• error in my.cnf -- I would need to see exactly what is in your my.cnf file
• my.cnf is in the wrong location
• mysql server doesn't read my.cnf because of the command-line settings
• mysql server overrides the settings from my.cnf with the command-line settings

显示您的 my.cnf 文件中的内容，它的路径以及您重新启动服务器的精确度，我将对我的回答进行阐述.

Show what's in your my.cnf file, what's its path, and how exactly you restart the server, and I will ellaborate on my answer.

更新:在UNIX(或任何具有区分大小写的文件系统的系统)上，您不应使用值"2"

UPDATE: You shouldn't use value "2" on UNIX (or any system that has case-sensitive file system)

摘自手册( http://dev. mysql.com/doc/refman/5.0/en/identifier-case-sensitiveivity.html ):

如果仅在一个平台上使用MySQL，则通常没有 更改 lower_case_table_names 变量的默认值. 但是，如果您要转移表，则可能会遇到困难. 文件系统区分大小写的平台之间的差异.为了 例如，在Unix上，可以有两个名为 my_table 的不同表， MY_TABLE ，但是在Windows上，这两个名称被认为是相同的.到 避免由于数据库的字母大小写而引起的数据传输问题 表名，您有两个选择:

If you are using MySQL on only one platform, you do not normally have to change the lower_case_table_names variable from its default value. However, you may encounter difficulties if you want to transfer tables between platforms that differ in file system case sensitivity. For example, on Unix, you can have two different tables named my_table and MY_TABLE, but on Windows these two names are considered identical. To avoid data transfer problems arising from lettercase of database or table names, you have two options:

在所有系统上使用 lower_case_table_names = 1 .主要缺点 这是因为当您使用 SHOW TABLES (显示表格)或 SHOW DATABASES (显示数据库)时， 没有看到其原始字母大写的名称.

Use lower_case_table_names=1 on all systems. The main disadvantage with this is that when you use SHOW TABLES or SHOW DATABASES, you do not see the names in their original lettercase.

在 Unix 上使用 lower_case_table_names = 0 ，在 lower_case_table_names = 2 上使用 Windows .这将保留数据库和表名的字母大小写. 这样做的缺点是您必须确保您的陈述 始终使用正确的名称引用数据库和表名称 Windows上的大写字母.如果将语句转移到Unix， 字母是重要的，如果字母是 不正确.

Use lower_case_table_names=0 on Unix and lower_case_table_names=2 on Windows. This preserves the lettercase of database and table names. The disadvantage of this is that you must ensure that your statements always refer to your database and table names with the correct lettercase on Windows. If you transfer your statements to Unix, where lettercase is significant, they do not work if the lettercase is incorrect.

例外:如果您使用的是 InnoDB 表，并且您试图避免 这些数据传输问题，应将 lower_case_table_names 设置为 在所有平台上 1 强制将名称转换为小写.

Exception: If you are using InnoDB tables and you are trying to avoid these data transfer problems, you should set lower_case_table_names to 1 on all platforms to force names to be converted to lowercase.

您要完成什么?为什么要将它设置为2?

What are you trying to accomplish? Why do you want to set it to 2?

这篇关于更新my.cnf中的变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！