MySQL vs MariaDB

HighLoad++ 2019
Alkin Tezuysal
Who am I?
@ask_dba - Alkin Tezuysal
Born to Sail, Forced to Work
❖ Open Source Database Evangelist
❖ Global Database Operations Expert
❖ Cloud Infrastructure Architect AWS
❖ Inspiring Technical and Strategic Leader
❖ Creative Team Builder
❖ Speaker, Mentor, and Coach
Agenda
❖ Installation
❖ Authentication
❖ Storage Engines
❖ Clustering Options
❖ Routing and Proxy
❖ Security and Encryption
❖ Backup and Recovery
❖ Performance and Benchmarks

3
Installation - MySQL 8
❖ Straight forward MySQL Yum repos
➢ Oracle Linux, Red Hat Enterprise Linux, CentOS
➢ Also available Debian packages

❖ Fedora provides RPM packages

➢ MySQL server, client, MySQL Workbench, MySQL Utilities, MySQL
Router, MySQL Shell, Connector/ODBC, Connector/Python
Installation - MySQL 8
$curl https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
$sudo yum localinstall mysql80-community-release-el7-3.noarch.rpm
$sudo yum install mysql-community-server
$sudo grep 'temporary password' /var/log/mysqld.log
$mysql -uroot -p
> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';
Installation - MySQL 8 (Debian)
Download the repository package from:
https://dev.mysql.com/get/mysql-apt-config_0.8.14-1_all.deb

shell> sudo dpkg -i mysql-apt-config_0.8.14-1_all.deb

Set up the version you want to install

and

shell> sudo apt update

shell> sudo apt-get install mysql-community-server
Installation - MySQL 8
1. The server is initialized.
2. SSL certificate and key files are generated in the data directory.
3. validate_password policy is installed and enabled: The default password
policy implemented by validate_password requires that passwords contain
at least one upper case letter, one lower case letter, one digit, and one
special character, and that the total password length is at least 8
characters.
Installation MariaDB 10.4
❖ Downloadable script to install
$curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo
bash
$sudo yum install MariaDB-server MariaDB-client MariaDB-shared MariaDB-backup
MariaDB-common
$sudo systemctl start mariadb
$sudo journalctl -f -u mariadb
Installation MariaDB 10.4 (Debian)
❖ Downloadable script to install
sudo apt-get install software-properties-common dirmngr
sudo apt-key adv --recv-keys --keyserver keyserver.ubuntu.com
0xF1656F24C74CD1D8
sudo add-apt-repository 'deb [arch=amd64]
http://mirror.biznetgio.com/mariadb/repo/10.4/debian buster main'

sudo apt update

sudo apt install mariadb-server
Authentication - MySQL 8
❖ A superuser account 'root'@'localhost is created.
❖ A password for the superuser is set and stored in the error
log file.
❖ Single authentication method per user
Authentication - MariaDB 10.4

❖ Multiple Authentication Methods per

Account
➢ The root user is created with both
mysql_native_password and the unix_socket auth
plugin.
➢ unix_socket matches your OS uid with a mysql user.
➢ mysql_native_password auth creates an “INVALID”
password change with SET PASSWORD() classic
approach.
Storage Engines - MySQL 8
❖ FEDERATED ❖ MRG_MYISAM
❖ MEMORY ❖ BLACKHOLE
❖ InnoDB ❖ CSV
❖ Performance_Schema ❖ ARCHIVE
❖ MyISAM
Storage Engines - MariaDB 10.4
Default Installation (8) Plugins (6)
❖ CSV ❖ TokuDB
❖ MRG_MyISAM ❖ RocksDB
❖ MEMORY ❖ Spider
❖ Aria ❖ Connect
❖ OQGRAPH
❖ MyISAM
❖ Mroonga
❖ SEQUENCE
❖ InnoDB
❖ PERFORMANCE_SCHEMA
Clustering Options
MySQL 8 MariaDB 10.4
❖ InnoDB Cluster which ❖ MariaDB Galera Cluster:
consists of: ➢ Galera 4 (available in a
➢ Group Replication (available separate package)
as a plugin)
➢ MySQL Shell
➢ MySQL Router
Routing & Proxy
MySQL 8 MariaDB 10.4
❖ MySQL Router (GPLv2) ❖ Maxscale (2.X versions are
[CE/EE] using the BSL licence)
❖ 3rd Party ProxySQL
Security and Encryption - MySQL 8
❖ (Data-at-Rest) Encryption
➢ MySQL System Tablespace Encryption
➢ General Tablespace Encryption
➢ Undo log
➢ Redo log
➢ Binary and relay log encryption
➢ Audit log
➢ Keyring
■ keyring_file
■ keyring_encrypted_file [E]
■ keyring_okv [E]
■ keyring_aws [E]
■ HashiCorp Vault Keyring [E]
Security and Encryption - MariaDB
10.4
❖ (TDE) Transparent Data Encryption
➢ Everything including all tables
➢ Individual tables
➢ Everything, excluding individual tables
❖ Key Management and Encryption Plugin
➢ Data-at-rest with Encryption Key Management
➢ File Key Management
➢ AWS Key Management
➢ Eperi Key Management
➢ Plugin API
Security and Encryption - HC Vault
MySQL 8 MariaDB 10.4
❖ In MySQL 8.0.18 available ❖ MariaDB 10.4 has a feature
as an Enterprise plugin request
❖ 3rd Party
➢ Percona Server 5.7 and 8.0
Data Masking
MySQL 8 MariaDB 10.4
❖ MySQL Enterprise Data ❖ Data masking by using
Masking and MaxScale proxy
De-Identification [EE only
feature]
❖ 3rd Party:
➢ Inexpensive Datamasking
for MySQL with ProxySQL
[CE]
Auditing
MySQL 8 MariaDB 10.4
❖ MySQL Enterprise Audit ❖ MariaDB Audit Plugin
[EE]
❖ 3rd Party
➢ Percona Audit Log Plugin
Backup and Recovery
MySQL 8 MariaDB 10.4
❖ Mysqldump ❖ Mysqldump
❖ Mysqlpump ❖ Mariabackup
❖ MySQL Enterprise Backup ❖ 3rd Party
[EE] ■ Mydumper
❖ The Clone Plugin (8.0.17)
❖ 3rd Party
➢ Percona XtraBackup [CE]
➢ Mydumper
Key Default Variables
MySQL 8 MariaDB 10.4
innodb_autoinc_lock_mode=2 innodb_autoinc_lock_mode=1

log_bin=ON log_bin=OFF

max_allowed_packet=64M max_allowed_packet=16M

open_files_limit=10000 open_files_limit=4186

Query cache removed: query_cache_type=off

https://mysqlserverteam.com/mysql-8-0-retiring-support-for-th But
e-query-cache/ query_cache_limit=1M

table_open_cache_instances=16 table_open_cache_instances=8

table_open_cache=4000 table_open_cache=2000
Key Default Variables
MySQL 8 MariaDB 10.4
thread_cache_size=9 thread_cache_size=151

binlog_format=ROW binlog_format=MIXED

binlog_group_commit_sync_delay ---

DEPRECATED innodb_locks_unsafe_for_binlog

log_slave_updates=ON log_slave_updates=OFF

sync_binlog=1 sync_binlog=0

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TAB sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISI
LES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_ ON_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_S
DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION UBSTITUTION
Performance and Benchmarks
Thanks to Dimitri Kravchuk for providing
❖ Information on Benchmarks
➢ Blog
➢ Talks
➢ Twitter
Benchmarks - MySQL

Ref:http://dimitrik.free.fr/Presentations/MySQL_Perf-PLIVE19-EU-dim.pdf
Benchmarks - MySQL

Ref:http://dimitrik.free.fr/Presentations/MySQL_Perf-PLIVE19-EU-dim.pdf
Benchmarks - MySQL

Ref:http://dimitrik.free.fr/Presentations/MySQL_Perf-PLIVE19-EU-dim.pdf
Benchmarks - MySQL

Ref:http://dimitrik.free.fr/Presentations/MySQL_Perf-PLIVE19-EU-dim.pdf
Benchmarks - MariaDB

Ref:http://dimitrik.free.fr/Presentations/MySQL_Perf-PLIVE19-EU-dim.pdf
Benchmarks - MariaDB

Ref:http://dimitrik.free.fr/Presentations/MySQL_Perf-PLIVE19-EU-dim.pdf
Conclusion - MySQL 8
❖ 8.0.0 to 8.0.5 Several Bugs Fixed
❖ 8.0.11 Deprecates uft8mb3
❖ 8.0.12 Improves performance_schema defaults
❖ 8.0.13 Major deprecations InnoDB, Partitioning
❖ 8.0.14 Dual passwords, Lateral Derived Tables, Parallel Read of Index , GR Consistency Levels
❖ 8.0.15 Bugs Fixed
➢ InnoDB: After a checkpoint operation persisted modifications to data dictionary metadata, there was
potential for new metadata changes to be lost under certain circumstances. (Bug #29120297)
➢ Group Replication was unable to function in the 8.0.14 release of MySQL Server if IPv6 support was
disabled at the operating system level, even if the replication group did not use any IPv6 addresses. The
issue is fixed by this release of MySQL Server, 8.0.15. (Bug #29249542, Bug #94004)
❖ If MySQL was running on the host system and within Docker, it was not possible to update or remove MySQL on
the host system. (Bug #28244773, Bug #91405)8.0.0 to 8.0.11 several bug fixes
❖ 8.0.16 Account-management capabilities, deprecation of mysql_upgrade, Check Constraints, GR Auto Re-Join
❖ 8.0.17 Clone Plugin, JSON Array Indexes and Schema
❖ 8.0.18 Hash Join, Explain Analyze

31
Conclusion - MariaDB 10.4
❖ Authentication
➢ The unix_socket authentication plugin is now default on Unix-like systems, which is a major change to
authentication in MariaDB (MDEV-12484)
➢ User password expiry (MDEV-7597)
➢ Account Locking (MDEV-13095)
➢ ...more
❖ InnoDB
➢ Added instant DROP COLUMN and changing of the order of columns (MDEV-15562)
➢ More Instant VARCHAR extension or ROW_FORMAT=DYNAMIC and ROW_FORMAT=COMPACT
(MDEV-15563)
➢ Reduced redo log volume for undo tablespace initialization (MDEV-17138)
➢ Removed crash-upgrade support for pre-10.2.19 TRUNCATE TABLE (MDEV-13564)
❖ Optimizer
➢ Implementation of the optimizer trace, one can enable the optimizer trace by enabling the system variable
optimizer_trace (MDEV-6111)
❖ Galera
➢ In MariaDB 10.4.2 and later, Galera has been upgraded from Galera 3 to Galera 4.

32
Ref:https://mariadb.com/kb/en/library/changes-improvements-in-mariadb-104/
Special Thanks to...
❖ Daniel Guzman Burgos
❖ Stephen Thorn
❖ Hrvoje Matijakovic
❖ Sveta Smirnova @svetsmirnova
❖ Engineering, Experts and Services Teams at
Percona
33
❖
 Q&A

34
Credits & References
https://www.percona.com/doc/percona-server/8.0/feature_comparison.html
https://mariadb.com/kb/en/library/changes-improvements-in-mariadb-104/#new-features-in-galera-4

35