ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock (111)'
Hello folks,
I have the following problem:
sometimes, at random period of time mysqld service is failed.
then I have to restore the password it with mysqld_safe with --skip-grant-tables
I don't want to reset it at random period of time.
I need to find out the reason why is goes down.
I run Fedora Core 20.
/*************************
[root@localhost ~]# yum list mariadb*
Loaded plugins: langpacks, refresh-packagekit
Installed Packages
mariadb.x86_64 1:5.5.38-3.fc20 @updates
mariadb-devel.x86_64 1:5.5.38-3.fc20 @updates
mariadb-embedded.x86_64 1:5.5.37-1.fc20 @updates
mariadb-libs.x86_64 1:5.5.38-3.fc20 @updates
mariadb-server.x86_64 1:5.5.38-3.fc20 @updates
Available Packages
mariadb.i686 1:5.5.38-3.fc20 updates
mariadb-bench.x86_64 1:5.5.38-3.fc20 updates
mariadb-devel.i686 1:5.5.38-3.fc20 updates
mariadb-embedded.i686 1:5.5.38-3.fc20 updates
mariadb-embedded.x86_64 1:5.5.38-3.fc20 updates
mariadb-embedded-devel.i68
mariadb-embedded-devel.x86
mariadb-galera-common.x86_
mariadb-galera-server.x86_
mariadb-libs.i686 1:5.5.38-3.fc20 updates
mariadb-test.x86_64 1:5.5.38-3.fc20 updates
[root@localhost ~]# yum list mysql*
Loaded plugins: langpacks, refresh-packagekit
Available Packages
MySQL-python.x86_64 1.2.3-8.fc20 fedora
MySQL-zrm.noarch 3.0-3.fc20 updates
mysql++.i686 3.1.0-13.fc20 fedora
mysql++.x86_64 3.1.0-13.fc20 fedora
mysql++-devel.i686 3.1.0-13.fc20 fedora
mysql++-devel.x86_64 3.1.0-13.fc20 fedora
mysql++-manuals.x86_64 3.1.0-13.fc20 fedora
mysql-connector-java.noarc
mysql-connector-odbc.x86_6
mysql-connector-python.noa
mysql-connector-python3.no
mysql-mmm.noarch 2.2.1-9.fc20 fedora
mysql-mmm-agent.noarch 2.2.1-9.fc20 fedora
mysql-mmm-monitor.noarch 2.2.1-9.fc20 fedora
mysql-mmm-tools.noarch 2.2.1-9.fc20 fedora
mysql-proxy.i686 0.8.4-1.fc20 updates
mysql-proxy.x86_64 0.8.4-1.fc20 updates
mysql-proxy-devel.i686 0.8.4-1.fc20 updates
mysql-proxy-devel.x86_64 0.8.4-1.fc20 updates
mysql-utilities.noarch 1.3.6-1.fc20 updates
mysqlnd-qc-panel.noarch 1.2.0-4.fc20 updates
mysqlreport.noarch 3.5-11.fc20 fedora
mysqltuner.noarch 1.2.0-6.fc20 fedora
mysqludf_xql.x86_64 1.0.0-7.fc20 fedora
**************************
/*************************
[root@localhost ~]# uname -a
Linux localhost.localdomain 3.12.9-301.nuriyevn.fc20.x
**************************
The problem is started when I try to execute the following command:
[root@localhost ~]# mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock
Of course, I haven't change the password. And of course, I put the right one.
I have the following problem:
sometimes, at random period of time mysqld service is failed.
then I have to restore the password it with mysqld_safe with --skip-grant-tables
I don't want to reset it at random period of time.
I need to find out the reason why is goes down.
I run Fedora Core 20.
/*************************
[root@localhost ~]# yum list mariadb*
Loaded plugins: langpacks, refresh-packagekit
Installed Packages
mariadb.x86_64 1:5.5.38-3.fc20 @updates
mariadb-devel.x86_64 1:5.5.38-3.fc20 @updates
mariadb-embedded.x86_64 1:5.5.37-1.fc20 @updates
mariadb-libs.x86_64 1:5.5.38-3.fc20 @updates
mariadb-server.x86_64 1:5.5.38-3.fc20 @updates
Available Packages
mariadb.i686 1:5.5.38-3.fc20 updates
mariadb-bench.x86_64 1:5.5.38-3.fc20 updates
mariadb-devel.i686 1:5.5.38-3.fc20 updates
mariadb-embedded.i686 1:5.5.38-3.fc20 updates
mariadb-embedded.x86_64 1:5.5.38-3.fc20 updates
mariadb-embedded-devel.i68
mariadb-embedded-devel.x86
mariadb-galera-common.x86_
mariadb-galera-server.x86_
mariadb-libs.i686 1:5.5.38-3.fc20 updates
mariadb-test.x86_64 1:5.5.38-3.fc20 updates
[root@localhost ~]# yum list mysql*
Loaded plugins: langpacks, refresh-packagekit
Available Packages
MySQL-python.x86_64 1.2.3-8.fc20 fedora
MySQL-zrm.noarch 3.0-3.fc20 updates
mysql++.i686 3.1.0-13.fc20 fedora
mysql++.x86_64 3.1.0-13.fc20 fedora
mysql++-devel.i686 3.1.0-13.fc20 fedora
mysql++-devel.x86_64 3.1.0-13.fc20 fedora
mysql++-manuals.x86_64 3.1.0-13.fc20 fedora
mysql-connector-java.noarc
mysql-connector-odbc.x86_6
mysql-connector-python.noa
mysql-connector-python3.no
mysql-mmm.noarch 2.2.1-9.fc20 fedora
mysql-mmm-agent.noarch 2.2.1-9.fc20 fedora
mysql-mmm-monitor.noarch 2.2.1-9.fc20 fedora
mysql-mmm-tools.noarch 2.2.1-9.fc20 fedora
mysql-proxy.i686 0.8.4-1.fc20 updates
mysql-proxy.x86_64 0.8.4-1.fc20 updates
mysql-proxy-devel.i686 0.8.4-1.fc20 updates
mysql-proxy-devel.x86_64 0.8.4-1.fc20 updates
mysql-utilities.noarch 1.3.6-1.fc20 updates
mysqlnd-qc-panel.noarch 1.2.0-4.fc20 updates
mysqlreport.noarch 3.5-11.fc20 fedora
mysqltuner.noarch 1.2.0-6.fc20 fedora
mysqludf_xql.x86_64 1.0.0-7.fc20 fedora
**************************
/*************************
[root@localhost ~]# uname -a
Linux localhost.localdomain 3.12.9-301.nuriyevn.fc20.x
**************************
The problem is started when I try to execute the following command:
[root@localhost ~]# mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock
Of course, I haven't change the password. And of course, I put the right one.
ASKER
[root@localhost mysql]# /usr/bin/mysqld_safe --skip_grant_tables --user=root &
[1] 8992
[root@localhost mysql]# 140808 20:33:00 mysqld_safe Logging to '/var/log/mariadb/mariadb.
140808 20:33:00 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
mysql -u root mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.38-MariaDB MariaDB Server
Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [mysql]> select user,password,hostname from mysql.user;
ERROR 1054 (42S22): Unknown column 'hostname' in 'field list'
MariaDB [mysql]> select user,password,host from mysql.user;
+--------+----------------
| user | password | host |
+--------+----------------
| root | *79C28907CD55F5D881237FEA3
| root | *79C28907CD55F5D881237FEA3
| root | *79C28907CD55F5D881237FEA3
| root | *79C28907CD55F5D881237FEA3
| ejudge | *A52452AC5F3C29034063F2BCD
| ali | *F7F29E9179E30A1E1F9345C8D
+--------+----------------
6 rows in set (0.00 sec)
MariaDB [mysql]> select password("<here is my password>");
+-------------------------
| password("<here is my password>") |
+-------------------------
| *79C28907CD55F5D881237FEA3
+-------------------------
1 row in set (0.00 sec)
So?
Also here is the log
[root@localhost mysql]# ll
total 28716
-rwxrwxrwx. 1 mysql mysql 16384 Aug 8 20:32 aria_log.00000001
-rwxrwxrwx. 1 mysql mysql 52 Aug 8 20:32 aria_log_control
drwx------ 2 mysql mysql 4096 Jul 29 13:16 bookstore
drwx------ 2 mysql mysql 4096 Jul 29 17:32 dummy
drwxrwxrwx. 2 mysql mysql 4096 Jul 26 22:20 ejudge
-rwxrwxrwx. 1 mysql mysql 18874368 Aug 8 20:32 ibdata1
-rwxrwxrwx. 1 mysql mysql 5242880 Aug 8 20:33 ib_logfile0
-rwxrwxrwx. 1 mysql mysql 5242880 Jun 20 17:26 ib_logfile1
drwxrwxrwx. 2 mysql mysql 4096 Jun 20 17:26 mysql
srwxrwxrwx 1 root root 0 Aug 8 20:33 mysql.sock
drwxrwxrwx 2 mysql ejudge 4096 Jun 23 20:14 performance_schema
drwx------ 2 mysql mysql 4096 Jul 29 17:54 pharma
[root@localhost mysql]# pwd
/var/lib/mysql
"Check your log purging processes."
How?
[1] 8992
[root@localhost mysql]# 140808 20:33:00 mysqld_safe Logging to '/var/log/mariadb/mariadb.
140808 20:33:00 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
mysql -u root mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.38-MariaDB MariaDB Server
Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [mysql]> select user,password,hostname from mysql.user;
ERROR 1054 (42S22): Unknown column 'hostname' in 'field list'
MariaDB [mysql]> select user,password,host from mysql.user;
+--------+----------------
| user | password | host |
+--------+----------------
| root | *79C28907CD55F5D881237FEA3
| root | *79C28907CD55F5D881237FEA3
| root | *79C28907CD55F5D881237FEA3
| root | *79C28907CD55F5D881237FEA3
| ejudge | *A52452AC5F3C29034063F2BCD
| ali | *F7F29E9179E30A1E1F9345C8D
+--------+----------------
6 rows in set (0.00 sec)
MariaDB [mysql]> select password("<here is my password>");
+-------------------------
| password("<here is my password>") |
+-------------------------
| *79C28907CD55F5D881237FEA3
+-------------------------
1 row in set (0.00 sec)
So?
Also here is the log
[root@localhost mysql]# ll
total 28716
-rwxrwxrwx. 1 mysql mysql 16384 Aug 8 20:32 aria_log.00000001
-rwxrwxrwx. 1 mysql mysql 52 Aug 8 20:32 aria_log_control
drwx------ 2 mysql mysql 4096 Jul 29 13:16 bookstore
drwx------ 2 mysql mysql 4096 Jul 29 17:32 dummy
drwxrwxrwx. 2 mysql mysql 4096 Jul 26 22:20 ejudge
-rwxrwxrwx. 1 mysql mysql 18874368 Aug 8 20:32 ibdata1
-rwxrwxrwx. 1 mysql mysql 5242880 Aug 8 20:33 ib_logfile0
-rwxrwxrwx. 1 mysql mysql 5242880 Jun 20 17:26 ib_logfile1
drwxrwxrwx. 2 mysql mysql 4096 Jun 20 17:26 mysql
srwxrwxrwx 1 root root 0 Aug 8 20:33 mysql.sock
drwxrwxrwx 2 mysql ejudge 4096 Jun 23 20:14 performance_schema
drwx------ 2 mysql mysql 4096 Jul 29 17:54 pharma
[root@localhost mysql]# pwd
/var/lib/mysql
"Check your log purging processes."
How?
ASKER
"Some people get a little too aggressive with their clean-up processes and something automatically deletes this socket file. In that case, just restart your server and the file should come back. Check your log purging processes to see if you can find what is deleting the socket."
Ok, seems now, I can log into using
mysql -u root -p
However, I haven't change the password.
Do you have any ideas according to this info?
Or the only solution is to change root password right now and wait for fish?
Ok, seems now, I can log into using
mysql -u root -p
However, I haven't change the password.
Do you have any ideas according to this info?
Or the only solution is to change root password right now and wait for fish?
Do you have an empty password set? You *SHOULD* set a password. I'm guessing you're having a security issue and *not* a clean-up issue.
ASKER
No I have the original password that was above. It's not an empty. Password
oh my buddha...
Yes, I can log into using these passwords
1. empty password
2. 1234
3. well, generally, any password.
Also, notice:
MariaDB [(none)]> select user, password, host from mysql.user
-> ;
+--------+----------------
| user | password | host |
+--------+----------------
| root | *79C28907CD55F5D881237FEA3
| root | *79C28907CD55F5D881237FEA3
| root | *79C28907CD55F5D881237FEA3
| root | *79C28907CD55F5D881237FEA3
| ejudge | *A52452AC5F3C29034063F2BCD
| ali | *F7F29E9179E30A1E1F9345C8D
+--------+----------------
6 rows in set (0.01 sec)
It looks like a security issue.
Does it sounds like a security issue?
oh my buddha...
Yes, I can log into using these passwords
1. empty password
2. 1234
3. well, generally, any password.
Also, notice:
MariaDB [(none)]> select user, password, host from mysql.user
-> ;
+--------+----------------
| user | password | host |
+--------+----------------
| root | *79C28907CD55F5D881237FEA3
| root | *79C28907CD55F5D881237FEA3
| root | *79C28907CD55F5D881237FEA3
| root | *79C28907CD55F5D881237FEA3
| ejudge | *A52452AC5F3C29034063F2BCD
| ali | *F7F29E9179E30A1E1F9345C8D
+--------+----------------
6 rows in set (0.01 sec)
It looks like a security issue.
Does it sounds like a security issue?
Yeah - did you remember to restart *WITHOUT* "skip_grant_tables"?
Well, I should say, you're still running "skip_grant_tables" - you need to restart without that option to turn back on normal authentication.
ASKER
[root@localhost mysql]# ps ax | grep mysql
8992 pts/6 S 0:00 /bin/sh /usr/bin/mysqld_safe --skip_grant_tables --user=root
9339 pts/6 Sl 0:01 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/my
9624 ? Ssl 0:00 /usr/libexec/mysqld --defaults-file=/etc/mysql
9656 pts/6 S+ 0:00 grep --color=auto mysql
Well , I have killed -9 all mysql processes, to make sure that it starts without --skip-grant-tables.
[root@localhost mysql]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
[root@localhost mysql]# service mysqld status -l
Redirecting to /bin/systemctl status -l mysqld.service
mysqld.service - MySQL Server
Loaded: loaded (/etc/systemd/system/mysql
Active: failed (Result: exit-code) since Fri 2014-08-08 21:18:01 AZST; 14s ago
Process: 9727 ExecStart=/usr/libexec/mys
Main PID: 9727 (code=exited, status=1/FAILURE)
Aug 08 21:18:00 localhost.localdomain systemd[1]: Started MySQL Server.
Aug 08 21:18:01 localhost.localdomain systemd[1]: mysqld.service: main process exited, code=exited, status=1/FAILURE
Aug 08 21:18:01 localhost.localdomain systemd[1]: Unit mysqld.service entered failed state.
Again, can't log into.
[root@localhost mysql]# mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock
8992 pts/6 S 0:00 /bin/sh /usr/bin/mysqld_safe --skip_grant_tables --user=root
9339 pts/6 Sl 0:01 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/my
9624 ? Ssl 0:00 /usr/libexec/mysqld --defaults-file=/etc/mysql
9656 pts/6 S+ 0:00 grep --color=auto mysql
Well , I have killed -9 all mysql processes, to make sure that it starts without --skip-grant-tables.
[root@localhost mysql]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
[root@localhost mysql]# service mysqld status -l
Redirecting to /bin/systemctl status -l mysqld.service
mysqld.service - MySQL Server
Loaded: loaded (/etc/systemd/system/mysql
Active: failed (Result: exit-code) since Fri 2014-08-08 21:18:01 AZST; 14s ago
Process: 9727 ExecStart=/usr/libexec/mys
Main PID: 9727 (code=exited, status=1/FAILURE)
Aug 08 21:18:00 localhost.localdomain systemd[1]: Started MySQL Server.
Aug 08 21:18:01 localhost.localdomain systemd[1]: mysqld.service: main process exited, code=exited, status=1/FAILURE
Aug 08 21:18:01 localhost.localdomain systemd[1]: Unit mysqld.service entered failed state.
Again, can't log into.
[root@localhost mysql]# mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
[root@localhost mysql]# mysql -h <global-ip-address> -u root -p
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on '<global-ip-address>' (111)
[root@localhost mysql]# ls -l /var/lib/mysql/mysql.sock
srwxrwxrwx 1 root root 0 Aug 8 20:33 /var/lib/mysql/mysql.sock
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on '<global-ip-address>' (111)
[root@localhost mysql]# ls -l /var/lib/mysql/mysql.sock
srwxrwxrwx 1 root root 0 Aug 8 20:33 /var/lib/mysql/mysql.sock
I'm at a loss. The socket looks fine. And you can't connect with *either* it or via TCP/IP. If it was a password problem, the error would report a username/password mis-match - not a "Can't Connect". Hmm... let me think about this one a little more.
ASKER
okay.
Are you by any chance doing anything odd in your /etc/mysql/my.cnf, like declaring a different port or socket to listen to?
ASKER
nano /etc/mysql/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysq
symbolic-links=0
log-error=/var/log/mysqld.
pid-file=/var/run/mysqld/m
[mysqld_safe]
!includedir /etc/my.cnf.d
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysq
symbolic-links=0
log-error=/var/log/mysqld.
pid-file=/var/run/mysqld/m
[mysqld_safe]
!includedir /etc/my.cnf.d
That looks good. How about the log file - "/var/log/mysqld.log" - are there any errors logged in there that can give us a hint as to what is going?
ASKER
[root@localhost log]# ll /var/log/mysqld.log
....
drwxr-x---. 2 mysql mysql 4096 Jun 27 19:09 mariadb
lrwxrwxrwx. 1 mysql mysql 28 Dec 12 2013 /var/log/mysqld.log -> /var/log/mariadb/mariadb.l
....
[root@localhost mariadb]# ll /var/log/mariadb/
total 0
-rwxrwxrwx. 1 root mysql 0 Aug 9 00:23 mariadb.log
Something happened with that file right now. Now it's empty.
....
drwxr-x---. 2 mysql mysql 4096 Jun 27 19:09 mariadb
lrwxrwxrwx. 1 mysql mysql 28 Dec 12 2013 /var/log/mysqld.log -> /var/log/mariadb/mariadb.l
....
[root@localhost mariadb]# ll /var/log/mariadb/
total 0
-rwxrwxrwx. 1 root mysql 0 Aug 9 00:23 mariadb.log
Something happened with that file right now. Now it's empty.
ASKER
When I tried to download that file with NppFtp..
Add these three lines to the '[[mysqld]]' section of your 'my.cnf' file and restart MySQLd. This should start up full logging:
general-log
general-log-file=queries.log
log-output=fileASKER
/etc/mysql/my.cnf content:
140809 0:44:58 InnoDB: The InnoDB memory heap is disabled
140809 0:44:58 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140809 0:44:58 InnoDB: Compressed tables use zlib 1.2.8
140809 0:44:58 InnoDB: Using Linux native AIO
140809 0:44:58 InnoDB: Initializing buffer pool, size = 128.0M
140809 0:44:58 InnoDB: Completed initialization of buffer pool
140809 0:44:58 InnoDB: highest supported file format is Barracuda.
140809 0:44:58 InnoDB: Waiting for the background threads to start
140809 0:44:59 Percona XtraDB (http://www.percona.com) 5.5.37-MariaDB-35.0 started; log sequence number 2540184
140809 0:44:59 [Note] Plugin 'FEEDBACK' is disabled.
140809 0:44:59 [Note] Server socket created on IP: '0.0.0.0'.
140809 0:44:59 mysqld: Can't create/write to file '/var/run/mariadb/mariadb.
140809 0:44:59 Can't start server: can't create PID file: Permission denied
140809 0:44:58 InnoDB: The InnoDB memory heap is disabled
140809 0:44:58 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140809 0:44:58 InnoDB: Compressed tables use zlib 1.2.8
140809 0:44:58 InnoDB: Using Linux native AIO
140809 0:44:58 InnoDB: Initializing buffer pool, size = 128.0M
140809 0:44:58 InnoDB: Completed initialization of buffer pool
140809 0:44:58 InnoDB: highest supported file format is Barracuda.
140809 0:44:58 InnoDB: Waiting for the background threads to start
140809 0:44:59 Percona XtraDB (http://www.percona.com) 5.5.37-MariaDB-35.0 started; log sequence number 2540184
140809 0:44:59 [Note] Plugin 'FEEDBACK' is disabled.
140809 0:44:59 [Note] Server socket created on IP: '0.0.0.0'.
140809 0:44:59 mysqld: Can't create/write to file '/var/run/mariadb/mariadb.
140809 0:44:59 Can't start server: can't create PID file: Permission denied
Looks like you don't have the permissions set correctly on /var/run/mariadb.
do an:
You'll most like have to do (as superuser on your server:)
do an:
ls -ld /var/run/mariadbYou'll most like have to do (as superuser on your server:)
chown mysql /var/run/mariadbASKER
[root@localhost mysql]# ls -ld /var/run/mariadb
drwxr-xr-x 2 mysql mysql 60 Aug 8 20:33 /var/run/mariadb
[root@localhost mysql]#
drwxr-xr-x 2 mysql mysql 60 Aug 8 20:33 /var/run/mariadb
[root@localhost mysql]#
ASKER
same stuff
140809 0:59:42 InnoDB: The InnoDB memory heap is disabled
140809 0:59:42 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140809 0:59:42 InnoDB: Compressed tables use zlib 1.2.8
140809 0:59:42 InnoDB: Using Linux native AIO
140809 0:59:42 InnoDB: Initializing buffer pool, size = 128.0M
140809 0:59:42 InnoDB: Completed initialization of buffer pool
140809 0:59:42 InnoDB: highest supported file format is Barracuda.
140809 0:59:42 InnoDB: Waiting for the background threads to start
140809 0:59:43 Percona XtraDB (http://www.percona.com) 5.5.37-MariaDB-35.0 started; log sequence number 2540184
140809 0:59:43 [Note] Plugin 'FEEDBACK' is disabled.
140809 0:59:43 [Note] Server socket created on IP: '0.0.0.0'.
140809 0:59:43 mysqld: Can't create/write to file '/var/run/mariadb/mariadb.
140809 0:59:43 Can't start server: can't create PID file: Permission denied
140809 0:59:42 InnoDB: The InnoDB memory heap is disabled
140809 0:59:42 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140809 0:59:42 InnoDB: Compressed tables use zlib 1.2.8
140809 0:59:42 InnoDB: Using Linux native AIO
140809 0:59:42 InnoDB: Initializing buffer pool, size = 128.0M
140809 0:59:42 InnoDB: Completed initialization of buffer pool
140809 0:59:42 InnoDB: highest supported file format is Barracuda.
140809 0:59:42 InnoDB: Waiting for the background threads to start
140809 0:59:43 Percona XtraDB (http://www.percona.com) 5.5.37-MariaDB-35.0 started; log sequence number 2540184
140809 0:59:43 [Note] Plugin 'FEEDBACK' is disabled.
140809 0:59:43 [Note] Server socket created on IP: '0.0.0.0'.
140809 0:59:43 mysqld: Can't create/write to file '/var/run/mariadb/mariadb.
140809 0:59:43 Can't start server: can't create PID file: Permission denied
Do:
ls -lL /var/run/mariadb/mariadb.p
ls -lL /var/run/mariadb/mariadb.p
ASKER
[root@localhost mariadb]# ls -lL /var/run/mariadb/mariadb.p
-rw-rw---- 1 root root 5 Aug 8 20:33 /var/run/mariadb/mariadb.p
-rw-rw---- 1 root root 5 Aug 8 20:33 /var/run/mariadb/mariadb.p
Ah! Root owns that file, but MySQL should.
chown mysql /var/run/mariadb/mariadb.p
And try running it again.
chown mysql /var/run/mariadb/mariadb.p
And try running it again.
ASKER
done:
chown mysql /var/run/mariadb/mariadb.p
why not
chown mysql:mysql /var/run/mariadb/mariadb.p
ok, server is now started, but
mysql -u root -p
Still doesn't work.
chown mysql /var/run/mariadb/mariadb.p
why not
chown mysql:mysql /var/run/mariadb/mariadb.p
ok, server is now started, but
mysql -u root -p
Still doesn't work.
You could set the group as well if you'd like
What does the error log say now?
What does the error log say now?
ASKER
140809 1:14:25 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140809 1:14:25 InnoDB: Compressed tables use zlib 1.2.8
140809 1:14:25 InnoDB: Using Linux native AIO
140809 1:14:25 InnoDB: Initializing buffer pool, size = 128.0M
140809 1:14:25 InnoDB: Completed initialization of buffer pool
140809 1:14:25 InnoDB: highest supported file format is Barracuda.
140809 1:14:25 InnoDB: Waiting for the background threads to start
140809 1:14:26 Percona XtraDB (http://www.percona.com) 5.5.37-MariaDB-35.0 started; log sequence number 2540184
140809 1:14:26 [Note] Plugin 'FEEDBACK' is disabled.
140809 1:14:26 [Note] Server socket created on IP: '0.0.0.0'.
140809 1:14:26 [Note] Event Scheduler: Loaded 0 events
140809 1:14:26 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.38-MariaDB-log' socket: '/var/run/mysqld/mysqld.so
That's all.
140809 1:14:25 InnoDB: Compressed tables use zlib 1.2.8
140809 1:14:25 InnoDB: Using Linux native AIO
140809 1:14:25 InnoDB: Initializing buffer pool, size = 128.0M
140809 1:14:25 InnoDB: Completed initialization of buffer pool
140809 1:14:25 InnoDB: highest supported file format is Barracuda.
140809 1:14:25 InnoDB: Waiting for the background threads to start
140809 1:14:26 Percona XtraDB (http://www.percona.com) 5.5.37-MariaDB-35.0 started; log sequence number 2540184
140809 1:14:26 [Note] Plugin 'FEEDBACK' is disabled.
140809 1:14:26 [Note] Server socket created on IP: '0.0.0.0'.
140809 1:14:26 [Note] Event Scheduler: Loaded 0 events
140809 1:14:26 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.38-MariaDB-log' socket: '/var/run/mysqld/mysqld.so
That's all.
ASKER
Also
[root@localhost mysqld]# service mysqld status
Redirecting to /bin/systemctl status mysqld.service
mysqld.service - MySQL Server
Loaded: loaded (/etc/systemd/system/mysql
Active: active (running) since Sat 2014-08-09 01:14:25 AZST; 5min ago
Main PID: 10765 (mysqld)
CGroup: /system.slice/mysqld.servi
└─10765 /usr/libexec/mysqld --defaults-file=/etc/mysql
Aug 09 01:14:25 localhost.localdomain systemd[1]: Started MySQL Server.
[root@localhost mysqld]# service mariadb status
Redirecting to /bin/systemctl status mariadb.service
mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/m
Active: inactive (dead)
[root@localhost mysqld]# service mysqld status
Redirecting to /bin/systemctl status mysqld.service
mysqld.service - MySQL Server
Loaded: loaded (/etc/systemd/system/mysql
Active: active (running) since Sat 2014-08-09 01:14:25 AZST; 5min ago
Main PID: 10765 (mysqld)
CGroup: /system.slice/mysqld.servi
└─10765 /usr/libexec/mysqld --defaults-file=/etc/mysql
Aug 09 01:14:25 localhost.localdomain systemd[1]: Started MySQL Server.
[root@localhost mysqld]# service mariadb status
Redirecting to /bin/systemctl status mariadb.service
mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/m
Active: inactive (dead)
You don't have both MySQL *AND* MariaDB installed, do you?
ASKER
When I install mysql it "redirects" to mariadb...
Look:
[root@localhost ~]# yum info mariadb
Loaded plugins: langpacks, refresh-packagekit
Installed Packages
Name : mariadb
Arch : x86_64
Epoch : 1
Version : 5.5.38
Release : 3.fc20
Size : 49 M
Repo : installed
From repo : updates
Summary : A community developed branch of MySQL
URL : http://mariadb.org
License : GPLv2 with exceptions and LGPLv2 and BSD
Description : MariaDB is a community developed branch of MySQL.
: MariaDB is a multi-user, multi-threaded SQL database server.
: It is a client/server implementation consisting of a server daemon
: (mysqld) and many different client programs and libraries. The
: base package contains the standard MariaDB/MySQL client programs
: and generic MySQL files.
Available Packages
Name : mariadb
Arch : i686
Epoch : 1
Version : 5.5.38
Release : 3.fc20
Size : 8.8 M
Repo : updates/20/x86_64
Summary : A community developed branch of MySQL
URL : http://mariadb.org
License : GPLv2 with exceptions and LGPLv2 and BSD
Description : MariaDB is a community developed branch of MySQL.
: MariaDB is a multi-user, multi-threaded SQL database server.
: It is a client/server implementation consisting of a server daemon
: (mysqld) and many different client programs and libraries. The
: base package contains the standard MariaDB/MySQL client programs
: and generic MySQL files.
[root@localhost ~]# yum info mysql
Loaded plugins: langpacks, refresh-packagekit
Error: No matching Packages to list
[root@localhost ~]# yum install mysql-libs mysql mysql-devel
Loaded plugins: langpacks, refresh-packagekit
adobe-linux-x86_64 | 951 B 00:00
rpmfusion-free-updates | 3.3 kB 00:00
updates/20/x86_64/metalink
updates | 4.9 kB 00:00
virtualbox | 951 B 00:00
updates/20/x86_64/primary_
(1/2): updates/20/x86_64/updatein
(2/2): updates/20/x86_64/pkgtags | 890 kB 00:00
Package 1:mariadb-libs-5.5.38-3.fc
Package 1:mariadb-5.5.38-3.fc20.x8
Package 1:mariadb-devel-5.5.38-3.f
Nothing to do
[root@localhost ~]#
Do I need to install mysql, if yes how? Is mariadb full replacement of mysql?
Look:
[root@localhost ~]# yum info mariadb
Loaded plugins: langpacks, refresh-packagekit
Installed Packages
Name : mariadb
Arch : x86_64
Epoch : 1
Version : 5.5.38
Release : 3.fc20
Size : 49 M
Repo : installed
From repo : updates
Summary : A community developed branch of MySQL
URL : http://mariadb.org
License : GPLv2 with exceptions and LGPLv2 and BSD
Description : MariaDB is a community developed branch of MySQL.
: MariaDB is a multi-user, multi-threaded SQL database server.
: It is a client/server implementation consisting of a server daemon
: (mysqld) and many different client programs and libraries. The
: base package contains the standard MariaDB/MySQL client programs
: and generic MySQL files.
Available Packages
Name : mariadb
Arch : i686
Epoch : 1
Version : 5.5.38
Release : 3.fc20
Size : 8.8 M
Repo : updates/20/x86_64
Summary : A community developed branch of MySQL
URL : http://mariadb.org
License : GPLv2 with exceptions and LGPLv2 and BSD
Description : MariaDB is a community developed branch of MySQL.
: MariaDB is a multi-user, multi-threaded SQL database server.
: It is a client/server implementation consisting of a server daemon
: (mysqld) and many different client programs and libraries. The
: base package contains the standard MariaDB/MySQL client programs
: and generic MySQL files.
[root@localhost ~]# yum info mysql
Loaded plugins: langpacks, refresh-packagekit
Error: No matching Packages to list
[root@localhost ~]# yum install mysql-libs mysql mysql-devel
Loaded plugins: langpacks, refresh-packagekit
adobe-linux-x86_64 | 951 B 00:00
rpmfusion-free-updates | 3.3 kB 00:00
updates/20/x86_64/metalink
updates | 4.9 kB 00:00
virtualbox | 951 B 00:00
updates/20/x86_64/primary_
(1/2): updates/20/x86_64/updatein
(2/2): updates/20/x86_64/pkgtags | 890 kB 00:00
Package 1:mariadb-libs-5.5.38-3.fc
Package 1:mariadb-5.5.38-3.fc20.x8
Package 1:mariadb-devel-5.5.38-3.f
Nothing to do
[root@localhost ~]#
Do I need to install mysql, if yes how? Is mariadb full replacement of mysql?
ASKER
These commands work:
mysql --protocol=TCP -u root -p
mysql -h 127.0.0.1 -u root -p
This one does not work:
mysql -u root -p
mysql --protocol=TCP -u root -p
mysql -h 127.0.0.1 -u root -p
This one does not work:
mysql -u root -p
You only want one of them. Either MySQL or MariaDB - don't install both! It's not binding to your servers IP address correctly. I'm not at a computer right now, but I'll look up the comment for my.cnf when I get back to a computer.
ASKER
Ah, then, it's ok. I have MariaDB. ok, will wait.
Add the following line to the [[mysqld]] section of your my.cnf, putting in your server's external IP address, and restart:
bind-address = <global-ip-address>
bind-address = <global-ip-address>
ASKER
Done.
Same story.
Same story.
Is it the same error still?
Looking at the entries for 'root' from the user table you posted earlier, you are only allowing connections for the root user from 'localhost'.
Can you try connecting as the user 'Ali' instead?
ASKER
[root@localhost mysql]# mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock
+--------+----------------
| user | host | password |
+--------+----------------
| root | localhost | *79C28907CD55F5D881237FEA3
| root | localhost.localdomain | *79C28907CD55F5D881237FEA3
| root | 127.0.0.1 | *79C28907CD55F5D881237FEA3
| root | ::1 | *79C28907CD55F5D881237FEA3
| ejudge | localhost | *A52452AC5F3C29034063F2BCD
| ali | % | *F7F29E9179E30A1E1F9345C8D
+--------+----------------
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock
+--------+----------------
| user | host | password |
+--------+----------------
| root | localhost | *79C28907CD55F5D881237FEA3
| root | localhost.localdomain | *79C28907CD55F5D881237FEA3
| root | 127.0.0.1 | *79C28907CD55F5D881237FEA3
| root | ::1 | *79C28907CD55F5D881237FEA3
| ejudge | localhost | *A52452AC5F3C29034063F2BCD
| ali | % | *F7F29E9179E30A1E1F9345C8D
+--------+----------------
ASKER
yes, I have tried.
[root@localhost ~]# mysql -u ali -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock
[root@localhost ~]# mysql -u ali -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock
Grab the encrypted version of root's password from the mysql.user table and store that somewhere. Next time this happens, *first* check that /var/lib/mysql/mysql.sock is still there. Some people get a little too aggressive with their clean-up processes and something automatically deletes this socket file. In that case, just restart your server and the file should come back. Check your log purging processes to see if you can find what is deleting the socket.
If the socket *is* still there, get back into MySQL using the "skip_grant_tables" method and first look at the encrypted password for root in mysql.user. If it is different, then I would be concerned about a security issue. Make sure you use a *NEW* password for the root account the next this happens, just to make sure. Also check the other user accounts. Perhaps there is another account that has "GRANT" privileges and that user is changing the password for the 'root' user.
Best practice is to change the 'root' user password to something *very* complex and to only use this account for administrative purposes. Your applications should each have their own username/password and access to only the data they need access to.