用Docker部屬 MariaDB

通常我們不可能會在正式區建立開發用的資料庫,因此,對程式設計師來說,使用Docker就是建立快速開發環境的一個方式。

手動輸入

sudo pull mariadb:latest

// 假設主機對外port為 3307
// /data/db/mariadb是主機目錄,/var/lib/mysql 容器中Mariadb 預設的目錄
sudo docker run  --name MariaDB -p 3307:3306 -v /data/db/mariadb:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mariadb:10.1


// 停止
sudo docker stop MariaDB

// 啟動
sudo docker start MariaDB

// 登入 container
sudo docker exec -it 969088c84a4f bash

方法二:MySQL

// 若是要使用MySQL,則可以用:
docker run --name db -d -e MYSQL_ROOT_PASSWORD=123 -p 3306:3306 mysql:latest  
// 進入Container內的base
docker exec -it db /bin/bash
// 登入資料庫
mysql -uroot -p123 

方法三:使用 script

  • setup.sql
create table directory (user_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, email TEXT, phone_number TEXT);  
insert into directory (email, phone_number) values ('homer@thesimpsons.com', '+1 888 123 1111');  
insert into directory (email, phone_number) values ('marge@thesimpsons.com', '+1 888 123 1112');  
insert into directory (email, phone_number) values ('maggie@thesimpsons.com', '+1 888 123 1113');  
insert into directory (email, phone_number) values ('lisa@thesimpsons.com', '+1 888 123 1114');  
    insert into directory (email, phone_number) values ('bart@thesimpsons.com', '+1 888 123 1115');  
  • 啟動 DB script
#!/bin/sh

# Run the MySQL container, with a database named 'users' and credentials
# for a users-service user which can access it.
echo "Starting DB..."  
docker run --name db -d \  
  -e MYSQL_ROOT_PASSWORD=123 \
  -e MYSQL_DATABASE=users -e MYSQL_USER=users_service -e MYSQL_PASSWORD=123 \
  -p 3306:3306 \
  mysql:latest

# Wait for the database service to start up.
echo "Waiting for DB to start up..."  
docker exec db mysqladmin --silent --wait=30 -uusers_service -p123 ping || exit 1

# Run the setup script.
echo "Setting up initial data..."  
docker exec -i db mysql -uusers_service -p123 users < setup.sql  
````

* 停止DB Script

#!/bin/sh

Stop the db and remove the container.

docker stop db && docker rm db



## MariaDB 的 Multi Tenancy

$>eval “$(docker-machine env default)”
$>docker run -d -p 3302:3306 -v ~/mdbdata/mdb3302:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=2iduudgR –name mdb3302 mariadb:10.0


## MariaDB in Docker ### 抓取影像檔 image file

$>docker search mariadb
$>docker pull mariadb:latest


### 建立容器 Container

$>docker run –name masterDB -p 13306:3306 -v /Users/ascc/db/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=yourPasswdnpm install -d gitbook-cli svgexport -g -d mariadb:latest

$>docker exec -it masterDB bash // 登入該台機器


### 透過Dockerfile快速建立 * MySQL 官方的 Docker Image 會把 MySQL 的資料庫存在 Docker Container 的 /var/lib/mysql 資料夾下 * -v 選項的後面是先放「實體的完整路徑」,接著,在冒號的後面才是放 Container 內部的路徑 Read more: http://www.arthurtoday.com/2016/06/docker-container-add-data-volumes.html#ixzz4CDf8iLMg Read more: http://www.arthurtoday.com/2016/06/docker-container-add-data-volumes.html#ixzz4CDcb1XKN * 目錄架構

Project Folder
├─ localdb-run.sh – from @motin
└─ Dockerfile

* localdb-run.sh

#!/bin/bash
set -e

Script to workaround docker-machine/boot2docker OSX host volume issues: https://github.com/docker-library/mysql/issues/99

echo ‘* Working around permission errors locally by making sure that “mysql” uses the same uid and gid as the host volume’
TARGET_UID=$(stat -c “%u” /var/lib/mysql)
echo ‘– Setting mysql user to use uid ‘$TARGET_UID
usermod -o -u $TARGET_UID mysql || true
TARGET_GID=$(stat -c “%g” /var/lib/mysql)
echo ‘– Setting mysql group to use gid ‘$TARGET_GID
groupmod -o -g $TARGET_GID mysql || true
echo
echo ‘* Starting MySQL’
chown -R mysql:root /var/run/mysqld/
/entrypoint.sh mysqld –user=mysql –console


