Friday, May 14, 2010

[Level 3] Schema management tips for MySQL

{Database management tips}

[in source save]
4 files in source save.
1. schema:
=> by release version.
=> only create table/procedure, no alter, no insert, no drop ...etc.
=> use mysqldump -d.
2. schema change log:
=> by date.
=> split into two parts. one is schema(except foreign key), and put it on the top. Another is foreign key, and put it on the tail.
=> must add drop tabele before create, and must be sequencial by date.
=> modify manually.
3. data:
=> by release version.
=> only insert, no update, no delete...etc.
=> use mysqldump.
4. data change log:
=> by date.
=> could use insert/update/delete, but delete(truncate) must before update than insert, and must be sequencial by date.
=> modify manually.

[use case]
how to create patch:
a: use schema comparision.
=> takes time, but more efficacy.
b: use changelog comparision.
=> esay way, but suffer performance.
so, use a to build patch, but use b to verify result.
but how to verify?
use a, dump a schema and data,
use b, dump b schema and data,
compare two results.

[useful skill]
mysql> set foreign_key_checks=0;
mysql> set unique_checks=0;

Wish this helps.
regards,
Stanley Huang