CODEWITHSUNDEEP
mysqlddlmysql-workbench
reggie
reggie

Reputation: 858

How do I run a script on/in a mysql database?

I have created a MySQL database using MySQL Workbench. It has about 20 tables. I cannot figure out how to run scripts on the database.

Basically, I want to make a database creation script which will allow me to create my database on any other MySQL server.

Upvotes: 2

Views: 32654

Answers (5)

Patrick Desjardins
Patrick Desjardins

Reputation: 140753

Here are some hints :

mysqldump -u [username] -p[password] [databasename] > [backupfile.sql]

And

mysql -u [username] -p[password] [database_to_restore] < [backupfile.sql]

It does generate a file with structures and data. The second line take the data from the file to the database.

There is no space between -p and the password. It would be better not to put the password in your command as this can end up in your .bash_history. If you omit the password it will be prompted for:

mysqldump -u [username] -p [databasename] > [backupfile.sql]

And

mysql -u [username] -p [database_to_restore] < [backupfile.sql]

Upvotes: 3

mmacaulay
mmacaulay

Reputation: 3029

create database mydatabase;

grant all on mydatabase.* to 'myuser'@'%' identified by 'mypasswd';

create table mytable (
id int not null auto_increment,
myfield varchar(255) not null default ''
);

etc...

Put that in a file called mydbcreate.sql and run (from the command line) 

mysql -u <myuser> -p < mydbcreate.sql

Upvotes: 9

John Himmelman
John Himmelman

Reputation: 22000

Knowing the mysql command line utilities is useful & important but for performing common tasks use a database administration tool, such as phpMyAdmin, to create and manage your databases. These tools make it extremely easy to create & browse tables, create export scripts (a file with all the queries to create tables, insert data, etc...), and other great things.

phpMyAdmin is great, runs in and LAMP environment, is free, and easy to use.

http://www.phpmyadmin.net/home_page/index.php

Screeny of exporting a db in phpMyAdmin (cropped shot)alt text http://www.typolight.org/tl_files/images/documentation/sql-export.jpg

Upvotes: 3

Peter Bailey
Peter Bailey

Reputation: 105878

You have two options.

  1. Export a CREATE script that you can store and/or arbitrarily run on any MySQL server
  2. Synchronize with a specific MySQL server directly.

For #1, use the following menu command

File >> Export >> Forward Engineer SQL CREATE Script...

From here, you can use a program like MySQL GUI Tools or see the other answers in this thread to import the generated SQL script from the command line.

For #2, use the following menu command

Database >> Forward Engineer...
- or -
Database >> Synchronize Model...

Before you do either of these, you may want to establish a connection first via

Database >> Manage Connections...

Important! For both of these options, make sure you look closely at every option available in the prompts - small settings can make a big change in the output!

Upvotes: 0

MJB
MJB

Reputation: 7686

Perhaps what you need is to know that those statements are called DDL? Then you can go to any MySQL site and ask how to generate DDL? Not being snarky, just saying . . .

Also, you can try this:

use mysqldump from the command line with the --no-data option. Ask for help if need more info. But a simple example would be:

$ mysqldump -u{username} -p{password} {dbname} --no-data

(of course, without the curly braces)

Upvotes: 2

Related Questions