How to Restore MySQL Database Using SSH

This would be a simple step by step post for the help of my customers. You require two tools for this.

i) putty – ssh client
ii) WinSCP – File transfer client over SSH (You may use FTP or File manager as well)

Once you download putty, open it, and use your server’s IP in the Host Name section:

Now, click on open. In the prompt, type ‘root’ as login as and enter. Then type the password and enter. If the password for the root is right, it will login to the command shell prompt or else it will put you in the authentication error console.

Now, open WinSCP to login to your server using the same manner of SSH. Once done, transfer your sql file to the home directory of root.

Login to your server using WinSCP
WinSCP Transfer

To transfer the file, navigate the sql file from your desktop on the left side and then drag it to the right side of WinSCP window. That will start the transfer. Remember the path of the destination on the server. Path is shown in the red marked area in the image.

Once the file is transferred, come back to putty, and change directory to the path you had noted in WinSCP, in our case, it is /root/

cd /root/
mysql -uusername -ppassword your_database_name < your_sql_file_name.sql

Remember the following. Your mysql username should go right after ‘-u’ without any space and the mysql password should be the same without space. Replace the ‘your_database_name’ with your original database name in the server and ‘your_sql_file_name.sql’ with the exact sql file name that you had uploaded.

Now if you allow sometime, the command should complete after the restore. If it returns any error, you would need to attend them to do a complete restore.

How To Compare Metrics within Identical Time of Today with Yesterday (or More) in Metabase / SQL / Metabase Trend Query

Scenario Explanation

Suppose, you have a live Ecommerce system. What you want to see, is how well your revenue is performing comparing to yesterday. But if you are comparing with the total revenue of yesterday, then this won’t be an efficient metric until you reach the end of the day. In real scenario, what you would like, is to compare it with the identical time it is happening right now. For example, if it is 7.19 PM now, then you want to compare either of the following two and show the trend:

i) Revenues within 00:00 – 7:00 PM yesterday to revenues within 00:00 – 7:00 PM today or
ii) Revenues within 00:00 – 8:00 PM yesterday to revenues within 00:00 – 8:00 PM today (remember, it is 7.19 PM, so the counter and comparison will continue to flow when you get new order on this case)

Both of the way of seeing trend is useful, depends on your choice, but if you are comparing the metrics with the full of yesterday and the half way through to today, this might not be as useful.

How To Do This In Meatabase?

Please remember, I would be doing this in Metabase. Metabase is an open source data analytical and visualization tool written in Java. It is simple, fast and efficient tool for all square use cases in data analytics. I am using a ‘Reformatted’ real time MySQL database as the data source, hence the query can be used in plain MySQL databases to see results.

Note: We can’t do this with the simple query of Metabase, hence, we have to use the Native query support of Metabase. If your Administrator has limited your access to Metabase to Simple & Custom query, then you can not do this until your Administrator allows you so.

Building The Query

Let’s assume, our orders lie in ‘orders’ table, order creation dates are given in ‘date_created’ column, along with order status and the total value of the order is stored separately under status and total column.

First, we write the sum of the total column with the mysql aggregation function ‘sum’ and set a variable ‘date_created’ based the on the date_created column of orders table as following:

SELECT date(`orders`.`date_created`) AS `date_created`, sum(`orders`.`total`) AS `sum`
FROM `orders`

Now, if you want to use the ‘Trend’ in Metabase, then the query must have ‘Group By’ a timeseries based on which it shows the ‘Trend’ metrics. And we know, that our group by timeseries going to be ‘date_created’. We also know that our conditions of selecting the rows, going to be under the ‘WHERE’ statement, so we will keep it as blank for now. So the query, goes as following now:

SELECT date(`orders`.`date_created`) AS `date_created`, sum(`orders`.`total`) AS `sum`
FROM `orders`
WHERE (
# Our conditions will follow here    
    )
    
