Python functions for easy working with docker mysql - via http://www.luiselizondo.net/a-tutorial-on-how-to-use-mysql-with-docker/

commandline.txt

#Create a container mot2013 with MySQL pwd: mot
#/host/path/to/ should contain:
## mot_boot.sql 
## test_result_2013.txt  #download and uncompress: http://data.dft.gov.uk/anonymised-mot-test/12-03/test_result_2013.txt.gz 
## test_item_2013.txt  #download and uncompress: http://data.dft.gov.uk/anonymised-mot-test/12-03/test_item_2013.txt.gz 
## mdr_test_lookup_tables/ #download and unzip: http://data.dft.gov.uk/anonymised-mot-test/mdr_test_lookup_tables.zip

dmysql-server mot2013 mot
dmysql-create-database mot2013 motdata
dmysql-import-database mot2013 /host/path/to/mot_boot.sql --database motdata

dmysql

#!/usr/bin/python

#Via https://github.com/luiselizondo/docker-mysql-scripts/

import sys
from subprocess import call
import argparse

def runContainer(containerName):
	''' Runs the mysql client using the containerName provided
	'''

	call("docker run -it --link="+containerName+":mysql --rm mysql sh -c 'exec mysql -h$MYSQL_PORT_3306_TCP_ADDR -P$MYSQL_PORT_3306_TCP_PORT -uroot -p'", shell=True)
	return

def main():
	''' Creates the parser and executes the function runContainer
	'''

	parser = argparse.ArgumentParser(
		description="Run mysql client on a docker container", 
		prog="dmysql")

	parser.add_argument("container", help="The mysql container to connect to")

	args = parser.parse_args()

	runContainer(args.container)
	return

# Execute the main function
main()

dmysql-create-database

#!/usr/bin/python

#Via https://github.com/luiselizondo/docker-mysql-scripts/

import sys
from subprocess import call
import argparse

def runContainer(containerName, databaseName):
	''' Runs the command to create the database given using the container given
	'''
	call("docker run -it --link="+containerName+":mysql --rm mysql sh -c 'exec mysqladmin -h$MYSQL_PORT_3306_TCP_ADDR -P$MYSQL_PORT_3306_TCP_PORT -uroot -p create "+ databaseName +" '", shell=True)
	return

def main():
	''' Parse the options from the command line and run the function runContainer
	'''
	parser = argparse.ArgumentParser(
		description="Import a .sql file into a database", 
		prog="dmysql-import-database")

	# Create the options
	parser.add_argument("container", help="The mysql container to use")
	parser.add_argument("database", help="Database to create")

	args = parser.parse_args()

	runContainer(containerName=args.container, databaseName=args.database)

	return

# Execute the main function
main()

dmysql-import-database

#!/usr/bin/python

#Via https://github.com/luiselizondo/docker-mysql-scripts/

import sys
from subprocess import call
import os
import argparse

def runContainer(containerName, path, database = ""):
	''' Runs the container calling docker run and passing
			the necessary arguments. The database is optional since
			we can import an .sql file without creating first a database or
			specifying one.
	''' 

	# Split the path given and create a list out of it
	pathList = path.split("/")

	# Take the last element of the list because this is the file
	fileName = pathList[-1]

	# Read the path and get the directory
	dirname = os.path.dirname(path)

	if database:
		print 'Importing {file} into database {database} using container {container}'.format(file=fileName, database=database, container=containerName)
		call("docker run -it --link="+containerName+":mysql -v "+ dirname +":/tmp/import --rm mysql sh -c 'exec mysql -h$MYSQL_PORT_3306_TCP_ADDR -P$MYSQL_PORT_3306_TCP_PORT -uroot -p "+ database +" < /tmp/import/" + fileName + "'", shell=True)
	else:
		print 'Importing {file} into container {container}'.format(file=fileName, container=containerName)
		call("docker run -it --link="+containerName+":mysql -v "+ dirname +":/tmp/import --rm mysql sh -c 'exec mysql -h$MYSQL_PORT_3306_TCP_ADDR -P$MYSQL_PORT_3306_TCP_PORT -uroot -p < /tmp/import/" + fileName + "'", shell=True)
	return

def main():
	''' Interpret the command line arguments
			and pass the options to runContainer
	'''

	parser = argparse.ArgumentParser(
		description="Import a .sql file into a database", 
		prog="dmysql-import-database")

	# Create the options
	parser.add_argument("container", help="The mysql container to use")
	parser.add_argument("filepath", help="File to the sql file you want to import")
	parser.add_argument("--database", help="Database to import the file into")

	args = parser.parse_args()

	if args.database:
		runContainer(args.container, args.filepath, args.database)
	else:
		runContainer(args.container, args.filepath)
	return

# Execute main
main()

dmysql-server

#!/usr/bin/env python

#Via https://github.com/luiselizondo/docker-mysql-scripts/

import sys
from subprocess import call
import argparse

def createVolume(containerVolumeName):
	''' Create a data only container
	'''
	print 'Creating the container using the volumes-from strategy. Using container {container} as the container name'.format(container=containerVolumeName)
	call("docker run -v /var/lib/mysql --name "+ containerVolumeName+" busybox true", shell=True)
	return

def runContainer(containerName, rootPassword, hasVolume=False):
	''' Creates the container
	'''

	if hasVolume:
		# Create the container name variable
		containerVolumeName = containerName.upper() + '_DATA'

		# Create data container
		createVolume(containerVolumeName)

		call('docker run --volumes-from ' + containerVolumeName + ' --name ' + containerName + ' -e MYSQL_ROOT_PASSWORD="' + rootPassword + '" -d mysql', shell=True)
	else:
		call('docker run --name ' + containerName + ' -e MYSQL_ROOT_PASSWORD="' + rootPassword + '" -d mysql', shell=True)
	return

def main():
	
	parser = argparse.ArgumentParser(
		description="Creates a new MySQL container using the mysql image", 
		prog="dmysql-server")

	parser.add_argument("containerName", help="The name of the mysql container to create")
	parser.add_argument("rootPassword", help="The root password to define when creating the container")
	parser.add_argument("--with-volume", help="Creates a data-only container", action='store_true')

	args = parser.parse_args()

	# If there's a volume
	if args.with_volume:
		runContainer(containerName=args.containerName, rootPassword=args.rootPassword, hasVolume=True)
	else:
		runContainer(containerName=args.containerName, rootPassword=args.rootPassword)

	return

# Execute main
main()

mot_boot.sql

CREATE TABLE IF NOT EXISTS  TESTRESULT (
	TESTID INT UNSIGNED
	,VEHICLEID INT UNSIGNED
	,TESTDATE DATE
	,TESTCLASSID CHAR(2)
	,TESTTYPE CHAR(2)
	,TESTRESULT CHAR(3)
	,TESTMILEAGE INT UNSIGNED
	,POSTCODEREGION CHAR(2)
	,MAKE CHAR(30)
	,MODEL CHAR(30)
	,COLOUR CHAR(16)
	,FUELTYPE CHAR(1)
	,CYLCPCTY INT UNSIGNED
	,FIRSTUSEDATE DATE
	,PRIMARY KEY (TESTID)
	,INDEX IDX1 (TESTDATE, TESTTYPE, TESTRESULT, TESTCLASSID) 
	)
;

CREATE TABLE IF NOT EXISTS TESTITEM (
	TESTID INT UNSIGNED
	,RFRID SMALLINT UNSIGNED
	,RFRTYPE CHAR(1)
	,LATLOCATIONID CHAR(1)
	,LONGLOCATIONID CHAR(1)
	,VERTLOCATIONID CHAR(1)
	,DMARK CHAR(1)
	,INDEX IDX1 (TESTID)
	,INDEX IDX2 (RFRID)
	)
;


CREATE TABLE IF NOT EXISTS TESTITEM_DETAIL (
	RFRID SMALLINT UNSIGNED
	,TESTCLASSID CHAR(2)
	,TSTITMID SMALLINT UNSIGNED
	,MINORITEM CHAR(1)
	,RFRDESC CHAR(250)
	,RFRLOCMARKER CHAR(1)
	,RFRINSPMANDESC TEXT(500)
	,RFRADVISORYTEXT CHAR(250)
	,TSTITMSETSECID SMALLINT UNSIGNED
	,PRIMARY KEY (RFRID, TESTCLASSID)
	,INDEX IDX1 (TSTITMID, TESTCLASSID)
	,INDEX IDX2 (TSTITMSETSECID, TESTCLASSID)
	)
;

CREATE TABLE IF NOT EXISTS TESTITEM_GROUP (
	TSTITMID SMALLINT UNSIGNED
	,TESTCLASSID CHAR(2)
	,PARENTID SMALLINT UNSIGNED
	,TSTITMSETSECID SMALLINT UNSIGNED
	,ITEMNAME CHAR(100)
	,PRIMARY KEY (TSTITMID, TESTCLASSID)
	,INDEX IDX1 (PARENTID, TESTCLASSID)
	,INDEX IDX2(TSTITMSETSECID, TESTCLASSID)
	)
;





CREATE TABLE IF NOT EXISTS FUEL_TYPES (
	FUELTYPE CHAR(1)
	,FUELTYPEDESC CHAR(30)
	,PRIMARY KEY (FUELTYPE)
);




CREATE TABLE IF NOT EXISTS TEST_OUTCOME (
	TESTRESULT CHAR(3),
	TESTRESULTDESC CHAR(35)
	,PRIMARY KEY (TESTRESULT)
);


CREATE TABLE IF NOT EXISTS TEST_TYPES (
	TESTTYPE CHAR(2)
	,TESTTYPEDESC CHAR(60)
	,PRIMARY KEY (TESTTYPE)
);

LOAD DATA LOCAL INFILE '/tmp/import/mdr_test_lookup_tables/mdr_fuel_types.txt'
INTO TABLE FUEL_TYPES
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'
;


LOAD DATA LOCAL INFILE '/tmp/import/mdr_test_lookup_tables/mdr_test_item_detail.txt'
INTO TABLE TESTITEM_DETAIL
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'
;

LOAD DATA LOCAL INFILE '/tmp/import/mdr_test_lookup_tables/mdr_test_item_group.txt'
INTO TABLE TESTITEM_GROUP
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'
;


LOAD DATA LOCAL INFILE '/tmp/import/mdr_test_lookup_tables/mdr_test_outcome.txt'
INTO TABLE TEST_OUTCOME
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'
;

LOAD DATA LOCAL INFILE '/tmp/import/mdr_test_lookup_tables/mdr_test_types.txt'
INTO TABLE TEST_TYPES
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'
;

LOAD DATA LOCAL INFILE '/tmp/import/test_item_2013.txt'
INTO TABLE TESTITEM
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'
;

LOAD DATA LOCAL INFILE '/tmp/import/test_result_2013.txt'
INTO TABLE TESTRESULT
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'
;