Docker: Create a persistent MySQL data-only container & attach a separate db server:

docker-mysql.sh

#!/bin/bash

# author: Josue Rodriguez <[email protected]>
# (c) October 2016

display_usage_help () {
	echo -e "[Usage]"
	echo -e "\t${0} -c <mysql data dir> <data-only name> <server name> <server port>"
	echo
	echo -e "[Examples]"
	echo -e "\t${0} -c\n"
	echo -e "\t${0} ~/.mysql_dir -c mysql_data mysql_server 3306\n"
	echo -e "\tAPP_NAME=rails APP_PORT=4444 ${0} -c"
	echo
	echo -e "[Special Note]"
	echo -e "\tUsing env variable APP_NAME={...} will create containers using the APP_NAME name as prefix, as if you were to do the following:\n"
	echo -e "\t${0} ~/.rails_mysql_data rails_mysql_data rails_mysql_server 3306"
	echo
	echo -e "[Options]"
	echo -e "\t-h = This help :)\n"
	echo -e "\t-l = List existing mysql (server|data) containers.\n"
	echo -e "\t-bind-ip-from-to = Bind container IP to another containers /etc/hosts file.\n"
	echo -e "\t\t usage: ${0} -bind-ip-from-to <mysql server> <another container> <host aliases>"
	echo -e "\t\t    ex: ${0} -bind-ip-from-to mysql_server rails_app 'rails_db local_mysql'\n"
	echo -e "\t-rm = Remove existing mysql (server|data) containers."
}

create_containers () {
	# create storage directory
	mkdir -p ${DB_DIR}

	# create data-only container and uses ~/.mysql_data as the volume storage
	docker create --name ${CONTAINER_DATA_ONLY} -v ${DB_DIR} mysql

	echo -e "\nCreated MySQL (data-only) Container:"
	docker ps -a | grep -i ${CONTAINER_DATA_ONLY}

	# create db server container, attached to data-only container, to read/write to db data-only container
	docker run -d --name ${CONTAINER_SERVER} --volumes-from ${CONTAINER_DATA_ONLY} -v ${DB_DIR}:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=root -it -p ${CONTAINER_PORT}:3306 mysql
	
	echo -e "\nCreated MySQL (server) Container:"
	docker ps -a | grep -i ${CONTAINER_SERVER}

	echo
	echo "Done :)"
}

remove_existing_containers () {
	RM_APP_NAME=""
	if [ "$1" != "" ]; then
		RM_APP_NAME="${1}_"
	fi
	echo -e "Removing containers: ${RM_APP_NAME}mysql_data + ${RM_APP_NAME}mysql_server\n"
	docker ps -a | egrep -i "${RM_APP_NAME}mysql_(server|data)" | awk '{ print $1 }' | xargs docker stop
	docker ps -a | egrep -i "${RM_APP_NAME}mysql_(server|data)" | awk '{ print $1 }' | xargs docker rm
	echo
	echo "Removed :("
}

get_server_ip_addr () {
	docker inspect --format '{{ .NetworkSettings.IPAddress }}' $1
}

add_server_ip_addr_to_container () {
	CONT_ALIAS="mysql_docker ${@:3}"
	CONTAINER_IP_ADDR="`get_server_ip_addr $1` ${CONT_ALIAS}"
	docker exec $2 bash -c "echo '${CONTAINER_IP_ADDR}' >> /etc/hosts"
	docker exec $2 bash -c "grep mysql_docker /etc/hosts"
}

# display help if asked
if [ "$1" == "-h" ] || [ "$1" == "" ]; then
	display_usage_help
	exit 1
fi

if [ "$1" == "-l" ]; then
	docker ps -a | egrep -i "mysql_(server|data)"
	exit 0
fi

if [ "$1" == "-bind-ip-from-to" ] && [ "$2" != "" ]; then
	add_server_ip_addr_to_container ${@:2}
	exit 0
fi

if [ "$1" == "-rm" ] && [ "$2" != "" ]; then
	remove_existing_containers $2
	exit 0
fi

# -------- Continue and create containers below -----------------
# params
APP=""
DB_DIR=${2:-$HOME/.mysql_data}
CONTAINER_DATA_ONLY=${3:-mysql_data}
CONTAINER_SERVER=${4:-mysql_server}
CONTAINER_PORT=${5:-3306}

if [ "${APP_NAME}" != "" ]; then
	APP=${APP_NAME}
	CONTAINER_DATA_ONLY="${APP_NAME}_${CONTAINER_DATA_ONLY}"
	CONTAINER_SERVER="${APP_NAME}_${CONTAINER_SERVER}"
fi
if [ "${APP_PORT}" != "" ]; then
	CONTAINER_PORT=$APP_PORT
fi

