MySQL: 'show databases;` command returning different results depending on how it is started.

Question

Im relatively new to MySQL and I'm having some trouble with my current setup. Depending on how I start the mysql prompt affects which Databases I can see using the show databases; command. In Method One (below) I can only see two databases. In Method Two (also below) I can see 5.

This has caused me a lot of issues as I thought I had lost some of my databases. It has also caused issues with logging into phpMyAdmin I believe.

My question: Why do I see different results using the same command in each method?

• Setup: OS Mavericks (10.9.4)

Method One

In this method I have already started MySQL using the "Start MySQL Server" button from the MySQL pane in System Preferences

I then use

(bias_experiment)localhost:bias_experiment brendan$ /usr/local/mysql/bin/mysql -v

It gives me some version feedback and opens the mysql> command prompt

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 378 Server version: 5.6.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Reading history-file /Users/brendan/.mysql_history Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> \q Writing history-file /Users/brendan/.mysql_history Bye (bias_experiment)localhost:bias_experiment brendan$ /usr/local/mysql/bin/mysql -v Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 401 Server version: 5.6.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Reading history-file /Users/brendan/.mysql_history Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

The show databases; command gives me back only two databases

mysql> show databases;
--------------
show databases
--------------

+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

mysql> 

Method Two

I use the below command to start MySQL

(bias_experiment)localhost:src brendan$ /usr/local/mysql/support-files/mysql.server start
Starting MySQL
 SUCCESS! 
(bias_experiment)localhost:src brendan$ 

I then use

(bias_experiment)localhost:src brendan$ mysql -u root -p
Enter password: 

I type in my root password which shows me the below

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 472 Server version: 5.6.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

However issuing the same show databases; command returns different results to the above.

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

mysql> 

Can anyone tell me why I'm seeing different results? Thanks

Answer 1

This has nothing to do with how you start the server and everything to do with how you are logging into the server.

In your first example, when you login without explicitly specifying a user name, you are using your current Linux user brendan. In the second case you explicitly specify the user as root.

The additional databases you are seeing are visible to root and not to brendan.