Backing Up and Running Your Public WordPress Site Locally

As I am currently changing my website provider to Packet3.co.uk I had to backup and download the 4 sites zips to my local PC so I can upload to the new site once transfer details are complete.

This is an involved process but very interesting, as it combines running Apache2, MySql, and a neat Search/Replace tool and maybe PHPMYADMIN if you wish, to get you old databases running at home, but so they don't link to your old public site name.

The first thing you must do is back up each site you may be running on your web providers site.

WordPress has a Plugin perfect for this job called, er (now defunct 2016):

Install a similar Plugin at your site then run a backup for local download without encryption:

You may also be able to backup from your cPanel depending what your Hosting Co provides:

p3backup.png

buwiz.png

buprog.png

This zip / tar.gz file will contain the files and database for you to run WordPress on your local, non public Linux PC. There is a lot involved, so keep the original backup safe and copied elsewhere also.

I assume from this point you have followed prior Posts on running an Apache2 server on the Raspberry Pi, and have a functional local web server already, and you have pointed your /etc/apache2/sites-enabled/default file to wherever you will be storing your home site files.

Install MySql and WordPress onto your Pi also - follow the mysql pword prompts etc.

apt-get install mysql-server wordpress

Create a subdirectory of /var/www (or elsewhere to suit) of the name of you site e.g. mine is on my Storebird USB drive attached to the Pi:

mkdir /SB/www/DebianAdmin

Copy the backup zip to that directory and unzip it. It should have Database and Filesystem folders containing the site SQL DB, and the .php files of the site in them, amongst others, e.g:

root@raspberrypi: ls /SB/www/DebianAdmin

Database
FileSystem

If you ran from cPanel, then the file may be like:

ls ~/Downloads/
backup-8.20.2016_14-03-18_steveped.tar.gz

In which case you need to untar/unzip it with :

tar -xvf backup-8.20.2016_14-03-18_steveped.tar.gz

You need to recursively copy the original Filesystem folder contents to your current local site subdirectory. I recommend you become root and cd to the root directory, then use full path names to do this to avoid confusion e.g:

root@raspberrypi:/# cp -vr /SB/www/DebianAdmin/FileSystem/DebianAdmin/* /SB/www/DebianAdmin/

This will copy all the php files of the old site root Filesystem, which become the root docs under your new local site folder title and Database name.

Once the Filesystem files have been copied to your Doc Root equivalent of /SB/www/DebianAdmin you get similar to:

root@raspberrypi:/# ls /SB/www/DebianAdmin/

403.shtml FileSystem wp-blog-header.php wp-mail.php

cgi-bin MOJOWordpressInstaller-ZxxnnQ9euZ.state.json wp-content xmlrpc.php

Database readme.html wp-cron.php

The Database folder contains:

root@raspberrypi:/SB/www/DebianAdmin# ls Database/

OBFW_Database.sql

This can now be imported into MySQL after you create a database of the site name. To get into the mysql cmd line mode type:

mysql -u root -p

Enter password:

Give root's pword and show the currently created databases - note the SQL language semicolon!

mysql> show databases ;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| phpmyadmin |

| wordpress |

+--------------------+

8 rows in set (0.04 sec)

If you mess up, use Ctrl+C to get back to linux.

Create your new database and USE it before you import the sql database file:

mysql> create database DebianAdmin ;

Query OK, 1 row affected (0.00 sec)

mysql> use DebianAdmin ;

Database changed

You need to allow access to the DBs for a user or root as you wish e.g across ALL DBs for root

grant all privileges on *.* to 'root'@'localhost' ;

If running the searchdb page remotely (below) you will also need to allow your local user access also so you can browser view the searchdb remotely and run the changes:

grant all privileges on *.* to 'stevee'@'localhost' ;

Now, import the database using the absolute path name, from the Database subdirectory you unzipped. I use a 2nd window to list then copy the path from, to not exit MySQL in the other window:

mysql> source /SB/www/DebianAdmin/Database/OBFW_Database.sql

.....

Query OK, 1 row affected (0.01 sec)

mysql>

