Setting up an MySQL database

[daansk44]

Hi everyone,

Maybe I’m going to ask an obious question, but I’m going to drop it anyway

One of the problems I’m facing is that I cannot connect to my MySQL app.
I checked this post: Fail to run MySQL app from Dockerfile and it seems that I can run the MySQL app but not connect to it on my Laravel application (also running in Fly.io).

When looking into the article I notices that there was a mount set but never used in the docker container (Am I right? and how can I do this for MySQL)

My Dockerfile is something like this:

FROM mysql:5.7.38

RUN chown -R mysql:root /var/lib/mysql/

ENV MYSQL_ALLOW_EMPTY_PASSWORD true

ARG MYSQL_DATABASE=database
ARG MYSQL_USER=root
ARG MYSQL_PASSWORD=root122211
ARG MYSQL_ROOT_PASSWORD=root122211

ENV MYSQL_DATABASE=${MYSQL_DATABASE}
ENV MYSQL_USER=${MYSQL_USER}
ENV MYSQL_PASSWORD=${MYSQL_PASSWORD}
ENV MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD}

EXPOSE 3306

CMD ["mysqld"]

The secrets are set

Dockerfile from laravel application

# syntax = docker/dockerfile:experimental
FROM alpine:edge as base

LABEL fly_launch_runtime="laravel"

RUN apk update \
    && apk add curl zip unzip tzdata supervisor nginx htop vim ca-certificates rsync \
           php8           php8-cli        php8-pecl-mcrypt \
           php8-soap      php8-openssl    php8-gmp \
           php8-pdo_odbc  php8-json       php8-dom \
           php8-pdo       php8-zip        php8-pdo_mysql \
           php8-sqlite3   php8-pdo_pgsql  php8-bcmath \
           php8-gd        php8-odbc       php8-pdo_sqlite \
           php8-gettext   php8-xmlreader  php8-bz2 \
           php8-iconv     php8-pdo_dblib  php8-curl \
           php8-ctype     php8-phar       php8-xml \
           php8-common    php8-mbstring   php8-tokenizer \
           php8-xmlwriter php8-fileinfo   php8-opcache \
           php8-simplexml php8-pecl-redis php8-sockets \
           php8-pcntl     php8-posix      php8-pecl-swoole \
           php8-fpm \
    && cp /etc/nginx/nginx.conf /etc/nginx/nginx.old.conf \
    && rm -rf /etc/nginx/http.d/default.conf \
    && curl -sS https://getcomposer.org/installer | php -- --install-dir=/usr/local/bin --filename=composer \
    && adduser -D -u 1000 -g 'app' app \
    && addgroup nginx app \
    && mkdir -p /var/run/php \
    && chown -R app:app /var/run/php \
    && mkdir -p /var/www/html

WORKDIR /var/www/html
# copy application code, skipping files based on .dockerignore
COPY . /var/www/html

# Install dependencies, configure server
# For the time being, we run "composer update" as best effort to get php 8.0 working
RUN composer update \
    && composer install --optimize-autoloader --no-dev \
    && mkdir -p storage/logs \
    && chown -R app:app /var/www/html \
    && /usr/bin/crontab docker/crontab \
    && mv docker/supervisor.conf /etc/supervisord.conf \
    && mv docker/nginx.conf /etc/nginx/nginx.conf \
    && mv docker/server.conf /etc/nginx/server.conf \
    && mv docker/php.ini /etc/php8/conf.d/php.ini \
    && sed -i 's/protected \$proxies/protected \$proxies = "*"/g' app/Http/Middleware/TrustProxies.php

# If we're not using Octane, configure php-fpm
RUN if ! grep -Fq "laravel/octane" /var/www/html/composer.json; then \
        rm -rf /etc/php8/php-fpm.conf; \
        rm -rf /etc/php8/php-fpm.d/www.conf; \
        mv docker/php-fpm.conf /etc/php8/php-fpm.conf; \
        mv docker/app.conf /etc/php8/php-fpm.d/app.conf; \
    elif grep -Fq "spiral/roadrunner" /var/www/html/composer.json; then \
        if [ -f ./vendor/bin/rr ]; then ./vendor/bin/rr get-binary; fi; \
        rm -f .rr.yaml; \
    fi