GROUP BY date(`orders`.`date_created`)
ORDER BY date(`orders`.`date_created`) ASC

We are done with the basic. Now, let’s fill up the WHERE statement. First, you may want to filter out certain ‘status’ of the orders in case you have many. You first, fill that up as following:

SELECT date(`orders`.`date_created`) AS `date_created`, sum(`orders`.`total`) AS `sum`
FROM `orders`
WHERE (

        (`orders`.`status` = 'completed'
        OR `orders`.`status` = 'confirmed' OR `orders`.`status` = 'processing')
        )

    )
    
GROUP BY date(`orders`.`date_created`)
ORDER BY date(`orders`.`date_created`) ASC

In my case, we are filtering if the order status is either completed, confirmed or processing. We are discarding other statuses like on-hold, or pending or abandoned cart.

Now, let’s think for a moment. We have to compare two time series, that means, we have to first, create an identical time differences. Let me give an example for better understanding. If we are trying to find out, the revenues between 00:00 – 20:00 yesterday and 00:00 – 20:00 today, then we need to have two conditions in WHERE statement for each of them, and they should select all the rows from both conditions, which necessarily means, it would be a ‘OR’ statement. For both cases, we need to select the order statuses, that would necessarily mean something like the following:

SELECT date(`orders`.`date_created`) AS `date_created`, sum(`orders`.`total`) AS `sum`
FROM `orders`
WHERE (

        (`orders`.`status` = 'completed'
        OR `orders`.`status` = 'confirmed' OR `orders`.`status` = 'processing')
        )
Or
        (`orders`.`status` = 'completed'
        OR `orders`.`status` = 'confirmed' OR `orders`.`status` = 'processing')
        )

    )
    
GROUP BY date(`orders`.`date_created`)
ORDER BY date(`orders`.`date_created`) ASC

Does the above make sense? If not, soon enough it would be, let’s move on to the next step. Now, how can you align the time to match the hour we have now in SQL query? MySQL curdate() returns the current date, which is today at 00:00 So, if I know the difference of hours left for today, and subtract that from curdate(), that would give us the hour it is now with the date of yesterday. Here is the example:

Say: curdate() = 10-6-2020 00:00
now() = 10-6-2020 20:45


This is 20th hour, so if we subtract 20 from 24, it would be 4. Now, we subtract 4 from curdate():

10-6-2020 00:00 - 4 Hour
09-6-2020 20:00

which basically is the same hour we are on, of today 10-6-2020 20:00 hour. Simple, isn’t it?

Now, how to write this in SQL query? Let’s see

First, we get 09-6-2020 00:00 Hour, this can be done as following in MySQL with negative Interval of 1 Day from current date.

date_add(curdate(), INTERVAL -1 DAY)

Next, we get 09-6-2020 20:00 Hour using the following:

date_add(curdate(), INTERVAL -(24 - hour(now())) HOUR)

See, how I am calculating the Interval Hour, I am using the MySQL function 'hour()' on another function 'now()' to get the hour we are on, which is giving me 20, then we are subtracting from 24 as 24 hours a day, the result is giving me 4 here. So the output going to be 20:00 hour. 

Now, for today, we first need 10-6-2020 00:00 Hour, which is simply:

curdate()

then, the target time is basically the now() function:

now()

We now have all 4 parts of it, here is the completed statement going to be only for the time selections:

For Yesterday:

str_to_date(date_format(`orders`.`date_created`, '%Y-%m-%d %H'), '%Y-%m-%d %H') 
 BETWEEN str_to_date(date_format(date_add(curdate(), INTERVAL -1 DAY), '%Y-%m-%d %H'), '%Y-%m-%d %H') AND str_to_date(date_format(date_add(curdate(), INTERVAL -(24 - hour(now())) HOUR), '%Y-%m-%d %H'), '%Y-%m-%d %H')

For Today:

str_to_date(date_format(`orders`.`date_created`, '%Y-%m-%d %H'), '%Y-%m-%d %H') 
BETWEEN str_to_date(date_format(curdate(), '%Y-%m-%d %H'), '%Y-%m-%d %H') AND str_to_date(date_format(now(), '%Y-%m-%d %H'), '%Y-%m-%d %H')

By adding the above to our basic and simple status selection statements:

SELECT date(`orders`.`date_created`) AS `date_created`, sum(`orders`.`total`) AS `sum`
FROM `orders`
WHERE (

        (`orders`.`status` = 'completed'
        OR `orders`.`status` = 'confirmed' OR `orders`.`status` = 'processing' OR `orders`.`status` = 'on-hold')
        AND str_to_date(date_format(`orders`.`date_created`, '%Y-%m-%d %H'), '%Y-%m-%d %H') 
        BETWEEN str_to_date(date_format(date_add(curdate(), INTERVAL -1 DAY), '%Y-%m-%d %H'), '%Y-%m-%d %H') AND str_to_date(date_format(date_add(curdate(), INTERVAL -(24 - hour(now())) HOUR), '%Y-%m-%d %H'), '%Y-%m-%d %H')
    
    OR
    
        (`orders`.`status` = 'completed'
        OR `orders`.`status` = 'confirmed' OR `orders`.`status` = 'processing' OR `orders`.`status` = 'on-hold')
        AND str_to_date(date_format(`orders`.`date_created`, '%Y-%m-%d %H'), '%Y-%m-%d %H') 
        BETWEEN str_to_date(date_format(curdate(), '%Y-%m-%d %H'), '%Y-%m-%d %H') AND str_to_date(date_format(now(), '%Y-%m-%d %H'), '%Y-%m-%d %H')
    
    )
    
GROUP BY date(`orders`.`date_created`)
ORDER BY date(`orders`.`date_created`) ASC

Simple, huh? Indeed, it is. If you run this query in phpmyadmin, or your mysql console, you will see the revenue amounts from the yesterday till the hour we are running through at this moment of today. But in Metabase, you can get a beautiful ‘Trend’ number metric with it. Just go To Visualization >> Trends to show you the Trend Number Metric for this as following:

It is cool to see how good or bad you are doing by this hour than yesterday.

phpMyAdmin Coming Blank in Cpanel

One of the customer reported an issue related to phpMyAdmin earlier today. He was getting a blank page of phpmyadmin that only says “Welcome to phpMyAdmin”

Once I hoped into the ssh and checked the cpanel error log file located under

/usr/local/cpanel/logs/error_log

I observed the following error:

PHP Fatal error: require_once(): Failed opening required './libraries/display_select_lang.lib.php' (include_path='/usr/local/cpanel/3rdparty/php/56/lib/php:.') in /usr/local/cpanel/base/3rdparty/phpMyAdmin/libraries/plugins/auth/AuthenticationCpanel.php on line 147

The error was peculiar because display_select_lang.lib.php wasn’t available in any other cpanel phpmyadmin source files I searched. Then I realized “AuthenticationCpanel.php” mentions the error which usually because Cpanel Authentication wasn’t done properly with the MySQL. Cpanel pass wasn’t synced with the MySQL.

Going to WHM >> Password Modification >> If you select the user and WHM shows you the ‘Sync with MySQL Password’ option, that means the MySQL password is outdated to cpanel and requires syncing (NB: If the password doesn’t require syncing, this option won’t be there). You can reset the pass and check the option to Sync the new pass with MySQL. That should restore your phpmyadmin.

A Brilliant App Optimization/Monitoring Tool – New Relic!

Almost 24 hours ago, one of my friend referred to me an interesting offer from ‘tutplus’

http://dev.tutsplus.com/articles/get-a-free-year-of-tuts-premium-by-trying-new-relic–cms-12

It seems Tutplus either affiliated or owned a new App optimization tool named “New Relic”. My primary objective was of course to get the free Tut+ Premium for a year and the Nerd T-shirt, and whats hard in deploying a PHP App Monitoring tool in one of the server! So I started.

The deployment of the tools are fairly easy. I am not really in the Mobile App thing, so I had chosen the PHP Web App monitoring tool. The deployment is well instructed. Its a RPM based installer for RHEL based releases, pretty clean and simple. Once the installation was done, it added a shared object in my PHP interpreter and started grabbing data. Out of a surprise, I started seeing details that are really cool. Things like “Errors” and “Stack Trace” are the finest invention of this tool. The Stack trace gives you reports like “strace” which is my favorite tool of linux debugging facility. The basic advantage of this feature in New Relic is, it saves the data and post you as a token in the dashboard of new relic. Now, isn’t it brilliant? I sorted almost 23 major bugs in client’s account since I have installed the monitor. Database monitoring also includes some exceptional features that are not usually available in App Monitoring/Optimizations tools I had used before.

Unfortunately, the tool is free for 2 weeks. Since then, the “Pro” version comes with 150$ a month per host. The price is certainly high, but the result is truly amazing, looking at the features and performance of the tool.

At the end of all, I had my Tut+ premium for one year for free of charge and a nerd T-shirt on the way to my home 😀

If you haven’t tried it, you can try it now. If you are an android developer, you can add the code in your app, and monitor your App for 14 days for free, and get a Tut+ premium for free for a year.

Just for a record, I am not affiliated with neither Tut+ nor New Relic. The link should not contain any affiliate url.

Happy troubleshooting!

Form Spam

I have monitoring this thing for long. It is really becoming a headache now. It continuously consuming a lot of CPU and Mysql resources for no reason. Form Spam like wordpress comment spam, directory registration/submission spam, forum spam consumes around 33% of the total CPU usage of a day in one of Mellowhost’s old server according to my calculation a couple of minutes ago. This consumption is pretty huge and grows as the server grows.

For some reason due to the development of auto scripts installer like Softaculous and Fantastico, users tend to try each script and leave them unattended. This keeps leaving form exploits for the botnet attackers. A wordpress blog without akismet is potentially threatened to form spam attack. Most of the phpbb forums contains no protection at all on the initial installation. These let the auto bot spammers to post their links in unattended forums/blogs to gain backlinks from their perspective.

This is not eventually only harmful for the server in realtime but also threaten the reputation of the shared IP. I have been working to try to develop a protection server wide to stop these spammers, but every attempt seems inadequate.

In many cases, it is hard to control or check manually as resellers add users and the users add many addon domains. It grows almost everyday. It is advised for every user, not to keep unattended blog/forum/script. It is always better to add some “Captcha” in all sort of registration form. Nowadays, spammers have broken the Captchas as well. Some people have already started some solution called Random questions. But anyway, there should be something like verification in the registration and the comments shouldn’t be allowed without registration. You can also add the Akismet plugin which is available for almost all the blogs and forums. It drastically reduces the number of spam and acts pretty quickly.

Protecting form spam is not only good for the server, but also good for your sites reputation. If you are hosting an unattended blog script inside one of your main site, than it may receive a serious damage of reputation in SEO if the unattended blog is regularly spammed by malicious users. So, check now, if you have any unattended script inside a folder, you should probably double check and delete it if it is not essential or protect it from auto botnets.

Improving MySQL database performance!

Most of the shared hosting users use different content management system for their websites. There are different types of content management systems. These content management systems use mysql database most of the time. A CMS can have degraded performance when the user’s database gets fragmented. Mysql Database fragmentation appears when you make lots of changes on your database cause lots of writes and updates. Periodically, these changes cause memory fragmentation on the database, mostly MyISAM storage engine performs tremendous slow on a fragmented condition! This tutorial should have the advises for shared hosting users, how often they should defragment their databases and how!

Continue reading “Improving MySQL database performance!”