Shutting down MySQL.........
 

Hello,

Unable to stop mysql service.
Other than Kill option, how can we trouble shoot it?.

1. Not able to stop service
---------------------------
/etc/init.d/mysql stop
Shutting down MySQL............................................................................................... .................................................................................................... .............................................

2. Not able to login
---------------------
mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)


/etc/my.cnf

[mysqld]

max_allowed_packet = 2G
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
log_bin_trust_function_creators=1
max_connections = 250

slow_query_log = 1
slow_query_log_file = /var/lib/mysql/mysql-slow.log

default-storage-engine=innodb

innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 256M
innodb_log_buffer_size = 2M
innodb_flush_log_at_trx_commit = 1
innodb_force_recovery=4
innodb_doublewrite = OFF
innodb_thread_concurrency=8
innodb_flush_method=O_DIRECT
innodb_checksums=0
innodb_file_per_table = 1
lower_case_table_names = 0
innodb_support_xa=0
innodb_force_recovery = 4
group_concat_max_len=100M

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash


[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout
query-cache-type=1
query-cache-size=8M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

So what, exactly, makes you think that MySQL did NOT shut down? You issued a stop, then couldn't access the service. Sounds about right to me.

And you post no details about your system; version/distro of Linux, if you were root when issuing that command, what the logs say, etc. Without details, there is nothing we can tell you.

Use "ps aux | grep mysql" to see if the MySQL service is still running. If so you can 9 it down with "kill -9 <pid>".

I would recommend tailing the log file for errors. You could do "tail -f /var/log/mysqld/log" assuming that is the correct log. Then when you issue the service mysqld start/stop commands you will probably see an error which will provide more information as to what is actually going on.

Hi,

MySQL Status shows running, but not allowing to login or stop the service. I killed the service and started the mysql service it will start but issue persists.

Distro: CentOS 5.6

Are you the root user?

If so, is there a /root/.my.cnf file and if so what is in it?

There may not even be a root user in the MySQL users table. What user do you normally use?

This issue getting after improper shout down of the system such as sudden power failure or ups malfunction.
Is the ib data file will get corrupt because of this. I have noticed many times this issue. could you have any idea regarding this scenario?.

Yes...make backups of your data, and make sure you shut the system down properly. If you have a UPS, a sudden power failure won't be an issue. If you TEST the UPS, a malfunction will be unlikely.

What you're asking for, in this post, is impossible. There is no command to run to make sure that things won't get corrupted, due to improper operation of the system. If you make (and test) regular backups, and take proper precautions, you're fairly safe.

Could you give me a brief explanation regarding this scenario, like what is happening internally in MySQL that cause the ib_data file corrupted.
I want to know while improper shutdown is just like stopping all the process at a time right? So the last I/O into database can be ignored by the engine that fraction of second right? So the data will be safe. (I am a newbie)

There really is no answer to that question. It is like asking what is happening inside an aircraft at the instant it crashes into the ground that results in the death of a particular passenger. There is an answer but it is unknowable to those on the outside.

Why in the world would you even think that an improper shutdown is just like stopping all processes at a time? Is an airplane crash just like a good landing, except for the fact that the whole plane comes into contact with the ground at one time?

No, they are not remotely the same.

The power has failed. The CPU is in a completely indeterminate state, as well as the I/O devices and the memory. The "engine" does not even exist at that point... how then can it choose to ignore anything? Again, ignoring the fraction of a second of actual impact, all the passengers should be safe... right?

But it doesn't work that way in any event. File descriptors are open, data is cached in memory waiting to be written. State information disappears...

It is not the case that a particular file has unfortunately been corrupted, as in something bad happened to it. Rather it is that the complete state of the very complex machine and data has been lost, and simply is not recoverable - it does not exist. The best that you can do is to put it back in to some known, but different, state, and start again from there.

astrogeek gave you a good explanation. But you've been here for two years now...hardly a 'newbie' anymore.

You shut down a system properly for a REASON. The 'fraction of a second' when you pull the plug gives NOTHING any time to react and save files correctly, shut them, etc. What you're saying is "I stopped my car by running into a tree, instead of using the brakes...now, my car doesn't work! Why????"