Changes between Initial Version and Version 1 of copy_mysql


Ignore:
Timestamp:
Oct 5, 2009, 9:39:46 AM (11 years ago)
Author:
Jamie McClelland
Comment:

--

Legend:

Unmodified
Added
Removed
Modified
  • copy_mysql

    v1 v1  
     1= How do I copy my MySQL database from one site to another? =
     2
     3There are two methods you can use.
     4
     5== Using phpMyAdmin ==
     6
     7If your database is relatively small (less than 5 or 10 MB), you can use [wiki:phpmyadmin_link 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.
     8
     9 * Login with the the source database username and password.
     10 * Click the export button
     11 * Select the database you want to export
     12 * Check the box that says "Add DROP TABLE / VIEW / PROCEDURE / FUNCTION"
     13 * Click Go.
     14 * 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.
     15 * The second line will be: "USE `name_of_source_db`". Change the name of the source database into the name of the target database.
     16 * Copy and paste the output into a file and save the file.
     17 * Logout and log back in again with the ''target'' database username and passwrd
     18 * Click Import.
     19 * Click Browse and find the file you just saved.
     20 * Click Go.
     21
     22== Using the Command Line and Secure FTP ==
     23
     24For large databases, you may need to use a more reliable method.
     25
     26You will need to be ready to access both the source and target sites via both [wiki:secure_shell secure shell (ssh)] and [wiki:sftp Secure FTP]. You will also need to know the name of both the source and target database names.
     27
     28 * [wiki:secure_shell 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:
     29{{{
     30mysqldump name-of-source-database > name-of-source-database.sql
     31}}}
     32 * [wiki:sftp Secure FTP] into the source site server and download the file name-of-source-database.sql to your computer
     33 * [wiki:sftp Secure FTP] into the target site server and upload the file
     34 * [wiki:secure_shell 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:
     35{{{
     36mysql name-of-target-database < name-of-source-database.sql
     37}}}
     38
     39