shntnu
4/6/2017 - 10:56 PM

Load MySQL tables generated using CellProfiler into a docker instance

Load MySQL tables generated using CellProfiler into a docker instance

DB_NAME=2015_10_05_DrugRepurposing_GolubLab_Broad

BASE_TABLE_NAME=analysis_2016_02_02_u2os_a549_24hr_48hr

IMAGE_TABLE_NAME=${BASE_TABLE_NAME}_Per_Image

OBJECT_TABLE_NAME=${BASE_TABLE_NAME}_Per_Object

zcat ${DB_NAME}-backup.gz | sed -n -e "/DROP TABLE IF EXISTS.*``${IMAGE_TABLE_NAME}``/,/UNLOCK TABLES;/p"  > ${IMAGE_TABLE_NAME}.sql

zcat ${DB_NAME}-backup.gz | sed -n -e "/DROP TABLE IF EXISTS.*``${OBJECT_TABLE_NAME}``/,/UNLOCK TABLES;/p" > ${OBJECT_TABLE_NAME}.sql

docker run \
  -p 3306:3306 \
  --name mysql-docker \
  -e MYSQL_ROOT_PASSWORD=cellprofiler \
  -d \
  mysql:8.0.0

mysql -h127.0.0.1 -P3306 -uroot -pcellprofiler -e "show databases;"

mysql -h127.0.0.1 -P3306 -uroot -pcellprofiler -e "CREATE USER 'cpuser'@'127.0.0.1 ' IDENTIFIED BY 'cPus3r';"

mysql -h127.0.0.1 -P3306 -uroot -pcellprofiler -e "GRANT ALL PRIVILEGES ON * . * TO 'cpuser'@'127.0.0.1';"

mysql -h127.0.0.1 -P3306 -uroot -pcellprofiler -e "CREATE DATABASE ${DB_NAME};"

mysql -h127.0.0.1 -P3306 -uroot -pcellprofiler ${DB_NAME} < ${IMAGE_TABLE_NAME}.sql

mysql -h127.0.0.1 -P3306 -uroot -pcellprofiler ${DB_NAME} < ${OBJECT_TABLE_NAME}.sql

mysql -h127.0.0.1 -P3306 -uroot -pcellprofiler ${DB_NAME} -e "select count(*) from ${IMAGE_TABLE_NAME};"

mysql -h127.0.0.1 -P3306 -uroot -pcellprofiler ${DB_NAME} -e "select count(*) from ${OBJECT_TABLE_NAME};"