You are here

Feed aggregator

MySQL Enterprise Schulung für Fortgeschrittene von FromDual

Oli Sennhauser - Tue, 2018-02-06 15:33

Aufgrund der gestiegen Nachfrage hat FromDual eine MySQL Enterprise Schulung für Fortgeschrittene MySQL DBAs und DevOps entwickelt. Nachdem wir diese Schulung im letzten Jahr zusammen mit ausgewählten Kunden ausführlich getestet haben bieten wir sie 2018 für eine breite Kundschaft an.

Die MySQL Enterprise Schulung richtet sich an MySQL DBA's und DevOps, welche bereits mit MySQL vertraut sind und nun vor der Herausforderung stehen, eine ernsthafte MySQL Enterprise Infrastruktur zu betreiben.

Die Liste der Themen, welche während der dreitägigen Schulung behandelt werden finden sie hier.

Es bietet sich zudem die Möglichkeit, 2 Tage MySQL Performance Tuning aus der MySQL für Fortgeschrittene Schulung anzuhängen.

Gerne führen wir diese Schulung bei ihnen vor Ort oder bei einem unserer Schulungspartner in Essen, Berlin oder Köln durch.

Bei Fragen stehen wir Ihnen auch gerne per eMail zur Verfügung.

Taxonomy upgrade extras: mysqlenterpriseschulungfortgeschrittene

Advanced MySQL Enterprise Training by FromDual

Shinguz - Tue, 2018-02-06 15:29

Due to the increasing demand FromDual has developed an Advanced MySQL Enterprise Training for DBAs and DevOps. After testing this training extensively with some selected customers last year we offer this MySQL Enterprise Training in 2018 for a broader audience.

The MySQL Enterprise Training addresses MySQL DBAs and DevOps which are already familiar with MySQL and approach now the challenge to operate a serious MySQL Enterprise infrastructure.

The topics of the 3 days MySQL Enterprise training you can find here.

You further have the opportunity to add 2 extra days of MySQL Performance Tuning from the Advanced MySQL Training.

We would be pleased to hold this training in-house in your company or at the location of one of our training partners in Essen, Berlin and Cologne (Germany).

For any question please contact us by eMail.

Taxonomy upgrade extras: trainingenterprisemysqladvanced

MySQL 8.0.4-rc is out

Shinguz - Wed, 2018-01-24 08:54

Yesterday MySQL 8.0.4-rc came out. The Release Notes are quite long.
But caution: Do a BACKUP before upgrading...

I experienced some nice surprises. First I have to admit that I did not read the Release Notes or anything else. Reading manuals is for Girlies! Possibly something is written in there which is of importance. But I expect that it just works as usual...

I downloaded MySQL 8.0.4-rc and just want to upgrade my MySQL 8.0.3-rc testing system, where we did the 1M tables test.

First I got:

[MY-011096] No data dictionary version number found. [MY-010020] Data Dictionary initialization failed. [MY-010119] Aborting

Hmmm... Maybe something was not clean with the old system. So downgrade again:

[ERROR] [000000] InnoDB: Unsupported redo log format. The redo log was created with MySQL 8.0.4. Please follow the instructions at http://dev.mysql.com/doc/refman/8.0/en/upgrading-downgrading.html [ERROR] [000000] InnoDB: Plugin initialization aborted with error Generic error [ERROR] [003957] Failed to initialize DD Storage Engine [ERROR] [003634] Data Dictionary initialization failed. [ERROR] [003742] Aborting

OK. Understandable. I should have done a backup before. But backup is for Girlies as well! Anyway this test system is not important. So I created a new instance from scratch which finally worked... Possibly just removing the redo log files as indicated would have helped as well.

Short term notice: Percona XtraDB Cluster training in English 7/8 February 2018 in Germany

Shinguz - Fri, 2018-01-19 17:05

FromDual offers short term a Percona XtraDB Cluster and MySQL Galera Cluster training (2 days) in English.

The training will take place in the Linuxhotel in Essen/Germany on February 7/8 2018.

There are already enough registrations so it is certain the training will take place. But there are still free places for some additional participants.

You can book online at the Linuxhotel.

Important: The Linuxhotel is nearly fully booked out. So accommodation is in nearby locations. The Linuxhotel will recommend you some locations.

The training is in English.

You can find the contents of this 2-day Percona XtraDB Cluster training here.

If you have any question please do not hesitate to contact us.

Taxonomy upgrade extras: galeraPercona XtraDB Clustertraininglinuxhotel

Schulung MySQL und MariaDB für Fortgeschrittene in Köln 2018

Oli Sennhauser - Wed, 2018-01-17 16:02

Ende Februar, genauer vom 26. Februar bis 2. März (5 Tage), bietet FromDual eine weitere Schulung für DBAs und DevOps an, unsere häufigst besuchte Schulung: MySQL und MariaDB für Fortgeschrittene.

Diese MySQL/MariaDB Schulung findet in den Räumlichkeiten des FromDual Schulungspartners der GFU Cyrus GmbH in Köln-Deutz statt.

Es haben sich bereits genügend Teilnehmer angemeldet, sodass diese Schulung sicher statt findet. Es hat aber noch Platz für mindestens 3 weitere Teilnehmer.

Die Schulung findet in deutscher Sprache statt.

Den Inhalt dieser fünftägigen MySQL/MariaDB Schulung finden Sie hier.

Bei weiteren Fragen nehmen Sie bitte mit uns Kontakt auf.

Taxonomy upgrade extras: schulungkölnfortgeschrittene

Advanced MySQL and MariaDB training in Cologne 2018

Shinguz - Wed, 2018-01-17 15:55

End of February, from February 26 to March 2 (5 days), FromDual offers an additional training for DBAs and DevOps: our most visited Advanced MySQL and MariaDB training.

This training is hold in the training facilities of the FromDual training partner GFU Cyrus GmbH in Cologne-Deutz (Germany).

There are already enough registrations so it is certain the training will take place. But there are still free places for at least 3 additional participants.

The training is in German.

You can find the training of this 5-day MySQL/MariaDB training here.

If you have any question please do not hesitate to contact us.

Taxonomy upgrade extras: trainingadvancedcologne

Oracle releases MySQL security vulnerability fixes 2018-01

Shinguz - Wed, 2018-01-17 11:27

As in every quarter of the year Oracle has released yesterday its recommendation for the MySQL security updates. This is called, in Oracle terminology, Critical Patch Update (CPU) Advisory.

This CPU is published for all Oracle products. But FromDual is only interested in MySQL related topics. So let us concentrate on those.

This time 25 fixes with a maximum score of 8.1 (out of 10.0) were published.

6 of theses 25 vulnerabilities are exploitable remotely over the network without authentication (no user credentials required)!

The following MySQL products are affected:

  • MySQL Enterprise Monitor (3.3.6.3293 and before, 3.4.4.4226 and before, 4.0.0.5135 and before)
  • MySQL Connector/Net (6.9.9. and before, 6.10.4 and before)
  • MySQL Connector/ODBC (5.3.9. and before)
  • MySQL Server (5.5.58 and before, 5.6.38 and before, 5.7.19 and before)

It is recommended to upgrade your MySQL products to close the security vulnerabilities.

FromDual upgrade decision aid

Because such security updates are published quarterly and some of our customers have dozens to hundreds of MySQL installations this would end up in a never ending story where you are continuously upgrading MySQL database servers and other products.

This led to idea to create an upgrade decision aid to decide if you have to upgrade to this CPU or not.

The following questions can be asked:

  • How exposed is your database?
    Databases can be located in various network segments. It is not recommended to expose databases directly to the internet. Databases are either installed in demilitarized zones (DMZ) with no direct access from the internet or in the companies private network (only company employees should be able to access the database) or even specialized secure networks (only a limited number of specific employees can access this network).
  • How critical are your data?
    Some data are more interesting or critical, some data are less interesting or critical. Interesting data are: User data (user name and password), customer data (profiles, preferences, etc.), financial data (credit cards) and health care data (medical data). Systems containing such data are more critical than others. You can also ask: How sever is it if such data leak?
  • How broad is the user base able to access the database?
    How many employees do you have in your company? How many contractors do you have in your company? How many employees have physical access to the database server? How good is the mood of those people?
    How good are the user credentials to protect your database? Do you have shared passwords or no passwords at all? Do you have an account management (expiring old accounts, rotate passwords from time to time)?
    How much do you trust your users? Do you trust all your employees? Do you trust only admins? Or do you not even trust your admins?
  • How severe are the security vulnerabilities?
    You can define a threshold of severity of the vulnerabilities above you want to take actions. According to your criticality you can take actions for example as follows: Greater or equal than 7.5 if you have less critical data. Greater or equal than 6.0 if you have critical data.
  • Can the vulnerability be use from remote (over the network) and does it need a user authentication to exploit the vulnerability? What products (MySQL Enterprise Monitor, MySQL Server, MySQL Connectors) and what modules (Apache/Tomcat, .Net Connector, Partitioning, Stored Procedures, InnoDB, DDL, GIS, Optimizer, ODBC, Replication, DML, Performance Schema) are affected?

Depending on your readiness to take a risk you get now answers to decide if you have to take actions or not.

Some examples
  • Situation: Your database is exposed directly to the internet or you forgot to install some firewall rules to protect your MySQL port.
    Analysis: You are probably affected by CVE-2018-2696 and CVE-2017-3737 (score 5.9 and 7.5). So you passed the threshold for non-critical data (7.5) and nearly passed the threshold for critical data (6.0). These vulnerabilities allow attacks over the network without user authentication.
    Action: Immediate upgrade is recommended. Mid-term action: Install firewall rules to protect your MySQL to avoid access from remote and/or do not expose databases directly to the internet.
  • Situation: Your database is located in the intranet zone. You have slack user/password policies and you have many employees and also many contractors from foreign countries working on various projects. And you have very sensitive/interesting financial data stored in your database.
    Analysis: Many people, not all of them are really trusted, have network access to the database. It is quite possible that passwords have been shared or people have passwords for projects they are not working for any more. You are affected by nearly all of the vulnerabilities (network).
    Action: You should plan an upgrade soon. Mid-term action: Try to restrict access to the databases and implement some password policy rules (no shared passwords, password expiration, account locking etc.).
  • Situation: Your highly critical databases are located in a specially secured network and only applications, Linux admins and DBAs have access to this network. And you completely trust those people.
    Analysis: Your threshold is 6.0 and (unauthenticated) attack over the network is not possible. There are some vulnerabilities of which you are affected but the database is only accessed by an application. So those vulnerabilities cannot be exploited easily.
    Action: You possibly can ignore this CPU for the MySQL database this time. But you have a vulnerability in the .Net Connector (Connector/Net). If an attacker exploits the vulnerability on the Connector he possibly can get access to the data. So you have to upgrade the Connector of your application accessing the database.

If you follow the ideas of this aid you will probably have one or two upgrades a year. And this you should do anyway just to stay up to date...

See also Common Vulnerability Scoring System Version 3.0 Calculator.

Taxonomy upgrade extras: cpusecuritymysqlupgrade

Galera Cluster and Antivirus Scanner on Linux

Shinguz - Tue, 2017-12-12 22:51

Today we had to investigate in a very strange behaviour of IST and SST on a MariaDB Galera Cluster.

