0

By mistake I restarted MySQL server in Linux system. But MySQL server was unable to start.

I renamed my.cnf to my_bak.cnf. After that MySQL server started. But I can not see my project database.

I tried below command:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

But I can not see prj_db. But the prj_db folder is available in my Linux system. I want to restore the database from prj_db.

The steps that I have done :

  1. I made a change in max_allowed_packet=500M.
  2. MySQL server is not starting after changing the max_allowed_packet.
  3. I reverted back to my old my.cnf.
  4. After that also server was not started.
  5. Then I renamed the my.cnf to my_bak.cnf.Then server started.But i can not able to see my prj_db database.(Here i thought a new my.cnf will be created.But i can not see any my.cnf file under /etc folder.)
  6. I moved my_bak.cnf file from /etc folder to another location. mysql service started, but I can not see any my.cnf file under /etc folder as well as my prj_db database also.
  7. Please suggest me how can i reload my prj_db database.

AND I HAVE .MYD,.frm,.MYI Files in my prj_db folder.

----------------------------------my.cnf----------------------- 

[client]
port = 3306
socket = /DB/mysql/mysql.sock

#This is to handle unicode
default-character-set=utf8

[mysqld]
datadir=/DB/mysql
socket=/DB/mysql/mysql.sock
port = 3306
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock

#skip-networking
#skip-external-locking

#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = localhost

#Added by JV to store and retrive unicode (native language) data
init_connect='SET collation_connection = utf8_general_ci'
init_connect='SET NAMES utf8'
#character_set_client=utf8
character-set-server=utf8
collation-server=utf8_general_ci
skip-character-set-client-handshake

User=mysql
max_allowed_packet=500M
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

But after that I reverted back to old my.cnf and then I moved the .cnf file from /etc folder to other backup place.

  • copy the folder to new datadir path as per new config file and you should be able to see when you issue show databases – Nawaz Sohail Dec 16 '14 at 11:43
  • Why did you rename my.cnf? – Michael - sqlbot Dec 17 '14 at 2:29
  • Please post the my.cnf into the question that you changed max_allowed_packet. – RolandoMySQLDBA Dec 17 '14 at 5:11
1

Make the sure the datadir in new cnf file is the same as the old one then restart the service and try again.

0

You are simply not going to believe this. The root cause of your problem is this:

ROOT CAUSE

User=mysql
max_allowed_packet=500M

Your max_allowed_packet is perfectly fine.

THE PROBLEM IS User !!!

SOLUTION

You can either

  • Remove User=mysql from my.cnf
  • Comment User=mysql in my.cnf
  • Change User=mysql to user=mysql in my.cnf

and start mysql.

GIVE IT A TRY !!!

UPDATE 2014-12-17 11:18 EST

This was not a guess. Here is what I did to prove it to myself.

I saw User=mysql right above max_allowed_packet=500M and that surprised me. I have never seen options with uppercase letters before.

Since I have MySQL 5.5.37 running on my Windows 7 laptop, I did the following:

  • Added User=mysql to the my.ini on my laptop
  • Ran net stop mysql and net start mysql and mysqld would not start up
  • Changed User=mysql to user=mysql
  • Ran net stop mysql and net start mysql and mysqld ran just fine

THANK YOU FOR POSTING YOUR my.cnf. I wouldn't have caught this problem otherwise.

  • I Followed u r steps.still i can not connect to the db – hema Dec 18 '14 at 4:38
  • [root@savitr init.d]# service mysqld start Starting mysqld: [ OK ] [root@savitr ~]# mysql -u root -p Enter password: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/DB/mysql/mysql.sock' (2) – hema Dec 18 '14 at 4:38
  • Run the following: service mysql restart --skip-grant-tables. You can login to mysql without a password. Please do that right now – RolandoMySQLDBA Dec 18 '14 at 4:41
  • see this please...[root@savitr init]# service mysql restart --skip-grant-tables mysql: unrecognized service [root@savitr init]# service mysql restart mysql: unrecognized service [root@savitr init]# service mysqld restart Stopping mysqld: [ OK ] Starting mysqld: [ OK ] [root@savitr init]# – hema Dec 18 '14 at 4:51
  • Thank you very much really.... its working,and my database also there... thanq very much. – hema Dec 18 '14 at 5:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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