Suponha que você tenha mydb.mytbe queira criarmydb.mytbcopy

Eu tenho cinco (5) abordagens para fazer esta cópia

ABORDAGEM #1

No mysqlcliente, execute o seguinte

USE mydb
CREATE TABLE mytbcopy LIKE mytb;
INSERT INTO mytbcopy SELECT * FROM mytb;

ABORDAGEM #2

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysql ${MYSQL_CONN} -ANe"CREATE DATABASE IF NOT EXISTS test"
mysqldump ${MYSQL_CONN} mydb mytb | mysql ${MYSQL_CONN} -Dtest
mysql ${MYSQL_CONN} -ANe"ALTER TABLE test.mytb RENAME mydb.mytbcopy"

ABORDAGEM #3

DUMPFILE=/some/path/tabledata.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysql ${MYSQL_CONN} -ANe"CREATE DATABASE IF NOT EXISTS test"
mysqldump ${MYSQL_CONN} mydb mytb > ${DUMPFILE}
mysql ${MYSQL_CONN} -Dtest < ${DUMPFILE}
rm -f ${DUMPFILE}
mysql ${MYSQL_CONN} -ANe"ALTER TABLE test.mytb RENAME mydb.mytbcopy"

ABORDAGEM #4

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysqldump ${MYSQL_CONN} mydb mytb | sed 's/mytb/mytbcopy' | mysql ${MYSQL_CONN} -Dmydb

ABORDAGEM Nº 5

DUMPFILE=/some/path/tabledata.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysqldump ${MYSQL_CONN} mydb mytb | sed 's/mytb/mytbcopy' > ${DUMPFILE}
mysql ${MYSQL_CONN} -Dmydb < ${DUMPFILE}
rm -f ${DUMPFILE}

ANÁLISE

• A ABORDAGEM #1 é a mais fácil em termos de etapas, mas requer o envio de 40 milhões de linhas em uma transação. Este será o mais exigente no InnoDB Storage Engine.
• Para as outras abordagens, o mysqldump enviará 40 milhões de linhas em blocos de milhares de linhas
  • APPROACH #2 e APPROACH #3 farão o mysqldump da tabela no banco de dados de teste. Depois de criar a tabela no banco de dados de teste, ela é posteriormente renomeada e movida para o banco de dados original
  • APPROACH #4 e APPROACH #5 renomeiam a tabela usando sed contra o stream vindo do mysqldump enquanto ele ecoa os comandos INSERT
  • APPROACH #2 e APPROACH #4 usam pipes em vez de um arquivo de saída
  • APPROACH #3 e APPROACH #5 usam um arquivo de saída para recarga subsequente

Se você deseja copiar mydb.mytbpara uma tabela já existente mydb.mytbcopye as duas tabelas possuem estruturas idênticas:

ABORDAGEM #6

INSERT INTO mytbcopy SELECT * FROM mytb;

Assim como #APPROACH 1 , #APPROACH 6 teria uma única transação de 40 milhões de linhas

ABORDAGEM Nº 7

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysqldump ${MYSQL_CONN} -t mydb mytb | sed 's/mytb/mytbcopy' | mysql ${MYSQL_CONN} -Dmydb

Essa abordagem não descarta a tabela. Ele simplesmente gera os INSERTs

EPÍLOGO

Não posso fornecer uma estimativa de tempo, pois não conheço a composição do servidor de banco de dados, estrutura de tabela, layout de índice e coisas assim.

DE UMA CHANCE !!!

As tabelas InnoDB, ao contrário do MyISAM*, não podem ser "apenas copiadas", pois parte de seu dicionário de dados (e potencialmente outras estruturas das quais a tabela depende, como o buffer de mesclagem) estão localizadas na memória (se o servidor estiver em execução) e em o tablespace comum/principal, também conhecido como aquele arquivo grande chamado ibdata1.

Se você estiver usando Percona Server >=5.1 ou MySQL >= 5.6, há suporte para tablespaces transportáveis, que permitem exportar e importar tabelas diretamente do sistema de arquivos. Aqui está o método para MySQL e para Percona . Em ambos os casos, é necessário que você tenha criado a tabela com a innodb_file_per_tableopção e envolva o uso de DISCARD TABLESPACE/IMPORT TABLESPACEe/ou Percona Xtrabakup (caso queira que a exportação seja feita online). Observe que o Percona Server ou o Xtrabakup não estão disponíveis para Windows.

Este método será, falando em geral, tão rápido quanto copiar o arquivo usando os comandos do sistema de arquivos (cp, rsync).

Embora possa haver alguns casos em que isso possa funcionar no MySQL < 5.6 (de maneira hacky) para restaurações, não funcionará para uma cópia de tabela. Nesses casos, uma maneira de fazer isso é usando SQL :

CREATE TABLE new_table LIKE old_table;
INSERT INTO new_table SELECT * FROM old_table;

Isso será tão rápido quanto o InnoDB pode executar Handler_read_rnd_nexte Handler_write, uma vez por linha. Se você usar esse método, certifique-se de desativar, pelo menos temporariamente, as opções de durabilidade e de ter um grande conjunto de buffers e log de transações. Nessas circunstâncias, pode reduzir o tempo de importação, mas definitivamente não caberá totalmente na memória, portanto, espere muito tempo. Além disso, você está tentando importar 40 milhões de linhas em uma única transação, o que pode causar problemas.

Minha recomendação real, neste segundo caso, seria usar algo como pt-archiver , pois ele realizará uma operação semelhante à que acabei de mencionar, mas será feita em "pedaços", evitando a sobrecarga transacional (pode não será mais rápido, mas em caso de falha, não tentará reverter toda a tabela, demorando uma eternidade). Para os tamanhos de dados que você mencionou, esse é provavelmente o melhor caminho a seguir.

Uma opção final seria exportar e importar usando o formato CSV (ou TSV) , com uma combinação de SELECT INTO OUTFILE/mysqldump e LOAD DATA/mysqlimport. Esta era uma opção muito comum se você precisasse de simultaneidade em certas versões antigas do mysql, pois o uso do sql criava bloqueios maiores (não é mais verdade se feito corretamente). Como o mysqldump/import só funciona de forma serializada, recomendo pesquisar opções para paralelizá-lo, muito útil para tabelas grandes.

De qualquer forma, tente evitar várias frases SQL, pois será seu gargalo mais importante se você executar muitas consultas diferentes (que devem ser executadas, analisadas e otimizadas individualmente).

*As estruturas do MyISAM não podem ser copiadas de maneira hot, mas é muito fácil sincronizá-las temporariamente no disco com arquivos FTWRL.

mover dados de uma tabela para outra no esquema

create table your_table_name select * from old_schema_table;