search

npotorino / zabbix-backup

Backup script for Zabbix configuration data (MySQL/PostgreSQL)
MIT License
67 stars 30 forks source link

Unable to restore Zabbix 6.0.13 from backup (mysql) #20

Closed sacca1986 closed 1 year ago

sacca1986 commented 1 year ago

When I'am trying to restore database from the backup I receive this error:

ERROR 1359 (HY000) at line 20337: Trigger already exists

Before to start the procedure of restore i dropped the Zabbix Database:

below the commands:

_mysql> drop database zabbix; mysql> create database zabbix character set utf8mb4 collate utf8mb4_bin; mysql> grant all privileges on zabbix.* to zabbix@localhost; mysql> set global log_bin_trust_function_creators = 1;

cat zabbix_backup.sql | mysql --default-character-set=utf8mb4 zabbix

Zabbix Server: 6.0.13 mysql: 8.0.26

sacca1986 commented 1 year ago

Hi, i resolved the problem adding the directive "--skip-triggers" in the Dump Data code section (row 547), because I noticed that the triggers were being dumped also in schema.

Original row: DUMP_OPTS=(--opt --single-transaction --skip-lock-tables --no-create-info --skip-extended-insert) Modified row: DUMP_OPTS=(--opt --single-transaction --skip-lock-tables --no-create-info --skip-extended-insert --skip-triggers)

ironbishop commented 1 year ago

I expect the trigger to be deleted during the drop...?

sacca1986 commented 1 year ago

Yes i confirm that the triggers deleting during drop. The problem is in the .sql file generated by script because the trigger creation directive is duplicate.