Assuming you have Apache2 set right by adding the specific sub directory as an Alias inside the virtual host block (if you don't want to alter your current /var/www settings) in

vi /etc/apache2/sites-available/000-default.conf

Alias /da /var/www/DebianAdmin
<Directory /var/www/DebianAdmin>

Options All
AllowOverride All

Require all granted

</Directory>

you should be able to browse to your webserver directory for the site and get a desired fault:

This is good! It means the database is seen but not read properly because of incorrect content at its new place.

The first file to check for is that there is an .htaccess file in the DebianAdmin directory and if not it will have to be created:

root@raspberrypi:/SB/www/DebianAdmin# ls .ht [TAB]

If repeated tabs don't see it, then create one with:

vi .htaccess

{UPDATE 2016}


https://httpd.apache.org/docs/current/howto/htaccess.html

When (not) to use .htaccess files

In general, you should only use .htaccess files when you don't have access to the main server configuration file. There is, for example, a common misconception that user authentication should always be done in .htaccess files, and, in more recent years, another misconception that mod_rewrite directives must go in .htaccessfiles. This is simply not the case. You can put user authentication configurations in the main server configuration, and this is, in fact, the preferred way to do things. Likewise, mod_rewrite directives work better, in many respects, in the main server configuration.

.htaccess files should be used in a case where the content providers need to make configuration changes to the server on a per-directory basis, but do not have root access on the server system. In the event that the server administrator is not willing to make frequent configuration changes, it might be desirable to permit individual users to make these changes in .htaccess files for themselves. This is particularly true, for example, in cases where ISPs are hosting multiple user sites on a single machine, and want their users to be able to alter their configuration.

However, in general, use of .htaccess files should be avoided when possible. Any configuration that you would consider putting in a .htaccess file, can just as effectively be made in a <Directory> section in your main server configuration file.

There are two main reasons to avoid the use of .htaccess files.

The first of these is performance. When AllowOverride is set to allow the use of .htaccess files, httpd will look in every directory for .htaccess files. Thus, permitting .htaccess files causes a performance hit, whether or not you actually even use them! Also, the .htaccess file is loaded every time a document is requested.


If you DO need an .htaccess file, paste this text below in it, with your folder details in place of DebianAdmin:

# BEGIN WordPress

<IfModule mod_rewrite.c>

RewriteEngine On

RewriteBase /DebianAdmin/

RewriteRule ^index\.php$ - [L]

RewriteCond %{REQUEST_FILENAME} !-f

RewriteCond %{REQUEST_FILENAME} !-d

RewriteRule . /DebianAdmin/index.php [L]

</IfModule>

# END WordPress

CD to the site folder, check the contents of

vi wp-config.php

From lines 17 on you should see data from the old site that needs to be changed for your local server - the servername, user=root, and root's password. Change them to suit, and don't miss an apostraphe:

// ** MySQL settings - You can get this info from your web host ** //

/** The name of the database for WordPress */

define('DB_NAME', 'DebianAdmin');

/** MySQL database username */

define('DB_USER', 'root');

/** MySQL database password */

define('DB_PASSWORD', 'pw****');

/** MySQL hostname */

define('DB_HOST', 'localhost');

You shouldn't have to change anything else in there like the hash salts or DB prefix section.

Now, what should happen when you restart Apache, is you get your site page ok, but all the Post links etc. will still point to your old site prefix - stevepedwards.com - in my case. These need to be changed to point to your home server name - and of course, there are maybe thousands of them in the whole site DB! So how?

Now for the fun part...if you mess it up...start over probably!!

Download the SearchReplace DB tool from:

https://interconnectit.com/products/search-and-replace-for-wordpress-databases/

Read all instructions before installing the tool in the same way as you did for the mysql steps above e.g:

root@raspberrypi:/SB/www/searchdb# ls

Search-Replace-DB-master.zip

root@raspberrypi:/SB/www/searchdb# unzip Search-Replace-DB-master.zip

inflating: Search-Replace-DB-master/tests/charset-test.php....

inflating: Search-Replace-DB-master/tests/db.sql

The download Search-Replace-DB-master.zip has a SQL DB in it also, so create a MYSQL DB of a suitable name and import it as above.

mysql> source /SB/www/searchdb/Search-Replace-DB-master/tests/db.sql ;

When installed, restart mysql:

root@raspberrypi:/SB/www/searchdb# /etc/init.d/mysql restart

[ ok ] Stopping MySQL database server: mysqld.

[ ok ] Starting MySQL database server: mysqld . . ..

[info] Checking for tables which need an upgrade, are corrupt or were

not closed cleanly..

You can browse to the filesystem on your local server if you have no index.html file in the apache2 root folder, and run the browser app by opening the searchdb folder:

Now, replace all instances of you old site name - don't forget the www! - with your local server name so the links stay local - update the details, then do a dry run first to see if it should work.

If you are happy it completes properly, then do a live run. Your local site should now work so all Posts are called locally, not trying to access the web. As you see, my recent Post is now read locally off the Pi.

If you get an incorrect view of the main page, then it can be your Permalinks that you may have set to Post Name on the real site needs changing back in Settings/Permalinks to the default as below.

permalinks.png

If you can't get anywhere with that App and happy on the command line, use sed to search and replace the entire DB in real time (change in place -i) - after making a copy of course:

sed -i 's/www.stevepedwards.com/hpmint/g' Downloads/backup-9.6.2016_19-37-11_steveped/mysql/steveped_debianadmin.sql

Now import it into mysql using the source command as above, to overwrite the old version. All links should now be local if you searched/replaced correctly.

What a neat App! I don't see the need to delete it after, if on your own firewalled, non public home webserver, but that's up to you. At least when learning this process anyway.

A big Thanks to Joe Moore from Packet3.co.uk for his expertise and patience in showing me this process.

----------------------------------------------------------------------------------------------------------------------------------

If you need to change your password for the local site because you change the site name root folder name as I did, and possibly the hashes don’t work any more, and as email from your server is not available to the local WordPress so you can’t use the “send new password” function, and if you don't have PHPMYADMIN installed, you can use MySQL and its own MD5 hash method in the MySQL ver 5 step 2 below.

MySQL method:

  1. mysql -u root -p” (log in to MySQL)
  2. enter your mysql password
  3. use (name-of-database)” (select WordPress database)
  4. show tables;” (you’re looking for a table name with “users” at the end)
  5. SELECT ID, user_login, user_pass FROM (name-of-table-you-found);” (this gives you an idea of what’s going on inside)
  6. UPDATE (name-of-table-you-found) SET user_pass="(MD5-string-you-made)" WHERE ID = (id#-of-account-you-are-reseting-password-for);” (actually changes the password)
  7. SELECT ID, user_login, user_pass FROM (name-of-table-you-found);” (confirm that it was changed)
  8. (type Control-D, to exit mysql client)

Note if you have a recent version of MySQL (version 5.x?) you can have MySQL compute the MD5 hash for you.

  1. Skip step 1. above.
  2. Do the following for step 7. instead.
    • UPDATE (name-of-table-you-found) SET user_pass = MD5('"(new-password)"') WHERE ID = (id#-of-account-you-are-reseting-password-for);” (actually changes the password)

Note that even if the passwords are salted, meaning they look like$P$BLDJMdyBwegaCLE0GeDiGtC/mqXLzB0, you can still replace the password with an MD5 hash, and WordPress will let you log in.

Using your site name DB still find the row name that contains your DB users names – “wp_users” in my case:

mysql> SELECT ID, user_login, user_pass FROM (wp_users);

+—-+————————-+——————————————-+
| ID | user_login | user_pass |
+—-+————————-+——————————————-+
| 1 | stevepe1 | *0AAD10E48295AA608A67B611C0477524B00BA770 | non working pword hash
| 2 | stevepedwards | *0AAD10E48295AA608A67B611C0477524B00BA770 | non working pword hash
+—-+————————-+——————————————-+

mysql> UPDATE (wp_users) SET user_pass=MD5(“password“) WHERE ID = 2 ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Now check the password hash value has changed:

mysql> SELECT ID, user_login, user_pass FROM (wp_users);
+—-+————————-+——————————————-+
| ID | user_login | user_pass |
+—-+————————-+——————————————-+
| 1 | stevepe1 | 4e4a33663988295b02fb247dcb7952da |
| 2 | stevepedwards | 5f4dcc3b5aa765d61d8327deb882cf99 | new working hash value for above pword = “password
+—-+————————-+——————————————-+
2 rows in set (0.00 sec)

Now check the new password = “password” works at the site login page /DebianAdmin/wp-admin/ for that user. Just to be sure once you get into WordPress, create a new user also with Admin privileges.

So how is it possible to generate a command line hash value in Linux?

Use the md5sum binary with a word of your choice piped into it:

root@debianP4:~# echo -n password | md5sum

5f4dcc3b5aa765d61d8327deb882cf99  -

As you see, it generates exactly the same hash value as the PHPMYADMIN and MySQL methods before.

If you want to check the MD5 for a file:

md5sum /Storebird/MintDownloads/pmagic_2015_07_07.iso
f9498543e0c9597a925be8bae9b42747 /Storebird/MintDownloads/pmagic_2015_07_07.iso

In Linux Mint, should you get a webpage stating:

Your PHP installation appears to be missing the MySQL extension which is required by WordPress.

Try installing php5, with php5-mysql:

apt-get install php5 php5-mysl

ALSO: If you find you cannot access your Posts locally from the main page, you may have changed the permalink defaults to names etc. Change them back to default number style, and it should work:

Perma.jpg

The home WordPress password change process below was explained on a forum and goes like this, assuming you do not have PHPMYADMIN installed in Linux already via:

apt-get install phpmyadmin

First, browse to that local site folder to see the web admin page and log in as root:

Click the DB you want from the left and find the table with the user data - wp_users in my case:

So, the first thing to note is the hash value for each user and how to check it against the MySQL values:

stevepe1 $P$B1TlEUwBl6jO/YfQnnhfpWO5ZmtA.K1

Now look at the MySQL value:

mysql> SELECT ID, user_login, user_pass FROM (wp_users);
+----+-------------------------+------------------------------------+
| ID | user_login              | user_pass                          |
+----+-------------------------+------------------------------------+
|  1 | stevepe1                | $P$B1TlEUwBl6jO/YfQnnhfpWO5ZmtA.K1 |
|  2 | stevepedwards@gmail.com | $P$B6RhdWxw35zLD8Qb9b8SwL08SBLnEl. |
+----+-------------------------+------------------------------------+
2 rows in set (0.00 sec)

Identical of course, but let’s see how to change the password using that forum method of selecting MD5 hash from the user_pass drop menu and typing in a new word = “mypassword”:

Scroll down and click Go:

As you may have noticed, the actual MySQL command is shown in the summary box, which is similar to what you use to do this manually at the MySQL command line itself if you have already logged in to MySQL and set the DB with the USE command:

UPDATE (wp_users) SET user_pass=MD5("mypassword") WHERE ID = 1 ;

Now check its hash value in the wp_users window = 34819d7beeabb9260a5c854bc85b3e44:

Now in MYPHPADMIN again with a refresh: 34819d7beeabb9260a5c854bc85b3e44

This reads the same as PHPMYADMIN just entered - same so far as you would expect.

Now, you can compare that to the forum MySQL method below and choose which you prefer.

If you need to change your password for the local site because you change the site name root folder name as I did, so the hashes don't work any more, and as email from your server is not available to the local WordPress so you can't use the "send new password" function, you can use MySQL and its own MD5 hash method in the MySQL ver 5 step 2 below.

MySQL method:

  • "mysql -u root -p" (log in to MySQL)
  • enter your mysql password
  • "use (name-of-database)" (select WordPress database)
  • "show tables;" (you're looking for a table name with "users" at the end)
  • "SELECT ID, user_login, user_pass FROM (name-of-table-you-found);" (this gives you an idea of what's going on inside)
  • "UPDATE (name-of-table-you-found) SET user_pass="(MD5-string-you-made)" WHERE ID = (id#-of-account-you-are-reseting-password-for);" (actually changes the password)
  • "SELECT ID, user_login, user_pass FROM (name-of-table-you-found);" (confirm that it was changed)
  • (type Control-D, to exit mysql client)

Note if you have a recent version of MySQL (version 5.x?) you can have MySQL compute the MD5 hash for you.

  • Skip step 1. above.
  • Do the following for step 7. instead.
  • "UPDATE (name-of-table-you-found) SET user_pass = MD5('"(new-password)"') WHERE ID = (id#-of-account-you-are-reseting-password-for);" (actually changes the password)

Note that even if the passwords are salted, meaning they look like $P$BLDJMdyBwegaCLE0GeDiGtC/mqXLzB0, you can still replace the password with an MD5 hash, and WordPress will let you log in.

Using your site name DB still find the row name that contains your DB users names - "wp_users" in my case:

mysql> SELECT ID, user_login, user_pass FROM (wp_users);

+----+-------------------------+-------------------------------------------+

| ID | user_login | user_pass |

+----+-------------------------+-------------------------------------------+

| 1 | stevepe1 | *0AAD10E48295AA608A67B611C0477524B00BA770 | current pword hash

| 2 | stevepedwards | *0AAD10E48295AA608A67B611C0477524B00BA770 | current pword hash

+----+-------------------------+-------------------------------------------+

mysql> UPDATE (wp_users) SET user_pass=MD5("mypassword") WHERE ID = 2 ;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Now check the password hash value has changed:

mysql> SELECT ID, user_login, user_pass FROM (wp_users);

+----+-------------------------+-------------------------------------------+

| ID | user_login | user_pass |

+----+-------------------------+-------------------------------------------+

| 1 | stevepe1 | 4e4a33663988295b02fb247dcb7952da |

| 2 | stevepedwards | 34819d7beeabb9260a5c854bc85b3e44 | new hash value for above pword = "mypassword"

+----+-------------------------+-------------------------------------------+

2 rows in set (0.00 sec)

Now check the new password = "mypassword" works at the site login page /DebianAdmin/wp-admin/ for that user. Just to be sure once you get into WordPress, create a new user also with Admin privileges.

So how is it possible to generate a hash value in Linux?

Use the md5sum binary with a word of your choice piped into it:

root@debianP4:~# echo -n mypassword | md5sum

34819d7beeabb9260a5c854bc85b3e44 -

As you see, it generates exactly the same hash value as the PHPMYADMIN and MySQL methods before.

However, this is not the same as reading that string once written inside a file, because as soon as text is entered into a file it is appended by an invisible delimiter and its visibility depends on what binary or switch is used to view the file contents.

For example, vi a new file of any name, and and type the text mypassword inside:

vi test.txt

mypassword

Now cat it to see the contents:

cat test.txt

mypassword

From above, the pure text MD5 value of “mypassword” is 34819d7beeabb9260a5c854bc85b3e44

but the value of the piped file is different:

root@debianP4:~# cat test.txt | md5sum

d84c7934a7a786d26da3d34d5f7c6c86

Why is that? Where is the extra data coming from to change the hash? It is not the file name adding because you can change the filename at any point and still get the same hash.

Vi adds a whitespace character at the end where the cursor is flashing also, which echo does not when the -n switch is used, from the man echo page:

-n     do not output the trailing newline

So again, creating the two different files, whose contents looks the same:

stevee@MintServer ~/Documents $ echo -n mypassword > mypw.txt

stevee@MintServer ~/Documents $ echo mypassword > mypw2.txt

stevee@MintServer ~/Documents $ cat mypw.txt mypw2.txt

mypasswordmypassword

The contents of each looks the same, but these two files give the different md5 sums seen above:

stevee@MintServer ~/Documents $ cat mypw.txt | md5sum

34819d7beeabb9260a5c854bc85b3e44  -

stevee@MintServer ~/Documents $ cat mypw2.txt | md5sum

d84c7934a7a786d26da3d34d5f7c6c86  -

Echo without the - n switch gives the same value as creating the file with vi.

od shows the octal characters that exist inside each, so beware hidden chars and assumptions about using echo or other progs for hashing what is output on the screen to what may really exist.

stevee@MintServer ~/Documents $ man od

-c     same as -t c,  select ASCII characters or backslash escapes

stevee@MintServer ~/Documents $ od -c mypw.txt

0000000   m   y   p   a   s   s   w   o   r   d

0000012

stevee@MintServer ~/Documents $ od -c mypw2.txt

0000000   m   y   p   a   s   s   w   o   r   d  \n

0000013

Clear now that a Newline char is present in the first, and a Vertical Tab in the second according to a chart converter page here:

http://www.ascii-code.com/

DECOCTHEXBINSymbolHTML NumberHTML NameDescription
00000000000000NUL&#000;Null char
10010100000001SOH&#001;Start of Heading
20020200000010STX&#002;Start of Text
30030300000011ETX&#003;End of Text
40040400000100EOT&#004;End of Transmission
50050500000101ENQ&#005;Enquiry
60060600000110ACK&#006;Acknowledgment
70070700000111BEL&#007;Bell
80100800001000BS&#008;Back Space
90110900001001HT&#009;Horizontal Tab
100120A00001010LF&#010;Line Feed
110130B00001011VT&#011;Vertical Tab

You could use awk or sed to remove any trailing chars from an output, then md5sum them to check it works, by giving the same output for a given file or echo output.

Comments are closed.

Post Navigation