[  Overview |  Setting up users    |    Creating a database   ]

Last modified April 11, 2020

MariaDB

LAMP Configuration

This site has been automatically translated with Google Translate from this original page written in french, there may be some translation errors

Presentation

For a MariaDB installation with full compilation see the corresponding page.

We will assume that MariaDB is already installed and configured (initialized database and defined root password) by going through the package of your preferred distribution or by compiling using the sources see my apache+PHP+MariaDB installation page for this.

Setting up users

/usr/local/mysql/bin/mysql -u root -p mysql
Enter password:

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 25
Server version: 10.5.3-MariaDB Source distribution

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

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

MariaDB [(mysql)]>

Now we will enter the user olivier who will be a super user with the same rights as root:

MariaDB [(mysql)] > GRANT ALL PRIVILEGES ON *.* TO 'olivier'@'localhost'
         > IDENTIFIED BY 'password' WITH GRANT OPTION;

to create a user toto who will only have access to the test database with certain functions listed below from the asterix.fr machine, type

MariaDB [(mysql)] > GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
            ON test.*
            TO toto@asterix.fr
            IDENTIFIED by 'password';
             
if you type

MariaDB [(mysql)] > SELECT * FROM user;

you see each of the rights assigned to your users, you also have the possibility to specify the rights according to these columns with this command

MariaDB [(mysql)] > INSERT INTO user
        -> VALUES('localhost','olivier',PASSWORD('password'),
        -> 'Y','Y','Y','Y','Y','Y',' (...) Y','Y','Y','Y','Y','Y','Y', Y','Y','Y','Y','Y', ' ',' ',' ',' ','0','0','0' ,'0'); Query OK, 1 row affected (0.00 sec)

The number of fields varies depending on the mysql version.

NOTE It is not mandatory to enter the login for the login username and password.

Now to take all this into account

MariaDB [(mysql)] > FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.08 sec)

To leave

MariaDB [(mysql)] >quit

Creating tables

Now our user olivier will create a table that we will use later for our experiments with Apache. He must first connect:

/usr/local/mysql/bin/mysql -u olivier -p
Enter password:

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 25
Server version: 10.5.3-MariaDB Source distribution

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

Reading table information for completion of table and column names

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

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

MariaDB [(none)]>

To see the list of available databases, type:

MariaDB [(none)] > SHOW DATABASES;

+-------------------+
| Database |
+-------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+-------------------+
4 rows in set (0.00 sec)

We will now create a test database :

MariaDB [(none)] > CREATE DATABASE try;
Query OK, 1 row affected (0.00 sec)

We will now use this database

MariaDB [(none)] > USE test
Database changed

As the base has just been created, it does not contain any tables, to be convinced of this, simply type:

MariaDB [(test)] > SHOW TABLES;
Empty set (0.00 sec)

For our first Apache+PHP+MySQL example , we will create the following table:

MariaDB [(test)] > CREATE TABLE coord (
    -> name VARCHAR(20),
    -> firstname VARCHAR(20),
    -> email VARCHAR(30)
    -> );
Query OK, 0 rows affected (0.03 sec)

Now let's take a look at the available tables:

MariaDB [(test)] > SHOW TABLES;
+---------------------+
| Tables in test |
+---------------------+
| coordinate |
+----------------------+
1 row in set (0.00 sec)

The newly created table appears correctly. To get the details of this table, type:

MariaDB [(test)] > DESCRIBE coord;
+---------------+--------------+--------+------+-----------+------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+--------+------+-----------+------+
| noun | varchar(20) | YES | | NULL | |
| first name | varchar(20) | YES | | NULL | |
| email | varchar(30) | YES | | NULL | |
+---------------+--------------+--------+------+-----------+------+
3 rows in set (0.00 sec)

For our second Apache+PHP+MariaDB example , we will create the following table:

 MariaDB [(try)] > CREATE TABLE ref (
         -> date VARCHAR(20),
         -> host VARCHAR(20),
        -> ip VARCHAR(15),
        -> os VARCHAR(20),
        -> page VARCHAR(30)
        -> );
Query OK, 0 rows affected (0.05 sec)  

It will contain information about the visitors of the site. Now to exit simply type quit.

Now refer to the Apache page and the corresponding paragraph for the manipulation of these tables with Apache+PHP.

[ Back to FUNIX home page ]

[ Back to top of page ]