{"id":3582,"date":"2010-10-23T15:24:19","date_gmt":"2010-10-23T19:24:19","guid":{"rendered":"http:\/\/g33kinfo.com\/info\/?p=3582"},"modified":"2010-10-23T15:24:19","modified_gmt":"2010-10-23T19:24:19","slug":"converting-mysql-database-to-firebird-part1-2","status":"publish","type":"post","link":"https:\/\/g33kinfo.com\/info\/converting-mysql-database-to-firebird-part1-2\/","title":{"rendered":"Converting MySQL database to Firebird – part1"},"content":{"rendered":"

From backwardcompatible.net<\/a><\/p>\n

I have a heavy-used website powered by LAMP stack (CentOS Linux,
\nApache 2, MySQL and PHP). It started on a shared hosting so I had to
\nuse MySQL. Year and a half later, I switched shared, virtual hosting
\nand not run it on a dedicated server. I decided to try Firebird to see
\nhow it performs and also how it compares to MySQL in RAM usage, disk
\nusage, etc.<\/p>\n

The software
\n————–<\/p>\n

The system is CentOS 5.5 64bit with default LAMP stack. I installed
\nFirebird 2.5. RC3 from the .rpm package on Firebird website.
\nSurprisingly, it does not require any additional rpm package \ud83d\ude42<\/p>\n

Converting the database
\n—————————–<\/p>\n

As far as I can tell, there are no tools to do this automatically. I
\ncreated Firebird database and tables by hand, slightly editing the
\nschema dump from phpMyAdmin. This was easy. Loading the data seemed a
\nproblem because default mysqldump places multiple VALUES clasuses in
\nINSERT statements. I used a Postgres tool mysql2pgsql to convert the
\nfile to a more usable form:<\/p>\n

http:\/\/pgfoundry.org\/projects\/mysql2pgsql\/<\/p>\n

I had to alter it a little bit, to avoid prefixing strings with E
\ncharacter. I commented out this line:<\/p>\n

\n#s\/'((?:.*?(?:\\\\')?.*?)*)'([),])\/E'$1'$2\/g; \n# for the E'' see <\/pre>\n
http:\/\/www.postgresql.org\/docs\/8.2\/interactive\/release-8-1.html<\/p>\n
Next problem was that ” and ‘ are escaped with backslash \\.<\/p>\n
With Firebird ” does not need escaping and ‘ is escaped with another
\n‘, becomes ”. A simple sed command to fix this:
\n
\ncat postgres.sql | sed s1\\\\\\\\\\\"11g | sed s1\\\\\\\\\\'1\\'\\'1g > firebird.sql
\n<\/code>
\nA few more manual edits were needed to remove the CREATE TABLE and
\nsimilar stuff, because I only needed data. After that I added
\n“commit;” to the end of the script and ran it via isql:
\n
\n\/opt\/firebird\/bin\/isql \/var\/db\/firebird\/s.fdb -user sysdba -pass
\n******** -i firebird.sql
\n<\/code>
\nthis took some time. Here is the result:<\/p>\n
# du -h -s \/var\/lib\/mysql\/slagalica\/
\n1.9G \/var\/lib\/mysql\/slagalica\/<\/p>\n
# du -h -s \/var\/db\/firebird\/slagalica.fdb
\n2.1G \/var\/db\/firebird\/slagalica.fdb
\n
\nThis is before I created indexed on tables in Firebird database.
\nAfterwards we get:
\n<\/code>
\n2.3G \/var\/db\/firebird\/slagalica.fdb
\n
\nSo, Firebird database is slightly bigger.<\/p>\n
Now, it's time to convert the DB access layer in PHP application, and
\ncompare the perfomance. Stay tuned...<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"
From backwardcompatible.net 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… <\/p>\n
Read More<\/i><\/a><\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[],"class_list":["post-3582","post","type-post","status-publish","format-standard","hentry","category-info"],"yoast_head":"\nConverting MySQL database to Firebird - part1 - Linux Shtuff<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/g33kinfo.com\/info\/converting-mysql-database-to-firebird-part1-2\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Converting MySQL database to Firebird - part1 - Linux Shtuff\" \/>\n<meta property=\"og:description\" content=\"From backwardcompatible.net 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... Read More\" \/>\n<meta property=\"og:url\" content=\"https:\/\/g33kinfo.com\/info\/converting-mysql-database-to-firebird-part1-2\/\" \/>\n<meta property=\"og:site_name\" content=\"Linux Shtuff\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/fb.me\/g33kinf0\" \/>\n<meta property=\"article:author\" content=\"https:\/\/fb.me\/g33kinf0\" \/>\n<meta property=\"article:published_time\" content=\"2010-10-23T19:24:19+00:00\" \/>\n<meta name=\"author\" content=\"g33kadmin\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@https:\/\/twitter.com\/drsinger1111\" \/>\n<meta name=\"twitter:site\" content=\"@drsinger1111\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/converting-mysql-database-to-firebird-part1-2\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/converting-mysql-database-to-firebird-part1-2\\\/\"},\"author\":{\"name\":\"g33kadmin\",\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/#\\\/schema\\\/person\\\/c022e4c40b13ea1b678e6f020756f547\"},\"headline\":\"Converting MySQL database to Firebird – part1\",\"datePublished\":\"2010-10-23T19:24:19+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/converting-mysql-database-to-firebird-part1-2\\\/\"},\"wordCount\":317,\"publisher\":{\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/#\\\/schema\\\/person\\\/c022e4c40b13ea1b678e6f020756f547\"},\"articleSection\":[\"General Info\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/converting-mysql-database-to-firebird-part1-2\\\/\",\"url\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/converting-mysql-database-to-firebird-part1-2\\\/\",\"name\":\"Converting MySQL database to Firebird - part1 - Linux Shtuff\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/#website\"},\"datePublished\":\"2010-10-23T19:24:19+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/converting-mysql-database-to-firebird-part1-2\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/g33kinfo.com\\\/info\\\/converting-mysql-database-to-firebird-part1-2\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/converting-mysql-database-to-firebird-part1-2\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Converting MySQL database to Firebird – part1\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/#website\",\"url\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/\",\"name\":\"Linux Shtuff\",\"description\":\"Because I have CRS Syndrome...\",\"publisher\":{\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/#\\\/schema\\\/person\\\/c022e4c40b13ea1b678e6f020756f547\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/#\\\/schema\\\/person\\\/c022e4c40b13ea1b678e6f020756f547\",\"name\":\"g33kadmin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/wp-content\\\/uploads\\\/2022\\\/07\\\/minion-researchA.gif\",\"url\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/wp-content\\\/uploads\\\/2022\\\/07\\\/minion-researchA.gif\",\"contentUrl\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/wp-content\\\/uploads\\\/2022\\\/07\\\/minion-researchA.gif\",\"width\":512,\"height\":512,\"caption\":\"g33kadmin\"},\"logo\":{\"@id\":\"https:\\\/\\\/g33kinfo.com\\\/info\\\/wp-content\\\/uploads\\\/2022\\\/07\\\/minion-researchA.gif\"},\"description\":\"I am a g33k, Linux blogger, developer, student and Tech Writer for Liquidweb.com\\\/kb. My passion for all things tech drives my hunt for all the coolz. I often need a vacation after I get back from vacation....\",\"sameAs\":[\"https:\\\/\\\/thelinuxreport.com\",\"https:\\\/\\\/fb.me\\\/g33kinf0\",\"https:\\\/\\\/x.com\\\/https:\\\/\\\/twitter.com\\\/drsinger1111\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Converting MySQL database to Firebird - part1 - Linux Shtuff","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/g33kinfo.com\/info\/converting-mysql-database-to-firebird-part1-2\/","og_locale":"en_US","og_type":"article","og_title":"Converting MySQL database to Firebird - part1 - Linux Shtuff","og_description":"From backwardcompatible.net 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... Read More","og_url":"https:\/\/g33kinfo.com\/info\/converting-mysql-database-to-firebird-part1-2\/","og_site_name":"Linux Shtuff","article_publisher":"https:\/\/fb.me\/g33kinf0","article_author":"https:\/\/fb.me\/g33kinf0","article_published_time":"2010-10-23T19:24:19+00:00","author":"g33kadmin","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/twitter.com\/drsinger1111","twitter_site":"@drsinger1111","schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/g33kinfo.com\/info\/converting-mysql-database-to-firebird-part1-2\/#article","isPartOf":{"@id":"https:\/\/g33kinfo.com\/info\/converting-mysql-database-to-firebird-part1-2\/"},"author":{"name":"g33kadmin","@id":"https:\/\/g33kinfo.com\/info\/#\/schema\/person\/c022e4c40b13ea1b678e6f020756f547"},"headline":"Converting MySQL database to Firebird – part1","datePublished":"2010-10-23T19:24:19+00:00","mainEntityOfPage":{"@id":"https:\/\/g33kinfo.com\/info\/converting-mysql-database-to-firebird-part1-2\/"},"wordCount":317,"publisher":{"@id":"https:\/\/g33kinfo.com\/info\/#\/schema\/person\/c022e4c40b13ea1b678e6f020756f547"},"articleSection":["General Info"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/g33kinfo.com\/info\/converting-mysql-database-to-firebird-part1-2\/","url":"https:\/\/g33kinfo.com\/info\/converting-mysql-database-to-firebird-part1-2\/","name":"Converting MySQL database to Firebird - part1 - Linux Shtuff","isPartOf":{"@id":"https:\/\/g33kinfo.com\/info\/#website"},"datePublished":"2010-10-23T19:24:19+00:00","breadcrumb":{"@id":"https:\/\/g33kinfo.com\/info\/converting-mysql-database-to-firebird-part1-2\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/g33kinfo.com\/info\/converting-mysql-database-to-firebird-part1-2\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/g33kinfo.com\/info\/converting-mysql-database-to-firebird-part1-2\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/g33kinfo.com\/info\/"},{"@type":"ListItem","position":2,"name":"Converting MySQL database to Firebird – part1"}]},{"@type":"WebSite","@id":"https:\/\/g33kinfo.com\/info\/#website","url":"https:\/\/g33kinfo.com\/info\/","name":"Linux Shtuff","description":"Because I have CRS Syndrome...","publisher":{"@id":"https:\/\/g33kinfo.com\/info\/#\/schema\/person\/c022e4c40b13ea1b678e6f020756f547"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/g33kinfo.com\/info\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/g33kinfo.com\/info\/#\/schema\/person\/c022e4c40b13ea1b678e6f020756f547","name":"g33kadmin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/g33kinfo.com\/info\/wp-content\/uploads\/2022\/07\/minion-researchA.gif","url":"https:\/\/g33kinfo.com\/info\/wp-content\/uploads\/2022\/07\/minion-researchA.gif","contentUrl":"https:\/\/g33kinfo.com\/info\/wp-content\/uploads\/2022\/07\/minion-researchA.gif","width":512,"height":512,"caption":"g33kadmin"},"logo":{"@id":"https:\/\/g33kinfo.com\/info\/wp-content\/uploads\/2022\/07\/minion-researchA.gif"},"description":"I am a g33k, Linux blogger, developer, student and Tech Writer for Liquidweb.com\/kb. My passion for all things tech drives my hunt for all the coolz. I often need a vacation after I get back from vacation....","sameAs":["https:\/\/thelinuxreport.com","https:\/\/fb.me\/g33kinf0","https:\/\/x.com\/https:\/\/twitter.com\/drsinger1111"]}]}},"_links":{"self":[{"href":"https:\/\/g33kinfo.com\/info\/wp-json\/wp\/v2\/posts\/3582","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/g33kinfo.com\/info\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/g33kinfo.com\/info\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/g33kinfo.com\/info\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/g33kinfo.com\/info\/wp-json\/wp\/v2\/comments?post=3582"}],"version-history":[{"count":0,"href":"https:\/\/g33kinfo.com\/info\/wp-json\/wp\/v2\/posts\/3582\/revisions"}],"wp:attachment":[{"href":"https:\/\/g33kinfo.com\/info\/wp-json\/wp\/v2\/media?parent=3582"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/g33kinfo.com\/info\/wp-json\/wp\/v2\/categories?post=3582"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/g33kinfo.com\/info\/wp-json\/wp\/v2\/tags?post=3582"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}