1

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.

  • 2
    Please run the following queries and post the output exactly as it appears in the question and not as a comment: 1) SHOW DATABASES; 2) SELECT USER(),CURRENT_USER(); 3) SHOW GRANTS; – RolandoMySQLDBA Apr 4 '14 at 1:12
  • >SHOW DATABASES; => information_schema, test. >SELECT USER(); => root@localhost, >SELECT CURRENT_USER(); => @localhost, >SHOW GRANTS; => GRANT USAGE ON *.* TO ''@'localhost'. – okey_on Apr 4 '14 at 4:46
  • One more question : What version of MySQL is up on the Linux Server ??? – RolandoMySQLDBA Apr 4 '14 at 14:37
3

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 !!!

  • I followed your instructions but I'm unable to login to mysql with the password I created. Can you please look to see if your code is ABSOLUTELY correct and nothing missing(Like I said before: I'm new to linux...) – okey_on Apr 6 '14 at 18:50
  • I created the script with vi and ran it with the sudo command. It seemed to go well because no errors were reported. I used sudo on the other two commands as well, restarted mysqld(you gave mysql but that did not work). – okey_on Apr 6 '14 at 19:00
  • OK you will have to restart mysql like this : sudo service mysql restart --skip-grant-tables. This will get you to log into mysql without passwords. Login to mysql and run this: SELECT COUNT(1) RowCount FROM mysql.user WHERE user='root' and host='localhost'; What do you get for RowCount, 0 or 1 ??? – RolandoMySQLDBA Apr 6 '14 at 20:35
  • I got this error message from mysql: ERROR 1142 (42000): SELECT command denied to user ''@'localhost' for table 'user' Apparently my exercise with the linux script did not succeed in creating any mysql.user account. – okey_on Apr 6 '14 at 21:26
  • Did you login to MySQL like this : mysql -uroot -pmys3cr3t ??? From the looks of your error message, you did not specify the root user on the command line. – RolandoMySQLDBA Apr 6 '14 at 22:27
0

If you are truly signed in as root - and the response to 'SELECT USER()' indicates this is true, then the databases do not exist on that instance of MySQL. You're either on the wrong instance (are there two copies of MySQL on that computer, or are you on the wrong host?) or you deleted the databases.

The "SHOW DATABASES" command actually lists the directories in the main mysql directory (which is configured in my.cnf configuration file). So it is possible that you moved or repointed your mysql directory structure and the databases are still on your disk somewhere.

Do a file system search for a directory with the name of one of your databases (e.g., "find / -type d -name my_database"). That will turn up anything that might be your database files in an unexpected place.

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.