Over the past many years I have been involved in projects requiring localization into many different languages. One issue with this is how data is stored in the database.

As we support and help various customers with Postgresql related database issues, we still find users who are encoding their databases in SQL_ASCII. Perhaps there is a reason for this, if there is I haven’t figured it out yet.

In many cases, this eventually causes them problems and they end up having to switch from SQL_ASCII or some other encoding over to UTF8.

There may be more, but I know of two different ways of converting the encoding of the database to UTF8.

One way is

iconv -f iso-8859-1 -t utf-8 dump_file dump_file_recoded

The preferred way however is to use the -E option of the pg_dump command like this:

pg_dump -U postgres -W -E UTF8 -d pg_bench >pgbench.backup.

the -W command forces you to put in a password.

For more details on how to use the pg_dump command, here are the docs:

http://www.postgresql.org/docs/8.3/interactive/app-pg_dump.html

SVN is great for keeping our projects under tight control. Occasionally, we have the need to get something back that was deleted in a previous revision. So I can remember how to do it next time, here it is:

$ svn copy -r 1234 url/to/deleted/file path/to/recovered/file

This will copy the file at the revision specified to the new file in the “restore to path” part. You can find the revision by doing an ’svn log –verbose’ of the directory it was in. That’s all there is to it!

My theme seems to be restoring and recovering… is that a bad thing?

This is a quick post to possibly save someone some time when they google for restoring mysql data files. The gist of it is, don’t try to copy InnoDB files directly into your data directory, it won’t work.

Background

Sometime back I wiped my development box clean and reinstalled the OS from scratch. It’s a wonderful feeling to start fresh and get rid of all the old whacky configs and random junk that collects on a filesystem. Prior to this cleansing, I backed up all the junk I considered save-worthy. One thing I saved was they MySQL data directory. Many of the databases were old projects and at the time I didn’t think I’d need to look at them anytime soon. Well, anytime soon arrived yesterday in the form of me really wanting to see how I had implemented a particular feature in an old project. The database component of that project was essential, so I couldn’t just glance at the code, I wanted to get it running again with the database backend.

Problem

I probably should have known better, but I decided to just try to shutdown mysql and copy the files directly to my new data directory. After some permission and user adjusting, that seemed to work… until I actually tried to access the restored database I needed, which used mostly InnoDB tables. They were not showing up or working properly. A quick glance revealed a file I hadn’t copied which seemed like it *might* be something important:

$ ls -lh /Backup/mysql/data/
-rw-rw----    1 brian  staff   1.0G May 21 10:05 ibdata1

Yeah, oops. So InnoDB stores things a bit differently than MyISAM.

Solution

The solution turns out to be simple and is what I should have done in the first place.

First, shutdown the mysql server:

$ mysqladmin -u root -p shutdown

Next, startup mysqld with the –datadir option pointing to your old data directory:

$ mysqld --datadir=/Backup/mysql/data/

It may complain about some issue with writing to log files, but that doesn’t matter for this quick export.

Now just dump your database as you normally would (or should have originally):

$ mysqldump -u root -p database_name > database_name.sql

Lastly, shutdown mysqld again, start mysqld normally and then do the import as usual:

$ mysql -u root -p database_name < database_name.sql

(you’ll likely need to create the database first)

There you have it - the way to get your old database files restored into your current setup. There is probably some better easier way to do this, and if someone knows it, clue me in will ya?

This isn’t intended to be a complete tutorial, but for those of us who have previously only run Slony on Linux, this might be a helpful post.
Read the rest of this entry »