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.

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

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 root@localhost                                                                                                              | 
+----------------------------------------------------------------------------------------------------------------------------------------+ 
| 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.