Copy a MySQL database to your local MySQL machine

Here’s one way to copy a MySQL database from one server to another using phpMyAdmin, and the MySQL command line client.

Requirements :

• MySQL 5.x installed on your local machine
• MySQL command line client on your local machine
• Access to phpMyAdmin on your source MySQL machine

Assumptions:

• You have access to the MySQL command line client.
• You have the root password for your local MySQL instance
• You are comfortable using phpMyAdmin and MySQL command line client

Instructions:

1. Log into phpMyAdmin on your source MySQL machine.
2. Click the Export link on the lower part of the home page.
3. Select your database in the ‘Export’ box on the left.
4. Make sure SQL is selected under the database box.
5. In the ‘Options’ box on the right, make sure Structure and Data are selected.
6. Make sure to check ‘Add DROP TABLE / DROP VIEW’ as well.
7. Towards the bottom, check the box next to ‘Save as file’. This will be the file we import later.
8. Click the GO button, and your browser should prompt you to download your SQL file. *Make note of where you saved your exported sql file. I saved mine to d:downloadsimport.sql because it’s an easy path to use later.
9. Open your local MySQL Command Line Client. ( From MySql Program menu or type “mysql -u root -p” in the run dialog box ).
10. Enter your root password when prompted.
11. Import your database by running this command “mysql . d:downloadsimport.sql“. Be sure to adjust the path to match where you saved your sql file from step 8.
12. Now you should see your database listed when you run the command “show databases;“

 

Here are instructions for doing the same thing on OS X

1. Follow steps 1 through 8 above to created your sql export file
2. Connect to mysql “/usr/local/mysql/bin/mysql -u root -p”
3. {enter root password}
4. If it’s not already created, created your databse “create database {your db name};”
5. Select your database “user {your db name};”
6. “mysql . /users/ericfickes/Downloads/mydbfile.sql”

Be sure to adjust the paths listed to match your MySQL install, and the location where you downloaded the sql file.