Method 1:
Method 2:
A quick way to duplicate MySQL databases is to do the following:
Step 1: Dump the database either through PHPMyAdmin’s export form or through mysqldump (as I mentioned above). Note (usually, it’s mysqldump <database-name> -h<hostname> -u<username> -p<password> if you ommit the -h switch it will default to “localhost”). After you enter the command below and press enter, you will be prompted for a password because you did not enter the password after the -p switch.
$ mysqldump <database-name> -u<username> -p >> somedatabasetable.sql
Step 2: Once you have the sql file, log into mysql and create a database or “use” an existing database – again, ommiting the -h will default to localhost and you will be prompted for a password if you don’t enter a password after the -p switch.
$ mysql -u<username> -p
Step 3: After logging in, you will need to either create a new database and select the database for use with the import, or use an existing database. For this example, we’ll create a new database. Remember to use backticks and not apostrophe’s to create your new MySQL database.
mysql> create database `somedb`
Step 4: Alright! Now we’re ready to start using! Issue a “use” statement to select the empty database
mysql> use `somedb`
Step 5: Remember where you put the SQL file and issue a “source” command:
mysql> source somedatabasetable.sql
The query is executed and if you didn’t mess around with the SQL produced by mysqldump, it should run things flawlessly.
For reference, the database version I was working with is MySQL 5.0.45 and the mysqldump version I was working with is 10.11 on my development rig.
Method 2:
It is possible to execute an SQL file without logging into the mysql client first. Previously, I used login to mysql and use the source command to execute commands from an SQL text file.
Now, I execute it all straight from bash or windows shell without having to login to the MySQL client.
To import an SQL file by passing it as if it were an arguement, you need to use the < sign. So for example, say that I wanted to import an external database from production and wanted to import it into my development environment, I can do so by doing the following:
How to Import an SQL file directly from the Command Line:
Step 1: Download the source sql file.
Step 2: Extract the SQL file sif required – usually, you’ll get the file in an archived form since an uncompressed SQL file is hideously large.
Step 3: Execute the following in the command line:
$ mysql -uusername -p -h databasehostname databasename < locally-extracted-sql-file.sql;
Step 4: Enter your password at the prompt.
Step 5: Check that your changes have been applied and make adjustments as required.
You should now see that the sql commands in the external file has been executed.