热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

MariaDB与MySQL兼容性的对比分析

MariaDB是MySQL代码级量身定制的替代者鉴于所有应用,MariaDB都是MySQL的代码级量身定制的替代者,相应的版本可以直接替换(如MySQL5.1-MariaDB5.1,MariaDB5.2MariaDB5.3也是兼容的。MySQL5.5将与兼容),具体是:数据及表的定义文件(.frm)在代
MariaDB是MySQL代码级量身定制的替代者

  鉴于所有应用,MariaDB都是MySQL的代码级量身定制的替代者,相应的版本可以直接替换(如MySQL 5.1 -> MariaDB 5.1, MariaDB 5.2 & MariaDB 5.3也是兼容的。MySQL 5.5将与兼容),具体是:

数据及表的定义文件(.frm)在代码级兼容;

所有客户端的API、协议和结构都是相同的;

所有文件件名、二进制文件、路径、端口、套接字等……全都是一样的;

所有的MySQL与其他语言(PHP、Perl、Python、Java、.NET、MyODBC、Rub、MySQL C……)的连接文件无需任何改动,在MariaDB就可工作;

但有些PHP5安装要点你要知道(旧的PHP5客户端库兼容性检查程序存在bug);

mysql-client程序也可在MariaDB服务器上工作。

  意思也就是,在大多数情况下,你只要卸载MySQL后,安装MariaDB后就可工作(在使用相同的主版本(如5.1版)的情况下,不需任何数据文件的转换).

  我们每月都会将MySQL基本代码编译入MariaDB,从而保证MariaDB与Oracle添加的任何补丁和更新的MySQL相兼容。

  同时我们为了容易地从从MySQL 5.0升级到MariaDB 5.1,我们在升级脚本上也做了大量的工作。

  也就是说:相对于MySQL,MariaDB有更多新选项、扩展、存储引擎及漏洞修复,你可以在MariaDB的不同之外页上查看详细。

MariaDB 5.1与MySQL 5.1不兼容部分

  为了提供更多、更好的性能,MariaDB在有些地方与MySQL难免有所不兼容。

  下面列出的是从用户使用角度,你可能看到的MariaDB 5.1与 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 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.

MariaDB 5.2与MySQL 5.1不兼容部分

The list is the same as between MariaDB 5.1 and MySQL 5.1, with one addition:

New SQL_MODE value 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 mysql database, fix that with mysql_upgrade.

For all practical purposes, MariaDB 5.2 is a drop in replacement for MariaDB 5.1 and MySQL 5.1.

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.

MariaDB does more strict checking of date, datetime and timestamp values. For example unix_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 --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.

