Fazendo backup de um banco de dados MySQL de 22 GB diariamente

Question

Agora eu posso fazer o backup usando o mysqldump. Mas eu tenho que derrubar o servidor web e leva cerca de 5 minutos para fazer o backup. Se eu não derrubar o servidor da web, levará uma eternidade e nunca terminará + o site ficará inacessível durante o backup.

Existe uma maneira mais rápida / melhor de fazer backup do meu banco de dados de 22 GB e crescente?

Todas as tabelas são MyISAM.

    

Answer 1

Sim.

Configure a replicação para uma segunda máquina. Quando você precisa fazer um backup, você pode bloquear a máquina secundária, executar mysqlhotcopy ou mysqldump e, em seguida, desbloqueá-la. Ele recuperará o backup com seu mestre e você nunca precisará colocar o mestre off-line.

Você pode até fazer isso na mesma máquina, se não se importar em duplicar a E / S de gravação, mas, idealmente, deve fazer backup em tempo real para um segundo servidor físico e fazer backups de instantâneos com tanta frequência como você precisa, sem atrapalhar seu servidor de produção.

Também é teoricamente possível restaurar um banco de dados usando um estado conhecido e binlogs. Eu nunca fiz isso, então, por favor, investigue primeiro, mas você pode fazer o backup de um estado conhecido do seu banco de dados, depois apenas fazer o backup de todos os novos binlogs e reproduzi-los se precisar restaurar. Como binlogs são escritos linearmente, rsyncing novos binlogs para um computador remoto seria muito rápido.

Edit: De fato, parece que o uso de binlogs para backup está documentado.

Esta questão é altamente relacionada

    

Answer 2

Desculpe-me por assumir que o sistema operacional é o Linux. Se você não está usando o LVM, você deveria estar. Se você é, aqui está uma maneira muito simples de fazer backups via snapshot.

# Define these vars to meet your needs. These meet mine.
lvol="/dev/VolGroup03/lvol0"
snap_name="nightly_snapshot"
snap_vol="$(dirname $lvol)/$snap_name"
snap_path="/mnt/$snap_name"
snap_size="10g" # Not the size of your data, anticipated data changes during the backup
backup_path="/backups/$snap_name"

/usr/bin/time -f 'Databases were locked for %E' -- \
mysql <<- MYSQL
# based on http://pointyhair.com/tiki-view_blog_post.php?blogId=1&postId=5
FLUSH TABLES WITH READ LOCK;
\! lvcreate --size $snap_size --snapshot --name $snap_name $lvol
UNLOCK TABLES;
MYSQL
mount $snap_vol $snap_path
rsync -av --delete $snap_path/mysql $backup_path/
umount $snap_path
lvremove -f $snap_vol

Isso permitirá que você faça backups noturnos sem precisar adicionar um servidor escravo. Eu sou muito a favor de ter um servidor escravo para alta disponibilidade, mas eu não quero que você pense que você está preso até que você possa criar esse escravo.

    

Answer 3

TABELAS DE DESCARGA COM BLOQUEIO DE LEITURA não é algo que você queira fazer regularmente (ou mesmo semi- regular) em um sistema de produção. Deve ser apenas um último recurso.

Configure pelo menos dois escravos de replicação (isso exigirá um FLUSH TABLES WITH READ LOCK, é claro). Depois de configurados, você pode fazer um backup de um enquanto o outro permanece em sincronia como um mestre de reposição.

Além disso, se um de seus escravos falhar, você poderá usar um instantâneo para reconstruir um segundo (ou terceiro) escravo. Se todos os seus escravos falharem, você estará de volta em TABELAS DE NIVELAMENTO COM LER BLOQUEIO.

Lembre-se de sempre ter um processo que verifica regularmente se os dados estão sincronizados - use algo como mk-table-checksum para fazer isso (isso não é trivial para configurar, consulte a documentação do Maatkit para obter detalhes).

Como 22 GB é relativamente pequeno, você não terá problemas em fazer isso. Fazer isso com um grande banco de dados pode ser mais problemático.

    

Answer 4

A solução aqui é dupla, conforme descrito acima:

1. Configure a replicação do seu servidor para um escravo que você pode colocar offline. A melhor maneira de fazer isso é fazer um dump do banco de dados usando mysqldump e o parâmetro --master-data.
2. Configure backups noturnos no escravo. Você pode tanto usar o mysqldump com o --master-data --flush-logs e --single-transaction flags, ou você pode parar o servidor mysql, copiar os arquivos de dados no disco, e reiniciá-lo (a replicação irá pegar onde parou.)
3. Execute um script no escravo a cada (por exemplo, 5, 10, 20 minutos) para verificar e certificar-se de que o mysql ainda está sendo replicado. Eu escrevi um script python simples para fazer isso, que você é bem-vindo a usar.

Note que se você estiver usando InnoDB para suas tabelas, você pode usar o sinalizador --single-transaction para evitar ter que fazer qualquer travamento de tabela e ainda obter um despejo consistente do banco de dados, mesmo no master, e assim fazer seus backups sem derrubar o servidor. A solução acima, no entanto, é melhor.

Além disso, se você estiver usando o LVM no Linux, poderá fazer um instantâneo LVM da partição e, em seguida, fazer o backup. Os instantâneos do LVM são atômicos, portanto, se você 'limpar as tabelas com o bloqueio de leitura' e, em seguida, tirar seu instantâneo e desbloquear, obterá um instantâneo consistente.

Se você está preocupado com a contenção de I / O que faz o dump demorar muito, você pode adicionar uma terceira máquina e executar o mysqldump na rede para evitar que seus discos sejam debandados.

    

Answer 5

Você pode fazer um backup gradual. Backup 1/24 dos registros a cada hora. O único problema com essa abordagem é que, se ela falhar durante as primeiras horas do dia, você perderá qualquer coisa a partir desse momento até a hora da falha. De qualquer forma, são menos de 24 horas de registros perdidos (não sei o quanto isso é importante para você).

    

Answer 6

Dependendo do seu ambiente, os instantâneos geralmente são um excelente caminho a percorrer. Particularmente, se você precisar fazer backup do mestre por algum motivo. Nós executamos pares master e slave e usamos backups de snapshots em ambos.

1. FLUSH TABLES WITH READ LOCK;
2. Puxar um instantâneo no banco de dados e nos sistemas de arquivos de log.
3. UNLOCK TABLES;
4. Copie seus arquivos de dados para fora do instantâneo à sua vontade.

Com tabelas InnoDB, você vai querer executar SET AUTOCOMMIT=0; antes de executar o bloqueio de leitura.

    

Answer 7

Dê uma olhada em " Prática recomendada para fazer backup de um banco de dados MySQL de produção? ". É uma postagem semelhante no Stack Overflow.