Mysql: How to use host volume in OSX by docker-machine (boot2docker)

[dsxack]

I use boot2docker through virtualbox via docker-machine. I seen #44, but doesn't find answer, how resolve this

docker-compose.yml:

mysql:
  image: mysql
  volumes:
    - ./volumes/mysql:/var/lib/mysql
  environment:
    - MYSQL_ALLOW_EMPTY_PASSWORD=yes

output:

mysql_1    | Running mysql_install_db
mysql_1    | 2015-09-10 21:28:40 0 [Note] /usr/sbin/mysqld (mysqld 5.6.26) starting as process 17 ...
mysql_1    | 2015-09-10 21:28:40 17 [Note] InnoDB: Using atomics to ref count buffer pool pages
mysql_1    | 2015-09-10 21:28:40 17 [Note] InnoDB: The InnoDB memory heap is disabled
mysql_1    | 2015-09-10 21:28:40 17 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
mysql_1    | 2015-09-10 21:28:40 17 [Note] InnoDB: Memory barrier is not used
mysql_1    | 2015-09-10 21:28:40 17 [Note] InnoDB: Compressed tables use zlib 1.2.7
mysql_1    | 2015-09-10 21:28:40 17 [Note] InnoDB: Using Linux native AIO
mysql_1    | 2015-09-10 21:28:40 17 [Note] InnoDB: Not using CPU crc32 instructions
mysql_1    | 2015-09-10 21:28:40 17 [Note] InnoDB: Initializing buffer pool, size = 128.0M
mysql_1    | 2015-09-10 21:28:40 17 [Note] InnoDB: Completed initialization of buffer pool
mysql_1    | 2015-09-10 21:28:40 7f2930843720  InnoDB: Operating system error number 13 in a file operation.
mysql_1    | InnoDB: The error means mysqld does not have the access rights to
mysql_1    | InnoDB: the directory.
mysql_1    | 2015-09-10 21:28:40 7f2930843720  InnoDB: Operating system error number 13 in a file operation.
mysql_1    | InnoDB: The error means mysqld does not have the access rights to
mysql_1    | InnoDB: the directory.
mysql_1    | 2015-09-10 21:28:40 17 [ERROR] InnoDB: Creating or opening ./ibdata1 failed!
mysql_1    | 2015-09-10 21:28:40 17 [ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data!
mysql_1    | 2015-09-10 21:28:40 17 [ERROR] Plugin 'InnoDB' init function returned error.
mysql_1    | 2015-09-10 21:28:40 17 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
mysql_1    | 2015-09-10 21:28:40 17 [ERROR] Unknown/unsupported storage engine: InnoDB
mysql_1    | 2015-09-10 21:28:40 17 [ERROR] Aborting
mysql_1    | 
mysql_1    | 2015-09-10 21:28:40 17 [Note] Binlog end
mysql_1    | 2015-09-10 21:28:40 17 [Note] /usr/sbin/mysqld: Shutdown complete
mysql_1    | 
mysql_1 exited with code 141

[bryanrossUK]

this comment on #44 does a good job of explaining the problem. Unfortunately, there doesn't seem to be a good solution other than storing your database files in a Docker Data Volume Container (rather than a directory on your computer) or change your Virtualbox/boot2docker configuration to have more permissive file access.

[4thAce]

I tried giong the data volume container route but it hit the same InnoDB error. It looks like for dev environments we will just tell our users to provision mysql using homebrew instead of bringing up a container.

[motin]

The solution is to make sure that mysql runs using the same user and group ids as your local OSX user.

Create the following script:

#!/bin/bash
set -e # fail on any error

echo '* Working around permission errors locally by making sure that "mysql" uses the same uid and gid as the host volume'
TARGET_UID=$(stat -c "%u" /var/lib/mysql)
echo '-- Setting mysql user to use uid '$TARGET_UID
usermod -o -u $TARGET_UID mysql || true
TARGET_GID=$(stat -c "%g" /var/lib/mysql)
echo '-- Setting mysql group to use gid '$TARGET_GID
groupmod -o -g $TARGET_GID mysql || true
echo
echo '* Starting MySQL'
chown -R mysql:root /var/run/mysqld/
/entrypoint.sh mysqld --user=mysql --console

Save it as localdb-run.sh or similar, mount it into the mysql container and use it as your starting command.

[dsxack]

@motin, thanks, i will try this solution at weekends.

[motin]

@DsXack Here is a sample implementation: https://github.com/neam/docker-stack/tree/develop/stacks/neam/debian-php-nginx.dna-project-base/stack

[dbaba]

I'd had the same error.
@motin The script worked for me. Thanks!

In docker-compose.yml,

(snip)
  entrypoint: /localdb-run.sh
  volumes:
    - ./path/to/localdb-run.sh:/localdb-run.sh
(snip)

This should work with docker-compose.

[motin]

@dbaba Great that it worked for you as well :) Here is a full docker-compose implementation with the script:

docker-compose.yml
stack/localdb/run.sh

Full explanation:

Step 1: Add the script run.sh somewhere in your project:

#!/bin/bash
set -e

# Script to workaround docker-machine/boot2docker OSX host volume issues: https://github.com/docker-library/mysql/issues/99

echo '* Working around permission errors locally by making sure that "mysql" uses the same uid and gid as the host volume'
TARGET_UID=$(stat -c "%u" /var/lib/mysql)
echo '-- Setting mysql user to use uid '$TARGET_UID
usermod -o -u $TARGET_UID mysql || true
TARGET_GID=$(stat -c "%g" /var/lib/mysql)
echo '-- Setting mysql group to use gid '$TARGET_GID
groupmod -o -g $TARGET_GID mysql || true
echo
echo '* Starting MySQL'
chown -R mysql:root /var/run/mysqld/
/entrypoint.sh mysqld --user=mysql --console

Step 2: Change the command in docker-compose.yml to run this script instead of the ordinary command.

Example:

# Local database server to mimic a cloud database
localdb:
  image: mysql:5.6.27
  volumes:
    - ./stack/localdb/.db/mysql:/var/lib/mysql:rw
    - ./stack/localdb/:/stack/localdb:rw
  ports:
    - "3306"
  environment:
    MYSQL_ROOT_PASSWORD: "local-mysql-pass"
  command: "/stack/localdb/run.sh"

[viktorsteinwand]

@motin @dbaba: Many thanks! It works.

[yangjm]

@motin Thank you your script and yml works perfectly.

[IanEdington]

Thanks @motin

I know this was probably clear to everyone already but when I tried to create a container with this script I had a number of issues with permissions and adding the file. This was my solution (incase someone like me has this problem in the future).

Project Folder
 ├─ localdb-run.sh  - from @motin
 └─ Dockerfile

Dockerfile

FROM mysql
MAINTAINER Ian Edington "[email protected]"

ENV MYSQL_ROOT_PASSWORD password
ENV MYSQL_PASSWORD password
ENV MYSQL_DATABASE databasename

# this need to stay the same for script to work
ENV MYSQL_USER mysql

COPY ./localdb-run.sh /
RUN chmod 755 /localdb-run.sh

ENTRYPOINT ["/localdb-run.sh"] 

Build and run container

docker build -t IanEdington/mysql .
docker run --name mysql -v /Users/path/to/mysql/directory:/var/lib/mysql -d IanEdington/mysql .

[roocell]

Thanks @motin and @IanEdington - works great.
I've been spending 10X more time fighting docker<->osx issues than I've been actually doing what I've intended to do.

[zmoon111]

thx, it does work!

[sebastianvilla]

Quick note: you need to chmod +x localdb-run.sh on the host (your computer) or use "sh path/to/localdb-run.sh" in your command or entrypoint

[jstdoit]

It works for me. I'm using a MacBook pro

[gharcombe-minson]

Thank you @motin - amazing fix and unblocks my efforts to improve our dev environment!

[motin]

@gharcombe-minson Great to hear :)

For anyone wanting to try out an example of the workaround easily, follow the instructions on Installation and Usage on https://github.com/neam/docker-stack/tree/9a90433a3a29ca24ad93c84f76d7245528b12e63/stacks/debian-php-nginx.database/stack

