mysql_secure_installation "Can't connect to local MySQL server through socket"

Question

When I am invoking the file mysql_secure_installation I get an error like

    [mysqk123@InstallZeMo bin]$ ./mysql_secure_installation 
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!


In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
Enter current password for root (enter for none): 

but the given sock file in my.cnf file is /home/mysqk123/tmp/mysql.sock and I am using redhat linux 5.3 and mysql 5.1.47

I know that the tasks performed by mysql_secure_installation script can be performed manually but here I have to run the script [ not allowed to do the task manually ]

Ovida

Answer 1

Since this utility is meant to be used over a standard installation, and because it seems to accept no parameter, I see very few options:

• temporarily configure MySQL to use the default socket location for the time of the procedure (alternatively, a symbolic link from the default location to your custom location might just work).
• modify the script mysql_secure_installation so that it uses your custom location. If I understand it correctly, the script creates a temporary configuration file as ./.my.cnf.$$ ($$ is the pid) around line 46 in the make_config subroutine.

Modify as follows: (disclaimer: not tested :)

make_config() {
    echo "# mysql_secure_installation config file" >$config
    echo "[mysql]" >>$config
    echo "user=root" >>$config
    echo "password='$rootpass'" >>$config
    # add the line below
    echo "socket=/home/mysqk123/tmp/mysql.sock" >>$config
}

Again, this script is meant to be used on a standard, out-of-the box installation. Tell your boss (or your client) that if you were able to configure MySQL to use a non-standard socket location, you are also able to run by hand simple commands such as deleting accounts and setting passwords.

Luke

As of MySQL 5.7.2, mysql_secure_installation.sh will be replaced by a binary that accepts options on the command line, in particular --socket=path. I haven't tested it yet, but I will update the answer when I know more about it. – Luke

Yes I used the same fix last night after I had to recover my root password. I must have a standard installation because it worked fine the first time I ran it on Saturday. It was only on the subsequent run that I found it preferring the "/tmp/mysql.sock" path. What changed?! Certainly not the my.cnf file. And I did NOT find that socket path in the script itself. What gives ? – Bacteriophage

Answer 2

Make sure you start mysql or mariadb, for example:

sudo systemctl start mariadb.service
sudo systemctl enable mariadb.service

Fungible

Answer 3

The MySQL Socket declaration should be located under [mysqld] in your my.cnf (located at /etc/mysql/my.cnf in Debian flavours). MySQL Socket information can also be found using the following command:

mysql> show variables like 'socket';
+-----------------------------------------+-------------------------------+
| Variable_name                           | Value                         |
+-----------------------------------------+-------------------------------+
| socket                                  | /yourpath/mysql.sock          |
+-----------------------------------------+-------------------------------+
1 rows in set (0.00 sec)

Update the environment variable for the current session and execute the command, eg:

export MYSQL_UNIX_PORT=/home/mysql123/tmp/mysql.sock
./mysql_secure_installation

Rachele

And where do you get mysql.sock path from – Wesla

fixed my issue with mariadb-secure-installation due to moving mysql datadir before running mariadb-secure-installation. Next time, stick with the install flow and run mariadb-secure-installation immediately after mariadb install... THEN move the datadir. – Region

Answer 4

You can also make a symlink to the socket file and then remove it:

ln -s /home/mysqk123/tmp/mysql.sock /var/lib/mysql/mysql.sock
mysql_secure_installation # Do your stuff
rm -rf /var/lib/mysql/mysql.sock

Puccini

Answer 5

I had this same error happen, and it turns out I had stopped the MySQL service and forgotten to restart it. So you may want to check the status of the mysql service using a command like:

mysqladmin -u root -p status

If you get an error, it means MySQL is not running, and you can start the MySQL service. Or, if you prefer, you can recklessly attempt to start the service without first checking its status (which won't work if it's already running) using one of these commands:

/etc/init.d/mysql start
service mysql start

Replace mysqld with mysqld if your system is using that version of MySQL.

Inhabitant

Answer 6

DO THIS if you are using mariadb

Before TO RUN : mysql_secure_installation

Step 1: sudo systemctl stop mariadb
Step 2: sudo systemctl start mariadb
Step 3: mysql_secure_installation

It will ask root password and you can simply press Enter and set your new root password.

Norry

I get this on step 1: System has not been booted with systemd as init system (PID 1). Can't operate – Unreeve

Answer 7

mysql_secure_instllation creates it's own (hidden and temporary) my.cnf file in the current working directory. With a vanilla install this shouldn't cause issues, but if you have configured your SQL server to put the socket in a non-standard location, this will cause issues.

To work around this, ensure you have properly configured your /etc/my.cnf file with the appropriate socket=/dir/to/mysql/socket.sock option. With this configured, tell mysql_secure_installation to use this file with the --defaults-file argument as such:

mysql_secure_installation --defaults-file=/etc/my.cnf

Darbydarce

Answer 8

mysql_secure_installation --defaults-file=/etc/my.cnf

Sialkot