Automatic Database backups using free Sypex Dumper tool

Photo credit – williamhook2631871046

Automatic database backups can be set up very easily using a simple shell script and a cron job. However it may not be a practical solution for huge databases and the restoration process can also be difficult. There are various tools available to make this process simple. Sypex Dumper is just one of them.

What is Sypex Dumper

Sypex Dumper is a software product (PHP-script), which can help you create a backup copy (dump, export) of a MySQL database, and also restore the database from the backup file if needed. Read more and download

With this tool huge databases can be backed up and restored with very high speed using least server resources and greatly reducing the size of the database dumps.

Free version of the tool is enough to create the automatic backups. The Paid version allows to selectively restore a particular table from the entire database.

Steps to set up automatic Database Backups

Let’s assume you want to create backup of your database every day and keep the recent 30 backups on your server

Create the required job in SXD

1. Login to Sypex Dumper with your database user credentials

Setup automatic database backups using Sypex Dumper

2. Click on the Export option

3. Select the database from the Database drop down

4. Since we want to keep only the last 30 database backups add 30 in the Autodelete If number of files more than box.

5. Add some comments e.g. Last 30 backups

6. Clicking the Save button will create the backup job with the name specified

Create the shell script to execute the job

Now that the job is created we want to execute it using a shell script. Here is a sample script

Automate the script

Finally just add the above script to a cron job so that it runs once daily

 

 

MySQL archive records based on date column

Photo credit: 27892629@N04 - cc
Photo credit: 27892629@N04cc

Let’s assume you have a logs table and you want to delete the logs which are more than 1 year old.

Ideally you would like to automate this using a cron job.

MySQL Between query

Using the above query we can delete all the logs for the year 2014. However we cannot automate this query since we are providing the dates manually.

MySQL DATE_SUB query

Above query deletes all the records which are older than a year. Here we do not need to provide dates. It automatically finds the records which are older than a year using NOW and INTERVAL parameters.

So let’s say you want to delete all the records which are 6 months old then the query would be

Now lets automate the process of archiving our logs table

This can be done through a number of ways.

Shell Script

A shell script can be created with above code. The script can then be added to a cron job.

MySQL event scheduler

Read more

PHP Script

Create a PHP page to run the query and then create a cron job for the PHP page

A php script is the most recommeded system in this case because deleting records does not need lot of memory and it is easier to manage the PHP Script.

WordPress XMLRPC attacks – How to prevent

Wordpress XMLRPC

What is wordpress XMLRPC

RPC stands for Remote Procedure Call. WordPress XMLRPC is a protocol which allows remote systems to communicate with WordPress. The language to communicate is XML.

With WordPress XMLRPC support, you can post to your WordPress blog using many popular Weblog Clients.

XML-RPC functionality is turned on by default since WordPress 3.5.

Brute Force Attack through wordpress XMLRPC

Attackers user system.multicall method in XML-RPC to create hundreds of request combined in a single request to attack a system i.e. mostly to guess the username and password to the system. This is called as Brute Force Amplification Attacks  via WordPress XML-RPC

How to prevent XMLRPC attack

  1. The most recommended way is to disable XML-RPC completely. To disable XML-RPC completely add following to your APACHE configuration file.
  1. Some plugins in wordpress e.g. Jetpack is based on XML-RPC. In that case it is not possible to disable XML-RPC entirely. In that case you can disable system.multicall requests through your firewall
  2. Check server logs regularly and find IPs trying to access XML-RPC. Any suspicious IP can be blocked through your Firewall or iptables. Here are some sample logs

     

When to use Database Triggers ?

What are Database Triggers ?

A Database trigger is an SQL code which is made to run just before or after a certain event. That event could be an INSERT, UPDATE or a DELETE query on a particular database table.

Thus a trigger is used to automate some of the events on your server/site/application.

Examples of some Database triggers

  1. Sync user details from one table to another when a user updates them
  2. Geocode users location and store them in a separate table
  3. Maintaing log of certain events e.g. a product addition, updation or deletion (In this case we wish to know who did the change)

When to use Database triggers

There are few pros and cons about using database triggers.

Pros:

  1. Yes they can automate quite a lot of activities
  2. For things like maintaining logs if you are doing this through your code then most probably you need to add the piece of code in a number of files. e.g. If you are maintaing a log about article updates then there may be a number of files in your code related to updating articles. You will need to add the log related code in every file. But if this is done through trigger then you do not need to worry about updating your code.
  3. In the above example if the log table structure changes then you just need to update your trigger code and you are done.
  4. Triggers are very handy in case you have completely different systems on different platforms and coding is not really possible. This is mostly the case in large organisations.

Cons:

  1. Since the trigger is not part of your normal code it may create lot of confusion later as to how certain things are happening especially if you are new to the system and things are not properly documented. Due to this lot of people prefer to write event driven procudures in their code instead of creating triggers as that way they are in full control of the application.
  2. If the business logic changes then the triggers can be difficult to handle/update. At that point of time it may happen that a certain event may not be possible through a trigger and you may need to revert back to your usual way to handling events through your code. This may further increase development time.

Conclusion

Based on the above pros and cons we can understand that using a trigger or not is not really a straightforward decision. It really depends on case by case basis.

  1. If you are coding in an object oriented manner then most probably you are writing all your events in structured manner and there is no duplication of code. In that case it becomes quite easy to add the event based procedures within your code.
  2. However in some cases the code is not object oriented as it may be quite an old application. In that case you can make a judgement call whether its the time to update the code or create triggers.
  3. In case 2 if the code is done by some other developer then it is likely that you would use triggers.
  4. For applications built on different platforms or different scripting languages sharing common resources triggers can be handy to achieve some form of integration.

 

 

 

WordPress REST API v2 Examples

Here are a few examples on how to use WordPress REST API v2

First of all Download and Install the plugin just like any other wordpress plugin

Get list of posts

Get list of pages

WordPress REST API for Custom Posts

Prerequisite: REST API support needs to be added to custom posts while registering the custom post.

Below parameters add the necessary support. For detailed instruction please refer Adding REST API support to Custom Post Types

Suppose we have a Custom Post Type news, below parameters would add the necessary REST support to the Custom Post Type

show_in_rest

This allows the Custom post type to be accessed through the REST API

rest_base => Optional Parameter

This allows to change the REST API route. For e.g. if the custom post type is news. We can define a custom route to access the books post using rest_base parameter e.g. news_api

rest_controller_class

This is only required to be changed if you are using a custom namespace i.e. other than wp/v2

Once the necessary support is added as shown above the Custom Post Type is ready to be accessed through the REST API

Add REST support for custom taxomonies

Add below parameters to the custom taxomony newstags

Here are a few examples for a news custom post type

Get list all posts for the custom post type news

Get first 5 posts for the custom post type news

Get list all posts for the custom post type news from a certain category

Get a single post from the custom post type news

Get posts from the custom post type news which has custom taxonomy newstags applied to it

Modify JSON Response

WordPress Rest API provides us a filter rest_prepare_{POST_NAME} to modify JSON Response i.e. to add or remove fields from the JSON response. JSON response by default include many fields which may not be necessary for you. Also it may be missing some of your custom fields.

Add a custom field

 

Create a WordPress staging site through shell script

wordpress staging
Photo credit – 132889348@N0722868800432

Creating a wordpress staging environment requires 4 things. Here we are assuming that the staging environment is on the same server.

Requirements for creating a wordpress staging environement

  1. Clone Database – Each Time
  2. Copy the code – Only the wp-content folder
  3. Edit wp-config to point to the Staging Database (single site only) plus the Staging Domain (multisite) – Once Only
  4. Update the wp-options table with the Staging Domain (single site), Update the wp_blogs, wp_site, wp_options, wp_1_options, etc tables with the Staging Domain (multisite) – Each Time

So how to to achive creating a wordpress staging environement with a single script? Here are the steps.

Step 1: Create staging.sh and define variables

Create a file named staging.sh inside a directory named staging (preferably outside your public_html folder)

Define all the variables related to your production and staging database connection as shown below

Step 2: MySQL Dump of the Production database

Add below code to staging.sh file to get MySQL Dump of the current production database

Step 3: Export the Database to the Staging database

Again add below code to staging.sh. Here we are connecting to staging database and importing the sql file created in Step 2

Step 4: Batch File

Now create a new file within the staging directory named as batch.txt. Inside this batch file we will write all the queries we want to run to rename the production instances within the staging database to refer to the staging environment. The batch file will hugely depend on the type of site setup you have i.e. single site or multisite

If you have a single site then its very easy. Add below query to the file. Remember to replace PROD_DOMAIN and STAG_DOMAIN with the respective names. e.g. www.myproddomain.com

For mulsite it is bit complicated

Now just repeat the last time changing the number in wp_1_options to the number of the subsites

Step 5: Run the Batch file queries

To run the batch file queries add below code to staging.sh file

