➜ cat init/10_ddl.sql
set global local_infile = 1;
create table if not exists test_tbl1(
`code` char(3) not null,
`name` varchar(80) not null,
primary key(`code`)
) engine=innodb default charset=utf8;
create table if not exists test_tbl2(
`code` char(3) not null,
`age` int not null,
primary key(`code`)
) engine=innodb default charset=utf8;
-----------------
➜ cat init/20_data_load.sh
mysql -uroot -pPass --local-infile=1 test_db -e "LOAD DATA LOCAL INFILE '/docker-entrypoint-initdb.d/data1.csv' INTO TABLE test_tbl1 FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES"
mysql -uroot -pPass --local-infile=1 test_db -e "LOAD DATA LOCAL INFILE '/docker-entrypoint-initdb.d/data2.csv' INTO TABLE test_tbl2 FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES"
➜ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
ab3ca9cd20b8 mysql:8.0.20 "docker-entrypoint.s…" 55 seconds ago Up 54 seconds 33060/tcp, 0.0.0.0:13306->3306/tcp mysql_mysql_1
➜ docker exec -it ab3ca9cd20b8 bash
root@ab3ca9cd20b8:/# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.20 MySQL Community Server - GPL
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select * from test_db.test_tl01;
ERROR 1146 (42S02): Table 'test_db.test_tl01' doesn't exist
mysql> select * from test_db.test_tbl1;
+------+-----------+
| code | name |
+------+-----------+
| 001 | Tanaka |
| 002 | Sato |
| 003 | Suzuki |
| 004 | Takahashi |
+------+-----------+
4 rows in set (0.02 sec)
mysql> select * from test_db.test_tbl2;
+------+-----+
| code | age |
+------+-----+
| 001 | 10 |
| 002 | 20 |
| 003 | 30 |
| 004 | 40 |
+------+-----+
4 rows in set (0.01 sec)
mysql> exit
Bye
root@ab3ca9cd20b8:/# exit
exit
➜ cd data_env/R/
➜ docker-compose up -d
➜ docker-compose up -d
Building r-studio-server
Step 1/2 : FROM rocker/verse:latest
latest: Pulling from rocker/verse
a4a2a29f9ba4: Pull complete
127c9761dcba: Pull complete
d13bf203e905: Pull complete
4039240d2e0b: Pull complete
febcccaaf348: Pull complete
00e7e5e4c4eb: Pull complete
93cbc5d9bf1e: Pull complete
6779fb005e80: Pull complete
86d76ba07063: Pull complete
6a92e6e10218: Pull complete
Digest: sha256:65cd0788d5882eed88bbe5a04049d3123cdec48816b05685ea2911c2c64f0b49
Status: Downloaded newer image for rocker/verse:latest
---> 992d935ef30d
Step 2/2 : RUN R -e "install.packages(c('DBI', 'RMySQL'), dependencies=TRUE, repos='https://cran.ism.ac.jp/')"
---> Running in cedb48a15e56
R version 4.0.2 (2020-06-22) -- "Taking Off Again"
Copyright (C) 2020 The R Foundation for Statistical Computing
Platform: x86_64-pc-linux-gnu (64-bit)
R is free software and comes with ABSOLUTELY NO WARRANTY.
You are welcome to redistribute it under certain conditions.
Type 'license()' or 'licence()' for distribution details.
R is a collaborative project with many contributors.
Type 'contributors()' for more information and
'citation()' on how to cite R or R packages in publications.
Type 'demo()' for some demos, 'help()' for on-line help, or
'help.start()' for an HTML browser interface to help.
Type 'q()' to quit R.
> install.packages(c('DBI', 'RMySQL'), dependencies=TRUE, repos='https://cran.ism.ac.jp/')
Installing packages into ‘/usr/local/lib/R/site-library’
(as ‘lib’ is unspecified)
trying URL 'https://cran.ism.ac.jp/src/contrib/DBI_1.1.0.tar.gz'
Content type 'application/x-gzip' length 633079 bytes (618 KB)
==================================================
downloaded 618 KB
trying URL 'https://cran.ism.ac.jp/src/contrib/RMySQL_0.10.20.tar.gz'
Content type 'application/x-gzip' length 52900 bytes (51 KB)
==================================================
downloaded 51 KB
* installing *source* package ‘DBI’ ...
** package ‘DBI’ successfully unpacked and MD5 sums checked
** using staged installation
** R
** inst
** byte-compile and prepare package for lazy loading
** help
*** installing help indices
** building package indices
** installing vignettes
** testing if installed package can be loaded from temporary location
** testing if installed package can be loaded from final location
** testing if installed package keeps a record of temporary installation path
* DONE (DBI)
* installing *source* package ‘RMySQL’ ...
** package ‘RMySQL’ successfully unpacked and MD5 sums checked
** using staged installation
Found mysql_config cflags and libs!
Using PKG_CFLAGS=-I/usr/include/mysql
Using PKG_LIBS=-L/usr/lib/x86_64-linux-gnu -lmysqlclient -lpthread -lz -lm -lrt -lssl -lcrypto -ldl
** libs
rm -f RMySQL.so RMySQL-init.o connection.o db-apply.o driver.o exception.o fields.o result.o utils.o
gcc -I"/usr/local/lib/R/include" -DNDEBUG -I/usr/include/mysql -I/usr/local/include -fpic -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -Wdate-time -D_FORTIFY_SOURCE=2 -g -c RMySQL-init.c -o RMySQL-init.o
gcc -I"/usr/local/lib/R/include" -DNDEBUG -I/usr/include/mysql -I/usr/local/include -fpic -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -Wdate-time -D_FORTIFY_SOURCE=2 -g -c connection.c -o connection.o
gcc -I"/usr/local/lib/R/include" -DNDEBUG -I/usr/include/mysql -I/usr/local/include -fpic -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -Wdate-time -D_FORTIFY_SOURCE=2 -g -c db-apply.c -o db-apply.o
gcc -I"/usr/local/lib/R/include" -DNDEBUG -I/usr/include/mysql -I/usr/local/include -fpic -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -Wdate-time -D_FORTIFY_SOURCE=2 -g -c driver.c -o driver.o
gcc -I"/usr/local/lib/R/include" -DNDEBUG -I/usr/include/mysql -I/usr/local/include -fpic -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -Wdate-time -D_FORTIFY_SOURCE=2 -g -c exception.c -o exception.o
gcc -I"/usr/local/lib/R/include" -DNDEBUG -I/usr/include/mysql -I/usr/local/include -fpic -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -Wdate-time -D_FORTIFY_SOURCE=2 -g -c fields.c -o fields.o
gcc -I"/usr/local/lib/R/include" -DNDEBUG -I/usr/include/mysql -I/usr/local/include -fpic -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -Wdate-time -D_FORTIFY_SOURCE=2 -g -c result.c -o result.o
gcc -I"/usr/local/lib/R/include" -DNDEBUG -I/usr/include/mysql -I/usr/local/include -fpic -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -Wdate-time -D_FORTIFY_SOURCE=2 -g -c utils.c -o utils.o
gcc -shared -L/usr/local/lib/R/lib -L/usr/local/lib -o RMySQL.so RMySQL-init.o connection.o db-apply.o driver.o exception.o fields.o result.o utils.o -L/usr/lib/x86_64-linux-gnu -lmysqlclient -lpthread -lz -lm -lrt -lssl -lcrypto -ldl -L/usr/local/lib/R/lib -lR
installing to /usr/local/lib/R/site-library/00LOCK-RMySQL/00new/RMySQL/libs
** R
** byte-compile and prepare package for lazy loading
** help
*** installing help indices
** building package indices
** testing if installed package can be loaded from temporary location
** checking absolute paths in shared objects and dynamic libraries
** testing if installed package can be loaded from final location
** testing if installed package keeps a record of temporary installation path
* DONE (RMySQL)
The downloaded source packages are in
‘/tmp/Rtmpf0VQZF/downloaded_packages’
>
>
Removing intermediate container cedb48a15e56
---> d46fbad0d21f
Successfully built d46fbad0d21f
Successfully tagged r_r-studio-server:latest
WARNING: Image for service r-studio-server was built because it did not already exist. To rebuild this image you must use `docker-compose build` or `docker-compose up --build`.
Creating r_r-studio-server_1 ... done
➜ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
851c8f7948d9 r_r-studio-server "/init" About a minute ago Up About a minute 0.0.0.0:8787->8787/tcp r_r-studio-server_1
ab3ca9cd20b8 mysql:8.0.20 "docker-entrypoint.s…" 15 minutes ago Up 15 minutes 33060/tcp, 0.0.0.0:13306->3306/tcp mysql_mysql_1
library(RMySQL)
library(tidyverse)
con <- dbConnect(
drv = RMySQL::MySQL(),
dbname = "test_db",
user = "root",
password = "Pass",
# docker network inspect mysql_default | jq '.[0].IPAM.Config[0].Gateway'の結果
host = "172.19.0.1",
port = 13306
)
dbGetQuery(con, "SELECT * FROM test_db.test_tbl1;")
# code name
# 1 001 Tanaka
# 2 002 Sato
# 3 003 Suzuki
# 4 004 Takahashi
dbGetQuery(con, "SELECT * FROM test_db.test_tbl2;")
# code age
# 1 001 10
# 2 002 20
# 3 003 30
# 4 004 40
➜ docker network ls
NETWORK ID NAME DRIVER SCOPE
1a76981eca72 bridge bridge local
e963e0cf6ead host host local
7ca5740cb687 mysql_default bridge local
c1fc877b851e r_default bridge local
➜ docker network create data_analysis_network
d3af08f4815e2bd5f9ad52395dafbc6158acef2d0979f43fe5ec0c0046ba8784
➜ docker network ls
NETWORK ID NAME DRIVER SCOPE
1a76981eca72 bridge bridge local
d3af08f4815e data_analysis_network bridge local
e963e0cf6ead host host local
7ca5740cb687 mysql_default bridge local
e6057ac87f12 none null local
a77d133853e6 python_default bridge local
c1fc877b851e r_default bridge local
➜ docker-compose ps
Name Command State Ports
------------------------------------------------------------------------------------------------------
data_env2_mysql_1 docker-entrypoint.sh mysqld Up 0.0.0.0:13306->3306/tcp, 33060/tcp
data_env2_r-studio-server_1 /init Up 0.0.0.0:8787->8787/tcp
library(RMySQL)
library(tidyverse)
con <- dbConnect(
drv = RMySQL::MySQL(),
dbname = "test_db",
user = "root",
password = "Pass",
# docker network inspect data_analysis_network | jq '.[0].IPAM.Config[0].Gateway'の結果
host = "172.22.0.1",
port = 13306
)
dbGetQuery(con, "SELECT * FROM test_db.test_tbl1;")
# code name
# 1 001 Tanaka
# 2 002 Sato
# 3 003 Suzuki
# 4 004 Takahashi
dbGetQuery(con, "SELECT * FROM test_db.test_tbl2;")
# code age
# 1 001 10
# 2 002 20
# 3 003 30
# 4 004 40
