How to Install MariaDB and Configure UFW on Ubuntu
MariaDB is an open-source relational database management system, commonly used as an alternative for MySQL. UFW (Uncomplicated Firewall) is a firewall configuration tool that runs on top of iptables, included by default within Ubuntu distributions. In this document, you will learn how to install MariaDB on Ubuntu, create a database, a table, a user and grant privileges, open the port 3306 for MySQL connections, reload the UFW firewall and test the status of the database and the firewall.
Step 1: Update Ubuntu
Before installing MariaDB, it’s crucial to update your Ubuntu system. This step ensures that all existing packages are current, minimizing potential conflicts. Run the following command to update the package list:
sudo apt update
After updating the list, proceed to upgrade any outdated packages:
sudo apt upgrade
Step 2: Install MariaDB
As of this writing, Ubuntu’s default APT repositories include MariaDB version 10.3. To install it, run the following command:
sudo apt install mariadb-server
● mariadb.service - MariaDB 10.3.31 database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Active: active (running) since Fri 2024-02-02 03:45:04 EST; 2min 13s ago
Docs: man:mysqld(8)
https://mariadb.com/kb/en/library/systemd/
Main PID: 1234 (mysqld)
Status: "Taking your SQL requests now..."
Tasks: 31 (limit: 2286)
Memory: 64.5M
CGroup: /system.slice/mariadb.service
└─1234 /usr/sbin/mysqld
Step 3: Secure MariaDB
After installation, it is a good practice to run a security script to protect the MariaDB database from being compromised or attacked. Run the following command:
sudo mysql_secure_installation
This will take you through a series of prompts where you can make some changes to your MariaDB installation’s security options. The first prompt will ask you to enter the current database root password. Since you have not set one up yet, press ENTER to indicate “none”.
Enter current password for root (enter for none):
The next prompt asks you whether you’d like to set up a database root password. Type Y and press ENTER to set up a password, and then enter a secure password of your choice.
Set root password? [Y/n] Y
New password:
Re-enter new password:
For the rest of the prompts, you can press ENTER to accept the default values. This will remove some anonymous users and test databases, disable remote root logins, and load these new rules so that MariaDB immediately respects the changes you have made.
Step 4: Create a Database
To create a new database, you need to access the MariaDB client shell with the root user and password you set up in the previous step. Run the following command:
sudo mysql -u root -p
Enter your password and you will see the MariaDB prompt. To create a new database, use the following syntax:
CREATE DATABASE database_name;
Replace database_name with the name of your choice. For example, to create a database named mydb, type:
CREATE DATABASE mydb;
You can verify that the database has been created by listing all the existing databases with the following command:
SHOW DATABASES;
You should see your new database in the output, along with the default ones.
Step 5: Create a Table
To create a table, you need to select the database you want to use first. Use the following command to select the database you created in the previous step:
USE database_name;
Replace database_name with the name of your database. For example, to select mydb, type:
USE mydb;
You should see a message indicating that the database has been changed. To create a table, use the following syntax:
CREATE TABLE table_name (
column_name1 data_type1 constraints1,
column_name2 data_type2 constraints2,
...
);
Replace table_name with the name of your table, column_name with the name of each column, data_type with the type of data for each column, and constraints with any optional constraints for each column. For example, to create a table named users with three columns: id (integer, primary key, auto-increment), name (varchar, not null), and email (varchar, not null, unique), type:
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
email VARCHAR(50) NOT NULL UNIQUE
);
You can verify that the table has been created by listing all the tables in the current database with the following command:
SHOW TABLES;
You should see your new table in the output.
Step 6: Create a User
To create a new user, you need to exit the MariaDB client shell and return to the terminal. Type \q and press ENTER to quit the MariaDB prompt. To create a new user, use the following syntax:
CREATE USER ‘user_name’@’host_name’ IDENTIFIED BY ‘password’;
Replace user_name with the name of the user, host_name with the name of the host from which the user can connect, and password with the password for the user. For example, to create a user named myuser who can connect from the localhost with the password mypassword, type:
CREATE USER ‘myuser’@’localhost’ IDENTIFIED BY ‘mypassword’;
You can also create a user who can connect from any host by using the wildcard character % instead of the host name. For example, to create a user named myuser who can connect from any host with the password mypassword, type:
CREATE USER ‘myuser’@’%’ IDENTIFIED BY ‘mypassword’;
Step 7: Grant Privileges to the User
To grant privileges to the user, you need to specify the level of access, the database and table name, and the user name and host name. Use the following syntax:
GRANT privileges ON database_name.table_name TO ‘user_name’@’host_name’;
Replace privileges with the list of privileges you want to grant, separated by commas. You can use the keyword ALL to grant all privileges. Replace database_name and table_name with the name of the database and table you want to grant access to. You can use the wildcard character * to grant access to all databases or tables. Replace user_name and host_name with the name of the user and host you created in the previous step. For example, to grant all privileges to the user myuser who can connect from any host on the database mydb and all its tables, type:
GRANT ALL ON mydb.* TO ‘myuser’@’%’;
You can also grant specific privileges, such as SELECT, INSERT, UPDATE, DELETE, etc. For example, to grant only the SELECT and INSERT privileges to the user myuser who can connect from any host on the table users in the database mydb, type:
GRANT SELECT, INSERT ON mydb.users TO ‘myuser’@’%’;
You can also grant the user the ability to grant privileges to other users by using the WITH GRANT OPTION clause. For example, to grant all privileges to the user myuser who can connect from any host on the database mydb and all its tables, and also allow the user to grant privileges to other users, type:
GRANT ALL ON mydb.* TO ‘myuser’@’%’ WITH GRANT OPTION;
After granting privileges to the user, you need to reload the privilege tables to make the changes effective. Use the following command:
FLUSH PRIVILEGES;
You can verify the privileges granted to the user by using the following command:
SHOW GRANTS FOR ‘user_name’@’host_name’;
Replace user_name and host_name with the name of the user and host you created. You should see the list of privileges granted to the user in the output.
Step 8: Update the Bind Address
By default, the MySQL server will only listen on the localhost interface, which means that it will not accept connections from other hosts. To allow remote connections, you need to update the bind address in the /etc/mysql/my.cnf file. The bind address is the IP address that the MySQL server listens on.
English