RHCE – Database Services

Install and configure MariaDB

# install / configure MariaDB #
yum groupinstall mariadb
systemctl start mariadb
systemctl enable mariadb

# configure MariaDB - complete the wizard - sets up root pwd and other security settings #
## root password / anon user / disable remote root login /  remove test db / reloads privilege tables ##
mysql_secure_installation  

# configure bind address in /etc/my.cnf #
## place at the bottom of [mysqld] block in /etc/my.cnf ##
bind-address=10.0.0.250

# configure firewalld - if remote connection are required #
firewall-cmd --permanent --add-service=mysql
systemctl restart firewalld

# basic usage #
## log in as root local
mysql -u root -p

## log in as root via network
mysql -u root -p'password' -h <DBhostname> 

## show available dbs
show databases;

## select db to use
use <databaseName>;

## to show tables of selected database  
show tables;

## to quit
quit

Backup and restore a database

## Backup single DB
mysqldump -u root -p'password' -h <hostname> <DBName> > <DBname>.sql

## Backup multiple DB
mysqldump -u root -p'password' -h <hostname> --databases <DBName1> <DBName2> > <DBname>.sql

## Backup all DBs
mysqldump -u root -p'password' -h <hostname> --all-databases > <DBname>.sql

## restore DBs
# note the DB you are restoring must have an empty DB with the same name to restore too. 
# This only applies to when a single DB is restored
mysql -u root -p'password' -h <hostname> <DBName> < DB_Name.sql

Create a simple database schema

# to create DB
create database <DBname>;

# to create User
CREATE USER <user>@localhost IDENTIFIED BY '<userPwd>';

# grant permission to user
grant all on test.* to <user>@localhost identified by '<userPwd>';
flush privileges;

# to select DB
use <DBname>;

# to create table
create table <TableName> (<fieldName1> VARCHAR(50) NOT NULL, <fieldName2> VARCHAR(50));

# to show tables
show tables;

# to get schema 
describe <TableName>

# to insert data into database
insert into <tableName>  (<fieldName1>,<fieldName2>) values ("<fieldName1Value>","<fieldName2Value>");

# to update data in database
update <tableName> set <fieldName1>=<newFieldName1> where <fieldName1>= "<fieldName1Value>";

Perform simple SQL queries against a database

select <fieldName> from <tableName> where <FieldName>=<FieldNmaeValue>;

Leave a comment

Your email address will not be published. Required fields are marked *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax