Hello Friends,
Let's just start by defining what is MariaDB? it is a fork of MySQL, it is one of the most popular database server, it is developed by the original founders of MySQL and guaranteed to remain to stay open source, it is pretty much works almost the same as MySQL, Let's get started by installing it on the system
- Installing and configure MariaDB
we are just need to type
$ sudo apt install mariadb-server
make sure it is up and running by typing
$ sudo systemctl status mysql
You should get Active: active (running) with a green font, if for any reason you don't get that and get inactive instead, just run the following command
$ sudo systemctl start mysql
also you need to make sure it will start up when system boot by typing
$ sudo systemctl enable mysql
After the installation done we need to setup and configure the new installed MariaDB server, by using this command
$ sudo mysql_secure_installation
Follow the instructions, You need to set a root password, then I highly recommended that you disable remote login with root but typing 'Y' when asked, also you need to remove any anonymous database by typing 'Y' when asked, and then you need to reload privileges. that's will do.
- Adding and configure users to MariaDB
Now MariaDB is installed and configured, we need to add a user to manage the server instead of the root and may be you need to allow the remote access to that user. type the following command
$ mysql -u root -p
after enter the password you just set early you will gain access to MariaDB console, to create a new user
MariaDB > Create User <username>@<location> identified by <password>;
Example
Create User 'codivya'@'localhost' identified by 'Password123';
That will create a user called codivya and it will have access to the localhost only and the user password will be Password123
or
Create User 'codivya'@'%' identified by 'Password123';
This will create a user called codivya and it will have a remote access from any ip address on the internet including the localhost and the user password will be Password123
After creating a user we need to to grant it access to all commands and all databases and tables on the server by typing the following command
Grants all privileges on *.* to 'codivya'@'localhost';
or
Grants all privileges on *.* to 'codivya'@'%';
Note: the first * before the dot mean all databases and the second * after the dot mean all tables.
Now let's test that everything works good, first we need to quit Mariadb Console by typing
quit;
then we need to login with our new created user
$ mysql -u codivya -p
after enter the password created for codivya user we should see the MariaDB console successfully, let's try to create a test database with our new user
Create Database test;
You should successfully able to create the database 'test' with no problem, if everything works.
- Remote access configuration
if you wish to make your mysql server have the ability to do a remote access from outside the server, you need to edit one little thing in the configuration files, the config file is
/etc/my.cnf
but for some reason i can't find that file on my server instead my config file was in
/etc/mysql/mariadb.conf.d/50-server.cnf
You need to open that file and search for
bind-address = 127.0.0.1
Edit the IP to your server IP, let's say your server IP is 45.45.45.45, you need to write it like that
bind-address = 45.45.45.45
then save, exit and reload mariadb server by typing
$ sudo systemctl restart mysql
Now you should be able to remote access mariaDB server
- Reset MariaDB to its default manually
If things got messed up, you can't add users, nothing works, you got a bunch of access denied there is a solution for that, we will just reset mariadb to it's default state and start again, but be very aware that the following commands will delete all databases you created if any, what we are gonna do is to reset mariadb as fresh install state, so be careful
$ sudo systemctl stop mysql
$ sudo rm -rf /var/lib/mysql/*
$ sudo mysql_install_db
$ sudo systemctl start mysql
then after that we will just run mysql_secure_installation and start again
$sudo mysql_secure_installation
-----------------------------------------
That's it you have a database server running and ready, Thank you for reading, I hope this post helped you somehow.
Comments
Post a Comment