Build MySQL; test MySQL; then reload with real data 1

[GKChesterton]

My database serves 5-12 concurrent users and has tables with up to 50k records. Referential integrity is strongly enforced and the UI has a lot of VBA.

No corruptions, but it's slowing down. So I will move from MS Access with split back/front to MySQL with the existing Access front-end. I'm completely new to MySQL.

The 'Data Migration Wizard' just squirted all the data over to MySQL. It all looks fine. I assume I'll be tweaking some data types, but of more concern is the ref. integrity. Building in the 'foreign keys' will take an hour at least.

For this initial dev and testing, I used a back-up of the data. Of course it's getting stale by the day. In a week or so I'll want to replace it with live data.

What will be the best way to perform that transition? I'd rather not repeat all the steps of configuring and optimizing. The ideal way I can imagine is to make a copy of the MySQL database in structure only. Then append in the live data.

Can that be done? or are there other suggestions?

[purple]If we knew what it was we were doing, it would not be called
research [blue]database[white].[/white]development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[r937]

yes, that can be done

make sure you use InnoDB tables, those are the only ones that enforce foreign key checking

r937.com | rudy.ca

 

[GKChesterton]

Thanks, r937! I was aware about InnoDB and that's what I'm using.

Back to my question ... I presume the method for making a shell of the DB is to either
A. use a technique that copies the shell only, or
B. make a copy; start emptying tables and then refill them.

I'd rather do A. Do you know a method?

[purple]If we knew what it was we were doing, it would not be called
research [blue]database[white].[/white]development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[r937]

all of the mysql front-end apps that i've used (sqlyog, phpmyadmin, and my favourite, heidisql) have this facility

i think it is also part of the mysqldump command

this facility simply generates CREATE TABLE statement, to copy the structure, and/or INSERT statements, to copy the data



r937.com | rudy.ca

 

[GKChesterton]

Thank you. I think mysqldump --no-data will work. And when I've learned how to use it, I will know more about MySQL than I did. And thanks for recommendations on admin applications.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database[white].[/white]development[/blue], would it? [tab]-- Albert Einstein[/purple]​