# if checks passed:
if [ "$1" == "-c" ]; then

	DATAONLY_EXIST="`docker ps -a | grep -i ${CONTAINER_DATA_ONLY}`"
	SERVER_EXIST="`docker ps -a | grep -i ${CONTAINER_SERVER}`"
	PORT_USED="`docker ps -a | grep ":${CONTAINER_PORT}->"`"

	# check before creating
	if [ "${PORT_USED}" != "" ]; then
	  echo "Error: Docker container already exist port: ${CONTAINER_PORT}"
	  echo
	  display_usage_help
	  exit 1
	fi

	if [ "${DATAONLY_EXIST}" != "" ]; then
	  echo "Error: Docker (data-only) container already exist: ${CONTAINER_DATA_ONLY}"
	  echo
	  display_usage_help
	  exit 1
	fi

	if [ "${SERVER_EXIST}" != "" ]; then
	  echo "Error: Docker (server) container already exist: ${CONTAINER_SERVER}"
	  echo
	  display_usage_help
	  exit 1
	fi

	create_containers
fi

test.php

<?php

$host = !empty(getenv('DB_HOST')) ? getenv('DB_HOST') : '127.0.0.1';
$db = !empty(getenv('DB_NAME')) ? getenv('DB_NAME') : 'sys';
$user = !empty(getenv('DB_USER')) ? getenv('DB_USER') : 'root';
$pass = !empty(getenv('DB_PASS')) ? getenv('DB_PASS') : 'root';
$table = !empty(getenv('DB_TABLE')) ? getenv('DB_TABLE') : 'user_summary';
$sql = !empty(getenv('DB_SQL')) ? getenv('DB_SQL') : sprintf('SELECT user FROM %s', $table);

$connect = sprintf('mysql:host=%s;dbname=%s', $host, $db);

print 'Connecting: ' . $connect . PHP_EOL . PHP_EOL;

try {
    $dbh = new PDO($connect, $user, $pass);
    $rows = $dbh->query($sql);

    if (!empty($rows)) {
        foreach ($rows as $row) {
            print_r($row);
        }
    } else {
        print 'Warning: SQL statement did not return any results.' . PHP_EOL;
        print 'SQL: ' . $sql . PHP_EOL;
    }

    $dbh = null;
} catch (PDOException $e) {
    print 'Error: ' . $e->getMessage() . PHP_EOL;
    die();
}

Download script & make executable:

curl "https://gist.githubusercontent.com/josue/8e7281a711c492e854503eaa82937ece/raw/docker-mysql.sh" \
     -o docker-mysql.sh && \
     chmod +x docker-mysql.sh;

Quickstart:

./docker-mysql.sh -c

See help:

./docker-mysql.sh -h

[Usage]
    ./docker-mysql.sh -c <mysql data dir> <data-only name> <server name> <server port>

[Examples]
    ./docker-mysql.sh -c

    ./docker-mysql.sh ~/.mysql_dir -c mysql_data mysql_server 3306

    APP_NAME=rails APP_PORT=4444 ./docker-mysql.sh -c

[Special Note]
    Using env variable APP_NAME={...} will create containers using the APP_NAME name as prefix, 
    as if you were to do the following:

    ./docker-mysql.sh ~/.rails_mysql_data rails_mysql_data rails_mysql_server 3306

[Options]
    -h = This help :)

    -l = List existing mysql (server|data) containers.

    -bind-ip-from-to = Bind container IP to another containers /etc/hosts file.

         usage: ./docker-mysql.sh -bind-ip-from-to <mysql server> <another container> <host aliases>
            ex: ./docker-mysql.sh -bind-ip-from-to mysql_server rails_app 'rails_db local_mysql'

    -rm = Remove existing mysql (server|data) containers.

Test using PHP

1. Download the php script below to test:

curl "https://gist.githubusercontent.com/josue/8e7281a711c492e854503eaa82937ece/raw/test.php" -o /tmp/test.php

2. Run the php script like:

php /tmp/test.php

other ways:

DB_HOST=127.0.0.1 DB_NAME=app DB_USER=root DB_PASS=root DB_SQL="SELECT id FROM users LIMIT 5" php /tmp/test.php

3. Expected output:

Connecting: mysql:host=127.0.0.1;dbname=sys
Array
(
[user] => root
[0] => root
)

Use-case:

If you don’t want to install MySQL entirely into your host machine or have separate mysql containers but wish to have one single container which stores all your databases and can be restored (in case you need to rebuild your mysql container) without data-loss, then this script will simple create a data-only container and link your mysql databank files from a known host directory (ie: ~/.mysql_data).

The script also allows you to bind your new data-only mysql container to any existing containers.

Also if your app needs to connect to a remote db host (either via VPN or not), you can simply dump the remote db, create this data-only container and import the db locally. 😉

---

Example Setup:

1. Run the quick start command: ./docker-mysql.sh -c

2. Connect your mysql client to the new mysql server container: localhost // 3306 // {user} // {password} and import your local db dump.

3. Bind your new mysql container to any running container (example binding mysql to PHP container):
   ./docker-mysql.sh -bind-ip-from-to mysql_server php_container_name 'remote-host-db or ip-address'