MariaDB: Tutorial & Best Practices

A relational SQL database

MariaDB is a fork of the MySQL database management system (DBMS) that is designed to be a drop-in replacement for MySQL. It is widely used as a database engine for web applications, and is known for its stability, security, and performance.

To install MariaDB on a Linux system, you can typically use the package manager that comes with your distribution. For example, on a Debian-based system, you can use the apt-get command to install MariaDB:

sudo apt update
sudo apt install mariadb-server

On a Red Hat-based system, you can use the yum command to install MariaDB:

sudo yum update
sudo yum install mariadb-server

Once the installation is complete, you can start the MariaDB server by running the following command:

sudo systemctl start mariadb

To configure MariaDB, you can use the mysql_secure_installation script to set the root password, remove anonymous users, and disable remote root logins.

To access the MariaDB command-line interface, you can use the mysql command. For example, to log in as the root user, you can run the following command:

mysql -u root -p

You will be prompted to enter the root password, after which you will be able to enter SQL commands to manage and query your database.

While for small applications, the performance of MariaDB is adequate, you may run into problems when your database gets very big. In that case, it makes sense to install the MyRocks engine because it can significantly improve the performance of your database by using a storage engine based on the RocksDB key-value store.

MyRocks is optimized for fast writes and low disk usage, making it well-suited for large databases that require quick insertion and retrieval of data. Additionally, MyRocks supports compression, which can further reduce disk usage and improve performance by reducing the amount of data that needs to be read from and written to disk. It is important to note, however, that MyRocks may not be suitable for all workloads, and it is recommended to thoroughly test it in your specific use case before deploying it in production.

On a Debian-based Linux, you can install MyRocks by running the following command:

sudo apt-get update
sudo apt-get install mariadb-plugin-rocksdb

Use this commands on RHEL, Centos, Fedora and similar distributions:

sudo yum install MariaDB-rocksdb-engine

For SLES or OpenSuse the command is:

sudo zypper install MariaDB-rocksdb-engine

Once the MyRocks storage engine is installed, you can activate it in MariaDB by adding the following lines to your MariaDB configuration file (usually located at /etc/mysql/my.cnf):

[mysqld]
default-storage-engine=rocksdb

Finally, restart MariaDB for the changes to take effect:

sudo service mysql restart

You can verify that MyRocks is correctly installed and activated by running the following command:

SHOW ENGINES;

This should show MyRocks as an available storage engine.

Count Number of Words in MySQL

This can easily be achieved by counting the spaces in the text:

SELECT SUM(LENGTH(column_with_text) - LENGTH(REPLACE(column_with_text, ' ', '')) + 1) FROM my_table;

Convert Column to UTF8

CONVERT(CAST(CONVERT(column USING latin1) AS BINARY) USING utf8);