FreeBSD MariaDB Databases Installation and Configuration

Despite the fact that MariaDB is a fork of Oracle's MySQL database, the two have diverged so much that they are now very different from each other. Database management systems like MySQL are pay-as-a-service software, whereas MariaDB is fully GPL licensed. MariaDB also offers significant performance improvements and supports a wide range of storage engines.


1. What is MariaDB?

MariaDB was first released in late 2009 to forever solidify the MySQL codebase as a free database management system. It is based on the highly successful MySQL and was developed as an open source resource. Microsoft and WordPress, as well as many other companies, rely on Maria DB and MySQL to run their businesses.

Maria DB has been designed to be interchangeable with MySQL, allowing customers to easily migrate from one platform to another. MariaDB is a great example of such exchange speed. Forked from MySQL in 2009, MariaDB has grown to become one of the top open source databases in use today.

When Oracle acquired MySQL in October 2009, many people on the team were worried about MySQL's future. MariaDB starts and takes its version from MySQL starting from 5.1. MariaDB is licensed under the GNU General Public License and is intended to remain free and open source. The developer named the project after his second daughter and intended it to be an easy replacement for MySQL and is often featured as an included DB for popular Linux distros such as CentOS. The APIs and protocols used in MySQL are also found in MariaDB.

2. Difference between MariaDB and MySQL

  1. MariaDB includes 12 new storage engines, while MySQL has fewer. MySQL has a smaller connection pool than MariaDB. So MariaDB is faster than MySQL.
  2. MySQL replication is slower than MariaDB.
  3. MariaDB is open source, but MySQL is not and uses proprietary code.
  4. MySQL supports data hiding and dynamic columns, while MariaDB does not.

Such is the power of MySQL that Facebook's ability to process millions of requests every second is facilitated by the fact that MySQL serves every user interaction. Netflix, YouTube, Booking.com, Airbnb, and many of the world's largest companies are only able to compete effectively by using MySQL to power their enormous databases.

MariaDB operates as open source software with a GPL, BSD, or LGPL license. It supports standard query languages and many high-performance storage engines that can be integrated with other RDMS. It is very important that MariaDB supports PHP. And finally, it supports Galera cluster technology.


3. MariaDB Features

MariaDB is backward compatible and open source, making it a cost-effective choice for companies looking to take advantage of its powerful database management capabilities. As a lightweight version of MySQL, MariaDB has many new features thanks to an active development team consisting of volunteers around the world. MySQL is slower and lacks support for data hiding and dynamic columns. Access to new commands such as KILL and WITH as well as JSON compatibility are attractive options for new software developers.

MySQL Enterprise Edition's shortcomings are addressed with plug-ins in MariaDB, and server operating systems such as Linux, Solaris, and Windows are also supported. The code is easy to convert due to its similarity to MySQL data structures and is written in C++, Bash and Perl; MariaDB is available to most programmers.

MySQL's scalability and flexibility, combined with its high performance and strong transaction support, make it the number one database tool. Web capabilities and storage advantages are important components of any development team's choice, and MySQL is updated regularly and is an effective player in many markets.

Access to multiple data and users can be performed simultaneously by multiple users in a highly scalable environment. MySQL is written in C and C++ languages, which have great support and availability for many programmers around the world.


4. Installing MariaDB on FreeBSD 13.2

In this article we will use the FreeBSD 13.2 system to install MariaDB. Type the following command to install MariaDB.

root@ns1:~ # cd /usr/ports/databases/mariadb106-client
root@ns1:/usr/ports/databases/mariadb105-client #
make config
root@ns1:/usr/ports/databases/mariadb105-client # make install clean

When using the make config command there will be several options. In the GSSAPI Security API support option, disable the GSSAPI option.

After the MariaDB client installation process is complete, continue with installing the MariaDB server.


root@ns1:~ # cd /usr/ports/databases/mariadb106-server
root@ns1:/usr/ports/databases/mariadb106-server # make config
root@ns1:/usr/ports/databases/mariadb106-server # make install clean

The same as the client installation process, in the MariaDB server installation, the GSSAPI option must be turned off.


5. Create Script rc.d

Like most applications running on FreeBSD, MariaDB also requires the rc.d script so that it can be booted automatically by the FreeBSD system. Creating this file is almost the same as the MySQL Server rc.d script. Type the following command in the /etc/rc.conf file.

root@ns1:~ # ee /etc/rc.conf
mysql_enable="YES"
mysql_user="mysql"
mysql_dbdir="/var/db/mysql"
mysql_optfile="/usr/local/etc/mysql/my.cnf"
mysql_rundir="/var/run/mysql"

After the script above has been created, run MariaDB. Type the following command to run the MariaDB application.

root@ns1:~ # service mysql-server start
Installing MariaDB/MySQL system tables in '/var/db/mysql' ...
OK

To start mariadbd at boot time you have to copy
support-files/mariadb.service to the right place for your system


Two all-privilege accounts were created.
One is root@localhost, it has no password, but you need to
be system 'root' user to connect. Use, for example, sudo mysql
The second is mysql@localhost, it has no password either, but
you need to be the system 'mysql' user to connect.
After connecting you can set the password, if you would need to be
able to connect as any of these users with a password and without sudo

