Como despejar eficientemente um enorme banco de dados innodb do MySQL?

Question

Eu tenho um servidor de banco de dados MySQL de produção Ubuntu 10.04 onde o tamanho total do banco de dados é de 260 GB enquanto o tamanho da partição raiz é 300 GB onde o banco de dados é armazenado, essencialmente significa que cerca de 96% de / está cheio e não há espaço para armazenar despejo / backup etc. Nenhum outro disco está conectado ao servidor a partir de agora.

Minha tarefa é migrar esse banco de dados para outro servidor instalado em um datacenter diferente. A questão é como fazer isso de forma eficiente com o mínimo de tempo de inatividade?

Estou pensando em:

• Solicite anexar uma unidade extra ao servidor e fazer um despejo nessa unidade. [EDIT: Não é possível agora.]
• Transferir o despejo para o novo servidor, restaurá-lo e tornar o novo servidor escravo do existente para manter os dados em sincronia
• Quando a migração for necessária, interrompa a replicação, atualize a configuração do escravo para aceitar solicitações de leitura / gravação e torne o servidor antigo somente leitura para que não receba solicitações de gravação e informe aos desenvolvedores de aplicativos que atualizem a configuração com novo endereço IP para db .

Qual é a sua sugestão para melhorar esta ou qualquer abordagem melhor alternativa para esta tarefa?

    

Answer 1

Se você estiver pensando em migrar para outro servidor de banco de dados com a mesma versão exata do MySQL, talvez queira rsync the datadir do servidor antigo para o novo servidor.

Isso funcionará independentemente do layout do arquivo InnoDB ou até mesmo da presença de tabelas MyISAM.

1. instala a mesma versão do mysql no ServerB que o ServerA possui
2. No ServerA, execute RESET MASTER; para apagar todos os logs binários antes do processo rsycn. Se o log binário não estiver ativado, você pode pular esta etapa.
3. No ServerA, execute SET GLOBAL innodb_max_dirty_pages_pct = 0; do mysql e cerca de 10 minutos (Isso limpa páginas sujas do Buffer Pool do InnoDB. Também ajuda a executar um desligamento do mysql mais rápido) Se seu banco de dados for todo MyISAM, você pode pular esta etapa. / li>
4. rsync / var / lib / mysql do ServerA para / var / lib / mysql no ServerB
5. Repita a Etapa 3 até que um rsync demore menos de 1 minuto
6. service mysql stop no ServerA
7. Execute mais um rsync
8. scp ServerA: /etc/my.cnf para ServerB: / etc /.
9. service mysql start no servidorB
10. service mysql start no ServerA (opcional)

Essencialmente, aqui está o que tal script gostaria que fosse

mysql -u... -p... -e"RESET MASTER;"
mysql -u... -p... -e"SET GLOBAL innodb_max_dirty_pages_pct = 0;"
RSYNCSTOTRY=10
cd /var/lib/mysql
X=0
while [ ${X} -lt ${RSYNCSTOTRY} ]
do
    X='echo ${X}+1|bc'
    rsync -r * targetserver:/var/lib/mysql/.
    sleep 60
done
service mysql stop
rsync -r * targetserver:/var/lib/mysql/.
service mysql start

Um colega do DBA StackExchange disse que eu deveria ficar longe de FLUSH TABLES WITH READ LOCK; baseado em algo no mysqlperformanceblog.com

Eu li e aprendi que SELECTs contra tabelas InnoDB no meio de um FLUSH TABLES WITH READ LOCK; ainda podem permitir que as gravações ocorram de alguma forma. Como apontado no comentário de Arlukin , o LVM trabalharia com FLUSH TABLES WITH READ LOCK no InnoDB (1 para o seu comentário ).

• link
• link

Para todos os usuários não-LVM, você está OK com um banco de dados all-MyISAM para uso com FLUSH TABLES WITH READ LOCK; . Para o InnoDB, fique com --single-tranaction usage no mysqldumps, por favor.

    

Answer 2

Um despejo e restauração de um banco de dados desse tamanho levaria horas. Eu iria, dependendo das versões do mysql, desde que o número da versão aumenta e não há saltos no número de revisão principal. Você deve ser capaz de pegar os arquivos de banco de dados brutos em / var / lib / mysql e colocá-los no novo servidor, definir as permissões e acionar o servidor com a opção --skip-grant-tables. Adicione as concessões necessárias para os usuários refletindo o novo endereço IP e, em seguida, reinicie normalmente.

Eu diria o tamanho do seu banco de dados, pois ele é grande demais para ser eficiente.

    

Answer 3

Você pode seguir estas etapas para migrar esse enorme banco de dados InnoDB.

• Instale o SSHFS e monte a partição relevante do servidor remoto no servidor de produção
• Use o Percona XtraBackup para obter um hotcopy do banco de dados InnoDB e salvá-lo diretamente no diretório montado do SSHFS

• Essa tarefa levará várias horas. Para minimizar o efeito do script hotcopy no servidor live, defina a prioridade baixa usando o renice

  $ renice -n 5 -p <SCRIPT-PID>

• Certifique-se de que ambos os servidores estejam executando a mesma versão do servidor MySQL.
• Quando a hotcopy estiver concluída, você poderá restaurá-la no novo servidor, iniciar o processo de replicação

Você pode sentir lentidão durante esse processo, mas definitivamente não há tempo de inatividade. O Percona XtraBackup criará um hotcopy que consome menos recursos do que o mysqldump. Isso é ideal para um enorme banco de dados InnoDB.

Dependendo dos padrões e estatísticas de uso, você pode executar esse processo quando houver tráfego mínimo no servidor. Talvez fazer isso no fim de semana seja uma boa ideia? O acima é apenas um esboço do processo. Você pode precisar passar pela documentação do Percona XtraBackup e SSHFS.

    

Answer 4

Você poderia simplesmente despejar o banco de dados diretamente para o servidor remoto ...

$ mysqldump | ssh user@server 'cat - > dumpfile.sql.gz'

... O SQL deve compactar bem, então você deve fazer isso muito mais rápido com uma dessas opções, embora também dependa da quantidade de RAM que você tem na caixa ...

$ mysqldump | ssh -C user@server 'cat - > dumpfile.sql.gz'
$ mysqldump | gzip -c | ssh user@server 'cat - > dumpfile.sql.gz'