Migration

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:

SELECT TABLE_SCHEMA AS `SCHEMA`, IF(TABLE_TYPE = 'BASE TABLE', 'TABLE', TABLE_TYPE) AS OBJECT_TYPE, TABLE_NAME AS `OBJECT_NAME`
     , IFNULL(ENGINE, '') AS ENGINE, IFNULL(TABLE_ROWS, '') AS `ROWS`
     , IFNULL(DATA_LENGTH, '') AS DATA_SIZE, IFNULL(INDEX_LENGTH, '') AS INDEX_SIZE
  FROM information_schema.tables
 WHERE TABLE_SCHEMA …

Recently we migrated a database system from MySQL 5.7 to MariaDB 10.3. Everything went fine so far just the following message started to pop-up in the MariaDB Error Log File with the severity Note:

InnoDB: page_cleaner: 1000ms intended loop took 4674ms. The settings might not be optimal. (flushed=102 and evicted=0, during the time.)

I remember that this message also appeared in earlier MySQL 5.7 releases but somehow disappeared in later releases. I assume MySQL has just disabled the Note?

You can find …

Up to version 5.5 MariaDB and MySQL can be considered as “the same” databases. The official wording at those times was “drop-in-replacement”. But now we are a few years later and times and features changed. Also the official wording has slightly changed to just “compatible”.
FromDual recommends that you consider MariaDB 10.3 and MySQL 8.0 as completely different database products (with some common roots) nowadays. Thus you should work and act accordingly.

Because more and …

Table of Contents

• General findings
• Migration from MySQL 5.x to MariaDB 5.5
• Migration from MySQL 5.0 to MariaDB 10.3
• Migration from MariaDB 10.0 to Percona Server 5.6
• Migration from MySQL/Percona 5.6 to MariaDB 10.0
• Migration from MySQL 5.7 to MariaDB 10.2
• Migration from MySQL 5.7 to MariaDB 10.3
• Migration from MySQL 5.7 to MariaDB 10.4
• Migration from Percona XtraDB Cluster 5.5 to MariaDB Galera Cluster 10.4
• Migration from MySQL 5.7 to MariaDB 10.5
• Migration from MySQL 5.7 to MariaDB 10.6
• Migration from …

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 …

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

This week, we did some migrations from MariaDB 10.0 to Percona Server 5.6 at the IT department of a big German bank.

We were perfectly aware that since version 10.0 the MariaDB code base started to diverge slightly away from the MySQL and Percona Server code base which are still pretty close to each other.

Because of the Percona Server option enforce_storage_engine we wanted to do this migration.

We stopped the MariaDB 10.0 server, replaced the binaries by the Percona Server 5.6 binaries, and started the …

Introduction

Galera is a synchronous Multi-Master Replication for MySQL. It is therefore in competition with several other MySQL architectures:

• Master-Master Replication with MySQL
• MySQL Cluster
• The non-open source product called Schooner

Very often they can be easily replaced by Galera’s synchronous Multi-Master Replication for MySQL.

All those products have some advantages and disadvantages. Very often MySQL Master-Master Replication is used in the field because of its simplicity to set-up. But …

Table of Contents

• Reasons to Upgrade MariaDB or MySQL
• How to Upgrade to MariaDB or MySQL
• MariaDB and MySQL Reserved Keywords
• MariaDB and MySQL Problems we hit in real life
  • Upgrade MySQL 4.1 to MySQL 5.1
  • Upgrade MySQL 4.1 to MySQL 5.6
  • Upgrade MySQL 5.0 to MySQL 5.1
  • Upgrade MySQL 5.0 to MySQL 5.6
  • Upgrade MySQL 5.0 to MariaDB 10.3
  • Upgrade MySQL 5.1 to MySQL 5.5
  • Upgrade MySQL 5.5 to MySQL 5.6
  • Upgrade MySQL 5.6 to MySQL 5.7
  • Upgrade MySQL 5.7.12 to MySQL 5.7.21
  • Upgrade MySQL 5.7 to MySQL 8.0
  • Upgrade MariaDB 5.5 …

Table of Contents

• Result set with temporary sequence
• Determination of optimal length of prefixed indexes
• Using MySQL keywords in table or columm names
• Missing Primary Key Index
• Problems while installing a MySQL 5.5 database
• InnoDB AUTO_INCREMENT at 2^nd position

Result set with temporary sequence

Sometimes you would like to have a result set with something like a rownum. You can do this at least in the following two ways:

a) with a TEMPORARY MEMORY table:

CREATE TEMPORARY TABLE mem (
    seq  INT NOT NULL …

Applications occasionally require redesign. However, redesigning an application cannot be done in one step because the application is distributed or several versions of applications must be supported. MySQL 5.0 provides the necessary means to stealthy migrate your data. In a short overview let’s look at what we plan to do: Stealthy Migration (PDF 98.7 kByte).