how to dump rows from mysqldump to reimport into different table

Question

I need to dump data from one table in my MySQL 8 database and reimport into a different table which already exists and has it's schema. The schema is identical between the two such as columns, how can I achieve this, i just need the row imsert statements from mysqldump without locking and create info

My export:

mysqldump -u root -p --no-create-info icicle_db users > users_existing.sql

I've tried to import

mysqldump -u root -p --no-create-info icicle_db users_new < users_existing.sql

But users_existing.sql contains references to the old table, i just need insert data not other schema. I'm trying it on a small table before trying a table with millions of rows. What am i missing?

Answer 1

But users_existing.sql contains references to the old table, i just need insert data not other schema. I'm trying it on a small table before trying a table with millions of rows. What am I missing?

You need to replace the table name in users_existing.sql file.

This can be done using sed command.

sudo sed -i 's/INSERT INTO `users`/INSERT INTO `users_new`/g' users_existing.sql

Then proceed with the restore

mysql -u root -p icicle_db < users_existing.sql