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