Installing MariaDB 10.1 in Debian Jessie and Running Various MariaDB Queries

In our last article The Story Behind Acquisition of ‘MySQL’ and the Rise of ‘MariaDB’ was highly appreciated. In this article, we have already discussed the need to fork MySQL, the rise of MariaDB, Features of it, a comparative study of MariaDB and MySQL, Movement of some of the world’s renowned Corporates and Companies (Google, Wikipedia) from MySQL to MariaDB and a lot other technical and non-technical aspect of it.

Install MariaDB in Debian

Install MariaDB in Debian

Here we are going to Install MariaDB 10.1 on Debian Jessie (Testing) and will be testing it by creating small tables and running several queries in the process of learning and understanding.

Install MariaDB 10.1 on Debian Jessie

Under Debian systems, it’s highly recommended to install ‘python-software-properties‘ package, before heading up for the MariaDB installation from official repositories.

# apt-get install python-software-properties

Next, import and regtister the GPG key, which enables apt to verify the integrity of software’s it downloads.

# apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db

Now, add the following MariaDB official repository to your sources.list file, using the following command.

# add-apt-repository 'deb http://mariadb.biz.net.id//repo/10.1/debian sid main'

If adding repository throws error like “add-apt-repository: command not found”, you need to install ‘software-properties-common’ as shown below.

# apt-get install software-properties-common

Update the list of available Packages on the system.

# apt-get update

Finally, install MariaDB Server and Client, using the following commands.

# apt-get install mariadb-server mariadb-client
Set MariaDB Password

Set MariaDB Password

Confirm MariaDB Password

Confirm MariaDB Password

If installation goes smooth, check the version of Installed MariaDB.

# mysql -V 

mysql  Ver 15.1 Distrib 5.5.38-MariaDB, for debian-linux-gnu (x86_64) using readline 5.1

Login to MariaDB using root (Not Recommended), followed by password.

$ mysql -u root -p
Sample Output
Welcome to the MariaDB monitor.  Commands end with ; or \g. 
Your MariaDB connection id is 28 
Server version: 5.5.38-MariaDB-1 (Debian) 

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

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

MariaDB [(none)]>

NOTE: The ‘none’ above, it means no Database is selected currently.

Running Various MariaDB Queries

How to create a user in MariaDB. Use the following syntax to create a user in MariaDB.

CREATE USER 'USER_NAME' IDENTIFIED BY 'PASSWORD';

For example, to create User ‘sam‘ with password ‘sam123‘, we need to execute.

MariaDB [(none)]> CREATE USER 'sam' IDENTIFIED BY 'sam123'; 
Query OK, 0 rows affected (0.00 sec)

Now exit MariaDB and login using user sam.

$ mysql -u 'sam' -p 
Enter password: 

Welcome to the MariaDB monitor.  Commands end with ; or \g. 
Your MariaDB connection id is 36 
Server version: 5.5.38-MariaDB-1 (Debian) 

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

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

MariaDB [(none)]>

Delete/Drop MySQL user ‘sam’.

MariaDB [(none)]> DROP USER sam; 
Query OK, 0 rows affected (0.00 sec)

See all the available Database.

MariaDB [(none)]> SHOW DATABASES; 

+--------------------+ 
| Database           | 
+--------------------+ 
| information_schema | 
| mysql              | 
| performance_schema | 
+--------------------+ 
3 rows in set (0.04 sec)

NOTE: All the databases show above are used by MariaDB Internally. Don’t edit these databases unless you know what you are doing.

Select a Database from the list (Necessary to run Queries).

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 all the tables within the Database.

MariaDB [mysql]> SHOW TABLES; 

| Tables_in_mysql           | 
+---------------------------+ 
| columns_priv              | 
| db                        | 
| event                     | 
| func                      | 
| general_log               | 
| help_category             | 
| help_keyword              | 
| help_relation             | 
| help_topic                | 
.....
24 rows in set (0.00 sec)

See all the columns from a table say ‘user’ from Database ‘mysql’. Use either of the two query.

SHOW COLUMNS FROM user;

or 

DESCRIBE user;

The result of both the queries are same.

MariaDB [mysql]> describe user; 
+------------------------+-----------------------------------+------+-----+---------+-------+ 
| Field                  | Type                              | Null | Key | Default | Extra | 
+------------------------+-----------------------------------+------+-----+---------+-------+ 
| Host                   | char(60)                          | NO   | PRI |         |       | 
| User                   | char(16)                          | NO   | PRI |         |       | 
| Password               | char(41)                          | NO   |     |         |       | 
| Select_priv            | enum('N','Y')                     | NO   |     | N       |       | 
| Insert_priv            | enum('N','Y')                     | NO   |     | N       |       | 
| Update_priv            | enum('N','Y')                     | NO   |     | N       |       | 
| Delete_priv            | enum('N','Y')                     | NO   |     | N       |       | 
| Create_priv            | enum('N','Y')                     | NO   |     | N       |       | 
| Drop_priv              | enum('N','Y')                     | NO   |     | N       |       | 
.......
42 rows in set (0.01 sec)

See extensive server status information of MariaDB.

MariaDB [mysql]> SHOW STATUS; 
+------------------------------------------+----------------------+ 
| Variable_name                            | Value                | 
+------------------------------------------+----------------------+ 
| Aborted_clients                          | 0                    | 
| Aborted_connects                         | 0                    | 
| Access_denied_errors                     | 0                    | 
| Aria_pagecache_blocks_not_flushed        | 0                    | 
| Aria_pagecache_blocks_unused             | 15737                | 
| Aria_pagecache_blocks_used               | 2                    | 
| Aria_pagecache_read_requests             | 176                  | 
| Aria_pagecache_reads                     | 4                    | 
| Aria_pagecache_write_requests            | 8                    | 
....
419 rows in set (0.00 sec)