Step 6: Copy WordPress files

This can be done by a simple cp (copy command).

Step 7: Edit wp-config on the Staging

Now the last thing is to edit the wp-config file to point to the staging database. Thats all.

Finally just running the staging.sh script through SSH will set up the staging server.

Step 8: Weekly Cron Job

To refresh/update the staging server every week add cp command to staging.sh file and this time just copy the wp-content folder. You could also copy differences i.e. last changes only using rsync command.

Then set up a cron job to run staging.sh to run every week

 

WordPress plugin – Create an explore topics page for your site/blog

Explore topics

Tags can be used quite effectively to show various topics you have covered on your site/blog.

Here is a plugin which lets you create an alphabetical listing of tags with search as you type filter as shown below

This plugin automatically lays all your tags in alphabetical order on a page and adds an ajax filter input box which allows to search as you type for a particular topic/tag.

Alphabetical listing of tags

Download the plugin from WordPress Plugin Repository

Features

  1. Search for topics as you type,
  2. Supports Multisite,
  3. Show your users the vast range of topics covered on your site,
  4. Help in visitor retention and reduce Bounce Rate,
  5. Simple and easy to configure

 

How to selectively dequeue a script on individual post

Lot of plugins. Not sure what to do??

All of a sudden you may realise that you already have quite a lot of plugins on your website and probably some of those plugins are slowing your site down. That’s the point of time you may decide to review your plugins but at that point it is very difficult to imagine the impact of deactivating a certain plugin.

Here is a simple trick we can use to review our plugins and make sure atleast our important pages do not break.

Idea

The idea is to try and dequeue or remove a script on a page by page basis or post by post basic. Using this approach we are not deactivating the plugin on the entire site but we are just deactivating or removing it on just a single post or page. This way we can study the impact of removing the plugin on our most important pages/posts just so that we are sure it won’t break our site and we feel more comfortable to deactivate the plugin entirely on our site.

So how do we achieve this

Step 1: Print all the script handles used on a page/post

Step 2: Create a custom field

Create a custom field named wpi_selective_scripts_dequeue on our post/page and add bar separated (|) handles of the scripts which you wish to remove from the page (referring to list generated in step 1)

Step 3: Dequeue the script for that page/post

 

 

 

 

Difference between wp_print_scripts, wp_enqueue_scripts and wp_register_scripts

I had lot of confusion earlier while enqueuing or dequeuing a script in wordpress. I was not sure which function to use and would just keep on using various functions on a trail and error basis.

Hope below points would help to clarify atleast a few things with regards to when we should use a particular function

wp_register_scripts

This just registers the script but does not actually queue it for execution

wp_enqueue_scripts

This is used to enqueue a script which is required to be executed on our template. A script can be enqueued without registering it i.e. without using wp_register_scripts.

wp_print_scripts

This is an action which prints/outputs/shows up the scripts on our template for execution

When to use wp_register_script

You may be wondering why would I use wp_register_scripts and why not just directly use wp_enqueue_scripts

The answer to that is if you are developing a custom plugin and wish to execute a script only on certain pages then it helps to just register the script and then programatically enqueue it on the required pages.

 

How to add custom checkout fields to WooCommerce order

Follow below steps to add custom checkout fields to WooCommerce.

Scenario: We want to create a new address field named as territory. It will be a drop down field and will be used to decide the shipping cost.

Create the new field/s on the checkout page

To create a similar field in shipping address, $fields array would need to be duplicated mentioning [‘shipping’] instead of [‘billing’]

Now let us understand the parameters used in the above code

Hook: Creating a new field uses woocommerce_checkout_fields hook

type: Since this an drop down field the type of the field is select. If you want a mobile number field it could be a text field

required: specify whether the field is compulsory or not. In our case we are calculating the shipping cost based on this field so it is compulsory.

class: 

  1. form-row-wide: The new field will occupy the entire div width for the billing section
  2. form-row-first: The new field will appear in the first half area of the row (like the first name field)
  3. form-row-last: The new field will appear in the last half area of the row (like the last name field)
  4. address-field: specifies that it is an address field
  5. update_totals_on_change: Since we want to update the shipping price on the checkout page based on this field this class triggers the ajax update process on field value change
  6. options: Drop down field options can be provided using this parameters. Obviously this field will not be applicable for text box

Validate the newly created field

Here we are just specifying the error message to display when the field is left empty

Save the field data

Here we are saving the data to the postmeta table for the corresponding order_id after the form is submitted

Show the custom field value on the orders page under billing section