Year: 2019

Database is a structured set of data stored electronically. The concept of database was known to our ancestors even when there were no computers, however creating and maintaining such database was very tedious job. In a manual database say of 100 pages, if you have to search for all the employees whose salary were less than 10k, just think how much difficult it would have been, then.

In today’s world you just can’t escape Database. Right now millions of database is working around the world to store and fetch data of every kind be it strategic data, employee record or web technologies.

Database is oftenly termed as back-end process, as because it is neither visible to end user nor End Userinteracts directly with the database. They works on front-end process viz., PHP, VB, ASP.NET, etc. and ask the front end to deal with database in back-end.

There are several database server and client available like Oracle, MySQL, MySQLi, MariaDB, MongoDB etc. The syntax of all of these are more or less the same. Mastering one means gaining control on most of them and learning the queries of a database is very easy and fun.

Lets start with simple queries on database. We will be using MySQL which comes bundled with most of the Linux distributions by default, you could install it manually from repository, if it is not installed by default in your case.

Well a database query is a simple piece of code that is sent to database to get custom and refined result, as required.

Install MySQL Database

Use “yum” or “apt” package manager to install MySQL Database.

# yum install mysql mysql-client mysql-server  (on Yum based Systems)

# apt-get install mysql mysql-client mysql-server (on Apt based Systems)

Start MySQL

Start MySQL database service as:

# service mysqld start
or
# service mysql start

Well installing a MySQL database will take you to the configuration where you are asked to setup adminpassword, etc. Once finished installing and starting the server go to your MySQL prompt.

# mysql -u root -p

Replace root with your configured username and enter password when prompted, if the login credential is correct, you will be at your MySQL prompt at the blink of your eyes.

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

Your MySQL connection id is 195 

Server version: 5.5.31-0+wheezy1 (Debian) 

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. 

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. 
Other names may be trademarks of their respective owners. 

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

mysql>

Now carrying out queries at this prompt is very educative and fun.

Create a database tecmint

mysql> create database tecmint ;
Query OK, 1 row affected (0.02 sec) 

mysql>

Note: It reports that the query was correct, means database is created. You can verify your newly created database as.

mysql> show databases; 
+--------------------+
| Database           | 
+--------------------+ 
| information_schema | 
| mysql              | 
| performance_schema | 
| tecmint            | 
| test               | 
+--------------------+ 
9 rows in set (0.00 sec) 
mysql>

Note: Notice your database in the above output.

Select Database

Now you need to select the database to work upon it.

mysql> use tecmint;
Database changed
mysql>

Create Tables in MySQL

Here we will be creating a table say “minttec” with three fields as:

mysql> CREATE TABLE minttec (
    -> id Int(3), 
    -> first_name Varchar (15), 
    -> email Varchar(20) 
    -> ); 
Query OK, 0 rows affected (0.08 sec) 
mysql>

Note: The above query says OK which means table was created without any error. To verify the table run the below query.

mysql> show tables; 
+-------------------+ 
| Tables_in_tecmint | 
+-------------------+ 
| minttec           | 
+-------------------+ 

1 row in set (0.00 sec) 

mysql>

Things are going fine till now. Yup! You can view the columns you created in the table “minttec” as:

mysql> show columns from minttec; 

+------------+-------------+------+-----+---------+-------+ 
| Field      | Type        | Null | Key | Default | Extra | 
+------------+-------------+------+-----+---------+-------+ 
| id         | int(3)      | YES  |     | NULL    |       | 
| first_name | varchar(15) | YES  |     | NULL    |       | 
| email      | varchar(20) | YES  |     | NULL    |       | 
+------------+-------------+------+-----+---------+-------+ 
3 rows in set (0.00 sec)

mysql>

It was nothing less than a magic. Anyway I will tell you about the types of declaration and their meaning.

1. Int is Integer
2. Varchar is char having variable length as defined. The value after Type is the length of field up-to which it can store data.

OK now we need to add a column say ‘last_name‘ after column ‘first_name‘.

mysql> ALTER TABLE minttec ADD last_name varchar (20) AFTER first_name; 
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

Now, verify it in your table.

mysql> show columns from minttec; 

+------------+-------------+------+-----+---------+-------+ 
| Field      | Type        | Null | Key | Default | Extra | 
+------------+-------------+------+-----+---------+-------+ 
| id         | int(3)      | YES  |     | NULL    |       | 
| first_name | varchar(15) | YES  |     | NULL    |       | 
| last_name  | varchar(20) | YES  |     | NULL    |       | 
| email      | varchar(20) | YES  |     | NULL    |       | 
+------------+-------------+------+-----+---------+-------+ 

4 rows in set (0.00 sec) 

mysql>

Add Column in MySQL

Now we will add a column to the right say a column ‘country‘ to the right of email.

mysql> ALTER TABLE minttec ADD country varchar (15) AFTER email; 
Query OK, 0 rows affected (0.16 sec) 
Records: 0  Duplicates: 0  Warnings: 0 

mysql>

Verify the above column insertion query.

mysql> show columns from minttec; 

+------------+-------------+------+-----+---------+-------+ 
| Field      | Type        | Null | Key | Default | Extra | 
+------------+-------------+------+-----+---------+-------+
| id         | int(3)      | YES  |     | NULL    |       | 
| first_name | varchar(15) | YES  |     | NULL    |       | 
| last_name  | varchar(20) | YES  |     | NULL    |       | 
| email      | varchar(20) | YES  |     | NULL    |       | 
| country    | varchar(15) | YES  |     | NULL    |       | 
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec) 

mysql>

Insert Values in Field

What about inserting values to the field?

mysql> INSERT INTO minttec VALUES ('1' , 'Ravi' , 'Saive' , 'raivsaive@xyz.com' , 'India' );
Query OK, 1 row affected (0.02 sec) 

mysql>

How about inserting more than 1 value at a time in the above table.

mysql> INSERT INTO minttec VALUES ('2' , 'Narad' , 'Shrestha' , 'narad@xyz.com' , 'India' ), ('3' , 'user' , 'singh' , 'user@xyz.com' , 'Aus' ), ('4' , 'tecmint' , '[dot]com' , 'tecmint@gmail.com' , 'India' );
Query OK, 3 rows affected (0.05 sec) 
Records: 3  Duplicates: 0  Warnings: 0

Verify the above insertion.

mysql> select * from minttec; 
+------+------------+-----------+-------------------+---------+ 
| id   | first_name | last_name | email             | country | 
+------+------------+-----------+-------------------+---------+ 
|    1 | Ravi	    | Saive     | raivsaive@xyz.com | India   | 
|    2 | Narad      | Shrestha  | narad@xyz.com     | India   | 
|    3 | user       | singh     | user@xyz.com      | Aus     | 
|    4 | tecmint    | [dot]com  | tecmint@gmail.com | India   | 
+------+------------+-----------+-------------------+---------+ 

4 rows in set (0.00 sec)

mysql>

Delete Values in Field

Let’s say the third entry in the above output is invalid and we need to delete the third entry.

mysql> DELETE FROM minttec WHERE id = 3;

Query OK, 1 row affected (0.02 sec)

Verify the above operation.

mysql> select * from minttec;

+------+------------+-----------+-------------------+---------+ 
| id   | first_name | last_name | email             | country | 
+------+------------+-----------+-------------------+---------+
|    1 | Ravi       | Saive     | raivsaive@xyz.com | India   | 
|    2 | Narad      | Shrestha  | narad@xyz.com     | India   | 
|    4 | tecmint    | [dot]com  | tecmint@gmail.com | India   | 
+------+------------+-----------+-------------------+---------+
3 rows in set (0.00 sec)

Update Values in Field

The id (=4) needs to be edited.

mysql> UPDATE minttec SET id = 3 WHERE first_name = 'tecmint'; 
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

Verify the above query.

mysql> UPDATE minttec SET id = 3 WHERE first_name = 'tecmint'; 
Query OK, 1 row affected (0.02 sec) 
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

Note: The above query, as performed is not a good idea. It will change the id to ‘4‘ where ever the first name is ‘tecmint’. It is always a good idea to use more than one column with where clause to get minimal error, as:

mysql> UPDATE minttec SET id = 6 WHERE first_name = 'tecmint'AND last_name = '[dot]com'; 
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

Delete Column in MySQL

Let we need to drop (delete) a column we think, is of no importance say ‘country‘ here.

