Feed Aggregator

Mostly, i use changing max_connection to 1 or ‘skip networking’.

Thanks for this new option.

There are some important differences between InnoDB FTS and MyISAM FTS. For example parser plugins won’t be used (but can be configured) (Bug #62004) and some defaults are not the same (InnoDB fulltext search in MySQL 5.6 part 2 at MySQL Performance Blog)

So only checking for the MySQL version might not be sufficient.

Switching from MySQL/MyISAM to Galera Cluster requires that all tables (except those from the mysql, information_schema and performance_schema) are using the InnoDB Storage Engine.

For altering the Storage Engine of the tables we wrote a script (alter_engine.pl) long time ago already. Because we have made many of those switches recently we have extended its functionality.

New features

• Recognizes VIEW’s and does NOT try to alter their Storage Engine (bug).
• Script is MySQL version aware. Complain if too old MySQL version is used.
• Find tables without a Primary Key.
• Check for too long InnoDB Primary Keys
• Check for FULLTEXT indexes in MySQL 5.1 and 5.5 and write a note if version is older.

Example

./alter_engine.pl
User                              [root] : 
Password                              [] : secret
Schema from (or all)              [test] : all
Engine to                       [InnoDB] : 

Version is   : 5.6.10
MR Version is: 050610

The following tables might not have a Primary Key: …

From time to time some maintenance work on the MySQL database has to be done. During the maintenance window we do not want to have application traffic on the database.

Sometimes it is hard to shut down all applications spread over the whole company. Or we want to allow only some specific hosts to access mysql from remote (for example the monitoring system or the backup server).

For this purpose we can use the Linux packet filtering.

To see what packet filtering rules are available we can run the following command:

iptables -L INPUT -v

To close the MySQL port on all interfaces we use:

iptables -A INPUT -p tcp --dport mysql -j DROP

and to open the MySQL port again after the maintenance window:

iptables -D INPUT -p tcp --dport mysql -j DROP

With the -i option we can restrict the rule to a specific interface for example eth0 and with the option -s we can specify a specific source only. Or with a ! -s we can implement an inverse rule (all but).

The mpm agent writes a lock file to prevent that several instances are running at the same time and piling up and possibly slow down the system.

This is the message for. There are several reasons why this message can occur:

• Agent are started too frequently or are locked by something
• Agent died and did not clean the lock file
• Agent quit before cleaning the lock file

Theoretically this message should disappear after a while (10 tries?). You can also try to kill process (3366 in this case) and remove the lock file manually.

If this happens often then something is wrong and/or the interval is too short.

I have gotten the FromDual modules and templates working on 2 of our 3 database servers. The last one, I keep getting the below errors in the FromDual log file: 3387:2013-02-05 12:55:51.296 - INFO: FromDual Performance Monitor for MySQL (0.9) run started. 3387:2013-02-05 12:55:51.297 - INFO: FromDualMySQLagent::setAgentLock 3387:2013-02-05 12:55:51.298 - WARN: Agent Lock file /tmp/FromDualMySQLagent.lock already exists. 3387:2013-02-05 12:55:51.299 - WARN: Another agent with PID 3366 (our PID is 3387) is running. We will abort now. Are there any suggestions on how to fix this? Thank you!

A while ago it was pretty inconvenient to start a complete Galera Cluster from scratch. Rolling restart an such things are already working well but bootstrapping was a pain.

With Galera v2.2 new functionality came in. We tried it out and it did not work as documented. :-( Thanks to Teemu’s help we found there was a documentation bug in the Galera documentation.

The settings which were working for us are:

wsrep_cluster_address = "gcomm://192.168.1.2,192.168.1.3?pc.wait_prim=no"

And when all 3 nodes of the Galera Cluster are started and ready to join you can run:

SET GLOBAL wsrep_provider_options="pc.bootstrap=1";

I hope we can go life on Thursday with the new Telco VoIP Cluster for 2500 employees…

Have fun and enjoy an even better Galera Cluster for MySQL!

I have gotten the FromDual modules and templates working on 2 of our 3 database servers. The last one, I keep getting the below errors in the FromDual log file:

3387:2013-02-05 12:55:51.296 - INFO: FromDual Performance Monitor for MySQL (0.9) run started. 3387:2013-02-05 12:55:51.297 - INFO: FromDualMySQLagent::setAgentLock 3387:2013-02-05 12:55:51.298 - WARN: Agent Lock file /tmp/FromDualMySQLagent.lock already exists. 3387:2013-02-05 12:55:51.299 - WARN: Another agent with PID 3366 (our PID is 3387) is running. We will abort now.

Are there any suggestions on how to fix this?

Thank you!

Copy a directory structure from one to an other server:

tar zcvf - mysql | ssh mysql@laptop3 "cd /home ; tar zxvf -"

FromDual invites you to check out our 2013 professional course schedule, set out below.

You need to book early to avoid disappointment. Why?

Because we run small, intimate and in-depth classes, giving you more time for -

• individual attention
• lots of interaction
• sharing of insights and experience

Our aim is to give you real value for your money and a genuine return for your investment with us.

Special Addition

Due to customer requests, we have added two of our advanced MySQL training courses in late April in Zurich, Switzerland. (One course will be presented in German, the other in English.)

Venues

Germany – The Linux Hotel in Essen and the Heinlein Academy in Berlin

Switzerland – HSO in Zürich

Training Schedule

Due to customer requests, we have added two of our advanced MySQL training courses in late April in Zurich, Switzerland. One course will be presented in German, the other in English.

The venue is the HSO in Zurich-Oerlikon.

The following dates has been set:

Caution: the 2^nd training contains May 1^st (bank holiday). The training takes place regardless.

Remember to book now to reserve your place.

The MySQL SIG of DOAG invites you to join on February 27 at the Inside Hotel in Munich. Topics of the SIG Event: MySQL Replication and new Features.

Overview

• MySQL Security Products
• Useful Security Links
• Security Vulnerabilities Fixed in MariaDB
• Oracle Critical Patch Update (CPU) Advisory for MySQL
• RedHat/CentOS vulnerabilities by CVE name
• Ubuntu Security Notice (USN) for MariaDB
• Ubuntu Security Notice (USN) for MySQL
• Debian Security Advisories
• Debian Security Advisories for MySQL
• MySQL Exploits
• Debian Security Advisories for MariaDB
• MariaDB Exploits
• MariaDB Security Risk Matrix
• Cyber Emergency Response Team (CERT) in Australia and APAC
• MariaDB Security | HackerOne

MySQL Security Products

• Database Firewall: GreenSQL
• MySQL Database Security: McAfee

Useful Security Links

• STIG Update: DISA releases the MariaDB Enterprise 10.x Security Technical Implementation Guide
• OWASP: Database Security Cheat Sheet
• CIS MySQL 4.1/5.0/5.1 Benchmark (v1.0.2)
• CIS Oracle MySQL Community Server 5.6 Benchmark (v1.1.0)
• CIS Oracle MySQL Community Server 5.7 Benchmark (v1.0.0)
• CIS Oracle MySQL Enterprise Edition 5.6 Benchmark (v1.1.0)
• CIS Oracle MySQL Enterprise Edition 5.7 …

Register here for our MySQL and MariaDB trainings in Switzerland. For our MySQL and MariaDB trainings in Germany you can register directly on-line at the MySQL and MariaDB training class schedule.

Prices

The training fees for Switzerland are:

• SQL for beginner (3 days): CHF 2'880.-
• MySQL for beginner (2 days): CHF 1'920.-
• MySQL for developer (3 days): CHF 2'880.-
• Advanced MySQL (5 days): CHF 4'450.-
• Galera Cluster for MySQL (2 days): CHF 1'920.-

If you wish other training dates, please get in contact with us.

You are already familiar with the MySQL or MariaDB and you are challenged to operate a serious MySQL database. If this is the case this training is the right one for you.

Requirements

You are familiar with the MySQL basics and have already some experience with MySQL or MariaDB operations. The SQL language does not look strange to you any more. Further you are familiar with the Linux console and rule the most important UNIX commands.

Audience

• MySQL application developer
• MySQL and MariaDB database administrators
• System administrators with database responsibility

Contents

The following 3 modules can also be booked separately for our facility in Essen (Linuxhotel).

MySQL and MariaDB operations (1 day)

• Installation and configuration of MySQL and MariaDB
• Backup and restore with mysqldump and xtrabackup
• Point-in-Time-Recovery (PiTR)
• Security

MySQL and MariaDB High Availability (2 days)

• Overview over the MySQL architecture
• MySQL architectures
• Master/Slave replication
• Master/Master replication
• Virtual IP …

Some MySQL customers do not want to use the root user for mysqldump backups. For this user you have to grant the following minimal MySQL privileges:

mysqldump --single-transaction (InnoDB)

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'secret';
GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER ON *.* TO 'backup'@'localhost';

mysqldump --lock-all-tables (MyISAM)

GRANT LOCK TABLES ON *.* TO 'backup'@'localhost';

If we missed a privilege please let us know.

The I_S.INNODB_BUFFER_PAGE query seems unsafe to me, as it queries the buffer pool and not the tablespace. The innochecksum approach to find unused pages at the end to truncate + header field patching seems to be as OK as it could possibly be given the method.

It seems that implementing the truncate of unused tablespace trailer would not be that hard (say, in XtraDB), and compacting with page relocation should be possible too with more effort. I wonder if it’s on InnoDB roadmap.

In this weeks MySQL workshop we were discussing, beside other things, about the innodb_file_per_table parameter and its advantages of enabling it. In addition there was a discussion if the InnoDB system tablespace file can be shrinked once it has been grown very large or not. We all know the answer: The InnoDB system tablespace file does never shrink again.

But why should it not be possible? Other databases like for example Oracle can shrink or even get rid of tablespace files… After some philosophising about it we came to the conclusion that we should give it a try if this is possible with InnoDB as well.

The scenario we considered was the following: You inherit a MySQL database with InnoDB tables but innodb_file_per_table was set to 0. So all the tables are located in the InnoDB tablespace file. And only a small amount of space is left on the device and there is a lot of free space in the InnoDB system tablespace file. The database itself is much too big to dump and restore and we want to get rid of …

It’s a nice but tricky solution.
 

1. If the procedure is dropped, so are the grants (doesn't happen if you drop the database 'tracking')
2. If the init_connect fails the user is denied access.

MySQL hosting providers can easily loose the overview over their customers and which user or schema is still in use and which not.

The MySQL database becomes bigger and bigger, uses more and more RAM and disk space and the backup takes longer and longer.

In this situation it would be nice to know which MySQL database user has logged in within the last 6 months for example. MySQL database users who did not login within a defined period can be backuped and removed from the production MySQL database.

The following MySQL login trigger helps to track the login of all non-super privileged MySQL users.

First we need a table where to log the login of the users:

-- DROP DATABASE tracking;
CREATE DATABASE tracking;

use tracking;

-- DROP TABLE IF EXISTS login_tracking;
CREATE TABLE login_tracking (
  user VARCHAR(16)
, host VARCHAR(60)
, ts TIMESTAMP
, PRIMARY KEY (user, host)
) engine = MyISAM;

Then we need a MySQL stored procedure which does the logging of the login:

-- DROP PROCEDURE IF EXISTS login_trigger; …