MariaBD Installation And Management in RHEL 7

MariaBD Installation And Management in RHEL 7

Required packages.

[root@feenixdv77 ~]# yum install mariadb-*

Start service and check status.

[root@feenixdv77 ~]# systemctl start mariadb

[root@feenixdv77 ~]# systemctl status mariadb

mariadb.service – MariaDB database server

Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled)

Active: active (running) since Thu 2016-12-22 09:48:54 EST; 12s ago

Process: 22914 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)

Process: 22832 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)

Main PID: 22913 (mysqld_safe)

CGroup: /system.slice/mariadb.service

Enable service in current run level.

[root@feenixdv77 ~]# systemctl enable mariadb

ln -s '/usr/lib/systemd/system/mariadb.service' '/etc/systemd/system/multi-user.target.wants/mariadb.service'

Run “mysql_secure_installation” script to setup secure setting.

[root@feenixdv77 ~]# mysql_secure_installation

/usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not found

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):

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

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

… 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

… 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

… skipping.

Reloading the privilege tables will ensure that all changes made so far

will take effect immediately.

Reload privilege tables now? [Y/n] Y

… 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!

Main configuration file for mariadb is “/etc/my.cnf”. Using this file you can customize database.

[root@feenixdv77 ~]# cat /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

# Settings user and group are ignored when systemd is used.

# If you need to run mysqld under a different user or group,

# customize your systemd unit file for mariadb according to the

# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]

log-error=/var/log/mariadb/mariadb.log

pid-file=/var/run/mariadb/mariadb.pid

# include all files from the config directory

!includedir /etc/my.cnf.d

Login in database with user and password.

[root@feenixdv77 ~]# mysql -u root -p

Enter password:

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 7

Server version: 5.5.41-MariaDB MariaDB Server

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.01 sec)

MariaDB [(none)]> use mysql;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [mysql]> show tables;

+—————————+

| Tables_in_mysql |

+—————————+

| columns_priv |

| db |

| event |

| func |

| general_log |

| help_category |

| help_keyword |

| help_relation |

| help_topic |

| host |

| ndb_binlog_index |

| plugin |

| proc |

| procs_priv |

| proxies_priv |

| servers |

| slow_log |

| tables_priv |

| time_zone |

| time_zone_leap_second |

| time_zone_name |

| time_zone_transition |

| time_zone_transition_type |

| user |

+—————————+

24 rows in set (0.00 sec)

MariaDB [mysql]> show databases

-> ;

+——————–+

| Database |

+——————–+

| information_schema |

| mysql |

| performance_schema |

| test |

+——————–+

4 rows in set (0.00 sec)

MariaDB [mysql]> use test;

Database changed

Now in this example we perform this task.

Create a database
Create table in side database
Insert data in table
Show data from table.

Create a database

MariaDB [test]> create database feenix;

Query OK, 1 row affected (0.08 sec)

MariaDB [test]> use feenix;

Database changed

Create table in side database

MariaDB [feenix]> create table feenix(title varchar(40), name varchar(40), year INT);

Query OK, 0 rows affected (0.02 sec)

Insert data in table

MariaDB [feenix]> insert into feenix (title,name,year) values(linux,rhel4,'2001');

ERROR 1054 (42S22): Unknown column 'linux' in 'field list'

MariaDB [feenix]> insert into feenix (title,name,year) values('linux','rhel4',2001);

Query OK, 1 row affected (0.01 sec)

MariaDB [feenix]> insert into feenix (title,name,year) values('suse','suse4',2005);

Query OK, 1 row affected (0.00 sec)

MariaDB [feenix]> insert into feenix (title,name,year) values('fedora','fedora25',2016);

Query OK, 1 row affected (0.01 sec)

Show data from table.

MariaDB [feenix]> select * from feenix;

+——–+———-+——+

| title | name | year |

+——–+———-+——+

| linux | rhel4 | 2001 |

| suse | suse4 | 2005 |

| fedora | fedora25 | 2016 |

+——–+———-+——+

3 rows in set (0.00 sec)

Managing users in mariadb

Now we perform this task.

Create a new user with password.
Grant access for select, update, delete to the user.
See the privileges of user.
Grant access on specific table of database.
Grant access on all table of database.
Grant access on all database.

Create a new user with password. % use for both access local and remote.

MariaDB [feenix]> create user eric@'%' identified by 'immad44';

Query OK, 0 rows affected (0.00 sec)

Grant access for select, update, delete to the user and after that must be flush privileges;

MariaDB [feenix]> grant select,update,delete on feenix to eric@'%';

Query OK, 0 rows affected (0.00 sec)

MariaDB [feenix]> flush privileges;

Query OK, 0 rows affected (0.01 sec)

See the privileges of user

MariaDB [feenix]> show grants for eric@'%';

+—————————————————————————————————–+

| Grants for eric@% |

+—————————————————————————————————–+

| GRANT USAGE ON *.* TO 'eric'@'%' IDENTIFIED BY PASSWORD '*0B9938E026930C6C71CE59ADB8671EB869C861AB' |

| GRANT SELECT, UPDATE, DELETE ON `feenix`.`feenix` TO 'eric'@'%' |

+—————————————————————————————————–+

2 rows in set (0.01 sec)

Grant access on specific table of database. In this example table name if feenix.

MariaDB [feenix]> grant select,update,delete on feenix.feenix to eric@'%';

Query OK, 0 rows affected (0.01 sec)

Grant access on all table of database.

MariaDB [feenix]> grant select,update,delete on feenix.* to eric@'%';

Query OK, 0 rows affected (0.00 sec)

Grant access on all database.

MariaDB [feenix]> grant select,update,delete on *.* to eric@'%';

Query OK, 0 rows affected (0.00 sec)

Backup with “mysqldump” command. Here feenix is database name.

[root@feenixdv77 ~]# mysqldump -u root -p feenix > /root/feenix.dump

Enter password:

[root@feenixdv77 ~]# ll /root/feenix.dump

-rw-r–r– 1 root root 1950 Dec 22 12:18 /root/feenix.dump

Direct take a backup of data directory. Use this command to see data directory.

[root@feenixdv77 ~]# mysqladmin -u root -p variables |grep datadir

Enter password:

| datadir | /var/lib/mysql/

Leave a Reply Cancel reply