Converting MySQL database to Firebird - part1
I have a heavy-used website powered by LAMP stack (CentOS Linux,
Apache 2, MySQL and PHP). It started on a shared hosting so I had to
use MySQL. Year and a half later, I switched shared, virtual hosting
and not run it on a dedicated server. I decided to try Firebird to see
how it performs and also how it compares to MySQL in RAM usage, disk
usage, etc.
The software
The system is CentOS 5.5 64bit with default LAMP stack. I installed
Firebird 2.5. RC3 from the .rpm package on Firebird website.
Surprisingly, it does not require any additional rpm package :)
Converting the database
As far as I can tell, there are no tools to do this automatically. I
created Firebird database and tables by hand, slightly editing the
schema dump from phpMyAdmin. This was easy. Loading the data seemed a
problem because default mysqldump places multiple VALUES clasuses in
INSERT statements. I used a Postgres tool mysql2pgsql to convert the
file to a more usable form:
http://pgfoundry.org/projects/mysql2pgsql/
I had to alter it a little bit, to avoid prefixing strings with E
character. I commented out this line:
#s/'((?:.*?(?:\\')?.*?)*)'([),])/E'$1'$2/g;
# for the E” see
http://www.postgresql.org/docs/8.2/interactive/release-8-1.html
Next problem was that ” and ’ are escaped with backslash .
With Firebird ” does not need escaping and ’ is escaped with another
’, becomes ”. A simple sed command to fix this:
cat postgres.sql | sed s1\\\\\"11g | sed s1\\\\\'1\'\'1g > firebird.sql
A few more manual edits were needed to remove the CREATE TABLE and
similar stuff, because I only needed data. After that I added
“commit;” to the end of the script and ran it via isql:
/opt/firebird/bin/isql /var/db/firebird/s.fdb -user sysdba -pass
******** -i firebird.sql
this took some time. Here is the result:
# du -h -s /var/lib/mysql/slagalica/ 1.9G /var/lib/mysql/slagalica/ # du -h -s /var/db/firebird/slagalica.fdb 2.1G /var/db/firebird/slagalica.fdb
This is before I created indexed on tables in Firebird database.
Afterwards we get:
2.3G /var/db/firebird/slagalica.fdb
So, Firebird database is slightly bigger.
Now, it’s time to convert the DB access layer in PHP application, and
compare the perfomance. Stay tuned…