© 2015, MariaDB Corp.

MariaDBRoadshowBerlin2015

MariaDB10.1:DatenbankverschlüsselungundandereSicherheitsvorteile

RalfGebhardt,PrincipalSalesEngineer

©MariaDBCorporaBonAb.

Agenda

• MariaDB10.1NewFeatures• ForHighAvailability• ForScalability• ForSecurity

• MariaDB10.1SecurityFeatureSet

07.12.15 2

©MariaDBCorporaBonAb.

MariaDB10.1Released

• FirstGAversion10.1.8releasedinOctober• BasedonMariaDB10.0• IncludescontribuBonsfromcommunitymemberslikeFacebook

07.12.15 3

©MariaDBCorporaBonAb.

MariaDB10.1Themes

High Availability

Scalability Security

07.12.15 4

©MariaDBCorporaBonAb.07.12.15 5

High Availability

©MariaDBCorporaBonAb.

GaleraClusterintegrated

• FullintegraBonofGaleraClusterintoMariaDB10.1—itwon’tbeaseparatedownload

• EnableGaleraClusterwhenyouneedit

07.12.15 6

©MariaDBCorporaBonAb.

GaleraCusterintegrated

•  PerdefaultMariaDB10.1workslikeavanillaMariaDBServer•  ForGaleraClusteritisrequiredto:

• wrep_on=ON• wsrep_provider• wsrep_cluster_address•  binlog_format=ROW•  default_storage_engine=InnoDB•  innodb_autoinc_lock_mode=2•  innodb_doublewrite=1•  query_cache_size=0

07.12.15 7

©MariaDBCorporaBonAb.07.12.15 8

Scalability

©MariaDBCorporaBonAb.

ParallelSlaveReplicaBon(10.0)

• MulB-sourcereplicaBonfromdifferentmasters(domains)executedinparallel

• Queriesthatareruninparallelonthemasterareruninparallelontheslave(basedongroupcommit)

• TransacBonsmodifyingthesametablecanbeupdatedinparallelontheslave!

• SupportsbothstatementbasedandrowbasedreplicaBon.

07.12.15 9

©MariaDBCorporaBonAb.

OpBmisBcParallelReplicaBon

• NewreplicatonmodeinMariaDB10.1• AnyINSERT,UPDATEorDELETEcanbeappliedinparallelontheslave

• Notneccessarilymeansthatitwascommitedinparallelonthemaster

• Needsa10.1master• NeedsatransacBonalengineforrollbackincaseofaconflict

07.12.15 10

©MariaDBCorporaBonAb.

OpBmisBcParallelReplicaBon

• Enabledbyslave-parallel-mode=optimistic

• Temporarilydisablebyvariable@@skip_parallel_replication

• ServeropBmisBcallyassumesthatfewconflictswilloccur

• rollbackandretryforconflicBngtransacBons

07.12.15 11

©MariaDBCorporaBonAb.

PerformanceImprovements

• EspeciallyforHigh-EndServers• Highprocessingpower• Morecores

• Benchmark10.1onLinuxOnlyPOWER8• „1millionSQLqueriespersecond:GAMariaDB10.1onPOWER8“

• heps://blog.mariadb.org/10-1-mio-qps/

07.12.15 12

©MariaDBCorporaBonAb.

InnoDBDefragmentaBon

• Deletedrecordscancreategapsonpages• DefragmentaBonbasedonanimplementaBonfromFacebookandKakaoCorp

• ButnonewSQLliteralsneededandchangestotheserverneeded

• OPTIMIZETABLEisused• innodb_defragment=1

07.12.15 13

©MariaDBCorporaBonAb.

MySQLCompaBbilityFeature

• MariaDB10.1canbeaslavetoMySQL5.6• AlsowhenGTIDsareused

• FeaturewasrequestedfromtheCommunity• TotestMariaDBinaMySQLdeployment• FormigraBngtoMariaDB

07.12.15 14

©MariaDBCorporaBonAb.07.12.15 15

Security

©MariaDBCorporaBonAb.

SecurityFeaturesinMariaDB10.1

• DataatRestEncrypBon• PasswordValidaBonPlugin• PAMAuthenBcaBonPlugin• AuditPlugin• SSLConnecBons• EncrypBonfuncBons

07.12.15 16

©MariaDBCorporaBonAb.

DataatRestEncrypBon

• NewwithMariaDB10.1• OriginatesfromGoogleencrypBonpatch• TablespaceandtableencrypBon• Basedon

• EncypBonkey• Keyid• KeyrotaBon• Keyversion

07.12.15 17

©MariaDBCorporaBonAb.

DataatRestEncypBon

•  EncrypBonfor•  XtraDB/InnoDBtablespaces•  XtraDB/InnoDBlogfiles•  Binarylogs•  Ariatables•  Temporaryfiles

•  NoEncrypBonfor• Metadata• Memory•  Config-Files

07.12.15 18

©MariaDBCorporaBonAb.

DataatRestEncrypBon

• LastinternalbenchmarksonencrypBonoverhead

• XtraDB/InnoDBencrypBon• <1%(ro)• ≈8-14%(rw)

• TemporaryfilesencrypBon• ≈7-10%(filesort)• BinarylogencrypBon:<4%

07.12.15 19

©MariaDBCorporaBonAb.

DeletedDataEncrypBon

• Scrubbing• Backgroundthreadsperiodicallyscantablespacesandlogsandoverwritealldatathatshouldbedeleted.

• Moreinfo:• heps://mariadb.com/kb/en/mariadb/xtradb-innodb-data-scrubbing/

07.12.15 20

©MariaDBCorporaBonAb.

PasswordValidaBonPlugins

•  PasswordvalidaBonpluginAPI•  simple_password_checkplugin

•  Canenforceaminimumpasswordlengthandguaranteethatapasswordcontainsatleastaspecifiednumberofupperandlowercaseleeers,digits,andpunctuaBoncharacters

•  cracklib_password_checkplugin•  Awidelyusedlibrary•  Stopusersfromchoosingeasytoguesspasswords.ItincludeschecksfornotallowingpasswordsbasedontheusernameoradicBonarywordetc.

07.12.15 21

©MariaDBCorporaBonAb.

PAMAuthenBcaBonPlugin

•  AuthenBcaBonusing/etc/shadow•  AuthenBcaBonusingLDAP,SSHpassphrases,passwordexpiraBon,usernamemapping,loggingeveryloginaeempt,etc…

•  INSTALL PLUGIN pam SONAME 'auth_pam.so'; •  CREATE USER foo@host IDENTIFIED via pam; •  REMEMBERtoconfigurePAM(/etc/pam.dor/etc/pam.conf)

07.12.15 22

©MariaDBCorporaBonAb.

MariaDBAuditPlugin

• AudiBngdatabaseaccessto• File(commadelimitedformat)• Syslog

• ModifiedPluginAPIinMariaDB• AuditPlugincompaBblewithMySQLServer

• OnlyMariaDBallowstomonitortablelevelevents

07.12.15 23

©MariaDBCorporaBonAb.

MariaDBAuditPlugin

07.12.15 24

CONNECTION

QUERY

CONNECT

DDL

DISCONNECT

FAILED CONNECT

DML+TCL

OBJECT DATABASE

TABLES

TIMESTAMP HOST USER

SESSION

DCL

©MariaDBCorporaBonAb.

MariaDBAuditPlugin

• Passwordfilteringincluded

07.12.15 25

20150117 23:40:56,MYSQL5530,root,localhost,1,1,QUERY,,'CREATE USER "test1"@"localhost" IDENTIFIED BY *****',0!20150117 23:40:56,MYSQL5530,root,localhost,1,1,QUERY,,'CREATE USER "test4"@"localhost" IDENTIFIED BY PASSWORD *****',0!20150117 23:40:56,MYSQL5530,root,localhost,1,1,QUERY,,'INSERT INTO t_pwdtest VALUES (1,PASSWORD("mypwd"))',0!20150117 23:40:56,MYSQL5530,root,localhost,1,1,QUERY,,'UPDATE t_pwdtest SET mypwd = PASSWORD("mynewpwd")',0!20150117 23:40:56,MYSQL5530,root,localhost,1,1,QUERY,,'INSERT INTO t_pwdtest VALUES (2,OLD_PASSWORD("mypwd2"))',0!20150117 23:40:56,MYSQL5530,root,localhost,1,1,QUERY,,'UPDATE t_pwdtest SET mypwd = OLD_PASSWORD("mynewpwd2")',0!20150117 23:40:56,MYSQL5530,root,localhost,1,1,QUERY,,'GRANT ALL ON *.* TO "test5"@"localhost" IDENTIFIED BY *****',0!

©MariaDBCorporaBonAb.

SSLConnecBons

• EncryBonbetweenclientandserver• Disabledbydefault

• TLSv1.2protocol• SSLalsoavailableforreplicaBon• VariablesneededtouseSSL

• ssl-ca=ca.pem• ssl-cert=server-cert.pem• ssl-key=server-key.pem

07.12.15 26

©MariaDBCorporaBonAb.

EncrypBonFuncBons

• EncryBonfuncBonsareusedpercolumn• AvailableencrypBons

• AES(AdvancedEncrypBonStandard)algorithm• DES(DataEncrypBonStandard)algorithm

• RequiresSSLtobeconfigured• StringencrypBonviaDECODE/ENCODE

07.12.15 27

©MariaDBCorporaBonAb.07.12.15 28

ThankYou

mariadb.com

ralf.gebhardt@mariadb.com

"MySQL is a registered trademark of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. MariaDB is not affiliated with MySQL."