User Tools

Site Tools


bdnog5:track2agenda:mariadb-lab

Database Administration with MariaDB

This tutorial will guide you through the deployment process of MariaDB on Ubuntu. We’ll start by configuring the hardware and then move into the installation and configuration of MariaDB.

MariaDB is a fork of the very popular and open source MySQL database, which is now owned by Oracle. In fact, the two were created by the same individual. They are essentially a mirror of each other, so a lot of the knowledge used to run MySQL can be used for MariaDB. This should make migration easier to swallow.

Install MariaDB

Beginning with Ubuntu 14, MariaDB is now found in the package archive.

Download the required packages to start the installation process.

# sudo apt-get install MariaDB-server

You will be prompted to set a password for the root account. Type in a password and then press Enter. MariaDB – Set Root Password

FIG1 – MariaDB – Set Root Password

When prompted, re-enter the password to verify it. After setting the password for root, the installation will complete.

Securing the Installation

The default installation includes settings and accounts that are good for testing, but they will make your server a fairly large security target.

One example is the Root database account – it has no set password. Anyone can access your databases just knowing this account name. Thankfully, much like MySQL, we can run a script that walks us through closing these security concerns.

  1. Run the secure installation script. MariaDB must be running before this script can be executed.
    # /usr/bin/mysql_secure_installation
  2. You are first prompted to enter the password for root. We set this during installation, so you may press ‘N’ and then Enter to proceed without changing it.
  3. A prompt to change the password for root will appear. Press ‘Y’ and then Enter to set one.
  4. Next you will be prompted to remove anonymous users. Press ‘Y’ and then Enter to do so,
  5. When asked to disallow root remote login, press ‘Y’ and then Enter. Your root account should never have remote access.
  6. When prompted to remove the test database, press ‘Y’ and then Enter.
  7. Finally, you will be asked to reload the privileges table. Press ‘Y’ and then Enter. This will flush out the old permissions to apply the new ones.

Logging into MariaDB

The administer the server and create databases we need to log in. To do this we use the following command.

mysql -u <username> -p

The -u switch tells MariaDB which user account to log in with, and the -p switch tells it to prompt us for a password. To log in as root, we would do the following.

mysql -u root -p

Allowing Remote Connections

The default installation of MariaDB on Ubuntu only listens for connections on the local loopback interfaces. This means only services running locally on the host can access the database. This is typical for a LAMP configuration.

When deploying your application and database onto different hosts, we need to configure MariaDB to listen on a network interface accessible from the network.

  1. Open the MariaDB configuration file into a text editor.
    # vi /etc/mysql/my.cnf
  2. Look for the following line
    bind-address: 127.0.0.1
  3. Modify it so that the address is that of a network interface connected to your network.
    bind-address: 192.168.30.<Your Host IP>

    Save your changes and exit the text editor.

  4. Restart MariaDB to apply the change.
    # sudo service mysql restart

Check which port MariaDB server is listening

In this section we will use two command netstat to find the listening port by MariaDB server. As we know, service name is mysql. We will use the netstat command here. By default MariaDB server use the port 3306

netstat -tanp | grep 3306

Working with Databases

In the following we will learn how to create tables.database, permit users, use SQL statements and drop tables/databases.

As mentioned earlier please login to MariaDB monitor. You should get something like the following.

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

Your MariaDB connection id is 4

