Basic Sql question related to migrating Wordpress.

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!

8 Replies

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. :-)

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

Cheers

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/mys … ssh-tunnel">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?

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

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 :-)

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

@Alice:

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.

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.

Reply

Please enter an answer
Tips:

You can mention users to notify them: @username

You can use Markdown to format your question. For more examples see the Markdown Cheatsheet.

> I’m a blockquote.

I’m a blockquote.

[I'm a link] (https://www.google.com)

I'm a link

**I am bold** I am bold

*I am italicized* I am italicized

Community Code of Conduct