Melhor maneira de garantir que um banco de dados MySQL esteja totalmente em UTF8

Question

Depois de alguns problemas com as strings UTF8 e none-UTF8, estamos padronizando o UTF8. Uma coisa que eu preciso fazer é verificar se tudo está em UTF8 no banco de dados MySQL? O que preciso verificar?

• Conjunto de caracteres padrão do servidor
• Conjunto de caracteres padrão de cada banco de dados
• Cada coluna de texto tem um conjunto de caracteres? Como faço para verificar isso?

Eu estava pensando em colocar tudo isso em um plugin nagios para verificar se tudo está em UTF8. Sugestões?

    

Answer 1

Rory, Primeiro de tudo, você está certo em querer monitorar o que é criado em seus bancos de dados. Enquanto todos nós implementamos as etapas para evitar erros, você não pode presumir que os erros não irão se arrastar. Eu faço uma coisa muito parecida com a que a maioria de nossa infraestrutura exige UTF8.

As seguintes consultas são boas para verificar as estatísticas:

SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME, COUNT(0) AS COUNT FROM information_schema.SCHEMATA GROUP BY DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME;
# to filter schema use SCHEMA_NAME in the where clause
SELECT TABLE_COLLATION, COUNT(0) AS COUNT FROM information_schema.TABLES WHERE TABLE_COLLATION IS NOT NULL GROUP BY TABLE_COLLATION;
# to filter schema use TABLE_SCHEMA in the where clause
SELECT CHARACTER_SET_NAME, COLLATION_NAME, COUNT(0) AS COUNT FROM information_schema.COLUMNS WHERE CHARACTER_SET_NAME IS NOT NULL AND COLLATION_NAME IS NOT NULL GROUP BY CHARACTER_SET_NAME, COLLATION_NAME;
# to filter schema use TABLE_SCHEMA in the where clause

As seguintes consultas seriam boas em um cron job que envia um e-mail para você se algum resultado for encontrado:

SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE DEFAULT_CHARACTER_SET_NAME NOT LIKE '%utf8%' OR DEFAULT_COLLATION_NAME NOT LIKE '%utf8%';
# to filter schema use SCHEMA_NAME in the where clause
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_COLLATION IS NOT NULL AND TABLE_COLLATION NOT LIKE '%utf8%';
# to filter schema use TABLE_SCHEMA in the where clause
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE CHARACTER_SET_NAME NOT LIKE '%utf8%' OR COLLATION_NAME NOT LIKE '%utf8%';
# to filter schema use TABLE_SCHEMA in the where clause

Essas consultas funcionam para o MySQL > = 5.0. Se você precisar de ajuda para escrever o cron job, me avise.

Eu também tenho um problema com meus desenvolvedores (principalmente o time do Ruby on Rails) criando tabelas InnoDB quando eles não precisam deles. Para manter isso em segredo, monitorei o banco de dados de preparação com um cron que chama:

SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE ENGINE = 'InnoDB';

    

Answer 2

Eu faria algo como mysqldump --all-databases --no-data | grep -i character

    

Answer 3

O Jure1873 sugere o uso do grep, e eu comentei que o awk era mais apropriado. Aqui está o que eu joguei juntos.

mysqldump --all-databases --no-data | awk '{gsub("[';]","");};/^USE /{db=$2;};/CREATE TABLE /{table=$3;};/^\) ENGINE/{sub(".*CHARSET=","");if(!match($1,"utf8")){print db ":" table ":" $1;}}'

Aqui está com maior legibilidade:

mysqldump --all-databases --no-data | \
awk '
    {
        gsub("[';]","");
    };
    /^USE /{
        db=$2;
    }; \
    /CREATE TABLE /{
        table=$3;
    }; \
    /^\) ENGINE/{
        sub(".*CHARSET=","");
        if(!match($1,"utf8")){
            print db ":" table ":" $1; 
        }   
    }   
'

    

Answer 4

O Nagios é o tipo errado de ferramenta para isso. Nagios é mais para monitoramento de serviço, não verificar a configuração está errado. Uma ferramenta melhor seria algum tipo de ferramenta de gerenciamento de configuração, como puppet ou cfengine, para garantir que os arquivos de configuração tenham as configurações corretas.

No que diz respeito à configuração, o manual do MySQL tem um capítulo em conjuntos de caracteres e codificação.

    

Answer 5

dbs=$(echo 'show databases;' | mysql | grep -v '^Database$' | tr "\n" " ")

for d in ${dbs}; do
 tables=$(echo 'show tables' | mysql $d | grep -v '^Tables' | tr "\n" " ")
 for t in ${tables}; do
  echo "**************** DB ${d} TABLE ${t}"
  mysqldump --no-data ${d} ${t} | grep  COLLATE | grep -v utf && echo "DB ${d} TABLE ${t} !!!!"
  mysqldump --no-data ${d} ${t} | grep  CHARSET | grep -v utf && echo "DB ${d} TABLE ${t} !!!!"
 done
done