How to create a utf8 database from a latin1 database backup

So I had an old database and it was using latin1 charset.
At some point we have moved to a new webserver and a new mysql database was created using utf8 charset.

After restoring a data backup from the old database — we saw a lot of symbols like  so that a quick fix was to set explicitly charset to latin1 like so:


After some time playing around with backups it turned out that if you create a backup in latin1 charset and then restore it in schema that is using utf8 charset — it actually becomes utf8 data so it is automatically converted for you.

This is how you can make your backup:

Before restoring data I’ve found that in backup.sql file there was latin1 charset set manually for one of the tables (like so: DEFAULT CHARSET=latin1) so I’ve just replaced that one with DEFAULT CHARSET=utf8 :)

After that you can import your data to a new / old schema that is using utf8 charset and — ta-daaaaam your data is in utf8 now.

UPDATE: it is better to use utf8mb4 collation for your schema as Dmitry Komanov suggests in comments :)

One clap, two clap, three clap, forty?

By clapping more or less, you can signal to us which stories really stand out.