VPS 服务器升级更新到 MariaDB 10.2.12 初体验

  在我的博客【记录一次失败的 Upgrade MySQL to MariaDB 经历】一文里,明月详细的说明了上周 Upgrade MySQL to MariaDB 失败的经历,可以说周末很不甘心。今天周一上班就想着在公司的服务器上试试了,因为公司目前使用的是 MySQL 5.7的,可以说硬件配置上是没有任何问题的。于是,马上进入控制台执行 Upgrade MySQL to MariaDB 脚本进行升级更新,这次选择的是 MariaDB 10.2.12版的,C m e K # p J t经过半个^ B B ( / } A 7多小时(配置高了编译就是快了很多呀!)编译,? ! ] I / 5成功升级 MySQL 到 MariaDB ,并且升级完成后网站直接自动恢复访问,不需要任何的附加操作,哈哈!

VPS 服务器升级更新到 MariaDB 10.2.12 初体验

  其实到目前为止,明月对 MariaDB 的了解也仅限于其创始人也是 MySQL 的创始人,至于为啥要重复开发一个 MariaDB 来,主要是因为 MK z 2ySQL 被甲骨文(Oracle)收购后就存在会收费和涉及版权的a x 3 = B e隐患了。单从 CQ ) W ?entOS 7 开始放弃 MySQL 转投 MariaDB 就可以知道这个“隐患”带来的影响有g e a , 7 3 ] v多大,当然 CentOZ Y 5 ~S 7 有“洁癖”也是事实。长久来看数据库的开源分享纯粹性对于网站来说还是很重要的,因为指不定哪天就面临着“收费授权”的尴尬就不好了,正所谓“长痛不如短痛”嘛!能尽早转投 MariaDB 还尽量V G r L d 5 (的早好的,公司的 MySQL 转 MariaDB 更是迫切需要的,毕竟涉及企业的很容易被“收费”嘛。

那么 MariaR : k ] : ! V KDB 和 MySQL 的区别主要] # W U s在哪些方面呢?

MariaDB和mySQL到底区别在哪,实验说明问题!

先看图,插入数据和时间的对数图,实验条件一直且关闭了mysql默认事务保证不是单条事务而是批量事务

VPS 服务器升级更新到 MariaDB 10.2.12 初体验

另外确保了 MySQL and MariaDB 都是在支持事务存储引擎下测试的。

MySQL 之父 Widenius 先生离开了 Sun 之后,觉得依靠 Sun/Oracle 来发展 MySQL ,实在很不靠谱,于是决定从新开发代码全部开源免费关系型数据库,这就是 MariaDB 。在 navicat 中操作 MariaDB 的界面和提示符还是 MySQL$ B 6 2 H 9 . T 端口也是 3306 外人看起来除了数据库名字改了其他和 MySQL 完全一致,但是( U , ? # 9 $ V l看一下性能比较,最近在美国 MariaDB 很火。我觉得 Oraclee - I W f S 是不是故意把 MySQL 做的很差?

MariaDB 在各方面都是 MySQL 创新和提高U { w b 0 r @版本,而不是简单的替代品。

MariaDB 的前世今生

2009年w ( i F E #

Michael Widenius创建新项目Michr & A %ael以规避关系型数据库开源的可能风险.V Y N / 6直到5.5的版b e = # { Z本,一直按照MySQL的版本进行c 4 f )发行。使用者基本j E s O上不会感受到和MySQL不同的地方。

2012年

MariaDB开始按照自己的节奏和版本发行方式进行发行,初始版本为:10.0.0,此版本以MySQL5.5为基础,同时k 2 3 Q -合并了MySQL5.6的相关功能。

MariaD! E o 7 dB 与 MySQL ——特性比较(官方版)

更多的存储引擎

 N @ ] h + / ` ! 除了提供标准的MyISAM、BLAl [ 9 o 1CKHOLE、CSV、MEMORY、ARCHIVE和MERGx L ^ R C b ! x 9E引擎外,在M^ D ) 3 e F : eariaDB源码与二进制包中还提供b _ 9 ! L G w n下列存储引擎:

  • Aria
  • XtraDB (与InnoDB对等替换)
  • PBXT(在MariaDB 5.1、MariaDB 5.2与 MariaDB 5.3提^ n , f ` @供,MariaDB 5.5己不提供了)
  • FederatedX (与Federated对等替换)
  • OQGRAPH (在MariaDB 5.1、MariaDB 5% C 3.2与 MariaDB 5.3提供,Maria% Q u D | } n Q sDB 5.5己不提供了)
    —5.2版添加
  • SphinxSE —5.2版添加
  • IBMDB2I. Oracle公司从MySQL 5.1.R m w N - r = R w55移除该引擎,MariaDB将在代码中保留其至版本5.5。
  • TokuDBMariaDB 5.5与MariaDB 10.0
  • Cassandra Ma+ _ j L u PriaDB 10.0

    • 我们A 2 j 9 ) G ^ }正忙于给MariaDB添加NoSQL存储引擎。
  • CONNECT MariaDB 10.0
  • SEQUENCE   MariaDB 10._ x a C A W W0
  • Spider MariaDB 10.0

速度提升

  • 在MariaDB 5.3中进行了大量的优化提升,子查询真正可用了!完整的列表及与的比较请点击此处,性能指标请在此处查看。
  • Faster and safer replication: Group commit for the binary log. This makes many setups that uses replicatiD | t % t Z Q -on and lot's of updu # Pates more than 2x times faster.
  • Improvements for Innodb asynchron9 1 - } F dous IO subsystem on Windows.
  • Indexes for the MEMORY(HEAz P } o n h S T VP) engine are fa@ X | Y }ster. According to a simple test, 24% faster on INSERT f: C ) E l %or integer index and 60% faster for index on a CHAR(20) colb ^ / ? 9umn.
  • CHECKSUM TABLE is faster.
  • We improved the performance of character set conversions (and removed converC + i ? 3 + qsir 9 _ons when they were not really needed). Overall speed improvement is 1-5 % (according to sql-be9 C 1 b p 6 H | gnch) but can be higher for big results sets with all characters between 0x00-0x7f.
  • Pool of Threads in MariaDB 5.1 aJ x l ( S xnd even better in MariaDB 5.5. This} r ` e l allows M] G MariaDB to run with 200,000+ connections and with a notable speed improvement when using many connections.
  • There are some improvements to the DBUG code to make its execut5 * V N ^ ~ Jion 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 tempo+ ` B S O U i :rary tables). The Aria storage engine is used for internal temporary tables, which shouldp y S ; { 7 j M
    give a speedup when doi. * ? $ Nng complex selects. Aria is usually faster for temporary tables when compared to MyISAM0 n w $ ! ) 5 because Aria caches row data in memory and normally doesn't have to write the temporary rows to disk.
  • The test suite has been extended and now runs much faster than before, even though it tests more things.

扩展&新特性

我们为MariaDB添加了很多新特性,假如一个补丁或特性是涉及可用性、安全与H ~ m r r F K 6稳定性——我们会积极地将其添加到MariaDB中。特别值得一提如下:

  • Microseconds in MariaDB — neP x 5 zw in 5.3
  • Microsecond Precision in Processlist
  • Table Elimination
  • Virtual Columns — new in 5.2
  • Extended User Statistics — new in 5.2
  • SeY C M , k X l ] qgmented Key Cache for MyISAM — new in 5.2
  • KILL all queries for a user — new in 5.3
  • Pluggable Authentication — new in 5.2
  • SG . b k [ % | itorage-engine-specific CREATE TABLE — new in 5.2
  • EnhanP , g X : |cements ta 6 qo INFORMATION SQ y A ?CHEMA.PLUGINS table — ne+ f `w in 5.2
  • Group commit for the binary log. This makes repl ? ^ 3 = ] 5ication notably faster! — new inz J k C T R E ` 5.3
  • Added --rewrite-db m! 0 - y ;ysqlbinlog option to change the used database — new in 5.2
  • Progress reporting for ALTER TABLE and LOAD DATA INFO o &ILE. — neZ _ 8 X Mw in 5.3
  • Faster joins and sub1 ~ F f U Aqu $ G U S ` y cueries. — new in} J U 5{ ; m } | + g 7.3
  • HandlerSocket and faster HANDLER calls. — new in 5.3
  • Dyna- = 1 A { C b @mic Columns support. — new in 5.3
  • GIS Functionality — new in 5.3
  • Multi-source replicJ @ u b ^ & ^ation. — new in 10.0
  • SHOW EXPLAIN gives the EXPLA# U y Y ) , yIN plan for a query running in another thread. — new in 10.0

更充分的测试

  • More tests in the test suite.
  • Bugs in tests fixed.
  • Test builds with different configure options to get better feature tes~ Y s 3 - pting.
  • Remove invalid tests. (e.g. don't test feature ''X'' if that feature is not in the tested build)

更少的警告与缺陷

  • Bugs are bad. Fix as many bugs as possible and tr% l | 0 % , e Uy to not introduce new ones| : g.
  • Compiler warnings are also bad. El} P ; 6 Yiminate as many compilk $ i ^ n ver warning} E g ` P c B Vs as possible.

真正开源

  • All code in MariaDB is released under GPL, LPGL or BSD. MariaDB does not have closed sox 9 S { W |urce modules like the ones that can be found in MySQL Enterprise Edition. IV ? . an fact, all the cl# - 9 Y ! r J %osed source features in MySQL 5.5 Enterpri= V M V +se Edition are found in the MariaDB open source version.
  • MariaDBf t v 7 $ k Y s includes test cases for all fixE T U N d ) t aed bugs. Oracle doesn M gn't p? + | m !rovide test cases for new bugs fixed in MySQL 5.5.
  • All bugs and development plans are/ t C e j Y public.
  • MariaDB is developed by the community in true open source spirit.

  作为一名服务器运维人员,任何“隐患”都要做好防范和修补的, CentOS 的“洁癖”其实@ d @就是运维人员们一个很好的标杆,虽然现在 MySQL 还可以依旧免费使用,但是就甲骨文(Oracle)的“尿性”, MySQL 未来版本性能下降Z w F % 5的可能性是大幅提升了,至于说“收费”问题也是一个必然会发生的事儿,所以 Upgrade MySQL to MariaDB 对于服务器运维来说还是很有必要的一个工作,如果服务器硬件配置合适,明月还是建议大家 Upgrad_ f Z Re MySQL to MariaDB 的。