在我的博客【记录一次失败的 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 ,并且升级完成后网站直接自动恢复访问,不需要任何的附加操作,哈哈!
其实到目前为止,明月对 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默认事务保证不是单条事务而是批量事务
另外确保了 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 的。