influxdb: copying data with SELECT INTO - pay attention to the TAGS (or they are transformed to fields)

 If you are using influxdb, one usecase could be, copy the data from a measurement ("table") to another.

This can be done with this statement:

select * into testtable2 from testtable1

By the way: the CLI is opened with

/usr/bin/influx -unsafeSsl -ssl -database telegraf

(if your database is named telegraf)

In my case (zigbee / mqtt / telegraf) the layout of mqtt_consumer measurement was like this:

> show tag keys from mqtt_consumer
name: mqtt_consumer
tagKey
------
host
topic
> show field keys from mqtt_consumer
name: mqtt_consumer
fieldKey    fieldType
--------    ---------
battery     float
contact     boolean
current     float
...

But after copying this to a testtable, the tags where gone and everything was a field. 

This is not a big problem - you can work with that data without a problem. BUT if you want to copy it back or merge it to the original table, you will get a table with the additional columns host_1 and topic_1.

This is because for influx you already had a column host. So it added a column field host_1. 

If a query in this new table (with host + host_1) spans over a time where both of this columns are in, you only select the data, with the entry host. If the time spans only entries with host_1, it is shown as host and you get your data. Really a unpredictable way to get data.

What is the solution? Easy:

select * into table1 from mqtt_consumer group by host,topic

The "group by" does not group anything. It just tells influx: host & topic are tags and not fields. Please do not transform them...

zigbee: moving data from mqtt to influxdb - transforming strings to integers

After some first steps with zigbee devices and storing the data in an influxdb, i noticed that string values are suboptimal for building graphs. 

Moving the data from mqtt to influxdb was done with telegraf:

https://www.influxdata.com/time-series-platform/telegraf/

And i was wondering, how i can change string to integers, but this i very easy:

  [[processors.enum]]
    order = 2
    [[processors.enum.mapping]]
      field = "state"
      [processors.enum.mapping.value_mappings]
        "ON" = 1
        "OFF" = 0
    [[processors.enum.mapping]]
      field = "contact"
      [processors.enum.mapping.value_mappings]
        "true" = 2
        "false" = 1
    [[processors.enum.mapping]]
      field = "tamper"
      [processors.enum.mapping.value_mappings]
        "true" = 1
        "false" = 0
    [[processors.enum.mapping]]
      field = "water_leak"
      [processors.enum.mapping.value_mappings]
        "true" = 1
        "false" = 0

Next problem: if the column "water_leak" was already added inside your influxdb, you can not add numbers - so you have to drop the table and loose your data...

(This is not the full truth: you can export the data via a select to a file and insert the data afterwards - with the appropriate numbers...)
 

Securing InfluxDB

In my monitoring setup i am heavily using InfluxDB. Starting with one linux server with grafana which loads the data from its local influxdb, i wanted to setup a second linux server.

My options:

1. new telegraf, new influxdb, new grafana
   but then i have two url (because of two grafanas and i can not copy graphs from one dashboard to the other)
   
2. new telegraf, new influxdb, but grafana from first server
   grafana has to get the data over the network
3. new telegraf, influxdb & grafana from first server
   what is happening if telegraf can not reach influxdb, because of network problem? what if the first server is down?
   
4. completely remote monitoring
   what is happening if telegraf can not reach the other server? what if the first server is down? 
   

As you can see, option 2 is the favorite here.

But therefore InfluxDB has to be secured: SSL + user/password.

So let's start with creating some certificates:

openssl req -new -x509 -nodes -out server-cert.pem -days 3650 -keyout server-key.pem

So that you get:

zigbee:/etc/influxdb# ls -lrt *pem
-rw-r--r-- 1 influxdb root  1704 Nov  7 09:48 key.pem
-rw-r--r-- 1 influxdb root  1411 Nov  7 09:48 cert.pem

Then add this in /etc/influxdb/influxdb.conf

 https-enabled = true
 https-certificate = "/etc/influxdb/cert.pem"
 https-private-key = "/etc/influxdb/key.pem"

But still a user is missing, so we have to create users (via bash):

influx -ssl -unsafeSsl

create user admin with password 'XXXXXXX' with all privileges

After that you can test this with

root@zigbee:# influx -ssl -unsafeSsl  
Connected to https://localhost:8086 version 1.6.4
InfluxDB shell version: 1.6.4
> show databases
ERR: unable to parse authentication credentials
Warning: It is possible this error is due to not setting a database.
Please set a database with the command "use <database>".
> auth
username: admin
password:
> show databases
name: databases
name
----
_internal

 

 

ZigBee@Linux: Getting Data from ZigBee Devices via MQTT to InfluxDB and Grafana

Getting sensors with zigbee integrated with my linux raspberry pi, i did some monitoring tasks on my raspberry pi.

1. Monitoring my raspberry pi:
   There is a very nice tutorial:
   https://medium.com/@andreea.sonda31/monitor-raspberry-pi-resources-and-parameters-with-grafana-board-part-1-ab0567303e8
   Or even better: Just use this from grafana:
   https://grafana.com/grafana/dashboards/10578
1. add deb https://packages.grafana.com/oss/deb stable main to a file in /etc/apt/sources.list.d/
   
2. apt install grafana telegraf influxdb
   
3. configure telegraf for your influxdb
4. import the json from the grafana.com-link above
   
   
   
   
   
   
2. Monitoring my Fritz.Box with Grafana:
   https://grafana.com/grafana/dashboards/713 
   and follow the given tutorial https://fetzerch.github.io/2014/08/23/fritzcollectd/

