Setup MySQL in Docker on Linux

To set up and configure MySQL in Docker, you can follow these steps:

Pull the MySQL Docker Image

Run the following command to pull the latest MySQL Docker image from Docker Hub:

docker pull mysql:latest

To list Docker images that have been downloaded to your system, you can use the docker images command. Here’s how you can do it:
```
 docker images
```
This command will display a list of all Docker images that have been downloaded to your local Docker host. The output typically includes columns for the repository, tag, image ID, creation date, and size.

If you want to see more details about the images, you can add the -a flag to include intermediate image layers:
```
 docker images -a
```
This will show all images, including intermediate layers that are not tagged and are usually created during the build process of other images.

Here’s a breakdown of what each column in the output generally represents:
- REPOSITORY: The repository name of the image.
- TAG: The tag of the image.
- IMAGE ID: The unique ID of the image.
- CREATED: When the image was created.
- SIZE: The disk space used by the image.

If you have Docker running with appropriate permissions (usually as root or with sudo), these commands will give you a comprehensive list of all Docker images available locally on your system.

The commands sudo docker ps and sudo docker ps -a are both used in Docker to list containers, but they have different behaviors:

sudo docker ps: This command lists only running containers. It won't show now cause we haven't run that mysql-container so don't worry even if it won't show up.
- Specifically, sudo docker ps displays containers that are currently in the "Up" state, meaning they are actively running.
- In your example:
```
  CONTAINER ID   IMAGE     COMMAND                  CREATED         STATUS         PORTS                 NAMES
  a7faf2672838   mysql     "docker-entrypoint.s…"   3 minutes ago   Up 3 minutes   3306/tcp, 33060/tcp   mysql-container
```
  This output shows that there is one container (a7faf2672838) running with the MySQL image.

sudo docker ps -a: This command lists all containers, including those that are not currently running.

sudo docker ps -a displays a list of all containers, regardless of their current state (running or stopped).

In your example:

  CONTAINER ID   IMAGE                      COMMAND                  CREATED         STATUS                    PORTS                 NAMES
  a7faf2672838   mysql                      "docker-entrypoint.s…"   3 minutes ago   Up 3 minutes              3306/tcp, 33060/tcp   mysql-container
  f952f7b80cfd   redis/redis-stack:latest   "/entrypoint.sh"         4 days ago      Exited (143) 3 days ago                         redis-stack

This output shows two containers:

One (a7faf2672838) is running (Up 3 minutes ago), using the MySQL image.
Another (f952f7b80cfd) is stopped (Exited (143) 3 days ago), using the Redis Stack image.

sudo docker ps: Lists only running containers.

sudo docker ps -a: Lists all containers (both running and stopped).

Create a Directory for Persistent Data

Create a directory on your host machine to store the persistent data for MySQL. This will ensure that your data is not lost when the container is stopped or removed.

mkdir ~/mysql-data

Run the MySQL Container

Run the following command to create and start a new MySQL container:

docker run --name mysql-container -e MYSQL_ROOT_PASSWORD=your_password -v ~/mysql-data:/var/lib/mysql -d mysql

--name mysql-container: Assigns a name to the container for easy reference.
-e MYSQL_ROOT_PASSWORD=your_password: Sets the root password for the MySQL server (replace your_password with your desired password).
-v ~/mysql-data:/var/lib/mysql: Mounts the ~/mysql-data directory on the host to /var/lib/mysql in the container, which is the location where MySQL stores its data files.
-d mysql: Runs the mysql image in detached mode (in the background).

sudo docker ps:Now this command lists all running containers.
- Specifically, sudo docker ps displays containers that are currently in the "Up" state, meaning they are actively running.
- In your example:
```
  CONTAINER ID   IMAGE     COMMAND                  CREATED         STATUS         PORTS                 NAMES
  a7faf2672838   mysql     "docker-entrypoint.s…"   3 minutes ago   Up 3 minutes   3306/tcp, 33060/tcp   mysql-container
```
  This output shows that there is one container (a7faf2672838) running with the MySQL image

If MySQL is running in a Docker container on your system, you can interact with it in several ways depending on what you want to do.

Accessing the MySQL Container

If MySQL is running inside a Docker container (mysql-container in your case), you might want to access it to manage databases, execute queries, or perform administrative tasks.

Accessing MySQL Shell in the Container

You can access the MySQL shell directly from the Docker host using the docker exec command:

docker exec -it mysql-container mysql -uroot -p

-it: Allows interactive terminal access.
mysql-container: Name or ID of your MySQL container.
mysql -uroot -p: Command to start the MySQL client as the root user (replace root with your MySQL username if different).

You will be prompted to enter the MySQL root password. Once authenticated, you can run SQL queries and manage your MySQL databases as usual.

Executing Commands in the Container

If you need to execute other commands inside the MySQL container, you can use docker exec:

docker exec -it mysql-container bash

This command opens a bash shell inside the MySQL container. From there, you can navigate around and execute any commands available in the container environment.

Here's how you can list the databases:

Access MySQL Shell: You're already inside the MySQL shell. If you haven't executed any commands yet, you should see a prompt like mysql>.
List Databases: To list all databases in MySQL, you can use the following command:

