mysql schema maintenance
DESCRIPTION
Schema maintenance is a hot topic for database administrators. Especially if they have to maintain schemas for development, staging, and production, the task could be difficult and confusing. Using MySQL Workbench, schema maintenance is greatly simplified, and the tasks of synchronizing schemas across servers become easy and manageable.TRANSCRIPT
MySQL schema maintenance
Giuseppe MaxiaMySQL Community Team Lead
This work is licensed under the Creative Commons Attribution-Share Alike 3.0 Unported License.
Thursday, 03 December 2009
about me - Giuseppe Maxia a.k.a. The Data Charmer MySQL Community Team Lead Long time MySQL hacker Formerly, database consultant, designer, coder. A passion for QA An even greater passion for open source ... and community Passionate blogger http://datacharmer.blogspot.com
Thursday, 03 December 2009
Schema what?
In a relational database, the schema defines the tables, the fields in each table, and the relationships between fields and tables.
http://en.wikipedia.org/wiki/Schema_(database)
Thursday, 03 December 2009
tables
Thursday, 03 December 2009
tables
table nametable nametable name
column column column
Thursday, 03 December 2009
tables
table nametable nametable name
column column column
CREATE TABLE employees ( ID INT NOT NULL, name VARCHAR(20), salary INT, PRIMARY KEY (ID))
Thursday, 03 December 2009
relationship
employeesemployees
ID INT
name varchar (10)
salary INT
dept_ID INT
departmentsdepartments
ID INT
name varchar (10)
Thursday, 03 December 2009
relationship
employeesemployees
ID INT
name varchar (10)
salary INT
dept_ID INT
departmentsdepartments
ID INT
name varchar (10)
1
Thursday, 03 December 2009
relationship
employeesemployees
ID INT
name varchar (10)
salary INT
dept_ID INT
departmentsdepartments
ID INT
name varchar (10)
1many
Thursday, 03 December 2009
relationship
employeesemployees
ID INT
name varchar (10)
salary INT
dept_ID INT
salessales
cust_ID INT
emp_ID INT
s_date DATE
amount DECIMAL
customercustomer
ID INT
name varchar (10)
Thursday, 03 December 2009
relationship
employeesemployees
ID INT
name varchar (10)
salary INT
dept_ID INT
salessales
cust_ID INT
emp_ID INT
s_date DATE
amount DECIMAL
customercustomer
ID INT
name varchar (10)
11
Thursday, 03 December 2009
relationship
employeesemployees
ID INT
name varchar (10)
salary INT
dept_ID INT
salessales
cust_ID INT
emp_ID INT
s_date DATE
amount DECIMAL
customercustomer
ID INT
name varchar (10)
manymany
11
Thursday, 03 December 2009
relationship
employeesemployees
ID INT
name varchar (10)
salary INT
dept_ID INT
salessales
cust_ID INT
emp_ID INT
s_date DATE
amount DECIMAL
customercustomer
ID INT
name varchar (10)
manymany
11
many to many
Thursday, 03 December 2009
Main actors
• Production
• Development
• Stage
• Test
Thursday, 03 December 2009
Production database
• You must have one
Thursday, 03 December 2009
Development database
• Where you create new thingsThursday, 03 December 2009
Test database
• Where you experiment crazy ideas
Thursday, 03 December 2009
Stage database
• Where you test before deploying to production
Thursday, 03 December 2009
Schema maintenance actions
• Reverse engineering
• Forward engineering
• Synchronization
Thursday, 03 December 2009
Reverse Engineering
Thursday, 03 December 2009
Reverse Engineering
DDLdept_empdept_empdept_emp
departmentdepartmentdepartment
dept_managerdept_managerdept_manager
employeesemployeesemployees
titlestitlestitles
salariessalariessalaries
Thursday, 03 December 2009
Reverse Engineering
DDLdept_empdept_empdept_emp
departmentdepartmentdepartment
dept_managerdept_managerdept_manager
employeesemployeesemployees
titlestitlestitles
salariessalariessalaries
Thursday, 03 December 2009
Reverse Engineering
DDLdept_empdept_empdept_emp
departmentdepartmentdepartment
dept_managerdept_managerdept_manager
employeesemployeesemployees
titlestitlestitles
salariessalariessalaries
Thursday, 03 December 2009
Forward Engineering
Thursday, 03 December 2009
Forward Engineering
Thursday, 03 December 2009
Forward Engineering
Thursday, 03 December 2009
Forward Engineering
DDLdept_empdept_empdept_emp
departmentdepartmentdepartment
dept_managerdept_managerdept_manager
employeesemployeesemployees
titlestitlestitles
salariessalariessalaries
Thursday, 03 December 2009
Forward Engineering
DDLdept_empdept_empdept_emp
departmentdepartmentdepartment
dept_managerdept_managerdept_manager
employeesemployeesemployees
titlestitlestitles
salariessalariessalaries
WARNING!
Forward engineerin
g is destr
uctive.
Thursday, 03 December 2009
Synchronization
Thursday, 03 December 2009
Synchronization
Thursday, 03 December 2009
Synchronization
Thursday, 03 December 2009
Synchronization
Thursday, 03 December 2009
Synchronization
Thursday, 03 December 2009
Synchronization
Thursday, 03 December 2009
Synchronization
Thursday, 03 December 2009
Synchronization
Thursday, 03 December 2009
Synchronization
Thursday, 03 December 2009
Synchronization
Thursday, 03 December 2009
Synchronization
Thursday, 03 December 2009
Synchronization
Thursday, 03 December 2009
Synchronization
Thursday, 03 December 2009
How it works
Production
StageTest
Development
OK?
no
yes
OK?yes
no
Thursday, 03 December 2009
How else it works
Production
StageTest
Development
• Partition adjustment
• Dynamic schemas
• Hot fixes
Thursday, 03 December 2009
What happens in between
Test
Development
OK?
no
Thursday, 03 December 2009
What happens in between
Test
Development
OK?
no What now?
Thursday, 03 December 2009
What happens in between
Test
Development
OK?
no What now? Wrong schema here
Thursday, 03 December 2009
What happens in between
Test
Development
OK?
no What now? Wrong schema here
REWIND?
Thursday, 03 December 2009
Schema maintenance: how to
1. Poor man's wayThursday, 03 December 2009
How poor is the poor man?• only command line access• No Perl, PHP, or Python• Can't install anything
• Command line access• Perl or Python available• Can install modules
• Command line access• GUI• Can install everything
Thursday, 03 December 2009
How poor is the poor man?• only command line access• No Perl, PHP, or Python• Can't install anything
• Command line access• Perl or Python available• Can install modules
• Command line access• GUI• Can install everything
EXTREMELY
POOR
Thursday, 03 December 2009
How poor is the poor man?• only command line access• No Perl, PHP, or Python• Can't install anything
• Command line access• Perl or Python available• Can install modules
• Command line access• GUI• Can install everything
POOR
EXTREMELY
POOR
Thursday, 03 December 2009
How poor is the poor man?• only command line access• No Perl, PHP, or Python• Can't install anything
• Command line access• Perl or Python available• Can install modules
• Command line access• GUI• Can install everything
POOR
EXTREMELY
POOR
RICH
Thursday, 03 December 2009
HOST 1
so, the extremely poor man's way ...
HOST 2
ProductionDevelopment
Thursday, 03 December 2009
HOST 1
so, the extremely poor man's way ...
get the schema
TEXT FILE
HOST 2
ProductionDevelopment
Thursday, 03 December 2009
HOST 1
so, the extremely poor man's way ...
get the schema
TEXT FILE
HOST 2
get the schema
TEXT FILE
ProductionDevelopment
Thursday, 03 December 2009
HOST 1
so, the extremely poor man's way ...
get the schema
TEXT FILE
HOST 2
get the schema
TEXT FILE
diff
ProductionDevelopment
Thursday, 03 December 2009
HOST 1
so, the extremely poor man's way ...
get the schema
TEXT FILE
HOST 2
get the schema
TEXT FILE
diff
ProductionDevelopment
then … uh … figure out how to continue
Thursday, 03 December 2009
HOST 1
the poor man's way ...HOST 2
ProductionDevelopment
Perl Script
get the schema
TEXT FILE
get the schema
TEXT FILE
diff
Thursday, 03 December 2009
HOST 1
the poor man's way ...HOST 2
ProductionDevelopment
Perl Script
get the schema
TEXT FILE
get the schema
TEXT FILE
diff
TELLS YOU WHAT TO DO
Thursday, 03 December 2009
HOST 1
not so poor man's way ...HOST 2
ProductionDevelopment
Python Script
DOES A LOT OF THINGS
http://schemasync.org
TELLS YOU WHAT TO DO
Thursday, 03 December 2009
HOST 1
not so poor man's way ...HOST 2
ProductionDevelopment
Python Script
DOES A LOT OF THINGS
http://schemasync.org
TELLS YOU WHAT TO DO
WARNING: doesn't deal with partitionsThursday, 03 December 2009
The rich man
http://aquafold.com
Thursday, 03 December 2009
The rich man
http://aquafold.com
WARNING: doesn't deal with partitionsThursday, 03 December 2009
MySQL Workbench
http://wb.mysql.com
Thursday, 03 December 2009
MySQL Workbench
http://wb.mysql.com
Thursday, 03 December 2009
MySQL Workbench
http://wb.mysql.com
Thursday, 03 December 2009
Schema and model
Thursday, 03 December 2009
Schema and model
DDLdept_empdept_empdept_emp
departmentdepartmentdepartment
dept_managerdept_managerdept_manager
employeesemployeesemployees
titlestitlestitles
salariessalariessalaries
Thursday, 03 December 2009
Schema and model
DDLdept_empdept_empdept_emp
departmentdepartmentdepartment
dept_managerdept_managerdept_manager
employeesemployeesemployees
titlestitlestitles
salariessalariessalaries
Thursday, 03 December 2009
Physical schema
implemented in a specific DBMS
Thursday, 03 December 2009
Physical schema
DDLdept_empdept_empdept_emp
departmentdepartmentdepartment
dept_managerdept_managerdept_manager
employeesemployeesemployees
titlestitlestitles
salariessalariessalaries
implemented in a specific DBMS
Thursday, 03 December 2009
Conceptual model
DBMS agnostic
Thursday, 03 December 2009
Conceptual model
DBMS agnostic
Thursday, 03 December 2009
MySQL Workbench basics
Thursday, 03 December 2009
Creating tables (1)
Thursday, 03 December 2009
Creating tables (2)
Thursday, 03 December 2009
Creating tables (3)
Thursday, 03 December 2009
Creating tables (3)
Thursday, 03 December 2009
Creating tables (4)
Thursday, 03 December 2009
Creating tables (4)
Thursday, 03 December 2009
Creating tables (5)
Thursday, 03 December 2009
Creating tables (5)
Thursday, 03 December 2009
database connections(1)
Thursday, 03 December 2009
database connections(2)
Thursday, 03 December 2009
Reverse engineering in the old world
Thursday, 03 December 2009
Reverse engineering in the old world
show create table departments\G
Create Table: CREATE TABLE `departments`
(
`dept_id` int(10) unsigned NOT NULL,
`department` char(30) NOT NULL,
PRIMARY KEY (`dept_id`),
KEY `department` (`department`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
Thursday, 03 December 2009
Reverse engineering in the old world
show create table departments\G
Create Table: CREATE TABLE `departments`
(
`dept_id` int(10) unsigned NOT NULL,
`department` char(30) NOT NULL,
PRIMARY KEY (`dept_id`),
KEY `department` (`department`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
Thursday, 03 December 2009
Reverse engineering (1)
Thursday, 03 December 2009
Reverse engineering (2)
Thursday, 03 December 2009
Reverse engineering (3)
Thursday, 03 December 2009
Reverse engineering (4)
Thursday, 03 December 2009
Reverse engineering (5)
Thursday, 03 December 2009
Reverse engineering (6)
Thursday, 03 December 2009
Reverse engineering (7)
Thursday, 03 December 2009
Reverse engineering (8)
Thursday, 03 December 2009
Reverse engineering (9)
Thursday, 03 December 2009
Reverse engineering (10)
Thursday, 03 December 2009
forward engineering in the old world
mysqldump --host=development -B \
--no-data crosstab > crosstab.sql
mysql --host=stage < crosstab.sql
Thursday, 03 December 2009
Forward engineering (1)
Thursday, 03 December 2009
Forward engineering (2)
Thursday, 03 December 2009
Forward engineering (3)
Thursday, 03 December 2009
Forward engineering (4)
Thursday, 03 December 2009
synchronization in the old world
Thursday, 03 December 2009
Synchronization (1)
Thursday, 03 December 2009
Synchronization (1)
Thursday, 03 December 2009
Synchronization (2)
Thursday, 03 December 2009
Synchronization (3)
Thursday, 03 December 2009
Synchronization (4)
Thursday, 03 December 2009
Synchronization (5)
Thursday, 03 December 2009
Synchronization (6)
Thursday, 03 December 2009
More goodies
• MySQL Workbench 5.2 (Beta) - live demo
Thursday, 03 December 2009
Thanks
Let's talk!
Thursday, 03 December 2009