Packaging MySQL in a container

Now that I have apache and PHP running in separate containers. I wanted to run MySQL in a container too. A google search revealed that MySQL uses port 3306. So I created a dockerfile inside mysql folder.  And this is how it looked

FROM ubuntu
RUN ["apt-get","update"]
RUN apt-get install mysql-server -y
EXPOSE 3306
ENTRYPOINT service mysql start && tail -f /dev/null

And also placed a db.php inside public_html folder with the following lines

<h1>DB TEST</h1>
<h4>Attempting MySQL connection from php...</h4>
<?php
$host_name = 'mysql';
$user = 'root';
$passwd = '';
$conn = new mysqli($host_name, $user, $passwd);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected to MySQL successfully!";
?>

So now to build and run it

docker build -t mysql  ./mysql

docker run -d --network testnet --network-alias mysql --name mysqlcontainer mysql

based on my experience with PHP container, I gave mysql a network-alias as well as connected it to my testnet and then I started up my apachecontainer as well as phpcontainer. Then I browsed to http://localhost:100/db.php but got an error that said connection refused.

Error 1:

I couldn't find more details so I connected to the apachecontainer using

docker exec -it apachecontainer /bin/bash

and installed mysql-client inside the apache container and ran the following command 

mysql -u root  'mysql'  -h mysql -P 3306

It said "ERROR 2003 (HY000): Can't connect to MySQL server on 'mysql' (111)". Searching for that in google revealed that MySQL by default accepts connections only from localhost and to change it I need to make changes to the cnf file used by it. I found that MySQL was using the cnf file found at the following location '/etc/mysql/mysql.conf.d/mysqld.cnf'. So I copied it out

docker cp  mysqlcontainer:/etc/mysql/mysql.conf.d/mysqld.cnf mysql/mysqld.cnf

I commented out the line that said "bind-address = 127.0.0.1". And changed my dockerfile as

FROM ubuntu
RUN ["apt-get","update"]
RUN apt-get install mysql-server -y
COPY mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf
EXPOSE 3306
ENTRYPOINT service mysql start && tail -f /dev/null

and then I built the image and ran it as before. This time I got a not allowed to connect error.

Error 2:

I checked it using mysql-client in apachecontainer and this was the error that I got "ERROR 1130 (HY000): Host 'apachecontainer.testnet' is not allowed to connect to this MySQL server". Based on the solution here. I modified the dockerfile as

FROM ubuntu
RUN ["apt-get","update"]
RUN apt-get install mysql-server -y
COPY mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf
RUN service mysql start
RUN mysql -u root -D mysql -e "update user set host='%' where host='localhost';"
EXPOSE 3306
ENTRYPOINT service mysql start && tail -f /dev/null

and when I built it I got the following warning and error

[Warning] World-writable config file '/etc/mysql/mysql.conf.d/mysqld.cnf' is ignored.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

To fix the first warning I had to change permission of the mysqld.cnf file. But the error was confusing since that error is supposed to mean that MySQL is not running at all but I had started it in the previous line. It seems each command is run in a separate intermediate container. I changed my dockerfile as follows to remove the warning and error

FROM ubuntu
RUN ["apt-get","update"]
RUN apt-get install mysql-server -y
COPY mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf
RUN chmod 0444 /etc/mysql/mysql.conf.d/mysqld.cnf
RUN service mysql start && mysql -u root -D mysql -e "update user set host='%' where host='localhost';"
EXPOSE 3306
ENTRYPOINT service mysql start && tail -f /dev/null

I added the mysql start command and the query to the same line. And now I was able to build the image and run it. But again when I connected to it I got an Access denied error message.

Error 3:

I checked it using mysql-client in apachecontainer and this was the error that I got "ERROR 1698 (28000): Access denied for user 'root'@'apachecontainer.testnet'". I need to set a password for root user. So again changed my dockerfile

FROM ubuntu
RUN ["apt-get","update"]
RUN apt-get install mysql-server -y
COPY mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf
RUN chmod 0444 /etc/mysql/mysql.conf.d/mysqld.cnf
RUN service mysql start && mysql -u root -D mysql -e "update user set host='%' where host='localhost';"
RUN service mysql start && mysql -u root -D mysql -e "ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'newpass';"
EXPOSE 3306
ENTRYPOINT service mysql start && tail -f /dev/null

I set a new password ie newpass for the root user. And also updated the password in the db.php

$host_name = 'mysql';
$user = 'root';
$passwd = 'newpass';

And built the image and ran the container with the commands used earlier and now when I browsed to http://localhost:100/db.php, I got the connection success message.

Securing MySQL Installation:

I am sure I was not doing it the right way and there must be other ways of doing it. But this was the only way I could think of.

docker exec -it mysqlcontainer /bin/bash

mysql_secure_installation

I clicked n for 'Disallow root login remotely' option and also didn't set a new root password since I already had one. I guess I could use the 'docker commit' to save the changes to an image.

Configuring phpMyAdmin:

First I downloaded phpMyAdmin zip file from here. And extracted all of its contents to the public_html/ phpMyAdmin folder. 

1. Inside the phpMyAdmin folder I Copy pasted the config.sample.inc.php as config.inc.php. 
2. Edited the config.inc.php file, replaced the line that says $cfg['Servers'][$i]['host'] = 'localhost'; with $cfg['Servers'][$i]['host'] = 'mysql'; (ie the network-alias for the mysqlcontainer)
3. Set the $cfg['blowfish_secret'] . I used a blowfish secret generator found here. 
4. Then I created a folder named tmp inside the phpMyAdmin folder and set permission so that anyone can change the content.
5. Now I could login with username (root) and password (newpass) at http://localhost:100/phpMyAdmin and It showed a warning like "The phpMyAdmin configuration storage is not completely configured, some extended features have been deactivated" clicking the "Find out why" option told me to create a Datatabase for phpMyAdmin. I just clicked the Create option and it got created.

Using docker-compose file:

I was also able to set up the services using the following docker-compose file

version: "3.7"
services:
  php:
    image: "phptest"
    networks:
      - backend
    volumes:
      - ./public_html/:/var/www/html/
  apache:
    image: "apache2"
    depends_on:
      - php
    networks:
      - backend
    ports:
      - "8080:80"
    volumes:
      - ./public_html/:/var/www/html/
  mysql:
    image: mysql
    networks:
      - backend
networks:
  backend:

and started the services using 'docker-compose up -d'. I was able to connect to the database without any problems. Now with a bit of difficulty I have setup Apache,PHP and MySQL each running in a different containers with ubuntu base image.