FromDual TechFeed (en)

“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 …

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 …

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

InnoDB plugin is enabled.

The InnoDB Log File (innodb_log_file_size) size is possibly too small.
Choosing a too small InnoDB Log File size can have significant write performance impacts.

For further details please consult MariaDB or MySQL documentation.

MySQL (InnoDB, PBXT, NDB, TokuDB) support Foreign Keys to show relatations between tables. Those relations can be enforced through Foreign Key Constraints.

Foreign Key Constraint Errors are always either a bug in your application (which should be fixed) or inconsistencies in your data (which should be fixed) or both (first fix the bug in the application, then clean-up your data).

Foreign Key Constraint Errors can be found with the following command:

SHOW ENGINE INNODB STATUS<br>G

SHOW ENGINE Syntax …

InnoDB Force Recovery (innodb_force_recovery) is enabled. This mode should be used for data recovery purposes only. It prohibits writing to the data.

Please consult MariaDB and MySQL documentation for further details or InnoDB Recovery Modes.

InnoDB Flush Log at Transaction Commit is set to a values != 1. This can lead to a loss of committed transactions in case of a power failure or an unclean shutdown of your database.

You got an InnoDB Deadlock. Please talk to your development department to fix this problem. Deadlocks are an application problem!

If you are using MariaDB/MySQL 5.5 and newer you should use several InnoDB Buffer Pool Instances for performance reasons.
Some rules to size InnoDB Buffer Pool instances are:

• One InnoDB Buffer Pool Instance should be at least 1 Gibyte in size (innodb_buffer_pool_size / innodb_buffer_pool_instances
  >= 1 Gib).
• InnoDB Buffer Pool Instances you can set equal to the number of cores of your machine.

Binlog format MIXED changes the binary log format (ROW or STATEMENT) depending on the queries (deterministic or not). This makes it impossible to define 100% correctly working binary log filter rules.

If the variable flush_time is set to a non-zero value, all tables are closed (and flushed) every flush_time seconds to disk. This can cause unnecessary and high I/O spikes.

For further information consult MariaDB or MySQL documentation.

The number of table definitions (SHOW CREATE TABLE<br>G) that can be stored in the table definition cache (table_definition_cache). If you have a large number of tables (
> 400) in your database instance, you should consider a larger table definition cache to increase your database throughput and decrease your query latency.
The command SELECT COUNT(*) FROM information_schema.tables; shows you how many tables and thus table definitions you have. The global status Open_table_definitions is the …

The Table Open Cache (table_open_cache or old name table_cache) is a cache to store file handles for all threads. The actual value of cache entries can be seen with the global status of open tables (Open_tables).
Increasing table_open_cache increases the number of file descriptors (open_files_limit) that MySQL requires.
You can check whether you need to increase the Table Open Cache by checking Open_tables and Opened_tables. If the value of Opened_tables is large and you do not use FLUSH TABLES often (which …

If and how to use Galera Cluster for MySQL has been described already by many others…

In this article we focus on the question: When is Galera Cluster NOT the right solution for you?

These thoughts also apply to Percona XtraDB Cluster (PXC) and MariaDB Galera Cluster.

• Galera Cluster currently supports only InnoDB (and TokuDB?) tables properly. You should avoid to use MyISAM or MEMORY tables. Otherwise data consistency is not guaranteed.
• It is wise to use Galera Cluster only if your software vendor …

Database migration tools

A generic database migration tool to MySQL and MariaDB is the MySQL Workbench.

An alternative is the old MySQL Migration Tool Kit.

Recommended by MariaDB Foundation: Sqlines.

Adabas D to MariaDB migration

Project migrating an Adabas D database to MariaDB is in progress. More information are expected before end of 2015.

Oracle to MySQL or MariaDB migration

Data Migration Tools

• MyOraDump is a tool to export data from an Oracle database to MySQL or MariaDB. …

The InnoDB Flush Method has changed. This can have an impact on InnoDB write Performance.

The press, be it the general daily newspaper or the computer magazines, is currently informing the public about an upcoming leap second, which will be taken in the night from June 30 to July 1 at 00:00:00 UTC. While we Europeans will enjoy our well-deserved sleep then, this will be at 5 PM (17:00) local time on June 30 for Califormia people, and during the morning of July 1 for people in China, Japan, Korea, or Australia. (Other countries not mentioned for the sake of brevity.) This is different from last …

The following example should work for you:

CREATE TABLE spec(txt VARCHAR(255));

INSERT INTO spec values (‘bla\tbla\nbla’); INSERT INTO spec values (’\n\tb)’); INSERT INTO spec values (‘abc\n\tb)xyz’);

SELECT * FROM spec;

SELECT * FROM spec WHERE txt LIKE ‘\n\tb)’; SELECT * FROM spec WHERE txt LIKE ‘%\n\tb)%’; SELECT * FROM spec WHERE txt REGEXP ‘^\n\tb)$’; SELECT * FROM spec WHERE txt REGEXP ‘\n\tb)’;