MySQL Workbench – “deploying” schema into a real database

deploymentmysql-5.5mysql-workbench

a noob here, finding my way around. So, how can I "transfer, deploy or whatever the right term is" the database tables, fields, etc. that I have created with Workbench into a real database?

The dB I have is currently on a local WAMP installation but I also have it on a real web server.

Many thanks,
Alex

EDIT: I wish to use MySQL Workbench to "draw" database tables, columns and all the other info that is needed for a database to function properly and then transfer this onto my server. Meaning, I wish to avoid having to use clunky phpMyAdmin to create database for my application but instead create it on my PC with Workbench and then transfer all this onto real production server. Thanks!

Best Answer

What you want is called either Forward engineering (create a new set of schemas + db objects based on your model) or Synchronization (apply changes in your model to an existing setup and incorporate changes from that setup into your model). The latter feature works best if you first reverse engineered an existing setup into a model and work from there. Synchronization does a 2-way merge and allows to manually exclude changes from being taken over to the other side or even change the direction for changes, if the automatic detection is not right.

Related Solutions

MySQL Workbench Database Sizes

I have some queries you can run against the INFORMATION_SCHEMA

Run this to get the Total MySQL Data and Index Usage By Storage Engine

SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
(SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM
information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema') AND
engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;

Run this to get the Total MySQL Data and Index Usage By Database

SELECT DBName,CONCAT(LPAD(FORMAT(SDSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size",CONCAT(LPAD(
FORMAT(SXSize/POWER(1024,pw),3),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size",
CONCAT(LPAD(FORMAT(STSize/POWER(1024,pw),3),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Total Size" FROM
(SELECT IFNULL(DB,'All Databases') DBName,SUM(DSize) SDSize,SUM(XSize) SXSize,
SUM(TSize) STSize FROM (SELECT table_schema DB,data_length DSize,
index_length XSize,data_length+index_length TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')) AAA
GROUP BY DB WITH ROLLUP) AA,(SELECT 3 pw) BB ORDER BY (SDSize+SXSize);

Run this to get the Total MySQL Data and Index Usage By Database and Storage Engine

SELECT Statistic,DataSize "Data Size",IndexSize "Index Size",TableSize "Table Size"
FROM (SELECT IF(ISNULL(table_schema)=1,10,0) schema_score,
IF(ISNULL(engine)=1,10,0) engine_score,
IF(ISNULL(table_schema)=1,'ZZZZZZZZZZZZZZZZ',table_schema) schemaname,
IF(ISNULL(B.table_schema)+ISNULL(B.engine)=2,"Storage for All Databases",
IF(ISNULL(B.table_schema)+ISNULL(B.engine)=1,
CONCAT("Storage for ",B.table_schema),
CONCAT(B.engine," Tables for ",B.table_schema))) Statistic,
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') DataSize,CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') IndexSize,
CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') TableSize FROM (SELECT table_schema,engine,
SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
AND engine IS NOT NULL GROUP BY table_schema,engine WITH ROLLUP) B,
(SELECT 3 pw) A) AA ORDER BY schemaname,schema_score,engine_score;

CAVEAT

In each query, you will see (SELECT 3 pw). The pw stands for the Power Of 1024 to display the results.

(SELECT 0 pw) will Display the Report in Bytes
(SELECT 1 pw) will Display the Report in KiloBytes
(SELECT 2 pw) will Display the Report in MegaBytes
(SELECT 3 pw) will Display the Report in GigaBytes
(SELECT 4 pw) will Display the Report in TeraBytes
(SELECT 5 pw) will Display the Report in PetaBytes (please contact me if you run this one)

Here is a report query with a little less formatting:

SELECT IFNULL(db,'Total') "Database",
datsum / power(1024,pw) "Data Size",
ndxsum / power(1024,pw) "Index Size",
totsum / power(1024,pw) "Total"
FROM (SELECT db,SUM(dat) datsum,SUM(ndx) ndxsum,SUM(dat+ndx) totsum
FROM (SELECT table_schema db,data_length dat,index_length ndx
FROM information_schema.tables WHERE engine IS NOT NULL
AND table_schema NOT IN ('information_schema','mysql')) AA
GROUP BY db WITH ROLLUP) A,(SELECT 1 pw) B;

Give it a Try !!!

UPDATE

I just ran the first query as is on a client's DB

mysql> SELECT IFNULL(B.engine,'Total') "Storage Engine",
    -> CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
    -> SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
    -> FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
    -> SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
    -> FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
    -> SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
    -> (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
    -> SUM(data_length+index_length) TSize FROM
    -> information_schema.tables WHERE table_schema NOT IN
    -> ('mysql','information_schema','performance_schema') AND
    -> engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
    -> (SELECT 3 pw) A ORDER BY TSize;
+----------------+----------------------+----------------------+----------------------+
| Storage Engine | Data Size            | Index Size           | Table Size           |
+----------------+----------------------+----------------------+----------------------+
| MyISAM         |             0.673 GB |             0.079 GB |             0.752 GB |
| InnoDB         |             4.227 GB |             2.436 GB |             6.663 GB |
| Total          |             4.900 GB |             2.515 GB |             7.415 GB |
+----------------+----------------------+----------------------+----------------------+
3 rows in set (0.79 sec)

mysql>

It works as is.

UPDATE 2015-01-16 14:46 EST

Here are Queries to Report Sizes and Computes the Memory Units On-The-Fly

Total MySQL Data and Index Usage By Storage Engine

SELECT IFNULL(ENGINE,'Total') "Storage Engine",
LPAD(CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',
SUBSTR(units,pw1*2+1,2)),17,' ') "Data Size",
LPAD(CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',
SUBSTR(units,pw2*2+1,2)),17,' ') "Index Size",
LPAD(CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',
SUBSTR(units,pw3*2+1,2)),17,' ') "Total Size" FROM
(SELECT ENGINE,DAT,NDX,TBL,IF(px>4,4,px) pw1,
IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3 FROM
(SELECT *,FLOOR(LOG(IF(DAT=0,1,DAT))/LOG(1024)) px,
FLOOR(LOG(IF(NDX=0,1,NDX))/LOG(1024)) py,
FLOOR(LOG(IF(TBL=0,1,TBL))/LOG(1024)) pz
FROM (SELECT ENGINE,SUM(data_length) DAT,SUM(index_length) NDX,
SUM(data_length+index_length) TBL FROM (SELECT engine,data_length,index_length
FROM information_schema.tables WHERE table_schema NOT IN
('information_schema','performance_schema','mysql') AND ENGINE IS NOT NULL) AAA
GROUP BY ENGINE WITH ROLLUP) AAA ) AA) A,(SELECT ' BKBMBGBTB' units) B;

Total MySQL Data and Index Usage By Database

SELECT IFNULL(DB,'Total') "Database",
LPAD(CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',
SUBSTR(units,pw1*2+1,2)),17,' ') "Data Size",
LPAD(CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',
SUBSTR(units,pw2*2+1,2)),17,' ') "Index Size",
LPAD(CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',
SUBSTR(units,pw3*2+1,2)),17,' ') "Total Size"
FROM (SELECT DB,DAT,NDX,TBL,IF(px>4,4,px) pw1,
IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3 FROM
(SELECT *,FLOOR(LOG(IF(DAT=0,1,DAT))/LOG(1024)) px,
FLOOR(LOG(IF(NDX=0,1,NDX))/LOG(1024)) py,
FLOOR(LOG(IF(TBL=0,1,TBL))/LOG(1024)) pz
FROM (SELECT DB,SUM(data_length) DAT,SUM(index_length) NDX,
SUM(data_length+index_length) TBL FROM
(SELECT table_schema DB,data_length,index_length FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','performance_schema','mysql')
AND ENGINE IS NOT NULL) AAA GROUP BY DB WITH ROLLUP) AAA) AA) A,
(SELECT ' BKBMBGBTB' units) B;

