20 C
Texas
Melhttp://www.osradar.com
Guru Unix /Linux and GNU supporter

Install and configure Mariadb server on Centos 6/7 and Fedora 27/28

MariaDB is an software  replacement for MySQL and is an open source fork of MySQL created by the original MySQL creators. As of Red Hat Enterprise Linux (RHEL7) and CentOS 7 MariaDB is now the default SQL database, rather than MySQL which was the default in previous releases of the operating system.
Here we’re going to cover how to install and configure MariaDB server.
Sinds 2015 is  MariaDB becoming standard Mysql software for many linux  distributions and can be easy  installed via repositories  ( Yum/Apt/Packman/Zypper/….. .)

How  to install  Mariadb server and  Client

MariaDB is simply installed with ‘yum’ as shown below. This is recommended over compiling from source or using RPM files, as MariaDB will be easier to update in the future through the package manager via ‘yum update’.

Install and configure Mariadb server

- Advertisement -

Please note:  i have tested  these tutoarial under my Centos7.4

How  to install  Mariadb server and  Client

yum install mariadb mariadb-server -y

MariaDB should now be installed, we can confirm the package version installed as shown below.

[root@centos7a ~]# mysql -V
mysql Ver 15.1 Distrib 5.5.56-MariaDB, for Linux (x86_64) using readline 5.1

Configuring MariaDB

By default after installation MariaDB will not be running and will not start on boot as shown below. you can fix tha twith the bellow commands.

[root@centos7a ~]# systemctl enable mariadb
ln -s '/usr/lib/systemd/system/mariadb.service' '/etc/systemd/system/multi-user.target.wants/mariadb.service'
[root@centos ~]# systemctl start mariadb

Check if  MariaDB running and Listening

check process

Port  listening

[root@centos7a ~]# netstat -plunt | grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 1449/mysqld 
[root@centos7a ~]#

Setup root  mariadb password

Now that MariaDB is running you can access the command line by simply running ‘mysql’ as there is not yet any password in place, to setup  the root password please use  the command   ‘mysql_secure_installation’

[root@osradar-test conf]# mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none): <=== just  Enter
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
Set root password? [Y/n] y  <== set new password
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
... Success!
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] y <== remove anonymous user
... Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] n <== allow or not to login with root remotly
... skipping.
By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] n <== delete  test database
... skipping.
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] y <== Reload privileges
... Success!
Cleaning up...All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!

Test you access and see the created databases

[root@osradar-test conf]# mysql -u root -p
Enter password:     <<== insert your mysql created password in the step before 
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 5.5.56-MariaDB MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]>

How to Create  Database 

Create database osradardb

MariaDB [(none)]> create database osradardb;
Query OK, 1 row affected (0.00 sec)

Create  database user osradaruser identified  with password  AAA123

MariaDB [(none)]> CREATE USER 'osradaruser'@'localhost' IDENTIFIED BY 'AAA123';
Query OK, 0 rows affected (0.03 sec)

Grant all privileges to a user (osradaruser) on a specific database ( example osradardb)

MariaDB [(none)]> GRANT ALL ON osradardb.* TO 'osradaruser'@'localhost';
Query OK, 0 rows affected (0.01 sec)

Because of security we are using  localhost  instead of ‘%’ (  manage database remotely)

Apply changes made

To be effective the new assigned permissions you must finish with the following command: and exit

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> exit 
Bye

Please Check if  the new user  able to  login and see the database

[root@centos7a ~]# mysql -u osradaruser -p 
Enter password:   <---  insert the user password 
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 23
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| osradardb |
+--------------------+
2 rows in set (0.01 sec)
MariaDB [(none)]> use osradardb;
Database changed
MariaDB [osradardb]> show tables;
Empty set (0.00 sec)

Its Empty database: How to import an dump  database to this one?

for example your export dump  named  dump.sql  located under /root

to import  this exported database to your empty  database, please do the following

# mysql -u osradaruser -p < /root/dump.sql 
Enter password: <--- insert the user password

Now you can login again and see the tables  and the columns.

I found  on internet this bash script that can help you easy to crease user,database and grant  the permissions  :

Don’t forget  chmod +x and run it under root user

#! /bin/bash

newUser='osradaruser'
newDbPassword='AAA123'
newDb='osradardb'
host=localhost
#host='%'
 
commands="CREATE DATABASE \`${newDb}\`;CREATE USER '${newUser}'@'${host}' IDENTIFIED BY '${newDbPassword}';GRANT ALL ON osradardb.* TO '${newUser}'@'${host}' IDENTIFIED BY '${newDbPassword}';GRANT ALL privileges ON \`${newDb}\`.*
TO '${newUser}'@'${host}';FLUSH PRIVILEGES;"

echo "${commands}" | /usr/bin/mysql -u root -p

Thank you for reading

- Advertisement -
Everything Linux, A.I, IT News, DataOps, Open Source and more delivered right to you.
Subscribe
"The best Linux newsletter on the web"

LEAVE A REPLY

Please enter your comment!
Please enter your name here



Latest article