Zabbix database backup MySQL user rights problem

[MuriloHB]

Hey guys!

So I've been monitoring my Zabbix database size and I've noticed that is getting too big (I've just received the message that free disk space is less than 20% on the problems dashboard). So I've read that the best way to deal with the size of the DB is to implement partitioning and I've decided to give it a try. But first I would like to do a backup of my Zabbix database and configuration files so that I won't lose it all if things goes south. I've already done this once with mysqldump but now I'm having some issues because there is no space left on the virtual machine to make a dump for the database. So what I've tried to do is to run the mysqldump on another machine that have sufficient space, but I'm having some issues doing that. Here is the problem:

I'll call the VM that Zabbix is running as A and the VM that I would like to make de dump B. Machine's A IP = 10.100.100.242 and Machine's B IP = 10.100.52.5

1. Machine A and B can communicate just fine (they can ping one another);
2. There are no firewall rules on either one of the machines that is blocking the communication;
3. I've already changed the mysql.conf file and changed the bind-address to 0.0.0.0;
4. When I run the command "mysqldump -P3306 -h 10.100.100.242 -u zabbix -p zabbix > /home/zabbixbd/zabbixbd.sql" on machine B I get the following output error:

mysqldump: Got error: 1130: Host '10.100.52.5' is not allowed to connect to this MySQL server when trying to connect

So I've read that I must create a new user, or grant access to the machine's B IP on machine A. But this is where it gets confusing...
I can log into mysql just fine on machine A (Zabbix Appliance) with the following command:

mysql -u zabbix -p zabbix

And then I use the password specified on may zabbix configuration file and log into mysql just fine.
But when I run

SHOW GRANTS;

I get the following output:

+------------------------------------------------------------+
| Grants for zabbix@localhost                                |
+------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zabbix'@'localhost'                 |
| GRANT ALL PRIVILEGES ON `zabbix`.* TO 'zabbix'@'localhost' |
+------------------------------------------------------------+
2 rows in set (0.00 sec)

And when I try to grant privileges to Machine B:

GRANT ALL ON *.* to [email protected] IDENTIFIED BY 'password';

GRANT ALL ON `zabbix`.* TO 'root'@'10.100.52.5' IDENTIFIED BY 'password';

I get the output:

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

I don't know much about mysql but I'm thinking that I'm not logged in as root on mysql and that's why I cant grant any privileges, but I don't know how to login as root.
Can anyone give me a hand on this?

Thanks!

[MuriloHB]

Just updating my status: I've been able to solve the problem and here are the steps I followed

On Machine A:

1. Stop MySQL services with service mysql stop
2. Start MySQL with the command: mysqld_safe --skip-grant-tables &
3. Login as root: mysql -u root
4. Select the database from MySQL: use mysql;
5. On versions after MySQL 5.7, the table of password on mysql.user was removed and substituted by the table 'authentication_string'. To see what table your version is using to store the password run: show tables;
6. Update the user root password: update user set authentication_string=password('newpassword') where user='root';
7. Update privileges and restart MySQL:
flush privileges;
service mysql stop;
service mysql start;

Now to make the dump using machine A, log in as root and create a new user on MySQL with all privileges:
CREATE USER 'zabbixbackup'@'IP-MACHINE-B' IDENTIFIED BY 'any-password';
GRANT ALL PRIVILEGES ON *.* TO 'zabbixbackup'@'IP-MACHINE-B' WITH GRANT OPTION ;

And on machine B just run the following command: mysqldump -P3306 -h IP-MACHINE-A -u zabbixbackup -p zabbix > /backuo/location/zabbixbd.sql

That's it!