[lmikelionis]

Hi, I'm getting a new error - space header page consists of zero bytes in data file ./ibdata1.

Full log:

db | * Working around permission errors locally by making sure that "mysql" uses the same uid and gid as the host volume
db | -- Setting mysql user to use uid 1000
db | -- Setting mysql group to use gid 50
db |
db | * Starting MySQL
db | 2016-01-10 16:39:48 0 [Note] mysqld (mysqld 5.6.27) starting as process 20 ...
db | 2016-01-10 16:39:48 20 [Warning] Setting lower_case_table_names=2 because file system for /var/lib/mysql/ is case insensitive
db | 2016-01-10 16:39:48 20 [Note] Plugin 'FEDERATED' is disabled.
db | mysqld: Table 'mysql.plugin' doesn't exist
db | 2016-01-10 16:39:48 20 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
db | 2016-01-10 16:39:48 20 [Note] InnoDB: Using atomics to ref count buffer pool pages
db | 2016-01-10 16:39:48 20 [Note] InnoDB: The InnoDB memory heap is disabled
db | 2016-01-10 16:39:48 20 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
db | 2016-01-10 16:39:48 20 [Note] InnoDB: Memory barrier is not used
db | 2016-01-10 16:39:48 20 [Note] InnoDB: Compressed tables use zlib 1.2.8
db | 2016-01-10 16:39:48 20 [Note] InnoDB: Using Linux native AIO
db | 2016-01-10 16:39:48 20 [Note] InnoDB: Using CPU crc32 instructions
db | 2016-01-10 16:39:48 20 [Note] InnoDB: Initializing buffer pool, size = 128.0M
db | 2016-01-10 16:39:48 20 [Note] InnoDB: Completed initialization of buffer pool
db | 2016-01-10 16:39:48 20 [Note] InnoDB: Restoring page 0 of tablespace 0
db | 2016-01-10 16:39:48 20 [Warning] InnoDB: Doublewrite does not have page_no=0 of space: 0
db | 2016-01-10 16:39:48 20 [ERROR] InnoDB: space header page consists of zero bytes in data file ./ibdata1
db | 2016-01-10 16:39:48 20 [ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data!
db | 2016-01-10 16:39:48 20 [ERROR] Plugin 'InnoDB' init function returned error.
db | 2016-01-10 16:39:48 20 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
db | 2016-01-10 16:39:48 20 [ERROR] Unknown/unsupported storage engine: InnoDB
db | 2016-01-10 16:39:48 20 [ERROR] Aborting

Any ideas, on this?

[lmikelionis]

OK, after workarounds with deleting ibdata1.
I had ran into another error:

2016-01-10 16:51:22 17 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist

[yosifkit]

@lmikelionis my guess is that there might have been an error in writing to the database earlier which is why ibdata1 had issues. I think there is probably data loss by deleting it, but I have no idea how you would recover from that.

[hklindworth]

Another workaround could be starting mysql as root:

Add this to your Dockerfile:
RUN sed -e 's/user._=._mysql/user=root/' -i /etc/mysql/my.cnf

[albingeorge]

@lmikelionis I got the same error. Did you figure out what went wrong here?

[lmikelionis]

@albingeorge: solved that by changin UID: https://github.com/VilniusTechnology/docker-vtech-ci/blob/master/mysql/script/permissions.sh dont think thats possible to to in Dockerfile only, need this "helping" startup script, or smth similar...

[lalop]

This workaround worked a moment for me but now I get this new error :

 Could not write unix socket lock file /var/run/mysqld/mysqld.sock.lock errno 28.

[lalop]

ok, my vm was full -_-'

[dmitrym0]

The work around doesn't function for me, Docker 1.11.1 on OS X 10.10.5. TARGET_UID=$(stat -c "%u" /var/lib/mysql) always return a 1000, and not what the UID of my account on mac. Any thoughts?

[IanEdington]

The easiest work around is to get the docker for Mac beta.

https://blog.docker.com/2016/03/docker-for-mac-windows-beta/

This is a direct implementation of docker on OSX, which means it has access
to volumes the way it does on Linux systems.

It takes about 2 days to get the invite after requesting it here:
https://beta.docker.com/

On Sun, Jun 5, 2016 at 00:29 Dmitry [email protected] wrote:

The work around doesn't function for me, Docker 1.11.1 on OS X 10.10.5. TARGET_UID=$(stat
-c "%u" /var/lib/mysql) always return a 1000, and not what the UID of my
account on mac. Any thoughts?

