MAMP: ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)

I instaled MAMP and it worked great. However, when I tried to access mysql from the shell I was getting

$ mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)


However, using a full path to MAMP binary worked.
$ /Applications/MAMP/Library/bin/mysql -u root -p


MAMP’s mysql works with another socket file which is located at /Applications/MAMP/tmp/mysql/mysql.sock

All you have to do is:
sudo ln -s /Applications/MAMP/tmp/mysql/mysql.sock /tmp/mysql.sock

UPDATE: As it turns out the above soft link gets erased by the system, so you need to run the command every once in a while. At one point it got too frustrating so I figured out a permanent way to take care of this problem. Open /etc/my.cnf and add/edit these lines:
[mysqld]
socket=/Applications/MAMP/tmp/mysql/mysql.sock

[client]
socket=/Applications/MAMP/tmp/mysql/mysql.sock

Instead of telling MAMP which socket file to use, you are instructing the mysql client to use MAMP’s default socket.

Let me know if this works for you.

 

Referenced by: http://boriskuzmanovic.wordpress.com/2008/11/26/mamp-error-2002-hy000-cant-connect-to-local-mysql-server-through-socket-tmpmysqlsock-2/

 

Recover MySQL root Password

You can recover MySQL database server password with following five easy steps.

Step # 1: Stop the MySQL server process.

Step # 2: Start the MySQL (mysqld) server/daemon process with the –skip-grant-tables option so that it will not prompt for password.

Step # 3: Connect to mysql server as the root user.

Step # 4: Setup new mysql root account password.

Step # 5: Exit and restart the MySQL server.

Here are commands you need to type for each step (login as the root user):

Step # 1 : Stop mysql service

# /etc/init.d/mysql stop
Output:

Stopping MySQL database server: mysqld.

Step # 2: Start to MySQL server w/o password:

# mysqld_safe --skip-grant-tables &
Output:

[1] 5988
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[6025]: started

Step # 3: Connect to mysql server using mysql client:

# mysql -u root
Output:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Step # 4: Setup new MySQL root user password

mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit

Step # 5: Stop MySQL Server:

# /etc/init.d/mysql stop
Output:

Stopping MySQL database server: mysqld
STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld_safe[6186]: ended

[1]+  Done                    mysqld_safe --skip-grant-tables

Step # 6: Start MySQL server and test it

# /etc/init.d/mysql start
# mysql -u root -p

 

MySQL Case-Sensitive Query

MySQL queries are not case-sensitive by default. Following is a simple query that is looking for ‘value’. However it will return ‘VALUE’, ‘value’, VaLuE’, etc…

SELECT *  FROM `table` WHERE `column` = ‘value’

The good news is that if you need to make a case-sensitive query, it is very easy to do:

SELECT *  FROM `table` WHERE BINARY `column` = ‘value’

 

Data truncation: Incorrect datetime value in MySQL

Message Error in MySQL:
“Data truncation: Incorrect datetime value: ’1969-12-31 16:00:00′ for column ‘LAST_LOGIN’ at row 1″

Resolution:

Now, open the my.ini file located at c:program files/MySQL/MySQL
Server X.x/my.ini.
Add a # at the begining of the line: sql-mode=”STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”
Type

sql_mode=” on the next line and save the file.
restart mysql

 

Select data that is not match data in another table in MySQL

We have mock up data in both table as below:

Table1
ID	Name
1	Mr. A
2	Ms. B
3	Mrs. C

—————————————————————

Table2
ID	Table1ID	Name
1	3	ABC Beer
2	1	Tiger Beer

And we want to select data in table1 that is not avaiable in table2, so when you want to do as I said.

You should use:

SELECT table1.*

FORM table1 LEFT JOIN table2 ON table1.ID = table2.Table1ID

WHERE table2.Table1ID is null

————————————————————–

Result:

Result
ID	Name
2	Ms. B