Docker Container: MySQL 8, additional scenarios

March 10, 2020

On the previous post we created a MySQL Docker Container with the following command:


docker run -d -p 3306:3306 --name=container_mysql8 -e MYSQL_ROOT_PASSWORD=root -e MYSQL_ROOT_HOST=% \
image_mysql8 --default-authentication-plugin=mysql_native_password

The resulting container had the database “data” files stored inside the container. However you can also build a container that uses database files stored on a folder of your host machine.

We can define three possible scenarios:


Using an existing data folder

If you already had a previous instance of a MySQL server running on your host machine you can use the databases created on that instance on your docker container.

Lets say that, for example, you had been using MAMP on your machine, and you created several databases there.
On Mac OS X, the folder that contains those databases would be something like: “/Applications/MAMP/db/mysql57

Now lets make a copy of those files to a new folder with the following commands on a terminal window:


mkdir ~/mysql57

cp -r /Applications/MAMP/db/mysql57 ~/mysql57


Then, we will use the parameter “-v ~/mysql57:/var/lib/mysql” to set this new folder as the location of the database files for the Docker Container.

In this example I used data files of a previous version of MySQL. The version 5.7. 
Then, when the database is initialized--on our container using the newer version 8--the data files will be upgraded.
Please note that this upgrade will cause these database files to not be usable on the previous version (5.7)


docker run -d -p 3306:3306 --name=container_mysql8 -v ~/mysql57:/var/lib/mysql \
-e MYSQL_ROOT_HOST=% image_mysql8

  • Make sure that the folder with the existing data files is shared and known to Docker.
    You can share the folder by going to the “Preferences / File Sharing” option of the Docker menu.
    If the folder is not shared you will get an error message like this, on Mac OS X:
    docker: Error response from daemon: Mounts denied: 
    The path /Users/USERNAME/mysql57
    is not shared from OS X and is not known to Docker.
    You can configure shared paths from Docker -> Preferences... -> File Sharing.
    See https://docs.docker.com/docker-for-mac/osxfs/#namespaces for more info.
    
  • Notice that we don't need to specify the root user password, because MySQL will use the password set on our existing database files.
  • As the existing files belong to a previous version of MySQL, the logs will show that an upgrade was performed:
    
            docker logs container_mysql8
    Output:
    2020-02-17T03:58:10.167661Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.19) starting as process 1
    2020-02-17T03:58:10.563879Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
    2020-02-17T03:58:41.305954Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
    2020-02-17T03:58:47.586804Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80019' started.
    2020-02-17T03:59:11.822160Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80019' completed.
    


Using a local empty data folder, without specifying the root user password

We can also use an empty folder on our host machine to store the database files.

Lets say that we have created the “my_db” folder on our home folder.

Then, we will use the parameter “-v ~/my_db:/var/lib/mysql” to set our local folder as the location of the database files for the Docker Container.


docker run -d -p 3306:3306 --name=container_mysql8 -v ~/my_db:/var/lib/mysql \
-e MYSQL_ROOT_HOST=% image_mysql8 --default-authentication-plugin=mysql_native_password

  • In this case, as we have not specified a password for our root user, a random will be assigned automatically when the Docker Container is created.
    To find the random password we will review the logs:
    
        docker logs container_mysql8
    And search for the “GENERATED ROOT PASSWORD” text:
    [Entrypoint] MySQL Docker Image 8.0.19-1.1.15
    [Entrypoint] No password option specified for new database.
    [Entrypoint]   A random onetime password will be generated.
    [Entrypoint] Initializing database
    
    ...
    
    [Entrypoint] GENERATED ROOT PASSWORD: ydas[3mID0kyKOmzoHLIw)4HjUf
    
    After you login, you must assign a new password before you can start using the database.

  • If you list the content of the “~/my_db” folder, you will find the folders and files created by MySQL.
    
        ls ~/my_db
    Output:
    #innodb_temp		client-cert.pem		mysql			server-cert.pem
    auto.cnf		client-key.pem		mysql.ibd		server-key.pem
    binlog.000001		ib_buffer_pool		mysql.sock		sys
    binlog.000002		ib_logfile0		mysql.sock.lock		undo_001
    binlog.index		ib_logfile1		performance_schema	undo_002
    ca-key.pem		ibdata1			private_key.pem
    ca.pem			ibtmp1			public_key.pem
    


Using a local empty data folder, specifying the root user password

When you use an empty folder, you can also have the option to set the password.

This way a random password won't be generated, and you can start using your new database with the password you specified.


docker run -d -p 3306:3306 --name=container_mysql8 -v ~/my_db:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=root -e MYSQL_ROOT_HOST=% image_mysql8 --default-authentication-plugin=mysql_native_password

  • The password has been set with the “MYSQL_ROOT_PASSWORD” parameter. In this example command we are using the password "root".