Many of the PHP-based applications that can run on an Elemental Linux Server (ELS) feature a SQLite database backend. This document will demonstrate how to backup and restore those databases.
SQLite is included in the base packages and no installation is required. However, you may wish to install a phpBB web forum or a Serendipity blog to have some live data to work with.
You will need to create a directory for databases and a sample database.
The following example shows SQLite database creation that is consistent with the examples used in the ELS Howto: phpBB Web Forum and ELS Howto: Serendipity Blog documents.
mkdir /home/sqlite chmod daemon /home/sqlite touch sample.db chmod daemon /home/sqlite/sample.db
Use your favorite text editor to create the following file and save it as music.sql:
create table music(artist varchar(40), title varchar(60)); insert into music values('Beastie Boys','Paul''s Boutique'); insert into music values('John, Elton','Goodbye Yellow Brick Road'); insert into music values('Parliment','Mothership Connection');
Now use the file you just created to populate your sample database.
cd /home/sqlite sqlite3 sample.db < music.sql
Verify that the data were successfully entered before continuing.
sqlite3 sample.db 'select * from music;' Beastie Boys|Paul''s Boutique John, Elton|Goodbye Yellow Brick Road Parliment|Mothership Connection
Pay particular attention to the repeated single quote in Paul''s Boutique. This is not a typo, it is how SQL escapes literal single quotes. If you forget to do this when entering data, you'll see an error about an incomplete SQL statement.
To make a backup copy of the database, simply do a "dump" and redirect the results to a file.
cd /home/sqlite sqlite3 sample.db .dump > sample.bak
Restoring the database from a backup is just as easy as backing up, except we must make sure the destination database is empty first. Alternatively you may want to delete or rename the destination database and let sqlite create a new one for you.
cd /home/sqlite mv sample.db sample.db.old sqlite3 sample.db < sample.bak
After restoring, verify the results.
sqlite3 sample.db 'select * from music;' Beastie Boys|Paul's Boutique John, Elton|Goodbye Yellow Brick Road Parliment|Mothership Connection
To gain confidence with SQLite backups and restores, try backing up the database from phpBB or Serendipity. After dumping the database to a backup, try to restore it. (Warning: make sure to rename the original database and not delete it. That way if you make a mistake it's easily corrected.)
Once you are comfortable dumping and restoring databases, make a cron job to do a nightly backup of a SQLite database on your system.