Linode Forum

[Alice]

Last August I switched to Linode and moved several Wordpress blogs over.

I'm a complete amateur and had never encountered the command line prior to this, but this went fine.

Now 6 months later I want to move another Wordpress blog over to my Linode, but I've forgotten how to do the part involving moving the database.

Can anyone help me with the commands? I don't have phpmyadmin setup as I read it can be a security problem and I don't generally need it. Previously I just used the command line but I've completely forgotten everything I learned 6 months ago about the sql commands.

Hopefully this is not too embarrassing a question :-)

Thanks!

[hoopycat]

I believe WordPress has an import/export thing. That's probably the easiest way to go.

Otherwise, grab MySQL Workbench; it will export and import entire databases like a boss. Someone else will chime in with the mysqldump approach, but I'm wicked lazy. :-)

[czhang]

This link may be of use: http://codex.wordpress.org/Backing_Up_Your_Database

Cheers

[Alice]

Decided to take Hoopycat's advice and try MySql workbench but I can't for the life of me get it to connect.

I use putty with keyfile to connect generally and I followed the instructions here under the "Setting Up the Tunnel" heading

http://library.linode.com/databases/mysql/mysql-ssh-tunnel

but I don't really understand the part that says "Once you've connected to the remote server with this tunnel configuration, you'll be able to direct your local MySQL client to localhost:3306."

I am quite possibly making some beginner error.

Any advice?

[hoopycat]

MySQL Workbench has all that built in... I don't have it in front of me right now, but you can specify a ssh host/user/password/keyfile/etc to connect with, and then tell it to connect to MySQL on 127.0.0.1:3306 (or whatnot).

[Alice]

I tried that option too, but I'm not sure what I'm doing wrong.

I'm getting error
Connecting to MySQL server (my ip)...

Error connecting SSH tunnel: Could not open socket to 127.0.0.1


For the first values I'm using the same details as I use to connect using putty

SSH Hostname 127.0.0.1:(port that I use an alternative to 21)
SSH username same as putty
SSH password
SSH keyfile

Then

Mysql hostname: my ip
mysql port: 3306
username root
password

Not sure if it is a problem with these above settings or a problem with the settings I have somewhere else.

Help much appreciated :-)

[vonskippy]

Kids today and their fancy schmancy tools and GUI this GUI that...

You just need PUTTY

On Old Host - Backup WP Database
#mysqldump -u root -p wpdb > /backup/wordpress wpdb_backup.sql

Copy the *.SQL file from your OLD Host to your NEW Host
use SCP directly, or copy it to your local workstation then back up to your new host.

On New Host - Create new WP database
#mysql -u root -p <then enter mysqld root password>
> Create Database wpdb2; (should respond OK)
>Grant ALL privileges on wpdb2.* to wp2@localhost Identified by “put-strong-pswd-here”; (should respond OK)
>Flush Privileges; (shoudl respond OK)
> Exit (should respond with BYE)

On New Host - Populate new database (wpdb2) with old database (wpdb)
mysql> use wpdb2
mysql> source /backup/wordpress/wpdb_backup.sql
mysql> Grant ALL privileges on wpdb2.* to wp2@localhost Identified by “put-strong-paswd-here”; (should respond OK)

Copy your ENTIRE WP directory from OLD host to NEW Host

Edit your WP-CONFIG.PHP
To point to your new database with your new username/pswd

It should be working, with all your users, content, etc the same as on your old host.

Then go back and double check all your file/directory permissions to make sure they're set to the recommended security settings (see WP install guide for details).

[hoopycat]

Alice wrote: SSH Hostname 127.0.0.1:(port that I use an alternative to 21)
Mysql hostname: my ip

Switch it around -- your IP for SSH hostname, 127.0.0.1 for MySQL hostname. It has to bring the SSH up before it can bring the MySQL up.

[Alice]

Well the good news is I managed it from putty before I checked back, but thanks so much for the detailed instructions. Next time I forget how to do this I will use this thread :) blog is working fine.

A good reminder to check the file permissions after the transfer.

I still can't connect using mysql workbench and would like to.

I changed them around and I'm still getting


Connecting to MySQL server (my ip)...

Can't connect to MySQL server on '127.0.0.1' (10061)

Any further ideas of settings that might be preventing the connection are appreciated.