Server version: 5.5.33a-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)]>
  1. See all databases that are available to work with
    MariaDB [(none)]> show databases;
    
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | test               |
    +--------------------+
    4 rows in set (0.00 sec)
  2. Create a new database
    MariaDB [(none)]> create database student;
    
    Query OK, 1 row affected (0.00 sec)
  3. Use the database
    MariaDB [(none)]> use student;
    
    Database changed
  4. Create a new Table
    MariaDB [student]> create table stud (Rollno int, Name varchar(20), Track varchar(20), Email varchar(20));
    
    Query OK, 0 rows affected (0.01 sec)
  5. Insert Data into tables
    MariaDB [student]> insert into stud (Rollno,Name,Track,Email) values (1,'John','1', '[email protected]');
    
    Query OK, 1 row affected (0.01 sec)
    
    MariaDB [student]> insert into stud (Rollno,Name,Track,Email) values (2,'Bob','2', '[email protected]');
    
    Query OK, 1 row affected (0.00 sec)
    
    MariaDB [student]> insert into stud (Rollno,Name,Track,Email) values (3,'Mary','1', '[email protected]');
    
    Query OK, 1 row affected (0.00 sec)
    
    MariaDB [student]> insert into stud (Rollno,Name,Track,Email) values (4,'Will','2', '[email protected]');
    
    Query OK, 1 row affected (0.00 sec)
  6. Show contents of a table
    MariaDB [student]> select * from stud;
    
    +--------+--------+----------+-------------------+
    | Rollno | Name   | Track    | Email             |
    +--------+--------+----------+-------------------+
    |      1 | John   | 1        | [email protected] |
    |      2 | Bob    | 2        | [email protected]  |
    |      3 | Mary   | 1        | [email protected] |
    |      4 | Will   | 2        | [email protected] |
    +--------+--------+----------+-------------------+
    4 rows in set (0.00 sec)
  7. Update a record in a Table
    MariaDB [student]> update stud set Track='1' where Rollno=2; 
    
    Query OK, 1 row affected (0.03 sec)
    
    Rows matched: 1  Changed: 1  Warnings: 0
  8. See the change
    MariaDB [student]> select * from stud;
    
    +--------+--------+----------+-------------------+
    | Rollno | Name   | Track    | Email             |
    +--------+--------+----------+-------------------+
    |      1 | John   | 1        | [email protected] |
    |      2 | Bob    | 1        | [email protected]  |
    |      3 | Mary   | 1        | [email protected] |
    |      4 | Will   | 2        | [email protected] |
    +--------+--------+----------+-------------------+
    4 rows in set (0.00 sec)
  9. Delete a record from Table
    MariaDB [student]> delete from stud where Rollno=4;
    
    Query OK, 1 row affected (0.01 sec)
  10. See the change
    MariaDB [student]> select * from stud;
    
    +--------+--------+----------+-------------------+
    | Rollno | Name   | Track    | Email             |
    +--------+--------+----------+-------------------+
    |      1 | John   | 1        | [email protected] |
    |      2 | Bob    | 2        | [email protected]  |
    |      3 | Mary   | 1        | [email protected] |
    +--------+--------+----------+-------------------+
    3 rows in set (0.00 sec)
  11. Access Permission
    MariaDB [student]> grant all on *.* to 'userall'@'%' identified by '';
    
    Query OK, 0 rows affected (0.01 sec)
  12. Display User creation
    MariaDB [student]> select user, password ,host from mysql.user;
    
    +---------+----------+-----------------------+
    | user    | password | host                  |
    +---------+----------+-----------------------+
    | root    |          | localhost             |
    | root    |          | localhost.localdomain |
    | root    |          | 127.0.0.1             |
    | root    |          | ::1                   |
    |         |          | localhost             |
    |         |          | localhost.localdomain |
    | userall |          | %                     |
    +---------+----------+-----------------------+
    7 rows in set (0.00 sec)
  13. Change mysql User Access Password
    MariaDB [student]> update mysql.user set password='sql' where user='userall';
    
    Query OK, 1 rows affected (0.00 sec)
    
    Rows matched: 1  Changed: 1  Warnings: 0
  14. Display changes
    MariaDB [student]> select user, password ,host from mysql.user;
    
    +---------+----------+-----------------------+
    | user    | password | host                  |
    +---------+---------+-----------------------+
    | root    |          | localhost             |
    | root    |          | localhost.localdomain |
    | root    |          | 127.0.0.1             |
    | root    |          | ::1                   |
    |         |          | localhost             |
    |         |          | localhost.localdomain |
    | userall | sql      | %                     |
    +---------+----------+-----------------------+
    7 rows in set (0.00 sec)
  15. Access Permission to specific databases
    MariaDB [student]> grant all on student.* to 'stud'@'%' identified by 'student';
    
    Query OK, 0 rows affected (0.01 sec)
  16. Test access
    # mysql -u stud -p
    Enter Password:
    
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    
    Your MariaDB connection id is 4
    
    Server version: 5.5.33a-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)]> show databases;
    
    +----------+
    | Database |
    +----------+
    | student  |
    +----------+
    1 rows in set (0.00 sec)
  17. Database modification from file. Before proceeding to next practice please drop the database 'student'. Create a file with the following contents.
    # vi class.sql
    create database student;
    use student;
    create table stud (Rollno int, Name varchar(20), Track varchar(20), Email varchar(20));
    insert into stud (Rollno,Name,Track,Email) values (1,'John','1', '[email protected]');
    insert into stud (Rollno,Name,Track,Email) values (2,'Bob','2', '[email protected]');
    insert into stud (Rollno,Name,Track,Email) values (3,'Mary','1', '[email protected]');
    insert into stud (Rollno,Name,Track,Email) values (4,'Will','2', '[email protected]');
  18. Insert the code from command line
    # mysql -u root -p < class.sql
    Enter Password:
  19. Check the database
    MariaDB [(none)]> show databases;
    
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | test               |
    | student            |
    +--------------------+
    4 rows in set (0.00 sec)
    
    MariaDB [(none)]> use student;
    
    Database changed
    
    MariaDB [(none)]> show tables;
    +--------------------+
    | Tables             |
    +--------------------+
    | stud               |
    +--------------------+
    
    MariaDB [(none)]> select * from stud;
    +--------+--------+----------+-------------------+
    | Rollno | Name   | Track    | Email             |
    +--------+--------+----------+-------------------+
    |      1 | John   | 1        | [email protected] |
    |      2 | Bob    | 2        | [email protected]  |
    |      3 | Mary   | 1        | [email protected] |
    |      4 | Will   | 2        | [email protected] |
    +--------+--------+----------+-------------------+
    4 rows in set (0.00 sec)
bdnog5/track2agenda/mariadb-lab.txt · Last modified: 2016/04/07 01:13 by Muhammad Moinur Rahman