Total MySQL Data and Index Usage By Database/Storage Engine

SELECT IF(ISNULL(DB)+ISNULL(ENGINE)=2,'Database Total',
CONCAT(DB,' ',IFNULL(ENGINE,'Total'))) "Reported Statistic",
LPAD(CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',
SUBSTR(units,pw1*2+1,2)),17,' ') "Data Size",
LPAD(CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',
SUBSTR(units,pw2*2+1,2)),17,' ') "Index Size",
LPAD(CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',
SUBSTR(units,pw3*2+1,2)),17,' ') "Total Size" FROM
(SELECT DB,ENGINE,DAT,NDX,TBL,
IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3 FROM
(SELECT *,FLOOR(LOG(IF(DAT=0,1,DAT))/LOG(1024)) px,
FLOOR(LOG(IF(NDX=0,1,NDX))/LOG(1024)) py,
FLOOR(LOG(IF(TBL=0,1,TBL))/LOG(1024)) pz
FROM (SELECT DB,ENGINE,SUM(data_length) DAT,SUM(index_length) NDX,
SUM(data_length+index_length) TBL FROM (SELECT table_schema DB,ENGINE,
data_length,index_length FROM information_schema.tables WHERE table_schema NOT IN
('information_schema','performance_schema','mysql') AND ENGINE IS NOT NULL) AAA
GROUP BY DB,ENGINE WITH ROLLUP) AAA) AA) A,(SELECT ' BKBMBGBTB' units) B;

MySQL Workbench ERROR 1064

Remove the (11) from the DOUBLE(11):

`f_sells` DOUBLE  NULL DEFAULT NULL ,

Approximate numeric dataypes (Float and Double) need no parameter. There is also a non-standard syntax with 2 parameters. From the MySQL docs, Floating-Point Types (Approximate Value):

MySQL allows a nonstandard syntax: FLOAT(M,D) or REAL(M,D) or DOUBLE PRECISION(M,D). Here, “(M,D)” means that values can be stored with up to M digits in total, of which D digits may be after the decimal point. For example, a column defined as FLOAT(7,4) will look like -999.9999 when displayed. MySQL performs rounding when storing values, so if you insert 999.00009 into a FLOAT(7,4) column, the approximate result is 999.0001.