wiki:copy_mysql

Version 1 (modified by Jamie McClelland, 11 years ago) (diff)

--

How do I copy my MySQL database from one site to another?

There are two methods you can use.

Using phpMyAdmin

If your database is relatively small (less than 5 or 10 MB), you can use phpMyAdmin to export your database from one site and then import it into another. You will need the database username and password for each site.

  • Login with the the source database username and password.
  • Click the export button
  • Select the database you want to export
  • Check the box that says "Add DROP TABLE / VIEW / PROCEDURE / FUNCTION"
  • Click Go.
  • You will see a big textarea box containing a text representation of your database. One of the first lines will be: "CREATE DATABASE name_of_source_db". That line should be deleted.
  • The second line will be: "USE name_of_source_db". Change the name of the source database into the name of the target database.
  • Copy and paste the output into a file and save the file.
  • Logout and log back in again with the target database username and passwrd
  • Click Import.
  • Click Browse and find the file you just saved.
  • Click Go.

Using the Command Line and Secure FTP

For large databases, you may need to use a more reliable method.

You will need to be ready to access both the source and target sites via both secure shell (ssh) and Secure FTP. You will also need to know the name of both the source and target database names.

  • Secure Shell into the source site server and run the following command to create a dump of your database into a file called name-of-source-database.sql. Replace "name-of-source-database" with the actual name of your source database:
    mysqldump name-of-source-database > name-of-source-database.sql
    
  • Secure FTP into the source site server and download the file name-of-source-database.sql to your computer
  • Secure FTP into the target site server and upload the file
  • Secure shell into your target server and run the following command to import the source database into the target database. Replace "name-of-target-database" with the name of your actual target database:
    mysql name-of-target-database < name-of-source-database.sql