MySQL Archives - WPcademy

December 21, 2020December 22, 2020

How to connect to a remote MySQL database using Linux terminal

MySQL is an open-source relational database management system (RDBMS).

connect remote mysql In this article we will connect to remote MySQL database in the simple way. After you setup a user with proper access rights run the below commands.

$ mysql -u yourUser -p -h

Lets explanation the above command:

-u tells mysql what is your username

-p tells mysql you have a password and will prompt you to enter it after you press enter

-h tells mysql the hostname or IP address of your MySQL server

You can learn more form MySQL database documentation

September 24, 2020

How To Install and Configure Zabbix to Securely Monitor Remote Servers on Ubuntu 20.04

The author selected the Computer History Museum to receive a donation as part of the Write for DOnations program.

Introduction

Zabbix is open-source monitoring software for networks and applications. It offers real-time monitoring of thousands of metrics collected from servers, virtual machines, network devices, and web applications. These metrics can help you determine the current health of your IT infrastructure and detect problems with hardware or software components before customers complain. Useful information is stored in a database so you can analyze data over time and improve the quality of provided services or plan upgrades of your equipment.

Zabbix uses several options for collecting metrics, including agentless monitoring of user services and client-server architecture. To collect server metrics, it uses a small agent on the monitored client to gather data and send it to the Zabbix server. Zabbix supports encrypted communication between the server and connected clients, so your data is protected while it travels over insecure networks.

The Zabbix server stores its data in a relational database powered by MySQL or PostgreSQL. You can also store historical data in NoSQL databases like Elasticsearch and TimescaleDB. Zabbix provides a web interface so you can view data and configure system settings.

In this tutorial, you will configure Zabbix on two Ubuntu 20.04 machines. One will be configured as the Zabbix server, and the other as a client that you’ll monitor. The Zabbix server will use a MySQL database to record monitoring data and use Nginx to serve the web interface.

Prerequisites

To follow this tutorial, you will need:

Two Ubuntu 20.04 servers set up by following the Initial Server Setup Guide for Ubuntu 20.04, including a non-root user with sudo privileges and a firewall configured with ufw. On one server, you will install Zabbix; this tutorial will refer to this as the Zabbix server. It will monitor your second server; this second server will be referred to as the second Ubuntu server.
The server that will run the Zabbix server needs Nginx, MySQL, and PHP installed. Follow Steps 1–3 of our Ubuntu 20.04 LEMP Stack guide to configure those on your Zabbix server.
A registered domain name. This tutorial will use your_domain throughout. You can purchase a domain name from Namecheap, get one for free with Freenom, or use the domain registrar of your choice.
Both of the following DNS records set up for your Zabbix server. If you are using DigitalOcean, please see our DNS documentation for details on how to add them.
- An A record with your_domain pointing to your Zabbix server’s public IP address.
- An A record with www.your_domain pointing to your Zabbix server’s public IP address.

Additionally, because the Zabbix Server is used to access valuable information about your infrastructure that you would not want unauthorized users to access, it’s important that you keep your server secure by installing a TLS/SSL certificate. This is optional but strongly encouraged. If you would like to secure your server, follow the Let’s Encrypt on Ubuntu 20.04 guide after Step 3 of this tutorial.

Step 1 — Installing the Zabbix Server

First, you need to install Zabbix on the server where you installed MySQL, Nginx, and PHP. Log in to this machine as your non-root user:

ssh sammy@zabbix_server_ip_address

Zabbix is available in Ubuntu’s package manager, but it’s outdated, so use the official Zabbix repository to install the latest stable version. Download and install the repository configuration package:

wget https://repo.zabbix.com/zabbix/5.0/ubuntu/pool/main/z/zabbix-release/zabbix-release_5.0-1+focal_all.deb
sudo dpkg -i zabbix-release_5.0-1+focal_all.deb

You will see the following output:

Output

Selecting previously unselected package zabbix-release.
(Reading database ... 64058 files and directories currently installed.)
Preparing to unpack zabbix-release_5.0-1+focal_all.deb ...
Unpacking zabbix-release (1:5.0-1+focal) ...
Setting up zabbix-release (1:5.0-1+focal) ...

Update the package index so the new repository is included:

sudo apt update

Then install the Zabbix server and web frontend with MySQL database support:

sudo apt install zabbix-server-mysql zabbix-frontend-php

Also, install the Zabbix agent, which will let you collect data about the Zabbix server status itself.

sudo apt install zabbix-agent

Before you can use Zabbix, you have to set up a database to hold the data that the Zabbix server will collect from its agents. You can do this in the next step.

Step 2 — Configuring the MySQL Database for Zabbix

You need to create a new MySQL database and populate it with some basic information in order to make it suitable for Zabbix. You’ll also create a specific user for this database so Zabbix isn’t logging in to MySQL with the root account.

sudo mysql

Create the Zabbix database with UTF-8 character support:

create database zabbix character set utf8 collate utf8_bin;

Then create a user that the Zabbix server will use, give it access to the new database, and set the password for the user:

create user zabbix@localhost identified by ‘your_zabbix_mysql_password‘;
grant all privileges on zabbix.* to zabbix@localhost;

That takes care of the user and the database. Exit out of the database console.

quit;

Next you have to import the initial schema and data. The Zabbix installation provided you with a file that sets this up.

Run the following command to set up the schema and import the data into the zabbix database. Use zcat since the data in the file is compressed:

zcat /usr/share/doc/zabbix-server-mysql*/create.sql.gz | mysql -uzabbix -p zabbix

Enter the password for the zabbix MySQL user that you configured when prompted.

This command may take a minute or two to execute. If you see the error ERROR 1045 (28000): Access denied for userzabbix@'localhost' (using password: YES) then make sure you used the right password for the zabbix user.

In order for the Zabbix server to use this database, you need to set the database password in the Zabbix server configuration file. Open the configuration file in your preferred text editor. This tutorial will use nano:

sudo nano /etc/zabbix/zabbix_server.conf

Look for the following section of the file:

/etc/zabbix/zabbix_server.conf

...
### Option: DBPassword                           
#       Database password. Ignored for SQLite.   
#       Comment this line if no password is used.
#                                                
# Mandatory: no                                  
# Default:                                       
# DBPassword=
...

These comments in the file explain how to connect to the database. You need to set the DBPassword value in the file to the password for your database user. Add this line after those comments to configure the database:

/etc/zabbix/zabbix_server.conf

...
DBPassword=your_zabbix_mysql_password
...

Save and close zabbix_server.conf by pressing CTRL+X, followed by Y and then ENTER if you’re using nano.

You’ve now configured the Zabbix server to connect to the database. Next, you will configure the Nginx web server to serve the Zabbix frontend.

Step 3 — Configuring Nginx for Zabbix

To configure Nginx automatically, install the automatic configuration package:

sudo apt install zabbix-nginx-conf

As a result, you will get the configuration file /etc/zabbix/nginx.conf, as well as a link to it in the Nginx configuration directory /etc/nginx/conf.d/zabbix.conf.

Next, you need to make changes to this file. Open the configuration file:

sudo nano /etc/zabbix/nginx.conf

The file contains an automatically generated Nginx server block configuration. It contains two lines that determine the server name and what port it is listening on:

/etc/zabbix/nginx.conf

server {
#        listen          80;
#        server_name     example.com;
...

Uncomment the two lines, and replace example.com with your domain name. Your settings will look like this:

/etc/zabbix/nginx.conf

server {
        listen          80;
        server_name     your_domain;
...

Save and close the file. Next, test to make sure that there are no syntax errors in any of your Nginx files and reload the configuration:

sudo nginx -t
sudo nginx -s reload

Now that Nginx is set up to serve the Zabbix frontend, you will make some modifications to your PHP setup in order for the Zabbix web interface to work properly.

Note: As mentioned in the Prerequisites section, it is recommended that you enable SSL/TLS on your server. If you would like to do this, follow our Ubuntu 20.04 Let’s Encrypt tutorial before you move on to Step 4 to obtain a free SSL certificate for Nginx. This process will automatically detect your Zabbix server block and configure it for HTTPS. After obtaining your SSL/TLS certificates, you can come back and complete this tutorial.

Step 4 — Configuring PHP for Zabbix

The Zabbix web interface is written in PHP and requires some special PHP server settings. The Zabbix installation process created a PHP-FPM configuration file that contains these settings. It is located in the directory /etc/zabbix and is loaded automatically by PHP-FPM. You need to make a small change to this file, so open it up with the following:

sudo nano /etc/zabbix/php-fpm.conf

The file contains PHP settings that meet the necessary requirements for the Zabbix web interface. However, the timezone setting is commented out by default. To make sure that Zabbix uses the correct time, you need to set the appropriate timezone:

/etc/zabbix/php-fpm.conf

...
php_value[max_execution_time] = 300
php_value[memory_limit] = 128M
php_value[post_max_size] = 16M
php_value[upload_max_filesize] = 2M
php_value[max_input_time] = 300
php_value[max_input_vars] = 10000
; php_value[date.timezone] = Europe/Riga

Uncomment the timezone line highlighted in the preceding code block and change it to your timezone. You can use this list of supported time zones to find the right one for you. Then save and close the file.

Now restart PHP-FPM to apply these new settings:

sudo systemctl restart php7.4-fpm.service

You can now start the Zabbix server:

sudo systemctl start zabbix-server

Then check whether the Zabbix server is running properly:

sudo systemctl status zabbix-server

You will see the following status:

Output

● zabbix-server.service - Zabbix Server
     Loaded: loaded (/lib/systemd/system/zabbix-server.service; disabled; vendor preset: enabled)
     Active: active (running) since Fri 2020-06-12 05:59:32 UTC; 36s ago
    Process: 27026 ExecStart=/usr/sbin/zabbix_server -c $CONFFILE (code=exited, status=0/SUCCESS)
  ...

Finally, enable the server to start at boot time:

sudo systemctl enable zabbix-server

The server is set up and connected to the database. Next, set up the web frontend.

Step 5 — Configuring Settings for the Zabbix Web Interface

The web interface lets you see reports and add hosts that you want to monitor, but it needs some initial setup before you can use it. Launch your browser and go to the address http://zabbix_server_name or https://zabbix_server_name if you set up Let’s Encrypt. On the first screen, you will see a welcome message. Click Next step to continue.

On the next screen, you will see the table that lists all of the prerequisites to run Zabbix.

All of the values in this table must be OK, so verify that they are. Be sure to scroll down and look at all of the prerequisites. Once you’ve verified that everything is ready to go, click Next step to proceed.

The next screen asks for database connection information.

You told the Zabbix server about your database, but the Zabbix web interface also needs access to the database to manage hosts and read data. Therefore enter the MySQL credentials you configured in Step 2. Click Next step to proceed.

On the next screen, you can leave the options at their default values.

The Name is optional; it is used in the web interface to distinguish one server from another in case you have several monitoring servers. Click Next step to proceed.

The next screen will show the pre-installation summary so you can confirm everything is correct.

Click Next step to proceed to the final screen.

The web interface setup is now complete. This process creates the configuration file /usr/share/zabbix/conf/zabbix.conf.php, which you could back up and use in the future. Click Finish to proceed to the login screen. The default user is Admin and the password is zabbix.

Before you log in, set up the Zabbix agent on your second Ubuntu server.

Step 6 — Installing and Configuring the Zabbix Agent

Now you need to configure the agent software that will send monitoring data to the Zabbix server.

ssh sammy@second_ubuntu_server_ip_address

Just like on the Zabbix server, run the following commands to install the repository configuration package:

wget https://repo.zabbix.com/zabbix/5.0/ubuntu/pool/main/z/zabbix-release/zabbix-release_5.0-1+focal_all.deb
sudo dpkg -i zabbix-release_5.0-1+focal_all.deb

Next, update the package index:

sudo apt update

Then install the Zabbix agent:

sudo apt install zabbix-agent

While Zabbix supports certificate-based encryption, setting up a certificate authority is beyond the scope of this tutorial. But you can use pre-shared keys (PSK) to secure the connection between the server and agent.

First, generate a PSK:

sudo sh -c “openssl rand -hex 32 > /etc/zabbix/zabbix_agentd.psk”

Show the key by using cat so you can copy it somewhere:

cat /etc/zabbix/zabbix_agentd.psk

The key will look something like this:

Output

75ad6cb5e17d244ac8c00c96a1b074d0550b8e7b15d0ab3cde60cd79af280fca

Save this for later; you will need it to configure the host.

Now edit the Zabbix agent settings to set up its secure connection to the Zabbix server. Open the agent configuration file in your text editor:

sudo nano /etc/zabbix/zabbix_agentd.conf

Each setting within this file is documented via informative comments throughout the file, but you only need to edit some of them.

First you have to edit the IP address of the Zabbix server. Find the following section: