How do I explore my MySQL database?
Linode
Linode Staff
My website's content management system installed a bunch of stuff in MySQL, how do I see what is there?
2 Replies
hphillips
Linode Staff
Connecting
To explore your MySQL or MariaDB server, you will first need to login from the command line. You will need to know the username and password for your MySQL Server. The command to do this locally is:
mysql -u USERNAME -p
Usually USERNAME will be 'root'. Please note that this 'root' user is different than the 'root' user on your Linode. If you need to reset the root password, you can see this guide:
[https://dev.mysql.com/doc/en/resetting-permissions.html]
Exploring
At the highest level, your MySQL server will have one or more databases. Once you have logged in to the MySQL Server and see the mysql> or MariaDB> prompt, you can enter the following:
mysql> show databases
MySQL expects a semicolon at the end of the line. If you forgot to type the semicolon, you will see the -> prompt. Just type the semicolon to finish the line. -> ;
You should see something like this:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
Before you can explore a database, you must use it first:
mysql> use mysql;
Now we can take a look at the tables in that database.
mysql> show tables;
This will list the tables.
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| column_stats |
| columns_priv |
| db |
| event |
| func |
| general_log |
| gtid_slave_pos |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| index_stats |
| innodb_index_stats |
| innodb_table_stats |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| roles_mapping |
| servers |
| slow_log |
| table_stats |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
30 rows in set (0.00 sec)
To see what the structure of a table, you can use the describe command.
mysql> describe help_topic;
+------------------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+----------------------+------+-----+---------+-------+
| help_topic_id | int(10) unsigned | NO | PRI | NULL | |
| name | char(64) | NO | UNI | NULL | |
| help_category_id | smallint(5) unsigned | NO | | NULL | |
| description | text | NO | | NULL | |
| example | text | NO | | NULL | |
| url | text | NO | | NULL | |
+------------------+----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
To see what is stored in a table you can use a SELECT command:
mysql> SELECT help_topic_id, name FROM help_topic;
+---------------+-----------------------------------+
| help_topic_id | name |
+---------------+-----------------------------------+
| 0 | MIN |
| 1 | JOIN |
| 2 | HEX |
| 3 | REPLACE |
| 4 | CONTAINS |
| 5 | SRID |
| 6 | CURRENT_TIMESTAMP |
| 7 | SHOW CONTRIBUTORS |
| 8 | VARIANCE |
| 9 | DROP SERVER |
| 10 | SHOW AUTHORS |
| 11 | VAR_SAMP |
| 12 | CONCAT |
| 13 | GEOMETRY HIERARCHY |
| 14 | CHAR FUNCTION |
| 15 | DATETIME |
| 16 | SHOW CREATE TRIGGER |
...
| 504 | COALESCE |
| 505 | VERSION |
| 506 | MAKE_SET |
| 507 | FIND_IN_SET |
+---------------+-----------------------------------+
508 rows in set (0.00 sec)
Other tools
You can also use the command mysqlshow from the commandline to display database, table, and column information.
mysqlshow --count mysql
Database: mysql
+---------------------------+----------+------------+
| Tables | Columns | Total Rows |
+---------------------------+----------+------------+
| column_stats | 11 | 0 |
| columns_priv | 7 | 0 |
| db | 22 | 0 |
| event | 22 | 0 |
| func | 4 | 0 |
| general_log | 6 | 0 |
| gtid_slave_pos | 4 | 0 |
| help_category | 4 | 39 |
| help_keyword | 2 | 464 |
| help_relation | 2 | 1028 |
| help_topic | 6 | 508 |
| host | 20 | 0 |
| index_stats | 5 | 0 |
| innodb_index_stats | 8 | 4 |
| innodb_table_stats | 6 | 1 |
| plugin | 2 | 0 |
| proc | 20 | 2 |
| procs_priv | 8 | 0 |
| proxies_priv | 7 | 1 |
| roles_mapping | 4 | 0 |
| servers | 9 | 0 |
| slow_log | 13 | 0 |
| table_stats | 3 | 0 |
| tables_priv | 8 | 0 |
| time_zone | 2 | 0 |
| time_zone_leap_second | 2 | 0 |
| time_zone_name | 2 | 0 |
| time_zone_transition | 3 | 0 |
| time_zone_transition_type | 5 | 0 |
| user | 46 | 1 |
+---------------------------+----------+------------+
30 rows in set.
watrick
Linode Staff
MySQL documentation also has a number of commands that you can use from the command line:
3.1 MySQL Shell Commands
4.5.1 mysql — The MySQL Command-Line Client