—
You are receiving this because you were mentioned.

Reply to this email directly, view it on GitHub
https://github.com/docker-library/mysql/issues/99#issuecomment-223792549,
or mute the thread
https://github.com/notifications/unsubscribe/AGIJ_xDbnp95qutwSJBAM77AiibBxHGDks5qIlCcgaJpZM4F7Vn4
.

[viktorsteinwand]

@dmitrym0 Are you using the docker-machine-nfs available at https://github.com/adlogix/docker-machine-nfs? If not, that might be the reason for described issue...

[dmitrym0]

Ah thanks @IanEdington, requested!

@viktorsteinwand I'm not sure. I know docker is hosted within a virtualbox host running on my mac.

[motin]

@dmitrym0 How does your docker-compose.yml look like? Are you using Docker Toolkit on OSX?

[dmitrym0]

@motin: I am on OSX 10.10.5, Docker 1.11.1.

Here's my Dockerfile:

db:
  image: mysql/mysql-server:5.6
  ports:
    - "13306:3306"
  env_file:
    - 'docker/.env.db'
  volumes:
    - '/Users/dmitry/workspace/drail/dbdir:/var/lib/mysql'

IanEdington above mentioned that it's working in the latest docker beta.

[clarkewd]

As @IanEdington says, get docker for mac beta - but no invite is needed - just download it at https://docs.docker.com/docker-for-mac/

[bhearsum]

Has anyone else encountered this error, even with the latest Docker for Mac, after removing the entrypoint workaround and just mounting a local directory for /var/lib/mysql?
mysqld: Can't create/write to file '/var/lib/mysql/is_writable' (Errcode: 13 - Permission denied)

[einfallstoll]

@mozbhearsum Yes, I'm currently running into the same issue and don't know what to do. Pretty much tried everything.

[johnsBeharry]

@einfallstoll @mozbhearsum Ye I'm running into that same permissions issue with mysql:5.7 – still using the older Docker Toolbox.

[bhearsum]

No surprise that you're hitting it on Docker Toolbox - there's no native support for file sharing there.

[einfallstoll]

It works using the latest version of Docker for Mac. They got rid of these permission and ownership problems now.

[raubreywhite]

@mozbhearsum I have this problem and i'm running it on Linux, so I have no idea what is wrong

[maximgeerinck]

I had the same issue before.
Apparently when using docker on windows (in my case) it had problems with the file path. The solution in that case was to create a volume in docker by using the following command:

docker volume create --name=your_volume

then in docker compose you would have something like

  db:
    image: mariadb    
    volumes:
      - volume_db:/var/lib/mysql

Hopes this helps anybody

[mario-rivera]

People using mariadb who're still having issues.
It worked for me using version mariadb:10.0.22

Without having to use the script for user and group permissions,
the mounted volume worked fine because this version doesn't create a tc.log file in the data directory.

[tianon]

Closing given that there's not really anything we can change in the image itself and this is more of an environmental issue; see also https://github.com/docker-library/percona/issues/42#issuecomment-298109103 (especially https://github.com/docker-library/percona/issues/42).

See also https://github.com/docker-library/mysql/pull/161, which should allow running the image with an arbitrary --user value, which may help to get permissions to line up (although won't help with the underlying filesystem needing to support the specific functionality MySQL itself requires).

[boycce]

How can i use @motin 's entry-point snippet with dockers mariadb image with vagrant? e.g

config.vm.define "db" do |d|
    d.vm.provider "docker" do |v|
        v.image = "mariadb:latest"
        v.env = { "MYSQL_ROOT_PASSWORD" => "xxx" }
    end
end