I have previously written on using containerized PostgreSQL with R. This show the steps for using containerized MySQL with R.

As a consulting data scientist I often have to debug and rehearse work away from the clients actual infrastructure. Because of this it is useful to be able to spin up disposable PostgreSQL or MySQL work environments. I have already written on how to do this for PostgreSQL, and here are our notes on how to do this for MySQL.

First make sure you have a current version of Docker installed and running on your system. Then launch a MySQL image with the standard MySQL communication port (3306) bound to your host machine’s network interface by typing the following at the command line (such as the shell in OSX, some instructions can be found here):

docker run -p 3306:3306 --name mysql -e MYSQL_ROOT_PASSWORD=passwd -d mysql/mysql-server:5.6

Now from the R side of things you can use the package RMySQL to connect to the database.

library('RMySQL') library('dplyr') mysql <- src_mysql('mysql','127.0.0.1',3306,'root','passwd')

And we are ready to work dplyr examples such as these:

library('nycflights13') # dplyr/mysql connector seems to error out on NA, overwrite them for now # submitted issue: https://github.com/hadley/dplyr/issues/2259 flts <- flights for(ci in colnames(flts)) { napos <- is.na(flts[[ci]]) if(any(napos)) { if(is.numeric(flts[[ci]])) { flts[[ci]][napos] <- NaN } if(is.character(flts[[ci]])) { flts[[ci]][napos] <- '' } } } flights_mysql <- copy_to(mysql, flts, temporary = FALSE, indexes = list( c("year", "month", "day"), "carrier", "tailnum")) flights_mysql

## Source: query [?? x 19] ## Database: mysql 5.6.34 [[email protected]:/mysql] ## ## year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time arr_delay carrier flight tailnum origin ## <int> <int> <int> <dbl> <int> <dbl> <dbl> <int> <dbl> <chr> <int> <chr> <chr> ## 1 2013 1 1 517 515 2 830 819 11 UA 1545 N14228 EWR ## 2 2013 1 1 533 529 4 850 830 20 UA 1714 N24211 LGA ## 3 2013 1 1 542 540 2 923 850 33 AA 1141 N619AA JFK ## 4 2013 1 1 544 545 -1 1004 1022 -18 B6 725 N804JB JFK ## 5 2013 1 1 554 600 -6 812 837 -25 DL 461 N668DN LGA ## 6 2013 1 1 554 558 -4 740 728 12 UA 1696 N39463 EWR ## 7 2013 1 1 555 600 -5 913 854 19 B6 507 N516JB EWR ## 8 2013 1 1 557 600 -3 709 723 -14 EV 5708 N829AS LGA ## 9 2013 1 1 557 600 -3 838 846 -8 B6 79 N593JB JFK ## 10 2013 1 1 558 600 -2 753 745 8 AA 301 N3ALAA LGA ## # ... with more rows, and 6 more variables: dest <chr>, air_time <dbl>, distance <dbl>, hour <dbl>, minute <dbl>, ## # time_hour <chr>

Or you can work directly through the underlying database connection:

dbGetQuery(mysql$con,'SELECT * FROM flts LIMIT 10')

And we have a MySQL database running. For more on working with R, databases, and dplyr please read Nina Zumel‘s article Using PostgreSQL in R: A quick how-to.