利用 Docker 快速建立 Sql Server 環境

環境：

1. Ubuntu Linux 2022
2. Docker CE 23.0.1

Docker 映像檔:

• mcr.microsoft.com/mssql/server:2022-latest

操作：

1. 下載 SQL Server 2022 Image
   $ docker pull mcr.microsoft.com/mssql/server:2022-latest
2. 執行容器(指定參數)
   $ docker run \
   -e "ACCEPT_EULA=Y" \
   -e "MSSQL_SA_PASSWORD=<YourStrong@Passw0rd>" \
   -p 1433:1433 --name sql1 --hostname sql1 \
   -d \
   mcr.microsoft.com/mssql/server:2022-latest
   
3. 檢查容器執行狀態
   $ docker ps  
   
   
4. 安裝成功後，可下載或利用 chocolatey 來安裝 Sql Studio Management Studio
   c:\> choco install sql-server-management-studio
5. 連線至 SQL Server 容器
   
   
   
   
   

容器執行參數說明：

1. -e ACCEPT_EULA=Y  同意使用者授權合約
2. -e MSSQL_SA_PASSWORD=<YourStrong@Passw0rd> 設定 SQL Server 最高管理員 sa 的密碼
3. --name sql1 設定容器名稱
4. --hostname sql1 設定容器主機名稱
5. -p 1433: 1433 設定 port 的對應
6. -d 設定容器以背景模式執行
7. mcr.microsoft.com/mssql/server:2022-latest  SQL Server 映像檔來源與名稱

進階：

1. 掛載 volume 保存資料庫內容

1. host 建立掛載的目錄
   $ mkdir data_mssql
2. 【必須】設定該目錄擁有者, 預設owner id 為 10001
   $ sudo chown -R 10001 data_mssql
3. 停止、刪除容器後，加上掛載 volume 參數後重新啟動容器
   $ docker run \
   -e "ACCEPT_EULA=Y" \
   -e "MSSQL_SA_PASSWORD=<YourStrong@Passw0rd>" \
   -p 1433:1433 --name sql1 --hostname sql1 \
   -v ./data_mssql:/var/opt/mssql
   -d \
   mcr.microsoft.com/mssql/server:2022-latest

2. 利用 docker-compose 執行容器

1. 建立 docker-compose.yml 檔案，內容如下：

version: '3.5'

services:
  mssql:
    image: mcr.microsoft.com/mssql/server:2022-latest
    restart: always
    ports:
      - 1433:1433
    volumes:
      - ./data_mssql:/var/opt/mssql
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=PutYourPassword
      - MSSQL_PID=Express

2. 執行

$ docker-compose up -d 

轉：DOCKER – HOW TO CONNECT TO A MYSQL RUNNING CONTAINER

轉貼自 LITTLETECHBLOGGER
---------------------------------

1. What we want to achieve:

We used the official mysql image from Docker Hub (https://hub.docker.com/_/mysql/), and created a running database container.

Now we need to connect to the MySQL database using MySQL command line client, to do some database operation.

2. Solution1:

Bash into the running container and run MySQL client.

2.1 Bash into the running MySQL container:

$ docker exec -t -i <container_name> /bin/bash

2.2 Run MySQL client:

$ mysql -u “<useranme>” -p

3. Solution2:

Use MySQl Command Line Client.

Reference: MySQL Image Official Doc

In the official guide, it states:

The following command starts another mysql container instance and runs the mysql command line client against your original mysql container, allowing you to execute SQL statements against your database instance:

$ docker run -it --link some-mysql:mysql --rm mysql sh -c 'exec mysql -h"$MYSQL_PORT_3306_TCP_ADDR" -P"$MYSQL_PORT_3306_TCP_PORT" -uroot -p"$MYSQL_ENV_MYSQL_ROOT_PASSWORD"'

The variables here are really confusing.  Here is a more readable version of this command:

$ docker run -it --link <container_name>:<image_name> --rm <image_name> sh -c 'exec mysql -h"<container_ip_address>" -P"<mysql_port_number>" -u"<mysql_username>" -p"<mysql_password>"'

Now, lets use an example to explain how to find all the variables.

3.1 Get container_name, image_name, mysql_port_number:

$ docker ps

We can get:

container_name: romuiweb_db_1

image_name: mysql

container_port_number: 3306

3.2 Get container_ip_address:

$ docker inspect –format ‘{{ .NetworkSettings.IPAddress }}’ <container_id>

3.3 Get mysql_username, mysql_password:

When we start the mysql image, we set environment variables to decide the username and password.

username is default to root.

password is set by MYSQL_ROOT_PASSWORD, and we can find it in docker-compose.yml file:

So we can get:

mysql_username: root,

mysql_password: password.

3.4 Create the real command:

Now we have all the values of the variables. We can construct the real command:

$ docker run -it –link romuiweb_db_1:mysql –rm mysql sh -c ‘exec mysql -h”172.17.0.2″ -P”3306″ -u”root” -p”password”‘

3.5 Run the command, gain access to MySQL:

If it works, skip the following problem solving part.

3.6 Problem Solving:

(In this problem, we are not using romuiweb_db_1 we used earlier. We switch to docker_db_1. The Ip Address is different now)

docker: Error reponse from daemon: Cannot link to <container_name>, as it does not belong to the default work.

$ docker run -it –link docker_db_1:mysql –rm mysql sh -c ‘exec mysql -h”172.20.0.3″ -P”3306″ -u”root” -p”password”‘

This means that we are not running the mysql connect container on the same network the mysql container is currently running on. We need to connect it to the same network use –net option. Reference: https://docs.docker.com/engine/userguide/containers/networkingcontainers/

Step1: Find out which network mysql container is running on:

$ docker inspect docker_db_1

Find the “Networks”:

As we can see from the screenshot above, the network docker_db_1 is running on is “docker_default”. We need to connect to this network. So we change out command to this:

$ docker run -it –net=docker_default –link docker_db_1:mysql –rm mysql sh -c ‘exec mysql -h”172.20.0.3″ -P”3306″ -u”root” -p”password”‘

After adding the network argument, we successfully connected: