Mysql – Copy Table To Different Host/database

[paulking]

All

I have a table which is around 40gb and I need to copy it to a new database on a new server.

I'm unsure on how the 'Copy Table to Different Host/Database' works i.e.

1) If I try to copy this table as a test will it lock the table until it's finished copying?

2) If it doesn't lock it and I try it I have a feeling its going to say 'mySQL has gone away' or an error relating to the 'max_allowed_packet' – so my question is: when it says packet, does this mean the max size of a row it is trying to insert? Or a table? Or does it mean something else?

Any advice is much appreciated as I'm not experienced when it comes to copying a database and mySQL database settings.

Thanks in advance!

Paul

[Khushboo]

Hi Paul,

Quote:

1) If I try to copy this table as a test will it lock the table until it's finished copying?

No, it will not lock the table, as copy table will not execute any LOCK statement on the server.

Quote:

when it says packet, does this mean the max size of a row it is trying to insert? Or a table? Or does it mean something else?

Yes, here it means the max size of row for single or bulk insert as you can not execute an INSERT statement longer than 'max_allowed_packet'.

You have to make sure 'max_allowed_packet' value of your target server(where you are trying to copy) should be greater than or equal to the same for the source server.

To find the current 'max_allowed_packet' setting in server configuration execute this on source and target server:

Show variables like 'max_allowed_packet';

Thanks & Regards,

Khushboo

[paulking]

Thanks Khushboo, thats helped alot!

Much Appreciated!

[Author]

Posts