Dê uma olhada no Xtrabackup de Percona, que permite backups quentes e é totalmente gratuito.
Atualmente estou usando o mysqldump para fazer backups que são lentos, mas OKish. O grande problema é restaurar o banco de dados, o que leva alguns dias. O despejo é de cerca de 7GB gzipped, então não é um banco de dados pequeno, mas não deve estar fora do alcance razoável com o mysql.
Então, quais são minhas outras opções? Algo como o mysqlhotcopy seria perfeito.
Dê uma olhada no Xtrabackup de Percona, que permite backups quentes e é totalmente gratuito.
Se você tem --innodb_file_per_table habilitado do que você pode mover um arquivo .ibd e a tabela associada de um banco de dados para outro, use uma instrução RENAME TABLE:
RENAME TABLE db1.tbl_name TO db2.tbl_name;
Se você tiver um backup “limpo” de um arquivo .ibd, poderá restaurá-lo para a instalação do MySQL da qual foi originado da seguinte forma:
Emita esta instrução ALTER TABLE para excluir o arquivo .ibd atual:
ALTER TABLE tbl_name DISCARD TABLESPACE;
Copie o arquivo .ibd de backup para o diretório de banco de dados apropriado.
Emita esta instrução ALTER TABLE para dizer ao InnoDB para usar o novo arquivo .ibd para a tabela:
ALTER TABLE tbl_name IMPORT TABLESPACE;
Neste contexto, um backup de arquivo .ibd “limpo” é aquele para o qual os seguintes requisitos são satisfeitos:
Não há modificações não comprometidas por transações no arquivo .ibd.
Não há entradas de buffer de inserção não mescladas no arquivo .ibd.
A remoção removeu todos os registros de índices marcados com exclusão do arquivo .ibd.
O mysqld liberou todas as páginas modificadas do arquivo .ibd do buffer pool para o arquivo.
Você pode fazer um arquivo .ibd de backup limpo usando o seguinte método:
Pare todas as atividades do servidor mysqld e confirme todas as transações.
Aguarde até que SHOW ENGINE INNODB STATUS mostre que não há transações ativas no banco de dados, e o status do thread principal do InnoDB está esperando pela atividade do servidor. Então você pode fazer uma cópia do arquivo .ibd.
Outro método para fazer uma cópia limpa de um arquivo .ibd é usar a ferramenta comercial Hot Backup do InnoDB
Existe a ferramenta InnoDB / Oracle Hot Backup , que custa dinheiro. Eu nunca usei, mas presumo que faz o que diz na lata.
Pessoalmente, faço um instantâneo de LVM das minhas tabelas InnoDB e faço backup do snapshot - na restauração, parece que ocorreu uma falha do sistema no InnoDB, que passa pelo processo normal de repetição de log na inicialização. Eu gosto da abordagem de cinturão e braceletes para backups, então eu combino o snapshot-e-regular de rsync s com mysqldump s (e às vezes é útil ter um despejo de SQL bastante recente por aí).
Em vez de uma solução de despejo / restauração, suspeito que sei o motivo pelo qual a restauração atual está demorando muito.
Temos alguns bancos de dados bastante grandes (até 40 GB) e restauramos seu tamanho em menos de uma hora.
Uma coisa que o mysqldump fará para tornar as inserções mais rápidas durante a restauração é desativar os índices, executar todas as inserções e depois aplicar os índices às suas tabelas.
Se algumas variáveis do mysql não forem grandes o suficiente, o mysql dirá que é "consertando pelo cache de chaves" ou algo similar ... isso é muito, muito, muito lento.
Se as variáveis corretas forem grandes o suficiente, ele dirá "repare, classificando", o que é muito, muito mais rápido.
Eu não consigo lembrar as variáveis exatas do topo da minha cabeça, mas se isso soa como pode ser o caso, me avise em um comentário e eu posso ver se consigo rastrear os detalhes.
Como Nick mencionou, o Xtrabackup de Percona também merece uma boa olhada.
Se você tem alguma tabela MyISAM, sugiro alterá-las para o InnoDB. Aqui está o que eu uso:
mysql -u root --password=<password> --database=db_name -B -N -e "SHOW TABLES" | awk '!/not_this_db/ && !/or_this_one/ && /^[a-z]/ {print "ALTER TABLE", $1, "ENGINE=INNODB;"}' | mysql -u root --password=<password> --database=db_name
Você pode excluir e incluir bancos de dados com o regex awk, como somente dbs começando com uma letra minúscula no meu exemplo acima. Isso trancará as mesas durante a alteração.
Em seguida, use o xtrabackup para copiar o banco de dados inteiro diretamente para outro servidor sem bloquear nenhuma tabela ou usar muito IO do disco (depois de configurar as chaves ssh rsa):
innobackupex --throttle=500 --compress --stream=xbstream /doesntneedtoexist | ssh user@otherhost "xbstream -x -C /root/backup/"
e, em seguida, você pode aplicar a etapa de registro de aplicativos completamente separada e economizar espaço em disco, E / S e CPU no servidor de produção.
Se você estiver usando o InnoDB, então existem muitos parâmetros que você pode configurar para um melhor desempenho. Se estiver usando o InnoDB, então é silencioso, no seu caso, que seu innodb_file_per_table seja muito pequeno. Aumente o tamanho desse parâmetro para pelo menos 50% do total de RAM e verifique o tempo necessário para a restauração.
Além disso, você pode aumentar o parâmetro innodb_thread_concurrency.
Tente também o xtrabackup Percona. Eu estou usando para meus TBs de Db e isso funciona perfeitamente.
Eu adicionei suporte para hotcopies ao mysql_manager rubygem, mas ele suporta apenas hotcopies de todo o diretório mysql. Isso é feito primeiro fazendo rsyncs repetitivos sem bloqueios de tabela até que a duração da sincronização seja tolerável e, em seguida, obtenha um bloqueio usando FLUSH TABLES WITH READ LOCK antes de fazer um rsync final. Como está usando o rsync, ele também suporta hosts remotos.
O uso é bem fácil.
Primeiro instale a gema.
gem install mysql_manager
Veja como você pode executá-lo.
mysql-manager --hotcopy \
--hotcopy:data-dir /var/lib/mysql/ \
--hotcopy:backup-dir [email protected]:/tmp/mysql/ \
--hotcopy:rsync-args "-av --exclude=*.err" \
--hotcopy:rsync-ttl 30 \
--db:user root \
--db:pass $MYSQL_ROOT_PASSWORD