The symptom was, that some Galera Cluster nodes took a very long time to start. Up to 7 minutes. So the customer was concluding that the Galera Cluster node does an SST instead of an IST and was asking why the SST happens.

It have to be mentioned here, that the MariaDB error log is very confusing about whether it is an SST or an IST. So the customer was confused and concluded, that MariaDB Galera Cluster was doing an SST instead of IST.

Further confusing was that this behaviour was not consistently on all 3 nodes and not consistently on the 3 stages production, test and integration.

First we had to clear if the Galera node was doing an IST or an SST to exclude problems with Galera Cache or event Bugs in MariaDB Galera Cluster. For this we were running our famous insert_test.sh and did some node restarts with forcing SST and without.

As a Galera Cluster operator you must mandatorily be capable to determine which one of both State Transfers happens from the MariaDB error log:

MariaDB Error Log with IST on Joiner 2017-12-12 22:29:33 140158145914624 [Note] WSREP: Shifting OPEN -> PRIMARY (TO: 204013) 2017-12-12 22:29:33 140158426741504 [Note] WSREP: State transfer required: Group state: e2fbbca5-df26-11e7-8ee2-bb61f8ff3774:204013 Local state: e2fbbca5-df26-11e7-8ee2-bb61f8ff3774:201439 2017-12-12 22:29:33 140158426741504 [Note] WSREP: New cluster view: global state: e2fbbca5-df26-11e7-8ee2-bb61f8ff3774:204013, view# 7: Primary, number of nodes: 3, my index: 2, protocol version 3 2017-12-12 22:29:33 140158426741504 [Warning] WSREP: Gap in state sequence. Need state transfer. 2017-12-12 22:29:33 140158116558592 [Note] WSREP: Running: 'wsrep_sst_rsync --role 'joiner' --address '127.0.0.1' --datadir '/home/mysql/database/magal-101-b/data/' --defaults-file '/home/mysql/database/magal-101-b/etc/my.cnf' --parent '16426' --binlog '/home/mysql/database/magal-101-b/binlog/laptop4_magal-101-b__binlog' ' 2017-12-12 22:29:33 140158426741504 [Note] WSREP: Prepared SST request: rsync|127.0.0.1:4444/rsync_sst 2017-12-12 22:29:33 140158426741504 [Note] WSREP: REPL Protocols: 7 (3, 2) 2017-12-12 22:29:33 140158426741504 [Note] WSREP: Assign initial position for certification: 204013, protocol version: 3 2017-12-12 22:29:33 140158203852544 [Note] WSREP: Service thread queue flushed. 2017-12-12 22:29:33 140158426741504 [Note] WSREP: IST receiver addr using tcp://127.0.0.1:5681 2017-12-12 22:29:33 140158426741504 [Note] WSREP: Prepared IST receiver, listening at: tcp://127.0.0.1:5681 2017-12-12 22:29:33 140158145914624 [Note] WSREP: Member 2.0 (Node B) requested state transfer from 'Node C'. Selected 1.0 (Node C)(SYNCED) as donor. 2017-12-12 22:29:33 140158145914624 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 204050) 2017-12-12 22:29:33 140158426741504 [Note] WSREP: Requesting state transfer: success, donor: 1 2017-12-12 22:29:33 140158426741504 [Note] WSREP: GCache history reset: old(e2fbbca5-df26-11e7-8ee2-bb61f8ff3774:0) -> new(e2fbbca5-df26-11e7-8ee2-bb61f8ff3774:204013) 2017-12-12 22:29:33 140158145914624 [Note] WSREP: 1.0 (Node C): State transfer to 2.0 (Node B) complete. 2017-12-12 22:29:33 140158145914624 [Note] WSREP: Member 1.0 (Node C) synced with group. WSREP_SST: [INFO] Joiner cleanup. rsync PID: 16663 (20171212 22:29:34.474) WSREP_SST: [INFO] Joiner cleanup done. (20171212 22:29:34.980) 2017-12-12 22:29:34 140158427056064 [Note] WSREP: SST complete, seqno: 201439 2017-12-12 22:29:35 140158427056064 [Note] WSREP: Signalling provider to continue. 2017-12-12 22:29:35 140158427056064 [Note] WSREP: SST received: e2fbbca5-df26-11e7-8ee2-bb61f8ff3774:201439 2017-12-12 22:29:35 140158426741504 [Note] WSREP: Receiving IST: 2574 writesets, seqnos 201439-204013 2017-12-12 22:29:35 140158426741504 [Note] WSREP: IST received: e2fbbca5-df26-11e7-8ee2-bb61f8ff3774:204013 2017-12-12 22:29:35 140158145914624 [Note] WSREP: 2.0 (Node B): State transfer from 1.0 (Node C) complete. 2017-12-12 22:29:35 140158145914624 [Note] WSREP: Shifting JOINER -> JOINED (TO: 204534) 2017-12-12 22:29:35 140158145914624 [Note] WSREP: Member 2.0 (Node B) synced with group. 2017-12-12 22:29:35 140158145914624 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 204535) 2017-12-12 22:29:35 140158426741504 [Note] WSREP: Synchronized with group, ready for connections
MariaDB Error Log with SST on Joiner 2017-12-12 22:32:15 139817123833600 [Note] WSREP: Shifting OPEN -> PRIMARY (TO: 239097) 2017-12-12 22:32:15 139817401395968 [Note] WSREP: State transfer required: Group state: e2fbbca5-df26-11e7-8ee2-bb61f8ff3774:239097 Local state: 00000000-0000-0000-0000-000000000000:-1 2017-12-12 22:32:15 139817401395968 [Note] WSREP: New cluster view: global state: e2fbbca5-df26-11e7-8ee2-bb61f8ff3774:239097, view# 9: Primary, number of nodes: 3, my index: 2, protocol version 3 2017-12-12 22:32:15 139817401395968 [Warning] WSREP: Gap in state sequence. Need state transfer. 2017-12-12 22:32:15 139817094477568 [Note] WSREP: Running: 'wsrep_sst_rsync --role 'joiner' --address '127.0.0.1' --datadir '/home/mysql/database/magal-101-b/data/' --defaults-file '/home/mysql/database/magal-101-b/etc/my.cnf' --parent '25291' --binlog '/home/mysql/database/magal-101-b/binlog/laptop4_magal-101-b__binlog' ' 2017-12-12 22:32:15 139817401395968 [Note] WSREP: Prepared SST request: rsync|127.0.0.1:4444/rsync_sst 2017-12-12 22:32:15 139817401395968 [Note] WSREP: REPL Protocols: 7 (3, 2) 2017-12-12 22:32:15 139817401395968 [Note] WSREP: Assign initial position for certification: 239097, protocol version: 3 2017-12-12 22:32:15 139817178507008 [Note] WSREP: Service thread queue flushed. 2017-12-12 22:32:15 139817401395968 [Warning] WSREP: Failed to prepare for incremental state transfer: Local state UUID (00000000-0000-0000-0000-000000000000) does not match group state UUID (e2fbbca5-df26-11e7-8ee2-bb61f8ff3774): 1 (Operation not permitted) at galera/src/replicator_str.cpp:prepare_for_IST():482. IST will be unavailable. 2017-12-12 22:32:15 139817123833600 [Note] WSREP: Member 2.0 (Node B) requested state transfer from 'Node C'. Selected 1.0 (Node C)(SYNCED) as donor. 2017-12-12 22:32:15 139817123833600 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 239136) 2017-12-12 22:32:15 139817401395968 [Note] WSREP: Requesting state transfer: success, donor: 1 2017-12-12 22:32:15 139817401395968 [Note] WSREP: GCache history reset: old(00000000-0000-0000-0000-000000000000:0) -> new(e2fbbca5-df26-11e7-8ee2-bb61f8ff3774:239097) 2017-12-12 22:32:17 139817123833600 [Note] WSREP: 1.0 (Node C): State transfer to 2.0 (Node B) complete. 2017-12-12 22:32:17 139817123833600 [Note] WSREP: Member 1.0 (Node C) synced with group. WSREP_SST: [INFO] Joiner cleanup. rsync PID: 25520 (20171212 22:32:17.846) WSREP_SST: [INFO] Joiner cleanup done. (20171212 22:32:18.352) 2017-12-12 22:32:18 139817401710528 [Note] WSREP: SST complete, seqno: 239153 2017-12-12 22:32:18 139817132226304 [Note] WSREP: (ebfd9e9c, 'tcp://127.0.0.1:5680') turning message relay requesting off 2017-12-12 22:32:22 139817401710528 [Note] WSREP: Signalling provider to continue. 2017-12-12 22:32:22 139817401710528 [Note] WSREP: SST received: e2fbbca5-df26-11e7-8ee2-bb61f8ff3774:239153 2017-12-12 22:32:22 139817123833600 [Note] WSREP: 2.0 (Node B): State transfer from 1.0 (Node C) complete. 2017-12-12 22:32:22 139817123833600 [Note] WSREP: Shifting JOINER -> JOINED (TO: 239858) 2017-12-12 22:32:22 139817123833600 [Note] WSREP: Member 2.0 (Node B) synced with group. 2017-12-12 22:32:22 139817123833600 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 239866) 2017-12-12 22:32:22 139817401395968 [Note] WSREP: Synchronized with group, ready for connections

After we cleared that it really was an IST and that it was not a SST because of some other reasons the question rose: Why does an IST of only a few thousand transactions was taking 420 seconds. And this was not always the case...

So we were looking with top at the Donor and the Joiner during IST and we found that on the Donor node the Antivirus software was heavily using CPU (2 x 50%) and otherwise the system was doing nothing for a while and then suddenly started to transfer data over the network (possibly IST?).
Later we found, that the MariaDB datadir (/var/lib/mysql) was not excluded from the Antivirus software. And finally it looks like the Antivirus software was not properly configured by its Master server because the Antivirus software agent was from a cloned VM and not reinitialized. So the Antivirus Master server seems to be confused because there are 2 Antivirus software agents with the same ID.

Another very surprising situation which we did not expect was, that IST was much heavier influenced by the Antivirus software than SST. SST finished in a few seconds while IST took 420 seconds.

Conclusion: Be careful when using Antivirus software in combination with MariaDB Galera Cluster databases and exclude at least all database directories from virus scanning. If you want to be sure to avoid side effects (noisy neighbours) disable the Antivirus software on the database server at all and make sure by other means, that no virus is reaching your precious MariaDB Galera Cluster...

Taxonomy upgrade extras: Galera ClusterVirusAntivirusISTSSTnoisy neighbours

First Docker steps with MySQL and MariaDB

Shinguz - Fri, 2017-11-24 23:05

The Docker version of the distributions are often quite old. On Ubuntu 16.04 for example:

shell> docker --version Docker version 1.13.1, build 092cba3

But the current docker version is 17.09.0-ce (2017-09-26). It seems like they have switched from the old version schema x.y.z to the new year.month.version version schema in February/March 2017.

Install Docker CE Repository

Add the Docker's official PGP key:

shell> curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add - OK

Add the Docker repository:

shell> echo "deb [arch=amd64] https://download.docker.com/linux/ubuntu \ $(lsb_release -cs) \ stable" > /etc/apt/sources.list.d/docker.list shell> apt-get update