# clear Laravel cache that may be left over
RUN composer dump-autoload \
    && php artisan optimize:clear \
    && chmod -R ug+w /var/www/html/storage \
    && chmod -R 755 /var/www/html \
    && php artisan migrate:fresh --force

# Multi-stage build: Build static assets
FROM node:14 as node_modules_go_brrr

RUN mkdir /app

RUN mkdir -p  /app
WORKDIR /app
COPY . .
RUN if [ -f "yarn.lock" ]; then \
        yarn install; \
    elif [ -f "package-lock.json" ]; then \
        npm ci --no-audit; \
    else \
        npm install; \
    fi

# Create final container, adding in static assets
FROM base

COPY --from=node_modules_go_brrr /app/public /var/www/html/public-npm
RUN rsync -ar /var/www/html/public-npm/ /var/www/html/public/ \
    && rm -rf /var/www/html/public-npm

# The same port nginx.conf is set to listen on and fly.toml references (standard is 8080)
EXPOSE 8080

ENTRYPOINT ["/var/www/html/docker/run.sh"]

The migration is breaking:

#13 8.226 In Connection.php line 759:
#13 8.226
#13 8.226   SQLSTATE[HY000] [2002] Connection refused (SQL: SHOW FULL TABLES WHERE tabl
#13 8.226   e_type = 'BASE TABLE')
#13 8.226
#13 8.226
#13 8.226 In Connector.php line 70:
#13 8.226
#13 8.226   SQLSTATE[HY000] [2002] Connection refused
#13 8.226
#13 8.226

I can ping and ssh into the other server (fly ssh console)
It seems that the password is not set correctly? I think

[daansk44]

Connecting to the database seems to work (as root user). I don’t think there is any volume yet

[johan]

You are going to need to setup an additional user and grant privileges for it if you don’t want to use root. Here is an example of the query. (MySQL 8 only). Assumes you have a MYSQL_USER and MYSQL _PASS variables defined.

CREATE USER IF NOT EXISTS '${MYSQL_USER}'@'%';
ALTER USER '${MYSQL_USER}'@'%' IDENTIFIED WITH mysql_native_password BY '${MYSQL_PASS}';
GRANT ALL ON *.* TO '${MYSQL_USER}'@'%';
FLUSH PRIVILEGES;

This gives the user access from any IP, you could further limit this to the IP of the fly app running Laravel.

Additionally I find it use-full to setup password less root access using socket auth in MySQL and limit root to socket auth only.

 CREATE USER IF NOT EXISTS 'root'@'localhost';
 ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;
 DROP USER IF EXISTS 'root'@'%';
 FLUSH PRIVILEGES;

Does require you enable the auth_socket plugin in your config.

[mysqld]
plugin-load-add=auth_socket.so

More inf: See: MySQL :: MySQL 8.0 Reference Manual :: 6.4.1.10 Socket Peer-Credential Pluggable Authentication

[daansk44]

Okay, but what about the mount and the persistent MySQL data.
The data is stored in /var/lib/mysql for the Linux container.

How can I mount an folder on fly.io so the data is stored?

Is it like editing your fly config file:

[mounts]
  source="data"
  destination="/var/lib/mysql"

[shortdiv]

I’m assuming the data is already on the volume? Assuming it is, mounting via the toml file as you described would work!

Otherwise, to get data onto a volume, you’ll need to add it to your Dockerfile, and write a script that copies the file to /var/lib/mysql or some folder on the volume.

[fideloper-fly]

Hi!

I’ve been playing with this and have trouble getting it to work as well. I started with a volume attached by got a similar issue where mysql fails to boot up, related to the initialization steps that the official mysql containers take (to create directories / base MySQL data on first boot).

Here are the steps I took.

First, init an app

mkdir my-mysql
cd my-mysql

fly launch

> app name "my-mysql"
> launch now? No # Don't launch immediately

Then edit the fly.toml file.

