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