Long comments which start with /*M! or /*M!##### are executed.

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=0 it 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 GRANT option) to -1 to stop users from connecting to the server. The global max_user_connections variable does not affect users with the SUPER privilege.

The IGNORE directive does not ignore all errors (like fatal errors), only things that are safe to ignore.

XtraDB

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_adaptive_flushing_method instead.

innodb_auto_lru_dump ;Use innodb_buffer_pool_restore_at_startup instead.

innodb_blocking_lru_restore ; Use innodb_blocking_buffer_pool_restore instead.

innodb_enable_unsafe_group_commit

innodb_expand_import ; Use innodb_import_table_from_xtrabackup instead.

innodb_extra_rsegments ; Use innodb_rollback_segment instead.

innodb_extra_undoslots

innodb_fast_recovery

innodb_flush_log_at_trx_commit_session

innodb_overwrite_relay_log_info

innodb_pass_corrupt_table ; Use innodb_corrupt_table_action instead.

innodb_use_purge_thread

xtradb_enhancements

XtraDB options that has changed default value
Option Old value New value
innodb_adaptive_checkpoint TRUE  FALSE
innodb_change_buffering inserts  all
innodb_flush_neighbor_pages 1  area
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)

innodb_adaptive_flushing_method

innodb_adaptive_hash_index_partitions

innodb_blocking_buffer_pool_restore

innodb_buffer_pool_instances

innodb_buffer_pool_restore_at_startup

innodb_change_buffering_debug

innodb_corrupt_table_action

innodb_flush_checkpoint_debug

innodb_force_load_corrupted

innodb_import_table_from_xtrabackup

innodb_large_prefix

innodb_purge_batch_size

innodb_purge_threads

innodb_recovery_update_relay_log

innodb_rollback_segments

innodb_sys_columns

innodb_sys_fields

innodb_sys_foreign

innodb_sys_foreign_cols

innodb_sys_tablestats

innodb_use_global_flush_log_at_trx_commit

innodb_use_native_aio

See also Perconas guide of how to upgrade to 5.5

旧的、也不支持的配置选项

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:

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


推荐阅读
  • 深入理解 SQL 视图、存储过程与事务
    本文详细介绍了SQL中的视图、存储过程和事务的概念及应用。视图为用户提供了一种灵活的数据查询方式,存储过程则封装了复杂的SQL逻辑,而事务确保了数据库操作的完整性和一致性。 ... [详细]
  • 在哈佛大学商学院举行的Cyberposium大会上,专家们深入探讨了开源软件的崛起及其对企业市场的影响。会议指出,开源软件不仅为企业提供了新的增长机会,还促进了软件质量的提升和创新。 ... [详细]
  • MySQL 数据库迁移指南:从本地到远程及磁盘间迁移
    本文详细介绍了如何在不同场景下进行 MySQL 数据库的迁移,包括从一个硬盘迁移到另一个硬盘、从一台计算机迁移到另一台计算机,以及解决迁移过程中可能遇到的问题。 ... [详细]
  • 根据最新发布的《互联网人才趋势报告》,尽管大量IT从业者已转向Python开发,但随着人工智能和大数据领域的迅猛发展,仍存在巨大的人才缺口。本文将详细介绍如何使用Python编写一个简单的爬虫程序,并提供完整的代码示例。 ... [详细]
  • 本文详细介绍了 MySQL 中 LAST_INSERT_ID() 函数的使用方法及其工作原理,包括如何获取最后一个插入记录的自增 ID、多行插入时的行为以及在不同客户端环境下的表现。 ... [详细]
  • 本文将深入探讨PHP编程语言的基本概念,并解释PHP概念股的含义。通过详细解析,帮助读者理解PHP在Web开发和股票市场中的重要性。 ... [详细]
  • MongoDB的核心特性与架构解析
    本文深入探讨了MongoDB的核心特性,包括其强大的查询语言、灵活的文档模型以及高效的索引机制。此外,还详细介绍了MongoDB的体系结构,解释了其文档、集合和数据库的层次关系,并对比了MongoDB与传统关系型数据库(如MySQL)的逻辑结构。 ... [详细]
  • PHP 编程疑难解析与知识点汇总
    本文详细解答了 PHP 编程中的常见问题,并提供了丰富的代码示例和解决方案,帮助开发者更好地理解和应用 PHP 知识。 ... [详细]
  • 本文介绍如何解决在 IIS 环境下 PHP 页面无法找到的问题。主要步骤包括配置 Internet 信息服务管理器中的 ISAPI 扩展和 Active Server Pages 设置,确保 PHP 脚本能够正常运行。 ... [详细]
  • CentOS7源码编译安装MySQL5.6
    2019独角兽企业重金招聘Python工程师标准一、先在cmake官网下个最新的cmake源码包cmake官网:https:www.cmake.org如此时最新 ... [详细]
  • 本文详细介绍了如何通过多种编程语言(如PHP、JSP)实现网站与MySQL数据库的连接,包括创建数据库、表的基本操作,以及数据的读取和写入方法。 ... [详细]
  • 使用Vultr云服务器和Namesilo域名搭建个人网站
    本文详细介绍了如何通过Vultr云服务器和Namesilo域名搭建一个功能齐全的个人网站,包括购买、配置服务器以及绑定域名的具体步骤。文章还提供了详细的命令行操作指南,帮助读者顺利完成建站过程。 ... [详细]
  • MySQL缓存机制深度解析
    本文详细探讨了MySQL的缓存机制,包括主从复制、读写分离以及缓存同步策略等内容。通过理解这些概念和技术,读者可以更好地优化数据库性能。 ... [详细]
  • 网络运维工程师负责确保企业IT基础设施的稳定运行,保障业务连续性和数据安全。他们需要具备多种技能,包括搭建和维护网络环境、监控系统性能、处理突发事件等。本文将探讨网络运维工程师的职业前景及其平均薪酬水平。 ... [详细]
  • PHP 过滤器详解
    本文深入探讨了 PHP 中的过滤器机制,包括常见的 $_SERVER 变量、filter_has_var() 函数、filter_id() 函数、filter_input() 函数及其数组形式、filter_list() 函数以及 filter_var() 和其数组形式。同时,详细介绍了各种过滤器的用途和用法。 ... [详细]
author-avatar
x-诗儿_683
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有