Moving wordpress cross hosts
Backup DB
Backing Up Your Database
Languages: English • 日本語 • Português do Brasil • РуÑÑкий • ไทย • ä¸æ–‡(简体) • (Add your language)
It is strongly recommended that you backup your database at regular intervals and before an upgrade.
Restoring your database from backup is then possible if something goes wrong.
Contents |
Backup using cPanel X
cPanel is a popular control panel used by many web host. The backup feature can be used to backup your MySql database. Do not generate a full backup, as these are strictly for archival purposes and cannot be restored via cPanel. Look for ‘Download a MySQL Database Backup’ and click the name of the database. A *.gz file will be downloaded to your local drive.
There is no need to unzip this file to restore it. Using the same cPanel program, browse to the gz file and upload it. Once the upload is complete, the bottom of the browser will indicate dump complete. If you are uploading to a new host, you will need to recreate the database user along with the matching password. If you change the password, make the corresponding change in the wp-config.php file.
Using phpMyAdmin
phpMyAdmin is the name of the program used to manipulate your database.
Information below has been tried and tested using phpMyAdmin versions 2.5.3, 2.5.7-pl1, and 2.6.1-pl3 running on Unix.
Detailed options are listed below, though in most cases you will be fine with the default export options.
Backup Process with phpMyAdmin
- Log into phpMyAdmin on your server
- From the main login screen, select ‘Databases’
- Now click the name of your database – or your WordPress database if you have several databases.
- The next screen will show you all the tables inside your WordPress database.
Ignore those, and click the ‘Export’ tab on the top set of tabs. - Look at the left box at the top of the Export section. All the tables in the database you selected are in that box.
- If you have other programs that use the database, then choose only those tables that correspond to your wordpress install. They will be the ones with that start with “wp_” or whatever ‘table_prefix’ you specified in your ‘wp-config.php’ file.
- If you only have your WordPress blog installed, leave it as is (or click ‘Select All’ if you changed the selection)
- Ensure that the SQL radio button is selected.
- The SQL section
- ‘Structure’
- ‘Add DROP TABLE / VIEW / PROCEDURE / FUNCTION’
- ‘Add IF NOT EXISTS’
- ‘Add AUTO_INCREMENT’ and
- ‘Enclose table and field names with backquotes’
- The DATA section
- Tick the ‘Save as file’ option, and leave the template name as is.
- Now click ‘Go’ and you should be prompted for a file to download. Save the file to your computer.
Depending on the database size, this may take a few moments. - You have now backed up your database.
(You may not need to do this step)
(Your screen may look slightly different, depending on the version.)
Tick the following boxes:
It doesn’t matter too much what you check here. So long as you check the DATA box itself, the Complete inserts and Extended Inserts are mostly cosmetic changes to the output style, though Extended Inserts can help if you are having issues with the file size.
If you wanted, you could download a backup in each of the compression formats. Your choice. For example: None and “zipped“: 
Remember – you have NOT backed up the files and folders – such as images – but all your posts and comments are now safe.
Using Straight MySQL Commands
phpMyAdmin can not handle large databases so using straight MySQL code will help.
1. Change your directory to the directory you want to dump things to:
user@linux:~> cd files/blog
2. Use mysqldump to dump all database tables. To dump only certain tables from the database, give their names at the place shown by (tablename tablename tablename), and omit the parentheses ( ) in any case. (For help, try: man mysqldump.):
user@linux:~/files/blog> mysqldump --add-drop-table -h mysqlhostserver -u mysqlusername -p databasename (tablename tablename tablename) | bzip2 -c > blog.bak.sql.bz2 Enter password: (enter your mysql password) user@linux~/files/blog>
Example: mysqldump --add-drop-table -h db01.example.net -u dbocodex -p dbwp | bzip2 -c > blog.bak.sql.bz2 Enter password: my-password user@linux~/files/blog>
The bzip2 -c after the | (pipe) means the backup is compressed on the fly, and the > blog.bak.sql.bz2 sends the bzip output to a file named blog.bak.sql.bz2. It does in one line the same thing that these two commands do:
mysqldump --add-drop-table -h db01.example.net -u dbocodex -p dbwp > blog.bak.sql bzip2 blog.bak.sql
Using MySQL Workbench
MySQL Workbench (formerly known as MySQL Administrator) is a program for performing administrative operations, such as configuring your MySQL server, monitoring its status and performance, starting and stopping it, managing users and connections, performing backups, restoring backups and a number of other administrative tasks.
You can perform most of those tasks using a command line interface such as that provided by mysqladmin or mysql, but MySQL Workbench is advantageous in the following respects:
- Its graphical user interface makes it more intuitive to use.
- It provides a better overview of the settings that are crucial for the performance, reliability, and security of your MySQL servers.
- It displays performance indicators graphically, thus making it easier to determine and tune server settings.
- It is available for Linux, Windows and MacOS X, and allows a remote client to backup the database across platforms. As long as you have access to the MySQL databases on the remote server, you can backup your data to wherever you have write access.
- There is no limit to the size of the database to be backed up as there is with phpMyAdmin.
Note: The instruction below was written for older version (MySQL Administrator).
Backing Up the Database
This assumes you have already installed MySQL Admin and set it up so that you can login to the MySQL Database Server either locally or remotely. Refer to the documentation that comes with the installation package of MySQL Admin for your platform for installation instructions.
- Open the MySQL Admin client and login as you had previously set up to do.
- From the icon menu on the left hand side of the client window select Backup.
- If you have not already created a Backup Project, do this now by clicking on the “New Project” button at the lower part of the window and type in a name for the Backup Project where prompted.
- Select one or more databases that you want to Backup (in the MySQL Admin client these are called a “Schema” (pl. “Schemata”)). Add them to the Backup Content window on the right using the right-pointing arrow button.
- When you have selected the Schema(ta), you can save the Backup Project. Or you may simply choose to Backup Now using the button on the lower right of the window.
- A dialogue will come up asking you where to put the Backup. Enter the pathname or browse to the location using the dialogue.
- Assuming all is correct (and you have write permissions in the directory to which you are writing the Backup), the backup will complete shortly.
Restoring From a Backup
- Open the MySQL Admin client and login as you had previously set up to do.
- From the icon menu on the left hand side of the client window select Restore.
- Click the “Open Backup File” button on the lower right of the window.
- Type in or browse to the Schema(ta) backup file and select. Click “Open”.
- The Target Schema(ta) will most likely be the “Original Location”, or you may choose an alternate location using the drop-down menu.
- Click the “Start Restore” button on the lower right of the window. The database restore will commence.
MySQL GUI Tools
In addition to MySQL Workbench, there are many GUI tools that let you backup (export) your database.
| Name | OS (Paid edition) | OS (Free edition) | Notes |
|---|---|---|---|
| MySQL Workbench | Windows/Mac/Linux | Windows/Mac/Linux | See above |
| EMS SQL Management Studio for MySQL | Windows | – | |
| Aqua Data Studio | – | Windows/Mac/Linux | Available in 21 languages |
| Navicat for MySQL | Windows/Mac/Linux | Windows/Mac/Linux (Lite) | Available in 8 languages |
| SQLyog | Windows | – | |
| Toad for MySQL | – | Windows | |
| HeidiSQL | – | Windows | |
| Sequel Pro | – | Mac | CocoaMySQL successor |
| Querious | – | Mac |
Using WordPress Database Backup Plugin
Austin Matzko maintains a WordPress plugin originally created by Skippy called WordPress Database Backup (WP-DB-Backup). It was bundled with WordPress 2.0 but is no longer included with WordPress 2.1.
Installation
- Search “WP-DB-Backup” on Administration > Plugins Panel > Add New.
- Activate the plugin.
- The plugin will attempt to create a directory
/wp-content/backup-*/inside your WordPress directory. You may need to make/wp-contentwritable (at least temporarily) for it to create this directory.
Backing up
- Navigate to Administration > Tools > Backup Panel
- Select any tables, in addition to the core WordPress tables, that should be backed-up.
- Select the Backup Options; the backup can be saved on the server, downloaded, or emailed.
- Finally, click on the Backup button to actually perform the backup. You can also schedule regular backups.
Restoring the Data
The file created is a standard SQL file. If you want information about how to upload that file, look at Restoring Your Database From Backup.
Resources and Backup Plugins
Change wp-config
WordPress has a famous 5 minute installation, that works pretty well.  However if you follow all the steps while using GoDaddy, once you get to the wp-install.php step, you’ll find an error connectiong to the database.
First you need to create a MySQL database on Godaddy.  Just click the MySQL option under the “Databases†tab in your hosting control center, you’ll be asked to fill in some info on the name of your database, the username you want, and the password.  Remember this, because you need to add it to your wp-config file.
So, let’s add it. Open up your wp-config file and look for these lines:
// ** MySQL settings - You can get this info from your web host ** //
/** The name of the database for WordPress */
define('DB_NAME', 'putyourdbnamehere');
/** MySQL database username */
define('DB_USER', 'usernamehere');
/** MySQL database password */
define('DB_PASSWORD', 'yourpasswordhere');
/** MySQL hostname */
define('DB_HOST', 'localhost');
Now you’ll put your info in, so where it says: ’putyourdbnamehere’ you’ll put in the name of whatever you called your database, then your password etc.
Here’s the important part!
You’ll need to change this line:
/** MySQL hostname */
define('DB_HOST', 'localhost');
When viewing your list of databases in GoDaddy’s control panel, you’llsee a little pencil icon next to the name of your database, like this:
Click on the pencil and you’ll see a list of info like this:
You’ll notice it’s all your info that you put in your wp-config file. Copy that funky long number after it says host name.
The 5 minute install says for the hostname: // 99% chance you won’t need to change this value - Well, with GoDaddy chance are 100% that you will have to change it.
So when you see this:
define(‘DB_HOST’, ‘local_host’);   in your wp-config file
Put that long Host Name value where it says “local_host†like this:
define(‘DB_HOST’, ‘p2nI30mysql16.secureserver.net‘);
Then, everything will work. Â Phew.
Change URL
How To Move WordPress To A New Server Or Host
26. Dec, 2008 
77 Comments
I have seen many requests online on how to move a WordPress blog to a new server or host- including requests from some of my readers.
It seems as though the easy part is transferring the files. What most people find difficulty with is moving their WordPress database to the new server or hosting company.
While there are other tutorials available online, many seem to lack clear instructions on how to move your blog. In this tutorial, I’m going to take you step-by-step and show you exactly how to move your WordPress blog to a new server or host.
It may look difficult at first with all the steps I’ve included, but I assure you- it’s easier than it looks- especially after you move a blog a few times. It may seem like there are many steps to moving your WordPress blog to a new server, but there really aren’t that many. I’ve just broken each step down to make it very clear even to beginners exactly how to move their WordPress blog.
So, are you ready? Here we go:
Open either your FTP program or your file manager through your hosting company and browse to your home or public_html directory. With WordPress installed, it should look something like this:
You want to download all the files and folders to a convenient location on your computer, then upload all the files and folders to your new server/host.
Once you have all the files on your new server/host, you need to download a copy of your original WordPress database from your original host.
(All of the following screenshots were taken from cPanel, but if your host uses a different administration panel, the steps will be the same- they may just look slightly different.)
Once logged in to your administration panel, go to phpMyAdmin.
Once you click phpMyAdmin, in the left column click on the database that is used for your original WordPress blog.
You should now see the name of the WordPress database in the drop-down box and all the WordPress tables listed underneath. The tables you have can be different than mine because the WordPress plug-ins you use will determine what tables you have in your blog’s database.
Regardless, it should look something like this:
Now in the main window, click the tab at the top of the screen that says “Exportâ€. In the “Export†box, all of your WordPress database tables should be highlighted.
Underneath that, check the box that says “Save as file†and name it whatever you want, then click “Go†to download your blog’s database.
Click the “Save†button to save a copy of your WordPress database to a convenient location on your computer.
Congratulations! Now you’re done with your old server/host! It’s time to move on to the new server/host.
Login to your new server/host’s control panel. The first step is to create a new database that will be used for your new WordPress Blog.
Click on “MySQL Databasesâ€.
Under “Create New Databaseâ€, enter a name for your new WordPress database, then click the “Create Database†button.
Now that your new WordPress database has been created, we need to make a new user for that database. Usually on the same screen you’re at, you have the option to create a new user.
Enter a name and password for the WordPress database user and click the “Create User†button.
Now you have to give that user permission to use your blog’s database. Again, usually on the same screen you will have the option to add that user to the database. Select your WordPress database and the user you just created, then click the “Add†button.
On the next screen, you have the option to grant which permissions this user has to your blog’s database. Be sure to grant this user total access to the WordPress database by checking “All Privilegesâ€, then click the “Make Changes†button.
You should get a confirmation telling you that the user has been successfully added to the database.
Now that your new database has been created, we need to import your old WordPress blog’s database contents into your new database.
Back in the main window of your control panel, click on “phpMyAdminâ€.
In the drop-down box in the left column, select your new WordPress database that you’ve just created. It should say that there are no tables in the database yet, since it’s a blank database that you’ve just created.
In the main window, click the “Import†tab. Under “File To Importâ€, click the “Browse†button and select the .sql file that you downloaded from your old WordPress database, then click the “Go†button.
After waiting for the entire blog’s database to import, you should get a confirmation telling you that the tables have been successfully imported. Again, the number of queries that were executed may be different than mine, depending on which plug-ins you have installed.
Now in the left column, you should be able to see all the WordPress tables that were just imported.
If your new WordPress blog is using a different domain name than the original, we need to modify three values, and they are all located in the “options†table.
Even if you are using the same domain name, there is one option you may have to change. I would recommend you continue to follow along, even if your new WordPress blog is using the same domain name as the original blog.
All of the changes we need to make are located in the options table. My options table is named “wp1_optionsâ€, but yours may be a little different (probably “wp_optionsâ€), just as long as the suffix is “optionsâ€.
After you click on the options table, click the “Browse†tab at the top of the page.
One of the option names will be “siteurlâ€. You will see that your original domain name is listed. If your new WordPress blog is on a new domain name, you need to edit that value to your new domain name.
You can do so by clicking the “Edit†button, which looks like a pencil.
Once you click the “Edit†button, just enter your new blog’s URL and then click the “Go†button to save the changes.
The next two values we need to change are usually located on page 2 of the options. At the top and bottom of the main window, you should see the heading “Page number†with a drop-down box. Select page 2.
Scroll down until you see the “home†option. Again, you will see your old WordPress blog’s URL. Click the “Edit†button which looks like a pencil to modify the old blog’s URL.
After you click the “Edit†button, enter your new WordPress blog’s URL, then click the “Go†button to save the changes.
The third and final value we need to change is the upload path. This is the location where WordPress will upload all of your images.
Scroll down until you see the “upload_path†option, and again click the “Edit†button that looks like a pencil to modify this value.
Now you need to enter your new upload path. The only part of your new upload path that you need to modify is the username that your hosting company gave you for your domain name.
This is usually just after “/home/â€, and in my case is “jrivnetâ€. Replace this with your new username. This is usually the username you use to login to cPanel, or your domain’s control panel.
If you are still unsure as to what it is, take a look in the left column. Your username is the prefix of your WordPress database. Notice how mine is “jrivnetâ€.
Once you’re done with that, click the “Go†button to save.
WHEW! You’re finally done with all the database stuff! Your final step is to go to your wp-config.php file of your new WordPress blog. This is in the root folder of your WordPress blog (usually /home/ or /public_html/). You can do this with your FTP program or the file manager for your new WordPress blog through your hosting company.
Find the file “wp-config.php†and click “Editâ€.
Once the “wp-config.php†file is opened, the first things you will see are:
DB_NAME, DB_USER, and DB_PASSWORD.
Enter your new WordPress blog’s database name, username and password between the ‘ ‘. Look at the 13th image in this article above just below where I said “You should get a confirmation telling you that the user has been successfully added to the databaseâ€. That was my new WordPress database and username that I created. The 10th image in this article is where I created the user’s password.
This IS NOT your WordPress admin username and password! This is your database’s name and password which you just created for your new host/server.
Once you enter the new information, save the updated “wp-config.php†file.
You’re done!
Leave a Reply