[10.6 Snow Leopard] ERROR 2002 (HY000): Can't connect to local MySQL server through socket

[Mitglied 26876]

Hallöchen liebe Leute!

Also mein Problem mit 10.6.8 Server betrifft MySQL, den built in. Ich hatte das Problem zwei Tage vorher unter 10.4.11 Server erfolgreich gelöst, weiß nur nicht mehr, wie.

(Natürlich habe ich schon gesucht und auch viel gefunden, allerdings nichts, was mich zur Problemlösung brachte.)

Das Problem:

$ mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/mysql/mysql.sock' (2)

$ ls /var/mysql/mysql.sock        mysql_service.log

Der Socket scheint also vorhanden… Mal die Service.log:

$ cat /var/mysql/mysql_service.log                                 
120702 13:19:04 [Warning] Setting lower_case_table_names=2 because file system for /Volumes/Data 2/ServiceData/MySQL/ is case insensitive
InnoDB: The InnoDB memory heap has been disabled.
InnoDB: Mutex and rw_lock use GCC atomic builtins.
120702 13:19:04  InnoDB: Started; log sequence number 0 43655
120702 13:19:04 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist

$ mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/mysql/mysql.sock' (2)

$ cat /var/mysql/mysql_service.log
120702 13:19:04 [Warning] Setting lower_case_table_names=2 because file system for /Volumes/Data 2/ServiceData/MySQL/ is case insensitive
InnoDB: The InnoDB memory heap has been disabled.
InnoDB: Mutex and rw_lock use GCC atomic builtins.
120702 13:19:04  InnoDB: Started; log sequence number 0 43655
120702 13:19:04 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
120702 13:19:14 [Warning] Setting lower_case_table_names=2 because file system for /Volumes/Data 2/ServiceData/MySQL/ is case insensitive
InnoDB: The InnoDB memory heap has been disabled.
InnoDB: Mutex and rw_lock use GCC atomic builtins.
120702 13:19:14  InnoDB: Started; log sequence number 0 43655
120702 13:19:14 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
120702 13:19:24 [Warning] Setting lower_case_table_names=2 because file system for /Volumes/Data 2/ServiceData/MySQL/ is case insensitive
InnoDB: The InnoDB memory heap has been disabled.
InnoDB: Mutex and rw_lock use GCC atomic builtins.
120702 13:19:24  InnoDB: Started; log sequence number 0 43655
120702 13:19:24 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
120702 13:19:35 [Warning] Setting lower_case_table_names=2 because file system for /Volumes/Data 2/ServiceData/MySQL/ is case insensitive
InnoDB: The InnoDB memory heap has been disabled.
InnoDB: Mutex and rw_lock use GCC atomic builtins.
120702 13:19:35  InnoDB: Started; log sequence number 0 43655
120702 13:19:35 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist

Hier mal ein erneuter Installationsversuch:

$ sudo mysql_install_db 
Password:
Installing MySQL system tables...
120702 13:11:47 [Warning] Setting lower_case_table_names=2 because file system for /var/mysql/ is case insensitive
OK
Filling help tables...
120702 13:11:47 [Warning] Setting lower_case_table_names=2 because file system for /var/mysql/ is case insensitive
OK


To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system


PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h qs-server.com password 'new-password'


Alternatively you can run:
/usr/bin/mysql_secure_installation


which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.


See the manual for more instructions.


You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &


You can test the MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl


Please report any problems with the /usr/bin/mysqlbug script!


The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com

$ mysqladmin ping
mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/var/mysql/mysql.sock' (2)'
Check that mysqld is running and that the socket: '/var/mysql/mysql.sock' exists!

$ sudo ps -A|grep mysql
45791 ??         0:00.06 /bin/sh /Applications/XAMPP/xamppfiles/bin/mysqld_safe --datadir=/Applications/XAMPP/xamppfiles/var/mysql --pid-file=/Applications/XAMPP/xamppfiles/var/mysql/qs-server.com.pid
45892 ??         0:00.98 /Applications/XAMPP/xamppfiles/sbin/mysqld --basedir=/Applications/XAMPP/xamppfiles --datadir=/Applications/XAMPP/xamppfiles/var/mysql --user=nobody --log-error=/Applications/XAMPP/xamppfiles/var/mysql/qs-server.com.err --pid-file=/Applications/XAMPP/xamppfiles/var/mysql/qs-server.com.pid --socket=/Applications/XAMPP/xamppfiles/var/mysql/mysql.sock --port=3307
40867 ttys000   78:00.06 sudo /usr/local/mysql/bin/safe_mysqld
47169 ttys000    0:00.00 grep mysql

Ich habe aktuell XAMPP auf 3307 laufen, weil ich dringend einen MySQL Server brauche, WordPress funktioniert auch, allerdings bekomme ich bei meinen eigenen PHP Dateien bei einer Abfrage folgendes:

SELECT * FROM apiTokens WHERE token =''[COLOR=#000000]No such file or directory[/COLOR]

(Interessant ist auch hier, dass er den GET-Parameter ignoriert und $token leer bleibt.)

Beide Systeme (mein eigenes und WordPress) liefen super auf einem gemieteten Server! Nur jetzt muss ich kurzfristig selbst hosten und bekomme da so das ein oder andere Problem, wie man sieht.

Kann mir da jemand helfen?

 

[mintracer]

es liegt Warscheinlich am Leerzeichen in deinem Volumenpfad --> data 2

MySql mag anscheinend keine Leerzeichen in Pfaden...

Quelle:

https://discussions.apple.com/thread/2223114?start=0&tstart=0

 

[Mitglied 26876]

Mh, also ich habe das Verzeichnis mal auf meinen Homeordner gelegt, wirklich was gebracht hat das bisher nicht. Der Fehler bleibt. Gibt es eine Möglichkeit das GUI-Setup von MySQL noch mal durchzugehen? Oder sollte ich einfach mal den Server komplett neustarten?

Ich weiß nicht genau warum, aber jetzt ist der Socket weg:

$ mysqladmin ping  
mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/var/mysql/mysql.sock' (2)'
Check that mysqld is running and that the socket: '/var/mysql/mysql.sock' exists!

$ ls /var/mysql/
mysql            mysql_service.log    test

EDIT: Der Reboot brachte leider kein Wunder…

 

[mintracer]

Der Herr im Apple Support Forum hat seinen Mysql Server neu installiert... nicht nur den Ordner verschoben... Vielleicht hilft das...

 

[Mitglied 26876]

$ sudo mysql_install_db --user=q --ldata=/Users/Q/MySQL/ --force
Installing MySQL system tables...
120702 14:14:24 [Warning] Setting lower_case_table_names=2 because file system for /Users/Q/MySQL/ is case insensitive
OK
Filling help tables...
120702 14:14:24 [Warning] Setting lower_case_table_names=2 because file system for /Users/Q/MySQL/ is case insensitive
OK


To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system


PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h qs-server.com password 'new-password'


Alternatively you can run:
/usr/bin/mysql_secure_installation


which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.


See the manual for more instructions.


You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &


You can test the MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl


Please report any problems with the /usr/bin/mysqlbug script!


The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com
qs-server:~ Q$ mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/mysql/mysql.sock' (2)

Ist das eine 'richtige' Neuinstallation, oder muss ich noch etwas anderes machen?
Im Notfall muss ich halt 10.6.8 Server neuinstallieren…

 

[QuickMik]

schon mal mit webmin versucht?

 

[Mitglied 26876]

Das sieht ja nach Arbeit aus, die Installation! :O

Aber ich frage mich ja immer noch, warum mein WordPress Blog funktioniert (über XAMPP) und der Rest nicht! :/

 

[martinv2]

Zeig mal die Ausgabe von

ps -ef | grep mysql

(Kann aber auch sein, dass ich danebenliege - ich habe eine selbstgebaute mariadb...)

 

[Mitglied 26876]

Sieht aus, als würde nur XAMPP wirklich laufen.

$ ps -ef | grep mysql
    0  2991     1   0   0:00.05 ??         0:00.08 /bin/sh /Applications/XAMPP/xamppfiles/bin/mysqld_safe --datadir=/Applications/XAMPP/xamppfiles/var/mysql --pid-file=/Applications/XAMPP/xamppfiles/var/mysql/qs-server.com.pid
   -2  3142  2991   0   0:01.38 ??         0:09.84 /Applications/XAMPP/xamppfiles/sbin/mysqld --basedir=/Applications/XAMPP/xamppfiles --datadir=/Applications/XAMPP/xamppfiles/var/mysql --user=nobody --log-error=/Applications/XAMPP/xamppfiles/var/mysql/qs-server.com.err --pid-file=/Applications/XAMPP/xamppfiles/var/mysql/qs-server.com.pid --socket=/Applications/XAMPP/xamppfiles/var/mysql/mysql.sock --port=3307
  501 14335 14323   0   0:00.00 ttys000    0:00.00 grep mysql

Es sieht weiterhin auch aus, als könnte ich den Datenbankort nicht ändern. Denn trotz neuem Pfad steht in der Service.log immer wieder der bei der Server-Installation gewählte Pfad.

$ sudo /usr/sbin/serveradmin settings mysql:databaseLocation = /var/mysql/

Password:
mysql:databaseLocation = "/var/mysql/"

$ sudo /usr/sbin/serveradmin start mysql
mysql = _empty_dictionary

$ sudo serveradmin status mysql
mysql:state = "STARTING"

Die Service.log sagt immer wieder:

120705 13:25:39 [Warning] Can't create test file /Volumes/Data 2/ServiceData/MySQL/qs-server.lower-test
120705 13:25:40 [Warning] Can't create test file /Volumes/Data 2/ServiceData/MySQL/qs-server.lower-test
^G/usr/libexec/mysqld: Can't change dir to '/Volumes/Data 2/ServiceData/MySQL/' (Errcode: 2)
120705 13:25:40 [ERROR] Aborting


120705 13:25:40 [Note] /usr/libexec/mysqld: Shutdown complete

my.cnf

# Example MySQL config file for very large systems.
#
# This is for a large system with memory of 1G-2G where the system runs mainly
# MySQL.
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /var/mysql) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.


