Skip to content

Create the Initial Database

Video Lecture

Create the Initial Database Create the Initial Database Create the Initial Database

Description

I need to install a MySQL database.

To check if you have a MySQL database server running, you can type

sudo service mysql status

or

mysql

If you get the error

Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'

Then it usually means that MySQL is not installed.

So in my case, since I have a brand-new server that doesn't have MySQL installed by default, I need to install MySQL.

sudo apt install mysql-server

Check if it is running.

sudo service mysql status

It should now indicate that MySQL is active.

We can now log onto the MySQL server and set up the Zabbix database.

mysql
create database zabbix character set utf8mb4 collate utf8mb4_bin;
create user zabbix@localhost identified by 'password';
grant all privileges on zabbix.* to zabbix@localhost;
quit;

Import the schema.

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

If prompted, enter the password that you've set for your zabbix@localhost user.

Wait about 30 seconds for it to finish,

Then edit the Zabbix Server configuration file and set the DBPassword property to being the password that you set for your zabbix@localhost user.

sudo nano /etc/zabbix/zabbix_server.conf
...
DBPassword=password
...

Do some MySQL checks if you want.

mysql
show databases;
use zabbix;
show tables;
select * from users;
quit;

Now that MySQL is running, we can start the Zabbix Server process.

sudo service zabbix-server start

And check its status is active

sudo service zabbix-server start

To ensure that Zabbix Server, Agent and Apache restart in case of reboot, run this command.

systemctl enable zabbix-server zabbix-agent apache2

Troubleshooting

ERROR : The server requested authentication method unknown to the client

Q. When logging into Zabbix for the first time, you are asked to enter the database connection details, you put in the complicated password, and you get the error "The server requested authentication method unknown to the client."

A. This is possibly due to the PHP front end not being aware if the MySQL password hashing requirement. So you can alter the Zabbix user in the database to use the mysql_native_password option.

Log into the MySQL prompt as the root

mysql

Alter the zabbix user

ALTER USER 'zabbix'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your-really-complicated-password-for-the-zabbix-user';
FLUSH PRIVILEGES;

And restart MySQL

sudo service mysql restart

ERROR : SET PASSWORD has no significance for user 'root'@'localhost'

mysql_secure_installation stopped working since MySQL version 8.0.29.

I no longer recommend running this command.

But if you want to, then before running mysql_secure_installation, you can enter the MySQL prompt

sudo mysql

Alter the localhost root user

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'my-secret-password';
FLUSH PRIVILEGES;

Exit

quit

And now run

mysql_secure_installation

Note that when using the MySQL prompt from now on, you now need to enter the password

I.e. start MySQL prompt using,

mysql -u root -p

instead of just

mysql