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 :)