Run like Hell: Docker-Swarm: How to run a mysql database...

After several tests with docker swarm (setting up a swarm, running with more than on master, running a webserver in a swarm) i am thinking about running a mysql database in a swarm.

If you are running a mysql database on one docker host, you have to set up a docker volume, because otherwise data inside the container is gone, if you restart your database container. You can follow this tutorial, which says, you have to run

docker run --name=mysql1 -d mysql/mysql-server:tag

but there is the persistent volume missing. If you try this one, you are on the right way:

docker run --name mysqldb --volumes-from mysql_data -v /var/lib/mysql:/var/l
ib/mysql -e MYSQL_USER=mysql -e MYSQL_PASSWORD=mysql -e MYSQL_DATABASE=sample -e MYSQL
_ROOT_PASSWORD=supersecret -it -p 3306:3306 mysql

The problem is, that you have to create a container, mysql_data which contains a volume.

What about running a mysql database just with a volume?

First idea is to create a docker volume:

alpine:~# docker volume create mysql_data
mysql_data
alpine:~# docker volume ls
DRIVER VOLUME NAME
local mysql_data

And then start the mysql database:

alpine:~# docker run --name mysqldb -v mysql_data:/var/lib/mysql -e MYSQL_USER=mysql
-e MYSQL_PASSWORD=mysql -e MYSQL_DATABASE=sample -e MYSQL_ROOT_PASSWORD=supersecret -i
t -p 3306:3306 mysql
Initializing database
2017-12-10T12:25:38.890958Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-12-10T12:25:39.114910Z 0 [Warning] InnoDB: New log files created, LSN=45790
2017-12-10T12:25:39.192006Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2017-12-10T12:25:39.269224Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 3b5276c6-dda5-11e7-847f-0242ac110002.
2017-12-10T12:25:39.293895Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2017-12-10T12:25:39.296705Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2017-12-10T12:25:39.734384Z 1 [Warning] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.
2017-12-10T12:25:39.734823Z 1 [Warning] 'user' entry 'mysql.session@localhost' ignored in --skip-name-resolve mode.
2017-12-10T12:25:39.735089Z 1 [Warning] 'user' entry 'mysql.sys@localhost' ignored in --skip-name-resolve mode.
2017-12-10T12:25:39.735325Z 1 [Warning] 'db' entry 'performance_schema mysql.session@localhost' ignored in --skip-name-resolve mode.
2017-12-10T12:25:39.735530Z 1 [Warning] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode.
2017-12-10T12:25:39.735734Z 1 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.
2017-12-10T12:25:39.735969Z 1 [Warning] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.
2017-12-10T12:25:39.736171Z 1 [Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.
Database initialized
Initializing certificates
Generating a 2048 bit RSA private key
............+++
...........................................................................................................................................................................................................+++
unable to write 'random state'
writing new private key to 'ca-key.pem'
-----
Generating a 2048 bit RSA private key
.......................+++
.......+++
unable to write 'random state'
writing new private key to 'server-key.pem'
-----
Generating a 2048 bit RSA private key
.......................+++
.............+++
unable to write 'random state'
writing new private key to 'client-key.pem'
-----
Certificates initialized
MySQL init process in progress...
2017-12-10T12:25:42.948452Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-12-10T12:25:42.949739Z 0 [Note] mysqld (mysqld 5.7.20) starting as process 87 ...
2017-12-10T12:25:42.952799Z 0 [Note] InnoDB: PUNCH HOLE support available
2017-12-10T12:25:42.953188Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-12-10T12:25:42.953550Z 0 [Note] InnoDB: Uses event mutexes
2017-12-10T12:25:42.953828Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2017-12-10T12:25:42.954120Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-12-10T12:25:42.954451Z 0 [Note] InnoDB: Using Linux native AIO
2017-12-10T12:25:42.954900Z 0 [Note] InnoDB: Number of pools: 1
2017-12-10T12:25:42.955173Z 0 [Note] InnoDB: Using CPU crc32 instructions
2017-12-10T12:25:42.956841Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2017-12-10T12:25:42.965639Z 0 [Note] InnoDB: Completed initialization of buffer pool
2017-12-10T12:25:42.969568Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2017-12-10T12:25:42.982838Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2017-12-10T12:25:43.001695Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2017-12-10T12:25:43.002427Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2017-12-10T12:25:43.028474Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2017-12-10T12:25:43.029519Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2017-12-10T12:25:43.029785Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2017-12-10T12:25:43.030165Z 0 [Note] InnoDB: Waiting for purge to start
2017-12-10T12:25:43.080873Z 0 [Note] InnoDB: 5.7.20 started; log sequence number 2565377
2017-12-10T12:25:43.085057Z 0 [Note] Plugin 'FEDERATED' is disabled.
2017-12-10T12:25:43.097783Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2017-12-10T12:25:43.111653Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2017-12-10T12:25:43.112695Z 0 [Warning] CA certificate ca.pem is self signed.
2017-12-10T12:25:43.112387Z 0 [Note] InnoDB: Buffer pool(s) load completed at 171210 12:25:43
2017-12-10T12:25:43.122126Z 0 [Warning] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.
2017-12-10T12:25:43.122654Z 0 [Warning] 'user' entry 'mysql.session@localhost' ignored in --skip-name-resolve mode.
2017-12-10T12:25:43.122922Z 0 [Warning] 'user' entry 'mysql.sys@localhost' ignored in --skip-name-resolve mode.
2017-12-10T12:25:43.123172Z 0 [Warning] 'db' entry 'performance_schema mysql.session@localhost' ignored in --skip-name-resolve mode.
2017-12-10T12:25:43.123424Z 0 [Warning] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode.
2017-12-10T12:25:43.123655Z 0 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.
2017-12-10T12:25:43.125278Z 0 [Warning] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.
2017-12-10T12:25:43.125625Z 0 [Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.
2017-12-10T12:25:43.130811Z 0 [Note] Event Scheduler: Loaded 0 events
2017-12-10T12:25:43.131463Z 0 [Note] mysqld: ready for connections.
Version: '5.7.20' socket: '/var/run/mysqld/mysqld.sock' port: 0 MySQL Community Server (GPL)
2017-12-10T12:25:43.131789Z 0 [Note] Executing 'SELECT * FROM INFORMATION_SCHEMA.TABLES;' to get a list of tables using the deprecated partition engine. You may use the startup option '--disable-partition-engine-check' to skip this check.
2017-12-10T12:25:43.132034Z 0 [Note] Beginning of list of non-natively partitioned tables
2017-12-10T12:25:43.141674Z 0 [Note] End of list of non-natively partitioned tables
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
2017-12-10T12:25:45.325153Z 5 [Warning] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.
2017-12-10T12:25:45.326169Z 5 [Warning] 'user' entry 'mysql.session@localhost' ignored in --skip-name-resolve mode.
2017-12-10T12:25:45.326766Z 5 [Warning] 'user' entry 'mysql.sys@localhost' ignored in --skip-name-resolve mode.
2017-12-10T12:25:45.327430Z 5 [Warning] 'db' entry 'performance_schema mysql.session@localhost' ignored in --skip-name-resolve mode.
2017-12-10T12:25:45.328439Z 5 [Warning] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode.
2017-12-10T12:25:45.329236Z 5 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.
2017-12-10T12:25:45.330139Z 5 [Warning] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.
2017-12-10T12:25:45.330625Z 5 [Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
2017-12-10T12:25:45.355944Z 9 [Warning] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.
2017-12-10T12:25:45.356363Z 9 [Warning] 'user' entry 'mysql.session@localhost' ignored in --skip-name-resolve mode.
2017-12-10T12:25:45.356743Z 9 [Warning] 'user' entry 'mysql.sys@localhost' ignored in --skip-name-resolve mode.
2017-12-10T12:25:45.357080Z 9 [Warning] 'db' entry 'performance_schema mysql.session@localhost' ignored in --skip-name-resolve mode.
2017-12-10T12:25:45.357407Z 9 [Warning] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode.
2017-12-10T12:25:45.358138Z 9 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.
2017-12-10T12:25:45.358579Z 9 [Warning] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.
2017-12-10T12:25:45.358825Z 9 [Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.

2017-12-10T12:25:45.360088Z 0 [Note] Giving 0 client threads a chance to die gracefully
2017-12-10T12:25:45.360365Z 0 [Note] Shutting down slave threads
2017-12-10T12:25:45.360562Z 0 [Note] Forcefully disconnecting 0 remaining clients
2017-12-10T12:25:45.360757Z 0 [Note] Event Scheduler: Purging the queue. 0 events
2017-12-10T12:25:45.360982Z 0 [Note] Binlog end
2017-12-10T12:25:45.361681Z 0 [Note] Shutting down plugin 'ngram'
2017-12-10T12:25:45.361897Z 0 [Note] Shutting down plugin 'BLACKHOLE'
2017-12-10T12:25:45.362098Z 0 [Note] Shutting down plugin 'partition'
2017-12-10T12:25:45.362285Z 0 [Note] Shutting down plugin 'ARCHIVE'
2017-12-10T12:25:45.362470Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL'
2017-12-10T12:25:45.362653Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES'
2017-12-10T12:25:45.362844Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES'
2017-12-10T12:25:45.363019Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
2017-12-10T12:25:45.363206Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
2017-12-10T12:25:45.363455Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
2017-12-10T12:25:45.363740Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
2017-12-10T12:25:45.364038Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
2017-12-10T12:25:45.364313Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
2017-12-10T12:25:45.364603Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
2017-12-10T12:25:45.364886Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
2017-12-10T12:25:45.365129Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
2017-12-10T12:25:45.365333Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
2017-12-10T12:25:45.365507Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
2017-12-10T12:25:45.365678Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED'
2017-12-10T12:25:45.365846Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
2017-12-10T12:25:45.366026Z 0 [Note] Shutting down plugin 'INNODB_METRICS'
2017-12-10T12:25:45.366214Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO'
2017-12-10T12:25:45.366413Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
2017-12-10T12:25:45.366616Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
2017-12-10T12:25:45.366799Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
2017-12-10T12:25:45.366976Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET'
2017-12-10T12:25:45.367142Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX'
2017-12-10T12:25:45.367305Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
2017-12-10T12:25:45.367467Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM'
2017-12-10T12:25:45.367628Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET'
2017-12-10T12:25:45.367796Z 0 [Note] Shutting down plugin 'INNODB_CMP'
2017-12-10T12:25:45.367978Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
2017-12-10T12:25:45.368141Z 0 [Note] Shutting down plugin 'INNODB_LOCKS'
2017-12-10T12:25:45.368307Z 0 [Note] Shutting down plugin 'INNODB_TRX'
2017-12-10T12:25:45.368469Z 0 [Note] Shutting down plugin 'InnoDB'
2017-12-10T12:25:45.368926Z 0 [Note] InnoDB: FTS optimize thread exiting.
2017-12-10T12:25:45.369212Z 0 [Note] InnoDB: Starting shutdown...
2017-12-10T12:25:45.469927Z 0 [Note] InnoDB: Dumping buffer pool(s) to /var/lib/mysql/ib_buffer_pool
2017-12-10T12:25:45.471353Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 171210 12:25:45
2017-12-10T12:25:47.118138Z 0 [Note] InnoDB: Shutdown completed; log sequence number 12169663
2017-12-10T12:25:47.125477Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2017-12-10T12:25:47.126721Z 0 [Note] Shutting down plugin 'MRG_MYISAM'
2017-12-10T12:25:47.127496Z 0 [Note] Shutting down plugin 'MyISAM'
2017-12-10T12:25:47.128253Z 0 [Note] Shutting down plugin 'CSV'
2017-12-10T12:25:47.128958Z 0 [Note] Shutting down plugin 'MEMORY'
2017-12-10T12:25:47.129665Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2017-12-10T12:25:47.130679Z 0 [Note] Shutting down plugin 'sha256_password'
2017-12-10T12:25:47.131807Z 0 [Note] Shutting down plugin 'mysql_native_password'
2017-12-10T12:25:47.132756Z 0 [Note] Shutting down plugin 'binlog'
2017-12-10T12:25:47.135910Z 0 [Note] mysqld: Shutdown complete

MySQL init process done. Ready for start up.

2017-12-10T12:25:47.379637Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-12-10T12:25:47.381062Z 0 [Note] mysqld (mysqld 5.7.20) starting as process 1 ...
2017-12-10T12:25:47.383987Z 0 [Note] InnoDB: PUNCH HOLE support available
2017-12-10T12:25:47.384373Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-12-10T12:25:47.384612Z 0 [Note] InnoDB: Uses event mutexes
2017-12-10T12:25:47.384828Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2017-12-10T12:25:47.385024Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-12-10T12:25:47.385229Z 0 [Note] InnoDB: Using Linux native AIO
2017-12-10T12:25:47.385591Z 0 [Note] InnoDB: Number of pools: 1
2017-12-10T12:25:47.385887Z 0 [Note] InnoDB: Using CPU crc32 instructions
2017-12-10T12:25:47.387049Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2017-12-10T12:25:47.393069Z 0 [Note] InnoDB: Completed initialization of buffer pool
2017-12-10T12:25:47.395207Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2017-12-10T12:25:47.406539Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2017-12-10T12:25:47.417533Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2017-12-10T12:25:47.418058Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2017-12-10T12:25:47.433202Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2017-12-10T12:25:47.434200Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2017-12-10T12:25:47.434456Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2017-12-10T12:25:47.434855Z 0 [Note] InnoDB: Waiting for purge to start
2017-12-10T12:25:47.485468Z 0 [Note] InnoDB: 5.7.20 started; log sequence number 12169663
2017-12-10T12:25:47.487084Z 0 [Note] Plugin 'FEDERATED' is disabled.
2017-12-10T12:25:47.500417Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2017-12-10T12:25:47.502089Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2017-12-10T12:25:47.511981Z 0 [Warning] CA certificate ca.pem is self signed.
2017-12-10T12:25:47.516700Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2017-12-10T12:25:47.517640Z 0 [Note] IPv6 is available.
2017-12-10T12:25:47.518010Z 0 [Note] - '::' resolves to '::';
2017-12-10T12:25:47.518467Z 0 [Note] Server socket created on IP: '::'.
2017-12-10T12:25:47.523590Z 0 [Warning] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.
2017-12-10T12:25:47.524079Z 0 [Warning] 'user' entry 'mysql.session@localhost' ignored in --skip-name-resolve mode.
2017-12-10T12:25:47.524350Z 0 [Warning] 'user' entry 'mysql.sys@localhost' ignored in --skip-name-resolve mode.
2017-12-10T12:25:47.524623Z 0 [Warning] 'db' entry 'performance_schema mysql.session@localhost' ignored in --skip-name-resolve mode.
2017-12-10T12:25:47.524854Z 0 [Warning] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode.
2017-12-10T12:25:47.525118Z 0 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.
2017-12-10T12:25:47.527817Z 0 [Warning] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.
2017-12-10T12:25:47.528270Z 0 [Note] InnoDB: Buffer pool(s) load completed at 171210 12:25:47
2017-12-10T12:25:47.528542Z 0 [Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.
2017-12-10T12:25:47.532739Z 0 [Note] Event Scheduler: Loaded 0 events
2017-12-10T12:25:47.533402Z 0 [Note] mysqld: ready for connections.
Version: '5.7.20' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server (GPL)
2017-12-10T12:25:47.533772Z 0 [Note] Executing 'SELECT * FROM INFORMATION_SCHEMA.TABLES;' to get a list of tables using the deprecated partition engine. You may use the startup option '--disable-partition-engine-check' to skip this check.
2017-12-10T12:25:47.534079Z 0 [Note] Beginning of list of non-natively partitioned tables
2017-12-10T12:25:47.541968Z 0 [Note] End of list of non-natively partitioned tables

and everything works like expected:

alpine:~# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
a6d0d908f034 mysql "docker-entrypoint..." 3 minutes ago Up 3 minutes 0.0.0.0:3306->3306/tcp mysqldb
alpine:~# docker exec -it a6d0d908f034 mysql -u root -psupersecret
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye

If i create the following:

mysql> create database mydb;

Query OK, 1 row affected (0.00 sec)
mysql> use mydb
Database changed
mysql> create table mytable ( id int);
Query OK, 0 rows affected (0.03 sec)

and inside the volume:

alpine:/# docker volume inspect mysql_data
[
    {
        "CreatedAt": "2017-12-10T12:30:53Z",
        "Driver": "local",
        "Labels": {},
        "Mountpoint": "/var/lib/docker/volumes/mysql_data/_data",
        "Name": "mysql_data",
        "Options": {},
        "Scope": "local"
    }
]
alpine:/# ls -l /var/lib/docker/volumes/mysql_data/_data/mydb/
total 112
-rw-r-----    1 999      ping            65 Dec 10 12:30 db.opt
-rw-r-----    1 999      ping          8556 Dec 10 12:31 mytable.frm
-rw-r-----    1 999      ping         98304 Dec 10 12:31 mytable.ibd

But after stopping the mysql database, i got the following on the next start:

alpine:~# docker run --name mysqldb -v mysql_data:/var/lib/mysql -e MYSQL_USER=mysql -e MYSQL_PASSWORD=mysql -e MYSQL_DATABASE=sample -e MYSQL_ROOT_PASSWORD=supersecret -it -p 3306:3306 mysq
l
docker: Error response from daemon: Conflict. The container name "/mysqldb" is already in use by container "a6d0d908f03466181682666affb390650179c322f2848d32f2c72fab828f980c". You have to remove (or rename) that container to be able to reuse that name.
See 'docker run --help'.

OK. I have to change the "--name" (--> mysqldb2):

alpine:/# docker exec -it mysqldb2 mysql -u root -psupersecret
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| sample             |
| sys                |
+--------------------+
6 rows in set (0.01 sec)

Yippie! The data is still there!

Shortcomings of this approach:

Each time you start the mysql database on a new host, you have to run "docker volume create..."
If you create a container with this volume (s. here oder there) you can skip this "docker volume create".
Both approaches will not work on docker swarm. The data is local to a node and not shared. One idea: Use a NFS mountpoint to redistribute the volume to all nodes.

Run like Hell

Jan 4, 2018

Docker-Swarm: How to run a mysql database...

No comments:

Post a Comment