You are here

Shinguz's blog

Partial physical database restore for MariaDB and MySQL

Taxonomy upgrade extras: 

What is it about?

When describing backup and restore scenarios, a full backup and a full restore of the database instance (mariadbd/mysqld) are usually assumed. This means that the entire database instance, including all databases (schemas), is backed up and restored.

In practice, however, the situation is often different: An entire database instance is not to be restored, but only individual databases or even individual tables, because only these have broken.

Shrinking the InnoDB system tablespace

Taxonomy upgrade extras: 

One feature that really excited me in the new MariaDB 11.4 LTS release is the shrinking of the system tablespace (ibdata1). I have been eagerly waiting for this feature since around 2006 and now it has finally arrived with MariaDB 11.4.
Actually, this feature has been available since MariaDB 11.2 IR (June 2023).

Unfortunately, the announcement of this feature came a little too short. In the MariaDB release notes it says succinctly:

FromDual Ops Center 1.2.2 for MariaDB and MySQL databases has been released

FromDual is pleased to announce the release of the new version 1.2.2 of the popular FromDual Ops Center focmm, a Graphical User Interface (GUI) for MariaDB, MySQL and compatible databases.

The FromDual Ops Center for MariaDB and MySQL (focmm) helps DBAs and system administrators to better manage their MariaDB and MySQL databases and Galera Cluster farms. Ops Center makes life easier for DBAs and Admins!

MariaDB's parallel replication to catch up

Due to an application error, our replication stopped for 5 days (over Easter). After the problem was solved, the replication was supposed to catch up, which turned out to be very slow. All the usual tricks (innodb_flush_log_at_trx_commit, sync_binlog, etc.) had already been exhausted. So we tried our hand at parallel replication of the MariaDB server.

Parallel replication is deactivated by default:

Building MariaDB Server from the sources

Taxonomy upgrade extras: 

Recently I had to test a new MariaDB feature that was developed at our request (MDEV-33782). To test this feature I had to build the MariaDB server myself from source, which I have not done for a long time. So a new challenge, especially with CMake...

I followed the MariaDB documentation Get, Build and Test Latest MariaDB the Lazy Way to build the server.

MariaDB/MySQL Environment MyEnv 2.1.0 has been released

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

The new MyEnv can be downloaded here. How to install MyEnv is described in the MyEnv Installation Guide.

We build a data warehouse from the General Query Log

Taxonomy upgrade extras: 

The design of a data warehouse differs from relational design. Data warehouses are often designed according to the concept of the star schema.

When building a data warehouse, you usually put the cart before the horse:

FromDual Performance Monitor for MariaDB 2.1.0 has been released

FromDual has the pleasure to announce the release of the new version 2.1.0 of its popular Database Performance Monitor for MariaDB and Galera Cluster fpmmm.

The FromDual Performance Monitor for MariaDB (fpmmm) enables DBAs and System Administrators to monitor and understand what is going on inside their MariaDB database instances and on the machines where the databases reside.

InnoDB Deadlock on SELECT? Not possible! Or Is It?

Taxonomy upgrade extras: 

Introduction

Two points in advance:

Upgrading MariaDB to the pre-latest minor release

Taxonomy upgrade extras: 

Scenario: MariaDB Community Server is release every 3 months. The exact date is not known but it is typically about 2 to 3 weeks after the MySQL/Oracle CPU.

We upgrade regularly customer systems, also their MariaDB Galera Cluster systems. Because we made in the past some very bad experience upgrading production MariaDB Galera Clusters short after release date we became a bit more careful. Our new policy is: We wait a few weeks before installing a new release.

FromDual Ops Center 1.2.1 for MariaDB, MySQL and compatible databases has been released

FromDual has the pleasure to announce the release of the new version 1.2.1 of its popular FromDual Ops Center focmm, a Graphical User Interface (GUI) for MariaDB, MySQL and compatible databases.

The FromDual Ops Center for MariaDB and MySQL (focmm) helps DBA's and System Administrators to better manage their MariaDB and MySQL databases and Galera Cluster farms. Ops Center makes DBA and Admins life easier!

FromDual Ops Center 1.2.0 for MariaDB, MySQL and compatible databases has been released

FromDual has the pleasure to announce the release of the new version 1.2.0 of its popular FromDual Ops Center focmm, a Graphical User Interface (GUI) for MariaDB, MySQL and compatible databases.

The FromDual Ops Center for MariaDB and MySQL (focmm) helps DBA's and System Administrators to better manage their MariaDB and MySQL databases and Galera Cluster farms. Ops Center makes DBA and Admins life easier!

Comparing Optimizer Results

A while ago I read a paper about Optimizer Benchmarks: How Good Are Query Optimizers, Really? by Viktor Leis from Fakultät für Informatik of the Technische Universität München.

Migration of your data from one database to another

Taxonomy upgrade extras: 

Before you consider migrating your data from MySQL to another database you have to know which objects have to be migrated.

With this query you will find the objects to consider:

Linux Container with LXD for focmm unit testing

Currently we are in the testing phase of the next release of our Ops Center (focmm).

FromDual Performance Monitor for MariaDB/MySQL allows SNMP monitoring

The newest release of the Performance Monitor for MariaDB and MySQL v2.0.0 (fpmmm) allows you to monitor your MariaDB and MySQL databases via SNMP (Simple Network Management Protocol).

This feature enables you to report your Database Metrics into Enterprise Monitoring solutions from Microsoft, IBM or into Oracle Cloud Control.

FromDual Performance Monitor for MariaDB 2.0.0 has been released

FromDual has the pleasure to announce the release of the new version 2.0.0 of its popular Database Performance Monitor for MariaDB and Galera Cluster fpmmm.

The FromDual Performance Monitor for MariaDB (fpmmm) enables DBAs and System Administrators to monitor and understand what is going on inside their MariaDB database instances and on the machines where the databases reside.

MariaDB MaxScale Load Balancer with Master/Master Replication

For this how-to we were working with a MariaDB 10.6.5 database server and the newest MariaDB MaxScale 6.3.0 (aka 2.6) load balancer/proxy.

As a starting point we had a working MariaDB Master/Master replication with one active Master (Ma) and one passive Master (Mp). Whereas the passive Master was set to read_only. As a replication user we used the user replication.

Streaming backup with MariaDB Backup

Because the original MariaDB documentation is a little bit sparse about this topic here are some notes about how to do a streaming MariaDB backup:

# mariadb-backup --user=root --backup --parallel=4 --stream=mbstream | ssh root@target_machine "/usr/bin/mbstream -x -C /var/lib/mysql/"

# mariadb-backup --user=root --prepare --target-dir=/var/lib/mysql

Pages

Subscribe to RSS - Shinguz's blog