* Dockerfile

FROM mysql
MAINTAINER andyliu “andyliu@sinica.edu.tw”

ENV MYSQL_ROOT_PASSWORD password
ENV MYSQL_PASSWORD password
ENV MYSQL_DATABASE databasename
ENV MYSQL_USER mysql

添加文件夹下的 MYSQL 配置文件

COPY my.cnf /etc/mysql/conf.d/my.cnf
COPY mysqld_charset.cnf /etc/mysql/conf.d/mysqld_charset.cnf

COPY ./localdb-run.sh /
RUN chmod 755 /localdb-run.sh

Replication set

#ENV REPLICATION_MASTER False
#ENV REPLICATION_SLAVE False
#ENV REPLICATION_USER replica
#ENV REPLICATION_PASS replica

設定volume

VOLUME [“/Users/ascc/db/mysql”, “/var/lib/mysql”]

ENTRYPOINT [“/localdb-run.sh”]


* 執行

docker build -t andyliu/mysql .
docker run –name masterDB -p 13306:3306 -v /Users/ascc/db/mysql:/var/lib/mysql -d andyliu/mysql


### Multi Tenancy

docker run -d -p 3301:3306 -v ~/mdbdata/mdb55:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=admin –name mdb55 mariadb:5.5
$>docker run -d -p 4000:3306 -v ~/andyliu/db/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=admin –name masterDB mariadb:10.1
docker run -d -p 2000:3306 -v /Users/ascc/db:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=admin –name mdb11 mariadb:latest


Docker 會自動從官方 mariadb images將系統下載,然後執行,接下來就可以從不同的port去連結MariaDB。

$ mysql -u root -padmin -h 127.0.0.1 -P3302


* 注意:each of our instances will use a persistent data volume located under the ~/mdbdata 目錄 - Docker will automatically create this directory tree for us. * 由於Docker images是不可異動的,所以當我們要更改參數時,必須要先移除,再重建。由於我們把資料放在 ~/mdbdata目錄,所以資料並不會受到影響。 * Let's say that we want our MariaDB 10.1 instance (named mdb11) to have a higher CPU priority than the two other instances. In this case we can lower the CPU shares of mdb10 and mdb55. Each instance has 1024 max CPU shares by default, so let's recreate our mdb55 and mdb10 containers with 512 CPU shares each.

docker rm -f mdb55 mdb10
docker run -d -p 3301:3306 –cpu-shares=512 -v ~/mdbdata/mdb55:/var/lib/mysql –name mdb55 mariadb:5.5
docker run -d -p 3302:3306 –cpu-shares=512 -v ~/mdbdata/mdb10:/var/lib/mysql –name mdb10 mariadb:10.0


* Another option is to bind the instance to a specific CPU core, so let's recreate the containers and do that:

docker rm -f mdb55 mdb10 mdb11
docker run -d -p 3301:3306 –cpuset-cpus=0 -v ~/mdbdata/mdb55:/var/lib/mysql –name mdb55 mariadb:5.5
docker run -d -p 3302:3306 –cpuset-cpus=1 -v ~/mdbdata/mdb10:/var/lib/mysql –name mdb10 mariadb:10.0
docker run -d -p 3303:3306 –cpuset-cpus=2-3 -v ~/mdbdata/mdb10:/var/lib/mysql –name mdb11 mariadb:10.1


* Let's limit our two first containers to 512M of memory and 250 block IO shares:

docker rm -f mdb55 mdb10
docker run -d -p 3301:3306 –blkio-weight=250 –memory=512M -v ~/mdbdata/mdb55:/var/lib/mysql –name mdb55 mariadb:5.5
docker run -d -p 3302:3306 –blkio-weight=250 –memory=512M -v ~/mdbdata/mdb10:/var/lib/mysql –name mdb10 mariadb:10.0


* 建立複寫帳號

$>mysql -uroot -p

mysql>change master to master_host=’10.0.0.52′, master_port=13306, master_user=’replica’, master_password=’YOUR_PASSWORD’;

mysql>start slave;


* 匯入資料庫

$>service mysql stop // 先暫停資料庫服務

$>mysql -uroot -p –default-character-set=utf8 < repl_db.sql // 使用mysqldump 匯出資料庫
$>tar xvf db.tar // 使用tar匯出資料庫,記得需要更改解壓縮之檔案權限
// datadir=/var/lib/mysql

$>service mysql start –skip-slave-start // 啟動資料庫服務
mysql>start slave;
mysql>show slave status\G; // 可看看目前執行狀態

“`