See the MariaDB Knowledgebase at https://mariadb.com/kb

You can start the MariaDB daemon with:
cd '/usr/local' ; /usr/local/bin/mariadb-safe --datadir='/var/db/mysql'

You can test the MariaDB daemon with mysql-test-run.pl
cd '/usr/local/' ; perl mariadb-test-run.pl

Please report any problems at https://mariadb.org/jira

The latest information about MariaDB is available at https://mariadb.org/.

Consider joining MariaDB's strong and vibrant community:
https://mariadb.org/get-involved/

Starting mysql.


If it appears as written above, it means that MariaDB is running on the FreeBSD system. Now we try to test by running MariaDB with the FreeBSD daemon.

root@ns1:~ # /usr/local/libexec/mysqld --skip-grant-tables --general-log &

After that, we create a root password, here's how to create a MariaDB root password.

root@ns1:~ # mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.

You already have your root account protected, so you can safely answer 'n'.

Switch to unix_socket authentication [Y/n]
n
... skipping.

You already have your root account protected, so you can safely answer 'n'.

Change the root password? [Y/n]
y
New password:
router
Re-enter new password:
router
Password updated successfully!
Reloading privilege tables..
... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n]
y
... Success!

Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n]
y
... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n]
y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n]
y
... Success!

Cleaning up...

All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!



6. Running MariaDB

Once you have missed none of the steps above and have everything configured, it's time to run MariaDB. The method is almost the same as MySQL Server. Now let's test whether MariaDB can enter the SQL command line menu or not?

root@ns1:~ # /usr/local/bin/mariadb -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.5.21-MariaDB FreeBSD Ports

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost [(none)]>

From the results of the command above, MariaDB has successfully entered the MySQL database menu. Let's look at the contents of the data base table.

root@localhost [mysql]> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| column_stats |
| columns_priv |
| db |
| event |
| func |
| general_log |
| global_priv |
| gtid_slave_pos |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| index_stats |
| innodb_index_stats |
| innodb_table_stats |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| roles_mapping |
| servers |
| slow_log |
| table_stats |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| transaction_registry |
| user |
+---------------------------+
31 rows in set (0.001 sec)

After successfully running MariaDB on FreeBSD, you can now use MariaDB to manage the database. Using MaraDB is not much different from MySql Server, for those who are used to MySql Server it will definitely be very easy to use MariaDB. Apart from being almost the same or you could say that MariaDB is MySQL Server's sibling, MariaDB has features that MySQL Server doesn't have. The following are MariaDB's superior features.
  1. Can use InnoDB with instant add columns as the default storage engine for MariaDB.
  2. Parallel and multi-origin replication.
  3. Can use FOR loops with MariaDB.
  4. Transparent column.
  5. The latest version comes with Galera Cluster.
  6. Can add sequences.
  7. Column compression is now independent of the storage engine.

7. Create User Database Mariadb

One of the most important tasks of a database server is granting access and permissions. MariaDB is a fully compatible open source relational database management system (RDBMS). The MariaDB client makes it easy to add new users and give them different privilege levels.

If your MariaDB database doesn't yet have a database, we recommend that you create a database first. On MariaDB, we can do this easily by typing the following command in your MariaDB client shell:

root@ns1:~ # /usr/local/bin/mariadb -u root -p
root@localhost [(none)]> CREATE DATABASE everest;
Query OK, 1 row affected (0.133 sec)

Use the following command to view all databases in MariaDB.

root@localhost [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| everest            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.023 sec)

If you want to use the Everest database use the following command.

root@localhost [(none)]> use everest;
Database changed

After you have successfully created the database, we continue by creating a user who can access the Everest database.

root@localhost [everest]> CREATE USER 'jhondoe'@'localhost' IDENTIFIED BY 'router123';
Query OK, 0 rows affected (0.039 sec)
root@localhost [everest]> GRANT ALL PRIVILEGES ON everest.* TO 'jhondoe'@'localhost';
Query OK, 0 rows affected (0.062 sec)
root@localhost [everest]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.013 sec)

With the above command, only jhondoe users can open the everest database. Meanwhile, other users cannot open the Everest database.

If you want one user to be able to open all databases in MariaDB, use the following command.

root@localhost [everest]> CREATE USER 'mary'@'localhost' IDENTIFIED BY 'router123';
Query OK, 0 rows affected (0.039 sec)
root@localhost [everest]> GRANT ALL PRIVILEGES ON *.* TO 'mary'@'localhost';
Query OK, 0 rows affected (0.037 sec)
root@localhost [everest]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.017 sec)

"GRANT ALL PRIVILEGES ON *.*" meaning it provides access to all databases on the MariaDB server.

With the help of this article, you have successfully run MariaDB server on FreeBSD. You also understand how to create users and provide access to each database. This article is only part of the MariaDB material, there is still a lot that has not been discussed, you can read other articles about MariaDB.
Iwan Setiawan

I Like Adventure: Mahameru Mount, Rinjani Mount I Like Writer FreeBSD

Post a Comment

Previous Post Next Post