mysql> ALTER TABLE minttec drop country; 
Query OK, 3 rows affected (0.15 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>

Verify the table.

mysql> select * from minttec; 

+------+------------+-----------+-------------------+ 
| id   | first_name | last_name | email             | 
+------+------------+-----------+-------------------+ 
|    1 | Ravi       | Saive     | raivsaive@xyz.com | 
|    2 | Narad      | Shrestha  | narad@xyz.com     | 
|    6 | tecmint    | [dot]com  | tecmint@gmail.com | 
+------+------------+-----------+-------------------+
3 rows in set (0.00 sec) 

mysql>

Rename Table in MySQL

Don’t you think our table name “minttec” is not very much relevant. How about changing it to tecmint_table.

mysql> RENAME TABLE minttec TO tecmint_table; 
Query OK, 0 rows affected (0.03 sec)

mysql>

List all Tables

See all the tables under the current database.

mysql> show tables; 

+-------------------+ 
| Tables_in_tecmint | 
+-------------------+ 
| tecmint_table     | 
+-------------------+
1 row in set (0.00 sec) 

mysql>

The table has been renamed. Now take a backup of the above MySQL database, in a single line of command without any sophisticated tool. Run the below code at your terminal and not on mysql prompt.

# mysqldump -u root -p tecmint > tecmint.sql

check the dumped file on your desktop which would have contents something like
-- MySQL dump 10.13  Distrib 5.5.31, for debian-linux-gnu (i686) --
-- Server version 5.5.31-0+wheezy1 -- 
Dump completed on 2013-09-02 12:55:37

It is always a good idea to maintain Backup of MySQL databases. Restoring the backed up MySQL Data is again a simple line of code you need to run at your terminal prompt and not at your mysql prompt.

But, wait first we will delete the database to verify if our restore is perfect.

Delete a Database

mysql> drop database tecmint; 
Query OK, 1 row affected (0.02 sec)

Check for database ‘tecmint’ on your database server.

mysql> show databases; 

+--------------------+ 
| Database           | 
+--------------------+ 
| information_schema | 
| my_database        | 
| mysql              | 
| performance_schema | 
| phpmyadmin         | 
| sisso              | 
| test               | 
+--------------------+

7 rows in set (0.00 sec) 
mysql>

Great! The database is lost, but we need not to worry, we are having the backup.

Restore a Database

To restore lost database, run the following command.

# mysql -u root -p tecmint < tecmint.sql
Enter password:
ERROR 1049 (42000): Unknown database 'tecmint'

OOPS! An error, hey we have not create the database tecmint. So go to your mysql prompt and create a database ‘tecmint‘.

mysql> create database tecmint; 
Query OK, 1 row affected (0.00 sec) 

mysql>

Now time to run restore command at your shell prompt (strictly).

# mysql -u root -p tecmint < tecmint.sql 
Enter password:

Verify your database.

mysql> show databases; 

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

Verify the contents of database.

mysql> show tables from tecmint;

+-------------------+ 
| Tables_in_tecmint | 
+-------------------+ 
| tecmint_table     | 
+-------------------+ 
1 row in set (0.00 sec)

mysql>

Verify the contents of your restored table.

mysql> select * from tecmint_table; 

+------+------------+-----------+-------------------+ 
| id   | first_name | last_name | email             | 
+------+------------+-----------+-------------------+ 
|    1 | Ravi       | Saive     | raivsaive@xyz.com | 
|    2 | Narad      | Shrestha  | narad@xyz.com     | 
|    6 | tecmint    | [dot]com  | tecmint@gmail.com | 
+------+------------+-----------+-------------------+

3 rows in set (0.00 sec)

This is not the end definitely, we will cover the concept of primary key, foreign key, multiple tables and running queries using simple PHP script in the next part of the article.

Source

This is the second part of a 2-article series about the essentials of MariaDB / MySQL commands. Please refer to our previous article on this topic before proceeding.

1. Learn MySQL/MariaDB Basics for Beginners – Part 1

In this second part of MySQL/MariaDB beginner series, we will explain how to limit the number of rows returned by a SELECT query, and how to order the result set based on a given condition.

Additionally, we will learn how to group the records and perform basic mathematical manipulation on numeric fields. All of this will help us to create a SQL script that we can use to produce useful reports.

Prerequisites

To begin, please follow these steps:

1. Download the employees sample database, which includes six tables consisting of 4 million records in total.

# wget https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2
# tar xjf employees_db-full-1.0.6.tar.bz2
# cd employees_db

2. Enter the MariaDB prompt and create a database named employees:

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

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

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

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

3. Import it into your MariaDB server as follows:

MariaDB [(none)]> source employees.sql

Wait 1-2 minutes until the sample database is loaded (keep in mind we’re talking about 4M records here!).

4. Verify that the database was imported correctly by listing its tables:

MariaDB [employees]> USE employees;
Database changed
MariaDB [employees]> SHOW TABLES;
+---------------------+
| Tables_in_employees |
+---------------------+
| departments         |
| dept_emp            |
| dept_manager        |
| employees           |
| salaries            |
| titles              |
+---------------------+
6 rows in set (0.02 sec)

5. Create a special account to use with the employees database (feel free to choose another account name and password):

MariaDB [employees]> CREATE USER empadmin@localhost IDENTIFIED BY 'empadminpass';
Query OK, 0 rows affected (0.03 sec)

MariaDB [employees]> GRANT ALL PRIVILEGES ON  employees.* to empadmin@localhost;
Query OK, 0 rows affected (0.02 sec)

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

MariaDB [employees]> exit
Bye

Now login as empadmin user into Mariadb prompt.

# mysql -u empadmin -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.1.14-MariaDB MariaDB Server

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

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

MariaDB [(none)]> USE employees;
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

Make sure all of the steps outlined in the above image have been completed before proceeding.

Ordering and Limiting the Number of Rows in the Result Set

The salaries table contains all the incomes of each employee with start and end dates. We may wish to view the salaries of emp_no=10001 over time. This will help answer the following questions:

1. Did he / she get any raises?
2. If so, when?

Execute the following query to find out:

MariaDB [employees]> SELECT * FROM salaries WHERE emp_no=10001 ORDER BY from_date;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  60117 | 1986-06-26 | 1987-06-26 |
|  10001 |  62102 | 1987-06-26 | 1988-06-25 |
|  10001 |  66074 | 1988-06-25 | 1989-06-25 |
|  10001 |  66596 | 1989-06-25 | 1990-06-25 |
|  10001 |  66961 | 1990-06-25 | 1991-06-25 |
|  10001 |  71046 | 1991-06-25 | 1992-06-24 |
|  10001 |  74333 | 1992-06-24 | 1993-06-24 |
|  10001 |  75286 | 1993-06-24 | 1994-06-24 |
|  10001 |  75994 | 1994-06-24 | 1995-06-24 |
|  10001 |  76884 | 1995-06-24 | 1996-06-23 |
|  10001 |  80013 | 1996-06-23 | 1997-06-23 |
|  10001 |  81025 | 1997-06-23 | 1998-06-23 |
|  10001 |  81097 | 1998-06-23 | 1999-06-23 |
|  10001 |  84917 | 1999-06-23 | 2000-06-22 |
|  10001 |  85112 | 2000-06-22 | 2001-06-22 |
|  10001 |  85097 | 2001-06-22 | 2002-06-22 |
|  10001 |  88958 | 2002-06-22 | 9999-01-01 |
+--------+--------+------------+------------+
17 rows in set (0.03 sec)

Now what if we need to view the latest 5 raises? We can do ORDER BY from_date DESC. The DESC keyword indicates that we want to sort the result set in descending order.

Additionally, LIMIT 5 allows us to return only the top 5 rows in the result set:

MariaDB [employees]> SELECT * FROM salaries WHERE emp_no=10001 ORDER BY from_date DESC LIMIT 5;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  88958 | 2002-06-22 | 9999-01-01 |
|  10001 |  85097 | 2001-06-22 | 2002-06-22 |
|  10001 |  85112 | 2000-06-22 | 2001-06-22 |
|  10001 |  84917 | 1999-06-23 | 2000-06-22 |
|  10001 |  81097 | 1998-06-23 | 1999-06-23 |
+--------+--------+------------+------------+
5 rows in set (0.00 sec)

You can also use ORDER BY with multiple fields. For example, the following query will order the result set based on the employee’s birth date in ascending form (the default) and then by the last names in alphabetical descending form:

MariaDB [employees]> SELECT CONCAT(last_name, ', ', first_name) AS Name, gender AS Gender,  hire_date AS "Hire date" FROM employees ORDER BY birth_date, last_name DESC LIMIT 10;
+--------------------+--------+------------+
| Name               | Gender | Hire date  |
+--------------------+--------+------------+
| Whitcomb, Kiyokazu | M      | 1988-07-26 |
| Schaad, Ronghao    | M      | 1988-07-10 |
| Remmele, Supot     | M      | 1989-01-27 |
| Pocchiola, Jouni   | M      | 1985-03-10 |
| Kuzuoka, Eishiro   | M      | 1992-02-12 |
| Decaestecker, Moni | M      | 1986-10-06 |
| Wiegley, Mircea    | M      | 1985-07-18 |
| Vendrig, Sachar    | M      | 1985-11-04 |
| Tsukuda, Cedric    | F      | 1993-12-12 |
| Tischendorf, Percy | M      | 1986-11-10 |
+--------------------+--------+------------+
10 rows in set (0.31 sec)

You can view more information about LIMIT here.

Grouping Records / MAX, MIN, AVG, and ROUND

As we mentioned earlier, the salaries table contains the incomes of each employee over time. Besides LIMIT, we can use the MAX and MIN keywords to determine when maximum and minimum number of employees were hired:

MariaDB [employees]> SELECT CONCAT(last_name, ', ', first_name) AS Name, MAX(B.salary) AS "Max. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
+-----------------+-------------+
| Name            | Max. salary |
+-----------------+-------------+
| Facello, Georgi |       88958 |
| Simmel, Bezalel |       72527 |
| Bamford, Parto  |       43699 |
+-----------------+-------------+
3 rows in set (0.02 sec)

MariaDB [employees]> SELECT CONCAT(last_name, ', ', first_name) AS Name, MIN(B.salary) AS "Min. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
+-----------------+-------------+
| Name            | Min. salary |
+-----------------+-------------+
| Facello, Georgi |       60117 |
| Simmel, Bezalel |       65828 |
| Bamford, Parto  |       40006 |
+-----------------+-------------+
3 rows in set (0.00 sec)

Based on the above result sets, can you guess what the below query will return?

MariaDB [employees]> SELECT CONCAT(last_name, ', ', first_name) AS Name, ROUND(AVG(B.salary), 2) AS "Avg. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
+-----------------+-------------+
| Name            | Avg. salary |
+-----------------+-------------+
| Facello, Georgi |    75388.94 |
| Simmel, Bezalel |    68854.50 |
| Bamford, Parto  |    43030.29 |
+-----------------+-------------+
3 rows in set (0.01 sec)

If you agree that it will return the average (as specified by AVG) salary over time rounded to 2 decimals (as indicated by ROUND), you’re right.

If we want to view the sum of the salaries grouped by employee and return the top 5, we can use the following query:

MariaDB [employees]> SELECT emp_no, SUM(salary) AS Salary FROM salaries GROUP BY emp_no ORDER BY Salary DESC LIMIT 5;
+--------+---------+
| emp_no | Salary  |
+--------+---------+
| 109334 | 2553036 |
|  43624 | 2492873 |
|  66793 | 2383923 |
| 237542 | 2381119 |
|  47978 | 2374024 |
+--------+---------+
5 rows in set (2.22 sec)

In the above query, salaries are grouped by employee and then the sum is performed.

Bringing it all Together

Fortunately, we don’t need to run query after query to produce a report. Instead, we can create a script with a series of SQL commands to return all the necessary result sets.

Once we execute the script, it will return the required information without further intervention on our part. For example, let’s create a file named maxminavg.sql in the current working directory with the following contents:

--Select database
USE employees;
--Calculate maximum salaries
SELECT CONCAT(last_name, ', ', first_name) AS Name, MAX(B.salary) AS "Max. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
--Calculate minimum salaries
SELECT CONCAT(last_name, ', ', first_name) AS Name, MIN(B.salary) AS "Min. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;
--Calculate averages, round to 2 decimal places
SELECT CONCAT(last_name, ', ', first_name) AS Name, ROUND(AVG(B.salary), 2) AS "Avg. salary" FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE A.emp_no IN (10001, 10002, 10003) GROUP BY A.emp_no;

Lines beginning with two dashes are ignored, and the individual queries are executed one after another. We can execute this script either from the Linux command line:

# mysql -u empadmin -p < maxminavg.sql
Enter password: 
Name	Max. salary
Facello, Georgi	88958
Simmel, Bezalel	72527
Bamford, Parto	43699
Name	Min. salary
Facello, Georgi	60117
Simmel, Bezalel	65828
Bamford, Parto	40006
Name	Avg. salary
Facello, Georgi	75388.94
Simmel, Bezalel	68854.50
Bamford, Parto	43030.29

or from the MariaDB prompt:

# mysql -u empadmin -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.1.14-MariaDB MariaDB Server

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

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

MariaDB [(none)]> source maxminavg.sql
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

Summary

In this article we have explained how to use several MariaDB functions in order to refine result sets returned by SELECT statements. Once they have defined, multiple individual queries can be inserted in a script to execute it more easily and to reduce the risk of human error.

Do you have any questions or suggestions about this article? Feel free to drop us a note using the comment form below. We look forward to hearing from you!

Source

In this article we will show how to create a database (also known as a schema), tables (with data types), and explain how to perform Data Manipulation Language (DML) operations with data on a MySQL / MariaDB server.

It is assumed that you have previously 1) installed the necessary packages on your Linux system, and 2)executed mysql_secure_installation to improve the database server’s security. If not, follow below guides to install MySQL/MariaDB server.

