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;