Best way to backup Database Mysql

[duluc]

Hi!
This is a long time I'm backing up the entire database with mysqldump,
but I saw when I try to restore with heidisql or phpmyadmin,
there's a lot of warnings and problems due to constraints, primary keys, foreign keys...
When trying to restore, first I clear the current data, then restore .sql ....

Due to thos foreigns and others constraints errors, I've to manually restore all deployables (tents), inworld vehicles, players profiles, survivors.... by hand and 1 after 1 command !

No way to restore whole database without those annoying errors ?!

 

[andret]

Whats your code for backing it up?

 

[dmustanger]

add to the top of the sql file SET FOREIGN_KEY_CHECKS=0;

add to the bottom of the sql file SET FOREIGN_KEY_CHECKS=1;

 

[duluc]

Whats your code for backing it up?

On an Ubuntu Server, a script.sh with crontab...

#!/bin/bash
#
## go to directory where backups will be stored
#
cd /home/server/dayzsave

for i in blisserver; do

## Backup the database in .sql
mysqldump -ublisserver -pmypassword $i > ${i}_`date +"%Y-%m-%d-%H-%M"`.sql

## Compress tar.bz2
tar jcf ${i}_`date +"%Y-%m-%d-%H-%M"`.sql.tar.bz2 ${i}_`date +"%Y-%m-%d-%H-%M"`.sql

## Delete .sql not compressed
rm ${i}_`date +"%Y-%m-%d-%H-%M"`.sql

done

add to the top of the sql file SET FOREIGN_KEY_CHECKS=0;

add to the bottom of the sql file SET FOREIGN_KEY_CHECKS=1;

I'll try this, thx!