Cool hack: You don’t NEED to define a Dockerfile in your case, as it’s not really changing anything. You can edit fly.toml and tell it was Docker image to use (so you don’t need a local Dockerfile):

# fly.toml file generated for fid-mysql on 2022-07-15T14:42:03-05:00

app = "fid-mysql"
kill_signal = "SIGINT"
kill_timeout = 5
processes = []

[mounts]
  source="mysql"
  destination="/var/lib/mysql"

[env]
    MYSQL_DATABASE = "my_db"
    MYSQL_USER = "my_user"

[build]
  image = "mysql:5.7"

Note that I just removed the services section, as it’s not needed for an internal service like this. The TCP health checks against databases don’t Do The Needful™ due to reasons specific to the various databases (they want very specific info sent in their TCP packets, else send errors).

Second, set secrets, create volume:

# Set secrets!
fly secrets set MYSQL_PASSWORD=password MYSQL_ROOT_PASSWORD=password

# create the volume the fly.toml file references
fly volumes create mysql --region dfw --size 6

Then you need to deploy the thing:

fly deploy

This is where I get errors. I’m working on seeing what’s up here! I know others are running MySQL on Fly, so it could be I’m doing something weird.

One strategy I’ll fall back to later, if needed, is to initialize with no attached volume, then add a volume to a different location, copy mysql’s data to it, and then run MySQL again, after changing it’s data directory location (I’m assuming apparmor won’t be an issue in a Docker container as it would in a full Debian server, will have to check).

Come yell at me here if you don’t hear back from me.

[daansk44]

@fideloper-fly

[daansk44]

@fideloper-fly

One strategy I’ll fall back to later, if needed, is to initialize with no attached volume, then add a volume to a different location, copy mysql’s data to it, and then run MySQL again, after changing it’s data directory location (I’m assuming apparmor won’t be an issue in a Docker container as it would in a full Debian server, will have to check).

Come yell at me here if you don’t hear back from me.

It would not make sense in my head if you have to do this. The data folder should be filled when running the container. In the build process the data will put there

[fideloper-fly]

Looks like I was able to get it working with a volume:

The secret was that mounting a disk to /var/lib/mysql triggered the MySQL’s ENTRYPOINT to believe there was data already there (and it never attempted to actually initialize MySQL).

I suspect this is because of the lost+found directory added to that location (/var/lib/mysql/lost+found) when mounting over a directory that already existed (as Linux does).

So a hack that worked was this:

1. Keep the volume mounted at /var/lib/mysql
2. Set the data directory MySQL uses to a subdirectory of /var/lib/mysql

For me, that involved updating the fly.toml file to include:

[experimental]
    cmd = ["--datadir", "/var/lib/mysql/thedata"]

On my next deployment, the new thedata directory was created and populated, and MySQL was able to initialize and then run successfully.

After that I was able to fly ssh console, etc.

Log output screenshot after that tweak:

[fideloper-fly]

Point of clarification: The ENTRYPOINT script appears to search specifically for the directory /var/lib/mysql/mysql (the directory mysql within $DATADIR) to decide if it should initialize or not:

From here: mysql/docker-entrypoint.sh at master · docker-library/mysql · GitHub

	declare -g DATABASE_ALREADY_EXISTS
	if [ -d "$DATADIR/mysql" ]; then
		DATABASE_ALREADY_EXISTS='true'
	fi

However, it’s mysqld itself that chokes if there’s anything in the data directory, it’s not the ENTRYPOINT script’s fault:

(And in this case, the lost+found dir is in the data directory);

[ERROR] --initialize specified but the data directory has files in it. Aborting.

[aristotelesbr]

This worked for me:

CMD ["mysqld", "--ignore-db-dir=lost+found"]

[fideloper-fly]

Oh snap - Good find @aristotelesbr ! They even use the example of lost+found in the docs!

Altho that option is not in MySQL 8 (I guess it’s depreciated)

This option is deprecated in MySQL 5.7. With the introduction of the data dictionary in MySQL 8.0, it became superfluous and was removed in that version

[nshahril2255]

