Moving Wordpress to new domain & change database prefix

Ahad, 16 Ogos 2020, 10:40 am0

Situation:

You have an existing WordPress blog (example.com) and you want to move it to a new domain (example.org) and change the database prefix (wp_ to xp_).

Preparation:

Backup your blog files and database in the current web hosting, then download them to your computer.

Modify backup database file:

I’m using Sublime Text to edit this SQL file.

• Comment out DROP IF EXISTS; (become -- DROP IF EXISTS;)
  Replace wp_ to xp_

  Then test out this modified SQL file in your localhost, to ensure there’s no SQL error.

  Modify backup website files:

  • wp-config.php – change MySQL settings according to the new hosting
  • wp-config.php – change $table_prefix to xp_.

  Upload website files to new web hosting:

  I’m using FileZilla to perform FTP file uploads here.

  Import modified backup database file:

  Open phpMyAdmin provided by the hosting if available, then go to Import, then drag & drop the SQL file there.

  Change values in database through phpMyAdmin:

  • xp_options – change siteurl and home to new url (http://example.org)
  • xp_users – change admin user’s user_url to new url (http://example.org)

  Changing values in tables below is important if you’re changing database table prefix. If not, you’re going to see this error message: “Sorry, you are not allowed to access this page”, even when you’re successfully logged in as admin.

  • xp_options – find option_name starts with wp_ and replace them with xp_
  • xp_usermeta – find meta_key starts with wp_ and replace them with xp_

  Change URL domain in database tables.

  These are the SQL that I used to change the URL values, you need to adjust accordingly.

  • UPDATE xp_comments SET comment_author_url = REPLACE(comment_author_url, 'http://example.com', 'http://example.org') WHERE comment_type = 'pingback'
  • UPDATE xp_posts SET post_content = REPLACE(post_content, 'http://example.com', 'http://example.org') WHERE post_type IN ('post', 'page')

  If you’re using Yoast SEO plugin, you may need to change this table too:

  • UPDATE xp_yoast_indexable SET permalink = REPLACE(permalink, 'http://example.com', 'http://example.org'), twitter_image = REPLACE(twitter_image, 'http://example.com', 'http://example.org'), open_graph_image = REPLACE(open_graph_image, 'http://example.com', 'http://example.org'), open_graph_image_meta = REPLACE(open_graph_image_meta, 'http://example.com', 'http://example.org')

  Change URL domain in PHP-serialized values in database tables.

  • UPDATE xp_options SET option_value = REPLACE(option_value, 'http://example.com', 'http://example.org') WHERE option_value LIKE '%example.com%' (you have to exclude option_name that starts with _transient)
  • UPDATE xp_postmeta SET meta_value = REPLACE(meta_value, 'http://example.com', 'http://example.org') WHERE meta_value LIKE '%example.com%'
  • UPDATE xp_usermeta SET meta_value = REPLACE(meta_value, 'http://example.com', 'http://example.org')

  Next step is required for tables listed above, if your previous and new domain lengths are different. In my example here, both are of the same length: strlen('http://example.com') == strlen('http://example.org'), but my actual domains are different.

  Check if the PHP-serialized values are valid. You can check it using code below:

  $s = '';
echo unserialize($s) ? 'Success' : 'Failed';

  Or, you can use the exact script that I used, here: https://gist.github.com/azwan082/2b1a20b9b293b1937e6e6dc6849959c5

  If you found serialized values that are not valid:

  • First find where the new domain exist inside the string. It should look something like this: s:88:"https://example.org/wp-content/plugins/yet-another-related-posts-plugin/images/default.png";
  • Check the length of the string: strlen('https://example.org/wp-content/plugins/yet-another-related-posts-plugin/images/default.png') == 90. In my script above, I’ve provided textfield to check for string length.
  • Fix the length number: s:90:"https://example.org/wp-content/plugins/yet-another-related-posts-plugin/images/default.png";

  Done .

  #wordpress

  Ogos 2020
  ShareTweetSaveSendCopy
  Buat jalan depan rumah kebun DIY, minggu 3

  17 Ogos 2020

  Harga petrol minggu ke-3 Ogos 2020

  15 Ogos 2020

  Artikel berkaitan:

  

  Squid 2 cache configuration 7 Disember 2010

  

  How to implement MySQL database migration feature 30 Jun 2013

  

  ORM or SQL query 22 Ogos 2011

  

  Sutera Maya 21 Ogos 2009

  

  Using database in Windows Phone app 11 Mei 2014

  

  PHP Templating 2 September 2011

  

  Kpop Idol Groups International Fan Forum 25 Mei 2013

  

  Designing web page for Android webview 6 November 2012

  

  Manjalara 28 Julai 2009

  

  PostgreSQL on Fedora 19 Februari 2010

  Komentar (0):

  Tulis komen:

  Komen

  Nama

  E-mel

  Hantar