Establish PrestaShop MariaDB Connect: 8 Easy Steps

The problem statement: Once a PrestaShop website is set up, the need for a reliable fork to MySQL(an alternative to MySQL), a relational database, arises. MariaDB is a relational database management system that e-commerce website developers can make use of to define structured information in a wide array of applications — ranging from banking to websites.

Prestashop MariaDB Connect provides high-performance speed for better monitoring because of the fact that MariaDB provides microsecond precision and extended user statistics. Moreover, MariaDB provides additional, improved storage engines that allow running SQL and NoSQL in a single database system.

This blog post will first talk about some basics for our new readers, introducing Prestashop and MariaDB. Then we will be deep-diving into the process to establish Prestashop MariaDB Connect. Let’s begin.

What is Prestashop?

PrestaShop is an open-source e-commerce platform with out-of-the-box features that help it stand out from its competitors. It is based on PHP and can assist companies and online retail merchants in creating full e-commerce websites with incredible capabilities.

PrestaShop is a powerful eCommerce website building platform with a very easy-to-use interface for managing your online store. It provides hundreds of modules to improve the software’s efficiency

Key Features of Prestashop

• Ease of Use: Because PrestaShop’s back-end is more sophisticated, it takes longer to set up. You use the online PrestaShop demo, which contains the front-end store and the back-end office. You can simply envision it with all of the needed characteristics on a PC, tablet, or mobile phone. PrestaShop takes some effort to get up, but it’s well worth the wait. It is quite simple to install and utilize PrestaShop. It quickly creates a completely responsive internet store for you.
• Free & Open-source: PrestaShop is a completely free and open-source platform that enables you to create a fully configurable website in minutes. PrestaShop makes e-commerce website building much simpler and more accessible. This fantastic e-commerce CMS is available in 65 countries and has over one million active users in the open-source community. As a result, it has one of the world’s largest open source communities. 
• Incredible Community: This community is always contributing to the platform’s improvement. They have already reached milestones such as providing 75+ free translators and more than 60 payment modules that are PrestaShop compatible. And each improvement makes PrestaShop a more powerful platform than ever before.
• Google Rich Snippets: Without a question, Google is the engine that drives visitors to your website. And it should understand what your website is about and what services you provide so that it can distinguish your website from others. As a result, it may drive visitors to your website without a hitch. This is when rich snippets come in handy. You may utilize rich snippets to set your website apart from the competition. In the PrestaShop marketplace, you may discover an add-on that streamlines this procedure precisely.
• Easy Customization: PrestaShop comes with a number of modules like as cross-selling and newsletter subscriptions that can be engaged with a single click, making dull and conventional websites a thing of the past. These modules might assist you in tailoring the site to your specific requirements. PrestaShop’s marketplace has over 5,000 modules and themes.

What is MariaDB?

MariaDB is the default MySQL database system in most, if not all, major Linux distributions, including RHEL (RedHat Enterprise Linux) and Fedora Linux. It is also compatible with Windows, macOS, and various other operating systems. It is used in the LAMP and LEMP stacks to replace the MySQL database system. MariaDB’s development began in response to concerns raised after Oracle Corporation bought MySQL in 2009. MariaDB’s developers and maintainers now do monthly merges with the MySQL codebase to guarantee that MariaDB receives any relevant bug fixes from MySQL.

Key Features of MariaDB

• Storage Engines in MariaDB: MariaDB includes InnoDB, a storage engine that balances high reliability and performance with ACID-compliant (Atomicity, Consistency, Isolation, and Durability) transaction characteristics, as well as support for foreign keys. MariaDB is made up of another well-known storage engine known as XtraDB. Prior to 10.1, this storage engine was utilized. InnoDB became MariaDB’s default storage engine from version 10.2.
• Compatible Across Platforms: MariaDB employs the widely used SQL language for data querying. It is compatible with a wide range of programming languages and can operate on a wide range of operating systems. It runs on Windows and Linux and supports languages such as Python, PHP, C++, Java, JavaScript, Ruby, and others.
• JSON Support: JSON is a data storage format that is widely used. JSON support methods are included in all MariaDB 10.2 and higher versions.

