How to Install MariaDB on Ubuntu 24.04
Introduction
MariaDB is an open-source relational database management system that works as a drop-in replacement for MySQL with improved reliability, performance, and flexibility. MariaDB uses the same SQL syntax as MySQL to enable the creation and management of databases on a server.
This article explains how to install MariaDB on Ubuntu 24.04. In addition, you will set up MariaDB with production configurations to enable the creation and management of databases on the server.
Install MariaDB.
sudo apt install mariadb-server
- This command installs the MariaDB server software on a Linux system. It uses sudo to gain administrative privileges, apt as the package manager, and install to download and set up the MariaDB server package.
View the installed MariaDB version on your server.
mariadb --version
- This command checks the version of the MariaDB database software installed on the system. When run in a terminal or command prompt, it displays the specific version number and related details about the MariaDB installation.
Enable the MariaDB system service to start at boot time.
sudo systemctl enable mariadb
This command sets up the MariaDB database service to start automatically when the computer boots up. It uses sudo for administrative rights and systemctl to manage the service configuration.
Start MariaDB on your server
sudo systemctl start mariadb
- This command starts the MariaDB database service on a Linux system. It uses sudo to gain administrative privileges and systemctl to manage the service. The start action tells the system to initialize and run the MariaDB server.
View the MariaDB service status to verify that it’s running on your server
sudo systemctl status mariadb
- This command checks the current status of the MariaDB service on a Linux system. It uses sudo to run with administrative privileges and systemctl to query the status. The output shows whether MariaDB is running, inactive, or has errors.
Output:
● mariadb.service - MariaDB 10.11.7 database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; preset: enabled)
Active: active (running) since Mon 2024-06-03 06:11:17 UTC; 8h ago
Docs: man:mariadbd(8)
https://mariadb.com/kb/en/library/systemd/
Process: 19853 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
Process: 19863 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 19892 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl> Process: 20014 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 20016 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
Main PID: 19994 (mariadbd)
Status: "Taking your SQL requests now..."
Tasks: 10 (limit: 14978)
Memory: 79.1M (peak: 81.5M)
CPU: 6.694s
CGroup: /system.slice/mariadb.service
└─19994 /usr/sbin/mariadbd
Access MariaDB
MariaDB is compatible with graphical management interfaces such as PhpMyAdmin that integrate directly with the database server console. You can access the MariaDB database server console using the
mariadbcommand ormysql. Follow the steps below to access the MariaDB console and create sample databases on the server.Log in to the MariaDB database server.
sudo mariadb -u root -p
Enter the database root user password
Create a new sample database. For example, exampledb.
MariaDB [(none)]> CREATE DATABASE exampledb;
- This command creates a new database named exampledb in MariaDB. It tells the database system to allocate space and set up the necessary structure to store data under that name.
View all databases on the server and verify that the new database is available.
MariaDB [(none)]> SHOW DATABASES;
- This command lists all the databases available in the MariaDB server. When executed, it shows a list of database names stored on the server.
Output:
+--------------------+
| Database |
+--------------------+
| exampledb |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.001 sec)
How to Allow Remote Access to MariaDB
Configure MariaDB Remote Access
As mentioned above, all remote access to the server is denied by default. To enable remote access, you’ll need to set the bind address to allow for remote access.
For example, to allow all IPv4 addresses set the bind address to: 0.0.0.0. This will allow the MariaDB server accepts connections on all host IPv4 interfaces. If you have IPv6 configured on your system, use::
Simply run the commands below to open the MariaDB configuration file.
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
Depending on your systems, you may find that same configuration file may be at the location below:
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
When the file is opened, search for a line that begins with bind-address, as shown below. Its default value should be 127.0.0.1.
# this is read by the standalone daemon and embedded servers
# this is only for the mysqld standalone daemon
[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1
#
# * Fine Tuning
What you need to do is change the default value 127.0.0.1 to 0.0.0.0, as shown below:
# this is read by the standalone daemon and embedded servers
# this is only for the mysqld standalone daemon
[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 0.0.0.0
#
# * Fine Tuning
In the same file, you’ll want to comment out the line that begins with skip-networking by putting the # before it. Or delete it together. Then save your changes.
Please add the changes above under the [mysqld] section.
After making the change above, save the file and run the commands below to restart the server.
sudo systemctl restart mariadb.service
sudo systemctl stop mariadb.service
sudo systemctl start mariadb.service
sudo systemctl enable mariadb.service
To verify that the change happens, run the commands below
sudo apt install net-tools
sudo netstat -anp | grep 3306
Now the server is set up to listen to all IP addresses, but individual IP needs to be explicitly configured to connect to a database.
You must grant access to the remote server to enable a client to connect to a database.
Access from Remote Clients
Now that the server is configured. Use the steps below to allow remote clients to access the database.
=================Remote User Create===================================
1- CREATE USER 'lona'@'type remote client server Ip example 192.168.1.103' IDENTIFIED BY 'password';
2- GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'lona'@'192.168.1.103' WITH GRANT OPTION;
3- FLUSH PRIVILEGES;
================Remote User create for all host acces========================
5- CREATE USER 'saikat'@'%' IDENTIFIED BY 'sumita';
4- GRANT ALL PRIVILEGES ON *.* TO 'saikat'@'%' WITH GRANT OPTION;
5- FLUSH PRIVILEGES;
===========Localhost full acces User Create===========================
5- CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
6- GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
7- FLUSH PRIVILEGES;
OR
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION;
Mysql Server Commnds
How to install Mysql server?
1- sudo apt update && sudo apt install mysql-server
2- sudo service mysql status
3-<root login> - sudo mysql
How to Mysql restart start stop ?
1- service mysql start
2- service mysql stop
3- service mysql restart
4- sudo systemctl start mysql.service
5- systemctl status mysql.service
How to check Mysql Version?
1- mysql --version
How to MySQL Secure installtion?
1- sudo mysql_secure_installation
2- ENTER | Y | Y | Y | Y
3- sudo mysqladmin -p -u root version
How to Mysql setup remote accses ?
1- sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf <edit Bind IP 0.0.0.0>
2- sudo systemctl restart mysql
3- mysql -u root -p
Mysql Firewall Allow setup
4- sudo ufw allow from remote_IP_address to any port 3306 (<spective IP addresses>)
ufw allow from **IP_ADDRESS** to any port 3306
5- sudo ufw allow 3306
6- sudo ufw status
7- sudo ufw app list
6- mysql -u user -h database_server_ip -p (<testing Login mysql to remote>)
How to create Mysql User to login & Grand Privilage ?
=================Remote User Create===================================
1- CREATE USER 'lona'@'type remote client server Ip example 192.168.1.103' IDENTIFIED BY 'password';
2- GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'lona'@'192.168.1.103' WITH GRANT OPTION;
3- FLUSH PRIVILEGES;
================Remote User create for all host acces========================
5- CREATE USER 'saikat'@'%' IDENTIFIED BY 'sumita';
4- GRANT ALL PRIVILEGES ON *.* TO 'saikat'@'%' WITH GRANT OPTION;
5- FLUSH PRIVILEGES;
===========Localhost full acces User Create===========================
5- CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
6- GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
7- FLUSH PRIVILEGES;
OR
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION;
How to install phpmyadmin for acces Mysql Web Gui?
1- sudo apt update && sudo apt install phpmyadmin php-mbstring
2- sudo ln -s /etc/phpmyadmin/apache.conf /etc/apache2/conf-available/phpmyadmin.conf
3- sudo a2enconf phpmyadmin.conf
4- sudo service apache2 reload
How to secure install phpmyadmin
5- sudo nano /etc/apache2/conf-available/phpmyadmin.conf
6- Alias /<type your_url> /usr/share/phpmyadmin
7- sudo service apache2 reload