Will mysql import overwrite my database

Question

So if I take a mysqldump of my production database called production_database, but I've made changes to by test environment database already, called work_database, adding function/stored_procedures/tables/collumns. What will happend to that when I run my mysql import? Will I lose all of my changes I made on my work_database or will it only transfer the new data and not remove anything?

The reason why I need the production_database copy, is because it's updated 27/7 with the latest data, and I'm making a test environment, that each night is updated with the latest results.

These are my mysqldump & mysql commands:

bin/mysqldump.exe -uUsername -p-hHost.com --lock-tables=false --single-transaction --routines --triggers production_database >backups/backups01/testdump.sql

bin/mysql.exe -uUsername -p -hHost.com work_database <backups/backups01/testdump.sql

EDIT - Testet it

So I tried to add a extra column to one of my tables and it was removed when I did my mysql.exe import. But when just create somthing totally new like a table/function/stored_procedure (NO edits), then it's still there since the import doesn't overwrites that. How can I avoid mysql to overwrite adding new collumn/edits to a function/stored_procedure, and just make it use my default column settings and/or just leave that new data/functions/stored_procedures alone if thats even possible?

It's just not smart to have this automatic update of data, if the programmers database changes gets overwritten.

Maybe I could implement something on my mysql.exe command that tells it only to do the full import if the structure/functions/stored_procedures/columns is the same (new data is okay)?

Answer 1

It is not practical to merge two databases that have both been added to. Decide on how to move data/procs/whatever from one into the other.

Adding a column can destroy code that does something simple like SELECT *. Beware.

1. Get rid of any dangerous SELECT * in the code; push that code.

2. ALTER TABLE .. ADD COLUMN ..

3. Push code to use that column.

That is just one example of how complex it can be to modify the schema.

Your question is too broad. Focus on a single update. Then we can go into details of how to sync that update from one server to the other. Then move onto another update.

Another example: Copying stored procs to the other machine:

1. Use args on mysqldump to dump only the stored procs.

2. Load them on the other machine

3. Push app code to use those procs.

In a rather general case of schema updates, this is needed:

1. Change the code to discover what the schema is, and work with either the old schema or new.

2. Push the schema change to the database

3. Clean up the code to remove the 'old' code.

Note how there are 3 steps in each of my examples. You need to think about the task taking more than just one step.