Copy from one MySQL table to another MySQL table of same database

Question

I have around 40 million rows in a MySQL table and I want to copy this table to another table in same database. What is the most efficient way of doing this? How much time will it take (approx.)?

Answer 1

Suppose you have mydb.mytb and you want to create mydb.mytbcopy

I have five(5) approaches to doing this copy

APPROACH #1

In the mysql client, run the following

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

APPROACH #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"

APPROACH #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"

APPROACH #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

APPROACH #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}

ANALYSIS

• APPROACH #1 is the easiest in terms of steps, but requires pushing 40 million rows into one transaction. This will be the most taxing on the InnoDB Storage Engine.
• For the other approaches, mysqldump will send 40 million row in chucks of thousands of rows
  • APPROACH #2 and APPROACH #3 will mysqldump the table into the test database. After creating the table in the test database, it is subsequently renamed and moved into the original database
  • APPROACH #4 and APPROACH #5 rename the table using sed against the stream coming from the mysqldump as it echoes the INSERT commands
  • APPROACH #2 and APPROACH #4 use pipes instead of an output file
  • APPROACH #3 and APPROACH #5 use an outpuit file for subsequent reload

If you want to copy mydb.mytb to an already existing table mydb.mytbcopy, and the two tables have identical structures:

APPROACH #6

INSERT INTO mytbcopy SELECT * FROM mytb;

Like #APPROACH 1, #APPROACH 6 would have a single transaction of 40 million rows

APPROACH #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

This approaches does not drop the table. It simply generates the INSERTs

EPILOGUE

I cannot give you a time estimate since I do not know the make up of the DB Server, table structure, index layout, and things like these.

GIVE IT A TRY !!!

Answer 2

InnoDB tables, unlike MyISAM*, cannot be "just copied away", as part of its data dictionary (and potentially other structures the table is depending on, like the merge buffer) are located in memory (if the server is running) and in the common/main tablespace, a.k.a. that large file called ibdata1.

If you are using Percona Server >=5.1 or MySQL >= 5.6, there is support for transportable tablespaces, which allows you to export and import tables directly from the filesystem. Here it is the method for MySQL and for Percona. In both cases, it is required that you had created the table with the innodb_file_per_table option and involves the usage of DISCARD TABLESPACE/IMPORT TABLESPACE and/or Percona Xtrabakup (if you want the export to be done online). Please note that Percona Server or Xtrabakup are not available for Windows.

This method will be, speaking in general, as fast as copying the file using the filesystem commands (cp, rsync).

While there may be some cases that this could work in MySQL < 5.6 (in a hacky way) for restores, it will not work for a table copy. In those cases, one way to do it is by using SQL:

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

This will be as fast as the InnoDB can execute Handler_read_rnd_next and Handler_write, once per row. If you use this method, make sure that you disable, at least temporarily, the durability options and you have a large buffer pool and transaction log. Under those circumstances, it may reduce the importing time, but it definitely won't fit into memory fully, so expect a lot of time. Also, you are trying to import 40M rows in a single transaction, that may lead into problems.

My actual recommendation, in this second case, would be to use something like pt-archiver, as it will perform an operation similar to the one I just mentioned, but it will be done in "chunks", avoiding the transactional overhead (it may not be faster, but in the case of a failure, it won't try to rollback the whole table, taking forever). For the data sizes that you mention, this is probably the best way to go.

A final option would be to export and import using the CSV (or TSV) format, with a combination of SELECT INTO OUTFILE/mysqldump and LOAD DATA/mysqlimport. This was a very common option if you needed concurrency in certain old versions of mysql, as using sql created larger locks (not true anymore if done correctly). As mysqldump/import only works in a serialized way, I would recommend you to research options to parallelize it, very useful for large tables.

In any case, try to avoid multiple SQL sentences, as it will be your most important bottleneck if you execute many different queries (that have to be executed, parsed and optimized individually).

---

*MyISAM structures cannot be copied in a hot way, but it is very easy to sync them temporarily to disk with FTWRL.

Answer 3

to Move data from one table to another in schema

create table your_table_name select * from old_schema_table;