RHEL7: Create a simple database schema.

Share this link

Note: This is an RHCE 7 exam objective.

Presentation of MariaDB Database Schema

A database schema consists of:

  • a database name that groups all the objects together,
  • one or several users with their associated access rights,
  • a list of tables storing records,
  • other objects like indexes, views, triggers, etc.

Note: A single MariaDB instance can host several databases.

Prerequisites

First, you need to install a MariaDB database.

Procedure

Then, you have to connect to the server with the password you created previously:

# mysql -u root -p
Enter password: your password
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 5.5.35-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>

Then, you can create a database (here called test):

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

Note: Use the drop database command to remove a database.

Give permissions to the user called user:

MariaDB [(none)]> grant all on test.* to user@localhost identified by 'your password';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Note: There is a password associated with the root account required during the server installation process. Then, there is another password linked to the database owner (here user). It’s easier if both are the same but they don’t need to.

Exit the MariaDB command line:

MariaDB [(none)]> quit
Bye

Now, you can connect to your own database directly:

# mysql -u user -p test
Enter password: your password
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 5.5.35-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

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

MariaDB [test]>

To get the list of all the available databases, type:

MariaDB [test]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

Note: You can go inside a database with the use command. For example, type use test; to go to your new database.

To create a table called addresses, type:

MariaDB [test]> create table addresses(id int(10) unsigned, name varchar(20), address varchar(40));
Query OK, 0 rows affected (0.14 sec)

Note: Use the drop table command to remove a table.

To get the list of all the tables created in your database, type:

MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| addresses      |
+----------------+
1 row in set (0.00 sec)

To get a description of a particular table (here addresses), type:

MariaDB [test]> desc addresses;
+---------+------------------+------+-----+---------+-------+
| Field   | Type             | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| id      | int(10) unsigned | YES  |     | NULL    |       |
| name    | varchar(20)      | YES  |     | NULL    |       |
| address | varchar(40)      | YES  |     | NULL    |       |
+---------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

To get the create table statement associated with the addresses table, type:

