map.sqlite backup with database locking
Posted: Thu Jan 22, 2015 22:59Hello everyone,
I'm currently looking into methods to do daily backups of my Linux minetest server and I ran into a difficulty.
If you do just a simple file copy of map.sqlite while the server is running, you end up with a corrupted backup.
This is because while the database is copied it is changed by server writes.
One way to do a sane copy is to stop the server for as long as the backup takes, and this seems to be the way most server admins do it.
That also means that all players get disconnected!
But I've tested another method:
I just leave the server running and tell the backup script to lock the database before copying it.
Once finished, the transaction is rolled back and the lock is released.
My test results for a 565MB map.sqlite file are pretty promising.
The script takes 12 seconds on my server to lock the database, copy the file and roll back.
There is no noticeable delay or lag for the players.
EDIT: I've modified the scripts to make a backup of the whole world directory, now it takes 19 seconds but still there's no lag at all for the players.
Here's how it works:
First, install the package "sqlite3" on your server.
You need at least version 3.8, the 3.7 version from Debian wheezy doesn't have the ".shell" command yet!
Save this script as "minetest-backup.sh" and make it executable (chmod 755 minetest-backup.sh):
Then save the following snippet as "minetest-backup.sql":
Of course you need to change the paths to your personal preference, but I hope you get the idea!
Tell me your experience and if it works for you, and if not, why :)
Bye!
vitaminx
I'm currently looking into methods to do daily backups of my Linux minetest server and I ran into a difficulty.
If you do just a simple file copy of map.sqlite while the server is running, you end up with a corrupted backup.
This is because while the database is copied it is changed by server writes.
One way to do a sane copy is to stop the server for as long as the backup takes, and this seems to be the way most server admins do it.
That also means that all players get disconnected!
But I've tested another method:
I just leave the server running and tell the backup script to lock the database before copying it.
Once finished, the transaction is rolled back and the lock is released.
My test results for a 565MB map.sqlite file are pretty promising.
The script takes 12 seconds on my server to lock the database, copy the file and roll back.
There is no noticeable delay or lag for the players.
EDIT: I've modified the scripts to make a backup of the whole world directory, now it takes 19 seconds but still there's no lag at all for the players.
Here's how it works:
First, install the package "sqlite3" on your server.
You need at least version 3.8, the 3.7 version from Debian wheezy doesn't have the ".shell" command yet!
Save this script as "minetest-backup.sh" and make it executable (chmod 755 minetest-backup.sh):
Your phone or window isn't wide enough to display the code box. If it's a phone, try rotating it to landscape mode.
- Code: Select all
#!/bin/bash
DEST="/opt/backups/minetest"
TMP="/tmp/minetest/world"
MAP="/home/minetest/.minetest/worlds/world/map.sqlite"
SQL_SCRIPT="/opt/scripts/bin/minetest-backup.sql"
FILE=$(/bin/date -I)
#############################
# remove temporary directory
function remove_tmp {
if [ -e "$TMP" ]; then
rm -rf "$TMP"
fi
}
#############################
# create temporary directory
function create_tmp {
mkdir -p $TMP
if [ "$?" -ne 0 ]; then
echo "Can't create backup directory '$TMP'. Aborting." >&2
exit -1
fi
}
#############################
# create backup directory
function create_dest {
if [ ! -e $DEST ]; then
mkdir -p $DEST
if [ "$?" -ne 0 ]; then
echo "Can't create backup directory '$DEST'. Aborting." >&2
exit -1
fi
fi
}
#############################
# backup minetest world
function backup {
echo ".read $SQL_SCRIPT" | time sqlite3 -echo "$MAP"
nice tar -czf "${DEST}/world_${FILE}.tar.gz" "$TMP"
chown -R backup: $DEST
}
#############################
# remove old backups
function remove_old {
find $DEST -type f -mtime +6 -exec rm -f {} \;
}
remove_tmp
create_tmp
create_dest
backup
remove_old
remove_tmp
Then save the following snippet as "minetest-backup.sql":
Your phone or window isn't wide enough to display the code box. If it's a phone, try rotating it to landscape mode.
- Code: Select all
begin immediate;
.shell cp -r /home/minetest/.minetest/worlds/world /tmp/minetest/
rollback;
Of course you need to change the paths to your personal preference, but I hope you get the idea!
Tell me your experience and if it works for you, and if not, why :)
Bye!
vitaminx