After these steps i have the following infrastructures running:

1. zigbee2mqtt --> MQTT -->FHEM
   
   
   
   
   
2. Fritz.box --> collectd --> InfluxDB --> Grafana
   
   
3. raspberry --> telegraf --> InfluxDB --> Grafana
   
   
   
   
   

For  2 and 3 it is very easy to create graphics and the presentation looks little bit prettier than 1 (imho). 

AND there is only one frontend to configure. So what about the following chain for my zigbee sensors:

1. zigbee2mqtt --> MQTT -->telegraf --> InfluxDB --> Grafana 

Looks like some more steps, but the telegraf --> InfluxDB --> Grafana chain is already there for monitoring my raspberry pi.

So i only had to add the following on /etc/telegraf/telegraf.conf:

[[inputs.mqtt_consumer]]
   servers = ["tcp://127.0.0.1:1883"]
   topics = [
     "zigbee2mqtt/0x00158d000542239e",
     "zigbee2mqtt/0x00158d00044a6378",
     "zigbee2mqtt/0x00158d0003f0faad",
     "zigbee2mqtt/0x00158d00044a72a2",
   data_format = "json"

And after that i was able to use the data in Grafana:

 

Oracle Database 19c (19.3) released!

And after nearly 3 months of waiting - Oracle released 19c:

First special feature: The rpm is still there and the release is only done for Linux and Oracle Solaris.

To get to the Linux download page just click here.

But the Grid Infrastructure is still not delivered as RPM package. :-(

Oracle Database 19c download

In january 2019 Oracle released the documentation for Oracle Database 19c.

More than 7 weeks later there is still nothing at https://www.oracle.com/downloads/:

The gap between release date of the documentation and the on premises software was for 18c not so long...

Will 19c on premises software be released before may? Or later in summer?

Oracle database 19c: documentation released

In january Oracle released the documentation for 19c:

If you are interested in the new features, take a look here:
https://docs.oracle.com/en/database/oracle/oracle-database/19/whats-new.html

Very nice is this link: Interactive Architecture Diagram where you can get a very good introduction to oracle database with many pictures like this one:

This new feature i find very interesting:

Root Scripts Automation Support for Oracle Database Installation

Starting with Oracle Database 19c, the database installer, or setup wizard, provides options to set up permissions to run the root configuration scripts automatically, as required, during a database installation. You continue to have the option to run the root configuration scripts manually.
Setting up permissions for root configuration scripts to run without user intervention can simplify database installation and help avoid inadvertent permission errors.

But 19c is not released for on premise, so i have to wait for testing this feature:

 Release date for Linux: Q2 2019?

MySQL 8: New Features

After installing mysql 8 on my ubuntu i did a lookaround for the new features. Oracle itself stated:

 

This is really cool, but how to test this?

A first look inside the standard data directory /var/lib/mysql shows:

/var/lib/mysql# ls -l
insgesamt 168012
-rw-r----- 1 mysql mysql       56 Aug 17 20:53 auto.cnf
-rw-r----- 1 mysql mysql      498 Aug 17 20:53 binlog.000001
-rw-r----- 1 mysql mysql      554 Aug 17 21:07 binlog.000002
-rw-r----- 1 mysql mysql       32 Aug 17 20:54 binlog.index
-rw------- 1 mysql mysql     1676 Aug 17 20:53 ca-key.pem
-rw-r--r-- 1 mysql mysql     1112 Aug 17 20:53 ca.pem
-rw-r--r-- 1 mysql mysql     1112 Aug 17 20:53 client-cert.pem
-rw------- 1 mysql mysql     1680 Aug 17 20:53 client-key.pem
-rw-r----- 1 mysql mysql     4533 Aug 17 20:53 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Aug 17 21:07 ibdata1
-rw-r----- 1 mysql mysql 50331648 Aug 17 21:07 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Aug 17 20:53 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Aug 17 20:56 ibtmp1
drwxr-x--- 2 mysql mysql     4096 Aug 17 21:07 mydatabase
drwxr-x--- 2 mysql mysql     4096 Aug 17 20:53 mysql
-rw-r----- 1 mysql mysql 25165824 Aug 17 21:07 mysql.ibd
drwxr-x--- 2 mysql mysql     4096 Aug 17 20:53 performance_schema
-rw------- 1 mysql mysql     1680 Aug 17 20:53 private_key.pem
-rw-r--r-- 1 mysql mysql      452 Aug 17 20:53 public_key.pem
-rw-r--r-- 1 mysql mysql     1112 Aug 17 20:53 server-cert.pem
-rw------- 1 mysql mysql     1680 Aug 17 20:53 server-key.pem
drwxr-x--- 2 mysql mysql     4096 Aug 17 20:53 sys
-rw-r----- 1 mysql mysql 10485760 Aug 17 21:07 undo_001
-rw-r----- 1 mysql mysql 10485760 Aug 17 21:07 undo_002

The sys and the perfomance_schema directories does not contain any frm or ISAM-style files. 

The mysql server team shows these pictures on their site (https://mysqlserverteam.com/mysql-8-0-data-dictionary-status-in-the-8-0-0-dmr/)

And if you create your own database each table is stored in its own ibd file and not all in one tablespace file, because innodb_file_per_table is set to 1 as default. With this shrinking tables can be done without a problem...

mysql 8: installation on ubuntu

Today i tried to install mysql 8 on my ubuntu:
First i visited the site https://dev.mysql.com/downloads/repo/apt/ which says:

So i downloaded this file (https://dev.mysql.com/get/mysql-apt-config_0.8.10-1_all.deb) and here we go:

# dpkg -i mysql-apt-config_0.8.10-1_all.deb 

Vormals nicht ausgewähltes Paket mysql-apt-config wird gewählt.

(Lese Datenbank ... 414911 Dateien und Verzeichnisse sind derzeit installiert.)

Vorbereitung zum Entpacken von mysql-apt-config_0.8.10-1_all.deb ...

Entpacken von mysql-apt-config (0.8.10-1) ...

mysql-apt-config (0.8.10-1) wird eingerichtet ...

Warning: apt-key should not be used in scripts (called from postinst maintainerscript of the package mysql-apt-config)

OK

with:

 

root@zerberus:~/Downloads# apt-get update

OK:1 http://de.archive.ubuntu.com/ubuntu bionic InRelease

Holen:2 http://de.archive.ubuntu.com/ubuntu bionic-updates InRelease [88,7 kB]

Holen:3 http://security.ubuntu.com/ubuntu bionic-security InRelease [83,2 kB]

Holen:4 http://repo.mysql.com/apt/ubuntu bionic InRelease [16,9 kB]

Holen:5 http://de.archive.ubuntu.com/ubuntu bionic-backports InRelease [74,6 kB]                

Holen:6 http://de.archive.ubuntu.com/ubuntu bionic-updates/main amd64 Packages [290 kB]

Holen:7 http://repo.mysql.com/apt/ubuntu bionic/mysql-8.0 Sources [898 B]

Holen:8 http://security.ubuntu.com/ubuntu bionic-security/main amd64 Packages [147 kB]                     

Holen:9 http://de.archive.ubuntu.com/ubuntu bionic-updates/main i386 Packages [262 kB]                                             

Holen:10 http://de.archive.ubuntu.com/ubuntu bionic-updates/main Translation-en [111 kB]                                                   

Holen:11 http://repo.mysql.com/apt/ubuntu bionic/mysql-apt-config amd64 Packages [568 B]                               

Holen:12 http://de.archive.ubuntu.com/ubuntu bionic-updates/main amd64 DEP-11 Metadata [138 kB]                                

Holen:13 http://de.archive.ubuntu.com/ubuntu bionic-updates/main DEP-11 48x48 Icons [31,4 kB]                            

Holen:14 http://de.archive.ubuntu.com/ubuntu bionic-updates/main DEP-11 64x64 Icons [53,7 kB]                         

Holen:15 http://de.archive.ubuntu.com/ubuntu bionic-updates/universe amd64 Packages [161 kB]                          

Holen:16 http://security.ubuntu.com/ubuntu bionic-security/main i386 Packages [119 kB]                            

Holen:17 http://de.archive.ubuntu.com/ubuntu bionic-updates/universe i386 Packages [161 kB]                       

Holen:18 http://security.ubuntu.com/ubuntu bionic-security/main Translation-en [57,4 kB]                                     

Holen:19 http://repo.mysql.com/apt/ubuntu bionic/mysql-apt-config i386 Packages [568 B]                                            

Holen:20 http://de.archive.ubuntu.com/ubuntu bionic-updates/universe Translation-en [73,6 kB]                                      

Holen:21 http://security.ubuntu.com/ubuntu bionic-security/main amd64 DEP-11 Metadata [204 B]                                             

Holen:22 http://security.ubuntu.com/ubuntu bionic-security/universe i386 Packages [50,8 kB]   

Holen:23 http://de.archive.ubuntu.com/ubuntu bionic-updates/universe amd64 DEP-11 Metadata [152 kB]                       

Holen:24 http://security.ubuntu.com/ubuntu bionic-security/universe amd64 Packages [50,9 kB]                                  

Holen:25 http://security.ubuntu.com/ubuntu bionic-security/universe Translation-en [29,4 kB]                                  

Holen:26 http://security.ubuntu.com/ubuntu bionic-security/universe amd64 DEP-11 Metadata [5.792 B]                                 

Holen:27 http://de.archive.ubuntu.com/ubuntu bionic-updates/universe DEP-11 48x48 Icons [153 kB]                                     

Holen:28 http://security.ubuntu.com/ubuntu bionic-security/universe DEP-11 48x48 Icons [6.962 B]                                 

Holen:29 http://security.ubuntu.com/ubuntu bionic-security/multiverse i386 Packages [1.608 B]                                

Holen:30 http://security.ubuntu.com/ubuntu bionic-security/multiverse amd64 Packages [1.444 B]                            

Holen:31 http://de.archive.ubuntu.com/ubuntu bionic-updates/universe DEP-11 64x64 Icons [262 kB]                    

Holen:32 http://repo.mysql.com/apt/ubuntu bionic/mysql-8.0 i386 Packages [7.004 B]            

Holen:33 http://de.archive.ubuntu.com/ubuntu bionic-updates/multiverse amd64 Packages [3.772 B]       

Holen:34 http://de.archive.ubuntu.com/ubuntu bionic-updates/multiverse i386 Packages [3.928 B]

Holen:35 http://de.archive.ubuntu.com/ubuntu bionic-updates/multiverse amd64 DEP-11 Metadata [2.468 B]

Holen:36 http://de.archive.ubuntu.com/ubuntu bionic-backports/universe amd64 DEP-11 Metadata [5.100 B]

Holen:37 http://repo.mysql.com/apt/ubuntu bionic/mysql-8.0 amd64 Packages [7.002 B]           

Holen:38 http://repo.mysql.com/apt/ubuntu bionic/mysql-tools amd64 Packages [2.519 B]

Holen:39 http://repo.mysql.com/apt/ubuntu bionic/mysql-tools i386 Packages [1.882 B]

Es wurden 2.617 kB in 1 s geholt (2.002 kB/s).

Paketlisten werden gelesen... Fertig

root@zerberus:~/Downloads# apt-get install mysql-server

Paketlisten werden gelesen... Fertig

Abhängigkeitsbaum wird aufgebaut.       

Statusinformationen werden eingelesen.... Fertig

Die folgenden Pakete wurden automatisch installiert und werden nicht mehr benötigt:

  btrfs-tools esound-common gnome-dictionary gnome-icon-theme-symbolic libarmadillo7 libaudiofile1 libboost-date-time1.62.0 libboost-filesystem1.62.0

  libboost-iostreams1.62.0 libboost-random1.62.0 libboost-regex1.62.0 libboost-serialization1.62.0 libboost-system1.62.0 libboost-thread1.62.0

  libcapnp-0.5.3 libcaribou-gtk-module libcaribou-gtk3-module libdevhelp-3-4 libesd0 libfabric1 libfolks-telepathy25 libgeos-3.5.1 libgit2-24 libgl2ps1

  libgl2ps1.4 libgnome-games-support-1-2 libhdf5-openmpi-100 libhttp-parser2.1 libhwloc-plugins libhwloc5 libical2 libiso9660-8 libjsoncpp1

  libjsonrpc-glib-1.0-0 liblept5 liblivemedia58 libllvm5.0 liblouis12 liblouisutdml7 liblttng-ust-ctl2 libmirclient-dev libmircommon-dev libmircookie-dev

  libmircookie2 libmircore-dev libnetcdf-c++4 libnetcdf11 libntfs-3g872 libopencv-core3.1 libopencv-flann3.1 libopencv-imgproc3.1 libopencv-ml3.1

  libopencv-photo3.1 libopencv-shape3.1 libopencv-video3.1 libopencv-viz3.1 libopenmpi2 liborcus-0.12-0 libpoppler68 libprocess-cpp3 libprotobuf-dev

  libpsm-infinipath1 libqpdf18 librpm3 librpmbuild3 librpmio3 librpmsign3 libruby2.3 libsodium18 libsrtp0 libsuitesparseconfig4 libtbb2 libtesseract-data

  libtesseract3 libunity-api0 libva-drm1 libva-wayland1 libva-x11-1 libva1 libvlccore8 libvpx4 libvtk6.3 libx264-148 libx265-130 libxerces-c3.1

  libxkbcommon-dev libzmqpp4 openmpi-bin openmpi-common python-gi python3-libarchive-c python3-libnacl ruby2.3 snapd-login-service

Verwenden Sie »apt autoremove«, um sie zu entfernen.

Die folgenden zusätzlichen Pakete werden installiert:

  libmecab2 mecab-ipadic mecab-ipadic-utf8 mecab-utils mysql-client mysql-common mysql-community-client mysql-community-client-core mysql-community-server

  mysql-community-server-core

Die folgenden NEUEN Pakete werden installiert:

  libmecab2 mecab-ipadic mecab-ipadic-utf8 mecab-utils mysql-client mysql-community-client mysql-community-client-core mysql-community-server

  mysql-community-server-core mysql-server

Die folgenden Pakete werden aktualisiert (Upgrade):

  mysql-common

1 aktualisiert, 10 neu installiert, 0 zu entfernen und 36 nicht aktualisiert.

Es müssen 54,2 MB an Archiven heruntergeladen werden.

Nach dieser Operation werden 414 MB Plattenplatz zusätzlich benutzt.

Möchten Sie fortfahren? [J/n] 

Holen:1 http://de.archive.ubuntu.com/ubuntu bionic/universe amd64 libmecab2 amd64 0.996-5 [257 kB]

Holen:2 http://repo.mysql.com/apt/ubuntu bionic/mysql-8.0 amd64 mysql-common amd64 8.0.12-1ubuntu18.04 [78,6 kB]

Holen:3 http://de.archive.ubuntu.com/ubuntu bionic/universe amd64 mecab-utils amd64 0.996-5 [4.856 B]

Holen:4 http://de.archive.ubuntu.com/ubuntu bionic/universe amd64 mecab-ipadic all 2.7.0-20070801+main-1 [12,1 MB]

Holen:5 http://repo.mysql.com/apt/ubuntu bionic/mysql-8.0 amd64 mysql-community-client-core amd64 8.0.12-1ubuntu18.04 [1.435 kB]

Holen:6 http://de.archive.ubuntu.com/ubuntu bionic/universe amd64 mecab-ipadic-utf8 all 2.7.0-20070801+main-1 [3.522 B]

Holen:7 http://repo.mysql.com/apt/ubuntu bionic/mysql-8.0 amd64 mysql-community-client amd64 8.0.12-1ubuntu18.04 [2.292 kB]

Holen:8 http://repo.mysql.com/apt/ubuntu bionic/mysql-8.0 amd64 mysql-client amd64 8.0.12-1ubuntu18.04 [75,7 kB]

Holen:9 http://repo.mysql.com/apt/ubuntu bionic/mysql-8.0 amd64 mysql-community-server-core amd64 8.0.12-1ubuntu18.04 [16,9 MB]

Holen:10 http://repo.mysql.com/apt/ubuntu bionic/mysql-8.0 amd64 mysql-community-server amd64 8.0.12-1ubuntu18.04 [21,0 MB]

Holen:11 http://repo.mysql.com/apt/ubuntu bionic/mysql-8.0 amd64 mysql-server amd64 8.0.12-1ubuntu18.04 [75,7 kB]                                           

Es wurden 54,2 MB in 10 s geholt (5.396 kB/s).                                                                                                              

Vorkonfiguration der Pakete ...

(Lese Datenbank ... 414916 Dateien und Verzeichnisse sind derzeit installiert.)

Vorbereitung zum Entpacken von .../00-mysql-common_8.0.12-1ubuntu18.04_amd64.deb ...

Entpacken von mysql-common (8.0.12-1ubuntu18.04) über (5.8+1.0.4) ...

Vormals nicht ausgewähltes Paket mysql-community-client-core wird gewählt.

Vorbereitung zum Entpacken von .../01-mysql-community-client-core_8.0.12-1ubuntu18.04_amd64.deb ...

Entpacken von mysql-community-client-core (8.0.12-1ubuntu18.04) ...

Vormals nicht ausgewähltes Paket mysql-community-client wird gewählt.

Vorbereitung zum Entpacken von .../02-mysql-community-client_8.0.12-1ubuntu18.04_amd64.deb ...

Entpacken von mysql-community-client (8.0.12-1ubuntu18.04) ...

Vormals nicht ausgewähltes Paket mysql-client wird gewählt.

Vorbereitung zum Entpacken von .../03-mysql-client_8.0.12-1ubuntu18.04_amd64.deb ...

Entpacken von mysql-client (8.0.12-1ubuntu18.04) ...

Vormals nicht ausgewähltes Paket libmecab2:amd64 wird gewählt.

Vorbereitung zum Entpacken von .../04-libmecab2_0.996-5_amd64.deb ...

Entpacken von libmecab2:amd64 (0.996-5) ...

Vormals nicht ausgewähltes Paket mysql-community-server-core wird gewählt.

Vorbereitung zum Entpacken von .../05-mysql-community-server-core_8.0.12-1ubuntu18.04_amd64.deb ...

Entpacken von mysql-community-server-core (8.0.12-1ubuntu18.04) ...

Vormals nicht ausgewähltes Paket mysql-community-server wird gewählt.

Vorbereitung zum Entpacken von .../06-mysql-community-server_8.0.12-1ubuntu18.04_amd64.deb ...

Entpacken von mysql-community-server (8.0.12-1ubuntu18.04) ...

Vormals nicht ausgewähltes Paket mecab-utils wird gewählt.

Vorbereitung zum Entpacken von .../07-mecab-utils_0.996-5_amd64.deb ...

Entpacken von mecab-utils (0.996-5) ...

Vormals nicht ausgewähltes Paket mecab-ipadic wird gewählt.

Vorbereitung zum Entpacken von .../08-mecab-ipadic_2.7.0-20070801+main-1_all.deb ...

Entpacken von mecab-ipadic (2.7.0-20070801+main-1) ...

Vormals nicht ausgewähltes Paket mecab-ipadic-utf8 wird gewählt.

Vorbereitung zum Entpacken von .../09-mecab-ipadic-utf8_2.7.0-20070801+main-1_all.deb ...

Entpacken von mecab-ipadic-utf8 (2.7.0-20070801+main-1) ...

Vormals nicht ausgewähltes Paket mysql-server wird gewählt.

Vorbereitung zum Entpacken von .../10-mysql-server_8.0.12-1ubuntu18.04_amd64.deb ...

Entpacken von mysql-server (8.0.12-1ubuntu18.04) ...

Trigger für ureadahead (0.100.0-20) werden verarbeitet ...

mysql-common (8.0.12-1ubuntu18.04) wird eingerichtet ...

Neue Version der Konfigurationsdatei /etc/mysql/conf.d/mysql.cnf wird installiert ...

Neue Version der Konfigurationsdatei /etc/mysql/my.cnf.fallback wird installiert ...

libmecab2:amd64 (0.996-5) wird eingerichtet ...

mysql-community-client-core (8.0.12-1ubuntu18.04) wird eingerichtet ...

mysql-community-server-core (8.0.12-1ubuntu18.04) wird eingerichtet ...

Trigger für libc-bin (2.27-3ubuntu1) werden verarbeitet ...

Trigger für systemd (237-3ubuntu10.3) werden verarbeitet ...

Trigger für man-db (2.8.3-2) werden verarbeitet ...

mecab-utils (0.996-5) wird eingerichtet ...

mysql-community-client (8.0.12-1ubuntu18.04) wird eingerichtet ...

mecab-ipadic (2.7.0-20070801+main-1) wird eingerichtet ...

Compiling IPA dictionary for Mecab.  This takes long time...

reading /usr/share/mecab/dic/ipadic/unk.def ... 40

emitting double-array: 100% |###########################################| 

/usr/share/mecab/dic/ipadic/model.def is not found. skipped.

reading /usr/share/mecab/dic/ipadic/Noun.adverbal.csv ... 795

reading /usr/share/mecab/dic/ipadic/Adverb.csv ... 3032

reading /usr/share/mecab/dic/ipadic/Postp.csv ... 146

reading /usr/share/mecab/dic/ipadic/Suffix.csv ... 1393

reading /usr/share/mecab/dic/ipadic/Verb.csv ... 130750

reading /usr/share/mecab/dic/ipadic/Adnominal.csv ... 135

reading /usr/share/mecab/dic/ipadic/Prefix.csv ... 221

reading /usr/share/mecab/dic/ipadic/Noun.demonst.csv ... 120

reading /usr/share/mecab/dic/ipadic/Noun.csv ... 60477

reading /usr/share/mecab/dic/ipadic/Conjunction.csv ... 171

reading /usr/share/mecab/dic/ipadic/Noun.verbal.csv ... 12146

reading /usr/share/mecab/dic/ipadic/Noun.proper.csv ... 27327

reading /usr/share/mecab/dic/ipadic/Others.csv ... 2

reading /usr/share/mecab/dic/ipadic/Filler.csv ... 19

reading /usr/share/mecab/dic/ipadic/Noun.number.csv ... 42

reading /usr/share/mecab/dic/ipadic/Noun.org.csv ... 16668

reading /usr/share/mecab/dic/ipadic/Symbol.csv ... 208

reading /usr/share/mecab/dic/ipadic/Noun.nai.csv ... 42

reading /usr/share/mecab/dic/ipadic/Noun.adjv.csv ... 3328

reading /usr/share/mecab/dic/ipadic/Noun.place.csv ... 72999

reading /usr/share/mecab/dic/ipadic/Interjection.csv ... 252

reading /usr/share/mecab/dic/ipadic/Noun.name.csv ... 34202

reading /usr/share/mecab/dic/ipadic/Postp-col.csv ... 91

reading /usr/share/mecab/dic/ipadic/Noun.others.csv ... 151

reading /usr/share/mecab/dic/ipadic/Adj.csv ... 27210

reading /usr/share/mecab/dic/ipadic/Auxil.csv ... 199

emitting double-array: 100% |###########################################| 

reading /usr/share/mecab/dic/ipadic/matrix.def ... 1316x1316

emitting matrix      : 100% |###########################################| 



done!

update-alternatives: /var/lib/mecab/dic/ipadic wird verwendet, um /var/lib/mecab/dic/debian (mecab-dictionary) im automatischen Modus bereitzustellen

mysql-client (8.0.12-1ubuntu18.04) wird eingerichtet ...

mecab-ipadic-utf8 (2.7.0-20070801+main-1) wird eingerichtet ...

Compiling IPA dictionary for Mecab.  This takes long time...

reading /usr/share/mecab/dic/ipadic/unk.def ... 40

emitting double-array: 100% |###########################################| 

/usr/share/mecab/dic/ipadic/model.def is not found. skipped.

reading /usr/share/mecab/dic/ipadic/Noun.adverbal.csv ... 795

reading /usr/share/mecab/dic/ipadic/Adverb.csv ... 3032

reading /usr/share/mecab/dic/ipadic/Postp.csv ... 146

reading /usr/share/mecab/dic/ipadic/Suffix.csv ... 1393

reading /usr/share/mecab/dic/ipadic/Verb.csv ... 130750

reading /usr/share/mecab/dic/ipadic/Adnominal.csv ... 135

reading /usr/share/mecab/dic/ipadic/Prefix.csv ... 221

reading /usr/share/mecab/dic/ipadic/Noun.demonst.csv ... 120

reading /usr/share/mecab/dic/ipadic/Noun.csv ... 60477

reading /usr/share/mecab/dic/ipadic/Conjunction.csv ... 171

reading /usr/share/mecab/dic/ipadic/Noun.verbal.csv ... 12146

reading /usr/share/mecab/dic/ipadic/Noun.proper.csv ... 27327

reading /usr/share/mecab/dic/ipadic/Others.csv ... 2

reading /usr/share/mecab/dic/ipadic/Filler.csv ... 19

reading /usr/share/mecab/dic/ipadic/Noun.number.csv ... 42

reading /usr/share/mecab/dic/ipadic/Noun.org.csv ... 16668

reading /usr/share/mecab/dic/ipadic/Symbol.csv ... 208

reading /usr/share/mecab/dic/ipadic/Noun.nai.csv ... 42

reading /usr/share/mecab/dic/ipadic/Noun.adjv.csv ... 3328

reading /usr/share/mecab/dic/ipadic/Noun.place.csv ... 72999

reading /usr/share/mecab/dic/ipadic/Interjection.csv ... 252

reading /usr/share/mecab/dic/ipadic/Noun.name.csv ... 34202

reading /usr/share/mecab/dic/ipadic/Postp-col.csv ... 91

reading /usr/share/mecab/dic/ipadic/Noun.others.csv ... 151

reading /usr/share/mecab/dic/ipadic/Adj.csv ... 27210

reading /usr/share/mecab/dic/ipadic/Auxil.csv ... 199

emitting double-array: 100% |###########################################| 

reading /usr/share/mecab/dic/ipadic/matrix.def ... 1316x1316

emitting matrix      : 100% |###########################################| 



done!

update-alternatives: /var/lib/mecab/dic/ipadic-utf8 wird verwendet, um /var/lib/mecab/dic/debian (mecab-dictionary) im automatischen Modus bereitzustellen

mysql-community-server (8.0.12-1ubuntu18.04) wird eingerichtet ...

update-alternatives: /etc/mysql/mysql.cnf wird verwendet, um /etc/mysql/my.cnf (my.cnf) im automatischen Modus bereitzustellen

Created symlink /etc/systemd/system/multi-user.target.wants/mysql.service → /lib/systemd/system/mysql.service.

mysql-server (8.0.12-1ubuntu18.04) wird eingerichtet ...

Trigger für systemd (237-3ubuntu10.3) werden verarbeitet ...

Trigger für ureadahead (0.100.0-20) werden verarbeitet ...

And everything is ready:

mysql -u root -pmysupersecretpassword

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 10

Server version: 8.0.12 MySQL Community Server - GPL



Copyright (c) 2000, 2018, 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> 

Where to find the oracle-database-ee-18c-1.0-1.x86_64.rpm package?

After writing about the release of Oracle database 18c i wanted to download

oracle-database-ee-18c-1.0-1.x86_64.rpm

to try the rpm-based installation.
But this rpm is not included in

LINUX.X64_180000_db_home.zip

The oracle documentation shows:

 But on OTN and Delivery Cloud only the zipfile without the rpm can be downloaded...

So let's see, when the download pages are updated...

(Three months later: https://dietrichschroff.blogspot.com/2018/11/oracle-database-18c-installation-via.html)

Oracle 18c database is released for Linux (on premise)

After my posting nearly a week ago about the published 18c documentation on monday the binaries for Oracle 18c database were released:

and

 Here the link to the download page: oracle.com

In one of the next postings i will try a rpm installation...

Oracle Database 18c: Documentation released!

Today i surfed to docs.oracle.com and there i found the documentation for oracle database 18c:

In march 2018 i wrote about the new installation procedure of oracle database 18c via rpm, which was announced inside the "new features".

The installation guide for Linux contains now the commands for installing the database via rpm. Here a screencopy of chapter 11:

 rpm -ivh oracle-ee-db-18.1.0.0.0-1.x86_64.rpm

This command creates the Oracle home at the location /opt/oracle/product/18.0.0.0.0-1/dbhome_1.

Cool!

The next step is to run the dbca:

7 The screen displays information that prompts you to create a database by using Oracle Database Configuration Assistant (Oracle DBCA) in silent mode.

[...]

8 Log in as the oracle user and run Oracle DBCA in silent mode to create the database.

[...]
Oracle DBCA creates the Oracle Database listener, the Oracle Database instance, starts the instance, and completes the Oracle Database creation process. Review the status information that is displayed on your screen.

Here the link to docs.oracle.com chapter 11 "Running RPM Packages to Install Oracle Database".

Oracle Database Software Downloads: 18c released

Ok - not the database binaries but the Oracle Database 18c Release 1 Client is released:

Only for windows and linux - not really suprising, but that shows, which platforms are well supported ;-)

The documentation can be found here.

The documentation is for solaris, too - but without binaries this sounds a little bit strange:

Oracle SOA Suite 12c: complete startup procedure including database and application server

After the first reboot i had to work hard to get the complete system up again. So here a list of all commands:

1. database
   export ORACLE_HOME=/home/oracle/app/oracle/product/12.2.0/dbhome_1
   export PATH=$PATH:$ORACLE_HOME/bin
   export ORACLE_SID=orcl
   
   $ sqlplus / as sysdba
   
   SQL*Plus: Release 12.2.0.1.0 Production on Fri Feb 16 20:53:02 2018
   
   Copyright (c) 1982, 2016, Oracle.  All rights reserved.
   
   Connected to an idle instance.
   
   SQL> startup
   ORACLE instance started.
   
   Total System Global Area 1560281088 bytes
   Fixed Size            8793160 bytes
   Variable Size         1006633912 bytes
   Database Buffers      536870912 bytes
   Redo Buffers            7983104 bytes
   Datenbank mounted.
   Datenbank geoffnet.
   SQL> alter pluggable database soasuite12c open;
   
   Integrierbare Datenbank geandert.
2. listener
   $ lsnrctl start
   
   LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 16-FEB-2018 21:09:17
   
   Copyright (c) 1991, 2016, Oracle.  All rights reserved.
   
   Starting /home/oracle/app/oracle/product/12.2.0/dbhome_1/bin/tnslsnr: please wait...
   
   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
   System parameter file is /home/oracle/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
   Log messages written to /home/oracle/app/oracle/diag/tnslsnr/mywww/listener/alert/log.xml
   Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
   
   Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
   STATUS of the LISTENER
   ------------------------
   Alias                     LISTENER
   Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
   Start Date                16-FEB-2018 21:09:17
   Uptime                    0 days 0 hr. 0 min. 0 sec
   Trace Level               off
   Security                  ON: Local OS Authentication
   SNMP                      OFF
   Listener Parameter File   /home/oracle/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
   Listener Log File         /home/oracle/app/oracle/diag/tnslsnr/mywww/listener/alert/log.xml
   Listening Endpoints Summary...
     (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
   The listener supports no services
   The command completed successfully
   
   Wait some seconds and then:$ lsnrctl status
   
   LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 16-FEB-2018 21:09:35
   
   Copyright (c) 1991, 2016, Oracle.  All rights reserved.
   
   Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
   STATUS of the LISTENER
   ------------------------
   Alias                     LISTENER
   Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
   Start Date                16-FEB-2018 21:09:17
   Uptime                    0 days 0 hr. 0 min. 18 sec
   Trace Level               off
   Security                  ON: Local OS Authentication
   SNMP                      OFF
   Listener Parameter File   /home/oracle/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
   Listener Log File         /home/oracle/app/oracle/diag/tnslsnr/mywww/listener/alert/log.xml
   Listening Endpoints Summary...
     (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
     (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=mywww)(PORT=5500))(Security=(my_wallet_directory=/home/oracle/app/oracle/product/12.2.0/dbhome_1/admin/orcl/xdb_wallet))(Presentation=HTTP)(Session=RAW))
   Services Summary...
   Service "5a81d0aff5393ed2e055000000000001" has 1 instance(s).
     Instance "orcl", status READY, has 1 handler(s) for this service...
   Service "5af731f7368117fbe055000000000001" has 1 instance(s).
     Instance "orcl", status READY, has 1 handler(s) for this service...
   Service "orcl" has 1 instance(s).
     Instance "orcl", status READY, has 1 handler(s) for this service...
   Service "orclXDB" has 1 instance(s).
     Instance "orcl", status READY, has 1 handler(s) for this service...
   Service "orclpdb" has 1 instance(s).
     Instance "orcl", status READY, has 1 handler(s) for this service...
   Service "soasuite12c" has 1 instance(s).
     Instance "orcl", status READY, has 1 handler(s) for this service...
   The command completed successfully
   
   and a check with sqlplus:
   $  sqlplus bpeladmin@soasuite12c
   
   SQL*Plus: Release 12.2.0.1.0 Production on Fri Feb 16 21:09:40 2018
   
   Copyright (c) 1982, 2016, Oracle.  All rights reserved.
   
   Enter password:
   Letzte erfolgreiche Anmeldezeit: So Jan 21 2018 21:05:53 +01:00
   
   Verbunden mit:
   Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
3. application server
   export PATH=/home/data/opt/jdk1.8.0_131/bin:$PATH
   export JAVA_HOME=/home/data/opt/jkd1.8.0_131
   
   $cd Oracle/Middleware/Oracle_Home/user_projects/domains/_domain/bin/
   $ nohup ./startNodeManager.sh &
   
   start the AdminServer
   $nohup ./startWeblogic.sh &
   
   and wait for:
   
   <16 .02.2018="" 21:20="" mez="" uhr="">
   <16 .02.2018="" 21:17="" mez="" uhr="">
   via tail -f nohup.outstart the soa_server
   
   ./startManagedWebLogic.sh soa_server1 t3://localhost:7001
   
   and enter user and password when prompted
   
      

And then check via http://192.168.178.49:7001/console:

All installation steps can be found here:

• Configurations of the virtualbox vm
• OS installation
• Virtualbox guest extensions
• OS preparation and DB installation
• Preparing the DB (PDB)
• Configuring the database via RCU
• Installation of the infrastructure software
• Installation of the SOA Suite software
• Configure the application servers

 i had to run the

Oracle database 18c: Installation via RPM

After the release of 18c i did a short dive into the "guide to new features" announced on oracle.com:

 And take a look, what can be found in "Database Overall -> Install, config, and patch":

RPM-based Database Installation

RPM-based Database Installation (RDI) enables an RPM-based installation of the Oracle Database software. Using the rpm-ivh command, an RPM-based database installation performs the preinstallation validations, extracts the packaged software, reassigns the ownership of the extracted software to the preconfigured user and groups, maintains the Oracle inventory, and executes all the root operations required to complete the Oracle Database software installation.
RPM-based Database Installation enables you to leverage an RPM framework to easily deploy Oracle Database.

A kind of rpm installation was introduced for the installation of grid infrastructure in 12.2 (oracle documentation): Image based installation
So no worries, you have to start the runInstaller.sh after rpm -i. But let's see, how you have to specify the Oracle_Home in this rpm-process.

EDIT: On 20th july 2018 the documentation is released. See here.
EDIT2: October 2018: Installation done via rpm

Oracle Database 18c released! Image-based Installation?

Today i discovered on otn.oracle.com --> database:

The downloads-page looks like the last weeks - 12c is the default...

... but the documentations tab lists 18c:

 

blogs.oracle.com shows the following:

So the "cloud first" strategy is still in place (by the way: is this something like "america first"?).

The installation procedure looks strange:

Starting with Oracle Database 18c, installation and configuration of Oracle Database software is simplified with image-based installation.
To install Oracle Database, create the new Oracle home, extract the image file into the newly-created Oracle home, and run the setup wizard to register the Oracle Database product.
Using image-based installation, you can install and upgrade Oracle Database for single-instance and cluster configurations.

Oracle shows up this:

 But there is no 18c on OTN for download...

UPDATE: The rpm based installation is ready for download now: https://dietrichschroff.blogspot.com/2018/11/oracle-database-18c-installation-via.html

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.

Related posts:

• Docker on Ubuntu 
• Running through a tutorial / investigating the processes
• Creating a small linux for my docker cluster (Alpine Linux) 
• How to build a docker swarm 
• Creating a minimal webserver container and deploying it to docker swarm
• Setting up a tomcat with docker  
• Running a swarm with multiple managers 
• Mysql inside a docker container - persistency with docker volumes 
• How to change the configuration of a standard docker container(e.g. changing tomcats memory settings)