|
Copying/Moving a MySQL database |
|
 Open source to the rescue !
If you have access to PHPMyAdmin, it can be done fairly easily, but I won't go into that here, since the process varies
depending on what version of PHPMyAdmin you have access to.
It's also a bit limited on the size and number of tables it can handle.
Personally, I prefer to copy databases using a command line tool called mysqldump.
That would require you to have access to a BASH prompt on your server, via SSH, or maybe telnet.
If you do use SSH... here is how to copy a database.
On the source server...
mysqldump -p -h localhost DBNAME table1_name table2_name table3_name | gzip > ./mydb.gz
After entering your mysql password, you'll have a compressed file of insert statements ready to be downloaded to the target server.
You can use SCP to copy your file directly to the target server
scp -l128 remoteusername@source_server.com:./mydb.gz
After entering the SSH password for remoteusername the gzip file will start downloading.
Afterwards, uncompress the file.
gzip -d ./mydb.gz
The resulting "mydb" file may need a little editing. I like to add a "USE DATABASENAME;"
as the first line to simplify restores. It's also handy when I find the backup file a year later..
I can see which database it went to. |
|
Once thats done, you can execute all the SQL statements in the mydb file like this ...
mysql -p <./mydb
After entering your MySQL password, you'll soon have an exact copy of the database from the source server. |