Thank you for your help. But i still stuck at below error

PS C:\Users\Arren\my-mysql> flyctl deploy
==> Verifying app config
→ Verified app config
==> Building image
←[33mWARN ←[0mError connecting to local docker daemon: error during connect: This error may indicate that the docker daemon is not running.: Get “http://%2F%2F.%2Fpipe%2Fdocker_engine/_ping”: open //./pipe/docker_engine: The system cannot find the file specified.
Searching for image ‘mysql:8’ remotely…
image found: img_rlw04n8e3jk4z2yq
==> Creating release
→ release v16 created

→ You can detach the terminal anytime without stopping the deployment
==> Monitoring deployment

1 desired, 1 placed, 0 healthy, 1 unhealthy [restarts: 2]
→ v16 failed - Failed due to unhealthy allocations - no stable job version to auto revert to and deploying as v17

→ Troubleshooting guide at Troubleshooting your Deployment · Fly Docs
Error abort

[jan]

@nshahril2255 go to Monitoring and check it out why it have failed.

If you use default shared instance, it’s killed by OOM (at-least that was my case). I did solve that with custom config file that disables performance schema. It runs, but with some traffic it might be killed again so additional tweaks might be required or using bigger instance should do the job.

# syntax=docker/dockerfile:1.4
FROM mysql:8

RUN <<EOF
cat << FFF > /etc/mysql/conf.d/mini.cnf
[mysqld]
performance_schema = off
FFF
EOF

[daansk44]

How does your complete config looks like?

[fideloper-fly]

I’ve seen that on MySQL8 - It will consistently OOM on the free-tier server size. Mysql 5.7 however will run.

[johan]

@daansk44 MySQL offers a datadir config option that allows you to change the default location. This will however not work if the database is already initialised, you will need to re-init it on the new locations. For more info: MySQL :: MySQL 8.0 Reference Manual :: 2.10.1 Initializing the Data Directory

Doing this during image build is not recommended. MySQL generates various pieces of private data like keys that should not be included in a Docker image. It’s advised to do the init when the fly VM is booted. This is my script, where $MYSQL_VOLUME is a env containing the location of the datadir.

#!/usr/bin/with-contenv bash

file="${0##*/}"

MYSQL_PID_FILE=/var/run/mysqld/mysqld.pid
MYSQL_RUN_DIR=/var/run/mysqld

# Make sure run dir exists
if [[ ! -d $MYSQL_RUN_DIR ]]; then
	mkdir -p $MYSQL_RUN_DIR
fi

# Remove stale MySQL PID file left behind when docker stops container
if [[ -f $MYSQL_PID_FILE ]]; then
	rm -f $MYSQL_PID_FILE
fi

# Ensure that $MYSQL_RUN_DIR (used for socket and lock files) is writable
chown -R mysql:mysql $MYSQL_RUN_DIR
chmod 1777 $MYSQL_RUN_DIR

# Make check command executable (called in /etc/services.d/mysql/run)
chmod u+x /etc/services.d/mysql/data/check

# Initialize MySQL data directory (if needed)
# See https://dev.mysql.com/doc/refman/8.0/en/data-directory-initialization.html
if [[ ! -f $MYSQL_VOLUME/mysql.ibd ]]; then

  echo "[cont-init.d] ${file}: Installing MySQL in ${MYSQL_VOLUME} ..."

  if [[ ! -d $MYSQL_VOLUME ]]; then
  	 mkdir -p $MYSQL_VOLUME
  fi

  # Ensure that $MYSQL_VOLUME (used data storage) is writable
  chown -R mysql:mysql $MYSQL_VOLUME
  chmod 1777 $MYSQL_VOLUME

  /usr/sbin/mysqld --initialize-insecure --datadir=${MYSQL_VOLUME}
fi

Then I’m running MySQL as /usr/sbin/mysqld --datadir=${MYSQL_VOLUME}

Hope that helps.

[bogordesain]

Any complete tutorial For This?

[fideloper-fly]

Yes indeed, that was published to the documentation very recently!

Use a MySQL Database

[daansk44]