Docker Container: MySQL 8, additional scenarios

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
• Using a local empty data folder, without specifying the root user password
• Using a local empty data folder, specifying the root user password

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.
  

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
  

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".

Related Posts