How to copy & move MySQL database

I'm very new to servers and databases, my experience spanning only a few installations of XAMPP for easy website testing and exploration. So I have XAMPP installed on my laptop, and I just put it on my netbook. I copied & moved my htdocs folder (soon to be synced with dropbox), and now need to move the database.

I am not looking for an extensive backup solution. What is the simplest way to copy my db setup on one XAMPP stack to another?

Solution 1:

The easiest way is probably by using phpMyAdmin, which is installed as part of XAMPP.

To export

In a browser, open

http://localhost/phpmyadmin

Click on export

Select your database(s) from the list

Tick the save as file box at the bottom of the page.

Click Go

Your database(s) will be exported as an SQL file, which you can use to re-create them on your other machine. Copy this file to your laptop.

To import

In a browser, open

http://localhost/phpmyadmin

Click on import

Browse to the file you just exported.

Click Go

Solution 2:

You can use the built-in function of mysql to do this.

If you have direct access to your mysql server by typing mysql into your console you can do some backups.

mysqldump -p databasename > filename_to_store_sql.sql

Copy the .sql file to the new computer/place and do the following.

mysql -p databasename < filename_to_store_sql.sql

Solution 3:

For export all databases:

mysqldump --all --all-databases --complete-insert --add-drop-table --add-locks --allow-keywords --lock-tables --quote-names --user=root --password=1234 > ~/Desktop/mysql_all_db.sql

For import:

mysql -uroot -p < ~/Desktop/mysql_all_db.sql

Solution 4:

Another alternative might be to use the MySQL Workbench