Probleme bei Konfiguration in der my.cnf

MySQL, PostgreSQL, SQLite
simcard
Posts: 19
Joined: 2005-08-22 19:29

Probleme bei Konfiguration in der my.cnf

Post by simcard » 2006-07-07 18:39

Hallo,

ich bin MySQL mäßig noch ziemlich unerfahren und wollte eben auf meinem Server die my.cnf optimieren, da dies damals vergessen wurde. Mein Problem ist, dass die völlig unübersichtlich ist bzw. war...
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "/var/lib/mysql/my.cnf" to set server-specific options or
# - "~/.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/serve ... ables.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
#
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
language = /usr/share/mysql/english
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 = 128K
#
# * Query Cache Configuration
#
query_cache_limit = 1048576
query_cache_size = 16777216
query_cache_type = 1
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
#log = /var/log/mysql.log
#log = /var/log/mysql/mysql.log
#
# Error logging goes to syslog. This is a Debian improvement :)
#
# Here you can see queries with especially long duration
#log-slow-queries = /var/log/mysql/mysql-slow.log
#
# The following can be used as easy to replay backup logs or for replication.
#server-id = 1
log-bin = /var/log/mysql/mysql-bin.log
# See /etc/mysql/debian-log-rotate.conf for the number of files kept.
max_binlog_size = 104857600
#binlog-do-db = include_database_name
#binlog-ignore-db = include_database_name
#
# * BerkeleyDB
#
# The use of BerkeleyDB is now discouraged and support for it will probably
# cease in the next versions.
skip-bdb
#
# * 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 Feature
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# If you want to enable SSL support (recommended) read the manual or my
# HOWTO in /usr/share/doc/mysql-server/SSL-MINI-HOWTO.txt.gz
# 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

Wenn ich nun so abändere:
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "/var/lib/mysql/my.cnf" to set server-specific options or
# - "~/.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/serve ... ables.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
#
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
language = /usr/share/mysql/english
skip-external-locking
skip-innodb
skip-locking
skip-networking
skip-name-resolve
skip-external-locking
skip-show-database
safe-user-create = 1
key_buffer_size = 512M
sort_buffer_size = 64M
join_buffer_size = 64M
read_buffer_size = 64M
read_rnd_buffer_size = 128M
myisam_sort_buffer_size = 512M
max_allowed_packet = 128M
thread_cache_size = 8
table_cache = 1024
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 10M
thread_concurrency = 8
local-infile = 0
long_query_time = 4
log_slow_queries
log_long_format
myisam_recover
innodb_buffer_pool_size = 32M
innodb_additional_mem_pool_size = 4M

#
# 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 = 512M
max_allowed_packet = 128M
thread_stack = 128K
#
# * Query Cache Configuration
#
query_cache_limit = 10 M
query_cache_size = 512 M
query_cache_type = 1
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
#log = /var/log/mysql.log
#log = /var/log/mysql/mysql.log
#
# Error logging goes to syslog. This is a Debian improvement :)
#
# Here you can see queries with especially long duration
#log-slow-queries = /var/log/mysql/mysql-slow.log
#
# The following can be used as easy to replay backup logs or for replication.
#server-id = 1
log-bin = /var/log/mysql/mysql-bin.log
# See /etc/mysql/debian-log-rotate.conf for the number of files kept.
max_binlog_size = 10 M
#binlog-do-db = include_database_name
#binlog-ignore-db = include_database_name
#
# * BerkeleyDB
#
# The use of BerkeleyDB is now discouraged and support for it will probably
# cease in the next versions.
skip-bdb
#
# * 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 Feature
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# If you want to enable SSL support (recommended) read the manual or my
# HOWTO in /usr/share/doc/mysql-server/SSL-MINI-HOWTO.txt.gz
# 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 = 100M

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

