Why Can't I find my databases from Mysql on linux?

I'm new to linux. I cannot get a list of my databases by typing SHOW DATABASES on the MySQL shell. Though this MySQL program is aware of the MySQL datadir because it is listed when I type SHOW VARIABLES LIKE "%dir", as datadir = /var/lib/mysql. I am logged in as root.

Normally on windows, I would type SHOW DATABASES on the MySQL command console that comes with WAMPSERVER and I'd get a list of all available databases on the localhost.

Your problem has nothing to do with missing databases. You real problem stems directly from the way you logged in to MySQL. First, look at your comment

SHOW DATABASES; => information_schema, test.
SELECT USER(); => root@localhost
SELECT CURRENT_USER(); => @localhost
SHOW GRANTS; => GRANT USAGE ON *.* TO ''@'localhost'

Note the difference between USER() and CURRENT_USER()

• USER() reports how you attempted to authenticate in MySQL ('root'@'localhost')

• CURRENT_USER() reports how you were allowed to authenticate in MySQL (''@'localhost')

Bottom Line : You have no rights to see anything. From the the output of USER() and CURRENT_USER(), there is no root@localhost defined in the table mysql.user.

The further proof of this is the fact that you cannot see the mysql and performance_schema databases. A fresh install of MySQL would still have these databases fully visible if you had all rights enabled.

You can verify this by going into the OS and running the following:

cd /var/lib/mysql
ls -l

You should see the mysql and performance_schema databases as Linux folders. Thus, they are physically there. The problem is just the rights you have after user authentication.

The only way to fix it is to create the root@localhost user with all the rights needed. I am going to show you how to create the MySQL user root@localhost in the Linux server

Step 01) Create a script to create user

If root had a password (such as mys3cr3t), run this

SQLSTMT="GRANT ALL PRIVILEGES ON *.* to root@localhost"
SQLSTMT="${SQLSTMT} IDENTIFIED BY 'mys3cr3t' WITH GRANT OPTION;"
echo ${SQLSTMT} > /var/lib/mysql/init.sql
chown mysql:mysql /var/lib/mysql/init.sql

Step 02) Restart mysql using the init.sql file

service mysql restart --init-file=/var/lib/mysql/init.sql

Step 03) Remove /var/lib/mysql/init.sql

rm -f /var/lib/mysql/init.sql

Now, you have root@localhost with the password mys3cr3t
Give it a Try !!!

StackExchange Database Administrators Q#62521, answer score: 4