INSIGHT EM FAZER BACKUPS COM mysqldump
IMHO Fazer backups tornou-se mais uma forma de arte se você souber como abordá-lo
Você tem opções
Opção 1: mysqldump uma instância inteira do mysql
Este é o mais fácil, o básico!
mysqldump -h... -u... -p... --routines --triggers --all-databases | gzip > MySQLData.sql.gz
Tudo escrito em um arquivo: estruturas de tabela, índices, acionadores, procedimentos armazenados, usuários, senhas criptografadas. Outras opções mysqldump também podem exportar diferentes estilos de comandos INSERT, arquivo de log e coordenadas de posição a partir de logs binários, opções de criação de banco de dados, dados parciais (--where option) e assim por diante.
Opção 2: mysqldump separar bancos de dados em arquivos de dados separados
Comece criando uma lista de bancos de dados (2 técnicas para fazer isso)
Técnica 1 -
mysql -h... -u... -p... -A --skip-column-names -e"SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema','mysql')" > ListOfDatabases.txt
Técnica 2 -
mysql -h... -u... -p... -A --skip-column-names -e"SELECT DISTINCT table_schema FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql')" > ListOfDatabases.txt
A técnica 1 é o caminho mais rápido. A técnica 2 é a mais segura e segura. A técnica 2 é melhor porque, às vezes, os usuários criam pastas para propósitos gerais em / var / lib / mysql (datadir) que não são relacionados ao banco de dados. O information_schema registraria a pasta como um banco de dados na tabela information_schema.schemata. A técnica 2 iria ignorar pastas que não contenham dados mysql.
Depois de compilar a lista de bancos de dados, você pode continuar a percorrer a lista e mysqldump, mesmo em paralelo, se assim desejar.
for DB in 'cat ListOfDatabases.txt'
do
mysqldump -h... -u... -p... --routines --triggers ${DB} | gzip > ${DB}.sql.gz &
done
wait
Se houver muitos bancos de dados para serem executados ao mesmo tempo, faça um paralelismo com eles 10 por vez:
COMMIT_COUNT=0
COMMIT_LIMIT=10
for DB in 'cat ListOfDatabases.txt'
do
mysqldump -h... -u... -p... --routines --triggers ${DB} | gzip > ${DB}.sql.gz &
(( COMMIT_COUNT++ ))
if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
then
COMMIT_COUNT=0
wait
fi
done
if [ ${COMMIT_COUNT} -gt 0 ]
then
wait
fi
Opção 3: mysqldump tabelas separadas em arquivos de dados separados
Comece criando uma lista de tabelas
mysql -h... -u... -p... -A --skip-column-names -e"SELECT CONCAT(table_schema,'.',table_name) FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql')" > ListOfTables.txt
Em seguida, despeje todas as tabelas em grupos de 10
COMMIT_COUNT=0
COMMIT_LIMIT=10
for DBTB in 'cat ListOfTables.txt'
do
DB='echo ${DBTB} | sed 's/\./ /g' | awk '{print $1}''
TB='echo ${DBTB} | sed 's/\./ /g' | awk '{print $2}''
mysqldump -h... -u... -p... --triggers ${DB} ${TB} | gzip > ${DB}_${TB}.sql.gz &
(( COMMIT_COUNT++ ))
if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
then
COMMIT_COUNT=0
wait
fi
done
if [ ${COMMIT_COUNT} -gt 0 ]
then
wait
fi
Opção 4: USE SUA IMAGINAÇÃO
Experimente variações das opções acima mencionadas, além de técnicas para fotos nítidas
Exemplos
- Solicite a lista de tabelas pelo tamanho de cada tabela crescente ou decrescente.
- Usando um processo separado, execute "FLUSH TABLES WITH READ LOCK; SELECT SLEEP (86400)" antes de iniciar o mysqldumps. Mate este processo depois que os mysqldumps estiverem completos.
- Salve os mysqldumps em pastas antigas e gire as pastas de backup antigas.
- Carregue mysqldumps de instância inteira em servidores independentes.
CAVEAT
Apenas a Opção 1 traz tudo. A desvantagem é que os mysqldumps criados dessa maneira só podem ser recarregados na mesma versão principal do mysql que o mysqldump foi gerado. Em outras palavras, um mysqldump de um banco de dados MySQL 5.0 não pode ser carregado em 5.1 ou 5.5. O motivo ? O esquema mysql é totalmente diferente entre os principais lançamentos.
As opções 2 e 3 não incluem salvar nomes de usuário e senhas.
Aqui está a maneira genérica de descarregar os SQL Grants para usuários que sejam legíveis e mais portáteis
mysql -h... -u... -p... --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -h... -u... -p... --skip-column-names -A | sed 's/$/;/g' > MySQLGrants.sql
A opção 3 não salva os procedimentos armazenados, portanto, você pode fazer o seguinte
mysqldump -h... -u... -p... --no-data --no-create-info --routines > MySQLStoredProcedures.sql &
Outro ponto que deve ser observado é sobre o InnoDB. Se você tem um grande buffer pool do InnoDB, faz sentido limpá-lo da melhor forma possível antes de executar qualquer backup. Caso contrário, o MySQL gasta o tempo de liberação de tabelas com a página suja restante do buffer pool. Aqui está o que eu sugiro:
Mais de 1 hora antes de executar o backup, execute este comando SQL
SET GLOBAL innodb_max_dirty_pages_pct = 0;
No padrão do MySQL 5.5, innodb_max_dirty_pages_pct é 75. No MySQL 5.1 e verso, innodb_max_dirty_pages_pct padrão é 90. Ao configurar innodb_max_dirty_pages_pct como 0, isso acelerará a liberação de páginas sujas no disco. Isso evitará ou pelo menos diminuirá o impacto de limpar quaisquer commits incompletos de duas fases de dados do InnoDB antes de executar qualquer mysqldump contra qualquer tabela InnoDB.
PALAVRA FINAL NO mysqldump
A maioria das pessoas foge do mysqldump em favor de outras ferramentas e essas ferramentas são realmente boas.
Essas ferramentas incluem
- MAATKIT ( despejo / restaurar scripts, de Percona [Deprecado mas ótimo])
-
XtraBackup (backup de snapshot do TopNotch da Percona)
-
CDP R1Soft (MySQL Module Option que captura instantâneos de um ponto no tempo)
-
MySQL Enterprise Backup (anteriormente Backups InnoDB Hot [comerciais])
Se você tem o espírito de um verdadeiro DBA MySQL, você pode abraçar o mysqldump e ter o domínio completo sobre ele que pode ser alcançado. Que todos os seus backups sejam um reflexo de suas habilidades como um DBA MySQL .