As a Web Developer we sooner or later maybe required to work with a CMS (Content Management System), perhaps out of own interest or plainly being forced by a customer request.

The idea behind such tools is the try to separate the data from the presentation layer. Following this approach later on it will be easy to change the look and feel of your site just by changing or updating the template used, immediately all content pages will get shown in that brand new look without changing them one by one.

Setting up such a CMS is fairly easy, first by uploading all files that come with it to your server. We now need to put together your database details as they are the key to your central data store and specifying them together with a few other essential (but usually not difficult to answer) questions during a typical setup procedure. From then on it is just learning about and working with the tool ñspecify what tool, is it CMS(?) – to get your site ready to use.

Your site, usually developed on your own server, or within a subfolder of the original environment sooner or later you may find the need to move your installation.

In all cases the principle process is the same. Following the idea of a CMS, data can be either located within the file system, the database, or within both. So moving a CMS always requires moving both!

We have already given you a few tips here about how to move the files between servers quickly and therefore we will now focus on the database data only.

The main benefit of a database is that the data is stored within, in a very efficient way for accessing it quickly and in a structured way. Most databases come with some binary data export/import tools for transferring both data and structures.

Making a WordPress move and the usual MySQL database, phpMyAdmin is the tool of our choice. Mostly offered by the hosting provider itself it is often part of your hosting package and easy to access.

Once started it offers you a nice and neat surface interface enabling you to work with your database and it hopefully will not take you long to get familiar with it. With the main view opened and looking at your database the menu directly offers you the options export and import. Now choosing export we usually can go on with the offered defaults. But to prevent problems I would recommend you to ensure that the check boxes “Add DROP TABLE” and “Complete inserts” are checked and “Export type” is defined as “INSERT”.

With this set we can now press “Go” and the result will be a long and curious screen output containing both, the data and all necessary SQL (Structured Query Language) elements, later on it is necessary to re-import this data.

This output we will now cut & paste to an ASCII Editor of our choice and the reason for this is there maybe references pointing to structures of the old server we now have to change (now hopefully making them “relative”). Within the editor this can be done easily by using the usual search and replace function.

Importing the data back into the database or transferring it to another one, we now copy & paste our content back into phpMyAdmin and connect to the new target database. For the re-import I recommend the SQL functionality of the tool. There you can just paste your script into the big SQL input field and finally press the OK button below. And after a few seconds of processing, a friendly message will tell us that our data is stored back within the database.

Having ported both – data and files – we finally need to change a few runtime settings of the WordPress installation.

Once again you should be aware that we are working with two different locations; database and filesystem and you will understand tweaks will be required to both.

All the necessary information to access the database is stored within a file wp-config.php. We should remember that file as it was part of the initial installation we did earlier.

Now we open this configuration file within an editor of our choice and replace the relevant parts e.q. username, password, database, server and table prefix with the correct new values.

Finally and as a last step we have to head back to the phpMyAdmin and navigate down to a table called  wp_options. Browsing the content of this table we will see two option rows siteurl (option_id=1) and home (option_id=39) which contains the old site URL as an argument. Adjusting them with the new site URL will finally allow us to run the WordPress on our new Server without issues.

For security reason you should change the root password of your MySQL installation immediately! Unfortunately and even it is very easy many users leave it untouched though.

There are several way to do and a lot of MySQL tools will assist nad help you with that. But to give you a helping hand with your security we will share the most two common ways to do with you here.

First there is the possibility to directly change it on your server prompt by entering a simple command forcing the MySQL engine to ask for a root password further on:

To change your the MySQL root password to PaSsWoRd, use:

mysqladmin -u root password PaSsWoRd

Another way to succeed could be changing it by SQL statement:

grant all on database.* to username@’%’  identified by “password”;
grant all on database.* to username@’localhost’  identified by “password”;

With this given example you can do both, either create a new user or change the password for an already existing user.

Please take care about to substitute the given variable within the SQL statement for database, username and password with appropriate values though. As also be aware that based on MySQL is differencing between local and remote traffic you have to do for both sources!

Already set a password and later on, you have to use the following command:
mysql -u root -p and at command login prompt password:  “type in your password” to then go on executing the statements above.

Many webdevs are doing an excellent stuff and as we all know their webpages do impress us every day again and again. But neing experts one hand, at other topics, like doing necessary database backups, they often act like bloody beginners.

Often when it comes to database interactions using some CMS (Content Management System) they often neglect even the basics like doing backups frequently. But it can be that easy to have well ordered backups of your CMS’ underlaying database done automatically in an most convenient way. And in case you are in some ‘need’ – just restore your database backup and you are back in business again.

Sure you can code that backup all by yourself, but today we wll show you how easy the combination of just a few things can do all that backup stuff automatically for you. At least when it comes to MySQL databases which are widely common for all kind of web related database activities.

We first start with some free avalable PHP based backup tool in which we will define the backup to take, while later on we will use some easy to implement tricks to automise the backups taken for you.

The tool of our choise is called phpMyBackupPro which is available for free at phpMyBackupPro.net. (The tool itself is for free, but the author who has really made a great job offers to to donate some little money helping him to continue his excellent work.)

Installing the tool is easy done by extracting the archive and uploading it’s content to your webserver, but for sure it also works for you on your local system as well.

(enlarge view)
Once uploaded it can get directly invoke and we do start with some basic configuration questions. It isn’t much about to know or perhaps guessing it needs  and filling out at that page – you should anyway know that already.

From there we will head over the the more        (enlarge view)
interesting part of scheduling a backup. There we will now first select the databases to backup at the left side. Depending on your environment there might be just one, but in a more complex situation you and also choose more or all backing up them all together later on.

It’s not problem going with all other setting as they are defined by default, but feel free to play with it if wished. Even the dangerous appearing question about to add a ‘drop table’ command is fine and should be checked, as for it is referencing to a possible later restore of a database and in this case for sure a prior drop table instruction should be executed.

When we now press the button ‘show script’, as a result we will see some PHP code we can just copy&paste into some new file (lets call it cron.php) we later on will upload into the root folder of our phpMyBackupPro installation.

Depending on our basic installation invoking this script within any of the usual browsers will now generate us the wished database backup.

The result can then either get stored within some subfolder inside the tools’ folder, emailed to one of our email account or pretty much handy transfered via FTP to some remote server really creating an offline backup there.

Now calling this script agan and again out of your browser is not what I really would call automated at all. But honestly it does not need much more now.

Once we tested the script as working we do now just need some automatim calling it for us again and again.

This can be done by the OS itself when we’re going to help it a bit. What we now need is some line mode browser invoking the script. On UNIX platform this can be done by using either lynx or curl.

An easy and simple command line script will do:

• for lynx (which is pretty much common on RedHat systems) we use:

    /usr/bin/lynx -source http://www.yourdomain.com/phpMyBackupPro/cron.php > /dev/null 2>&1

•   and with curl (mostly used on Debian distributions) we use:

    /usr/bin/curl http://www.yourdomain.com/phpMyBackupPro/cron.php
   
So either one of these lines we will put into another new file (lets call it cron.sh and don’t forget about to proper ‘chmod’ it) which we then finally schedule out of the UNIX usual cron utility.

So with the command crontab -e we will invoke our crontab file and with adding there a line like:

0 0 * * * /home/ /cron.sh

the system will do an automated backup for us seven days a week around midnight.

For a higher frequence and or other specialties please refer into the separate cron documentation first.