Install MariaDB on Ubuntu 18.04 – Database Management System, the New MySQL

MySQL/MariaDB is probably the most popular multi-user database in the world.
Since Ubuntu 18.04, you should install mariadb-* packages to use this database.

These are short notes – a lot of commands, little explanation. For more explanations, see MySQL Install and One Table Database – SQL CRUD tutorial for Ubuntu. Just remember to use MariaDB instead of MySQL on Ubuntu 18.04 and newer.

Firewall

Database must always be protected by a firewall. If you’re remotely on a server, remember to make a hole for SSH, too.

$ sudo ufw allow 22/tcp
$ sudo ufw enable

Install MariaDB Client and Server

MariaDB and MySQL have diverged from the same codebase. In Ubuntu 18.04+, you should install MariaDB, and you should not install MySQL.

$ sudo apt-get update
$ sudo apt-get -y install mariadb-client mariadb-server
$ sudo mysql_secure_installation   # you still need a firewall
Set root password? [Y/n]
New password:
Re-enter new password:
Remove anonymous users? [Y/n] y
Disallow root login remotely? [Y/n] y
Remove test database and access to it? [Y/n] y
Reload privilege tables now? [Y/n] y

Create New Database & Database User

Only use database root to create a new project user. Remember that database users are completely different from system (Linux) users.

$ sudo mariadb -u root
MariaDB [(none)]> create database foo;
MariaDB [(none)]> drop database foo;
MariaDB [(none)]> CREATE DATABASE hats;
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| hats               |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
MariaDB [(none)]> GRANT ALL ON hats.* TO hats@localhost IDENTIFIED BY 'd--e73%_29tHisIsNotMyRealPassword';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> exit
Bye

Login as Normal Database User

$ mariadb -u hats -p
MariaDB [(none)]> CREATE DATABASE hajotankaiken;
ERROR 1044 (42000): Access denied for user 'hats'@'localhost' to database 'hajotankaiken'

Create Some Tables

MariaDB [(none)]> USE hats;
MariaDB [hats]> CREATE TABLE hats (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(1024), price FLOAT);
MariaDB [hats]> SHOW TABLES;
+----------------+
| Tables_in_hats |
+----------------+
| hats           |
+----------------+
MariaDB [hats]> SELECT * FROM hats;
Empty set (0.00 sec)

Insert New Records (Create)

MariaDB [hats]> INSERT INTO hats(name, price) VALUES ("Ninja hood", 104.3);
Query OK, 1 row affected (0.00 sec)
MariaDB [hats]> SELECT * FROM hats;
+----+------------+-------+
| id | name       | price |
+----+------------+-------+
|  1 | Ninja hood | 104.3 |
+----+------------+-------+
1 row in set (0.00 sec)
MariaDB [hats]> INSERT INTO hats(name, price) VALUES ("Space helmet", 10400.3);
Query OK, 1 row affected (0.00 sec)

Select (Read) Your Records

MariaDB [hats]> SELECT * FROM hats;
+----+--------------+---------+
| id | name         | price   |
+----+--------------+---------+
|  1 | Ninja hood   |   104.3 |
|  2 | Space helmet | 10400.3 |
+----+--------------+---------+
2 rows in set (0.00 sec)

Update Existing Records

MariaDB [hats]> UPDATE hats SET price=1000000 WHERE id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
MariaDB [hats]> SELECT * FROM hats;
+----+--------------+---------+
| id | name         | price   |
+----+--------------+---------+
|  1 | Ninja hood   |   104.3 |
|  2 | Space helmet | 1000000 |
+----+--------------+---------+
2 rows in set (0.00 sec)

Delete

MariaDB [hats]> DELETE FROM hats WHERE price<1000;
Query OK, 1 row affected (0.00 sec)
MariaDB [hats]> SELECT * FROM hats;
+----+--------------+---------+
| id | name         | price   |
+----+--------------+---------+
|  2 | Space helmet | 1000000 |
+----+--------------+---------+
1 row in set (0.00 sec)
MariaDB [hats]> exit

Automate Login to MariaDB

$ touch .my.cnf
$ chmod og-rwx .my.cnf
$ ls -l .my.cnf
$ nano .my.cnf
[client]
user="hats"
database="hats"
password="d--e73%_29tHisIsNotMyRealPassword1"

$ mariadb

MariaDB [hats]>

MariaDB Users

Database management system (MariaDB DBMS) users are completely different from Linux users. They must have different (but good) passwords.

$ sudo mariadb -u root
MariaDB [hats]> GRANT ALL on hats.* TO baduser IDENTIFIED BY 'af/(msbUhio!8hdHQQm';
MariaDB [hats]> USE mysql
MariaDB [mysql]> DESCRIBE user;
MariaDB [mysql]> SELECT User FROM user;
+---------+
| User    |
+---------+
| baduser |
| hats    |
| root    |
+---------+
MariaDB [mysql]> DROP USER baduser;
Posted in Uncategorized | Tagged , , , , , , , | Leave a comment

Leave a Reply

Your email address will not be published. Required fields are marked *