Prestashop MariaDB Connect: Step-by-step Process

In this section of the blog post, we will discuss the process of establishing PrestaShop MariaDB Connect.

• Step 1: Preparing Hosts
• Step 2: Deploying MariaDB Cluster
• Step 3: Configuring iSCSI Target 
• Step 4: Creating Cluster for GFS2
• Step 5: Configuring for iSCSI Initiator & Mount GFS2
• Step 6: Configure Apache & PHP
• Step 7: Load Balancers & Failover
• Step 8: Migrating PrestaShop to the Cluster

Step 1: Preparing Hosts

• The following host definitions in /etc/hosts are given below:

192.168.50.100      virtual-ip prestashop
192.168.50.101      prestashop1 web1 maria1
192.168.50.102      prestashop2 web2 maria2
192.168.50.103      prestashop3 web3 maria3
192.168.50.111      lb1
192.168.50.112      lb2
192.168.50.120      storage1 clustercontrol

• To make the deployment process easier, we will disable SElinux and iptables on all hosts:

$ setenforce 0
$ service iptables stop
$ sed -i 's|SELINUX=enforcing|SELINUX=disabled|g' /etc/selinux/config
$ chkconfig iptables off

Step 2: Deploying MariaDB Cluster

The database cluster will be deployed from storage1, also known as the ClusterControl node. This is the second step in the process to establish the PrestaShop MariaDB Connect

• To get started with MariaDB Cluster, use the Galera Configurator to create a deployment package. When setting our database cluster in the wizard, we utilized the following values:

Vendor: MariaDB Cluster
Infrastructure: none/on-premises
Operating System: RHEL6 - Redhat 6.4/Fedora/Centos 6.4/OLN 6.4/Amazon AMI
Number of Galera Servers: 3
OS user: root
ClusterControl Server: 192.168.50.120
Database Servers: 192.168.50.101 192.168.50.102 192.168.50.103

• Run deploy.sh after downloading the deployment package.

$ wget http://www.severalnines.com/galera-configurator/tmp/5p7q2amjqf7pioummp2hgo6iu2/s9s-galera-mariadb-3.5.0-rpm.tar.gz
$ tar xvfz s9s-galera-mariadb-3.5.0-rpm.tar.gz
$ cd s9s-galera-mariadb-3.5.0-rpm/mysql/scripts/install
$ bash ./deploy.sh 2>&1 | tee cc.log

• The deployment will take about 15 minutes, after which the ClusterControl UI will be available at http://192.168.50.120/clustercontrol. In the UI, you should now see your MariaDB Cluster.

Step 3: Configuring iSCSI Target 

Note: On storage1, the following steps should be performed to establish PrestaShop MariaDB Connect.

• The storage server (storage1) must export a disc through iSCSI so that it may be mounted on all three web servers (prestashop1, prestashop2, and prestashop3). Basically, iSCSI informs your kernel that you have a SCSI drive and transmits that access over IP. The “server” is referred to as the “target,” and the “client” that utilizes the iSCSI device is referred to as the “initiator.” Set up an iSCSI target in storage1:

$ yum install -y scsi-target-utils

• Make a directory for the disc image. This image will be exported as a shared disc using the iSCSI protocol, and it will be able to be mounted on all web servers:

$ mkdir -p /iscsi/disks
$ dd if=/dev/zero of=/iscsi/disks/shared_disk01.img count=0 bs=1 seek=5G

• With the following lines define the iSCSI target configuration.

# /etc/tgt/targets.conf
<target iqn.2013-06.storage:shared_disk1="">
    backing-store /iscsi/disks/shared_disk01.img
    initiator-address 192.168.50.101
    initiator-address 192.168.50.102
    initiator-address 192.168.50.103
</target>

• Start the iSCSI target service and set it to run on boot:

$ service tgtd start
$ chkconfig tgtd on

Step 4: Creating Cluster for GFS2

Unless otherwise noted, the following actions should be conducted on prestashop1, prestashop2, and prestashop3 to Establish PrestaShop MariaDB Connect.

•  Install the relevant packages in prestashop1, then enable and start luci and Ricci on boot:

$ yum groupinstall -y "High Availability" "High Availability Management" "Resilient Storage"
$ chkconfig luci on
$ chkconfig ricci on
$ service luci start
$ service ricci start

• Install the necessary packages in prestashop2 and prestashop3, enable Ricci on boot, then launch the service:

$ yum groupinstall -y "High Availability" "Resilient Storage"
$ chkconfig ricci on
$ service ricci start

• Reset the password for user Ricci. It is suggested that all nodes use the same password:

$ passwd ricci

• Navigate to https://192.168.50.101:8084/ to access the management page. Log in with your root credentials and navigate to Manage Clusters > Create, where you may provide the following information:

Ascertain that the Web Cluster cluster has been successfully established and connected.

Step 5: Configuring for iSCSI Initiator & Mount GFS2

Unless otherwise noted, the following actions should be conducted on prestashop1, prestashop2, and prestashop3 to Establish PrestaShop MariaDB Connect.

• On the respective host, install the iSCSI initiator.

$ yum install -y iscsi-initiator-utils

• Now, you’ll need to discover the iSCSI targets that were set up earlier:

$ iscsiadm -m discovery -t sendtargets -p storage1
192.168.50.120:3260,1 iqn.2013-06.storage:shared_disk1

• If you can view the above output, we can see and connect to the iSCSI target. To connect to an iSCSI disc, configure the iSCSI initiator to start automatically and restart the iSCSI initiator service:

$ chkconfig iscsi on
$ service iscsi restart

• Now list out new disks, making sure you see the new disk, /dev/sdb:

$ fdisk -l

• Make a partition in /dev/sdb that will be mounted through iSCSI on prestashop1.

$ fdisk /dev/sdb

•  GFS2 from prestashop1 should be used to format the partition. Keep in mind that the name of our cluster is Web Cluster, and the iSCSI partition is /dev/sdb1:

$ mkfs.gfs2 -p lock_dlm -t Web_Cluster:data -j 4 /dev/sdb1

• When you’re done, you’ll see some information about the freshly established GFS2 partition. Obtain the UUID value and place it in /etc/fstab:

UUID="4e7f0049-f92e-8ec8-23b3-6cc940344381" /home/prestashop/public_html gfs2 noatime,nodiratime 0 0

• Create a system account called “Prestashop,” as well as the web directory that will be used to mount GFS2:

$ useradd prestashop
$ mkdir -p /home/prestashop/public_html
$ chmod 755 /home/prestashop

• To obtain the revised partition table on the iSCSI drive, restart the iSCSI service once more:

$ service iscsi restart

• Start the service after enabling GFS2 on boot. GFS2 will be mounted under /home/Prestashop/public HTML.

$ chkconfig gfs2 on
$ service gfs2 start

Step 6: Configure Apache & PHP

Unless otherwise noted, the following actions should be conducted on prestashop1, prestashop2, and prestashop3 to Establish PrestaShop MariaDB Connect.

• Install Apache, PHP, and all associated packages:

$  yum install -y httpd php php-cli php-gd php-mysql php-pdo php-mbstring php-mcrypt php-domxml-php4-php5 unzip

• Create an Apache logs directory with log files for our Prestashop site:

$ mkdir -p /home/prestashop/logs
$ touch /home/prestashop/logs/error_log
$ touch /home/prestashop/logs/access_log

• Add the following lines to a new configuration file for the site in /etc/httpd/conf.d/:

# /etc/httpd/conf.d/prestashop.conf
NameVirtualHost *:80
<virtualhost *:80="">
    ServerName myonlineshop.com
    ServerAlias www.myonlineshop.com
    ServerAdmin webmaster@myonlineshop.com
    DocumentRoot /home/prestashop/public_html
    ErrorLog /home/prestashop/logs/error_log
    CustomLog /home/prestashop/logs/access_log combined
</virtualhost>

• Start the service and enable Apache on boot:

$ chkconfig httpd on
$ service httpd start

Step 7: Load Balancers & Failover

Unless otherwise noted, the following actions should be conducted on prestashop1, prestashop2, and prestashop3 to Establish PrestaShop MariaDB Connect

• Set up passwordless SSH from storage1 to lb1 and lb2 so that ClusterControl may provision the node:

$ ssh-copy-id -i ~/.ssh/id_rsa 192.168.50.111
$ ssh-copy-id -i ~/.ssh/id_rsa 192.168.50.112

• Use the Add Load Balancer wizard to install HAProxy on lb1 and lb2.
• Install Keepalived and set 192.168.50.100 as the virtual IP address for HAproxy failover, as shown in the image below.

• The top summary bar in the ClusterControl UI should look like this at the end of the deployment:

• Add the following lines to /etc/haproxy/haproxy.cfg on lb1 and lb2 to configure HAProxy to serve PrestaShop on port 80.

listen prestashop_cluster
    bind *:80
    mode tcp
    option httpchk GET /
    balance leastconn
    server prestashop1 192.168.50.101:80 check inter 5000
    server prestashop2 192.168.50.102:80 check inter 5000
    server prestashop1 192.168.50.103:80 check inter 5000

• Restart for the HAProxy instance:

$ service haproxy restart

• Create an Alfresco schema by going to Manage > Schemas and Users > New. Create a database called “Prestashop” and save it. Then, using a wildcard host (‘percent’), grant all rights to the schema.

Step 8: Migrating PrestaShop to the Cluster

Unless otherwise noted, the following actions should be conducted on prestashop1, prestashop2, and prestashop3 to Establish PrestaShop MariaDB Connect

• It is recommended that the shop be placed in maintenance mode using the Administration interface. Navigate to Prestashop > Preferences > Maintenance > Enable Shop and choose No.
• Assuming that a separate PrestaShop server is operating under /home/Prestashop/public HTML, SSH into the previous server and archive the web content:

$ cd /home/prestashop/
$ tar -cvzf public_html.tar.gz public_html

• Using mysqldump, export the old database:

$ mysqldump -uprestashop -p -h localhost -P 3306 --single-transaction prestashop > prestashop.sql

• Transfer the backups to the new server. We picked prestashop1 for this purpose:

$ scp public_html.tar.gz prestashop.sql root@192.168.50.101:~

• On prestashop1, restore the site content backup as well as the MySQL database:

$ tar -xvzf public_html.tar.gz /home/prestashop
$ mysql -u prestashop -p prestashop < prestashop.sql

• Inside /home/prestashop/public html/config/settings.inc.php, update the new database server configuration value:

define('_DB_SERVER_', '192.168.50.100:33306');
define('_DB_NAME_', 'prestashop');
define('_DB_USER_', 'prestashop');
define('_DB_PASSWD_', 'password');

• When the transfer is complete, you will be able to access the website using the virtual IP address 192.168.50.100. Remember to disable Prestashop maintenance mode by going to: Prestashop > Preferences > Maintenance > Enable Shop > Yes.

And, that’s it. The PrestaShop MariaDB Connect has been established.

Conclusion

In this blog post, you learned about the basics of Prestashop and MariaDB. You also learned the process through which we can establish Prestashop MariaDB connect for better analytics. If you want to know more and have more data sources to connect to MariaDB, Hevo can help.

Hevo Data provides an Automated No-code Data Pipeline that can help you transfer data from WooCommerce via MySQL. Also, Hevo caters to 100+ data sources (including 40+ free sources) and can seamlessly load your data from MariaDB within minutes (including PrestaShop MariaDB Connect).

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite firsthand. 

Share your learning experience about Prestashop MariaDB connect in the comments section below! 

Frequently Asked Questions