shrink do arquivo ibdata1 do MySQL

Como comprimir um arquivo ibdata1 do MySQL com mínimo tempo de inatividade 

Olá a todos,

Neste artigo vou tratar do banco de dados MySQL e vou usar como desculpas que a Oracle é proprietária desse, então me permito a tratar do mesmo.

Na verdade, ocorreu a necessidade desse trabalho no serviço e achei interessante postar e dividir com vocês a forma que efetuei o processo, evitando que o banco estivesse indisponível por muito tempo.

A configuração padrão do MySQL para tabelas de banco de dados é no formato InnoDB e cria um arquivo de armazenamento maciço chamado 'ibdata1'. 

Basicamente, o arquivo ibdata1 contém os dados da tabela de suas tabelas InnoDB. Em grandes ambientes de produção, este arquivo pode crescer e ser extremamente grande. Em alguns dos servidores que eu administro, eu vi esse arquivo exceder tamanhos de 30GB. No caso de hoje, foi tratado um banco com 12GB e era necessário não indisponibilizar o mesmo por muito tempo. 

Corrigindo o tamanho do arquivo, obviamente, tem o efeito de limitar a quantidade total de dados que podem ser armazenados em tabelas InnoDB, de modo que não é uma opção viável.

O arquivo ibdata1 é, por padrão "auto-crescimento" ou "auto-incremento", por isso vai inflar quanto mais dados são colocados em tabelas InnoDB. Após os registros serem excluídos das tabelas InnoDB, o arquivo irá conter páginas marcadas como "livre", que poderia ser usado para dados futuros, mas o arquivo em si não reduzirá de tamanho.

O arquivo ibdata1 realmente não pode ser reduzido a menos que você exclua todos os bancos de dados, remova os arquivos e recarregar um dump / backup. Eu vim com uma solução para fazer isso com o mínimo de inatividade para o serviço do MySQL (o tempo que leva para um reinício do serviço normal).

Para fazer isso, é necessário que você tenha espaço em disco suficiente para dobrar o seu espaço de armazenamento mySQL. Nós iremos, essencialmente, desenvolver um novo serviço em uma área temporária e em seguida, importar os dados de volta usando a opção file-per-table.

CUIDADO: Certifique-se de que você tem backups de seus dados antes de executar essas operações. É possível que algo possa dar errado e estragar o seu dia / semana / mês / ano.

Passos para diminuir arquivo ibdata1: 
IMPORTANTE: Antes de reduzir o arquivo ibdata, não se esqueça que o my.cnf está configurado para usar arquivos separados por tabela InnoDB:
sed '/innodb_file_per_table/d' -i /etc/my.cnf
echo 'innodb_file_per_table' >> /etc/my.cnf

1. Verificar toda lista de tabelas mysql> SELECT TABLE_SCHEMA,TABLE_NAME FROM information_schema.tables WHERE ENGINE = 'InnoDB'
2. Dump todo a base de dados para /root/all-databases.sql/usr/bin/mysqldump --extended-insert --all-databases --add-drop-database --disable-keys --flush-privileges --quick --routines --triggers > /root/all-databases.sql
3. Prepare um local secundário para que possamos realizar nosso trabalhomkdir /var/lib/mysql2
4. Sincronizar as duas áreas com seus privilégios
   rsync -avz /var/lib/mysql/mysql /var/lib/mysql2
5. Setar as permissões corretamente
   chown -R mysql.mysql /var/lib/mysql2
6. Carregar a nova instância mysqld, permitindo-lhe criar um ambiente inovado do ibdata1
   Nota: para evitar conflitos estamos usando arquivos temporários pid/socket/error, e pulando ligação de rede 3306:/usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql2 --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql2/error.log --pid-file=/var/lib/mysql2/temp.pid --skip-networking --socket=/var/lib/mysql2/dirty.sock
7. Confira o novo ambiente, garantindo não há outros além do "information_schema" e bancos de dados "mysql"
   # ls -lah /var/lib/mysql2
   total 29M
   drwxr-xr-x 3 mysql mysql 4.0K Aug 2 08:46 ./
   drwxr-xr-x 28 root root 4.0K Aug 2 08:39 ../
   srwxrwxrwx 1 mysql mysql 0 Aug 2 08:46 dirty.sock=
   -rw-rw---- 1 mysql mysql 7.2K Aug 2 08:46 error.log
   -rw-rw---- 1 mysql mysql 18M Aug 2 08:41 ibdata1-rw-rw---- 1 mysql mysql 5.0M Aug 2 08:46 ib_logfile0
   
   -rw-rw---- 1 mysql mysql 5.0M Aug 2 08:41 ib_logfile1
   drwx--x--x 2 mysql mysql 4.0K Jun 27 07:27 mysql/
   -rw-rw---- 1 mysql mysql 5 Aug 2 08:46 temp.pid
   # mysql -S /var/lib/mysql2/dirty.sock
   mysql> show databases;
   +--------------------+
   | Database |
   +--------------------+
   | information_schema |
   | mysql |
   +--------------------+
   2 rows in set (0.00 sec)
8. Para uma importação bem-sucedida, é preciso eliminar as tabelas de log. Para fazer isso, precisamos desativá-las temporariamente:
   mysql> SET @old_log_state = @@global.general_log;
   mysql> SET GLOBAL general_log = 'OFF';
   mysql> ALTER TABLE mysql.general_log ENGINE = MyISAM;
   mysql> SET @old_log_state = @@global.slow_query_log;
   mysql> SET GLOBAL slow_query_log = 'OFF';
   mysql> SET GLOBAL log_slow_queries = 'OFF';
   mysql> ALTER TABLE mysql.slow_query_log ENGINE = MyISAM;
9. Ainda no console para o servidor secundário, desabilite as configurações de chave estrangeira e de importação, em seguida, permitir verificação de chaves estrangeiras novamente
   # mysql -S /var/lib/mysql2/dirty.sock
   mysql> SET FOREIGN_KEY_CHECKS=0;
   mysql> SOURCE /root/all-databases.sql;
   mysql> SET FOREIGN_KEY_CHECKS=1;
10. Por fim, desligue ambos os servidores e mova o novo diretório mysql no lugar do original. Após o reinício, a sua conversão e contração deve ser bem sucedida.
    
    # service mysql stop
    # killall mysqld
    # mv /var/lib/mysql /var/lib/mysql.old
    # mv /var/lib/mysql2 /var/lib/mysql
    # service mysql start
    Nota:logging será automaticamente ligado novamente desde que você tenha as configurações adequadas definidas em seu my.cnf

É isso pessoal, a idéia é que agora, tenha um "ambiente cópia" do original no lugar desse. Espero ter ajudado, novamente, e que possa ser útil. 
Boa sorte a todos e até um próximo post.