Backup

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

The new FromDual Backup and Recovery Manager can be downloaded from here. The FromDual Repositories were updated. How to install and use the Backup and Recovery Manager is described in FromDual Backup and Recovery Manager (brman) installation guide.

In the inconceivable case that you find a bug in the FromDual Backup and Recovery Manager please send us an …

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

The new FromDual Backup and Recovery Manager can be downloaded from here. The FromDual Repositories were updated. How to install and use the Backup and Recovery Manager is described in FromDual Backup and Recovery Manager (brman) installation guide.

In the inconceivable case that you find a bug in the FromDual Backup and Recovery Manager please report it to the …

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

The new FromDual Backup and Recovery Manager can be downloaded from here. The FromDual Repositories were updated. How to install and use the Backup and Recovery Manager is described in FromDual Backup and Recovery Manager (brman) installation guide.

In the inconceivable case that you find a bug in the FromDual Backup and Recovery Manager please report it to the …

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

The new FromDual Backup and Recovery Manager can be downloaded from here. The FromDual Repositories were updated. How to install and use the Backup and Recovery Manager is described in FromDual Backup and Recovery Manager (brman) installation guide.

In the inconceivable case that you find a bug in the FromDual Backup and Recovery Manager please report it to the …

Nearly everybody does backups. But nobody needs backups! What everybody wants and needs is a working restore not a working backup…

So how to make sure that your backup is working for the restore? There are a few things you can do already during your backup:

• Check that your backup was running fine. For example by checking the return code of your backup.
• Check the runtime of your backup. If the runtime of your backup significantly changed, it is worth to have a closer look at the backup.
• Check the …

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

The new FromDual Backup and Recovery Manager can be downloaded from here. The FromDual Repositories were updated. How to install and use the Backup and Recovery Manager is described in FromDual Backup and Recovery Manager (brman) installation guide.

In the inconceivable case that you find a bug in the FromDual Backup and Recovery Manager please report it to the …

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

The new FromDual Backup and Recovery Manager can be downloaded from here. The FromDual Repositories were updated. How to install and use the Backup and Recovery Manager is described in FromDual Backup and Recovery Manager (brman) installation guide.

In the inconceivable case that you find a bug in the FromDual Backup and Recovery Manager please report it to the …

InnoDB tries to keep pages in Buffer Pool to be fast. If a page is changed by a DML statement (INSERT, UPDATE, DELETE) this change will be done in InnoDB Buffer Pool and not directly on disk. But those changed InnoDB pages residing in InnoDB Buffer Pool must be flushed sooner or later to disk to become persistent. This is done by the InnoDB background writer thread(s) (default 4).

InnoDB flushes the dirty pages with a pace of innodb_io_capactiy (default 200) pages/s. This variable should be set depending on …

In my former Blog Post Partial Table or Schema restore from mariabackup full backup we worked out the basics of a partial restore of a table into a MariaDB database instance.

An now we use this know-how to try the same procedure on a Galera Cluster.

The backup is done in the exact same way as described in the mentioned article. We can even use the backup made there.

For the restore we use the following procedure:

Prepare and Restore a table

# BACKUPDIR="/home/mysql/bck/qamariadb105/daily"
# …

For me it was for a long time not clear if a mariabackup full backup can be used to do partial table or schema restores. Now we faced this challenge with a customer. So time to try it out…

This test was made with MariaDB 10.5.5. So it may not work with some older MariaDB releases…

Backup

Because I do not know during the backup if I need a full or a partial restore I always want to do a full mariabackup backup!

The full backup can be done as normal but the prepare should not be done yet during …

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

The new FromDual Backup and Recovery Manager can be downloaded from here. The FromDual Repositories were updated. How to install and use the Backup and Recovery Manager is describe in FromDual Backup and Recovery Manager (brman) installation guide.

In the inconceivable case that you find a bug in the FromDual Backup and Recovery Manager please report it to the …

FromDual has the pleasure to announce the release of the new version 0.9.2 of its popular FromDual Ops Center for MariaDB and MySQL focmm.

The FromDual Ops Center for MariaDB and MySQL (focmm) helps DBA’s and System Administrators to manage MariaDB and MySQL database farms. Ops Center makes DBA and Admins life easier!

The main task of Ops Center is to support you in your daily MySQL and MariaDB operation tasks. More information about FromDual Ops Center you can find here.

Download

The new FromDual Ops …

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

The new FromDual Backup and Recovery Manager can be downloaded from here. The FromDual Repositories were updated. How to install and use the Backup and Recovery Manager is describe in FromDual Backup and Recovery Manager (brman) installation guide.

In the inconceivable case that you find a bug in the FromDual Backup and Recovery Manager please report it to the …

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