[isamchk]
key_buffer = 512M
und diese überspiel, erscheint anschließend beim mysql Neustart die Fehlermeldung
hera87:~# /etc/init.d/mysql stop
Stopping MySQL database server: mysqld.
hera87:~# /etc/init.d/mysql start
Starting MySQL database server: mysqld...failed.
Please take a look at the syslog.
/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)'
Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!
Ersetz ich die my.cnf wieder mit der vorherigen und starte mysql, gibts keine Probleme. Es dürfte also nicht an der mysqld.sock, sondern der Config liegen. Wäre schön, wenn mir hier jemand bei der Anpassung der my.cnf helfen könnte - danke im voraus :)

Roger Wilco
Administrator
Administrator
Posts: 5924
Joined: 2004-05-23 12:53

Re: Probleme bei Konfiguration in der my.cnf

Post by Roger Wilco » 2006-07-07 18:46

Schau in deine MySQL Logs. Mein Tipp ist, dass es an den Konfigurationsänderungen für InnoDB liegt...

simcard
Posts: 19
Joined: 2005-08-22 19:29

Re: Probleme bei Konfiguration in der my.cnf

Post by simcard » 2006-07-07 18:59

Hi,
Jul 7 18:33:42 hera87 mysqld[7510]: 060707 18:33:42 /usr/sbin/mysqld: Normal shutdown
Jul 7 18:33:42 hera87 mysqld[7510]:
Jul 7 18:33:43 hera87 mysqld[7510]: 060707 18:33:43 InnoDB: Starting shutdown...
Jul 7 18:33:45 hera87 mysqld[7510]: 060707 18:33:45 InnoDB: Shutdown completed
Jul 7 18:33:45 hera87 mysqld[7510]: 060707 18:33:45 /usr/sbin/mysqld: Shutdown Complete
Jul 7 18:33:45 hera87 mysqld[7510]:
Jul 7 18:33:45 hera87 mysqld_safe[7767]: ended
Jul 7 18:33:56 hera87 mysqld_safe[7824]: started
Jul 7 18:33:56 hera87 mysqld[7828]: Unknown suffix ' ' used for variable 'query_cache_limit' (value '10 M')
Jul 7 18:33:56 hera87 mysqld[7828]: 060707 18:33:56 /usr/sbin/mysqld: Error while setting value '10 M' to 'query_cache_limit'
Jul 7 18:33:56 hera87 mysqld[7828]:
Jul 7 18:33:56 hera87 mysqld_safe[7830]: ended
Jul 7 18:34:02 hera87 /etc/init.d/mysql[7893]: 0 processes alive and '/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf ping' resulted in
Jul 7 18:34:02 hera87 /etc/init.d/mysql[7893]: ^G/usr/bin/mysqladmin: connect to server at 'localhost' failed
Jul 7 18:34:02 hera87 /etc/init.d/mysql[7893]: error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)'
Jul 7 18:34:02 hera87 /etc/init.d/mysql[7893]: Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!
Jul 7 18:34:02 hera87 /etc/init.d/mysql[7893]:
Jul 7 18:34:25 hera87 mysqld_safe[7966]: started
Jul 7 18:34:25 hera87 mysqld[7970]: 060707 18:34:25 InnoDB: Started
Jul 7 18:34:25 hera87 mysqld[7970]: /usr/sbin/mysqld: ready for connections.
Jul 7 18:34:25 hera87 mysqld[7970]: Version: '4.0.24_Debian-10sarge2-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 Source distribution
Jul 7 18:34:26 hera87 /etc/mysql/debian-start[8003]: Checking for crashed MySQL tables.
Was ist an der query_cache_limit Angabe denn falsch, seh da nichts - sorry :? Liegt es überhaupt an der query_cache_limit Angabe?

Roger Wilco
Administrator
Administrator
Posts: 5924
Joined: 2004-05-23 12:53

Re: Probleme bei Konfiguration in der my.cnf

Post by Roger Wilco » 2006-07-07 19:06

Es darf kein Leerzeichen in der Größenangabe vorkommen.

simcard
Posts: 19
Joined: 2005-08-22 19:29

Re: Probleme bei Konfiguration in der my.cnf

Post by simcard » 2006-07-07 19:24

:oops: dankö, nu gehts :) Manchmal kann es echt einfach sein :wink: