I’ve updated my pypi carleslibs package to version 0.99.2.
The addition I made to this version is StringUtils class which offer functionalities for handling amount conversions (to different units), number formatting, string formatting and align (left, right…). I added a 85% of Unit Testing Code Coverage.
Here you have some general information about how to install and how to use the package:
mysql> UPDATE wp_options set option_value='blog.carlesmateo.local' WHERE option_name='siteurl'; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0
This way I set an entry in /etc/hosts and I can do all the tests I want.
Is in the main page, just after the recommended articles.
Here you can see the source code.
This article shows the code for my PHP program posts_and_views.php that you can see at the bottom of in the Quick Selection bar.
So I wrote this small PHP Code to display the number of Post Views recorded in my WordPress Database, sorted by Views in descending order.
In other words, sorted from Most Viewed.
Please note: In order to track the post views I use the excellent plugin WP-PostViews by Lester ‘GaMerZ’ Chan
Here is the code for posts_and_views.php
As you can see I use wp-config.php file to get the Database Settings (username, password, database) but I don’t use the WordPress Engine, it is just a stand alone PHP. So I wrote my own function to get the slug based on the category name. I believe the slug for it is in the Database and I could have added this as a SubQuery with JOINs, which would be better, but I wanted to keep the Database workload lightweight and especially, I did not want to invest more time investigating how the get the slug.
As I tested my function I didn’t find any Category failing but I saw that I had the Category Storage repeated in two different structure tree in Categories, and I will only always link to just the first one (So I was not linkin to the slug storage-2). I fixed that Category that I found repeated but to be honest if this script was a commercial solution or an Open Source solution properly maintained rather than just a sample, I would update it to have Categories and Tags’ slugs coming from the Database.
I would probably make it work with a Cron that would generate a cached page, updated every hour or every ten minutes. I did this in other projects I worked or in my PHP Framework Catalonia Framerwork.
But honestly, the load of the page does not justify a major effort in optimizing here, in this case.
<!DOCTYPE html>
<html>
<head>
<style>
.first {
background-color: blue;
padding: 12px;
}
.second {
background-color: rgba(254, 253, 252, 0.7);
text-align: center;
padding:20px 0;
font-size: 20px;
}
.centered {
text-align: center;
}
/* unvisited link */
a:link {
color: blue;
text-decoration: none;
}
/* visited link */
a:visited {
color: blue;
text-decoration: none;
}
/* mouse over link */
a:hover {
color: #00A8EF;
text-decoration: underline;
}
/* selected link */
a:active {
color: blue;
}
</style>
<body>
<?php
include "wp-config.php";
$s_site_name = "Carles Mateo's blog";
$s_site_link = "https://blog.carlesmateo.com";
function get_category_slug($s_text) {
$s_output_text = strtolower($s_text);
$s_output_text = str_replace(" ", "-", $s_output_text);
return $s_output_text;
}
?>
<h1><a href="<?php print $s_site_link; ?>"><?php print($s_site_name); ?></a></h1>
<?php
$s_sort = "views DESC, post_date, post_title DESC";
if (array_key_exists("sort", $_GET)) {
if ($_GET["sort"] == "date") {
$s_sort = "post_date, views, post_title";
}
}
$s_servername = "localhost";
$s_database = DB_NAME;
$s_username = DB_USER;
$s_password = DB_PASSWORD;
// Create connection
$o_conn = new mysqli($s_servername, $s_username, $s_password, $s_database);
// Check connection
if ($o_conn->connect_error) {
die("Connection failed: " . $o_conn->connect_error);
}
/*
mysql> DESCRIBE wp_posts;
+-----------------------+-----------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------+------+-----+---------------------+----------------+
| ID | bigint unsigned | NO | PRI | NULL | auto_increment |
| post_author | bigint unsigned | NO | MUL | 0 | |
| post_date | datetime | NO | | 0000-00-00 00:00:00 | |
| post_date_gmt | datetime | NO | | 0000-00-00 00:00:00 | |
| post_content | longtext | NO | | NULL | |
| post_title | text | NO | | NULL | |
| post_excerpt | text | NO | | NULL | |
| post_status | varchar(20) | NO | | publish | |
| comment_status | varchar(20) | NO | | open | |
| ping_status | varchar(20) | NO | | open | |
| post_password | varchar(255) | NO | | | |
| post_name | varchar(200) | NO | MUL | | |
| to_ping | text | NO | | NULL | |
| pinged | text | NO | | NULL | |
| post_modified | datetime | NO | | 0000-00-00 00:00:00 | |
| post_modified_gmt | datetime | NO | | 0000-00-00 00:00:00 | |
| post_content_filtered | longtext | NO | | NULL | |
| post_parent | bigint unsigned | NO | MUL | 0 | |
| guid | varchar(255) | NO | | | |
| menu_order | int | NO | | 0 | |
| post_type | varchar(20) | NO | MUL | post | |
| post_mime_type | varchar(100) | NO | | | |
| comment_count | bigint | NO | | 0 | |
+-----------------------+-----------------+------+-----+---------------------+----------------+
*/
/*
mysql> describe wp_postmeta;
+------------+-----------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-----------------+------+-----+---------+----------------+
| meta_id | bigint unsigned | NO | PRI | NULL | auto_increment |
| post_id | bigint unsigned | NO | MUL | 0 | |
| meta_key | varchar(255) | YES | MUL | NULL | |
| meta_value | longtext | YES | | NULL | |
+------------+-----------------+------+-----+---------+----------------+
*/
$s_sql = "SELECT DISTINCT post_title, post_content,
(SELECT CAST(meta_value AS SIGNED) FROM wp_postmeta WHERE wp_postmeta.meta_key = 'views' AND wp_postmeta.post_id = wp_posts.ID) AS 'views',
(SELECT group_concat(wp_terms.name separator ',')
FROM wp_terms
INNER JOIN wp_term_taxonomy
ON wp_terms.term_id = wp_term_taxonomy.term_id
INNER JOIN wp_term_relationships wpr
ON wpr.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
WHERE
taxonomy= 'category' AND wp_posts.ID = wpr.object_id
) AS 'Categories',
(SELECT group_concat(wp_terms.name separator ', ')
FROM wp_terms
INNER JOIN wp_term_taxonomy
ON wp_terms.term_id = wp_term_taxonomy.term_id
INNER JOIN wp_term_relationships wpr
ON wpr.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
WHERE
taxonomy= 'post_tag' AND wp_posts.ID = wpr.object_id
) AS 'Tags',
ID, post_name, post_date, post_modified, post_type
FROM wp_posts
WHERE
post_type = 'post' AND post_status = 'publish'
ORDER BY
$s_sort";
$o_result = $o_conn->query($s_sql);
if ($o_result->num_rows > 0) {
?><table style="border:1px solid">
<tr class="first"><th>Title</th><th style="min-width:100px">Views</th><th style="min-width:150px">Creation Date</th><th>Categories</th><th>Tags</th></tr>
<?php
$i_total_views = 0;
$i_articles = 0;
// output data of each row
while($row = $o_result->fetch_assoc()) {
$s_style='style="border:1px solid"';
$s_style='';
$s_url = $row['post_name'];
print('<tr>');
print("<td $s_style>");
print('<a href="'.$s_url.'" target="_blank">');
print($row["post_title"]);
print('</a>');
print("</td>");
print('<td class="centered" '.$s_style.'>');
print(number_format($row["views"]));
print("</td>");
print("<td $s_style>");
print("<small>");
print($row["post_date"]);
print("</small>");
print("</td>");
print("<td $s_style>");
$s_categories = $row["Categories"];
$a_categories = explode (",", $s_categories);
$s_categories_content = "";
foreach($a_categories as $s_category) {
$s_category_slug = "/category/".get_category_slug($s_category)."/";
$s_categories_content = $s_categories_content .'<a href="'.$s_category_slug.'" target="_blank">';
$s_categories_content = $s_categories_content .$s_category;
$s_categories_content = $s_categories_content ."</a>, ";
}
if (strlen($s_categories_content) > 0) {
$s_categories_content = substr($s_categories_content, 0, -2);
}
print($s_categories_content);
print("</td>");
print("<td $s_style>");
print($row["Tags"]);
print("</td>");
// $row["post_content"];
$i_total_views = $i_total_views + intval($row["views"]);
$i_articles++;
echo "</tr>";
} ?></table><?php
print("<strong>Total articles:</strong> ".number_format($i_articles)." <strong>Total Views:</strong> ".number_format($i_total_views));
print("<br>");
} else {
echo "<p>0 results</p>";
}
$o_conn->close();
?>
</body>
</html>
I start with a VM, to learn about the providers and the migration project as I go.
My VM has been running in Amazon AWS for years.
It has 3.5GB of RAM and 1 Core. However is uses only 580MB of RAM. I’m paying around $85/month for this with Amazon.
I need to migrate:
For the DNS Server I don’t need it anymore, each Domain provider has included DNS Service for free, so I do not longer to have my two DNS.
For the email I find myself in the same scenario, most providers offer 3 email accounts for your domain, and some alias, for free.
I’ll start the Service as Docker in the new CSP, so I will make it work in my computer first, locally, and so I can move easily in the future.
Note: exporting big images is not the idea I have to make backups.
I locate a Digital Ocean droplet with 1GB of RAM and 1 core and SSD disks for $5, for $6 I can have a NVMe version. That I choose.
The first thing I do is to analyze the disk space needs of the service.
In this old AWS CentOS based image I have:
[root@ip-10-xxx-yyy-zzz ec2-user]# df -h Filesystem Size Used Avail Use% Mounted on /dev/xvda1 79G 11G 69G 14% / devtmpfs 1.8G 12K 1.8G 1% /dev tmpfs 1.8G 0 1.8G 0% /dev/shm
Ok, so if I keep the same I have I need 11GB.
I have plenty of space on this server so I do a zip of all the contents of the blog:
cd /var/www/wordpress zip -r /home/ec2-user/wp_sizeZ.zip wp_siteZ
I need a dump of the databases I want to migrate.
I check what databases are in this Server.
mysql -u root -p mysql> show databases;
I do a dump of the databases that I want:
sudo mysqldump --password='XXXXXXXX' --databases wp_mysiteZ > wp_mysiteZ.sql
I get an error, meaning MySQL needs repair:
mysqldump: Got error: 145: Table './wp_mysiteZ/wp_visitor_maps_wo' is marked as crashed and should be repaired when using LOCK TABLES
So I launch a repair:
sudo mysqlcheck --password='XXXXXXXX' --repair --all-databases
And after the dump works.
My dump takes 88MB, not much, but I compress it with gzip.
gzip wp_mysiteZ.sql
It takes only 15MB compressed.
Do not forget the parameter –databases even if only one database is exported, otherwise the CREATE DATABASE and USE `wp_mysiteZ`; will not be added to your dump.
I will need to take some data form the mysql database, referring to the user used for accessing the blog’s database.
I always keep the CREATE USER and the GRANT permissions, if you don’t check the wp-config.php file. Note that the SQL format to create users and grant permissions may be different from a SQL version to another.
I create a file named mysql.sql with this part and I compress with gzip.
php -v PHP 7.3.23 (cli) (built: Oct 21 2020 20:24:49) ( NTS ) Copyright (c) 1997-2018 The PHP Group Zend Engine v3.3.23, Copyright (c) 1998-2018 Zend Technologies
WordPress is updated, and PHP is not that old.
The new Ubuntu 20.04 LTS comes with PHP 7.4. It will work:
php -v
PHP 7.4.3 (cli) (built: Jul 5 2021 15:13:35) ( NTS )
Copyright (c) The PHP Group
Zend Engine v3.4.0, Copyright (c) Zend Technologies
with Zend OPcache v7.4.3, Copyright (c), by Zend Technologies
FROM ubuntu:20.04
MAINTAINER Carles Mateo
ARG DEBIAN_FRONTEND=noninteractive
# RUN echo "nameserver 8.8.8.8" > /etc/resolv.conf
RUN echo "Europe/Ireland" | tee /etc/timezone
# Note: You should install everything in a single line concatenated with
# && and finalizing with
# apt autoremove && apt clean
# In order to use the less space possible, as every command
# is a layer
RUN apt update && apt install -y apache2 ntpdate libapache2-mod-php7.4 mysql-server php7.4-mysql php-dev libmcrypt-dev php-pear git mysql-server less zip vim mc && apt autoremove && apt clean
RUN a2enmod rewrite
RUN mkdir -p /www
# If you want to activate Debug
# RUN sed -i "s/display_errors = Off/display_errors = On/" /etc/php/7.2/apache2/php.ini
# RUN sed -i "s/error_reporting = E_ALL & ~E_DEPRECATED & ~E_STRICT/error_reporting = E_ALL/" /etc/php/7.2/apache2/php.ini
# RUN sed -i "s/display_startup_errors = Off/display_startup_errors = On/" /etc/php/7.2/apache2/php.ini
# To Debug remember to change:
# config/{production.php|preproduction.php|devel.php|docker.php}
# in order to avoid Error Reporting being set to 0.
ENV PATH_WP_MYSITEZ /var/www/wordpress/wp_mysitez/
ENV PATH_WORDPRESS_SITES /var/www/wordpress/
ENV APACHE_RUN_USER www-data
ENV APACHE_RUN_GROUP www-data
ENV APACHE_LOG_DIR /var/log/apache2
ENV APACHE_PID_FILE /var/run/apache2/apache2.pid
ENV APACHE_RUN_DIR /var/run/apache2
ENV APACHE_LOCK_DIR /var/lock/apache2
ENV APACHE_LOG_DIR /var/log/apache2
RUN mkdir -p $APACHE_RUN_DIR
RUN mkdir -p $APACHE_LOCK_DIR
RUN mkdir -p $APACHE_LOG_DIR
RUN mkdir -p $PATH_WP_MYSITEZ
# Remove the default Server
RUN sed -i '/<Directory \/var\/www\/>/,/<\/Directory>/{/<\/Directory>/ s/.*/# var-www commented/; t; d}' /etc/apache2/apache2.conf
RUN rm /etc/apache2/sites-enabled/000-default.conf
COPY wp_mysitez.conf /etc/apache2/sites-available/
RUN chown --recursive $APACHE_RUN_USER.$APACHE_RUN_GROUP $PATH_WP_MYSITEZ
RUN ln -s /etc/apache2/sites-available/wp_mysitez.conf /etc/apache2/sites-enabled/
# Please note: It would be better to git clone from another location and
# gunzip and delete temporary files in the same line,
# to save space in the layer.
COPY *.sql.gz /tmp/
RUN gunzip /tmp/*.sql.gz; echo "Starting MySQL"; service mysql start && mysql -u root < /tmp/wp_mysitez.sql && mysql -u root < /tmp/mysql.sql; rm -f /tmp/*.sql; rm -f /tmp/*.gz
# After this root will have password assigned
COPY *.zip /tmp/
COPY services_up.sh $PATH_WORDPRESS_SITES
RUN echo "Unzipping..."; cd /var/www/wordpress/; unzip /tmp/*.zip; rm /tmp/*.zip
RUN chown --recursive $APACHE_RUN_USER.$APACHE_RUN_GROUP $PATH_WP_MYSITEZ
EXPOSE 80
CMD ["/var/www/wordpress/services_up.sh"]
For starting MySQL and Apache I relay in services_up.sh script.
#!/bin/bash
echo "Starting MySql"
service mysql start
echo "Starting Apache"
service apache2 start
# /usr/sbin/apache2 -D FOREGROUND
while [ true ];
do
ps ax | grep mysql | grep -v "grep "
if [ $? -gt 0 ];
then
service mysql start
fi
sleep 10
done
You see that instead of launching apache2 as FOREGROUND, what keeps the loop, not exiting from my Container is a while [ true ]; that will keep looping and checking if MySQL is up, and restarting otherwise.
Some of my sites receive DoS attacks. More than trying to shutdown my sites, are spammers trying to publish comment announcing fake glasses, or medicines for impotence, etc… also some try to hack into the Server to gain control of it with dictionary attacks or trying to explode vulnerabilities.
The downside of those attacks is that some times the Database is under pressure, and uses more and more memory until it crashes.
More memory alleviate the problem and buys time, but I decided not to invest more than $6 USD per month on this old site. I’m just keeping the contents alive and even this site still receives many visits. A restart of the MySQL if it dies is enough for me.
As you have seen in my Dockerfile I only have one Docker Container that runs both Apache and MySQL. One of the advantages of doing like that is that if MySQL dies, the container does not exit. However I could have had two containers with both scripts with the while [ true ];
When planning I decided to have just one single Container, all-in-one, as when I export the image for a Backup, I’ll be dealing only with a single image, not two.
I created a Bash script named build_docker.sh that does the build for me, stopping and cleaning previous Containers:
#!/bin/bash
# Execute with sudo
s_DOCKER_IMAGE_NAME="wp_sitez"
printf "Stopping old image %s\n" "${s_DOCKER_IMAGE_NAME}"
sudo docker stop "${s_DOCKER_IMAGE_NAME}"
printf "Removing old image %s\n" "${s_DOCKER_IMAGE_NAME}"
sudo docker rm "${s_DOCKER_IMAGE_NAME}"
printf "Creating Docker Image %s\n" "${s_DOCKER_IMAGE_NAME}"
# sudo docker build -t ${s_DOCKER_IMAGE_NAME} . --no-cache
sudo docker build -t ${s_DOCKER_IMAGE_NAME} .
i_EXIT_CODE=$?
if [ $i_EXIT_CODE -ne 0 ]; then
printf "Error. Exit code %s\n" ${i_EXIT_CODE}
exit
fi
echo "Ready to run ${s_DOCKER_IMAGE_NAME} Docker Container"
echo "To run type: sudo docker run -d -p 80:80 --name ${s_DOCKER_IMAGE_NAME} ${s_DOCKER_IMAGE_NAME}"
echo "or just use run_in_docker.sh"
echo
echo "Debug running Docker:"
echo "docker exec -it ${s_DOCKER_IMAGE_NAME} /bin/bash"
echo
I assign to the image and the Running Container the same name.
Once it works in local, I set the Firewall rules and I deploy the Droplet (VM) with Digital Ocean, I upload the files via SFTP, and then I just run my script build_docker.sh
And assuming everything went well, I run it:
sudo docker run -d -p 80:80 --name wp_mysitez wp_mysitez
I check that the page works, and here we go.
This could also have been put in a private Git repository. You only have to care about not storing the passwords in it. (Like the MySQL grants)
It may be interesting for you to disable directory browsing.
The build from the Git repository can be validated with a Jenkins. Here you have an article about setup a Jenkins for yourself.