The new FromDual Backup and Recovery Manager can be downloaded from here. The FromDual Repositories were updated. How to install and use the Backup and Recovery Manager is describe in FromDual Backup and Recovery Manager (brman) installation guide.

In the inconceivable case that you find a bug in the FromDual Backup and Recovery Manager please report it to the …

It is Saturday night at 23:19. Time to go to bed after a hard migration day. Just a last clean-up query before finishing: Tap tap tap. Enter! - Oops!

SQL> UPDATE prospect_lists_prospects SET prospect_list_id = '73ae6cca-7b34-c4a3-5500-5d0e2674dbb6';
Query OK, 4686 rows affected (0.21 sec)
Rows matched: 5666  Changed: 4686  Warnings: 0

A verification query to make sure I am in the mess:

SQL> SELECT prospect_list_id, COUNT(*) FROM prospect_lists_prospects GROUP BY prospect_list_id; …

Some applications have the bad behaviour to CREATE or DROP tables while our FromDual Backup Manager (bman) backup is running.

This leads to the following bman error message:

/opt/mysql/product/5.7.26/bin/mysqldump --user=dba --host=migzm96i --port=3306 --all-databases --quick --single-transaction --flush-logs --triggers --routines --hex-blob --events | tee >(md5sum --binary >/tmp/checksum.23357.md5) | gzip -1
to Destination: /var/mysql/dumps/mysql96i/daily/bck_mysql96i_full_2019-05-22_06-50-01.sql.gz …

FromDual has the pleasure to announce the release of the new version 0.9.1 of its popular FromDual Ops Center for MariaDB and MySQL focmm.

The FromDual Ops Center for MariaDB and MySQL (focmm) helps DBA’s and System Administrators to manage MariaDB and MySQL database farms. Ops Center makes DBA and Admins life easier!

The main task of Ops Center is to support you in your daily MySQL and MariaDB operation tasks. More information about FromDual Ops Center you can find here.

Download

The new FromDual Ops …

Caution: We have introduced an evil bug which prohibits installation of focmm. Sorry! Somehow it did pass our QA. To fix this bug update file lib/Upgrade.inc on Line 1965 as follows:

- $sql = sprintf("REPLACE INTO `focmm_configuration` (`key`, `value`) VALUES ('%s', '%s'), ('%s', '%s'), ('%s', '%s')"
+ $sql = sprintf("REPLACE INTO `focmm_configuration` (`key`, `value`) VALUES ('%s', '%s'), ('%s', '%s')"

In the meanwhile we prepare a new release.

FromDual has the pleasure to announce …

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

The new FromDual Backup and Recovery Manager can be downloaded from here. How to install and use the Backup and Recovery Manager is describe in FromDual Backup and Recovery Manager (brman) installation guide.

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

MySQL Enterprise Backup (MEB) is a bit limited related to support of older MySQL versions. So you should consider the following release matrix:

* MySQL Enterprise Backup 8.0.15 only supports MySQL 8.0.15. For earlier versions of MySQL 8.0, use the MySQL Enterprise Backup version with the same version number as the server.

MySQL Enterprise Backup is available for …

As we already stated in earlier articles in this blog
[1 and 2
] it is a good idea to use NULL values properly in MariaDB and MySQL.

One of my Mantras in MariaDB performance tuning is: Smaller tables lead to faster queries! One consequence out of this is to store NULL values instead of some dummy values into the columns if the value is not known (NULL: undefined/unknown).

To show how this helps related to space used by a table we created a little example:

CREATE TABLE big_null1 (
  id INT UNSIGNED NOT NULL …

A while ago we released our FromDual Backup and Recovery Manager (brman) 2.0.0 for MariaDB and MySQL. So what are the new cool features of this new release?

First of all brman 2.0.0 is compatible with MariaDB 10.3 and MySQL 8.0:

shell> bman --target=brman:secret@127.0.0.1:3318 --type=full --mode=logical --policy=daily

Reading configuration from /etc/mysql/my.cnf
Reading configuration from /home/mysql/.my.cnf
No bman configuration file.

Command line: /home/mysql/product/brman-2.0.0/bin/bman.php …

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

The new FromDual Backup and Recovery Manager can be downloaded from here. How to install and use the Backup and Recovery Manager is describe in FromDual Backup and Recovery Manager (brman) installation guide.

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

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:

Differential Incremental 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 …

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](mailto:contact@fromdual.com?Subject=Bug report for fromdual_brman).

Any feedback, statements and testimonials are welcome as well! …

FromDual has the pleasure to announce the release of the new version 1.2.4 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 Backup and Recovery Manager please report it to our Bugtracker or just send us an [email](mailto:contact@fromdual.com?Subject=Bug report for fromdual_brman).

Any feedback, statements and testimonials are welcome as well! Please send …

FromDual has the pleasure to announce the release of the new version 1.2.3 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 Backup and Recovery Manager please report it to our Bugtracker or just send us an [email](mailto:contact@fromdual.com?Subject=Bug report for fromdual_brman).

Any feedback, statements and testimonials are welcome as well! Please send …

FromDual has the pleasure to announce the release of the new version 0.3 of the FromDual Ops Center for MySQL and MariaDB.

The FromDual Ops Center for MySQL and MariaDB (focmm) is an application for DBA’s and system administrators to manage MySQL and MariaDB database farms.

The main task of Ops Center is to support you in your daily MySQL and MariaDB operation tasks.

More information about FromDual Ops Center you can find here.

Download

The new FromDual Ops Center can be downloaded here.

In the …

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 1.2.2 of the popular Backup and Recovery Manager for MySQL and MariaDB (fromdual_bman).

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

In the inconceivable case that you find a bug in the Backup and Recovery Manager please report it to our Bugtracker.

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

Upgrade from 1.2.x to 1.2.2

# cd ${HOME}/product
# tar xf …

You find many different possibilities how to do a MariaDB/MySQL backup with mariadb-dump/mysqldump. But which one is the correct one?

MySQL Enterprise Backup (MEB) has the capability to make real incremental (differential and cumulative?) backups. The actual releases are quite cool and you should really look at it…

Unfortunately the original MySQL documentation is much too complicated for my simple mind. So I did some testing and simplified it a bit for our customers…

If you want to dive into the original documentation please look here: Making an Incremental Backup .

If you want to use MySQL Enterprise Backup please let us …

MySQL provides some great enterprise features beside the MySQL Server. The ones we are asked the most at customers are:

• MySQL Enterprise Backup (MEB)
• MySQL Enterprise Monitor (MEM) and
• MySQL Enterprise Workbench (MWB)

MySQL Enterprise Backup (MEB)

MySQL Enterprise Backup (MEB) is an alternative to the mysqldump backup utility. Its big advantage is its fast backup but even faster restore performance. This is a must for all MySQL users having bigger databases than let’s say 10 to 20 Gigabytes and/or …

Sometimes we face the situation where we have a full MySQL database backup done with mysqldump and then we have to restore and recover just one single table out of our huge mysqldump file.
Further our mysqldump backup was taken hours ago so we want to recover all the changes on that table since our backup was taken up to the end.

In this blog article we cover all the steps needed to achieve this goal for MySQL and MariaDB.

Recommendation: It is recommended to do theses steps on a testing system and then …

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

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

In the inconceivable case that you find a bug in the Backup and Recovery Manager please report it to our Bugtracker.

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

Upgrade from 1.2.0 to 1.2.1

# cd ${HOME}/product
# tar xf …

We recently run into some troubles with max_allowed_packet size problems during backups with the FromDual Backup/Recovery Manager and thus I investigated a bit more in the symptoms of such problems.

Read more about: max_allowed_packet.

A general rule for max_allowed_packet size to avoid problems is: All clients and the server should have set the same value for max_allowed_packet size!

I prepared some data for the test which looked as follows:

mysql> SELECT id, LEFT(data, 30), LENGTH(data), ts FROM test; …

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

You can download MyEnv from here.

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

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

Upgrade from 1.1.1 to 1.1.2

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

Precaution: Before you try this out on your production system do a BACKUP first! FromDual Backup Manager can help you with this.

Situation

A MySQL user has delete its InnoDB table files for example like this:

shell> rm -f $datadir/test/test.*

Analysis

We do some analysis first:

mysql> DROP TABLE test;
ERROR 1051 (42S02): Unknown table 'test'

mysql> CREATE TABLE test (id INT) ENGINE = InnoDB;
ERROR 1050 (42S01): Table '`test`.`test`' already exists

The MySQL error log shows us the following …

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

The majority of improvements happened in the MySQL Backup Manager (mysql_bman) where we added a backup catalog.

You can download MyEnv from here.

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

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

Upgrade …

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

The majority of improvements happened in the MySQL Backup Manager (mysql_bman) where we added a backup catalog.

You can download MyEnv from here.

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

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

Upgrade …

FromDual tools provide valuable additional functionality which facilitate and optimize daily operations of your MySQL databases. Since our last newsletter a lot of things have changed in the FromDual tools.

To the MySQL Environment (MyEnv) numerous improvements and suggestions of our customers were added. The most important changes were introduced to the MySQL Backup Manager (mysql_bman).

With the MySQL Ops Center we meet the wishes of our numerous customers, which desire a graphical user interface for …

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

The majority of improvements happened on the MySQL Backup Manager (mysql_bman) utility.

You can download MyEnv from here.

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

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

Upgrade from 1.0.x to 1.0.5

# cd …

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

The majority of improvements happened on the MySQL Backup Manager (mysql_bman) utility.

You can download MyEnv from here.

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

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

Upgrade from 1.0.x to 1.0.4

# cd …

The FromDual Ops Center for MariaDB and MySQL (focmm) is a browser-based Graphical User Interface (GUI) to operate and manage your MariaDB and MySQL database farms (2 - 99 instances) more easily.
If further provides tools to implement your own Database as a Service (DBaaS) infrastructure.

FromDual Ops Center covers the full life-cycle of a database:

• Installation of your database from different Repositories.
• Configuration of your database instance.
• Track configuration changes of your database instance. …

Table of Contents

• Introduction
• Prerequisites
• Full Backup
  • Create Full Backup
  • Prepare Full Backup
  • Restore Full Backup
  • Prepare slave from full backup
  • Prepare GTID slave from full backup
  • Advantages / Disadvantages
  • Important Hints
• Incremental Backup
  • Create Incremental Backup
  • Prepare Incremental Backup
  • Restore Incremental Backup
  • Advantages / Disadvantages
  • Important Hints
  • Differential Backup
• Partial Backup
  • Create Partial Backup
  • Prepare Partial Backup
  • Restore Partial Backup
  • Advantages / Disadvantages …

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.

When a MySQL Slave is set-up with mysqldump you have 2 possibilities:

• You dump into a file and then load the data into the Slave with the mysql client utility.
• You dump directly into the mysql client utility.

The first possibility has the advantage that you can start the load again if it failed. You can look into the file (and do some changes if needed).
The second possibility has the advantage that you do not need disk space and that it is possibly faster. But when the load fails you have to start from …

Recently I found in a forum the following request for help:

My MySQL instance crashed because of free disk space fault. I saw in /var/lib/mysql all the files: ibdata1, ib_logfile* and all the folders containing frm files. Well, when i solved the problem and run successfully the instance, some databases disappeared. One of those is the most important, and i don’t know how many tables had and their structures. Is there any way for recover the entire lost database (structure and data) only having the …

Abstract: In this article we have a look at the compression options of common zipping tools and its impact on the size of the compressed files and the compression time. Further we look at the new parallel zip tools which make use of several cores.

Start with a backup first

From time to time I get into the situation where I have to compress some database files. This happens usually when I have to do some recovery work on customers systems. Our rule number 1 before starting with a recovery is: Do a file …

Backup is for sissies! Let’s have a look what we can do when we are not a sissy…

First of all: Your life is much easier when you have a proper backup process implemented and verified the restore procedure of your MySQL database.

But what if you have no backup in place and did a DROP TABLE. What shall we do?

We assume our data we just dropped are located on the following device:

# export IMAGE=/dev/hda1

First of all, power off your server. This avoids that the operating system writes data down …

Controlling developers is like herding cats.
Kevin Loney, Oracle DBA Handbook

Oh no, it’s not. It’s much harder than that!
Bruce Pihlamae, long-term Oracle DBA

Do not assume!
Unknown IT specialist

Backups ist was für Warmduscher!
(engl. Backup is for sissies!)
Unkown DBA

Yesterday (the DBA version)

Yesterday,
All those backups seemed a waste of pay.
Now my database has gone away.
Oh I believe in yesterday.

Suddenly,
There’s not half the files there used to be,
And there’s a …

Table of Contents

• Search with special characters
• Why is InnoDB disabled?
• How to find MySQL system information?
• What is the difference between MySQL certified server and community server?
• MySQL monitoring
• MySQL backup
• Corrupt MyISAM table
• How to compile MySQL
• Test restore procedure
• Reset a MySQL user password
• Reset the MySQL root user password
• How to enable the InnoDB plugin
• Storage Engines shipped with MariaDB / MySQL
• Compiling MySQL Cluster ndb-test fails
• NDB information schema does not show up
• Hyper …

Today a customer with corrupted data files showed up. When we enquired a bit more he told us that he had a broken I/O controller. This is one of the worst things which can happen to you!

The reason is the following: When a I/O controller starts to die it often does not happen immediately. The controller dies slowly producing more and more corrupt data. When you just write data without checking or reading them it can take days or even weeks until you discover the problem.

But the nasty thing is, that even …

Recently the question came up if it is faster to restore a MySQL cluster when all nodes are up or only ONE node from each node group during restore.

The answer from our gurus was: All nodes up during restore! I wanted to find out why. So I set up the following cluster and started to measure:

MySQL Cluster set up

MySQL Cluster backup

The backup is not that interesting. But I made the drawing for possible future use :-) :

MySQL Cluster restore

For the restore there are 4 different ways thinkable:

• Restore …