# The following options will be passed to all MySQL clients
[client]
#password       = your_password
port            = 3306
socket          = /var/mysql/mysql.sock


# Here follows entries for some specific programs


# The MySQL server
[mysqld]
port            = 3306
socket          = /var/mysql/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
skip-thread-priority


# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking


# Disable Federated by default
skip-federated
# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin


# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id       = 1


# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
#    the syntax is:
#
#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
#    where you replace <host>, <user>, <password> by quoted strings and
#    <port> by the master's port number (3306 by default).
#
#    Example:
#
#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
#    MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
#    start replication for the first time (even unsuccessfully, for example
#    if you mistyped the password in master-password and the slave fails to
#    connect), the slave will create a master.info file, and any later
#    change in this file to the variables' values below will be ignored and
#    overridden by the content of the master.info file, unless you shutdown
#    the slave server, delete master.info and restart the slaver server.
#    For that reason, you may want to leave the lines below untouched
#    (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id       = 2
#
# The replication master for this slave - required
#master-host     =   <hostname>
# The username the slave will use for authentication when connecting
# to the master - required
#master-user     =   <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password =   <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port     =  <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin


# Point the following paths to different dedicated disks
#tmpdir         = /tmp/
#log-update     = /path-to-dedicated-directory/hostname


# Uncomment the following if you are using BDB tables
#bdb_cache_size = 384M
#bdb_max_lock = 100000


# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/mysql/
#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
#innodb_log_group_home_dir = /var/mysql/
#innodb_log_arch_dir = /var/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 384M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 100M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50


[mysqldump]
quick
max_allowed_packet = 16M


[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M


[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M


[mysqlhotcopy]
interactive-timeout

 

[martinv2]

XAMPP verlegt den Socket nach /Applications/XAMPP/xamppfiles/var/mysql/mysql.sock - vielleicht trägst Du den mal in /etc/my.cnf ein, damit mysql(admin) ihn auch dort sucht.

Oder Du konfigurierst XAMPP so um, dass es den Socket in /var/mysql/mysql.sock anlegen lässt; sorry, ich hab' keine Ahnung, wie.

Oder Du legst /var/mysql/mysql.sock als symbolischen Link auf den XAMPP-Socket an:

sudo ln -s /Applications/XAMPP/xamppfiles/var/mysql/mysql.sock /private/var/mysql/mysql.sock

 

[Mitglied 26876]

Das ist natürlich eine Option, allerdings wollte ich eigentlich XAMPP runterschmeißen, sobald der native SQL-Server wieder funktioniert.
Oder soll ich einfach den Pfad in der cnf eintragen und damit weiter leben? Was meint ihr?


EDIT:
Ich hab die mal symbolisch Verlinkt und seit einem Reboot habe ich jetzt den Fehler in der Service.log und XAMPP läuft nun auch nicht mehr, weil er meint, es laufe schon ein SQL-Server

2012-07-05 17:41:55 Europe/Berlin [I]  Starting MySQL Service
2012-07-05 17:41:55 Europe/Berlin [I]  Starting MySQL daemon...
2012-07-05 17:41:55 Europe/Berlin [I]  MySQL: verifying/installing default my.cnf
2012-07-05 17:41:55 Europe/Berlin [I]  MySQL startup options: socket=/var/mysql/mysql.sock; datadir=/var/mysql/; pid-file=/var/mysql/qs-server.com.pid; network=YES
2012-07-05 17:42:26 Europe/Berlin [E]  {
    NSLocalizedDescription = "MySQL daemon start up timed-out.";
}

EDIT2:

Nachdem ich der Plist den entsprechenden Wert, der fehlte, hinzugefügt hatte, funktioniert der interne MySQL Server wieder!! Nur bekomme ich keine Verbindung von außerhalb oder via SSH-Tunnel mit SequelPro hin. Wenn ich die Standard Verbindung versuche, sagt er mir, dass ich von diesem Ort nicht zugriffsberechtigt sei. Obwohl ich 'Netzwerkverbindungen erlauben' angehakt habe.
Nicht mal auf dem Server selbst kann SequelPro zugreifen.
Jemand eine Idee?


EDIT3:

Ok, das war einfach. Das root-Passwort war nicht mehr aktiv, obwohl ich es noch mal neu gesetzt habe. Es geht nun alles!