So, in your MySQL shell prompt, type:
```
 SHOW DATABASES;
```
This command will display a list of databases available in your MySQL server. It typically includes system databases like information_schema, mysql, and potentially others depending on your configuration and any databases you may have created.
Example Output: After running SHOW DATABASES;, you might see output similar to this:
```
 +--------------------+
 | Database           |
 +--------------------+
 | information_schema |
 | mysql              |
 | performance_schema |
 | sys                |
 +--------------------+
```
This output shows the default databases that MySQL typically creates. Your list may vary depending on your MySQL setup and any databases you've added.
Configure MySQL

Once you're connected to the MySQL server, you can execute SQL commands to create databases, users, and perform other configuration tasks as needed.

For example, to create a new database named mydatabase:
```
 CREATE DATABASE myfockerdatabase;
```
To list the databases in your MySQL server running inside the Docker container, you can use MySQL commands from within the MySQL shell you accessed using docker exec.
Switch tomyfockerdatabase: Inside the MySQL shell, use the USE command to switch to the myfockerdatabase:
```
 USE myfockerdatabase;
```
After executing this command, the MySQL shell will switch to using the myfockerdatabase for subsequent queries.
Verify Current Database: To verify that you are now working in myfockerdatabase, you can use the SELECT DATABASE(); command:
```
 SELECT DATABASE();
```
This will output the name of the current database, which should now be myfockerdatabase.

Perform Operations inmyfockerdatabase: Now that you are in the myfockerdatabase, you can create tables, insert data, and perform other SQL operations specific to that database.

Example:

Here's an example of how it would look in your MySQL shell session:

 mysql> USE myfockerdatabase;
 Database changed
 mysql> SELECT DATABASE();
 +------------------+
 | DATABASE()       |
 +------------------+
 | myfockerdatabase |
 +------------------+
 1 row in set (0.00 sec)

 mysql> -- Perform operations in myfockerdatabase
 mysql> CREATE TABLE mytable (
     -> id INT AUTO_INCREMENT PRIMARY KEY,
     -> name VARCHAR(50)
     -> );
 Query OK, 0 rows affected (0.06 sec)

 mysql> INSERT INTO mytable (name) VALUES ('John'), ('Alice'), ('Bob');
 Query OK, 3 rows affected (0.03 sec)
 Records: 3  Duplicates: 0  Warnings: 0

 mysql> SELECT * FROM mytable;
 +----+-------+
 | id | name  |
 +----+-------+
 |  1 | John  |
 |  2 | Alice |
 |  3 | Bob   |
 +----+-------+
 3 rows in set (0.00 sec)

 mysql> -- Continue working in myfockerdatabase or perform other operations as needed

Exiting MySQL Shell:

To exit the MySQL shell and return to your Docker container’s command prompt, type:

 exit;

This will close the MySQL client session and bring you back to the Docker container's command line.

Summary:

You can switch to a specific database (myfockerdatabase in this case) within the MySQL shell using the USE command.
Once inside the desired database, you can perform SQL operations specific to that database, such as creating tables, inserting data, querying data, etc.
Use exit; to exit the MySQL shell and return to the Docker container's command line when you're done working with the database.

Now Lets talk about how you can connect the mysql running on dokcer to any mysql client

Yes, you can connect to a MySQL server running in a Docker container using Beekeeper Studio (or any other MySQL client) on your laptop. To do this, you'll need to ensure that the MySQL container's ports are exposed and accessible from your host machine.

Here are the steps to connect Beekeeper Studio to your MySQL container:

1. Ensure MySQL Container Ports are Exposed

When you start your MySQL container, you should map the container ports to your host machine ports. If you haven't already done this, you can restart your container with the necessary port mappings.

For example, you can start the container with the following command to map the MySQL port (3306) to your host machine:

sudo docker run --name mysql-container -e MYSQL_ROOT_PASSWORD=my-secret-pw -d -p 3306:3306 mysql

This command does the following:

-e MYSQL_ROOT_PASSWORD=my-secret-pw: Sets the root password for MySQL.
-d: Runs the container in detached mode.
-p 3306:3306: Maps port 3306 of the container to port 3306 of the host.

If your container is already running, you can use the docker run command above or stop the container and restart it with the correct port mappings:

sudo docker stop mysql-container
sudo docker rm mysql-container
sudo docker run --name mysql-container -e MYSQL_ROOT_PASSWORD=my-secret-pw -d -p 3306:3306 mysql

2. Find the Host IP Address

If you are running Docker on Linux, you can connect to localhost (or 127.0.0.1). For Docker Toolbox or Docker Desktop on Windows and Mac, you might need to find the Docker host IP.

3. Connect Using Beekeeper Studio

Open Beekeeper Studio.
Create a New Connection:
- Click on the "New Connection" button.
Enter Connection Details:
- Connection Name: Give your connection a name.
- Host: Use localhost (or the IP address of your Docker host if you're not on Linux).
- Port: 3306
- Username: root (or any other MySQL user you've set up)
- Password: Enter the root password you specified (e.g., my-secret-pw).
- Database: (Optional) Specify a database if you want to connect to a specific one. You can leave this blank to connect to the default database.
Test the Connection:
- Click the "Test Connection" button to ensure that Beekeeper Studio can connect to your MySQL server.
Save and Connect:
- Once the connection is successful, save the connection and click "Connect".

Example

Here’s a visual example of what the connection details might look like:

Host: localhost
Port: 3306
Username: root
Password: my-secret-pw
Database: (leave it blank or specify the database you want to connect to)

By following these steps, you should be able to connect to your MySQL server running in a Docker container using Beekeeper Studio on your laptop.

How to setup and configure MYSQL in Docker on Linux.