Cannot get select into to work

Question

I have a database squid with a table configs.

I am trying to get the configs table into a different database [testsquid] on the same host.

Here is what I've tried:

select * into testsquid.configs from squid.configs;

I tried first without creating the configs table in testsquid, and then I actually created the configs table in testsquid.

I am trying to get the configs table from squid into the new database testsquid. I decided not to use mysql dump because it locks the table.

What am I doing incorrectly?

Answer 1

If the table schemas are equal you can try:

insert into testsquid.configs
    select * from squid.configs;

If the new table does not exist yet create it with:

create table testsquid.configs like squid.configs;

Update:

I am not sure if insert-select also locks the table. To keep the locking time short you could create a similar temporary table with memory engine without any indexes. Copy the data into the temp table and after that from the temp table into the physical one.