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

There are two methods you can use. If both your source database and the target database are on May First/People Link servers, then both methods are available to you. If either the source or the target are on other servers, then they may or may not work depending on the software and access provided by the other provider.

Using phpMyAdmin

If your database is relatively small (less than 3 MB), you can use phpMyAdmin to export your database from one site and then import it into another. For this method to work, both the source and target database servers must have the phpMyAdmin program installed. All May First/People Link servers have phpMyAdmin installed (see our our phpMyAdmin FAQ to learn how to access it).

You will need the database username and password for each site (this may be different from the ssh/sftp/ftp username and password for the site). Please see our faq on retrieving your database password if you don't know it.

• Login to phpMyAdmin 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
  

  If you get an error like "Access denied for user 'DBUSER'@'localhost' (using password: NO) when trying to connect", you may need to create a ~/.my.cnf file. On Mayfirst servers, for many web apps, you may be able to automatically create one using the command mf-generate-my-cnf. If that does no work, you can create one manually. The format is simple. In a text file in your home directory called .my.cnf place something like:

  [client]
  user = DBUSERNAME
  password = DBPASSWORD
  [mysql]
  database = DBNAME
  

  You will want to make sure it's only readable by your user:

  chmod 600 .my.cnf
  

• 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