Troubleshooting MYSQL : Could not connect: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

1. maybe lost mysql.sock file

You need to give the mysql group ownership of the /var/lib/mysql directory.

#> chown -R mysql:mysql /var/lib/mysql

If you lost your mysql.sock file just make a symlink to the file:

# ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock

ref: http://answers.yahoo.com/question/index?qid=20080704165734AAG757T


2. When connecting to a MySQL server located on the local system, the mysql client connects thorugh a local file called a socket instead of connecting to the localhost loopback address 127.0.0.1. For the mysql client, the default location of this socket file is /tmp/mysql.sock. However, for a variety of reasons, many MySQL installations place this socket file somewhere else like /var/lib/mysql/mysql.sock.

While it is possible to make this work by specifying the socket file directly in the mysql client command

mysql --socket=/var/lib/mysql/mysql.sock ...

it is painful to type this in every time. If you must do so this way (because you don’t have permissions to the file in the solution below), you could create an alias in your shell to make this work (like alias mysql=”mysql –socket=/var/lib/mysql/mysql.sock” depending on your shell).

To make your life easier, you can make a simple change to the MySQL configuration file /etc/my.cnf that will permanently set the socket file used by the mysql client. After making a backup copy of /etc/my.cnf, open it in your favorite editor. The file is divided into sections such as
[mysqld]
datadir=/usr/local/mysql/data
socket=/var/lib/mysql/mysql.sock

[mysql.server]
user=mysql
basedir=/usr/local/mysql

If there is not currently a section called [client], add one at the bottom of the file and copy the socket= line under the [mysqld] section such as:

[client]
socket=/var/lib/mysql/mysql.sock

If there is already a [client] section in the my.cnf file, add or edit the socket line as appropriate. You won’t need to restart your server or any other processes. Subsequent uses of the mysql client will use the proper socket file.

ref: http://www.tech-recipes.com/rx/762/solve-cant-connect-to-local-mysql-server-through-socket-tmpmysqlsock/


3. Okay, so let's answer to this question, once for all.

First of all, you DO NOT WANT to recreate mysql.sock,
that logically makes NO sense at all.

What you REALLY want to do is restart the mysql daemon (mysqld),
which when it is up and running will create a socket for you,
so you can communicate with it.

- Creating a file by hand won't help
- Creating a socket by hand won't help
- None of these "can talk" to the server which is "not running".

The reason why you "lost mysql.sock" is that:
- the mysqld daemon stop or crashed
- a fatal error occurred
- your configuration is wrong /etc/my.cnf
- your permissions are wrong /var/lib/mysql and /var/lib/mysql/mysql ...
=> make sure the owner is mysql:mysql and 775 or similar.
- your mysql data is corrupted: myisamcheck or mysql_install_db

Now, you may say, hey but I tried:
- /etc/init.d/mysqld restart
- /etc/init.d/mysqld start
- mysqld_safe --user=mysql &

and none of those worked...

That's sort of normal, if the server crashed in the first place.

So, what you REALLY need to do is look at the file located in:
/var/log/mysqld.log

like this:
tail -f /var/log/mysqld.log
or
cat /var/log/mysqld.log

figure out the error message, solve that error message
or try to find a work-around for that error on google.

Once that error message is "solved", then you can safely restart mysqld
[no need to reboot], and once the daemon is up and running,
your magical "mysql.sock" file will appear as it did before. =)

The big problem with this is that the "mysql.sock" error message
is not helping at all, and you must "know" that the real error message
is in the mysql log file that's all.

Hope this help.

ref: http://www.linuxforums.org/forum/servers/1451-what-mysql-sock-file-2.html

4. Only delete de log files ib_logfile0, ib_logfile1 and ibdata1 in the /var/lib/mysql directory and then type:

"mysqld_safe --user=mysql &"

and automatically the mysql.sock file will be create and the news logs files.

Wow!!! Do not this if you are using InnoDB as db. Then you'll loose a lot of information. Deleting those files that is.

ref : http://www.linuxforums.org/forum/servers/1451-what-mysql-sock-file.html

5. For self-installed MySQL

If you have downloaded and installed MySQL yourself but are using the pre-installed version of PHP, note that your custom version of MySQL might be configured to use the old MySQL socket location, /tmp/mysql.sock. The version of PHP in this software update uses the newer location /var/mysql/mysql.sock by default.

Therefore, if your PHP scripts are failing to connect to your custom installation of MySQL, this is a likely cause. To correct this, you can modify the PHP configuration file to use the old MySQL socket location:

1. Create /etc/php.ini if it is not present. (You can do so by copying /etc/php.ini.default to /etc/php.ini).
2. Edit the /etc/phi.ini config file, find the [MySQL] section, and change this line:

mysql.default_socket = /var/mysql/mysql.sock

To:

mysql.default_socket = /tmp/mysql.sock

3. Restart the web server to pick up the new PHP settings. It should not be necessary to restart MySQL.

If you subsequently decide to use the version of MySQL that is pre-installed with Mac OS X Server, which uses /var/mysql/mysql.sock, you'll want to either remove that line from /etc/php.ini, or change it back.

ref: http://support.apple.com/kb/HT3077?viewlocale=en_US


6. Since its a prod DB, I need to do this sometime tonight. I will try >su
> cd /etc/init.d
>./mysql stop
>./mysql start

Apart from:
chmod 755

Have you checked that the files are owned by mysql?

Otherwise you should run:
chown -R mysql /your/mysql/datadir

ref : http://forum.percona.com/index.php/m/4324/

MySQL - Import/Export Large MYSQL Databases

When working with MYSQL I often use phpMyAdmin, which is a nice GUI way to manipulate my database. But some operations won't work in phpMyAdmin when the database is too large. In particular, you can't import or export really large databases using phpMyAdmin. So sometimes you need to do things on the command line.

So I thought I'd document some of the command line snippets we use frequently.
In the following, replace
[USERNAME] with your mysql username,
[DBNAME] with your database name,
[/path_to_file/DBNAME] with the path and name of the file used for the database dump, and
[/path_to_mysql/] with the path to mysql bin (like /Applications/MAMP/Library/bin/).


Copy/Export a Large Database
 
MYSQL has no 'Copy' function. You create a copy by dumping the database with mysqldump. To dump the database and gzip it at the same time, use the following. This will prompt you for your password.

 

mysqldump -u [USERNAME] -p [DBNAME] | gzip > [/path_to_file/DBNAME].sql.gz

Import a Large Database

  

If you want to replace the database with a fresh dump created by the above process, do the following.
First, unzip the file.

 

gzip -d [/path_to_file/DBNAME].sql.gz

Get to a mysql prompt (you will be asked for your password.)

 

[/path_to_mysql/]mysql -u [USERNAME] -p

Then do the following to wipe out the old database and replace it with the new dump:

 

SHOW DATABASES;
DROP DATABASE [DBNAME];
CREATE DATABASE [DBNAME];
USE [DBNAME];
SOURCE [/path_to_file/DBNAME].sql;

Conditional Dumps

 

Sometimes the search index is huge and you want to omit it from the dump. Do so with:

 

mysqldump -u [USERNAME] -p [DBNAME] --ignore-table=[DBNAME].search_index | gzip > [/path_to_file/DBNAME].sql.gz

There are actually a number of tables you could exclude, like the sessions table, the watchdog table and all the cache* tables.

But if you use the above technique to destroy and recreate the database after doing this, you will be missing all those excluded tables. So you will want to do a two step process instead:

First, create a backup with ONLY the table information, no data.

mysqldump -u [USERNAME] -p [DBNAME] --no-data | gzip > [/path_to_file/DBNAME].info.sql.gz

Then create a backup, including only data from the tables you need.

 

[path_to_mysql/]mysqldump -u [USERNAME] -p [DBNAME]  --no-create-info --ignore-table=[DBNAME].search_index --ignore-table=[DBNAME].cache --ignore-table=[DBNAME].cache_block --ignore-table=[DBNAME].cache_content --ignore-table=[DBNAME].cache_filter --ignore-table=[DBNAME].cache_form --ignore-table=[DBNAME].cache_menu --ignore-table=[DBNAME].cache_mollom --ignore-table=[DBNAME].cache_page --ignore-table=[DBNAME].cache_pathdst --ignore-table=[DBNAME].cache_pathsrc --ignore-table=[DBNAME].cache_views | gzip > [/path_to_file/DBNAME].data.sql.gz;

Well that's a lot of typing. Wouldn't it be nice if there was a wildcard we could use instead of typing out all those cache_ tables? Well there is!! You can do:

 

[path_to_mysql/]mysqldump -u [USERNAME] -p [DBNAME]  --no-create-info --ignore-table=[DBNAME].search_index --ignore-table=[DBNAME].cache% | gzip > [/path_to_file/DBNAME].data.sql.gz;

After doing this, just import the two files as above, first the one with only the table info, and then the data. Result, a (relatively) small database with all the optional tables emptied out.

Note that the wildcard trick above is not documented anywhere that I can see, so you'll want to test that it works in your setup.

Ref.: Thanks toKaren Stevenson

http://www.lullabot.com/blog/importexport-large-mysql-databases

MySQL - Installation layout

The installation layout of the directory is as shown in the following table:

