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-mariadb bitnami/mariadb

Configuration files

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

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: select
  • Create a basic table: create 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
• Adminer
• Navicat Monitor

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
• Database management, psql, Sqlelectron, Prisma ORM
• 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