Access denied for mysql during fresh Ubuntu install

[twigles]

Sorry if this has been answered elsewhere, I couldn't find anyone discussing this beyond one person on stackoverflow who solved it by switching to postgres.

Following the install guide for Zabbix 5.0 on Ubuntu 18.04 using MySQL and Apache, I issued the following commands:

wget https://repo.zabbix.com/zabbix/5.0/ubuntu/pool/main/z/zabbix-release/zabbix-release_5.0-1+bionic_all.deb
dpkg -i zabbix-release_5.0-1+bionic_all.deb
apt update

Worked fine.

apt install zabbix-server-mysql zabbix-frontend-php zabbix-apache-conf zabbix-agent

Worked fine.

mysql -uroot -p
[password]
mysql> create database zabbix character set utf8 collate utf8_bin;
mysql> create user zabbix@localhost identified by 'password';
mysql> grant all privileges on zabbix.* to zabbix@localhost;
mysql> quit;

Worked fine.

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

Entered the password I used in the previous step (copy/pasted both times from a text file), and got:

ERROR 1045 (28000): Access denied for user 'zabbix'@'localhost' (using password: YES)

The user and DB exist, and I'm at a loss as to where to go from here. Did I do something stupid?

Thanks for any help

[markfree]

Please, first check the information bellow taken from MySQL manual (man mysql),

--password[=password], -p[password]

The password to use when connecting to the server. If you use the short option form (-p), you cannot have a space between the option and the password. If you omit the password value following
the --password or -p option on the command line, mysql prompts for one.

Note that you cannot have a space between the option and the password.

The second check I suggest is to see if you typed the correct Zabbix user password.
When you take the steps to create a zabbix database you also set the user "zabbix" and its password.

So, when using "zcat" command, please, check that you typed the correct password for DB user "zabbix" and join "-p" parameter with the actual password.

mysql -uzabbix -pzabbix

Then tell us if it worked. Cheers.

[tim.mooney]

Note that you cannot have a space between the option and the password.

mysql -uzabbix -pzabbix

While you're correct that you cannot put a space between '-p' and the password if you pass it on the command line, that's not actually what's being recommended by the instructions or what twigles did.

The '-p zabbix' isn't an incorrectly specified password, it's '-p' by itself (in other words, prompt for the password) and the database name to connect to.

I'm wondering if this is a case of the MySQL version in use is defaulting to not including mysql_native_password in the list of plugins allowed for user authentication, so the pluggable authentication system in recent MySQL is rejecting the authentication not because the password was typed or copied incorrectly, but because using a password is not allowed for that user at all. It might be worth a look at the contents of the 'plugin' column for the zabbix user, to see if any authentication plugins are listed there.

[twigles]

I dorked around with the passwords a bit, and the plugins sounds promising based on the bellyflop that was my morning. I reset the zabbix user's password from within mysql, dropped the user entirely and recreated it when that yielded the same results, and tried various iterations of the zcat command.

zcat /usr/share/doc/zabbix-server-mysql*/create.sql.gz | mysql -uzabbix -p password1
Enter password: [password1]
zcat /usr/share/doc/zabbix-server-mysql*/create.sql.gz | mysql -uzabbix -ppassword1
ERROR 1046 (3D000) at line 1: No database selected
zcat /usr/share/doc/zabbix-server-mysql*/create.sql.gz | mysql -uzabbix -p
Enter password: [password1]

So yeah, I can see it in #2 thinking I just told it to use 'password1' as my database. I'll begin the hunt anew, thanks.

Edit:

root@ip-172-31-0-221:~# mysql --version
mysql Ver 15.1 Distrib 10.1.44-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

[zemlicka]

I am having this exact problem too. I cannot get past the zcat part of the installation instructions. I've wiped and resetup (thinking I did something along the way) the VM at least 4 or 5 times now. I have a basic setup of Debian 10 with ssh server, customize IP address, install XCP-NG tools, reboot, and then proceed with the Zabbix setup. I always get an access denied at the zcat step. I've walked through the following articles (which turn out to all be pretty much the same). The only difference is on the ones that reference the older packages, I'm downloading the latest zabbix-release_5.0-1+buster_all.deb.

https://www.zabbix.com/download?zabbix=5.0&os_distribution=debian&os_version=10_buster&db=mysql

https://www.zabbix.com/documentation/current/manual/installation/install_from_packages/debian_ubuntu

https://kifarunix.com/install-zabbix-server-on-debian-10-buster/

https://computingforgeeks.com/how-to...-debian-linux/

https://www.tecmint.com/install-zabbix-on-debian-10/

Most of these were with a clean install on the off chance that I did something wrong with the previous attempt. Some have a manual install of mariadb with a mysql_secure_installation run and some just have the mysql install through the zabbix-server-mysql.

WTH am I doing wrong?

[zemlicka]

Ok, for resolution and the next person, I realized what I was doing wrong. It seems that there is a number of tutorials out there that either have incorrect info or a strategy that doesn't seem to work anymore (or I'm just missing something). The earlier posts in here indicating not to use a space after -p are correct. The line that worked for me is as follows

zcat /usr/share/doc/zabbix-server-mysql/create.sql.gz | mysql -u zabbix -pYourZabbixUserDBpassword zabbix

[tim.mooney]

zcat /usr/share/doc/zabbix-server-mysql/create.sql.gz | mysql -u zabbix -pYourZabbixUserDBpassword zabbix

The result is the same if you do this:

zcat /usr/share/doc/zabbix-server-mysql/create.sql.gz | mysql -u zabbix -p   zabbix
Enter password:

Maybe it's more obvious what's going on if you use the long options and reorder them:

zcat /usr/share/doc/zabbix-server-mysql/create.sql.gz | mysql --user=zabbix --database=zabbix --password
Enter password:

Also, from the mysql man page (note the part I've bolded):

o --password[=password], -p[password]

The password to use when connecting to the server. If you use the
short option form (-p), you cannot have a space between the option
and the password. If you omit the password value following the
--password or -p option on the command line, mysql prompts for one.

Specifying a password on the command line should be considered
insecure. You can use an option file to avoid giving the password
on the command line.

[joneken]

I have a similar issue however notwithstanding I expanded as far as possible on php.ini, the difficult remains. Have you some other proposals vpn black friday deals