MySQL on Docker

Numerous articles were written on how Docker is going to change the IT as we know it. Removing the need for full/para virtualization and configuration management frameworks.

While these statements are a bit exaggerated in my opinion, it seems that the technology is here to stay and is being rapidly adopted especially by the SaaS/web companies for obvious reasons such as portability and lower footprint than traditional Hypervisors.

In this short post I'd like to present a small "Howto" on running a MySQL (now MariaDB) DB on a Docker container, and present you some potential pitfalls ,so let's get started...

We will create a Docker file, which is our bootstrapping manifest for our DB image:

	FROM ubuntu
	RUN apt-get update
	RUN apt-get upgrade -y
	RUN apt-get -y install mysql-server
	RUN sed -i 's/^bind-address/#bind-adress/g' /etc/mysql/my.cnf
	RUN /etc/init.d/mysql start && echo "GRANT ALL ON *.* TO admin@'%' IDENTIFIED BY 'admin' WITH GRANT OPTION; FLUSH PRIVILEGES" | mysql -u root
	EXPOSE 3306
	CMD ["/usr/bin/mysqld_safe"]

view raw Docker Mysql hosted with ❤ by GitHub

OK, so what we go here? We are pulling an Ubuntu image out of the Docker repository, installing the server and making sure the it is not bound to 'localhost', with some 'sed' magic, all in all pretty standard.

If more modifications were required for my.conf (and in real life scenario this would probably be mandatory), obviously 'sed' will be an ugly way to modify it so we could create a local copy of my.conf, make all the modification , add it to our Docker file and run the build process:

ADD ./my.cnf /etc/mysql/my.cnf

view raw mysql_example2 hosted with ❤ by GitHub

	docker build -t mysql-test .
	docker run -d -p 3306:3306 test-mysql

view raw mysql_example_3 hosted with ❤ by GitHub

At this point we will be able to connect both from host and from other containers through a TCP socket:

	[root@vm02 test]# lsof -i :3306
	COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
	docker 11267 root 4u IPv6 51821 0t0 TCP *:mysql (LISTEN)
	[root@vm02 test]# mysql -u admin --protocol tcp -padmin -e "show databases"
	+--------------------+
	| Database |
	+--------------------+
	| information_schema |
	| mysql |
	| performance_schema |
	+--------------------+

view raw test hosted with ❤ by GitHub

But what about data persistence?  Remember that all the local data that is currently running in the container is ephemeral... While we could do something as:

docker run -d -p 3306:3306 -v /data/mysql:/var/lib/mysql test-mysql

view raw docker_run_persistent_storage hosted with ❤ by GitHub

This would delete our system data (tables with metadata), so what's the solution?

We need to add a wrapper script that will re-initialize the db in case there is no metadata available. The script can be added to the Docker file via 'ADD' statement:

	cat << EOF > mysql-init-script.sh
	#if ibdata exists, simply start mysql
	if [ -f /var/lib/mysql/ibdata1 ] ;then
	/usr/bin/mysqld_safe
	#if not, initialize db and re-grant permissions
	else
	/usr/bin/mysql_install_db && \
	/usr/bin/mysqld_safe
	/usr/bin/mysql -uroot -e "GRANT ALL ON *.* TO admin@'%' IDENTIFIED BY 'admin' WITH GRANT OPTION; FLUSH PRIVILEGES"
	fi
	EOF

view raw mysql-init-script hosted with ❤ by GitHub

	FROM ubuntu
	RUN apt-get update
	RUN apt-get upgrade -y
	RUN apt-get -y install mysql-client mysql-server
	RUN sed -i -e"s/^bind-address\s*=\s*127.0.0.1/bind-address = 0.0.0.0/" /etc/mysql/my.cnf
	ADD ./mysql-init-script.sh /tmp/mysql-init-script.sh
	EXPOSE 3306
	CMD ["/bin/bash -c /tmp/mysql-init-script.sh"]

view raw new_dockerfile hosted with ❤ by GitHub

That's better, now our DB runs on a persistent storage (/data/mysql - on the host machine which can be external SAN or NAS storage).