The Ultimate Guide to High Availability with MariaDB

MariaDB versus MySQL - Compatibility

You are viewing an old version of this article. View the current version here.

Whitepaper: MariaDB vs. MySQL Feature Comparison

  Download Now

Contents

  1. MariaDB is a binary drop in replacement for MySQL
  2. Incompatibilities between MariaDB 5.1 and MySQL 5.1
  3. Incompatibilities between MariaDB 5.2 and MySQL 5.1
  4. Incompatibilities between MariaDB 5.3 and MySQL 5.1 and MariaDB 5.2
  5. Old, unsupported configuration options
  6. Replacing a MySQL RPM ==

MariaDB is a binary drop in replacement for MySQL

For all practical purposes, MariaDB is a binary drop in replacement of the same MySQL version (for example MySQL 5.1 -> MariaDB 5.1, MariaDB 5.2 & MariaDB 5.3 are compatible. MySQL 5.5 will be compatible with MariaDB 5.5). What this means is that:

  • Data and table definition files (.frm) files are binary compatible.
  • All client APIs, protocols and structs are identical.
  • All filenames, binaries, paths, ports, sockets, and etc... should be the same.
  • All MySQL connectors (PHP, Perl, Python, Java, .NET, MyODBC, Ruby, MySQL C connector etc) work unchanged with MariaDB.
  • The mysql-client package also works with MariaDB server.

This means that for most cases, you can just uninstall MySQL and install MariaDB and you are good to go. (No need to convert any datafiles if you use same main version, like 5.1).

We have also done a lot of work on the upgrade scripts and it's now easier to upgrade from MySQL 5.0 to MariaDB 5.1 than from MySQL 5.0 to MySQL 5.1.

That said, MariaDB has a lot of new options, extension, storage engines and bug fixes that are not in MySQL. You can find the feature set for the different MariaDB versions on the What is in the different MariaDB Releases page.

See also MariaDB versus MySQL - Features.

Incompatibilities between MariaDB 5.1 and MySQL 5.1

In some few cases MariaDB has to be incompatible to allow MariaDB to provide more and better information than MySQL.

Here is the list of all known user level incompatibilities you may see when using MariaDB 5.1 instead of MySQL 5.1.

  • The installation package names starts with MariaDB instead of MySQL.
  • Timings may be different as MariaDB is in many cases faster than MySQL.
  • mysqld in MariaDB reads also the [mariadb] sections of your my.cnf files.
  • You can't use a binary only storage engine library with MariaDB if it's not compiled for exactly the same MariaDB version. (This is because the server internal structure THD is different between MySQL and MariaDB. This is common also between different MySQL versions). This should not be a problem as most people don't load new storage engines and MariaDB comes with more more storage engines than MySQL.
  • CHECKSUM TABLE may give different result as MariaDB doesn't ignore NULL's in the columns as MySQL 5.1 does (Future MySQL versions should calculate checksums the same way as MariaDB). You can get the 'old style' checksum in MariaDB by starting mysqld with the --old option. Note however that that the MyISAM and Aria storage engines in MariaDB are using the new checksum internally, so if you are using --old, the CHECKSUM command will be slower as it needs to calculate the checksum row by row.
  • The slow query log has more information about the query, which may be a problem if you have a script which parses the slow query log.
  • MariaDB by default takes a bit more memory than MySQL because we have by default enabled the Aria storage engine for handling internal temporary tables. If you need MariaDB to take very little memory (at the expense of performance), you can set the value of aria_pagecache_buffer_size to 1M (the default is 128M).
  • If you are using new command options, new features of MariaDB or new storage engines, you can't move easily back and forth between MySQL and MariaDB anymore.

Incompatibilities between MariaDB 5.2 and MySQL 5.1

The list is the same as between MariaDB 5.1 and MySQL 5.1.

Incompatibilities between MariaDB 5.3 and MySQL 5.1 and MariaDB 5.2

  • A few error messages related to wrong conversions are different as MariaDB provides more information in the message about what went wrong.
  • Error numbers for MariaDB specific errors has been moved to start from 1900 to not conflict with MySQL errors.
  • Microseconds now works in all contexts; MySQL did, in some contexts, lose the microsecond part from datetime and time.
  • The old --maria- startup options are removed. You should use the --aria- prefix instead. (MariaDB 5.2 supports both --maria- and --aria-)
  • SHOW PROCESSLIST has an extra Progress column which shows progress for some commands. You can disable it by starting mysqld with the --old flag.
  • INFORMATION_SCHEMA.PROCESSLIST has three new columns for progress reporting: STAGE, MAX_STAGE, and PROGRESS.

Old, unsupported configuration options

If you are using any of the following options in your /etc/my.cnf or other my.cnf file you should remove them. This is also true for MySQL 5.1 or newer:

  • skip-bdb

Replacing a MySQL RPM

If you uninstalled a MySQL RPM to install MariaDB, note that the MySQL RPM on uninstall renames /etc/my.cnf to /etc/my.cnf.rpmsave.

After installing MariaDB you should do the following to restore your old configuration options:

mv -vi /etc/my.cnf.rpmsave /etc/my.cnf

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.