Directory	Contents of Directory
bin	Client programs and the mysqld server
data	Log files, databases
docs	Manual in Info format
include	Include (header) files
lib	Libraries
man	Unix manual pages
mysql-test	MySQL test suite
scripts	Contains the mysql_install_db script
share/mysql	Error message files
sql-bench	Benchmarks
support-files	Scripts and sample configuration files
/tmp/mysql.sock	The location of the MySQL Unix socket

During the package installer process, a symbolic link from /usr/local/mysql to the version/platform specific directory created during installation will be created automatically.

MySQL - Troubleshooting - Can’t connect to local MySQL server through socket /tmp/mysql.sock

Yesterday is a big trouble and very urgent. Do not know why, the are conflict in processing when my previous version for mysql is still running and cannot being kill. It conflict in --port=3306 so the website cannot find the socket.

0:01.14 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql --log-error=/usr/local/mysql/data/mdce1corpsvr03.xxx.com.my.err --pid-file=/usr/local/mysql/data/mdce1corpsvr03.xxx.com.my.pid --socket=/var/mysql/mysql.sock --port=3306


I detect it when check it out :

ps xa | grep mysql

ps -aef | grep mysql

the we can check it using:

netstat -ln | grep mysql

What I am doing yesterday is:

kill - 9 PIDNo - cannot
kill -15 PIDNo - the server restarted

so no others choice.

1. first and important - BACKUP the database first!!! and copy in /users/admin folder

mysqldump -p [db name] > [backup.dmp]

 
2. remove the previous version that disturbing the process

sudo rm -rf /usr/local/mysql*

3. install new version of mysql
4. relink the socket

sudo ln -s /var/mysql/mysql.sock /tmp/mysql.sock


5. create and restore the database using mysqldump

mysql> CREATE database xxxxa;mysql -u #username# -p #xxxxa# < #backup.dmp#

6. set password for -u root

$ mysqladmin -u root password NEWPASSWORD

7. change database name at dbconnect.php



Refering to  :

Info
mysqld, also known as MySQL Server, is the main program that does most of the work in a MySQL installation.

The mysqld program has many options that can be specified at startup. For a complete list of options, run this command:

shell> mysqld --verbose --help 

When connecting to a MySQL server located on the local system, the mysql client connects thorugh a local file called a socket instead of connecting to the localhost loopback address 127.0.0.1. For the mysql client, the default location of this socket file is /tmp/mysql.sock.
 
Link :
http://dev.mysql.com/doc/refman/5.1/en/mysqld.html
http://www.tech-recipes.com/rx/762/solve-cant-connect-to-local-mysql-server-through-socket-tmpmysqlsock/

Uninstalling MySQL on Mac OS X Leopard

To uninstall MySQL and completely remove it (including all databases) from your Mac do the following:

• Use mysqldump to backup your databases to text files!
• Stop the database server
• sudo rm /usr/local/mysql
• sudo rm -rf /usr/local/mysql*
• sudo rm -rf /Library/StartupItems/MySQLCOM
• sudo rm -rf /Library/PreferencePanes/My*
• edit /etc/hostconfig and remove the line MYSQLCOM=-YES-
• rm -rf ~/Library/PreferencePanes/My*
• sudo rm -rf /Library/Receipts/mysql*
• sudo rm -rf /Library/Receipts/MySQL*

The last two lines are particularly important as otherwise, you can't install an older version of MySQL even though you think that you've completely deleted the newer version!

Link :

http://akrabat.com/computing/uninstalling-mysql-on-mac-os-x-leopard/
http://obscuredclarity.blogspot.com/2009/08/uninstall-mysql-on-mac-os-x.html

Unix Post-Installation Procedures

Link :  http://dev.mysql.com/doc/refman/5.1/en/unix-post-installation.html

Starting and Troubleshooting the MySQL Server

Link : http://dev.mysql.com/doc/refman/5.1/en/starting-server.html

How to Protect or Change the MySQL Unix Socket File

Link : http://dev.mysql.com/doc/refman/5.1/en/problems-with-mysql-sock.html - I am not trying this yet.

MySQL - Installation on Mac

This is some of the way i ask Mr. Google how to install the mySql on Mac.

1. getting-mysql-mac-os-x-105-leopard-togetherfinally

http://jwopitz.wordpress.com/2007/12/09/getting-mysql-mac-os-x-105-leopard-togetherfinally/


2. Installing MySQL on Mac OS X

http://dev.mysql.com/doc/mysql-macosx-excerpt/5.1/en/mac-os-x-installation.html


3. Installing MySQL from Generic Binaries on Unix/Linux

http://dev.mysql.com/doc/refman/5.1/en/installing-binary.html