1. Install Latest MySQL Database in Linux Systems
2. Install Latest MariaDB Database in Linux Systems

For brevity, we will refer to MariaDB exclusively throughout this article, but the concepts and commands outlined here apply to MySQL as well.

Creating Databases, Tables, and Authorized Users

As you know, a database can be defined in simple terms as an organized collection of information. Particularly, MariaDB is a relational database management system (RDBMS) and uses the Structure Query Language to perform operations on databases. Additionally, keep in mind that MariaDB uses the terms database and schema interchangeably.

Creating a New Database

To create a new database named BooksDB, enter the MariaDB prompt with the following command (you’ll be prompted to enter the password for the root MariaDB user):

[root@TecMint ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.1.14-MariaDB MariaDB Server

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

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

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

MariaDB [(none)]> 

Once the database has been created, we need to create at least two tables on it. But first let’s explore the concept of data types.

Introducing MariaDB data Types

As we explained earlier, tables are database objects where we will keep persistent information. Each table consists of two or more fields (also known as columns) of a given data type (the type of information) that such field can store.

The most common data types in MariaDB are the following (you can consult the complete list in the official MariaDB online documentation):

Numeric:

1. BOOLEAN considers 0 as false and any other values as true.
2. TINYINT, if used with SIGNED, covers the range from -128 to 127, whereas the UNSIGNED range is 0 to 255.
3. SMALLINT, if used with SIGNED, covers the range from -32768 to 32767. The UNSIGNED range is 0 to 65535.
4. INT, if used with UNSIGNED, covers the range from 0 to 4294967295, and -2147483648 to 2147483647 otherwise.

Note: In TINYINT, SMALLINT, and INT, the default SIGNED is assumed.

DOUBLE(M, D), where M is the total number of digits and D is the number of digits after the decimal point, represents a double-precision floating-point number. If UNSIGNED is specified, negative values are not be allowed.

String:

1. VARCHAR(M) represents a string of variable length where M is the maximum allowed column length in bytes (65,535 in theory). In most cases, the number of bytes is identical to the number of characters, except for some characters that can take up as much as 3 bytes. For example, the Spanish letter ñ represents one character but takes up 2 bytes.
2. TEXT(M) represents a column with a maximum length of 65,535 characters. However, as it happens with VARCHAR(M), the actual maximum length is reduced if multi-byte characters are stored. If M is specified, the column is created as the smallest type that can store such number of characters.
3. MEDIUMTEXT(M) and LONGTEXT(M) are similar to TEXT(M), only that the maximum allowed lengths are 16,777,215 and 4,294,967,295 characters, respectively.

Date and Time:

1. DATE represents the date in YYYY-MM-DD format.
2. TIME represents the time in HH:MM:SS.sss format (hour, minutes, seconds, and milliseconds).
3. DATETIME is the combination of DATE and TIME in YYYY-MM-DD HH:MM:SS format.
4. TIMESTAMP is used to define the moment a row was added or updated.

After having reviewed these data types, you will be in a better position to determine which data type you need to assign to a given column in a table.

For example, a person’s name can easily fit into a VARCHAR(50), whereas a blog post will need a TEXT type (choose M as per your specific needs).

Creating Tables with Primary and Foreign Keys

Before we dive into creating tables, there are two fundamental concepts about relational databases that we need to review: primary and foreign keys.

A primary key contains a value that uniquely identifies each row, or record, in the table. On the other hand, a foreign key is used to create a link between the data in two tables, and to control the data that can be stored in the table where the foreign key is located. Both primary and foreign keys are generally INTs.

To illustrate, let’s use the BookstoreDB and create two tables named AuthorsTBL and BooksTBL as follows. The NOT NULL constraint indicates that the associated field requires a value other than NULL.

Also, AUTO_INCREMENT is used to increase by one the value of INT primary key columns when a new record is inserted into the table.

MariaDB [(none)]> USE BookstoreDB;

MariaDB [(none)]> CREATE TABLE AuthorsTBL (
AuthorID INT NOT NULL AUTO_INCREMENT,
AuthorName VARCHAR(100),
PRIMARY KEY(AuthorID)
);

MariaDB [(none)]> CREATE TABLE BooksTBL (
BookID INT NOT NULL AUTO_INCREMENT,
BookName VARCHAR(100) NOT NULL,
AuthorID INT NOT NULL,
BookPrice DECIMAL(6,2) NOT NULL,
BookLastUpdated TIMESTAMP,
BookIsAvailable BOOLEAN,
PRIMARY KEY(BookID),
FOREIGN KEY (AuthorID) REFERENCES AuthorsTBL(AuthorID)
);

MariaDB [(none)]> USE BookstoreDB;
Database changed
MariaDB [BookstoreDB]> CREATE TABLE AuthorsTBL (
    -> AuthorID INT NOT NULL AUTO_INCREMENT,
    -> AuthorName VARCHAR(100),
    -> PRIMARY KEY(AuthorID)
    -> );
Query OK, 0 rows affected (0.05 sec)

MariaDB [BookstoreDB]> CREATE TABLE BooksTBL (
    -> BookID INT NOT NULL AUTO_INCREMENT,
    -> BookName VARCHAR(100) NOT NULL,
    -> AuthorID INT NOT NULL,
    -> BookPrice DECIMAL(6,2) NOT NULL,
    -> BookLastUpdated TIMESTAMP,
    -> BookIsAvailable BOOLEAN,
    -> PRIMARY KEY(BookID),
    -> FOREIGN KEY (AuthorID) REFERENCES AuthorsTBL(AuthorID)
    -> );
Query OK, 0 rows affected (0.05 sec)

MariaDB [BookstoreDB]> 

Now we can go ahead and start inserting records into AuthorsTBL and BooksTBL.

Selecting, Inserting, Updating, and Deleting Rows

We will first populate the AuthorsTBL table. Why? Because we need to have values for AuthorID before inserting records into the BooksTBL.

Execute the following query from your MariaDB prompt:

MariaDB [BookstoreDB]> INSERT INTO AuthorsTBL (AuthorName) VALUES ('Agatha Christie'), ('Stephen King'), ('Paulo Coelho');

After that, we will select all records from AuthorsTBL. Remember we will need the AuthorID for each record to create the INSERT query for BooksTBL.

If you want to retrieve one record at a time, you can use a WHERE clause to indicate a condition that a row must meet to be returned. For example,

MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL WHERE AuthorName='Agatha Christie';

Alternatively, you can select all the records simultaneously:

MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL;

MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL WHERE AuthorName='Agatha Christie';
+----------+-----------------+
| AuthorID | AuthorName      |
+----------+-----------------+
|        1 | Agatha Christie |
+----------+-----------------+
1 row in set (0.00 sec)

MariaDB [BookstoreDB]> SELECT * FROM AuthorsTBL;
+----------+-----------------+
| AuthorID | AuthorName      |
+----------+-----------------+
|        1 | Agatha Christie |
|        2 | Stephen King    |
|        3 | Paulo Coelho    |
+----------+-----------------+
3 rows in set (0.00 sec)

MariaDB [BookstoreDB]>

Now let’s create the INSERT query for BooksTBL, using the corresponding AuthorID to match the author of each book. A value of 1 in BookIsAvailable indicates the book is in stock, 0 otherwise:

MariaDB [BookstoreDB]> INSERT INTO BooksTBL (BookName, AuthorID, BookPrice, BookIsAvailable)
VALUES ('And Then There Were None', 1, 14.95, 1),
('The Man in the Brown Suit', 1, 23.99, 1),
('The Stand', 2, 35.99, 1),
('Pet Sematary', 2, 17.95, 0),
('The Green Mile', 2, 29.99, 1),
('The Alchemist', 3, 25, 1),
('By the River Piedra I Sat Down and Wept', 3, 18.95, 0);

MariaDB [BookstoreDB]> INSERT INTO BooksTBL (BookName, AuthorID, BookPrice, BookIsAvailable)
    -> VALUES ('And Then There Were None', 1, 14.95, 1),
    -> ('The Man in the Brown Suit', 1, 23.99, 1),
    -> ('The Stand', 2, 35.99, 1),
    -> ('Pet Sematary', 2, 17.95, 0),
    -> ('The Green Mile', 2, 29.99, 1),
    -> ('The Alchemist', 3, 25, 1),
    -> ('By the River Piedra I Sat Down and Wept', 3, 18.95, 0);
Query OK, 7 rows affected (0.03 sec)
Records: 7  Duplicates: 0  Warnings: 0

At this point we will do a SELECT to see the records in BooksTBL. Then let’s UPDATE the price of “The Alchemist” by Paulo Coelho and SELECT that specific record again.

Note how the BookLastUpdated field now shows a different value. As we explained earlier, a TIMESTAMP field shows the value when the record was inserted or last modified.

MariaDB [BookstoreDB]> SELECT * FROM BooksTBL;
MariaDB [BookstoreDB]> UPDATE BooksTBL SET BookPrice=22.75 WHERE BookID=6;
MariaDB [BookstoreDB]> SELECT * FROM BooksTBL WHERE BookID=6;

MariaDB [BookstoreDB]> SELECT * FROM BooksTBL;
+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+
| BookID | BookName                                | AuthorID | BookPrice | BookLastUpdated     | BookIsAvailable |
+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+
|      1 | And Then There Were None                |        1 |     14.95 | 2016-10-01 23:31:41 |               1 |
|      2 | The Man in the Brown Suit               |        1 |     23.99 | 2016-10-01 23:31:41 |               1 |
|      3 | The Stand                               |        2 |     35.99 | 2016-10-01 23:31:41 |               1 |
|      4 | Pet Sematary                            |        2 |     17.95 | 2016-10-01 23:31:41 |               0 |
|      5 | The Green Mile                          |        2 |     29.99 | 2016-10-01 23:31:41 |               1 |
|      6 | The Alchemist                           |        3 |     25.00 | 2016-10-01 23:31:41 |               1 |
|      7 | By the River Piedra I Sat Down and Wept |        3 |     18.95 | 2016-10-01 23:31:41 |               0 |
+--------+-----------------------------------------+----------+-----------+---------------------+-----------------+
7 rows in set (0.00 sec)

MariaDB [BookstoreDB]> UPDATE BooksTBL SET BookPrice=22.75 WHERE BookID=6;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [BookstoreDB]> SELECT * FROM BooksTBL WHERE BookID=6;
+--------+---------------+----------+-----------+---------------------+-----------------+
| BookID | BookName      | AuthorID | BookPrice | BookLastUpdated     | BookIsAvailable |
+--------+---------------+----------+-----------+---------------------+-----------------+
|      6 | The Alchemist |        3 |     22.75 | 2016-10-01 23:35:00 |               1 |
+--------+---------------+----------+-----------+---------------------+-----------------+
1 row in set (0.00 sec)

MariaDB [BookstoreDB]> 

Although we won’t do it here, you can also delete a record if not used anymore. For example, suppose we want to delete “The Alchemist” from BooksTBL.

To do so, we will use the DELETE statement as follows:

MariaDB [BookstoreDB]> DELETE FROM BooksTBL WHERE BookID=6;

As in the case of UPDATE, it is a good idea to do a SELECT first in order to view the record(s) that may potentially be impacted by the DELETE.

Also, don’t forget to add the WHERE clause and a condition (BookID=6) to select the specific record to be removed. Otherwise, you run the risk of deleting all the rows in the table!

If you desire to concatenate two (or more) fields, you can use the CONCAT statement. For example, let’s say we want to return a result set that consist of one field with the book name and author in the form of “The Alchemist (Paulo Coelho)” and another column with the price.

This will require a JOIN between AuthorsTBL and BooksTBL on the common field shared by both tables (AuthorID):

MariaDB [BookstoreDB]> SELECT CONCAT(BooksTBL.BookName, ' (', AuthorsTBL.AuthorName, ')') AS Description, BooksTBL.BookPrice FROM AuthorsTBL JOIN BooksTBL ON AuthorsTBL.AuthorID = BooksTBL.AuthorID;

As we can see, CONCAT allows us to join multiple string expressions separated by commas. You’ll also noticed that we chose the alias Description to represent the result set of the concatenation.

The output of the above query is shown in the below image:

MariaDB [BookstoreDB]> SELECT CONCAT(BooksTBL.BookName, ' (', AuthorsTBL.AuthorName, ')') AS Description, BooksTBL.BookPrice FROM AuthorsTBL JOIN BooksTBL ON AuthorsTBL.AuthorID = BooksTBL.AuthorID;
+--------------------------------------------------------+-----------+
| Description                                            | BookPrice |
+--------------------------------------------------------+-----------+
| And Then There Were None (Agatha Christie)             |     14.95 |
| The Man in the Brown Suit (Agatha Christie)            |     23.99 |
| The Stand (Stephen King)                               |     35.99 |
| Pet Sematary (Stephen King)                            |     17.95 |
| The Green Mile (Stephen King)                          |     29.99 |
| The Alchemist (Paulo Coelho)                           |     25.00 |
| By the River Piedra I Sat Down and Wept (Paulo Coelho) |     18.95 |
+--------------------------------------------------------+-----------+
7 rows in set (0.00 sec)

Create User to Access the BookstoreDB Database

Using root to perform all DML operations in a database is a bad idea. To avoid this, we can create a new MariaDB user account (we’ll name it bookstoreuser) and assign all necessary permissions for BookstoreDB:

MariaDB [BookstoreDB]> CREATE USER bookstoreuser@localhost IDENTIFIED BY 'YourPasswordHere';
MariaDB [BookstoreDB]> GRANT ALL PRIVILEGES ON  BookstoreDB.* to bookstoreuser@localhost;
MariaDB [BookstoreDB]> FLUSH PRIVILEGES;

MariaDB [BookstoreDB]> CREATE USER bookstoreuser@localhost IDENTIFIED BY 'tecmint';
Query OK, 0 rows affected (0.00 sec)

MariaDB [BookstoreDB]> GRANT ALL PRIVILEGES ON  BookstoreDB.* to bookstoreuser@localhost;
Query OK, 0 rows affected (0.00 sec)

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

Having a dedicated, separate user for each database will prevent damages to the entire database should a single account become compromised.

Extra MySQL Tips

To clear up the MariaDB prompt, type the following command and press Enter:

MariaDB [BookstoreDB]> \! clear

To inspect the configuration of a given table, do:

MariaDB [BookstoreDB]> SELECT COLUMNS IN [TABLE NAME HERE ];

For example,

MariaDB [BookstoreDB]> SHOW COLUMNS IN BooksTBL;

MariaDB [BookstoreDB]> SHOW COLUMNS IN BooksTBL;
+-----------------+--------------+------+-----+-------------------+-----------------------------+
| Field           | Type         | Null | Key | Default           | Extra                       |
+-----------------+--------------+------+-----+-------------------+-----------------------------+
| BookID          | int(11)      | NO   | PRI | NULL              | auto_increment              |
| BookName        | varchar(100) | NO   |     | NULL              |                             |
| AuthorID        | int(11)      | NO   | MUL | NULL              |                             |
| BookPrice       | decimal(6,2) | NO   |     | NULL              |                             |
| BookLastUpdated | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| BookIsAvailable | tinyint(1)   | YES  |     | NULL              |                             |
+-----------------+--------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.02 sec)

A quick inspection reveals that the BookIsAvailable field admits NULL values. Since we don’t want to allow that, we’ll ALTER the table as follows:

MariaDB [BookstoreDB]> ALTER TABLE BooksTBL MODIFY BookIsAvailable BOOLEAN NOT NULL;

(Feel free to show the columns again – the highlighted YES in the above image should now be a NO).

Finally, to view all the databases on your server, do:

MariaDB [BookstoreDB]> SHOW DATABASES;
OR
MariaDB [BookstoreDB]> SHOW SCHEMAS;

[root@TecMint ~]# mysql -u bookstoreuser -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.1.14-MariaDB MariaDB Server

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

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

MariaDB [BookstoreDB]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| BookstoreDB        |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

MariaDB [BookstoreDB]> SHOW SCHEMAS;
+--------------------+
| Database           |
+--------------------+
| BookstoreDB        |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)

The following image shows the result of the above command after accessing the MariaDB prompt as the bookstoreuser (note how this account can’t “see” any databases other than BookstoreDB and information_schema (available for all users):

Summary

In this article we have explained how to run DML operations and how to create a database, tables, and dedicated users on a MariaDB database. Additionally, we shared a few tips that may make your life as a system / database administrator easier.

1. MySQL Database Administration Part – 1
2. MySQL Database Administration Part – 2
3. MySQL Performance Tunning and Optimization – Part 3

If you have any questions about this article, don’t hesitate to let us know! Feel free to use the comment form below to reach us.

Source

MySQL is a powerful open source Relational Database Management System or in short RDBMS. It was released back in 1995 (20 years old). It uses Structured Query Language which is probably the most popular choice for managing content within a database. The latest MySQL version is 5.6.25 and was released on 29 May 2015.

An interesting fact about MySQL is the fact that the name comes from Michael Widenius’s (MySQL’s creator) daughter My. Even though there are plenty of interesting facts about MySQL, this article is meant to show you some useful practices to help you manage your MySQL server.

In April 2009 the MySQL project was bought by Oracle. As a result a MySQL community fork called MariaDB was created. The main reason for creating the fork was to keep the project free under the General Public License.

Today MySQL and MariaDB are one of the most (if not the most) frequently used RDBMS used for web applications such as WordPress, Joomla, Magento and others.

This article will show you some basic, yet useful tips how to optimize the fine tune the performance of MySQL/MariaDB. Please keep in mind that this article assumes that you already have MySQL or MariaDB installed. If you are still wondering how to install them on your system, you can follow our extensive guides here:

1. Installing LAMP on RHEL/CentOS 7
2. Installing LAMP on Fedora 22
3. Setting Up LAMP on Ubuntu 15.04
4. Installing MariaDB on Debian 8
5. Install MariaDB on Gentoo Linux
6. Install MariaDB on Arch Linux

Important: Before we start – do not accept this suggestions blindly. Each MySQL setup is unique and requires additional thought, before making any changes.

Things you need to know:

1. MySQL/MariaDB configuration file is located in /etc/my.cnf. Every time you modify this file you will need to restart the MySQL service so the new changes can take effect.
2. For writing this article MySQL version 5.6 has been used as template.

1. Enable InnoDB file-per-table

First it is important to explain that InnoDB is a storage engine. MySQL and MariaDB use InnoDB as default storage engine. In the past MySQL used to keep database tables and indexes in a system tablespace. This approach was meant for servers which sole purpose is database processing and their storage disk is not used for any other purposes.

The InnoDB provides more flexible approach and each database information is kept in a .ibd data file. Each .ibd file represents a tablespace of its own. That way database operations such as “TRUNCATE” can be completed faster and you may also reclaim unused space when dropping or truncating a database table.

Another benefit of this configuration is the fact that you can keep some of the database tables in a separate storage device. This can greatly improve the I/O load on your disks.

The innodb_file_per_table is enabled by default in MySQL 5.6 and above. You can see that in /etc/my.cnf file. The directive looks like this:

innodb_file_per_table=1

2. Store MySQL Database Data on Separate Partition

Note: This setup only works with MySQL, but not with MariaDB.

Sometimes OS read/writes can slow down the performance of your MySQL server, especially if located on same hard drive. Instead, I would recommend using separate hard drive (preferably SSD) for the MySQL service.

To complete, this you will need to attach the new drive to your computer/server. For the purpose of this article, I will assume that the drive will be under /dev/sdb.

The next step is to prepare the new drive:

# fdisk /dev/sdb

Now press “n” to create new partition. Next press “p” to make the new partition primary. After that, set the partition number from 1-4. After that you will select the partition size. Press enter here. On the next step you will need to configure the size of the partition.

If you wish to use the entire disk press enter once more. Otherwise you can manually set the size of the new partition. When ready press “w” to write the changes. Now we will need to create a filesystem for our new partition. This can be easily done with:

# mkfs.ext4 /dev/sdb1

Now we will mount our new partition in a folder. I have named my folder “ssd” and created in the root directory:

# mkdir /ssd/

We are ready to mount the new partition we just made in the new folder:

# mount /dev/sdb1  /ssd/

You can perform the mount at startup by adding the following line in /etc/fstab file.

/dev/sdb1 /ssd ext3 defaults 0 0

Now you are ready to move MySQL to the new disk. First stop the MySQL service with:

# service mysqld stop

I would recommend you stopping Apache/nginx as well to prevent any attempts to write in the databases:

# service httpd stop
# service nginx stop

Now copy the entire MySQL directory in the new drive:

# cp /var/lib/mysql /ssd/ -Rp

This may take a while depending on the site of your MySQL databases. Once this process is complete rename the MySQL directory:

# mv /var/lib/mysql /var/lib/mysql-backup

Next we will create a symlink.

# ln -s /ssd/mysql /var/lib/mysql

Now you are ready to start your MySQL and web service:

# service mysqld start
# service httpd start
# service nginx start

At this point your MySQL databases will be accessed from the new drive.

3. Optimizing InnoDB buffer pool Usage

The InnoDB engine has a buffer pool used for caching data and indexes in memory. This of course will help your MySQL/MariaDB queries be executed significantly faster. Choosing the proper size here requires some very important decisions and good knowledge on your system’s memory consumption.

Here is what you need to consider:

1. How much memory you need for other processes. This includes your system processes, page tables, socket buffers.
2. Is your server dedicated for MySQL or you will be running other memory hungry services.

On a dedicated box, you would probably want to give about 60-70% of the memory to the innodb_buffer_pool_size. If you plan on running more services on a single box, you should re-consider the amount of memory you dedicate for your innodb_buffer_pool_size.

The value that you should edit in my.cnf is:

innodb_buffer_pool_size

4. Avoid Swappiness in MySQL

Swapping is process that occurs when system moves part of memory to a special disk space called “swap”. The event usually appears when your system runs out of physical memory and instead of freeing up some RAM, the system pushed the information into disk. As you might have guess the disk is much slower than your RAM.

By default the option is enabled:

# sysctl vm.swappiness 

vm.swappiness = 60

To disable swappiness, run the following command:

# sysctl -w vm.swappiness=0

5. Set MySQL Max Connections

The max_connections directive tells your server how many concurrent connections are permitted. The MySQL/MariaDB server allows the value given in max_connections + 1 for user with SUPER privileges. The connection is opened only for the time MySQL query is executed – after that it is closed and new connection can take its place.

Keep in mind that too many connections can cause high RAM usage and lock up your MySQL server. Usually small websites will require between 100-200 connections while larger may require 500-800 or even more. The value you apply here strongly depends on your particular MySQL/MariaDB usage.

You can dynamically change the value of max_connections, without having to restart the MySQL service by running:

# mysql -u root -p
mysql> set global max_connections := 300;

6. Configure MySQL thread_cache_size

The thread_cache_size directive sets the amount of threads that your server should cache. As the client disconnects, his threads are put in the cache if they are less than the thread_cache_size. Further requests are completed by using the threads stored in the cache.

To improve your performance you can set the thread_cache_size to a relatively high number. To find the thread cache hit rate, you can use the following technique:

mysql> show status like 'Threads_created';
mysql> show status like 'Connections';

Now use the following formula to calculate the thread cache hit rate percentage:

100 - ((Threads_created / Connections) * 100)

If you get a low number, it means that most of the new mysql connections are starting new thread instead of loading from cache. You will surely want to increase the thread_cache_size in such cases.

The good thing here is that the thread_cache_size can be dynamically changed without having to restart the MySQL service. You can achieve this by running:

mysql> set global thread_cache_size = 16;

7. Disable MySQL Reverse DNS Lookups

By default MySQL/MariaDB perform a DNS lookup of the user’s IP address/Hostname from which the connection is coming. For each client connection, the IP address is checked by resolving it to a host name. After that the host name is resolved back to an IP to verify that both match.

This unfortunately may cause delays in case of badly configured DNS or problems with DNS server. This is why you can disable the reverse DNS lookup by adding the following in your configuration file:

[mysqld]
# Skip reverse DNS lookup of clients
skip-name-resolve

You will have to restart the MySQL service after applying these changes.

8. Configure MySQL query_cache_size

If you have many repetitive queries and your data does not change often – use query cache. People often do not understand the concept behind the query_cache_size and set this value to gigabytes, which can actually cause degradation in the performance.

The reason behind that is the fact that threads need to lock the cache during updates. Usually value of 200-300 MB should be more than enough. If your website is relatively small, you can try giving the value of 64M and increase in time.

You will have to add the following settings in the MySQL configuration file:

query_cache_type = 1
query_cache_limit = 256K
query_cache_min_res_unit = 2k
query_cache_size = 80M

9. Configure tmp_table_size and max_heap_table_size

Both directives should have the same size and will help you prevent disk writes. The tmp_table_size is the maximum amount of size of internal in-memory tables. In case the limit in question is exceeded the table will be converted to on-disk MyISAM table.

[mysqld]
tmp_table_size= 64M
max_heap_table_size= 64M

10. Enable MySQL Slow query Logs

Logging slow queries can help you determine issues with your database and help you debug them. This can be easily enabled by adding the following values in your MySQL configuration file:

slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
long_query_time = 1

The first directive enables the logging of slow queries, while the second one tells MySQL where to store the actual log file. Use long_query_time to define the amount of time that is considered long for MySQL query to be completed.

11. Check for MySQL idle Connections

Idle connections consume resources and should be interrupted or refreshed when possible. Such connections are in “sleep” state and usually stay that way for long period of time. To look for idled connections you can run the following command:

# mysqladmin processlist -u root -p | grep “Sleep”

This will show you list of processes that are in sleep state. The event appears when the code is using persistent connection to the database. When using PHP this event can appear when using mysql_pconnect which opens the connection, after that executes queries, removes the authentication and leaves the connection open. This will cause any per-thread buffers to be kept in memory until the thread dies.

The first thing you would do here is to check the code and fix it. If you don’t have access to the code that is being ran, you can change the wait_timeout directive. The default value is 28800 seconds, while you can safely decrease it to something like 60:

wait_timeout=60

12. Choosing Right MySQL Filesystem

Choosing the right filesystem is vital for your databases. Most important things you need to consider here are – data integrity, performance and ease of administration.

As per MariaDB’s recommendations, the best file systems are XFS, Ext4 and Btrfs. All of them are enterprise journaling filesystems that can be used with very large files and large storage volumes.

Below you can find some useful information about the three filesystems:

The pros and cons of the Linux filesystems have been extensively covered in our article:

1. Linux Filesystem Explained

13. Set MySQL max_allowed_packet

MySQL splits data into packets. Usually a single packet is considered a row that is sent to a client. The max_allowed_packet directive defines the maximum size of packet that can be sent.

Setting this value too low can cause a query to stall and you will receive an error in your MySQL error log. It is recommended to set the value to the size of your largest packet.

14. Check MySQL Performance Tuning

Measuring your MySQL/MariaDB performance is something that you should do on regular basis. This will help you see if something in the resource usage changes or needs to be improved.

There are plenty of tools available for benchmarking, but I would like to suggest you one that is simple and easy to use. The tool is called mysqltuner.

To download and run it, use the following set of commands:

# wget https://github.com/major/MySQLTuner-perl/tarball/master
# tar xf master
# cd major-MySQLTuner-perl-993bc18/
# ./mysqltuner.pl 

You will receive a detailed report about your MySQL usage and recommendation tips. Here is a sample output of default MariaDB installation:

15. Optimize and Repair MySQL Databases

Sometimes MySQL/MariaDB database tables get crashed quite easily, especially when unexpected server shut down, sudden file system corruption or during copy operation, when database is still accessed. Surprisingly, there is a free open source tool called ‘mysqlcheck‘, which automatically check, repair and optimize databases of all tables in Linux.

# mysqlcheck -u root -p --auto-repair --check --optimize --all-databases
# mysqlcheck -u root -p --auto-repair --check --optimize databasename

That’s it! I hope you have found the above article useful and help you tune up your MySQL server. As always if you have any further questions or comments, please submit them in the comment section below.

Read Also:

1. 20 MySQL Commands for Database Administration
2. 4 Useful Tools to Monitor MySQL Performance
3. MySQL Backup and Restore Commands

Source

MySQL is the world’s most popular open source database system and MariaDB (a fork of MySQL) is the world’s fastest growing open source database system. After installing MySQL server, it is insecure in it’s default configuration, and securing it is one of the essential tasks in general database management.

Read Also: Learn MySQL/MariaDB for Beginners – Part 1

This will contribute to hardening and boosting of overall Linux server security, as attackers always scan vulnerabilities in any part of a system, and databases have in the past been key target areas. A common example is the brute-forcing of the root password for the MySQL database.

In this guide, we will explain useful MySQL/MariaDB security best practice for Linux.

1. Secure MySQL Installation

This is the first recommended step after installing MySQL server, towards securing the database server. This script facilitates in improving the security of your MySQL server by asking you to:

• set a password for the root account, if you didn’t set it during installation.
• disable remote root user login by removing root accounts that are accessible from outside the local host.
• remove anonymous-user accounts and test database which by default can be accessed by all users, even anonymous users.

# mysql_secure_installation

After running it, set the root password and answer the series of questions by entering [Yes/Y] and press [Enter].

2. Bind Database Server To Loopback Address

This configuration will restrict access from remote machines, it tells the MySQL server to only accept connections from within the localhost. You can set it in main configuration file.

# vi /etc/my.cnf	                   [RHEL/CentOS]	
# vi /etc/mysql/my.conf                    [Debian/Ubuntu] 
OR
# vi /etc/mysql/mysql.conf.d/mysqld.cnf    [Debian/Ubuntu] 

Add the following line below under [mysqld] section.

bind-address = 127.0.0.1

3. Disable LOCAL INFILE in MySQL

As part of security hardening, you need to disable local_infile to prevent access to the underlying filesystem from within MySQL using the following directive under [mysqld] section.

local-infile=0

4. Change MYSQL Default Port

The Port variable sets the MySQL port number that will be used to listen on TCP/ IP connections. The default port number is 3306 but you can change it under the [mysqld] section as shown.

Port=5000

5. Enable MySQL Logging

Logs are one of the best ways to understand what happens on a server, in case of any attacks, you can easily see any intrusion-related activities from log files. You can enable MySQL logging by adding the following variable under the [mysqld] section.

log=/var/log/mysql.log

6. Set Appropriate Permission on MySQL Files

Ensure that you have appropriate permissions set for all mysql server files and data directories. The /etc/my.conf file should only be writeable to root. This blocks other users from changing database server configurations.

# chmod 644 /etc/my.cnf

7. Delete MySQL Shell History

All commands you execute on MySQL shell are stored by the mysql client in a history file: ~/.mysql_history. This can be dangerous, because for any user accounts that you will create, all usernames and passwords typed on the shell will recorded in the history file.

# cat /dev/null > ~/.mysql_history

8. Don’t Run MySQL Commands from Commandline

As you already know, all commands you type on the terminal are stored in a history file, depending on the shell you are using (for example ~/.bash_history for bash). An attacker who manages to gain access to this history file can easily see any passwords recorded there.

It is strongly not recommended to type passwords on the command line, something like this:

# mysql -u root -ppassword_

When you check the last section of the command history file, you will see the password typed above.

# history 

The appropriate way to connect MySQL is.

# mysql -u root -p
Enter password:

9. Define Application-Specific Database Users

For each application running on the server, only give access to a user who is in charge of a database for a given application. For example, if you have a wordpress site, create a specific user for the wordpress site database as follows.

# mysql -u root -p
MariaDB [(none)]> CREATE DATABASE osclass_db;
MariaDB [(none)]> CREATE USER 'osclassdmin'@'localhost' IDENTIFIED BY 'osclass@dmin%!2';
MariaDB [(none)]> GRANT ALL PRIVILEGES ON osclass_db.* TO 'osclassdmin'@'localhost';
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> exit

and remember to always remove user accounts that are no longer managing any application database on the server.

10. Use Additional Security Plugins and Libraries

MySQL includes a number of security plugins for: authenticating attempts by clients to connect to mysql server, password-validation and securing storage for sensitive information, which are all available in the free version.

You can find more here: https://dev.mysql.com/doc/refman/5.7/en/security-plugins.html

11. Change MySQL Passwords Regularly

This is a common piece of information/application/system security advice. How often you do this will entirely depend on your internal security policy. However, it can prevent “snoopers” who might have been tracking your activity over an long period of time, from gaining access to your mysql server.

MariaDB [(none)]> USE mysql;
MariaDB [(none)]> UPDATE user SET password=PASSWORD('YourPasswordHere') WHERE User='root' AND Host = 'localhost';
MariaDB [(none)]> FLUSH PRIVILEGES;

12. Update MySQL Server Package Regularly

It is highly recommended to upgrade mysql/mariadb packages regularly to keep up with security updates and bug fixes, from the vendor’s repository. Normally packages in default operating system repositories are outdated.

# yum update
# apt update

After making any changes to the mysql/mariadb server, always restart the service.

# systemctl restart mariadb		#RHEL/CentOS
# systemctl restart mysql		#Debian/Ubuntu

Read Also: 15 Useful MySQL/MariaDB Performance Tuning and Optimization Tips

That’s all! We love to hear from you via the comment form below. Do share with us any MySQL/MariaDB security tips missing in the above list.

Source

Normally while installing MySQL/MariaDB database server on Linux, it’s recommended to set a MySQL root user password to secure it, and this password is required to access the database server with root user privileges.

Suggested Read: How to Install and Secure MariaDB 10 in CentOS 7

In this guide, we will show you how to connect and run MySQL commands without entering a password (mysql passwordless root login) on the Linux terminal.

How to Set MySQL Root Password

In case you have freshly installed the MySQL/MariaDB server, then it doesn’t require any password to connect to it as root user. To secure it, set the MySQL/MariaDB password for root user with the following command.

Note that this command is just one of the many MySQL (Mysqladmin) Commands for Database Administrationin Linux.

# mysqladmin -u root password YOURNEWPASSWORD

How to Connect or Run MySQL Without Root Password

To run MySQL commands without entering password on the terminal, you can store your user and password in the ~/.my.cnf user specific configuration file in user’s home directory as described below.

Now create the config file ~/.my.cnf and add configurations below in it (remember to replace mysqluser and mysqlpasswd with your own values).

[mysql]
user=user
password=password

Save and close the file. Then set the suitable permissions on it, to make it only readable and writable by you.

# chmod 0600 .my.cnf

Once you have set user and password in the Mysql configuration file, from now on when you run mysql commands such as mysql, mysqladmin etc, they will read the mysqluser and mysqlpasswd from the above file.

# mysql 
# mysql -u root 

You may also like to read these related articles about MySQL/MariaDB:

1. 1. 20 MySQL (Mysqladmin) Commands for Database Administration in Linux
   2. How to Change Root Password of MySQL or MariaDB in Linux
   3. How to Reset MySQL or MariaDB Root Password in Linux
   4. 15 Useful MySQL/MariaDB Performance Tuning and Optimization Tips
   5. 4 Useful Commandline Tools to Monitor MySQL Performance in Linux

In this guide, we showed how to run MySQL commands without entering root password on the terminal. If you have any queries or thoughts to share, do hit us up via the feedback form below.

Source

Following previous tutorials concerning Zabbix series, this article describes how to install and setup an instance of Zabbix agent to run as a service on Microsoft Windows systems in order to monitor your infrastructure windows environments, especially server machines.

Requirements

 Installing and Configuring Zabbix on Debian 8 and RHEL/CentOS 7 – Part 1

Related Guides

 How to Configure ‘Zabbix Monitoring’ to Send Email Alerts to Gmail Account – Part 2

 How to Install and Configure Zabbix Agents on Remote Linux Systems – Part 3

Step 1: Download and Install Zabbix Agent on Windows

1. The pre-compiled zip agents for Windows environments can be obtained from official Zabbix download page and manually installed and started on the system using windows Command Prompt as in the following example:

C:\Users\caezsar><full system path to zabbix_agentd.exe> --config <full system path to zabbix_agentd.win.conf> --install

Example, suppose you’ve downloaded and extracted the Zabbix agent zip archive to D:\Downloads\zabbix_agents_2.4.4.win\, run the following command to install the service:

C:\Users\caezsar>D:\Downloads\zabbix_agents_2.4.4.win\bin\win32\zabbix_agentd.exe --config D:\Downloads\zabbix_agents_2.4.4.win\conf\zabbix_agentd.win.conf --install

2. After the service has been installed on your windows host, open zabbix_agentd.win.conf file and manually edit the following parameters:

Server=IP of Zabbix Server
ServerActive=IP of Zabbix Server
Hostname=use the FQDN of your windows host

3. To start the service just type:

C:\Users\caezsar>D:\Downloads\zabbix_agents_2.4.4.win\bin\win32\zabbix_agentd.exe --start

To stop the service run the same command as above with --stop argument and to uninstall the service use the --uninstall argument.

C:\Users\caezsar>D:\Downloads\zabbix_agents_2.4.4.win\bin\win32\zabbix_agentd.exe --stop
C:\Users\caezsar>D:\Downloads\zabbix_agents_2.4.4.win\bin\win32\zabbix_agentd.exe --uninstall

4. A second and more convenient method to install and automatically configure Zabbix agent on Windows environments is by downloading Zabbix Agent installer msi package specific for your system architecture by visiting the link: http://www.suiviperf.com/zabbix/index.php.

5. Once the Zabbix agent msi file has been downloaded on your system, run it and supply the required information in order to configure and install the agent on the target monitored host as it follows:

Hostname: use the FQDN of your windows host (the hostname value should match the “Full Computer name” configured for your machine)
Zabbix server Name: use the IP of the Zabbix Server
Agent Port: 10050 
Remote Command: check this value
Active Server: IP of Zabbix Server

If you need to modify Zabbix configuration file with other custom values at a later date, the conf file can be found on %programfiles%\Zabbix Agent\ path.

6. After you’ve finished the setup, open a windows Command Prompt with Administrator privileges, run services.msc command in order to open Windows Services utility and locate Zabbix Agent service to check if the service is running and automatically started after reboot.

services.msc

From this console you can manage the service (start, stop, pause, resume, enable or disable).

Step 2: Configure Windows Firewall and Test Zabbix Agent

7. Almost all Windows based systems have Windows Firewall active and running, therefore zabbix agent port must be opened in firewall in order to communicate with zabbix server.

In order to open Zabbix agent port in windows firewall, open Control Panel -> System and Security – > Windows Firewall and hit on Allow an app through Windows Firewall.

8. Next, click on Allow another app button and a new window should open. Use the Browse button to navigate and add Zabbix agent executable file (usually found in %programfiles%\Zabbix Agent\ if you installed it using msi program), then hit on Add button to add the service.

9. Next, make sure you check and open the firewall rule on the network segment where zabbix server is located in your network and hit OK button to finish and apply configuration.

10. In order to test if Zabbix agent running on windows is reachable from Zabbix server side, use telnet or netcat command on zabbix server against windows agent IP-Port and a Connected message should appear. Hit Enter key to generate an error message and automatically disconnect from agent:

telnet <Windows_agent IP Address> 10050

Step 3: Add Zabbix Agent Monitored Windows Host to Zabbix Server

11. Once the windows agent has been tested from command line and everything looks fine, go to Zabbix Serverweb interface, move to Configuration tab -> Hosts and hit on Create Host button in order to add the Windows monitored host.

12. On Host window add the FQDN of your windows agent machine in Host name filed, add an arbitrary name to Visible name filed in order to easily identify the monitored machine on Zabbix panel, make sure the host is included into a Group Servers and add the IP Address of your windows host in Agent interfaces filed. The Portvalue leave it unchanged.

13. Next, go to Template tab and hit on Select button. A new window with Zabbix Templates should appear. Navigate through this window, check Template OS Windows and hit on Select button to add the template.

14. Once the Template OS Windows appears on Link new templates filed, hit on Add button in order to link this template to the windows host configuration.

Finally, after the Template OS Windows is visible in Linked Templates filed hit on below Add button to complete the process and add the entire Windows host configuration.

15. After your monitored windows machine has been added return to Configuration -> Hosts and the windows Host should now be present in this window as illustrated on below screenshot.

That’s all! Just assure that your windows host Status is set to Enabled and wait a few minutes in order for Zabbix server to contact the windows agent side and process the received remote data.

As an example, to get a graphical inside of the CPU load on the monitored Windows machine go to Zabbix web console Monitoring tab -> Graphs, select the windows machine host name and CPU load Graph and all the collected data so far should be presented into a nice graphical chart.

Continuing Zabbix series, this tutorial will guide you on how you can install and configure Zabbix agents on Linux (Debian based systems and CentOS) in order to actively monitor local resources on remote systems.

The main job of zabbix agents consists in gathering local information from the targets where they run and send the data to a central Zabbix server to be further processed and analyzed.

Requirements

Install and Configure Zabbix 2.4.5 on Debian 8 and RHEL/CentOS 7.

1. Setting Up Zabbix Monitoring on Debian and CentOS based Systems

Step 1: Install Zabbix Agents in Linux Systems

1. Depending on the Linux distribution you are running, go to Zabbix download page, grab the latest version of the available agents binary packages using a tool such as wget or curl and install it on your machine using the distribution specific package manager – Yum, Rpm or Dpkg.

On Debian based Systems

For Debian systems (including latest release – Debian 8 Jessie) use the following steps to download and install Zabbbix Agent:

$ wget http://repo.zabbix.com/zabbix/2.4/debian/pool/main/z/zabbix/zabbix-agent_2.4.0-1+wheezy_amd64.deb  
$ sudo dpkg -i zabbix-agent_2.4.0-1+wheezy_amd64.deb

On CentOS based Systems

For CentOS alike systems, download the .rpm packaged for the distribution specific release number, using the same page as above, and install it using rpm package manager.

In order to automatically manage missing dependency issues and install the agent using one shot use the yumcommand followed by the binary package download link, as in the example below used for installing the agent on CentOS 7:

# rpm -Uvh http://repo.zabbix.com/zabbix/2.4/rhel/7/x86_64/zabbix-agent-2.4.1-2.el7.x86_64.rpm

Step 2: Configure and Test Zabbix Agent in Linux

2. The next logical step after installing the packages on the system is to open Zabbix agent configuration file located in /etc/zabbix/ system path on both major distributions and instruct the program to send all the collected information to Zabbix server in order to be analyzed and processed.

Therefore, open zabbix_agentd.conf file with your favorite text editor, find the below lines (use the screenshots as a guide), uncomment them and make the following changes:

# nano /etc/zabbix/zabbix_agentd.conf

add zabbix server IP address and hostname as shown below.

Server=IP of Zabbix Server
ServerActive=IP of Zabbix Server
Hostname=use the FQDN of the node where the agent runs

3. Once you’ve finished editing the Zabbix agent configuration file with the required values, restart the daemon using the following command, then use netstat command to verify if the daemon has been started and operates on the specific port – 10050/tcp:

$ sudo systemctl restart zabbix-agent
$ sudo netstat -tulpn|grep zabbix

For older distributions use the service command to manage zabbix agent daemon:

$ sudo service zabbix-agent restart
$ sudo netstat -tulpn|grep zabbix

4. If your system is behind a firewall then you need to open 10050/tcp port on the system in order to reach through Zabbix server.

For Debian based systems, including Ubuntu, you can use ufw tool to open the port and on CentOS 7 you can use Firewalld utility to manage the firewall rules as the below examples:

$ sudo ufw allow 10050/tcp  [On Debian based systems]

$ sudo firewall-cmd --add-port=10050/tcp                [For centOS 7 on-fly rule]
$ sudo firewall-cmd --add-port=10050/tcp --permanent    [For centOS 7 permanent rule]

For older distributions such as centOS 6 or unmanaged firewalls through specific utilities use the powerful iptables command to open ports:

# iptables -A INPUT -p tcp -m tcp --dport 10050 -j ACCEPT

5. Finally, in order to test if you can reach Zabbix Agent from Zabbix Server, use Telnet command from Zabbix server machine to the IP addresses of the machines that run the agents, as illustrated below (don’t worry about the thrown error from agents):

# telnet zabbix_agent_IP 10050

Step 3: Add Zabbix Agent Monitored Host to Zabbix Server

6. On the next step it’s time to move to Zabbix server web console and start adding the hosts which run zabbix agent in order to be monitored by the server.

Go to Configuration -> Hosts -> Create Host -> Host tab and fill the Host name field with the FQDN of the monitored zabbix agent machine, use the same value as above for Visible name field.

Next, add this host to a group of monitored servers and use the IP Address of the monitored machine at Agent interfaces field – alternatively you can also use DNS resolution if it’s the case. Use the below screenshots as a guide.

7. Next, move to Templates tab and hit Select. A new window with templates should open. Choose Template OS Linux then scroll down and hit on Select button to add it and automatically close the window.

8. Once the template appears to Link new template box, hit on Add text to link it to zabbix server, then hit on the lower Add button to finish the process and completely add the monitored host. The visible name of the monitored host should now appear hosts window.

That’s all! Just assure that the host Status is set to Enabled and wait a few minutes in order for Zabbix server to contact the agent, process the received data and inform or eventually alert you if something goes bad on the monitored target.

Source

If you are using Zabbix to monitor your infrastructure you might want to receive email alerts from your local domain somewhere on public internet domain, even if you don’t own a valid registered internet domain name with a mail server which you can configure on your own.

This tutorial will briefly discuss you on how to setup Zabbix server to send mail reports to a Gmail address by using SSMTP program, without the need to install and configure any local MTA daemon, such as Postfix, Eximetc.

Requirements

1. Install Zabbix Monitoring Server

Step 1: Install and Configure SSMTP

1. SSMTP is a small software, which does not fulfill any of the functionality of a mail server, but only delivers emails from a local machine to an external email address on a mailhub.

To install SSMTP program alongside with mailutils package that you will use it to send mails, issue the following command on your RedHat and Debian like server:

# yum install ssmtp mailx                    [On RHEL/CentOS 7] 
$ sudo apt-get install ssmtp mailutils       [On Debian 8]

# vi /etc/ssmtp/ssmtp.conf                   [On RHEL/CentOS 7]
$ sudo nano /etc/ssmtp/ssmtp.conf            [On Debian 8]

SSMTP settings for GMAIL account

root=gmail-username@gmail.com
mailhub=smtp.gmail.com:587
rewriteDomain=your_local_domain
hostname=your_local_FQDN
UseTLS=Yes
UseSTARTTLS=Yes
AuthUser=Gmail_username
AuthPass=Gmail_password
FromLineOverride=YES

Step 2: Gmail Tests for Zabbix Email Alerts

3. On the next step it’s time to send a local generated email to Gmail account by issuing the below command.

# echo "Body test email from 'hostname -f' "| mail -s "subject here" gmail_user@gmail.com

4. Normally, Gmail prevents different types of authentications to their servers from your account, so, in case you get the error “mail: cannot send message: Process exited with non-zero status”, then login to your Gmail account from browser and navigate to the following link https://www.google.com/settings/security/lesssecureapps in order to allow access for less secure apps as in the following screen.

5. After you have turned on Less Secure Apps feature on your Gmail account, run the above mail command again and verify your Inbox after a few seconds to check if the locally generated email has been successfully delivered – you should normally see the email has incoming from Gmail.

Step 3: Configure Zabbix Sendmail Script

6. Further, based on the $(which mail) command create the following Bash script to Zabbix alertscriptsdirectory with the following content and give it execute permissions:

# vi /usr/local/share/zabbix/alertscripts/zabbix-sendmail            [On RHEL/CentOS 7]
$ sudo nano /usr/local/share/zabbix/alertscripts/zabbix-sendmail     [On Debian 8]

Script content:

#!/bin/bash
echo "$3" | /usr/bin/mail -s "$2" $1

Next, set the execute permission on the script file.

# chmod +x /usr/local/share/zabbix/alertscripts/zabbix-sendmail

7. Next, as previously, test the script functionality by sending a local email to Gmail account. The way to run the script with positional parameters is explained above:

# /usr/local/share/zabbix/alertscripts/zabbix-sendmail gmail_username@gmail.com "Subject here" "Body of the message here"

Afterwards, verify Gmail Inbox and check if the new local message has arrived.

Step 4: Configure Zabbix to Send Alerts to Gmail

8. If the tests so far were successful, then you can move to next step and setup Zabbix to send generated email alerts to Gmail. First, login to Zabbix web interface and navigate to the following menu: Administration -> Media types -> Create media type.

9. On the next screen enter an arbitrary Name to uniquely identify for the script in the Zabbix configurations (in this example Send-Email-Script is used), choose Script as Type from the list and enter the name of the Bash script created earlier (zabbix-sendmail used in this tutorial) to send email from command line (don’t use the path for the script, only the script name). When you’re done, hit the Add button below to reflect changes.

10. Further, let’s configure an email address to which you will send Zabbix alerts. Go to Profile -> Media -> Addand a new pop-up window should appear.

Here, select the name of the script that you have earlier named (in this example Send-Email-Script is used) for Type, enter the Gmail address to which you will send emails, choose the time period (week, hours) when email reports should be active for sending, choose the severity of the messages that you want to receive on your Gmail address, select Enabled as Status and hit the Add button to add the media. Finally hit the Update button to apply configuration.

11. On the next step, enable the defult zabbix alerts by navigating to Configuration -> Actions, select as the Event Source – > Triggers from the right menu and hit on Disabled Status in order to enable it. Repeat the step for Event Source – > Internal or other custom created Actions and you’re done.

Wait for a while for Zabbix to start gather information and generate some reports, then verify your Gmail Inboxand you should see some Zabbix alerts submitted so far.

That’s all! Although this guide was mainly focused on sending Zabbix alerts to a Gmail account using Gmail SMTP server as a mailhub, using the same configuration you can, also, push zabbix email alerts further to other valid internet email accounts by relying on Gmail to route your emails through SMTP servers.

Source

Zabbix is an Open Source, high-level enterprise software designed to monitor and keep track of networks, servers and applications in real time. Build in a server-client model, Zabbix can collect different type of data than are used to create historical graphics and output performance or load trends of the monitored targets.

The server has the ability to check standard networking services (HTTP, FTP, SMTP, IMAP etc) without the need to install extra software on the monitored hosts.

However, in order to gather data and create statistics about local services or other specific system resources that run on remote instances, such as CPU, disks, internal system process, RAM, etc, you need to install and configure a Zabbix agent.

Following are the 4-article series about Zabbix Monitoring application:

--------------- On Debian/Ubuntu --------------- 
$ sudo apt-get install apache2 php7.0 php7.0-mysql php7.0-ldap php7.0-bcmath php7.0-mbstring php7.0-gd php7.0-xml php7.0-mcrypt

--------------- On CentOS/RHEL ---------------
# yum -y install epel-release
# yum install httpd php php-mysql php-ldap php-bcmath- php-mbstring php-gd php-xml php-mcrypt		

$ sudo nano /etc/php/7.0/apache2/php.ini 	[On Debian/Ubuntu] 
# vi /etc/php.ini				[On RHEL/CentOS 7]

max_execution_time 300
memory_limit 128M
always_populate_raw_post_data -1
session.auto_start = 0
mbstring.func_overload = 0
date.timezone = Europe/Bucharest

$ sudo systemctl restart apache2.service	 [On Debian/Ubuntu] 
# systemctl restart httpd.service		 [On RHEL/CentOS 7]

$ sudo apt-get install mariadb-server mariadb-client libmysqld-dev	 [On Debian/Ubuntu] 
# yum install mariadb-server mariadb-client mariadb-devel	         [On RHEL/CentOS 7]

# mysql_secure_installation

# mysql -u root -p
MariaDB [(none)]> create database zabbixdb character set utf8 collate utf8_bin;
MariaDB [(none)]> grant all privileges on zabbixdb.* to 'zabbixuser'@'localhost' identified by 'password1';
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> exit

# wget http://repo.zabbix.com/zabbix/3.4/debian/pool/main/z/zabbix-release/zabbix-release_3.4-1+stretch_all.deb 
# dpkg -i zabbix-release_3.4-1+stretch_all.deb
# apt update
# apt install zabbix-server-mysql zabbix-frontend-php 

# wget http://repo.zabbix.com/zabbix/3.4/ubuntu/pool/main/z/zabbix-release/zabbix-release_3.4-1+xenial_all.deb 
# dpkg -i zabbix-release_3.4-1+xenial_all.deb
# apt-get update
# apt install zabbix-server-mysql zabbix-frontend-php 

# rpm -ivh http://repo.zabbix.com/zabbix/3.4/rhel/7/x86_64/zabbix-release-3.4-2.el7.noarch.rpm
# yum install zabbix-server-mysql zabbix-web-mysql

# apt install zabbix-agent    [On Debian/Ubuntu] 
# yum install zabbix-agent    [On RHEL/CentOS 7] 

# systemctl restart apache2   [On Debian/Ubuntu] 
# systemctl restart httpd     [On RHEL/CentOS 7] 
# setenforce 0                [Disable SELinux on RHEL/CentOS 7] 

# zcat /usr/share/doc/zabbix-server-mysql/create.sql.gz | mysql -u zabbixuser zabbixdb -p

# nano /etc/zabbix/zabbix_server.conf

DBHost=localhost
DBName=zabbixdb
DBUser=zabbixuser
DBPassword=password1

# systemctl restart zabbix-server.service

# nano /etc/zabbix/zabbix_agentd.conf 

Server=127.0.0.1
ListenPort=10050

# systemctl restart zabbix-agent.service 

http://192.168.1.151/zabbix/setup.php
OR
https://192.168.1.151/zabbix/setup.php

# chown -R www-data /var/www/html/conf/		[on Debian 8]
# chown -R apache /var/www/html/conf/		[on RHEL/CentOS 7]

https://your_domain.tld/zabbix/ 
Username: Admin
Password: zabbix