MariaDB versus MySQL
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.3are compatible. MySQL 5.5 will be compatible withMariaDB 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.
- There are some installation issues with PHP5 that you should be aware of (a bug in how the old PHP5 client checks library compatibility).
mysql-clientpackage also works with MariaDB server.
- The shared client library is binary compatible with MySQL's client library.
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 do monthly merges with the MySQL code base to ensure we keep up our compatibility and get any and all features and bug fixes Oracle adds.
We have also done a lot of work on the upgrade scripts to the point where it is 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.
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 storage engines than MySQL.
CHECKSUM TABLEmay 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
--oldoption. Note however that that the MyISAM and Aria storage engines in MariaDB are using the new checksum internally, so if you are using
CHECKSUMcommand 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
1M(the default is
- 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, with one addition:
SQL_MODEvalue was added:
IGNORE_BAD_TABLE_OPTIONS. If it is not set, using a table, field, or index attribute (option) that is not supported by the chosen storage engine will cause an error. This change might cause warnings in the error log about incorrectly defined tables from the
mysqldatabase, fix that with
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.
- UNIX_TIMESTAMP(constant-date-string) returns in MariaDB a timestamp with 6 decimals while MySQL returns it without a decimal. This can cause a problem if you are using UNIX_TIMESTAMP() as a partitioning function. You can fix this by using FLOOR(UNIX_TIMESTAMP(..)) or changing the date string to a date number, like 20080101000000.
- MariaDB does more strict checking of date, datetime and timestamp values. For exampleUNIX_TIMESTAMP('x') now returns NULL instead of 0.
- The old
--maria-startup options are removed. You should use the
--aria-prefix instead. (MariaDB 5.2 supports both
SHOW PROCESSLISThas an extra
Progresscolumn which shows progress for some commands. You can disable it by starting
INFORMATION_SCHEMA.PROCESSLISThas three new columns for progress reporting:
- Long comments which start with
- If you use
max_user_connections=0(which means any number of connections) when starting mysqld, you can't change the global variable anymore while mysqld remains running. This is because when mysqld is started with
max_user_connections=0it does not allocate counting structures (which also involve a mutex for each connection). This would lead to wrong counters if you later changed the variable. If you want to be able to change this variable at runtime, set it to a high value at startup.
- You can set
max_user_connections(both the global variable and the
-1to stop users from connecting to the server. The global
max_user_connectionsvariable does not affect users with the
- The IGNORE directive does not ignore all errors (like fatal errors), only things that are safe to ignore.
Incompatibilities between MariaDB 5.5 andMariaDB 5.3
Percona, the provider of XtraDB, does not provided all earlier features of XtraDB in the 5.5 code base. Because of that, MariaDB 5.5 can't provide them either.
XtraDB options missing in 5.5
The following options are not supported by XtraDB 5.5. If you are using them in any of your my.cnf files, you should remove them before upgrading to 5.5.
- innodb_adaptive_checkpoint ; Use
- innodb_auto_lru_dump ;Use
- innodb_blocking_lru_restore ; Use
- innodb_expand_import ; Use
- innodb_extra_rsegments ; Use
- innodb_pass_corrupt_table ; Use
XtraDB options that has changed default value
|Option||Old value||New value|
New options in XtraDB 5.5
The following new options has been added to XtraDB / InnoDB in 5.5. (Listed here just to have all XtraDB information in the same place)
Incompatibilities between MariaDB 5.5 andMariaDB 5.3 and MySQL 5.5
INSERT IGNOREalso gives warnings for duplicate key errors.
Incompatibilities between MariaDB 10.0 and MySQL 5.6
- All MySQL binaries (
mysqld, myisamchk etc.) give a warning if one uses a prefix of an option (such as
--big-tables). MariaDB binaries work in the same way as most other Unix commands and don't give warnings when using an unique prefix.
Old, unsupported configuration options
If you are using any of the following options in your
my.cnffile you should remove them. This is also true for MySQL 5.1 or newer:
Replacing a MySQL RPM
If you uninstalled a MySQL RPM to install MariaDB, note that the MySQL RPM on uninstall renames
After installing MariaDB you should do the following to restore your old configuration options:
mv -vi /etc/my.cnf.rpmsave /etc/my.cnf
Incompatibilities between MariaDB and MySQL-Proxy
A MySQL client API is able to connect to MariaDB using MySQL-Proxy but a MariaDB client API will receive progress reporting informations that MySQL-Proxy does not implement, to get full compatibility in all case just disable progress reporting on the client or server side.
More Storage Engines
In addition to the standard MyISAM, BLACKHOLE, CSV, MEMORY, ARCHIVE, and MERGE storage engines, the following are also included with MariaDB Source and Binary packages:
- XtraDB (drop-in replacement for InnoDB)
- PBXT (In MariaDB 5.1, MariaDB 5.2, and MariaDB 5.3. Disabled in MariaDB 5.5.)
- FederatedX (drop-in replacement for Federated)
- OQGRAPH (In MariaDB 5.1, MariaDB 5.2, and MariaDB 5.3. Disabled in MariaDB 5.5.) — new in 5.2
- SphinxSE — new in 5.2
- IBMDB2I. Oracle removed this in MySQL 5.1.55 but we have kept the code in MariaDB until the version 5.5.
- TokuDB in MariaDB 5.5 and MariaDB 10.0
- Cassandra in MariaDB 10.0
- We are working on adding other NoSQL storage engines into MariaDB.
- CONNECT in MariaDB 10.0
- SEQUENCE in MariaDB 10.0
- Spider in MariaDB 10.0
- A lot of optimizer enhancements in MariaDB 5.3. Sub queries are now finally usable! The complete list and a comparison with MySQL is here. A benchmark can be found here.
- Faster and safer replication: Group commit for the binary log. This makes many setups that uses replication and lot's of updates more than 2x times faster.
- Improvements for Innodb asynchronous IO subsystem on Windows.
- Index for MEMORY(HEAP) engine is faster. According to a simple test, 24 % faster on INSERT for integer index and 60 % faster for index on a CHAR(20) column.
- CHECKSUM TABLE is faster.
- We improved the performance of character set conversions (and removed conversions when they were not really needed). Overall speed improvements is 1-5 % (according to sql-bench) but can be higher for big results sets with all characters between 0x00-0x7f.
- Pool of Threads in MariaDB 5.1 and even better in MariaDB 5.5. This allows to run MariaDB with 200,000+ connections and with a notable speed improvement when using many connections.
- There are some improvements to DBUG code to make its execution faster when debug is compiled in but not used.
- Our use of the Aria storage engine enables faster complex queries (queries which normally use disk-based temporary tables). The Aria storage engine is used for internal temporary tables, which should give a speedup when doing complex selects. Aria is usually faster for temporary tables when compared to MyISAM because Aria caches row data in memory and normally doesn't have to write the temporary rows to disk.
- The test suite is extend and now runs much faster than before even though it tests more things.
Extensions & new features
We've added a lot of new features to MariaDB. If a patch or feature is useful, safe, and stable — we make every effort to include it in MariaDB. The most notable features are:
- Microseconds in MariaDB — new in 5.3
- Microsecond Precision in Processlist
- Table Elimination
- Virtual Columns — new in 5.2
- Extended User Statistics — new in 5.2
- Segmented Key Cache for MyISAM — new in 5.2
- KILL all queries for a user — new in 5.3
- Pluggable Authentication — new in 5.2
- Storage-engine-specific CREATE TABLE — new in 5.2
- Enhancements to INFORMATION SCHEMA.PLUGINS table — new in 5.2
- Group commit for the binary log. This makes replication notably faster! — new in 5.3
mysqlbinlog option to change the used database — new in 5.2
- Progress reporting for
LOAD DATA INFILE. — new in 5.3
- Faster joins and subqueries. — new in 5.3
- HandlerSocket and faster HANDLER calls. — new in 5.3
- Dynamic Columns support. — new in 5.3
- GIS Functionality — new in 5.3
- Multi-source replication. — new in 10.0
- SHOW EXPLAIN gives the EXPLAIN plan for a query running in another thread. — new in 10.0
For a full list, please see features for each release
- More tests in the test suite.
- Bugs in tests fixed.
- Test builds with different configure options to get better feature testing.
- Remove invalid tests. (e.g. don't test feature ''X'' if that feature is not in the tested build)
Fewer warnings and bugs
- Bugs are bad. Fix as many bugs as possible and try to not introduce new ones.
- Compiler warnings are also bad. Eliminate as many compiler warnings as possible.
Truly Open Source
- All code in MariaDB is released under GPL, LPGL or BSD. MariaDB does not have closed source modules like the ones that can be found in MySQL Enterprise Edition. In fact, all the closed source features in MySQL 5.5 Enterprise Edition are found in the MariaDB open source version.
- MariaDB includes test cases for all fixed bugs. Oracle doesn't provide test cases for new bugs fixed in MySQL 5.5.
- All bugs and development plans are public.
- MariaDB is developed by the community in true open source spirit.