4. Installing MySQL Using the Installation Package

http://dev.mysql.com/doc/mysql-macosx-excerpt/5.1/en/mysql-installation-macosx-pkg.html

+ Installing and Using the MySQL Preference Pane

http://dev.mysql.com/doc/mysql-macosx-excerpt/5.1/en/mysql-installation-macosx-prefpane.html


I try option no.4 and solve my problem. Thanks Mr. Google.

MySQL - Change Root Password

How do I change MySQL root password under Linux, FreeBSD, OpenBSD and UNIX like operating system over ssh / telnet session?

Setting up mysql password is one of the essential tasks. By default root user is MySQL admin account. Please note that the Linux / UNIX login root account for your operating system and MySQL root are different. They are separate and nothing to do with each other (indeed some admin removes root account and setup admin as mysql super user).

mysqladmin command to change root password

If you have never set a root password for MySQL, the server does not require a password at all for connecting as root. To setup root password for first time, use mysqladmin command at shell prompt as follows:

$ mysqladmin -u root password NEWPASSWORD

However, if you want to change (or update) a root password, then you need to use following command

$ mysqladmin -u root -p'oldpassword' password newpass

For example, If old password is abc, and set new password to 123456, enter:

$ mysqladmin -u root -p'abc' password '123456'

keep note = no spacebar between -p'abc' 

Change MySQL password for other user

To change a normal user password you need to type (let us assume you would like to change password for vivek):

$ mysqladmin -u vivek -p oldpassword password newpass

Changing MySQL root user password using MySQL sql command

This is another method. MySQL stores username and passwords in user table inside MySQL database. You can directly update password using the following method to update or change password for user vivek:
1) Login to mysql server, type following command at shell prompt:

$ mysql -u root -p

2) Use mysql database (type command at mysql> prompt):

mysql> use mysql;

3) Change password for user vivek:

mysql> update user set password=PASSWORD("NEWPASSWORD") where User='vivek';

4) Reload privileges:

mysql> flush privileges;
mysql> quit

MySQL - Restore database

There are 2 ways to restore your MySQL database from sql dump file.


1st way to restore mysql database from sql dump file is using mysql web control panel – phpMyAdmin
- Log into phpMyAdmin.
- Select your preference database on the left database navigation drop down list.
- Click on Import tab on the top.
- Select your sql dumb file at File to import
- Then select your mysql database charset (ex: Latin1, utf-8)
- and click GO and it’s done!

Do not use phpMyAdmin to import or restore your MySQL database if your MySQL database file is large. This is because, phpMyAdmin has limit on total upload size which depend on php setting. Besides, there is also maximum execution time which may cause browser to time out.

The solution to restore large mysql database from sql dump file is using unix/linux shell command.

To restore mysql database from a dump file, just type the command below:-

mysql -u #username# -p #database# < #dump_file#

Of course you need to replace #username# to your database username and #database# to your target database. and rename #dump_file# to your dump file file name (Ex: dump.sql) Once you enter the command, the linux/unix shell will prompt you for your database user password, just key in your database password and you are done.


Referring from:
http://www.techiecorner.com/31/how-to-restore-mysql-database-from-sql-dump-file/

MySQL - Create database

The command is simple just write mysqladmin in a CLI followed by the database name you want to create

# mysqladmin -u root -p create building
# mysql


 Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.82-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW databases;
+----------+
| Database |
+----------+
| mysql    |
| building|
| test     |
+----------+
2 rows in set (0.00 sec)
mysql>

You can also type the query in mysql> prompt like this

mysql> CREATE database building;
Query OK, 1 row affected (0.00 sec)

To show available databases in mysql use the command show databases on mysql> prompt.
Now use the database by typing USE building and then type SHOW tables to see what tables are available in the database

mysql> USE building;
Database changed

mysql> SHOW tables;
Empty set (0.00 sec)

MySQL - Create/password user

1. Creating a new user. Login as root. Switch to the MySQL db. Make the user. Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES('%','username',PASSWORD('password'));
mysql> flush privileges;

2. Change a users password from unix shell.

# [mysql dir]/bin/mysqladmin -u username -h hostname.com.my -p password 'new-password'

MySQL - Backup database

The mysqldump utility can be found in the /usr/local/bin directory on Unix/Linux systems where MySQL is installed.

mysqldump -p [db name] > [db name.dmp]

MySQL - Basic command for support

This is a list of MySQL commands that I use time and time again.

1. to login

mysql -u root -p
  

2. to list all databases

mysql> show databases;
   

3. switch to a databases

mysql> use [db name];

      
4. to see all table in database

mysql> show tables;

    

5. to see database's field formats

mysql> describe [table name];