MariaDB [test]> show create table addresses;
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                    |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| addresses | CREATE TABLE `addresses` (
  `id` int(10) unsigned DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `address` varchar(40) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Useful Tip

At the MariaDB prompt, you can get some information about the syntax by using the help command:

MariaDB [(none)]> help drop table
Name: 'DROP TABLE'
Description:
Syntax:
DROP [TEMPORARY] TABLE [IF EXISTS]
    tbl_name [, tbl_name] ...
    [RESTRICT | CASCADE]

DROP TABLE removes one or more tables. You must have the DROP privilege
for each table. All table data and the table definition are removed, so
be careful with this statement! If any of the tables named in the
argument list do not exist, MySQL returns an error indicating by name
which nonexisting tables it was unable to drop, but it also drops all
of the tables in the list that do exist.
...

Additional Resources

You can also watch Andrew Mallett‘s video about Creating a MariaDB Database (6min/2015).

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading...
14 comments on “RHEL7: Create a simple database schema.
  1. krz says:

    After installing group mariadb and mariadb-client and updating man with mandb and check documentation on /usr/share/docs/ I cannot find anyplace with SQL syntax examples.

    Any advice where to find it during the exam some examples just in case I don’t remember some details of the syntax?

    • CertDepot says:

      You are perfectly right. I haven’t seen any such example and I don’t know where to find!
      Sorry.

    • Sam says:

      The simplest way to get the syntax is well the simplest, simply type help at the MariaDB or MySql command line.

      The syntax is not pretty but with a little practice easy to used.
      Example
      MariaDB : help table;

      Note: This is done from memory! It is easy to get confused using this method!

      • CertDepot says:

        I wasn’t aware of this feature.
        Thank you for your useful comment.

      • krz says:

        Thanks Sam, I was not aware neither. This is great help, because apart from the syntax, some commands have even examples.

        MariaDB [inventory]> help grant;
        Name: ‘GRANT’
        Description:
        Syntax:
        GRANT
        priv_type [(column_list)]
        [, priv_type [(column_list)]] …
        ON [object_type] priv_level
        TO user_specification [, user_specification] …
        [REQUIRE {NONE | ssl_option [[AND] ssl_option] …}]
        [WITH with_option …]

        GRANT PROXY ON user_specification
        TO user_specification [, user_specification] …
        [WITH GRANT OPTION]

        object_type:
        TABLE
        | FUNCTION
        | PROCEDURE

        priv_level:
        *
        | *.*
        | db_name.*
        | db_name.tbl_name
        | tbl_name
        | db_name.routine_name

        user_specification:
        user
        [
        IDENTIFIED BY [PASSWORD] ‘password’
        | IDENTIFIED WITH auth_plugin [AS ‘auth_string’]
        ]

        ssl_option:
        SSL
        | X509
        | CIPHER ‘cipher’
        | ISSUER ‘issuer’
        | SUBJECT ‘subject’

        with_option:
        GRANT OPTION
        | MAX_QUERIES_PER_HOUR count
        | MAX_UPDATES_PER_HOUR count
        | MAX_CONNECTIONS_PER_HOUR count
        | MAX_USER_CONNECTIONS count

        The GRANT statement grants privileges to MySQL user accounts. GRANT
        also serves to specify other account characteristics such as use of
        secure connections and limits on access to server resources. To use
        GRANT, you must have the GRANT OPTION privilege, and you must have the
        privileges that you are granting.

        Normally, a database administrator first uses CREATE USER to create an
        account, then GRANT to define its privileges and characteristics. For
        example:

        CREATE USER ‘jeffrey’@’localhost’ IDENTIFIED BY ‘mypass’;
        GRANT ALL ON db1.* TO ‘jeffrey’@’localhost’;
        GRANT SELECT ON db2.invoice TO ‘jeffrey’@’localhost’;
        GRANT USAGE ON *.* TO ‘jeffrey’@’localhost’ WITH MAX_QUERIES_PER_HOUR 90;

        However, if an account named in a GRANT statement does not already
        exist, GRANT may create it under the conditions described later in the
        discussion of the NO_AUTO_CREATE_USER SQL mode.

        The REVOKE statement is related to GRANT and enables administrators to
        remove account privileges. See [HELP REVOKE].

        When successfully executed from the mysql program, GRANT responds with
        Query OK, 0 rows affected. To determine what privileges result from the
        operation, use SHOW GRANTS. See [HELP SHOW GRANTS].

        URL: http://dev.mysql.com/doc/refman/5.5/en/grant.html

  2. sarwan kr. Sharma says:

    Guys, you are doing a tremendous work here. Thanks

  3. sarwan kr. Sharma says:

    I am facing a issue.
    ===============================================================
    MariaDB [(none)]> use tom;
    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 [tom]> show tables;
    +—————+
    | Tables_in_tom |
    +—————+
    | addresses |
    +—————+
    1 row in set (0.00 sec)

    MariaDB [tom]> show
    ->
    -> clear
    -> DON’T KNOW HOW TO GO BACK TO MariaDB [tom]> ……menu.
    ‘> help!!!!
    ================================================================

    Whenever I make any syntax mistake in MariaDB prompt, it takes me in ” -> ” prompt and I become unable to come out.
    One option that I try is to exit MariaDB prompt by pressing “ctrl+c” but it’s time consuming.
    I can’t find a solution in the article either.

    Pls help, if there is any other way to get out of this mess.

  4. maikeu says:

    I’ve been seeking some further sources for mariadb to help me get more comfortable with the syntax.

    I have found: A document in the /usr/share/doc/mariadb area (can’t find exact location) which confirms that the mysql manual couldn’t be distributed with mariadb for licencing reasons,

    but more on the bright side, another way to get a little help with syntax: the command “show create table tablename” will show you the exact command that would create one of the tables that is already present.
    That should hopefully be enough to get someone started 🙂 )

    https://www.safaribooksonline.com/library/view/learning-mysql-and/9781449362898/ch04.html

  5. POC says:

    For some help with SQL syntax, there are some example sql files installed by default under /usr/share/mysql which show basic syntax for create, insert drop and select statements.

Leave a Reply

Upcoming Events (Local Time)

There are no events.

RHCSA7: Task of the day

Allowed time: 10 minutes.
Create an EXT4 file system mounted under /vol based on a logical volume of 100MB.
Reduce the size to 60MB.

RHCE7: Task of the day

Allowed time: 10 minutes.
Set up a httpd virtual server called "dummy" with DocumentRoot assigned to /opt.

Follow me on Twitter

Poll for favorite RHEL 7 book

What is your favorite RHEL 7 book to prepare RHCSA & RHCE exams?

View Results

Loading ... Loading ...

Poll for most difficult RHCSA 7 topic

What do you think is the most difficult RHCSA 7 topic?

View Results

Loading ... Loading ...

Poll for most difficult RHCE 7 topic

What do you think is the most difficult RHCE 7 topic?

View Results

Loading ... Loading ...