Create the Initial Database

Video Lecture

Description

I need to install a MySQL database.

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

service mysql status

or

mysql

If you get the error

Unit mysql.service could not be found.

or

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

Then it most likely means that the MySQL database service is not installed.

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

apt install mysql-server

Check if it is running.

service mysql status

It should now indicate that MySQL is active.

Create Zabbix User and Database

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

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;

Warning

Since August 2022, we now need to temporarily enable the log_bin_trust_function_creators option.

set global log_bin_trust_function_creators = 1;

quit;

Import Zabbix Schema

Now, import the Zabbix schema.

zcat /usr/share/zabbix-sql-scripts/mysql/server.sql.gz | mysql --default-character-set=utf8mb4 -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.

Now to disable the log_bin_trust_function_creators option.

mysql

set global log_bin_trust_function_creators = 0;
quit;

Edit Zabbix Server configuration file

Now in the Zabbix Server configuration file, find and set the DBPassword property to being the password that you set for your zabbix@localhost user.

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;

Start the Zabbix Server Process

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

service zabbix-server start

And check its status is active

service zabbix-server status

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 : 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

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

ERROR : You do not have the SUPER privilege and binary logging is enabled

Since August 2022, you need to modify the MySQL log_bin_trust_function_creators flag in order to install the Zabbix server database schema.

If you followed my above commands but didn't enable the log_bin_trust_function_creators option, then we need to drop the new Zabbix database from our new MySQL server.

mysql

DROP DATABASE zabbix;

Now re-create it.

create database zabbix character set utf8mb4 collate utf8mb4_bin;

If your zabbix@localhost user doesn't already exist from the earlier install attempt, then

create user zabbix@localhost identified by 'password';

Re grant all privileges of the new Zabbix database to your Zabbix database user.

grant all privileges on zabbix.* to zabbix@localhost;

This next step is now required since August 2022.

set global log_bin_trust_function_creators = 1;

Quit as usual.

quit;

Now go back and continue the installation steps above where we need to try and re-import the schema.

Comments