How to migrate MySQL database from VPS to DreamCompute via SSH?


#1

Greetings,

I’m trying to follow the instructions on this page: https://help.dreamhost.com/hc/en-us/articles/221614988-Importing-a-database-via-SSH-Shell-

However, this is not working. I’m executing the command on my DreamCompute server where the old host details are from my MySQL VPS page and the new host is my compute details.

However, I get errors like this:

Unknown MySQL server host '***.***.***.***:3306' (0)
mysqldump: Got errno 32 on write

or:

ERROR 1130 (HY000): Host 'ip-***.***.***.***.nodes.dream.io' is not allowed to connect to this MySQL server
mysqldump: Got errno 32 on write

I also followed the steps on this page as well: https://help.dreamhost.com/hc/en-us/articles/215575787-Managing-MySQL-users-allowable-hosts-and-privileges

added ip-***.***.***.***.nodes.dream.io and ***.***.***.*** as allowable hosts on my VPS database settings.

The *** . *** . *** . *** above is the IP address of my DreamCompute database server.

I looked up the DNS records and found my old host database IP and used the UFW firewall to allow this IP access to the 3306 mysql port on my DreamComputer server, but it still does not work and I’m not sure what to do next.

Thanks
Kind regards


#2

If you can you use the phpMyAdmin interface provided by DreamHost to access the database on the VPS, I’d take the dump from phpMyAdmin and restore that on your DreamCompute server.


#3

Thanks for the reply. I figured out a 2-step command like the one below, which seems to work pretty nicely when executed on the DreamCompute server through SSH:

mysqldump -u remoteusername -p remotepassword -h your.site.com databasename > dump.sql
mysql -u localusername -p localpassword databasename < dump.sql

#4

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.