Install or upgrade Docker:

shell> apt-get install docker-ce shell> docker --version Docker version 17.09.0-ce, build afdb6d4

To test your Docker installation run:

shell> docker run --rm hello-world
Add Docker containers for MariaDB, MySQL and MySQL Enterprise Edition

First we want to see what Docker containers are available:

shell> docker search mysql --no-trunc --filter=stars=100 NAME DESCRIPTION STARS OFFICIAL AUTOMATED mysql MySQL is a widely used, open-source relational database management system (RDBMS). 5273 [OK] mariadb MariaDB is a community-developed fork of MySQL intended to remain free under the GNU GPL. 1634 [OK] mysql/mysql-server Optimized MySQL Server Docker images. Created, maintained and supported by the MySQL team at Oracle 368 [OK] percona Percona Server is a fork of the MySQL relational database management system created by Percona. 303 [OK] ...

OK. It seems like MySQL Server Enterprise Edition is missing. So we have to create an account on Docker Store and get the MySQL Server Enterprise Edition Image from there:

shell> docker login --username=fromdual Password: Login Succeeded

Unfortunately one can still not see MySQL Server Enterprise Edition.

But we can try anyway:

shell> docker pull store/oracle/mysql-enterprise-server:5.7 shell> docker logout shell> docker pull mysql shell> docker pull mariadb shell> docker pull mysql/mysql-server

To see what is going on on your local Docker registry you can type:

shell> docker images REPOSITORY TAG IMAGE ID CREATED SIZE mariadb latest abcee1d29aac 8 days ago 396MB mysql latest 5709795eeffa 2 weeks ago 408MB mysql/mysql-server latest a3ee341faefb 5 weeks ago 246MB store/oracle/mysql-enterprise-server 5.7 41bf2fa0b4a1 4 months ago 244MB hello-world latest 48b5124b2768 10 months ago 1.84kB

I personally do not like that all those images which are tagged with latest because I want a clear control over what version is used. MariaDB and MySQL community server have implemented this quite nicely but not MySQL Enterprise Edition:

shell> docker pull mariadb:10.0 shell> docker pull mariadb:10.0.23 shell> docker pull mysql:8.0 shell> docker pull mysql:8.0.3 docker images | sort REPOSITORY TAG IMAGE ID CREATED SIZE hello-world latest 48b5124b2768 10 months ago 1.84kB mariadb 10.0.23 93631b528e67 21 months ago 305MB mariadb 10.0 eecd58425049 8 days ago 337MB mariadb latest abcee1d29aac 8 days ago 396MB mysql 8.0.3 e691422324d8 2 weeks ago 343MB mysql 8.0 e691422324d8 2 weeks ago 343MB mysql latest 5709795eeffa 2 weeks ago 408MB mysql/mysql-server latest a3ee341faefb 5 weeks ago 246MB store/oracle/mysql-enterprise-server 5.7 41bf2fa0b4a1 4 months ago 244MB
Run a MariaDB server container

Start a new Docker container from the MariaDB image by running:

shell> CONTAINER_NAME=mariadb shell> CONTAINER_IMAGE=mariadb shell> TAG=latest shell> MYSQL_ROOT_PASSWORD=Secret-123 shell> MYSQL_ROOT_USER=root shell> docker run \ --name=${CONTAINER_NAME} \ --detach \ --env=MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD} \ ${CONTAINER_IMAGE}:${TAG} shell> docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 60d7b6de7ed1 mariadb:latest "docker-entrypoint..." 24 seconds ago Up 23 seconds 3306/tcp mariadb shell> docker logs ${CONTAINER_NAME} shell> docker exec \ --interactive \ --tty \ ${CONTAINER_NAME} \ mysql --user=${MYSQL_ROOT_USER} --password=${MYSQL_ROOT_PASSWORD} --execute="status" shell> docker image tag mariadb:latest mariadb:10.2.10 shell> docker exec --interactive \ --tty \ ${CONTAINER_NAME} \ bash shell> docker stop ${CONTAINER_NAME} shell> docker rm ${CONTAINER_NAME}
Run a MySQL Community server container shell> CONTAINER_NAME=mysql shell> CONTAINER_IMAGE=mysql/mysql-server shell> TAG=latest shell> MYSQL_ROOT_PASSWORD=Secret-123 shell> docker run \ --name=${CONTAINER_NAME} \ --detach \ --env=MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD} \ ${CONTAINER_IMAGE}:${TAG} shell> docker stop ${CONTAINER_NAME} shell> docker rm ${CONTAINER_NAME}
Run a MySQL Server Enterprise Edition container shell> CONTAINER_NAME=mysql-ee shell> CONTAINER_IMAGE=store/oracle/mysql-enterprise-server shell> TAG=5.7 shell> MYSQL_ROOT_PASSWORD=Secret-123 shell> docker run \ --name=${CONTAINER_NAME} \ --detach \ --env=MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD} \ ${CONTAINER_IMAGE}:${TAG} shell> docker ps --all CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 0cb4e6a8a621 store/oracle/mysql-enterprise-server:5.7 "/entrypoint.sh my..." 37 seconds ago Up 36 seconds (healthy) 3306/tcp, 33060/tcp mysql-ee 1832b98da6ef mysql:latest "docker-entrypoint..." 6 minutes ago Up 6 minutes 3306/tcp mysql 60d7b6de7ed1 mariadb:latest "docker-entrypoint..." 21 minutes ago Up 21 minutes 3306/tcp mariadb

All my 3 docker containers are currently running as root:

shell> ps -ef | grep docker root 13177 1 20:20 ? 00:00:44 /usr/bin/dockerd -H fd:// root 13186 13177 20:20 ? 00:00:04 docker-containerd -l unix:///var/run/docker/libcontainerd/docker-containerd.sock --metrics-interval=0 --start-timeout 2m --state-dir /var/run/docker/libcontainerd/containerd --shim docker-containerd-shim --runtime docker-runc root 24004 13186 21:41 ? 00:00:00 docker-containerd-shim 60d7b6de7ed1ff62b67e66c6effce0094fd60e9565ede65fa34e188b636c54ec /var/run/docker/libcontainerd/60d7b6de7ed1ff62b67e66c6effce0094fd60e9565ede65fa34e188b636c54ec docker-runc root 26593 13186 21:56 ? 00:00:00 docker-containerd-shim 1832b98da6ef7459c33181e9b9ddd89a4136c3b2676335bcbbb533389cbf6219 /var/run/docker/libcontainerd/1832b98da6ef7459c33181e9b9ddd89a4136c3b2676335bcbbb533389cbf6219 docker-runc root 27714 13186 22:02 ? 00:00:00 docker-containerd-shim 0cb4e6a8a62103b66164ccddd028217bb4012d8a6aad1f62d3ed6ae71e1a38b4 /var/run/docker/libcontainerd/0cb4e6a8a62103b66164ccddd028217bb4012d8a6aad1f62d3ed6ae71e1a38b4 docker-runc

But the user running the process IN the container is not root:

shell> docker exec \ --interactive \ --tty \ ${CONTAINER_NAME} \ grep ^Uid /proc/1/status Uid: 27 27 27 27 shell> docker exec \ --interactive \ --tty \ ${CONTAINER_NAME} \ bash -c "id 27" uid=27(mysql) gid=27(mysql) groups=27(mysql)
Run a Docker container from mysql user shell> id uid=1001(mysql) gid=1001(mysql) groups=1001(mysql) shell> docker images Got permission denied while trying to connect to the Docker daemon socket at unix:///var/run/docker.sock: Get http://%2Fvar%2Frun%2Fdocker.sock/v1.32/images/json: dial unix /var/run/docker.sock: connect: permission denied shell> sudo adduser mysql docker Adding user `mysql' to group `docker' ... Adding user mysql to group docker Done.
Taxonomy upgrade extras: dockermysqlmariadb

MariaDB master/master GTID based replication with keepalived VIP

Shinguz - Sat, 2017-11-11 11:29

Some of our customers still want to have old-style MariaDB master/master replication clusters. Time goes by, new technologies appear but some old stuff still remains.

The main problem in a master/master replication set-up is to make the service highly available for the application (applications typically cannot deal with more than one point-of-contact). This can be achieved with a load balancer (HAproxy, Galera Load Balancer (GLB), ProxySQL or MaxScale) in front of the MariaDB master/master replication cluster. But the load balancer by it-self should also become highly available. And this is typically achieved by a virtual IP (VIP) in front of one of the load balancers. To make operations of the VIP more handy the VIP is controlled by a service like keepalived or corosync.

Because I like simple solutions (I am a strong believer in the KISS principle) I thought about avoiding the load balancer in the middle and attach the VIP directly to the master/master replication servers and let them to be controlled by keepalived as well.

Important: A master/master replication set-up is vulnerable to split-brain situations. Neither keepalived nor the master/master replication helps you to avoid conflicts and in any way to prevent this situation. If you are sensitive to split-brain situations you should look for Galera Cluster. Keepalived is made for stateless services like load balancers, etc. but not databases.

Set-up a MariaDB master/master replication cluster

Because most of the Linux distributions have a bit old versions of software delivered we use the MariaDB 10.2 repository from the MariaDB website:

# # /etc/yum.repos.d/MariaDB-10.2.repo # # MariaDB 10.2 CentOS repository list - created 2017-11-08 20:32 UTC # http://downloads.mariadb.org/mariadb/repositories/ # [mariadb] name = MariaDB baseurl = http://yum.mariadb.org/10.2/centos7-amd64 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1

Then we install the MariaDB server and start it:

shell> yum makecache shell> yum install MariaDB-server MariaDB-client shell> systemctl start mariadb shell> systemctl enabled mariadb

For the MariaDB master/master replication set-up configuration we use the following parameters:

# # /etc/my.cnf # [mysqld] server_id = 1 # 2 on the other node log_bin = binlog-m1 # binlog-m2 on the other node log_slave_updates = 1 gtid_domain_id = 1 # 2 on the other node gtid_strict_mode = On auto_increment_increment = 2 auto_increment_offset = 1 # 2 on the other node read_only = On # super_read_only for MySQL 5.7 and newer

Then we close the master/master replication ring according to: Starting with empty server.

mariadb> SET GLOBAL gtid_slave_pos = ""; mariadb> CHANGE MASTER TO master_host="192.168.56.101", master_user="replication" , master_use_gtid=current_pos; mariadb> START SLAVE;
Installing keepalived

Literature:


The next step is to install and configure keepalived. This can be done as follows:

shell> yum install keepalived shell> systemctl enable keepalived

Important: In my tests I got crashes and core dumps with keepalived which disappeared after a full upgrade of CentOS 7.

Configuring keepalived

The most important part is the keepalived configuration file:

# # /etc/keepalived/keepalived.conf # global_defs { notification_email { root@localhost dba@example.com } notification_email_from root@master1 # master2 on the other node smtp_server localhost 25 router_id MARIADB_MM enable_script_security } # Health checks vrrp_script chk_mysql { script "/usr/sbin/pidof mysqld" weight 2 # Is relevant for the diff in priority interval 1 # every ... seconds timeout 3 # script considered failed after ... seconds fall 3 # number of failures for K.O. rise 1 # number of success for OK } vrrp_script chk_failover { script "/etc/keepalived/chk_failover.sh" weight -4 # Is relevant for the diff in priority interval 1 # every ... seconds timeout 1 # script considered failed after ... seconds fall 1 # number of failures for K.O. rise 1 # number of success for OK } # Main configuration vrrp_instance VI_MM_VIP { state MASTER # BACKUP on the other side interface enp0s9 # private heartbeat interface priority 100 # Higher means: elected first (BACKUP: 99) virtual_router_id 42 # ID for all nodes of Cluster group debug 0 # 0 .. 4, seems not to work? unicast_src_ip 192.168.56.101 # Our private IP address unicast_peer { 192.168.56.102 # Peers private IP address } # For keepalived communication authentication { auth_type PASS auth_pass Secr3t! } # VIP to move around virtual_ipaddress { 192.168.1.99/24 dev enp0s8 # public interface for VIP } # Check health of local system. See vrrp_script above. track_script { chk_mysql # If File /etc/keepalived/failover is touched failover is triggered # Similar can be reached when priority is lowered followed by a reload chk_failover } # When node becomes MASTER this script is triggered notify_master "/etc/keepalived/keepalived_master.sh --user=root --password= --wait=yes --variable=read_only" # When node becomes SLAVE this script is triggered notify_backup "/etc/keepalived/keepalived_backup.sh --user=root --password= --kill=yes --variable=read_only" # Possibly fault and stop should also call keepalived_backup.sh to be on the safe side... notify_fault "/etc/keepalived/keepalived_fault.sh arg1 arg2" notify_stop "/etc/keepalived/keepalived_stop.sh arg1 arg2" # ANY state transit is triggered notify /etc/keepalived/keepalived_notify.sh smtp_alert # send notification during state transit }

With the command:

shell> systemctl restart keepalived

the service is started and/or the configuration is reloaded.

The scripts we used in the configuration file are the following:

chk_failover.sh keepalived_backup.sh keepalived_fault.sh keepalived_master.sh keepalived_notify.sh keepalived_stop.sh
#!/bin/bash # # /etc/keepalived/keepalived_notify.sh # TYPE=${1} NAME=${2} STATE=${3} PRIORITY=${4} TS=$(date '+%Y-%m-%d_%H:%M:%S') LOG=/etc/keepalived/keepalived_notify.log echo $TS $0 $@ >>${LOG}
#!/bin/bash # # /etc/keepalived/chk_failover.sh # /usr/bin/stat /etc/keepalived/failover 2>/dev/null 1>&2 if [ ${?} -eq 0 ] ; then exit 1 else exit 0 fi

To make MariaDB master/master replication more robust against replication problems we took the following (configurable) actions on the database side:

Getting the MASTER role:

  • Waiting for catch-up replication
  • Make the MariaDB instance read/write

Getting the BACKUP role:

  • Make the MariaDB instance read-only
  • Kill all open connections

Testing scenarios

The following scenarios where tested under load (insert_test.sh):

  • Intentional fail-over for maintenance: shell> touch /etc/keepalived/failover shell> rm -f /etc/keepalived/failover
  • Stopping keepalived: shell> systemctl stop keepalived shell> systemctl start keepalived
  • Stopping MariaDB node: shell> systemctl stop mariadb shell> systemctl start mariadb
  • Reboot server: shell> reboot
  • Simulation of split-brain: shell> ip link set enp0s9 down shell> ip link set enp0s9 up

Problems

Problems we faced during set-up and testing were:

  • SElinux/AppArmor
  • Firewall

Keepalived controlling 2 virtual IPs

A second scenario we wanted to build is a MariaDB master/master GTID based replication cluster with 2 VIP addresses. This is to achieve either a read-only VIP and a read/write VIP or to have half of the load on one master and half of the load on the other master:

For this scenario we used the same scripts but a slightly different keepalived configuration:

# # /etc/keepalived/keepalived.conf # global_defs { notification_email { root@localhost dba@example.com } notification_email_from root@master1 # master2 on the other node smtp_server localhost 25 router_id MARIADB_MM enable_script_security } # Health checks vrrp_script chk_mysql { script "/usr/sbin/pidof mysqld" weight 2 # Is relevant for the diff in priority interval 1 # every ... seconds timeout 3 # script considered failed after ... seconds fall 3 # number of failures for K.O. rise 1 # number of success for OK } vrrp_script chk_failover { script "/etc/keepalived/chk_failover.sh" weight -4 # Is relevant for the diff in priority interval 1 # every ... seconds timeout 1 # script considered failed after ... seconds fall 1 # number of failures for K.O. rise 1 # number of success for OK } # Main configuration vrrp_instance VI_MM_VIP1 { state MASTER # BACKUP on the other side interface enp0s9 # private heartbeat interface priority 100 # Higher means: elected first (BACKUP: 99) virtual_router_id 42 # ID for all nodes of Cluster group unicast_src_ip 192.168.56.101 # Our private IP address unicast_peer { 192.168.56.102 # Peers private IP address } # For keepalived communication authentication { auth_type PASS auth_pass Secr3t! } # VIP to move around virtual_ipaddress { 192.168.1.99/24 dev enp0s8 # public interface for VIP } # Check health of local system. See vrrp_script above. track_script { chk_mysql chk_failover } # ANY state transit is triggered notify /etc/keepalived/keepalived_notify.sh smtp_alert # send notification during state transit } vrrp_instance VI_MM_VIP2 { state BACKUP # MASTER on the other side interface enp0s9 # private heartbeat interface priority 99 # Higher means: elected first (MASTER: 100) virtual_router_id 43 # ID for all nodes of Cluster group unicast_src_ip 192.168.56.101 # Our private IP address unicast_peer { 192.168.56.102 # Peers private IP address } # For keepalived communication authentication { auth_type PASS auth_pass Secr3t! } # VIP to move around virtual_ipaddress { 192.168.1.98/24 dev enp0s8 # public interface for VIP } # Check health of local system. See vrrp_script above. track_script { chk_mysql chk_failover } # ANY state transit is triggered notify /etc/keepalived/keepalived_notify.sh smtp_alert # send notification during state transit }
Taxonomy upgrade extras: mariadbmaster-masterkeepalivedVIPreplicationGTID

Galera Load Balancer the underestimated wallflower

Shinguz - Thu, 2017-09-21 15:25

There are some pretty sophisticated Load Balancers for Galera Clusters setups out in the market (ProxySQL, MaxScale, HAproxy, ...). They have many different exotic features. You can nearly do everything with them. But this comes at the cost of complexity. Non of them is simple any more.

A widely underestimated Load Balancer solution for Galera Cluster setups is the Galera Load Balancer from Codership. It is an simple Load Balancer solution which serves all of our daily needs when it comes to Galera Cluster. Unfortunately this product is not much promoted by the software vendor himself.

Installation of Galera Load Balancer

This starts with the installation. There are no packages ready to install. You have to compile Galera Load Balancer yourself. FromDual provides some compiled packages or can help you building and installing it.

You can get the Galera Load Balancer sources from Github. The binaries are built straight forward:

shell> git clone https://github.com/codership/glb shell> cd glb/ shell> ./bootstrap.sh shell> ./configure shell> make shell> make install

If you prefer a binary tar ball as I do, you can run the following commands instead of make install:

