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
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
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:
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);