See the MariaDB statement that was used to create Database say ‘mysql’.

MariaDB [mysql]> SHOW CREATE DATABASE mysql; 
+----------+------------------------------------------------------------------+ 
| Database | Create Database                                                  | 
+----------+------------------------------------------------------------------+ 
| mysql    | CREATE DATABASE `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */ | 
+----------+------------------------------------------------------------------+ 
1 row in set (0.00 sec)

See the MariaDB statement that was used to create Table say ‘user’.

MariaDB [mysql]> SHOW CREATE TABLE user; 
+ 
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
+-------
| user  | CREATE TABLE `user` ( 
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '', 
  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '', 
  `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', 
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', 
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', 
....

See the security rights granted to a/all MariaDB user.

MariaDB [mysql]> SHOW GRANTS; 
+----------------------------------------------------------------------------------------------------------------------------------------+ 
| Grants for [email protected]                                                                                                              | 
+----------------------------------------------------------------------------------------------------------------------------------------+ 
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*698vsgfkemhvjh7txyD863DFF63A6bdfj8349659232234bs3bk5DC1412A' WITH GRANT OPTION | 
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           | 
+----------------------------------------------------------------------------------------------------------------------------------------+ 
2 rows in set (0.00 sec)

See the WARNINGS of MariaDB server.

MariaDB [mysql]> SHOW WARNINGS; 
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
| Level | Code |Message                                                                                                                                                      | 
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
| Error | 1064 | You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ON mysql' at line 1 | 
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
1 row in set (0.00 sec)

See Errors of MariaDB serve.

MariaDB [mysql]> SHOW ERRORS; 

+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
| Level | Code | Message                                                                                                                                                      | 
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
| Error | 1064 | You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ON mysql' at line 1 | 
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
1 row in set (0.00 sec)

That’s all for Now. The ‘SHOW‘ Statement has a lot of features, which we will be discussing in the future article along with other queries to be run on MariaDB to get desired result. Till then stay tuned and connected to Tecmint. Don’t forget to provide us with your valuable feedback in the comment section below.

If You Appreciate What We Do Here On TecMint, You Should Consider:

TecMint is the fastest growing and most trusted community site for any kind of Linux Articles, Guides and Books on the web. Millions of people visit TecMint! to search or browse the thousands of published articles available FREELY to all.

If you like what you are reading, please consider buying us a coffee ( or 2 ) as a token of appreciation.

Support Us

We are thankful for your never ending support.

RedHat RHCE and RHCSA Certification Book
Linux Foundation LFCS and LFCE Certification Preparation Guide

You may also like...

8 Responses

  1. gibbonyang says:

    vi /etc/apt/sources.list, add the following two lines:

    deb http://mariadb.biz.net.id//repo/10.1/debian jessie main
    deb-src http://mariadb.biz.net.id//repo/10.1/debian jessie main

  2. gibbonyang says:

    add-apt-repository ‘deb http://mariadb.biz.net.id//repo/10.1/debian jessie main’
    —— it works for me:
    Accept this solution? [Y/n/q/?] n
    The following actions will resolve these dependencies:

    Install the following packages:
    1) galera-3 [25.3.19-jessie ()]
    2) libmariadbclient18 [10.1.20+maria-1~jessie ()]
    3) libmysqlclient18 [10.1.20+maria-1~jessie ()]
    4) mariadb-client-core-10.1 [10.1.20+maria-1~jessie ()]
    5) mariadb-server-core-10.1 [10.1.20+maria-1~jessie ()]

    Keep the following packages at their current version:
    6) mariadb-client [Not Installed]
    7) mariadb-client-10.1 [Not Installed]
    8) mariadb-server [Not Installed]
    9) mariadb-server-10.1 [Not Installed]

  3. Brus says:

    Followed above process to install MariaDB in raspberry pi(OS Debian Jessie) after,

    # apt-get install mariadb-server mariadb-client
    

    I checked mysql -v and mysql --version but gives error mysql: command not found

  4. Krzysztof says:

    In this line: “# add-apt-repository ‘deb http://mariadb.biz.net.id//repo/10.1/debian sid main'” there should be “jessie” not “sid”
    Sid makes dependencies errors trying to install mariadb-server and mariadb-client with apt-get.

    • Ravi Saive says:

      Krzy,

      Thanks for a tip, but to verify it have you tried in Jessie? If yes, could you share the screenshot where Sid makes dependencies errors while installing MariaDB?

      • Krzysztof says:

        Unfortunately I can’t give you screenshots because it’s a past now. I was installing mariadb on Debian 8 for my client and apt-get refused to install mariadb because there were not compatible version of packets. Apt-get displayed only that those missing/bad version packets couldn’t be installed. Searching for this errors gave me links with resolving different, newer in Linux distribution than in mariadb repo, issues. This gave me a clue, that maybe changing “sid” to “jessie” in apt-get’s sources.list will be a resolution and I’ve had right. After that installation went without problems.

        • Ravi Saive says:

          @Kzyho,

          Thanks for detailed information, let me give a try on Debian 8 and see whether it still gives the same error and force me to change from sid to jessie, will update you my findings..

Leave a Reply to Krzysztof Cancel reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.