shell> TARGET=glb-1.0.1-linux-$(uname -m) shell> mkdir -p ${TARGET}/sbin ${TARGET}/lib ${TARGET}/share/glb shell> cp src/glbd ${TARGET}/sbin/ shell> cp src/.libs/libglb.a src/.libs/libglb.so* ${TARGET}/lib/ shell> cp files/* ${TARGET}/share/glb/ shell> cp README NEWS COPYING CONTRIBUTORS.txt CONTRIBUTOR_AGREEMENT.txt ChangeLog BUGS AUTHORS shell> tar czf ${TARGET}.tar.gz ${TARGET} shell> rm -rf ${TARGET}
Configuration of Galera Load Balancer

The Galera Load Balancer is configured in a file called glbd which must be located under /etc/sysconfig/gldb (Red Hat and its derivatives) or /etc/default/glbd (Debian and its derivatives). I did not find any option to tell Galera Load Balancer where to search for a configuration file.

The Galera Load Balancer parameters are documented here.

Starting and Stopping Galera Load Balancer

This means for me I have to specify all my parameters on the command line:

product/glb/sbin/glbd --threads 8 --max_conn 500 \ --round --fifo /home/mysql/run/glbd.fifo --control 127.0.0.1:3333 \ 127.0.0.1:3306 \ 192.168.1.1:3306:1 192.168.1.2:3306:2 192.168.1.3:3306:1

An equivalent configuration file would look as follows:

# # /etc/sysconfig/glbd.cfg # LISTEN_ADDR="127.0.0.1:3306" CONTROL_ADDR="127.0.0.1:3333" CONTROL_FIFO="/home/mysql/run/glbd.fifo" THREADS="8" MAX_CONN="500" DEFAULT_TARGETS="192.168.1.1:3306:1 192.168.1.2:3306:2 192.168.1.3:3306:1" OTHER_OPTIONS="--round"
Stopping Galera Load Balancer is simple: killall glbd
Galera Load Balancer operations

Beside starting and stopping Galera Load Balancer you also want to look into it. This can be done with the following 2 commands:

echo getinfo | nc -q 1 127.0.0.1 3333 echo getstats | nc -q 1 127.0.0.1 3333

Or if you want to have it in a more top/vmstat like style:

watch -n 1 "echo getstats | nc -q 1 127.0.0.1 3333" watch -n 1 -d "echo getinfo | nc -q 1 127.0.0.1 3333"

More interesting are operations like draining and undraining a Galera Cluster node from the Galera Load Balancer. To drain a Galera Cluster node for example for maintenance (kernel upgrade?) you can run the following command:

echo "192.168.1.2:3306:0" | nc 127.0.0.1 3333

To undrain the node again it works like this:

echo "192.168.1.2:3306:2" | nc 127.0.0.1 3333

Unfortunately Galera Load Balancer does not memorize the weight (:2).

If you want to remove or add a node from/to the Galera Load Balancer this works as follows:

echo "192.168.1.2:3306:-1" | nc 127.0.0.1 3333 echo "192.168.1.2:3306:1" | nc 127.0.0.1 3333

Further Galera Load Balancer operation tasks you can find in the documentation.

Taxonomy upgrade extras: Galera Clusterglbload balancerOperations

Find evil developer habits with log_queries_not_using_indexes

Shinguz - Wed, 2017-09-20 16:00

Recently I switched on the MariaDB slow query logging flag log_queries_not_using_indexes just for curiosity on one of our customers systems:

mariadb> SHOW GLOBAL VARIABLES LIKE 'log_quer%'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | log_queries_not_using_indexes | OFF | +-------------------------------+-------+ mariadb> SET GLOBAL log_queries_not_using_indexes = ON;

A tail -f on the MariaDB Slow Query Log caused a huge flickering on my screen.
I got to see about 5 times per second the following statement sequence in the Slow Query Log:

# User@Host: app_admin[app_admin] @ [192.168.1.42] Id: 580195 # Query_time: 0.091731 Lock_time: 0.000028 Rows_sent: 273185 Rows_examined: 273185 SELECT LAST_INSERT_ID() FROM `placeholder`; # Query_time: 0.002858 Lock_time: 0.000043 Rows_sent: 6856 Rows_examined: 6856 SELECT LAST_INSERT_ID() FROM `data`;

So at least 5 times 95 ms (5 x (92 + 3) = 475 ms) per 1000 ms (48%) where spent in these 2 statements which are running quite fast but do not use an index (long_query_time was set to 2 seconds).

So I estimate, that this load job can be speed up at least by factor 2 when using the LAST_INSERT_ID() function correctly not considering the possible reduction of network traffic (throughput and response time).

To show the problem I made a little test case:

mariadb> INSERT INTO test VALUES (NULL, 'Some data', NULL); mariadb> SELECT LAST_INSERT_ID() from test; +------------------+ | LAST_INSERT_ID() | +------------------+ | 1376221 | ... | 1376221 | +------------------+ 1048577 rows in set (0.27 sec)

The response time of this query will linearly grow with the amount of data as long as they fit into memory and the response time will explode as soon as the table does not fit into memory any more. In addition the network traffic would be reduced by about 8 Mbyte (1 Mio rows x BIGINT UNSIGNED (64-bit) + some header per row?) per second (6-8% of the network bandwidth of a 1 Gbit network link).

shell> ifconfig lo | grep bytes RX bytes:2001930826 (2.0 GB) TX bytes:2001930826 (2.0 GB) shell> ifconfig lo | grep bytes RX bytes:2027289745 (2.0 GB) TX bytes:2027289745 (2.0 GB)

The correct way of doing the query would be:

mariadb> SELECT LAST_INSERT_ID(); +------------------+ | last_insert_id() | +------------------+ | 1376221 | +------------------+ 1 row in set (0.00 sec)

The response time is below 10 ms.

So why is the first query taking so long an consuming so many resources? To get an answer to this question the MariaDB Optimizer can tell us more with the Query Execution Plan (QEP):

mariadb> EXPLAIN SELECT LAST_INSERT_ID() FROM test; +------+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+ | 1 | SIMPLE | test | index | NULL | PRIMARY | 4 | NULL | 1048577 | Using index | +------+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+ mariadb> EXPLAIN FORMAT=JSON SELECT LAST_INSERT_ID() FROM test; { "query_block": { "select_id": 1, "table": { "table_name": "test", "access_type": "index", "key": "PRIMARY", "key_length": "4", "used_key_parts": ["id"], "rows": 1048577, "filtered": 100, "using_index": true } } }

The database does a Full Index Scan (FIS, other call it a Index Fast Full Scan (IFFS)) on the Primary Key (column id).

The Query Execution Plan of the second query looks as follows:

mariadb> EXPLAIN SELECT LAST_INSERT_ID(); +------+-------------+-------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+----------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +------+-------------+-------+------+---------------+------+---------+------+------+----------------+ mariadb> EXPLAIN FORMAT=JSON SELECT LAST_INSERT_ID(); { "query_block": { "select_id": 1, "table": { "message": "No tables used" } } }
Taxonomy upgrade extras: query tuningOptimizerindexindex scanlast_insert_idexplainslowlog

Storing BLOBs in the database

Shinguz - Fri, 2017-06-30 14:18

We have sometimes discussions with our customers whether to store LOBs (Large Objects) in the database or not. To not rephrase the arguments again and again I have summarized them in the following lines.

The following items are more or less valid for all large data types (BLOB, TEXT and theoretically also for JSON and GIS columns) stored in a MySQL or MariaDB (or any other relational) database.

The idea of a relational table based data-store is to store structured data (numbers, data and short character strings) to have a quick write and read access to them.

And yes, you can also store other things like videos, huge texts (PDF, emails) or similar in a RDBMS but they are principally not designed for such a job and thus non optimal for the task. Software vendors implement such features not mainly because it makes sense but because users want it and the vendors want to attract users (or their managers) with such features (USP, Unique Selling Proposition). Here also one of my Mantras: Use the right tool for the right task:

The main topics to discuss related to LOBs are: Operations, performance, economical reasons and technical limitations.

Disadvantages of storing LOBs in the database
  • The database will grow fast. Operations will become more costly and complicated.
  • Backup and restore will become more costly and complicated for the admin because of the increased size caused by LOBs.
  • Backup and restore will take longer because of the same reason.
  • Database and table management functions (OPTIMIZE, ALTER, etc.) will take longer on big LOB tables.
  • Smaller databases need less RAM/disk space and are thus cheaper.
  • Smaller databases fit better into your RAM and are thus potentially faster (RAM vs disk access).
  • RDBMS are a relatively slow technology (compared to others). Reading LOBs from the database is significantly slower than reading LOBs from a filer for example.
  • LOBs stored in the database will spoil your database cache (InnoDB Buffer Pool) and thus possibly slow down other queries (does not necessarily happen with more sophisticated RBDMS).
  • LOB size limitation of 1 Gbyte in reality (max_allowed_packet, theoretically limit is at 4 Gbyte) for MySQL/MariaDB.
  • Expensive, fast database store (RAID-10, SSD) is wasted for something which can be stored better on a cheap slow file store (RAID-5, HDD).
  • It is programmatically often more complicated to get LOBs from a database than from a filer (depends on your libraries).

Advantages of storing LOBs in the database
  • Atomicity between data and LOB is guaranteed by transactions (is it really in MySQL/MariaDB?).
  • There are no dangling links (reference from data to LOB) between data and LOB.
  • Data and LOB are from the same point in time and can be included in the same backup.

Conclusion

So basically you have to balance the advantages vs. the disadvantages of storing LOBs in the database and decided what arguments are more important in your case.

If you have some more good arguments pro or contra storing LOBs in the database please let me know.

Literature

Check also various articles on Google.

Taxonomy upgrade extras: blobtextlobdesign

MySQL Enterprise Backup Incremental Cumulative and Differential Backup

Shinguz - Thu, 2017-05-11 17:20

Preparing the MySQL Enterprise Administrator Training I found that the MySQL Enterprise Backup Incremental Backup is not described very well. Thus I tried it out and wrote down this how-to:

Incremental Differential Backup

Full Backup mysqlbackup --user=root --backup-dir=/tape/full backup-and-apply-log grep end_lsn /tape/full/meta/backup_variables.txt end_lsn=2583666
Incremental Backups mysqlbackup --user=root --incremental-backup-dir=/tape/inc1 --start-lsn=2583666 --incremental backup grep end_lsn /tape/inc1/meta/backup_variables.txt end_lsn=2586138 mysqlbackup --user=root --incremental-backup-dir=/tape/inc2 --start-lsn=2586138 --incremental backup grep end_lsn /tape/inc2/meta/backup_variables.txt end_lsn=2589328 mysqlbackup --user=root --incremental-backup-dir=/tape/inc3 --start-lsn=2589328 --incremental backup grep end_lsn /tape/inc3/meta/backup_variables.txt end_lsn=2592519
Binary Log Backups cp /var/lib/binlog/binlog.* /tape/binlog/
Restore

This step will modify the original full backup!

mysqlbackup --incremental-backup-dir=/tape/inc1 --backup-dir=/tape/full apply-incremental-backup mysqlbackup --incremental-backup-dir=/tape/inc2 --backup-dir=/tape/full apply-incremental-backup mysqlbackup --incremental-backup-dir=/tape/inc3 --backup-dir=/tape/full apply-incremental-backup mysqlbackup --user=root --datadir=/var/lib/mysql --backup-dir=/tape/full copy-back
Point-in-Time-Recovery grep binlog_position /tape/inc3/meta/backup_variables.txt /tape/inc3/meta/backup_variables.txt:binlog_position=binlog.000001:7731 cd /tape/binlog mysqlbinlog --disable-log-bin --start-position=7731 binlog.000001 | mysql -uroot
Incremental Cumulative Backup

Full Backup mysqlbackup --user=root --backup-dir=/tape/full backup-and-apply-log grep end_lsn /tape/full/meta/backup_variables.txt end_lsn=2602954 Incremental Backups mysqlbackup --user=root --incremental-backup-dir=/tape/inc1 --start-lsn=2602954 --incremental backup mysqlbackup --user=root --incremental-backup-dir=/tape/inc2 --start-lsn=2602954 --incremental backup mysqlbackup --user=root --incremental-backup-dir=/tape/inc3 --start-lsn=2602954 --incremental backup
Binary Log Backups cp /home/mysql/database/mysql-5.7/binlog/binlog.* /tape/binlog/
Restore

This step will modify the original full backup!

mysqlbackup --incremental-backup-dir=/tape/inc3 --backup-dir=/tape/full apply-incremental-backup mysqlbackup --user=root --datadir=/var/lib/mysql --backup-dir=/tape/full copy-back
Point-in-Time-Recovery grep binlog_position /tape/*/meta/backup_variables.txt /tape/inc3/meta/backup_variables.txt:binlog_position=binlog.000001:7731 cd /tape/binlog mysqlbinlog --disable-log-bin --start-position=7731 binlog.000001 | mysql -uroot

I very much dislike that during my restore the backup is modified. So if I do a mistake during restore my backup is gone and I am doomed.

Taxonomy upgrade extras: BackupRestoreMySQL Enterprise Backupenterpriseincrementalcumulativedifferential

FromDual Backup and Recovery Manager for MySQL 1.2.5 has been released

FromDual.en - Wed, 2017-05-10 15:26

FromDual has the pleasure to announce the release of the new version 1.2.5 of the popular Backup and Recovery Manager for MySQL and MariaDB (fromdual_brman).

You can download the FromDual Backup and Recovery Manager from here.

In the inconceivable case that you find a bug in the FromDual Backup and Recovery Manager please report it to our Bugtracker or just send us an email.

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

Upgrade from 1.2.x to 1.2.5 # cd ${HOME}/product # tar xf /download/fromdual_brman-1.2.5.tar.gz # rm -f fromdual_brman # ln -s fromdual_brman-1.2.5 fromdual_brman
Changes in FromDual Backup Manager 1.2.5

This release contains mainly fixes related to the backup catalog and the backup type cleanup.

You can verify your current FromDual Backup Manager version with the following command:

shell> fromdual_bman --version
FromDual Backup Manager
  • Docker container is ready.
  • Testing infrastructure improved.
  • Better option checking to be usage compliant.
  • Option handling and checking refactored.
  • PHP variable variables_order is included into program. No more php.ini change required any more.
  • Connection error made more verbose.
  • Downstream myEnv.inc library from rev873.
  • Incompatibility change: Default --backupdir location changed from ./bck to $HOME/bck.
FromDual Backup Manager Catalog
  • Catalog creation and upgrade messages put into right order.
  • Backup catalog should be upgraded directly to newest release when created.
  • Only do catalog version check if --upgrade is not specified.
  • Incompatibility change: Catalog operation options (--create and --upgrade) made more consistent (--type=catalog). This could lead to different behaviour than earlier!
  • Backup cleanup in catalog made nicer.
  • Catalog version changed from 010 to 020.
