Skip to content

Create the Initial Database

Video Lecture

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.

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.

If you see the error You do not have the SUPER privilege and binary logging is enabled, then you need to delete the Zabbix database that we just created and start again. This error has started to appear since August 2022. See troubleshooting log_bin_trust_function_creators below, otherwise continue next steps.

Disable log_bin_trust_function_creators option after importing database schema.

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.

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;

Start the Zabbix Server Process

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

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

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