Link to home
Start Free TrialLog in
Avatar of sharingsunshine
sharingsunshineFlag for United States of America

asked on

Linux Server to Amazon VPS Help Migratng MySQL DB

I have a Centos Linux instance with Amazon and I need to migrate my DB's to the instance.  I have acesss to ssh on both ends.  I have PHPMyAdmin on both ends but trying to import to MyAdmin on the Amazon side didn't work.  The import was too large.  Additionally, I want to do this server to server because it will take too long to do each DB from the desktop.

I prefer to do it via ssh if that is possble.

Please tell me how I can get this done.

Thanks,

Randal
Avatar of MajorBigDeal
MajorBigDeal
Flag of United States of America image

Can you store it on S3, which you can do initially with either an upload or directly from an instance. Then it will be available to your instances using the S3 CLI.  For Linux
the format of the S3 commands would be:

sudo s3cmd put <file name> s3://<bucket name>

sudo s3cmd get s3://<bucket name>/<file name>
Avatar of sharingsunshine

ASKER

I don't have S3 setup nor do I want to do it that way if I can help it.  As I said above, I really want to do this via ssh.
Take the mysql dump on the source server using the following command

mysqldump   -uroot  -p  --all-databases > /root/databasedump.sql

If you want only one particular database try replacing --all-databases with the database that you want to backup.

once the backup or the dump is complete transfer it to the remote server using the following command.

scp   <databasebackupfile>   root@amazonserverIP:/root/

after authenticating the file will be transferred to the remote amazon server.

on the remote server use the below command to restore the database.

mysql  -uroot  -p  <  databasebackupfile.sql
I tried the scp approach and I get the following message

ssh: connect to host xx.xx.xx.xx port 22: Connection timed out
lost connection

It never asked for any authentication either.
ASKER CERTIFIED SOLUTION
Avatar of Manohar M.
Manohar M.
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I have ssh installed on the Amazon machine.  I will have to do some research on any iptables or firewall.  I have mod evasive but it should be cleared for the ip from the old server.  Thanks for these suggestions and I will get back to you.
I tried the approach of initiating the scp from Amazon and the data transferred over successfully.  I have initiated the mysql commands but it has been over 30 minutes now. How long should it take to put the db's in the correct place?  The dump was only 5 Gb .

Is there a way to tell if anything is really happening?
this was a great help.  Looking at the logs it tried to populate MySQL with the DB's but it defaulted to the system disk so it is now full.

I will post another question on how to get it to store on a different mount point.  So glad you had this answer.