MariaDB

wikipedia:MariaDB

MariaDB is at least offered as a service in AWS RDS (2015), Azure and Alibaba cloud providers.

Installation

• apt install mariadb-common
• apt install mariadb-server
• brew install mariadb

Helm by Bitnami

helm repo add bitnami https://charts.bitnami.com/bitnami && helm install my-release bitnami/mariadb

Configuration files

• Ubuntu: /etc/mysql/mariadb.conf.d/50-server.cnf
• Data: /var/lib/mysql/

Binaries

• mariadb
• mariadbcheck: mariadbcheck --repair --all-databases (mariadb-client-core package)
• mysql_upgrade

Logs

• /var/log/mysql/error.log

• show variables
  • SHOW VARIABLES LIKE 'maria_group_commit';

Activities

• Perform the following tasks: (from retired RHCE certification)
  • Install MariaDB. For example in MacOS: brew install mariadb
  • Start and configure MariaDB.
  • Backup and restore a database (mariabackup)
  • Create a simple database schema
  • Perform simple SQL queries against a database
  • Create a basic table

• Activate MariaDB slow queries log
• Verify your database with mariadb-check command

Install and start MariaDB in MacOS

• brew install mariadb
• Start MariaDB:

/usr/local/bin/mysql.server start (command is mysql but you are actually starting MariaDB)

Starting MariaDB
.180619 10:15:19 mysqld_safe Logging to '/usr/local/var/mysql/file.err'.
180619 10:15:19 mysqld_safe Starting mysqld daemon with databases from /usr/local/var/mysql
 SUCCESS! 

• Check MariaDB is running: /usr/local/bin/mysql.server status

 SUCCESS! MariaDB running (80662)

Docker installation

• https://hub.docker.com/_/mariadb
• docker run --name some-mariadb -v /my/own/datadir:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mariadb:latest

Create a table: CREATE TABLE

• Connect to database test, will be created in default MariaDB installation:

mysql test

• Create a basic table with two columns using the create table SQL statement:

create table my_first_table (my_first_column char, my_second_column char);

• Show tables so we can check our new created table is available:

MariaDB [test]> describe my_first_table;
+------------------+
| Tables_in_test   |
+------------------+
| my_first_table   |
+------------------+

• Show details about your new table:

MariaDB [test]> describe my_first_table;
+------------------+---------+------+-----+---------+-------+
| Field            | Type    | Null | Key | Default | Extra |
+------------------+---------+------+-----+---------+-------+
| my_first_column  | char(1) | YES  |     | NULL    |       |
| my_second_column | char(1) | YES  |     | NULL    |       |
+------------------+---------+------+-----+---------+-------+
2 rows in set (0.003 sec)

1. Show privileges: MariaDB [(none)]> show grants;
2. Allow remote root access: GRANT ALL PRIVILEGES on *.* to 'root'@'%' IDENTIFIED BY 'YOUR_PASSWORD';

Commands

• show engines
• show tables
• FLUSH STATUS;

Advanced Features

• Understand MariaDB wikipedia:partitioning (w:https://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems#Partitioning) and uses cases. SHOW PLUGINS to see if your version support it.
• Create a MariaDB Cluster using MariaDB Galera Cluster.
• Tunning you limitating variables like: max_connections
• MariaDB replication

Related terms

• Percona
• SkySQL
• MariaDB Foundation
• HA: Replication
• https://github.com/major/MySQLTuner-perl/blob/master/INTERNALS.md

Activities

1. Understand how to limit the number of threads to save memory for the database memory buffers: https://mariadb.com/kb/en/library/thread-pool-in-mariadb/

SHOW VARIABLES LIKE 'thread_handling';

MariaDB [(none)]> SHOW VARIABLES LIKE 'thread_handling';
+-----------------+---------------------------+
| Variable_name   | Value                     |
+-----------------+---------------------------+
| thread_handling | one-thread-per-connection |
+-----------------+---------------------------+
1 row in set (0.00 sec)

thread_handling = [ pool-of-threads | one-thread-per-connection ]

1. Read MariaDB Changelog/releases
2. Read MariaDB encryption capabilites https://mariadb.com/kb/en/data-at-rest-encryption-overview/

See also

• MyRocks
• Databases: Database management, SQL, NoSQL, Wide column, PostgreSQL, MariaDB, MySQL, Derby DB, MongoDB, Cassandra, SQLite, HSQL2, H2, RocksDB, Microsoft SQL Server, DB2, Oracle Database, Memcached, Berkeley DB, Collation, SingleStore, Amazon Aurora, Graph database, Amazon DynamoDB, PrestoDB, Cache hit ratio, ACID, WAL, ARIES, DBMS, OLTP, OLAP, Database Schema, CockroachDB, Tables, Views, Apache Druid, RDMS
• MariaDB: mariadbcheck, mariabackup, mysqldump, MyRocks, show (MariaDB) MariaDB Changelog, 50-server.cnf, MariaDB engines, .ibd, .myd, .myi, /var/log/mysql/error.log, /var/log/mysql/mariadb-slow.log, MariaDB files: .frm, .ibd, /etc/mysql/mariadb.conf.d/50-server.cnf, replication, MariaDB slow queries, show databases;, Aria, MyISAM, MariaDB SkySQL, binary log, 3306, /etc/mysql/

Text is available under the Creative Commons Attribution-ShareAlike License; additional terms may apply. By using this site, you agree to the Terms of Use and Privacy Policy.

Original Source: https://en.wikiversity.org/wiki/MariaDB