After mysql server restarts I cannot see my own database

Question

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.

Answer 1

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

Answer 2

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.