FromDual Backup Manager Cleanup
  • Archive cleanup output empty line removed.
  • Backup cleanup in catalog made nicer.
  • Cleanup should delete only instance backups but not all backups if specified (bug #166).
FromDual Backup Manager Logical Full and Schema backup
  • Omit warning for non-transactional tables if option --blocking-backup is set.
  • Function getDumpOverview now also includes empty schemata.
  • Transactional storage engine selection made ready for MariaDB 10.1/10.2 and MySQL 8.0.
Taxonomy upgrade extras: BackupRestoreRecoverypitrfromdual_brman

How the Lack of a Primary Key May Effectively Stop the Slave

Jörg Brühe - Tue, 2017-05-02 09:50

Most (relational) DBAs and DB application developers know the concept of a primary key ("PK") and what it is good for. However, much too often one still encounters table definitions without a PK. True, the relational theory based on sets does not need a PK, and all operations (insert, select, update, delete) can also be done on tables for which no PK was defined. If performance doesn't matter (or the data volume is small, a typical situation in tests), the lack of a PK does not immediately cause negative consequences.

But recently, we had several customers who (independent of each other) had big tables without a PK in a replication setup which they wanted to delete, and they all suffered severely from that: Their replication did not progress, the slave lag grew larger and larger, and all this without reporting any error. When I say "larger and larger", I mean it: I'm not talking about minutes or even hours, I'm talking about days!

Observations

In all cases, the situation could be summarized as:

  • They were running a traditional, asynchronous replication using the "row" format.
  • They had a table without PK with many entries, say a million rows.
  • This table wasn't needed any more, and someone issued a "delete from T" statement on the master.
  • From this moment, the slave did not show any sign of progress: The output of "show slave status \G"
    - listed both the IO and the SQL slave thread as running,
    - reported the same log positions without any change,
    - did not mention any slave error,
    - but the slave lag grew in sync with the passing time.

Looking at the slave's machine load, say using "vmstat", you could see a certain amount of CPU load in user mode, possibly some "waiting for IO", and some read IO (group "io", column "bi" = "block input"). Checking with "top", you could see that the MySQL server process was the only significant CPU load, and closer inspection would reveal that it used roughly one CPU core.

(A side remark: The numbers of "vmstat" and "top" are not directly comparable: While "vmstat" reports the overall CPU usage as a percentage of the total available CPU power, "top" reports the CPU usage of the individual processes as the percentage of a single CPU core. So if you had a 4-core machine with only one active process running an infinite loop, "top" would show this process as using 100%, while "vmstat" would report the CPU as running 25% in user mode and being 75% idle. If you don't know it, look at "/proc/cpuinfo" to find the number of CPU cores.)

What was the Issue? From "top", we could tell that the MySQL server was using one CPU core at full speed. From "vmstat", we could tell that it might access some data from disk, but didn't write any significant amount. This sure looked like an infinite loop or a close relative of it, executed by only one thread.

Some of you may have heard that (under some conditions) replication may do a full table scan: This is it!

When the slave has to apply a change (for this discussion: a delete or an update) which is provided in row format, and the table has no PK (or other suitable index), the SQL thread does a full table scan searching for the matching row. Note that it doesn't stop on the first match, but rather continues the scan throughout the rest of the table.

To be honest: I don't understand why it doesn't stop after the first match. I also ran a modified test where some master rows had two matches on the slave (I had duplicated them by "insert select"): The "delete" was still replicated as before, and these additional slave rows were still present after the delete.

Until now, you may consider that "full table scan" as a claim without proof, and I agree with all demands for a more thorough check.

The Experiment For tests, I have a setup of two VMs, each running a MySQL server process, and they are configured for a traditional master-slave replication. In fact, I have it for the versions MySQL 5.5.44, 5.6.24, and 5.7.17. (Yes, I might add newer versions.)

So I designed an experiment:

  • Start both master and slave, make sure replication is running.
  • On the master, create a table without any key / index, neither primary nor secondary: CREATE TABLE for_delete ( inc_num int, # ascending numbers 1 .. 10,000 repeated for larger tables clock timestamp, # now() counted: 129 different values with 200 k rows bubble char(250) # repeat('Abcd', 60) ) DEFAULT CHARACTER SET latin1 ;
  • Insert into that table the desired number of rows (I had runs with 10 k, 20 k, 100 k, and 200 k)
  • on both master and slave, run "SELECT ... FROM information_schema.global_status WHERE ..." to get status counters,
  • Delete all rows of that table
  • sleep for a time depending on the row count (you will later see why)
  • get new status counters
  • compute the status counter differences

This experiment reliably reproduced the symptoms which the customers had reported: When the "delete" had been done on the master, the slave became busy for a long time, depending on the row count, and in this time it did not show any progress indicators. This holds for all three versions I checked.

So what did the slave do? It worked as assumed: For each row, it did a full table scan, continuing even after it had found a matching row. This can be easily seen in the status counters, which you will find in the result tables below.

In this article, I will show the results of tests with 200 k rows only, as they are the most impressive. As described above, the table was created anew for each run. Then, 10,000 rows were inserted, and this was repeated 20 times. Each such group had the value of "inc_num" go up from 1 to 10,000.

In all runs, the status counters "HANDLER_DELETE", "INNODB_ROWS_DELETED", and "INNODB_ROWS_READ" showed the value of 200,000, the table size. So the handler calls did not show any scan, it must be below that interface (= within the handler).

If the server does a table scan, it must access all data pages of the table, so there must be read requests for them to guarantee they are in the buffer pool. And yes, the status counters showed these read requests: Table 1: No Key or Index

200,000 rows, status counters on Master Slave


Version 5.5.44

INNODB_BUFFER_POOL_READ_REQUESTS 2,174,121 380,132,972 Ratio "read_requests" Slave/Master
174.8 "read_requests" per "rows_deleted" 10.9 1,900.7


Version 5.6.24

INNODB_BUFFER_POOL_READ_REQUESTS 2,318,685 379,762,583 Ratio "read_requests" Slave/Master
163.8 "read_requests" per "rows_deleted" 11.6 1,898.8


Version 5.7.17

INNODB_BUFFER_POOL_READ_REQUESTS 1,598,745 379,538,695 Ratio "read_requests" Slave/Master
237.4 "read_requests" per "rows_deleted" 8.0 1,897.7

While master and slave (from a logical point of view) do the same work (delete all rows from a 200 k rows table), the slave accesses many more pages than the master - depending on the version, the factor ranges from 164 to 237. (Note that in version 5.7 the slave didn't do worse than in earlier versions, rather the master did better. This shows clearly in the ratio of "innodb buffer pool read requests" to "rows deleted" on the master: This is about 11 in 5.5 and 5.6, but only 8 in 5.7.)

I won't bother you with the detailed results for 100 k rows. It is enough to say that for half as many rows, the master had half as many read requests, while the slave had a quarter. This means that the effort on the master grows proportional to table size, but on the slave it grows with the square of the table size. Accepting the fact that the slave always scans the full table, this is easy to understand: If the table has twice as many rows, there are twice as many pages to scan, and the number of scans also doubles.

Quadratic growth means that an increase of the table size by a factor ten lets the effort grow by a factor hundred! Now imagine I had tested with a million of rows ...

All this isn't new: In the public MySQL bug database, this is listed as bug #53375. The bug was reported on May 3, 2010, for MySQL 5.1. The bug is closed since December 15, 2011. This does not mean the full table scans were stopped - obviously, they are considered unavoidable, so the bug fix is to write an explaining message into the error log. In my tests, I encountered it only once, here it is:

[Note] The slave is applying a ROW event on behalf of a DELETE statement on table for_delete and is currently taking a considerable amount of time (61 seconds). This is due to the fact that it is scanning an index while looking up records to be processed. Consider adding a primary key (or unique key) to the table to improve performance. Avoiding the Issue

Of course, this problem is the consequence of sloppy table design: If there were a primary key, it would not occur. But this may not help those who currently have tables without a PK and dare not add one, because it would require prior testing and a maintenance window to alter the table on the master.

Luckily, there are remedies, and even more than the log message (quoted above) mentions: In my tests, I found that the slave will use any index on that table if one is available. (It even uses an index with low selectivity, which may increase the page request count.) I did 5 tests:

  • Create an index on "clock" (129 different values).
  • Create an index on "inc_num" (10,000 different values).
  • Create an index on both "clock" and "inc_num" (all combinations are distinct).
  • As before, and declare it as "unique".
  • Define the combination of "clock" and "inc_num" to be the primary key.

The "alter table" statement for this was always issued on the slave only, after the inserts, but before the delete.

In all three versions, the slave used any index available. The index on "clock" of course had many different rows per value, so it caused many even more page requests than the no-index case: between 2,800 and 3,165 per row deleted. Obviously, thios makes the problem even worse. But all other indexes were huge improvements, the primary key of course was the best choice. Here are the numbers: Table 2: With Added Key or Index

200,000 rows, status counters on Master Slave Slave Slave Slave Slave






"Alter table ..." on slave before "Delete":
Index on "clock" 129 values Index on "inc_num" 10,000 v. Index
on both
Unique I.
on both
Primary key on both Version 5.5.44





INNODB_BUFFER_POOL_READ_REQUESTS 2,174,121 633,132,032 10,197,554 4,429,243 4,421,357 1,810,734 Ratio "read_requests" Slave/Master
291.2 4.7 2.0 2.0 0.8 "read_requests" per "rows_deleted" 10.9 3,165.7 51.0 22.1 22.1 9.1






Version 5.6.24





INNODB_BUFFER_POOL_READ_REQUESTS 2,318,685 610,482,315 10,327,132 4,375,106 4,073,543 1,794,524 Ratio "read_requests" Slave/Master
342.1 5.0 2.1 2.0 0.9 "read_requests" per "rows_deleted" 11.6 3,052.4 51.6 21.9 20.4 9.0






Version 5.7.17





INNODB_BUFFER_POOL_READ_REQUESTS 1,598,745 559,398,633 9,531,444 3,859,343 3,836,390 1,720,731 Ratio "read_requests" Slave/Master
354.8 6.0 2.3 2.5 1.1 "read_requests" per "rows_deleted" 8.0 2,797.0 47.7 19.3 19.2 8.6

But one very important fact should be mentioned in addition to the numbers: The fact that it worked! By adding an index, I made the slave's schema differ from the master's. The primary key even totally changed the B-tree in which the rows are stored, and it made the slave drop the internal row ID which InnoDB had added on the master. Still, replication using the row format could handle these differences without problems.

The Way Out

So the good message is:

  • Even if you have a table without indexes or primary key, you can add these on the slave without breaking the replication.
  • If you suffer from slow replication on such a table, adding a good index or (even better) the PK will solve this problem.
  • In a replication setup, you can improve the schema on the slave and then do a failover, effectively improving it for all accesses without any maintenance window - just the short failover time.

This might help many DBAs who otherwise don't see a chance to improve a bad schema once it is used in production.

Take care!

Abbrechende MariaDB/MySQL Verbindungen

Oli Sennhauser - Sun, 2017-04-23 14:48
Translate to your preferred language:

Wer sich etwas vertieft mit den MariaDB Status Zählern (SHOW GLOBAL STATUS;) auseinander setzt, wird früher oder später auf den Zähler Aborted_clients stossen:

mariadb> SHOW GLOBAL STATUS LIKE 'aborted_clients'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | Aborted_clients | 5392 | +-----------------+-------+

Wenn man sich dann die MariaDB Dokumentation anschaut, steht da folgendes:

Number of aborted client connections. This can be due to the client not calling mysql_close() before exiting, the client sleeping without issuing a request to the server for more seconds than specified by wait_timeout or interactive_timeout, or by the client program ending in the midst of transferring data.

Also:

  • Vergessener Aufruf von mysql_close().
  • Inaktive Verbindung (Sleep) für mehr als wait_timeout oder interactive_timeout Sekunden.
  • Unerwartete Beendigung der Applikation.

Der erste Punkt geht unter die Kategorie: Unsauber programmiert und somit ein Fehler in der Anwendung.

Der zweite Punkt ist eher ein Konfigurationsproblem sei es auf Datenbankseite oder auf Applikationsseite.

Hier stellt sich die Frage: Warum sind die Timeouts so eingestellt, wenn die Timeouts kurz sind? Default für beide Timeouts ist 28800 Sekunden, also 8 Stunden.

mariadb> SHOW GLOBAL VARIABLES LIKE '%timeout'; +---------------------------+-------+ | Variable_name | Value | +---------------------------+-------+ | interactive_timeout | 28800 | | wait_timeout | 28800 | +---------------------------+-------+

Oder aber: Warum schickt die Anwendung so lange keine Daten über eine geöffnete Verbindung (hat die Anwendung die Verbindung verloren)?

Der zweite Fall trifft üblicherweise dann ein, wenn persistente Verbindungen verwendet werden (Java Connection Pool, Ruby on Rails, PHP Persistent Database Connections, etc.). Dann sollten die Entwickler den Connector so konfigurieren, dass er alle paar Sekunden einen Ping über die Verbindung schickt.

Der dritte Fall hat sehr viel Ähnlichkeit mit dem ersten Fall: Die Applikation beendet sich früher als erwartet. Das kann zum Beispiel auftreten, wenn:

  • exit() vor mysql_close() (Fall 1 von oben)
  • Applikation wurde unerwartet von aussen beendet (kill, OOM Killer, systemd, etc.)
  • Firewalls oder LoadBalancer die eine idelnde Verbindung nach einer bestimmten Zeit terminieren (z.B. 300 Sekunden).

Feststellen, wen es betrifft

Eigentlich sollte die Applikation in den meisten Fällen selber merken, wenn sie unerwartet beendet wurde (Fall 2 und 3). Sehr oft tut sie dies aber nicht. Daher müssen wir als Datenbankverantwortliche der Applikation manchmal auf die Sprünge helfen, und Ihr mitteilen, dass unerwartete Abbrüche überhaupt vorkommen und wo im Applikationscode das ungefähr geschieht.

Das erste Anzeichen dafür ist, wie oben Beschrieben, ein Status Zähler von Aborted_clients grösser 0. Spannend wir das ganze aber erst wenn wir Aborted_clients in Relation zur Uptime setzen. Wenn wir nur 10 Aborted_clients über die letzten 100 Tage haben, dann kann man diesen Zähler getrost vernachlässigen. Wenn Aborted_clients im Minutentakt hochgezählt wird, sollte man sich das Ganze schon genauer anschauen:

mariadb> SHOW GLOBAL STATUS WHERE Variable_name = 'aborted_clients' OR Variable_name = 'uptime'; +-----------------+--------+ | Variable_name | Value | +-----------------+--------+ | Aborted_clients | 5438 | | Uptime | 257991 | +-----------------+--------+ mariadb> SELECT 257991/5438 AS Abort_every_s; +---------------+ | Abort_every_s | +---------------+ | 47.4423 | +---------------+

Die nächste Frage, die sich stellt, ist, welcher Applikationsuser ist davon betroffen? Diese Frage kann auf 2 verschiedene Wege beantwortet werden. Entweder über das PERFORMANCE_SCHEMA mit der Abfrage nach Accounts, welche die Verbindung nicht sauber schliessen:

mariadb> SELECT ess.user, ess.host , (a.total_connections - a.current_connections) - ess.count_star as not_closed , ((a.total_connections - a.current_connections) - ess.count_star) * 100 / (a.total_connections - a.current_connections) as pct_not_closed FROM performance_schema.events_statements_summary_by_account_by_event_name ess JOIN performance_schema.accounts a on (ess.user = a.user and ess.host = a.host) WHERE ess.event_name = 'statement/com/quit' AND (a.total_connections - a.current_connections) > ess.count_star ; +-----------+---------------+------------+----------------+ | user | host | not_closed | pct_not_closed | +-----------+---------------+------------+----------------+ | applicat | 10.0.246.74 | 31 | 0.0001 | | applicat | 10.0.246.73 | 59 | 0.0003 | | replicate | 10.0.246.72 | 1 | 100.0000 | | applicat | 10.0.246.76 | 4 | 0.0024 | | root | localhost | 3 | 0.0053 | | applicat | localhost | 51880 | 0.2991 | | applicat | 10.0.246.77 | 1 | 100.0000 | +-----------+---------------+------------+----------------+

Oder über das Error Log, wenn die die Variable log_warnings auf 2 gesetzt ist:

mariadb> SHOW GLOBAL VARIABLES LIKE 'log_warn%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_warnings | 2 | +---------------+-------+

Bei MySQL 5.7 und neuer wird hierzu die Variable log_error_verbosity auf 3 gesetzt:

mysql> SHOW GLOBAL VARIABLES LIKE '%verbosity%'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | log_error_verbosity | 3 | +---------------------+-------+

Ein Eintrag ins Error Log sieht dann in etwa wie folgt aus:

[Warning] Aborted connection 411 to db: 'app_production' user: 'app_prod' host: 'mysql_LB' (Got an error writing communication packets) [Warning] Aborted connection 417 to db: 'app_production' user: 'app_prod' host: 'mysql_LB' (Got an error writing communication packets) [Warning] Aborted connection 424 to db: 'billing' user: 'billing' host: 'mysql_LB' (Got an error reading communication packets) [Warning] Aborted connection 433 to db: 'app_production' user: 'app_prod' host: 'mysql_LB' (Got an error reading communication packets) [Warning] Aborted connection 449 to db: 'app_production' user: 'app_prod' host: 'mysql_LB' (Got an error reading communication packets)

Somit wissen wir jetzt also bereits etwas genauer, welchen User von welchem Host mit Zugriff auf welches Schema es erwischt hat. Zudem haben wir noch die Connection ID welche eindeutig und aufsteigen ist.

Feststellen wo im Code es ungefähr passiert

Um festzustellen, wo im Applikationscode der unerwartete Abbruch ungefähr passiert haben wir wiederum 2 Möglichkeiten.

Wir können dazu das General Query Log einschalten (Achtung: kann sehr rasant anwachsen!) und dann die enstprechende Verbindung suchen:

mariadb> SET GLOBAL general_log = 1; mariadb> SHOW GLOBAL VARIABLES LIKE '%general%'; +------------------+----------------------------------------------------------------------+ | Variable_name | Value | +------------------+----------------------------------------------------------------------+ | general_log | ON | | general_log_file | /home/mysql/database/mariadb-10.2/log/chef_mariadb-10.2_general.log | +------------------+----------------------------------------------------------------------+

Eine sauber abgebaute Verbindung sieht darin wie folgt aus:

Time Id Command Argument 2017-04-20T10:26:05.613569Z 26 Connect app@localhost on test using TCP/IP 2017-04-20T10:26:05.613629Z 26 Query SELECT ... 2017-04-20T10:26:05.613681Z 26 Quit

Eine unsauber abgebaute oder noch offene Verbindung wie folgt:

Time Id Command Argument 2017-04-20T10:26:17.165585Z 27 Connect app@localhost on test using TCP/IP 2017-04-20T10:26:17.165785Z 27 Query SELECT ... Fehlendes Quit

Die zweite Möglichkeit besteht darin, die Sequenz von Abfragen über das PERFORMANCE_SCHEMA zu ermitteln. Hierzu müssen wir als erstes herausfinden, wie gross der Unterschied zwischen der Processlist ID und der Datenbankserver internen Thread ID ist:

mariadb> SELECT thread_id, processlist_id, thread_id-processlist_id AS diff FROM performance_schema.threads WHERE processlist_id IS NOT NULL ORDER BY thread_id DESC LIMIT 3; +-----------+----------------+------+ | thread_id | processlist_id | diff | +-----------+----------------+------+ | 436 | 433 | 3 | | 427 | 424 | 3 | | 420 | 417 | 3 | +-----------+----------------+------+

In einem zweiten Schritt können wir über das PERFORMANCE_SCHEMA herausfinden welche Befehle von der Applikation ausgeführt wurden:

UPDATE performance_schema.setup_consumers SET enabled = 1 WHERE name = 'events_statements_history_long'; mariadb> SELECT thread_id, event_name, sql_text, current_schema FROM performance_schema.events_statements_history_long WHERE thread_id = 433 + 3; +-----------+---------------------------------+----------------------------------------------------------+----------------+ | THREAD_ID | EVENT_NAME | SQL_TEXT | CURRENT_SCHEMA | +-----------+---------------------------------+----------------------------------------------------------+----------------+ | 436 | statement/sql/set_option | SET NAMES utf8, @@SESSION.sql_mode = 'STRICT_ALL_TABLES' | app_production | | 436 | statement/com/Ping | NULL | app_production | | 436 | statement/sql/select | select @@character_set_database as 'Value' | app_production | | 436 | statement/sql/show_tables | SHOW TABLES LIKE 'schema_migrations' | app_production | | 436 | statement/sql/show_tables | SHOW TABLES LIKE 'schema_migrations' | app_production | | 436 | statement/sql/select | SELECT `schema_migrations`.* FROM `schema_migrations` | app_production | | 436 | statement/sql/show_fields | SHOW FULL FIELDS FROM `schema_migrations` | app_production | | 436 | statement/sql/show_fields | SHOW FULL FIELDS FROM `settings` | app_production | +-----------+---------------------------------+----------------------------------------------------------+----------------+

Nun sollte es in Zusammenarbeit mit den Entwicklern nicht mehr allzu schwer fallen, die entsprechenden Stellen im Applikationscode zu finden und die Fehler zu beheben.

Taxonomy upgrade extras: verbindungaborted_clients

DOAG 2017 K+A: Aufruf zur Einreichung eines MySQL-Vortrags

FromDual.de - Fri, 2017-04-07 10:45

Der Call for Presentations für die DOAG 2017 Konferenz + Ausstellung vom 21. bis 24. November ist nun eröffnet!

Damit die DOAG erneut das umfangreichste Vortrags-Programm für Oracle/MySQL Produkte in Europa anbieten kann, benötigen wir Ihre Unterstützung.

Wir laden Sie hiermit herzlich ein Vorträge jeden Levels von 45 Minuten Länge zum Thema MySQL einzureichen. Es gilt: je mehr Praxisbezug, desto besser.

Themen können zum Beispiel sein:

  • Migration von Oracle nach MySQL.
  • Praktische Erfahrungen aus dem Betrieb eines MySQL Clusters.
  • Stolperfallen bei der Adaption einer Anwendung an MySQL.
  • Performance Tuning Tipps aus Sicht eines MySQL DBAs.
  • Upgrade nach MySQL 5.7 und Erfahrungen damit im Betrieb.
  • Gedanken zur Entscheidung für MySQL als strategische DB-Plattform.

Als Gegenleistung erhalten Sie 3 Tage kostenfreien Zutritt zur Konferenz, zur Ausstellung und allen DOAG Vorträge sowie zum grossen Galadiner.

Mit mehr als 2000 Besuchern pro Jahr ist die DOAG Konferenz + Ausstellung das Highlight der Oracle-Community im deutschsprachigen Raum. Seien Sie als Referent dabei - teilen Sie Ihr Wissen, knüpfen Sie neue Kontakte.

Jetzt bis zum 1. Juni Vortrag einreichen und dabei sein.

Wir freuen uns auf Ihre Mitwirkung
Ihr FromDual Team

Taxonomy upgrade extras: doag2017conferenceOraclemysql

MySQL and MariaDB authentication against pam_unix

Shinguz - Mon, 2017-02-13 18:02

The PAM authentication plug-in is an extension included in MySQL Enterprise Edition (since 5.5) and in MariaDB (since 5.2).

MySQL authentication against pam_unix

Check if plug-in is available:

# ll lib/plugin/auth*so -rwxr-xr-x 1 mysql mysql 42937 Sep 18 2015 lib/plugin/authentication_pam.so -rwxr-xr-x 1 mysql mysql 25643 Sep 18 2015 lib/plugin/auth.so -rwxr-xr-x 1 mysql mysql 12388 Sep 18 2015 lib/plugin/auth_socket.so -rwxr-xr-x 1 mysql mysql 25112 Sep 18 2015 lib/plugin/auth_test_plugin.so

Install PAM plug-in:

mysql> INSTALL PLUGIN authentication_pam SONAME 'authentication_pam.so';

Check plug-in information:

mysql> SELECT * FROM information_schema.plugins WHERE plugin_name = 'authentication_pam'\G *************************** 1. row *************************** PLUGIN_NAME: authentication_pam PLUGIN_VERSION: 1.1 PLUGIN_STATUS: ACTIVE PLUGIN_TYPE: AUTHENTICATION PLUGIN_TYPE_VERSION: 1.1 PLUGIN_LIBRARY: authentication_pam.so PLUGIN_LIBRARY_VERSION: 1.7 PLUGIN_AUTHOR: Georgi Kodinov PLUGIN_DESCRIPTION: PAM authentication plugin PLUGIN_LICENSE: PROPRIETARY LOAD_OPTION: ON

It seems like this set-up is persisted and survives a database restart because of the mysql schema table:

mysql> SELECT * FROM mysql.plugin; +--------------------+-----------------------+ | name | dl | +--------------------+-----------------------+ | authentication_pam | authentication_pam.so | +--------------------+-----------------------+

Configuring PAM on Ubuntu/Debian:

#%PAM-1.0 # # /etc/pam.d/mysql # @include common-auth @include common-account @include common-session-noninteractive

Create the database user matching to the O/S user:

mysql> CREATE USER 'oli'@'localhost' IDENTIFIED WITH authentication_pam AS 'mysql' ; mysql> GRANT ALL PRIVILEGES ON test.* TO 'oli'@'localhost';

Verifying user in the database:

mysql> SELECT user, host, authentication_string FROM mysql.user WHERE user = 'oli'; +-----------+-----------+-------------------------------------------+ | user | host | authentication_string | +-----------+-----------+-------------------------------------------+ | oli | localhost | mysql | +-----------+-----------+-------------------------------------------+ mysql> SHOW CREATE USER 'oli'@'localhost'; +-----------------------------------------------------------------------------------------------------------------------------------+ | CREATE USER for oli@localhost | +-----------------------------------------------------------------------------------------------------------------------------------+ | CREATE USER 'oli'@'localhost' IDENTIFIED WITH 'authentication_pam' AS 'mysql' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK | +-----------------------------------------------------------------------------------------------------------------------------------+

Connection tests:

# mysql --user=oli --host=localhost ERROR 2059 (HY000): Authentication plugin 'mysql_clear_password' cannot be loaded: plugin not enabled # mysql --user=oli --host=localhost --enable-cleartext-plugin --password=wrong ERROR 1045 (28000): Access denied for user 'oli'@'localhost' (using password: YES) # tail /var/log/auth.log Feb 13 15:15:14 chef unix_chkpwd[31600]: check pass; user unknown Feb 13 15:15:14 chef unix_chkpwd[31600]: password check failed for user (oli) # mysql --user=oli --host=localhost --enable-cleartext-plugin --password=rigth ERROR 1045 (28000): Access denied for user 'oli'@'localhost' (using password: YES) # tail /var/log/auth.log Feb 13 15:15:40 chef unix_chkpwd[31968]: check pass; user unknown Feb 13 15:15:40 chef unix_chkpwd[31968]: password check failed for user (oli)

Some research led to the following result: The non privileged mysql user is not allowed to access the file /etc/shadow thus it should be added to the group shadow to make it work:

# ll /sbin/unix_chkpwd -rwxr-sr-x 1 root shadow 35536 Mar 16 2016 /sbin/unix_chkpwd # usermod -a -G shadow mysql

Connection tests:

# mysql --user=oli --host=localhost --enable-cleartext-plugin --password=rigth mysql> SELECT USER(), CURRENT_USER(), @@proxy_user; +---------------+----------------+--------------+ | USER() | CURRENT_USER() | @@proxy_user | +---------------+----------------+--------------+ | oli@localhost | oli@localhost | NULL | +---------------+----------------+--------------+
MariaDB authentication against pam_unix

Check if plug-in is available:

# ll lib/plugin/auth*so -rwxr-xr-x 1 mysql mysql 12462 Nov 4 14:37 lib/plugin/auth_0x0100.so -rwxr-xr-x 1 mysql mysql 33039 Nov 4 14:37 lib/plugin/auth_gssapi_client.so -rwxr-xr-x 1 mysql mysql 80814 Nov 4 14:37 lib/plugin/auth_gssapi.so -rwxr-xr-x 1 mysql mysql 19015 Nov 4 14:37 lib/plugin/auth_pam.so -rwxr-xr-x 1 mysql mysql 13028 Nov 4 14:37 lib/plugin/auth_socket.so -rwxr-xr-x 1 mysql mysql 23521 Nov 4 14:37 lib/plugin/auth_test_plugin.so

Install PAM plug-in:

mysql> INSTALL SONAME 'auth_pam';

Check plug-in information:

mysql> SELECT * FROM information_schema.plugins WHERE plugin_name = 'pam'\G *************************** 1. row *************************** PLUGIN_NAME: pam PLUGIN_VERSION: 1.0 PLUGIN_STATUS: ACTIVE PLUGIN_TYPE: AUTHENTICATION PLUGIN_TYPE_VERSION: 2.0 PLUGIN_LIBRARY: auth_pam.so PLUGIN_LIBRARY_VERSION: 1.11 PLUGIN_AUTHOR: Sergei Golubchik PLUGIN_DESCRIPTION: PAM based authentication PLUGIN_LICENSE: GPL LOAD_OPTION: ON PLUGIN_MATURITY: Stable PLUGIN_AUTH_VERSION: 1.0

Configuring PAM on Ubuntu/Debian:

#%PAM-1.0 # # /etc/pam.d/mysql # @include common-auth @include common-account @include common-session-noninteractive

Create the database user matching to the O/S user:

mysql> CREATE USER 'oli'@'localhost' IDENTIFIED VIA pam USING 'mariadb' ; mysql> GRANT ALL PRIVILEGES ON test.* TO 'oli'@'localhost';

Verifying user in the database:

mysql> SELECT user, host, authentication_string FROM mysql.user WHERE user = 'oli'; +------+-----------+-----------------------+ | user | host | authentication_string | +------+-----------+-----------------------+ | oli | localhost | mariadb | +------+-----------+-----------------------+

Connection tests:

# mysql --user=oli --host=localhost --password=wrong ERROR 2059 (HY000): Authentication plugin 'dialog' cannot be loaded: /usr/local/mysql/lib/plugin/dialog.so: cannot open shared object file: No such file or directory # tail /var/log/auth.log Feb 13 17:11:16 chef mysqld: pam_unix(mariadb:auth): unexpected response from failed conversation function Feb 13 17:11:16 chef mysqld: pam_unix(mariadb:auth): conversation failed Feb 13 17:11:16 chef mysqld: pam_unix(mariadb:auth): auth could not identify password for [oli] Feb 13 17:11:16 chef mysqld: pam_winbind(mariadb:auth): getting password (0x00000388) Feb 13 17:11:16 chef mysqld: pam_winbind(mariadb:auth): Could not retrieve user's password # mysql --user=oli --host=localhost --password=wrong --plugin-dir=$PWD/lib/plugin ERROR 1045 (28000): Access denied for user 'oli'@'localhost' (using password: NO) Feb 13 17:11:30 chef mysqld: pam_unix(mariadb:auth): authentication failure; logname= uid=1001 euid=1001 tty= ruser= rhost= user=oli Feb 13 17:11:30 chef mysqld: pam_winbind(mariadb:auth): getting password (0x00000388) Feb 13 17:11:30 chef mysqld: pam_winbind(mariadb:auth): pam_get_item returned a password Feb 13 17:11:30 chef mysqld: pam_winbind(mariadb:auth): request wbcLogonUser failed: WBC_ERR_AUTH_ERROR, PAM error: PAM_USER_UNKNOWN (10), NTSTATUS: NT_STATUS_NO_SUCH_USER, Error message was: No such user

Add mysql user to the shadow group:

# ll /sbin/unix_chkpwd -rwxr-sr-x 1 root shadow 35536 Mar 16 2016 /sbin/unix_chkpwd # usermod -a -G shadow mysql

Connection tests:

# mysql --user=oli --host=localhost --password=right --plugin-dir=$PWD/lib/plugin mysql> SELECT USER(), CURRENT_USER(), @@proxy_user; +---------------+----------------+--------------+ | USER() | CURRENT_USER() | @@proxy_user | +---------------+----------------+--------------+ | oli@localhost | oli@localhost | NULL | +---------------+----------------+--------------+
Taxonomy upgrade extras: authenticationpamsecuritypluginplug-in

FromDual Performance Monitor for MySQL and MariaDB 1.0.1 has been released

FromDual.en - Fri, 2017-02-10 15:13

FromDual has the pleasure to announce the release of the new version 1.0.1 of its popular Database Performance Monitor for MySQL, MariaDB, Galera Cluster and Percona Server fpmmm.

You can download fpmmm from here.

In the inconceivable case that you find a bug in the fpmmm please report it to our Bug-tracker.

Any feedback, statements and testimonials are welcome as well! Please send them to feedback@fromdual.com.

This release contains various bug fixes.

Changes in fpmmm v1.0.1 fpmmm agent
  • Fpmmm suppresses server has gone away message to stdout.
  • Fpmmm should behave correctly now when database was started after fpmmm agent.
  • MyEnv library synced from MyEnv project.
  • Added LaunchDaemon configuration for fpmmmm on Max OSX (darwin).
fpmmm agent installer
  • Added some more support for Mac OSX (darwin).

For subscriptions of commercial use of fpmmm please get in contact with us.

Taxonomy upgrade extras: mysqlperformancemonitormonitoringfpmmmmaasperformance monitormpmrelease

Pages

Subscribe to MySQL, Galera Cluster and MariaDB support and services aggregator