Problems with MySql Backup

[fulltilt]

I'm using reoback for my backups ...
Now i get the following error message by running a backup when zabbix running:
/var/lib/mysql/ibdata1: file changed by reading ...

Is it possible to tell zabbix stop working when reoback is running?

[Calimero]

You cannot just copy InnoDB storages files (ibdata*) while mysql is running. That's the kind of things you can only do with MyISAM tables, provided that you've issued the right FLUSH/LOCK commands or used a script like mysqlhotcopy .

Either you go mysqldump or spend some money on Innodb Hot Backup (tool that allows innodb online backups without locks, referred to in the Mysql Reference Manual).

[dantheman]

Or if it's ok for zabbix to be down for you while the backup is running you can shutdown zabbix, shutdown mysql, run your backup, then restart mysql and then zabbix again.

[fulltilt]

thanks its working now.
--------------------------

is it possible to stop and start zabbix with cron ...
e.g.
* 3 * * * /etc/init.d/zabbix-agent stop
* 5 * * * /etc/init.d/zabbix-agent start
&
* 3 * * * /etc/init.d/zabbix-server stop
* 5 * * * /etc/init.d/zabbix-server start

i mean stop & start without pid error?
/var/run/zabbix/check_server.pid seems to be stolen. Removing..

Or if it's ok for zabbix to be down for you while the backup is running you can shutdown zabbix, shutdown mysql, run your backup, then restart mysql and then zabbix again.

[alj]

I'm using reoback for my backups ...
Now i get the following error message by running a backup when zabbix running:
/var/lib/mysql/ibdata1: file changed by reading ...

Is it possible to tell zabbix stop working when reoback is running?

What you can do is disable php front end (stop apache) do mysqldump then enable front end. This will prevent users from making any changes to the configuration (paging/monitoring will still be working).
All you gonna loose is several last entries in history table, very low risk to have inconsistency.

I. e.
#!/bin/sh
/etc/init.d/apache2 stop
mysqldump zabbix | gzip --fast > /tmp/zabbix.sql.gz
/etc/init.d/apache2 start
# run your backup software here to save /tmp/zabbix.sql.gz

[LEM]

consider using a MySQL replica for backup purpose

You could use a MySQL replicate where backups are done (while the master one for production purpose will still be running)...

...probably the best option, imho (but you need another mysql server somewhere to do that)

Cheers,

[fulltilt]

You could use a MySQL replicate where backups are done (while the master one for production purpose will still be running)...

...probably the best option, imho (but you need another mysql server somewhere to do that)

Cheers,

I have 2 webserver running ... did you mean to set mysql connection for zabbix on another server .... is that possible?
And the Backup Times different ...

[Calimero]

I have 2 webserver running ... did you mean to set mysql connection for zabbix on another server .... is that possible?
And the Backup Times different ...

I guess LEM was talking about MySQL Replication:

(Zabbix Server) <===> (MySQL Master) <===> (MySQL Slave)

MySQL data is replicated from the master to the slave. As it's an asyncronous process, you can shutdown the slave to perform offline backups, then bring it back online and the slave will "catch up" as replication resumes.

==> Master MySQL (and thus Zabbix) is running 24x7

But you need an extra server to host the mysql slave...

[netod]

I guess LEM was talking about MySQL Replication:

(Zabbix Server) <===> (MySQL Master) <===> (MySQL Slave)

MySQL data is replicated from the master to the slave. As it's an asyncronous process, you can shutdown the slave to perform offline backups, then bring it back online and the slave will "catch up" as replication resumes.

==> Master MySQL (and thus Zabbix) is running 24x7

But you need an extra server to host the mysql slave...

I don't mean to be a smartass but couldn't you just use mysqldump (like Calimero suggested) to get the data out, surely that works on a live system?

[Calimero]

I don't mean to be a smartass but couldn't you just use mysqldump (like Calimero suggested) to get the data out, surely that works on a live system?

mysqldump take quite some resources when dumping big databases thus running it on a "working" server might not be all that good.

Depends on how busy your mysql server is and how big your database is, I'd say.