Moving Wordpress to new domain & change database prefix


Ahad, 16 Ogos 2020, 10:40 am0


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


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:

    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, '', '') WHERE comment_type = 'pingback'
    • UPDATE xp_posts SET post_content = REPLACE(post_content, '', '') 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, '', ''), twitter_image = REPLACE(twitter_image, '', ''), open_graph_image = REPLACE(open_graph_image, '', ''), open_graph_image_meta = REPLACE(open_graph_image_meta, '', '')

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

    • UPDATE xp_options SET option_value = REPLACE(option_value, '', '') WHERE option_value LIKE '' (you have to exclude option_name that starts with _transient)
    • UPDATE xp_postmeta SET meta_value = REPLACE(meta_value, '', '') WHERE meta_value LIKE ''
    • UPDATE xp_usermeta SET meta_value = REPLACE(meta_value, '', '')

    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('') == strlen(''), 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:

    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:"";
    • Check the length of the string: strlen('') == 90. In my script above, I’ve provided textfield to check for string length.
    • Fix the length number: s:90:"";

    Done 🤞🏻.

    Komentar (0):

    Tulis komen: