Resolved Script to Create a MySQL Database

hunter1611 · 12-24-2012, 11:46 PM

I was hoping someone could point me in the right direction. I've made a PHP script that relies on a MySQL database, and I wanted to also make a script to create the required database for my PHP script for when I give a copy of my script to someone else. (Basically, so they can run the script to create the database, and then be able to use the PHP script that I've given them).

SO, what language/tool do I use to make a database creation script? Are there security conflicts that I'm going to run into? Because the script will be creating a MySQL database on a web hosting account...

Old Pedant · 12-25-2012, 03:00 AM

Do you want to clone the existing database AND ALL ITS CONTENTS and have the other person install that?

Or do you want to just clone the existing tables but with no data in any tables?

Or some mix of the two?

The first option--some database and all its contents--is actually the easiest. MySQL provides a command line tool that does this:

Code:

mysqldump -uUSERNAME -pPASSWORD nameofdatabase > anyNameYouChoose.sql

Then, from the command line, it just as easy to restore (or re-create) that database:

Code:

mysql -uUSERNAME -pPASSWORD < anyNameYouChoose.sql

Again, these are issued from the command line. From the LINUX command line or from at DOS window command line on Windows. The name of the dump file doesn't have to end in ".sql" but it's traditional to do so.

********

If you want a mix, some tables with data and some without, the easiest way might be to dump the entire database, import (re-create) it on another machine, issue TRUNCATE TABLE xxx commands to clear the data out of the tables that are to be blank, and then re-dump from that second machine.

Old Pedant · 12-25-2012, 03:02 AM

If the web hosting account doesn't have access to the MySQL command line tools, then *probably* you could just use mysqlphpadmin to import the same ".sql" file.

If not it would be trivial to create your own PHP program to do so: It would simply read one line at a time from the ".sql" text file and issue that command to the MySQL DB.

hunter1611 · 12-25-2012, 04:32 AM

Hmmm...OK. Thanks.