Setup MariaDB repository
1
2
|
sudo dnf module list mariadb
sudo vi /etc/yum.repos.d/mariadb.repo
|
1
2
3
4
5
6
7
8
|
# mariadb.repo
# MariaDB 10.6 RedHat repository list
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.6/rhel8-amd64
module_hotfixes=1
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
|
Install
1
2
3
|
sudo dnf install mariadb-server mariadb -y
sudo systemctl start mariadb
sudo systemctl enable mariadb
|
Verify
1
|
$ ps -ef | grep -i mysql
|
Output:
1
2
|
mysql 11212 1 0 Sep04 ? 00:01:36 /usr/sbin/mariadbd
root 108589 108532 0 15:50 pts/0 00:00:00 grep --color=auto --exclude-dir=.bzr --exclude-dir=CVS --exclude-dir=.git --exclude-dir=.hg --exclude-dir=.svn --exclude-dir=.idea --exclude-dir=.tox -i mysql
|
Secure MariaDB
1
|
sudo mariadb-secure-installation
|
Manage
Create a new database
1
|
echo "CREATE DATABASE IF NOT EXISTS <database_name>;" | mysql -u root -p
|
Grant privileges to a user
1
|
echo "GRANT ALL PRIVILEGES ON <database_name>.* TO '<database_user>'@'localhost' IDENTIFIED BY '<password>';" | mysql -u root -p
|
Populate a database from a file
1
|
mysql -h localhost -D <database_name> -u <database_user> -p < mysql.sql
|
Verify
1
|
echo "SHOW TABLES;" | mysql -h localhost -D <database_name> -u <database_user> -p --table
|
Enable remote login
First of all, in order to enable remote login, you have to bind the MariaDB server address to allow connections from remote hosts.
By default, MariaDB server is bound to localhost only. To bind the server to all interfaces, edit the MariaDB configuration file:
1
|
sudo vi /etc/mysql/my.cnf
|
1
2
|
# my.cnf
bind-address = 0.0.0.0
|
Next, you have to create a user WITH GRANT OPTION who would be able to connect to the database from remote hosts.
Grand all privileges to a user for all databases
1
|
echo "GRANT ALL PRIVILEGES ON *.* TO 'remoteuser'@'%' IDENTIFIED BY 'remoteuser_password' WITH GRANT OPTION;" | mysql -u root -p
|
[!Warning] This grants the user all privileges to be able to connect to the database from remote hosts.
Grant read-only access to a user for all databases
1
2
|
echo "GRANT SELECT ON *.* TO 'remoteuser'@'%' IDENTIFIED BY 'remoteuser_password' WITH GRANT OPTION;" | mysql -u root -p
|
This grants the user read-only access to the database from remote hosts.
Grant read-only access to a user for a specific database
1
|
echo "GRANT SELECT ON <database_name>.* TO 'remoteuser'@'%' IDENTIFIED BY 'remoteuser_password' WITH GRANT OPTION;" | mysql -u root -p
|
Note: The % sign in the @ clause means that the user can connect from any host.
If you want to allow the user to connect from a specific host, use the host IP address instead of %.Eg. GRANT ALL PRIVILEGES ON *.* TO 'remoteuser'@'55.123.1.80' IDENTIFIED BY 'remoteuser_password' WITH GRANT OPTION;
You may need to flush the privileges to make the changes effective:
DELETE a user
1
|
echo "DROP USER 'remoteuser'@'%';" | mysql -u root -p
|
DELETE a database
1
|
echo "DROP DATABASE <database_name>;" | mysql -u root -p
|
Open mysql port & restart MariaDB
For iptables firewall:
1
|
sudo iptables -I INPUT -p tcp -m tcp --dport 3306 -j ACCEPT
|
For firewalld:
1
2
|
sudo firewall-cmd --zone=public --add-port=3306/tcp --permanent
sudo firewall-cmd --reload
|
Finally, restart MariaDB & check status of Database host port listening
1
2
|
sudo systemctl restart mariadb
netstat -ant | grep 3306
|
Succeed output:
1
|
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN
|
Now you can connect to the database from remote hosts using the following command:
1
|
mysql -h <mysq_host_ip> -u <remote_db_username> -p
|