Terrible Mysql collation issue, help me fix it

[jair101]

My board is in non english language. Right now I am on a shared hosting and the database settings and collations there are complete mess.

===================================================

I have this setting in my ACP:

Document Character Set: UTF-8

Character conversion method: internal

This is the result from show variables in mysql:

character_set_client cp1251

character_set_connection cp1251

character_set_database cp1251

character_set_filesystem binary

character_set_results cp1251

character_set_server cp1251

character_set_system utf8

character_sets_dir /usr/share/mysql/charsets/

collation_connection cp1251_general_ci

collation_database cp1251_bulgarian_ci

collation_server cp1251_bulgarian_ci

On my phpmyadmin I see:

Server connection collation: utf_general_ci

My table collations are:

cp1251_bulgarian_ci

most of the text fields there are also cp1251_bulgarian_ci

=====================================================

All of this mess somehow works. I say somehow, because search in my language is not working properly and also the database in phpmyadmin looks like this, which is not native Bulgarian :)

But it works as far as all letters are displayed properly to the members and aside from the search problem everything looks fine. This is on shared hosting and I don't have access to mysql system variables to fix everything anyway. Now, I plan to move to VPS where I have all the control and I want to convert everything to UTF8.

What is the proper strategy to export the database and consequently import it? What system settings I need to use for mysql?

I ask this, because I tried to export the database with mysqldump and it didn't work. Tried through phpmyadmin - didn't work either. I tried with one specifc tool and all the time when I import it on the new server I get squares and question marks in the forum. I noticed that my system variables on the new server by default were latin1, so I fixed my my.cnf file to set it to utf8, the result was:

Your settings could not be read by IP.Board. This is a fatal error and IP.Board cannot function while this issue persists.
This issue is generally caused by changing your character set in the ACP to one that does not support data stored in the rest of your settings, or by restoring a database backup/completing a server transfer and importing your database tables using the wrong character set or collation. You should contact IPS Technical Support for further assistance.

So it looks like I cannot fix it. If I try to use utf8 in the database I see squares and question marks. If I try to setup the connection and server collation to utf8, then it dies completely.

Can anyone guide me step by step what is the proper way to export the database from my shared server, fix my new server system variables and import it on the new server in a way that there are no issues?

[jair101]

Well, after many many hours, I somehow got it working. Don't ask how. There are just tons of places where you have diffrenet settings, collations, characters and apparently if they are not in sync everything falls apart. Its a nightmare. Hope the problem won't come back.