Thesql restart issue after move database

MySQL

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

Best Answer

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

Feb 28, 2013 : mysql restart won't kill child processes on CentOS
Mar 06, 2013 : How to properly kill MySQL?
Apr 22, 2013 : /usr/libexec/mysqld: Normal shutdown, but my team doesn't do that?

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

kill -9 729300
kill -9 729861
service mysql start

This should

kill mysqld_safe
kill mysqld
start mysqld

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

I described this before

Mar 06, 2013 : mysqld_safe version different than mysqld?
Jul 11, 2013 : Rare error lasts only a second "Can't connect to local MySQL server through socket '/tmp/mysql.sock' (146)" 146=ECONNREFUSED

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.

Related Solutions

MySQL – Differences Between mysqld_safe and mysqld

As we all know, mysqld_safe and mysqld are very different

mysqld : The database server instance daemon

mysqld_safe : Control program that examines and sets the environment for mysqld to execute. The mysqld executable is actually launched in a loop. When mysqld terminates, the mysqld_safe program will examine the return results and decide whether

mysqld terminated normally (intentional shutdown), leaves mysqld_safe
mysqld terminated abnormally (crash or kill -9 of mysqld)
- Loop back, mysqld fails on retry, leaves mysqld_safe
- Loop back, mysqld starts up, stays in the mysqld_safe loop

Why is it important to have mysqld and mysqld_safe using the same MySQL version?

Let me illustrate it this way: Percona Server sometimes has additional features in mysqld_safe for manipulating the OS. For example, I have seen numactl --interleave=all in a Percona Server mysqld_safe. If that line was not there, the mysqld for Percona Server may run into issues with memory and swapping.

The same scenario could possibly be the case for Oracle's (ugh, still hate saying that) mysqld and mysqld_safe. There could be improvements from one major release to another that would be removed if the mysqld_safe was older.

Rather than exploring the possibilities of using a old mysqld_safe and a new mysqld (or vica versa), please make your life simple and reinstall MySQL 5.5.30 from scratch.

Before doing so, please run

updatedb
locate mysqld_safe

in Linux and see if there are two lingering. If there are, get the paths straightened out. Otherwise, you may have to reinstall MySQL 5.5.30.

Mysql – innodb completely crashed after interruption

it might be not as bad. InnoDB dictionary is corrupt, but data may be still good. As long as MySQL start with innodb_force_recovery=6 dump as many tables tables as it allows:

    for d in `mysql -NBe "select distinct TABLE_SCHEMA  from information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema')"`
    do
        mkdir -p "dumps/$d"
        for t in `mysql -NBe "select TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA='$d' AND TABLE_TYPE = 'BASE TABLE' AND  ENGINE='InnoDB';"`
        do
            mysqldump --skip-lock-tables $d $t > "dumps/$d/$t.sql"
            # let MySQL start if it crashed
            if [ "$?" -ne 0 ]; then sleep 30; else echo "$d/$t" >> crashed fi
        done
     done

Then take list of "crashed" tables and recover them with data recovery toolkit.

You need to parse InnoDB tables space:

 ./stream_parser -f /path/to/table.ibd

and fetch records from it:

 ./c_parser -5f pages-XXXX/FIL_PAGE_INDEX/0-<minimal id>.pages -t table.sql

Then load the dump back into MySQL(as well as dumps taken with mysqldump)

P.S. I should probably write a blog post as I answered this question hundred times here.

Best Answer

Related Solutions

MySQL – Differences Between mysqld_safe and mysqld

Mysql – innodb completely crashed after interruption

Related Question