Basic Sql question related to migrating Wordpress.
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
Otherwise, grab MySQL Workbench
Cheers
I use putty with keyfile to connect generally and I followed the instructions here under the "Setting Up the Tunnel" heading
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?
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
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.
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.