Avatar of sharingsunshine
sharingsunshine
Flag 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
LinuxApache Web ServerMySQL Server

Avatar of undefined
Last Comment
sharingsunshine

8/22/2022 - Mon
MajorBigDeal

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>
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.
Manohar M.

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
sharingsunshine

ASKER
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
Manohar M.

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
sharingsunshine

ASKER
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.
sharingsunshine

ASKER
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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
sharingsunshine

ASKER
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.