Feed Aggregator

You are already a database application developer or on the way there. Now you would like to learn more about, what MySQL and MariaDB features are available which you can use later to build even better applications, to develop more efficiently applications and shorten your time to market.

Requirements

You are already familiar with the SQL language and have already some experience with MySQL or any other relational SQL database system.

Audience

• Future MySQL application developer
• MySQL and MariaDB database administrators

Contents

• Connecting and MySQL-Tools
• JOIN, UNION, SUBQUERY
• Transactions
• Locking
• Deadlocks
• Import and export of data
• Views, Materialized Views, Virtual Columns
• MySQL Functions
• Spatial Functions
• Fulltext search
• Prepared Statements
• Variables
• Stored Routines
• Stored Functions
• Triggers
• Events
• Compound Statements and Cursor
• Dynamic SQL
• Exceptions and error handling
• Debugging and Logging
• Profiling
• User Defined Functions
• Schema Tuning
• Query Tuning
• Software Profiling

Duration

3 days

Booking trainings …

You are new in the field of relational SQL databases and have the task to get familiar with MySQL oder MariaDB. This training teaches you the basics of MySQL.

Requirements

You are ideally already familiar with the SQL language and know-how to use Linux or Windows systems.

Audience

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

Contents

• Installation of MySQL
• Configuration of MySQL
• Starting and stopping of MySQL
• MySQL applications and the command line
• Creating database schemata and user
• MySQL architecture and storage engines
• Managing user privileges
• Security and upgrade
• Backup, restore and Recovery
• Monitoring of MySQL
• MySQL log files
• Non standard compliant MySQL special commands

Duration

2 days

Booking trainings

Trainings for Germany can be booked on-line here and trainings in Switzerland here.

Advanced trainings

• Advanced MySQL and MariaDB training
• MySQL and MariaDB developer training

You are new in the field of relational SQL databases and have the task to learn the SQL language. This training teaches you the SQL basics.

Requirements

You are an experienced user of Linux or Windows systems and you ideally have already learned another programming language.

Audience

• Future MySQL application developer
• Future MySQL and MariaDB database administrators
• System administrators with MySQL database responsibility
• Business analysts

Contents

• SQL, relations and relational databases
• Connecting to and accessing databases
• SELECT, WHERE, JOIN
• Functions
• GROUP BY, ORDER BY, DISTINCT
• UNION and sub queries
• DML commands: INSERT, UPDATE and DELETE
• DDL commands: CREATE, ALTER, DROP
• Data types
• Indexes
• Views
• Query Tuning basics

Duration

3 days

Booking trainings

Trainings for Germany can be booked on-line here and trainings in Switzerland here.

Advanced trainings

• MySQL and MariaDB beginner training
• MySQL and MariaDB developer training

We offer our MySQL and MariaDB trainings together with well-respected training organizations: The Linuxhotel in Essen (Germany), the Heinlein Academy in Berlin (Germany) and GFU Cyrus AG in Cologne (Germany). Our own MySQL and MariaDB training classes are hold in the building of the HSO in Zürich (Switzerland).

If you prefer a MySQL or MariaDB in-house training at your location and if you provide the infrastructure we are happy to train you on-site either in German or English as well.

Further dates or dates in Zurich on request. During the year more dates can come along.

FromDual offers different MySQL, Galera Cluster and MariaDB training modules. For training dates see our MySQL and MariaDB training class schedule.

SQL for Beginners (3 days)

This training is made for all who want to learn the basics of the SQL language. Future DBAs, database developers and data analysts.

MySQL and MariaDB for Beginners (2 days)

This MySQL and MariaDB training is made for those who want to learn the basics of the MySQL database system. Future MySQL DBAs and MySQL database developers.

MySQL and MariaDB for Developers (3 days)

This MySQL and MariaDB training is made for application developers or DevOps who want to learn more specific MySQL development features beside the SQL language.

Advanced MySQL and MariaDB (5 days)

This MySQL and MariaDB training is made for MySQL DBAs or DevOps who have to operate more demanding MySQL database servers, architects who need to know more about complex MySQL architectures and developers who want to know more about how applications can interact with complex …

Hi oli

Since MySQL 5.6.2 there is a new MySQL variable called binlog_rows_query_log_events. If you enable this variable MySQL will write the original SQL statements into the binary logs. More information about this variable you can find here:

http://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#sysvar_binlog_rows_query_log_events

To see the original queries you can either run SHOW BINLOG EVENTS ... in the MySQL client or you can run mysqlbinlog -vv binary-log.000001

I hope, this helps?

Regards,
shinguz

“Split Brain” is the term commonly used for a cluster whose nodes have different contents, rather than identical as they should have. Typically, a “split brain” situation is the DBA’s nightmare, and the Galera software is designed to avoid it. Galera is very successful in that avoidance, and it needs some special steps by the DBA to achieve “split brain”. Here is how to do it - or, for most DBAs, what to avoid doing to not get a split-brain cluster.

Galera’s Design

First, let’s remember how Galera is operating:

• The Galera software ensures that all nodes participating in a cluster will start from identical contents, by doing a “snapshot state transfer” (SST) of all current data to a newly joining node.
• When the cluster is running, Galera transfers all changes (transactions) to all cluster nodes and applies them (or rolls back and ignores, in the case of a conflict).
• If some connections get lost, all nodes check whether they “have …

Hello all

we use MySQL binary logging with binlog_format = row. With mysqlbinlog -v binary-log.000001 we can see proximately how the original query looked like. But this is a transformation from row events into pseudo SQL statements.

How can we see the original SQL statements again?

Regards,
Oli

Problem

We use the following binary log filter for filtering out some schemata.

binlog-ignore-db = mysql, information_schema, test

