Setup Production DB Server: MariaDB + PhpMyAdmin

Standard

Update Centos:

sudo yum update
sudo yum install nano wget

DB Server

sudo yum install httpd php php-mysql

sudo service httpd start
sudo systemctl enable httpd

Install MariaDB
Add MariaDB Repo

sudo nano /etc/yum.repos.d/MariaDB.repo

Put this inside MariaDB.repo


# MariaDB 10.1 CentOS repository list - created 2016-06-07 15:06 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Install and Start Maria DB

sudo yum install MariaDB-server MariaDB-client

sudo service mariadb start
sudo systemctl enable mariadb

Securing Maria DB

sudo /usr/bin/mysql_secure_installation

Installing PHP MyAdmin

sudo yum install epel-release

sudo yum install phpmyadmin

PhpMyadmin Configuration

sudo nano /etc/httpd/conf.d/phpMyAdmin.conf

Change Php MyAdmin URL

Alias /NePhpMyAdminUrl /usr/share/phpMyAdmin

To allow access from All add Require all granted

<RequireAny>
   Require all granted
   Require ip 127.0.0.1
   Require ip ::1
</RequireAny>

Restart Web Server

sudo service httpd restart

URL

http://domain.com/NePhpMyAdminUrl

Mysql Export Import through terminal

Standard

Mysql Export Import through terminal

Export:

mysqldump -p -u username from_mydb > to_mydb.sql

Compress:

tar -zcvf mydb.tar.gz mydb.sql

Decompress:

tar -zxvf mydb.tar.gz

Import

mysql -p -u username db_target < from_mydb.sql

Special Query 1

Standard
SELECT t1.shade_sku SKU, t3.catalogue_title Product, t1.shade_name Shade, SUM( t1.order_quantity ) Quantity, SUM( t1.order_quantity * ( `item_promo_id` 
OR trans_promo_id ) <>0 ) Promo
FROM p_shop_order t1, p_shop_transaction t2, p_shop_catalogue_data t3
WHERE `order_product_id` = t3.catalogue_id AND t3.catalogue_lang = 'eng'
AND t1.`order_transaction` = t2.trans_id
AND t2.trans_status
IN (
'3', '4', '5', '6'
)
AND t2.trans_date_buy
BETWEEN '2013-03-01 00:00:00'
AND '2013-04-01 00:00:00'
GROUP BY t1.shade_sku
ORDER BY Quantity DESC 
LIMIT 20