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

Question

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.

Answer 1

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)

Answer 2

Here is 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.

Answer 3

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

Answer 4

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!

Answer 5

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)