Docker - How to take a look at the Tables inside MySQL volume?

Question

I have imported an SQL file contains my schema and all its tables, By using:

services:
  db:
    image: mysql:5.7
    volumes:
      - db_data:/var/lib/mysql
      - ./resources/file.sql:/docker-entrypoint-initdb.d/file.sql
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: db

The problem is, when I trying to retrieve data from some tables an exception in the backend appear:

throws exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'db.Configuration' doesn't exist com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'db.Configuration' doesn't exist

And some tables work perfectly like user table.

Although I have tested the SQL file in MySQL Workbench.

The question is, Is there a way I can see what tables are inside the db_data volume?

Answer 1

Yes, you can see All Table information from docker command line.

First, go inside docker container, run below command.

docker exec -it mysql_container_name mysql -uroot -p

where “root” is the username for MySQL database. After running above command it will ask you a password.

Then Select Database, run below command

USE Name-Of-The-Database

get the list of all tables.

show tables;

Run any query, e.g select * from

SELECT * FROM table_name;

I have listed down some daily docker useful commands, have a look. https://rohanjmohite.wordpress.com/2017/08/04/docker-daily-useful-commands/

please let me know in case any further more explanation required?

Answer 2

One solution is to use MySQL Workbench and create a connection pointing to the docker database container. From there you can check what schema tables have been created.

If the database docker container is started, you can inspect the container and find the IPAddress using the following command:

docker inspect container-name-here

get the IPAddress and use it in the MySQLWorkbench to create the connection

Answer 3

You can execute any SQL command directly from the host to view your database.
You are looking for this command:

docker exec -it mysql-container mysql -uroot -pmy-secret-pw -D thisdatabase -e "SELECT * FROM table_name;

where mysql-container is the name of the mysql container
where -uroot is the account for the sql container
where -pmy-secret-pw is the password for the sql container
where thisdatabase is the name of the database to inspect
where table_name is obviously the database table name of interest

TIP: if it is a new container, and you don't know the password, just run this command:

sudo docker logs mysql-container 2>&1 | grep GENERATED