2

Update :

I know how can terminate process or restart mysql Manually , but I want know why this problem happen and why service mysql restart not work ? and how can resolve this problem basically

I move all our user database to new mysql server (Remote mysql server) after start mysql server we can not restart it .

please see following :

root@mysql [~]# service mysql restart
Shutting down  

MySQL........................................................................................................................................
.............................................................................
.............................................................................      ....... ERROR!

  ERROR! Failed to stop running server, so refusing to try to start.

and then mysql version not run :

root@mysql [~]# mysqladmin version
mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)'
Check that mysqld is running and that the socket: '/var/lib/mysql/mysql.sock' exists!

and you can see mysql process exist :

root@mysql [~]# ps aux | grep mysql
root      729300  0.0  0.0  11308  1504 ?        S    09:45   0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/mysql/mysql/ --pid-file=/mysql/mysql//mysql.shosting.net.pid
mysql     729861  100 22.4 42457756 11070156 ?   Sl   09:45 270:45 /usr/sbin/mysqld --basedir=/usr --datadir=/mysql/mysql/ --plugin-dir=/usr/lib64/mysql/plugin --user=
mysql --log-error=/mysql/mysql//mysql.shosting.net.err --open-files-limit=1024000 --pid-file=/mysql/mysql//mysql.shosting.net.pid

and check log :

2015-07-13 13:59:51 729861 [Note] /usr/sbin/mysqld: Normal shutdown

2015-07-13 13:59:51 729861 [Note] Giving 43 client threads a chance to die gracefully
2015-07-13 13:59:51 729861 [Note] Event Scheduler: Purging the queue. 0 events
2015-07-13 13:59:51 729861 [Note] Shutting down slave threads
2015-07-13 13:59:53 729861 [Note] Forcefully disconnecting 42 remaining clients
2015-07-13 13:59:53 729861 [Warning] /usr/sbin/mysqld: Forcing close of thread 482  user: 'root'

2015-07-13 13:59:53 729861 [Warning] /usr/sbin/mysqld: Forcing close of thread 499  user: 'root'

2015-07-13 13:59:53 729861 [Warning] /usr/sbin/mysqld: Forcing close of thread 642  user: 'root'

2015-07-13 13:59:53 729861 [Warning] /usr/sbin/mysqld: Forcing close of thread 661  user: 'root'

2015-07-13 13:59:53 729861 [Warning] /usr/sbin/mysqld: Forcing close of thread 678  user: 'root'

2015-07-13 13:59:53 729861 [Warning] /usr/sbin/mysqld: Forcing close of thread 713  user: 'root'

2015-07-13 13:59:53 729861 [Warning] /usr/sbin/mysqld: Forcing close of thread 730  user: 'root'

2015-07-13 13:59:53 729861 [Warning] /usr/sbin/mysqld: Forcing close of thread 747  user: 'root'

2015-07-13 13:59:53 729861 [Warning] /usr/sbin/mysqld: Forcing close of thread 764  user: 'root'

2015-07-13 13:59:53 729861 [Warning] /usr/sbin/mysqld: Forcing close of thread 784  user: 'root'

2015-07-13 13:59:53 729861 [Warning] /usr/sbin/mysqld: Forcing close of thread 801  user: 'root'

2015-07-13 13:59:53 729861 [Warning] /usr/sbin/mysqld: Forcing close of thread 870  user: 'root'

2015-07-13 13:59:53 729861 [Warning] /usr/sbin/mysqld: Forcing close of thread 889  user: 'root'

2015-07-13 13:59:53 729861 [Warning] /usr/sbin/mysqld: Forcing close of thread 836  user: 'root'

before restart i see following query in server :

+-----+------+-----------+--------------------+---------+------+----------------------+-----------------------------------------------------+
| Id  | User | Host      | db                 | Command | Time | State                | Info                                                |
+-----+------+-----------+--------------------+---------+------+----------------------+-----------------------------------------------------+
| 178 | root | localhost | information_schema | Query   | 763  | checking permissions | SELECT count(*) FROM tables WHERE ENGINE = 'InnoDB' |
| 295 | root | localhost | information_schema | Query   | 463  | checking permissions | SELECT count(*) FROM tables WHERE ENGINE = 'InnoDB' |
| 411 | root | localhost | information_schema | Query   | 163  | checking permissions | SELECT count(*) FROM tables WHERE ENGINE = 'InnoDB' |
| 465 | root | localhost | information_schema | Sleep   | 33   |                      |                                                     |
| 476 | root | localhost |                    | Query   | 0    | init                 | show processlist                                    |
+-----+------+-----------+--------------------+---------+------+----------------------+-----------------------------------------------------+

How can resolve this issue ?

Thanks

Improve this question
1
  • @pradis this problem could arise be change in mysql database. I have updated my answer
    – Hitesh Mundra
    Commented Jul 13, 2015 at 22:55

2 Answers 2

Reset to default
2

When you get the error message

Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'

This means mysqld is still up.

While this means you cannot connect to mysqld using the socket file, it is still possible to connect to mysqld using TCP/IP under certain conditions

When you connect using root@localhost, mysqld tries the socket file.

There is no mechanism for the service mysql ... to use TCP/IP.

You must shutdown mysqld gracefully with TCP/IP like this

mysqladmin -uroot -p -h127.0.0.1 -P3306 --protocol=tcp shutdown

I have described this method before

If this does not work, then you need to do the following;

kill -9 729300
kill -9 729861
service mysql start

This should

  1. kill mysqld_safe
  2. kill mysqld
  3. start mysqld

You kill mysqld_safe first because it has an infinite loop that checks on mysqld

I described this before

If you kill mysqld, mysqld_safe will try to restart mysqld on its own. It is better to kill mysqld_safe and then kill mysqld. That way, you start up mysqld on your terms.

Improve this answer
2

Mysql server is running not terminated, and port is already used with the socket. When we restart mysql server service, Sometimes it doesn't terminate the mysql server process and remains in the queue, In that case you can kill the mysql server process externally. 

sudo kill -9 pid
sudo service mysql start

sudo kill -9 729300

This is a common problem if you do a mysql import and overwrite the mysql database itself, such as when you might be restoring from a mysqldump -A backup.

This is a good thing: you probably want to back up all your mysql users, permissions, etc -- but it can wreak havoc with things like the debian-sys-maint user used to cleanly shutdown mysql.

Although this new database will possibly change both the root password and the debian-sys-maint password, of course it won't automatically change the expected debian-sys-maint password in /etc/mysql/debian.cnf. In fact, unless you also backed up that file, you probably don't even know what that password is anymore!

First things first. If the mysql root password was different between old and new servers, you can use mysqladmin to fix it:

mysql -p -u root password 'newpassword'

Fix the debian sys maint password. So now look up the debian sys maint password that debian created for you when you installed it on the new server. (You need sudo because this should be a highly protected file.)

sudo cat /etc/mysql/debian.cnf

Now, log in to mysql using the root password you set above:

mysql -p -u root   # use your new password when prompted

Reset the password for the debian-sys-maint user and don't forget to flush privileges:

>  SET PASSWORD FOR 'debian-sys-maint'@'localhost' = PASSWORD('samepassword');
>  FLUSH PRIVILEGES;
>  QUIT

Test to be sure it works:

sudo /etc/init.d/mysql restart
Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.