What is wrong with this filter it somehow does not work: :-(

Solution

there are different things to mention:

• The dash (-) notation is for command line options (--binlog-ignore-db) the underscore (_) is for the MySQL configuration file (binlog_ignore_db = ...). The configuration accepts both but is is not good style.
• Then because MySQL database names can contain special characters and commas (,) your filter is interpreted as database name "mysql, information_schema, test". This is possibly not what you intended. You have to put every database filter into its own separate line like this:

  binlog_ignore_db = mysqlbinlog_ignore_db = information_schemabinlog_ignore_db = test
  

  
  

  To specify multiple databases you must use multiple instances of this option. Because database names can contain commas, the list will be treated as the name of a single database if you supply a …

In my first Blog ever, I am going to cover the migration of a SQLite-Database to MySQL. The Tool used is MySQL-Workbench, which you can Download from the MySQL website. In this particular case, it is about the upgrade of mocenter 0.2 to 0.3.

In the Workbench on the right side, you have the button “Database Migration”. Once you click on it, the introduction to the migration wizard will show up.

Setting up source and target

On the bottom of the screen there is a button called “Start Migration”. Click it to get to the source selection.

On the first drop down menu choose “SQLite”. The menu will now change and give you the possibility to load a file. After you did this you can test the connection with the button on the bottom left, or go to the bottom right and click next.

You get a new menu, where you can choose your target of the database. You can use a stored connection or a new one. If you have no idea which connection type to use, TCP/IP usually works fine. Here I recommend you to test the connection, so …

FromDual has the pleasure to announce the release of the new version 1.2.2 of its popular MySQL, Galera Cluster, MariaDB and Percona Server multi-instance environment MyEnv.

The new MyEnv can be downloaded here.

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

Any feedback, statements and testimonials are welcome as well! Please send them to [feedback@fromdual.com](mailto:feedback@fromdual.com?Subject=Feedback for MyEnv).

Upgrade from 1.1.x to 1.2.2

# cd ${HOME}/product
# tar xf /download/myenv-1.2.2.tar.gz
# rm -f myenv
# ln -s myenv-1.2.2 myenv

If you are using plug-ins for showMyEnvStatus create all the links in the new directory structure:

cd ${HOME}/product/myenv
ln -s ../../utl/oem_agent.php plg/showMyEnvStatus/

Changes in MyEnv 1.2.2

MyEnv

• IP addresses are shown in up.
• All values from [default] section should be inherited to [dbname] section.
• Configuration file parser made more robust.
• Empty my.cnf is caught correctly now.

MyEnv Installer

• Mac OSX …

Problem

My MySQL Replication Slave is lagging more and more and I have no clue why. This happens since last weekends application change. I see that one Core is running at 100% user time but the I/O system is nearly completely idle. Any help is appreciated!

Solution

I assume this is a known problem related to MySQL Row Based Replication with tables on the Slave without Primary Keys.

Please check:

• If Replication is Row Based (SHOW GLOBAL VARIABLES LIKE ‘binlog_format’; )
• If there are some tables on the Slave without a Primary Key
• If the currently executed event by the SQL thread is affecting one of those Primary Key less tables
• You can additionally check with SHOW GLOBAL STATUS WHERE variable_name IN (‘Handler_read_rnd_next’, ‘Innodb_rows_read’); if there are more reads than expected. The first one is an indication of full table scans which would support my theory.

Nagios/Icinga plugins for MySQL and MariaDB

Nagios and Icinga compatible plugins to monitor MySQL, Galera Cluster, MariaDB and Percona Server. If you use Nagios or Icinga these are the MySQL monitoring plugins you must have.

Get the Latest release.

FromDual Ops Center for MariaDB and MySQL (focmm)

The FromDual Ops Center for MariaDB, MySQL and Galera Cluster helps you to operate your complex MariaDB/MySQL database set-ups with and easy to use Graphical User Interface (Web-GUI). Master/Slave- and Master/Master-Replication, Galera Cluster and VIP failover, etc. is possible.

Get the Latest release.

FromDual Performance Monitor for MySQL and MariaDB including MaaS

The FromDual Performance Monitor for MySQL, Galera Cluster, MariaDB and Percona Server is a Monitoring solution based on Zabbix. Its various alerts, advisors and graphs help you to monitor and operate your MySQL databases. This solution is also available as Monitoring as a Service solution if you want to avoid the hassle of installing your own monitoring solution.

Get the Latest release.

FromDual Backup and Recovery Manager (brman) for MariaDB and MySQL

The FromDual Backup and Recovery Manager (brman) for MariaDB and MySQL is an easy to use software to do Backup, Restore and Point-in-Time-Recovery for MySQL, Galera Cluster, MariaDB and Percona Server. It integrates the utilities mysqldump, mysql, mariabackup, xtrabackup and mysqlbackup (MySQL Enterprise Backup).

Get the Latest release.

FromDual has the pleasure to announce the release of the new version 0.10.5 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 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 and improvements. The previous release had some major bugs so we recommend to upgrade…

New installation of fpmmm v0.10.5

Please follow our fpmmm installation guide. A specific fpmmm installation guide will follow with the next version.

Prerequisites

CentOS 6

# yum install php-cli php-process php-mysqli

# cat << _EOF >/etc/php.d/fpmmm.ini
variables_order = "EGPCS"
date.timezone = 'Europe/Zurich'
_EOF

rpm -Uvh http://repo.zabbix.com/zabbix/2.2/rhel/6/x86_64/zabbix-release-2.2-1.el6.noarch.rpm
yum update
yum install zabbix-sender

CentOS 7 …

With Galera version 25.3.10, the “garbd” daemon would not start unless I appended the port number (default: 4567) to the IP addresses of the cluster nodes.
The failure symptom was that an exception “gu::NotSet” was thrown, without any helpful information what it was really missing.

How many connections can be opened concurrently against my MySQL or MariaDB database can be configured and checked with the following command:

SHOW GLOBAL VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 505   |
+-----------------+-------+

If this limit was ever reached in the past can be checked with:

SHOW GLOBAL STATUS LIKE 'max_use%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 23    |
+----------------------+-------+

But on MySQL instances with many different applications (= databases/schemas) and thus many different users it is a bit more complicated to find out which of these users have connected how many times concurrently. We can configure how many connections one specific user can have at maximum at the same time with:

SHOW GLOBAL VARIABLES LIKE 'max_user_connections';
+----------------------+-------+
| Variable_name        | Value …

FromDual has the pleasure to announce the release of the new version 1.2.1 of its popular MySQL, Galera Cluster, MariaDB and Percona Server multi-instance environment MyEnv.

The new MyEnv can be downloaded here.

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

Any feedback, statements and testimonials are welcome as well! Please send them to [feedback@fromdual.com](mailto:feedback@fromdual.com?Subject=Feedback for MyEnv).

Upgrade from 1.1.x to 1.2.1

# cd ${HOME}/product
# tar xf /download/myenv-1.2.1.tar.gz
# rm -f myenv
# ln -s myenv-1.2.1 myenv

If you are using plug-ins for showMyEnvStatus create all the links in the new directory structure:

cd ${HOME}/product/myenv
ln -s ../../utl/oem_agent.php plg/showMyEnvStatus/

Changes in MyEnv 1.2.1

MyEnv

• Bug in myenv.server fixed (chmod user:).
• Distribution check for CentOS 7.1 fixed (bug report Jörg).
• Directory /var/run/mysqld is created by myenv.server
• Missing function output added.
• Output changes unified, execute …