System backup of database in non-latin charset

[differentlocal]

Hello!

In my MySQL server I have database utf8_czech_ci collation, but serverwide encoding is windows-1251. Database dump process executes with windows-1251 too, and ignores local database collation. As result I have dump of database with broken Czech symbols.

As normal result I expecting what dump process will use serverwide and database-level collations.

How I can fix or workaround this?

 

[zEitEr]

Hello,

You might need to specify charset in /etc/my.cnf (if it's not done yet)

init-connect="SET NAMES utf8"
character-set-server=utf8
collation-server=utf8_general_ci

Change utf8 with your charsets.

Note, that might break the things on your server, and results from MySQL tables might be shown in ???? or in other symbols. So you need to test it line by line. MySQL server require restart to apply changes.

p.s. Syntax actual for MySQL 5.1.x

 

[differentlocal]

Hello!

This is not good solution, because I have databases in windows-1251 too.

How I can use backup system with databases in different charsets? phpMyAdmin works correclty in this env.

 

[propcgamer]

The easiest way would be to create a separate script that does a mysqldump with the proper charset parameters for that one czech database.

Or depending if your using system backup or admin backup, you might be able to modify the backup scripts to add it in there, but I think having a separate cronjob might be better.

 

[zEitEr]

@differentlocal

Did you try to use not all the lines, but only one of them? Two of them?

As far as I know, when mysqldump is running it uses /usr/local/directadmin/conf/my.cnf if the file exist. So put your directives into the file and check it out.

 

[propcgamer]

@differentlocal

Did you try to use not all the lines, but only one of them? Two of them?

As far as I know, when mysqldump is running it uses /usr/local/directadmin/conf/my.cnf if the file exist. So put your directives into the file and check it out.

I'm not sure how mysql would like that, if most of the tables are windows format.

You can pass the same variables to mysqldump for just the one database.

 

[zEitEr]

I'm not sure how mysql would like that, if most of the tables are windows format.

I have those three lines in /etc/my.cnf of mine. And I still have some tables there in cp1251_general_ci and utf8_general_ci and even latin1_swedish_ci.

So it's OK by me.