Install Webmin on CentOS 7

INSTALL WEBMIN

To get started, login your remote server and follow the steps below:

First, create a new file webmin.repo in /etc/yum.repos.d/ :

vi /etc/yum.repos.d/webmin.repo

and paste below line:

[Webmin]
name=Webmin Distribution Neutral
#baseurl=http://download.webmin.com/download/yum
mirrorlist=http://download.webmin.com/download/yum/mirrorlist
enabled=1

Install webmin GPG key using below command.

rpm --import http://www.webmin.com/jcameron-key.asc

Now, update the repositorys:

yum check-update

Install webmin:

yum install webmin -y

Start the service and make it start automatically

chkconfig webmin on
service webmin start

By default, Webmin will listen on port 10000. Enable webmin Port:

firewall-cmd --add-port=10000/tcp

Now, open http://ip.add.re.ss:10000 in your browser and enter yor root username and password

 

SETTING UP WEBSITES IN WEBMIN

Let’s login to webmin.

This can be done from http://yourserverIP:10000 or https://yourserverIP:10000 (add a security exception)

First create a directory for each site.

Let’s create the folder with the website domain name.

From webmin is pretty fast. Just go to: Others/File Manager (This require java in your browser) and create the folder under /var/www/html using the “yourdomain.com” .
ex.

mkdir -p /var/www/html/yourdomain.com

Once you have the folder created. Go to Servers/Apache Web Server (please make sure you “Start Apache” at the top right corner) and click on the tab named “Create Virtual Host”.

– You can leave it at “Any address” for now.

 – Leave the next boxes checked.

 – Set the port number to 80.

 – In “Document  Root” click on the button at the right of the box and it will open a pop up windows for you to browse the server’s folders. Make sure you browse to /var/www and the folder you created. Click OK to return to the “Create Virtual Host” screen and there you must see the path to the folder you created for this website.

 – Check “Allow access to this directory”

 – Set the server Name to the domain you are creating this host to without the “www”

 – Add virtual server to file. Leave it on “New file under…”.

 – Click on “Create Now”

 It the Virtual Host has been created then we need to make a little more changes to finish this.

Click on the Virtual Host you just created. This will open the “Virtual Server Options page.

Click on “Edit Directives” and under the line “ServerName yourdomain.com”, insert a line that says the following:

ServerAlias www.yourdomain.com

This is an example of how this file must be:

“DocumentRoot /var/www/yourdomain.com
ServerName yourdomain.com
<Directory /var/www/yourdomain.com>
allow from all
Options +Indexes
</Directory>”

You will be taken back to the “Virtual Server Options page”

At the top right corner click on “Apply Changes”.

SETTING UP DNS USING WEBMIN

Go to Servers, and then click on “BIND DNS Server”. If you can’t see it then it needs to be installed .

Start BIND by clicking at the top right option on your browser.

Creating a Master Zone.
1) Click on “Create master zone”
2) Enter the domain without the www into the “Domain name / network” box.
3) For the master server, type in the domain name as well.
4) You can check the “Create NS record” box if you’d like.
5) Enter your email address in the appropriate box.
6) Type in the IP address of the server (The external IP address provided by your ISP).
7) Click “Create”

Creating A records.
1) Click on “Address”
2) Leave the name field blank, and type in the IP address.
3) Click Save.
(The following is dependent on what you want to run the server for.)
4) Type in www for the name, and type in the IP address.
5) Click Save.
6) Type in mail for the name, and type in the IP address.
7) Click Save.
8) Type in ftp for the name, and type in the IP address.
9) Click Save.
10) Type in ns1 for the name, and type in the IP address.
11) Click Save.
12) Type in ns2 for the name, and type in the IP address.
13) Click Save.
14) At the bottom, click “Return to Record Types”

Creating NS records.
1) Click on Name Server
2) For the zone name, type in the domain name (Put a . at the end; for example:
yourdomain.com.
(Notice the ending period… DO Not Forget that)
3) For the name server, type in ns1.yourdomain.com.
(Also notice the period)
4) Click Save.
5) Type in ns2.yourdomain.com.
6) Click Save.
7) Click “Return to Record Types”

Creating an MX Record.
1) Click Mail Server (If you want a mail server).
(Make sure you put the period at the end; mail.yourdomain.com.
2) For the name, type in the domain.
3) For the mail server, type in mail.yourdomain.com.
4) Set the priority to what you like… 10 is usually default.
5) Click “Return to Record Types”

SETTING FTP ACCESS USING WEBMIN

Install proftpd  via Un-used Modules in Webmin

After that go to  System/Users and Groups, and select “Create a new user.

Provide a username for what is to be the FTP account.

As “Home directory” select “Directory” and press the button to show the folders in your server.
Select the /var/www//html/yourdomain.com folder you previously created or whatever folder you want the ftp account to login into.

Select the “Shell” /bin/false

Select “Normal Password” and provide a unique, complex password for the account

Select “New Group” and write down the same name as the user you’re creating

Disable:

Create home directory
Copy template files…
Create user in other modules
Click on “Create”

It will complete the process and take you back to the users list page.

Go back to the SSH console and run the following:

#chown username /var/www/html/yourdomain.com 

(folder you previously created )

Now start your favorite FTP Client en try your new user.

Magento orders by sku

SQL code for orders by sku

SQL code for orders by sku


SELECT sales_flat_order.increment_id,
 sales_flat_order_item.order_id,
 sales_flat_order_item.sku,
 sales_flat_order.status,
 sales_flat_order_item.name,
 sales_flat_order.customer_email
 FROM dbname.sales_flat_order_item sales_flat_order_item
 INNER JOIN dbname.sales_flat_order sales_flat_order
 ON (sales_flat_order_item.order_id = sales_flat_order.entity_id)
 WHERE (sales_flat_order_item.sku = 'fill in sku no ')
 AND (sales_flat_order.status &lt;&gt; 'Canceled')
ORDER BY sales_flat_order.increment_id DESC


Install OpenSSL en SOAP on CentOS

Install OpenSSL
yum install openssl
Note: This is typically installed on CentOS by default.

Install SOAP
yum install php-soap

apachectl restart

Disable paypal logo from sidebar in Magento

System > Configuration > Payment Methods

> Paypal All in One Payments Solutions
> Paypal Payments Advanced (Includes Express Checkout)
> Basic Settings – Paypal Payments Advanced
> Frontend Expereince settings
> Paypal Product Logo

Sql query: Delete all Orders, Sales & Customer Data in Magento

 Sql query: Delete all Orders, Sales & Customer Data in Magento

# Tested on Magento CE 1.4.1.1 - 1.9.0.1

SET FOREIGN_KEY_CHECKS=0;

##############################
# SALES RELATED TABLES
##############################
TRUNCATE `sales_flat_creditmemo`;
TRUNCATE `sales_flat_creditmemo_comment`;
TRUNCATE `sales_flat_creditmemo_grid`;
TRUNCATE `sales_flat_creditmemo_item`;
TRUNCATE `sales_flat_invoice`;
TRUNCATE `sales_flat_invoice_comment`;
TRUNCATE `sales_flat_invoice_grid`;
TRUNCATE `sales_flat_invoice_item`;
TRUNCATE `sales_flat_order`;
TRUNCATE `sales_flat_order_address`;
TRUNCATE `sales_flat_order_grid`;
TRUNCATE `sales_flat_order_item`;
TRUNCATE `sales_flat_order_payment`;
TRUNCATE `sales_flat_order_status_history`;
TRUNCATE `sales_flat_quote`;
TRUNCATE `sales_flat_quote_address`;
TRUNCATE `sales_flat_quote_address_item`;
TRUNCATE `sales_flat_quote_item`;
TRUNCATE `sales_flat_quote_item_option`;
TRUNCATE `sales_flat_quote_payment`;
TRUNCATE `sales_flat_quote_shipping_rate`;
TRUNCATE `sales_flat_shipment`;
TRUNCATE `sales_flat_shipment_comment`;
TRUNCATE `sales_flat_shipment_grid`;
TRUNCATE `sales_flat_shipment_item`;
TRUNCATE `sales_flat_shipment_track`;
TRUNCATE `sales_invoiced_aggregated`;            # ??
TRUNCATE `sales_invoiced_aggregated_order`;        # ??
TRUNCATE `log_quote`;

ALTER TABLE `sales_flat_creditmemo_comment` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_creditmemo_grid` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_creditmemo_item` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_invoice` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_invoice_comment` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_invoice_grid` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_invoice_item` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_order` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_order_address` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_order_grid` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_order_item` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_order_payment` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_order_status_history` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_address` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_address_item` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_item` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_item_option` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_payment` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_shipping_rate` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_shipment` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_shipment_comment` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_shipment_grid` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_shipment_item` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_shipment_track` AUTO_INCREMENT=1;
ALTER TABLE `sales_invoiced_aggregated` AUTO_INCREMENT=1;
ALTER TABLE `sales_invoiced_aggregated_order` AUTO_INCREMENT=1;
ALTER TABLE `log_quote` AUTO_INCREMENT=1;

#########################################
# DOWNLOADABLE PURCHASED
#########################################
TRUNCATE `downloadable_link_purchased`;
TRUNCATE `downloadable_link_purchased_item`;

ALTER TABLE `downloadable_link_purchased` AUTO_INCREMENT=1;
ALTER TABLE `downloadable_link_purchased_item` AUTO_INCREMENT=1;

#########################################
# RESET ID COUNTERS
#########################################
TRUNCATE `eav_entity_store`;
ALTER TABLE  `eav_entity_store` AUTO_INCREMENT=1;

##############################
# CUSTOMER RELATED TABLES
##############################
TRUNCATE `customer_address_entity`;
TRUNCATE `customer_address_entity_datetime`;
TRUNCATE `customer_address_entity_decimal`;
TRUNCATE `customer_address_entity_int`;
TRUNCATE `customer_address_entity_text`;
TRUNCATE `customer_address_entity_varchar`;
TRUNCATE `customer_entity`;
TRUNCATE `customer_entity_datetime`;
TRUNCATE `customer_entity_decimal`;
TRUNCATE `customer_entity_int`;
TRUNCATE `customer_entity_text`;
TRUNCATE `customer_entity_varchar`;
TRUNCATE `tag`;
TRUNCATE `tag_relation`;
TRUNCATE `tag_summary`;
TRUNCATE `tag_properties`;            ## CHECK ME
TRUNCATE `wishlist`;
TRUNCATE `log_customer`;

ALTER TABLE `customer_address_entity` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_datetime` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_decimal` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_int` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_text` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_varchar` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_datetime` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_decimal` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_int` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_text` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_varchar` AUTO_INCREMENT=1;
ALTER TABLE `tag` AUTO_INCREMENT=1;
ALTER TABLE `tag_relation` AUTO_INCREMENT=1;
ALTER TABLE `tag_summary` AUTO_INCREMENT=1;
ALTER TABLE `tag_properties` AUTO_INCREMENT=1;
ALTER TABLE `wishlist` AUTO_INCREMENT=1;
ALTER TABLE `log_customer` AUTO_INCREMENT=1;

##############################
# ADDITIONAL LOGS
##############################
TRUNCATE `log_url`;
TRUNCATE `log_url_info`;
TRUNCATE `log_visitor`;
TRUNCATE `log_visitor_info`;
TRUNCATE `report_event`;
TRUNCATE `report_viewed_product_index`;
TRUNCATE `sendfriend_log`;
### ??? TRUNCATE `log_summary`

ALTER TABLE `log_url` AUTO_INCREMENT=1;
ALTER TABLE `log_url_info` AUTO_INCREMENT=1;
ALTER TABLE `log_visitor` AUTO_INCREMENT=1;
ALTER TABLE `log_visitor_info` AUTO_INCREMENT=1;
ALTER TABLE `report_event` AUTO_INCREMENT=1;
ALTER TABLE `report_viewed_product_index` AUTO_INCREMENT=1;
ALTER TABLE `sendfriend_log` AUTO_INCREMENT=1;
### ??? ALTER TABLE `log_summary` AUTO_INCREMENT=1;

SET FOREIGN_KEY_CHECKS=1;

 

Installing OAuth for PHP clients on CentOS 6

Installing EPEL and Remi repo on CentOS 6

wget http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
wget http://rpms.famillecollet.com/enterprise/remi-release-6.rpm
sudo rpm -Uvh remi-release-6*.rpm epel-release-6*.rpm
sudo vi /etc/yum.repos.d/remi.repo

set : enabled=1 on the topmost repo

Installing OAuth for PHP clients

yum install php-pecl-oauth

REASON 442: FAILED TO ENABLE VIRTUAL ADAPTER

REASON 442: FAILED TO ENABLE VIRTUAL ADAPTER on Windows 8.1

 cisco-vpn-adapter3

If you receive this error on Windows 8 or Windows 8.1 while trying to connect with the Cisco VPN Client then the solution is a simple registry fix.

To fix:

cisco-vpn-adapter2

Click Start and type regedit in the Search field and hit enter.
Navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\CVirtA
Find the String Value called DisplayName
Right click and select Modify from the context menu.
In Value data, remove @oemX.inf,%CVirtA_Desc%;. The Value data should only contain Cisco Systems VPN Adapter for 64-bit Windows.
Click Ok.

cisco-vpn-adapter3
Close Registry Editor.

Retry your Cisco VPN Client connection.

MySQL query caching for ex. Magento

Login Plesk MySQL

On server with Parallels Plesk Panel versions 8.x, 9.x and above admin password is stored in file /etc/psa/.psa.shadow. Use the following command to get the password:

~# cat /etc/psa/.psa.shadow

Since version 10 admin password stored in file /etc/psa/.psa.shadow is encrypted. Use the following command to get admin password in plain text:

~# /usr/local/psa/bin/admin --show-password

Despite version of Parallels Plesk Panel you may use the following command to login to mysql with root privileges:

~# mysql -uadmin -p`cat /etc/psa/.psa.shadow`

MySQL query caching

Login with:

mysql -u username -p
SHOW VARIABLES LIKE 'have_query_cache';

mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+
1 row in set (0.00 sec)
SHOW VARIABLES LIKE 'query_cache_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_size | 0     |
+------------------+-------+
1 row in set (0.00 sec)
SHOW VARIABLES LIKE 'query%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_alloc_block_size       | 8192    |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
| query_prealloc_size          | 8192    |
+------------------------------+---------+
7 rows in set (0.01 sec)

Hieronder zie je wat de meest belangrijke onderdelen betekenen in de bovenstaande lijst:

query_cache_size : Dit is de grootte van de cache in bytes. Wanneer je de waarde van deze instelling op het getal 0 zet zul je effectief caching uitzetten. query_cache_type : Deze waarde moet ingesteld staan op ON of het getal 0 om query caching standaard aan te hebben staan. query_cache_limit – Dit is de maximale query grootte (wederom in bytes) dat gecached zal worden.

Wanneer de instelling query_cache_size op 0 staat, of je deze zelf wilt aanpassen zul je onderstaande query uit moeten voeren. Hou er rekening mee dat de waarde in bytes is! Bijvoorbeeld: wanneer je 16 megabyte wil toewijzen aan de cache zul je deze moeten berekenen met : 1024 x 1024 x 16 = 16777216. Stel dan 16777216 in als de waarde voor de instelling query_cache_size. SET GLOBAL query_cache_size = 16777216;

SET GLOBAL query_cache_size = 16777216;

De overige instellingen zijn op een soortgelijke manier in te stellen: SET GLOBAL query_cache_type = 1; SET GLOBAL query_cache_limit = 1048476;

SET GLOBAL query_cache_type = 1;
SET GLOBAL query_cache_limit = 1048476;

Hoe kun je er zeker van zijn dat je instellingen werken? Simpel, je kunt hier de query SHOW STATUS voor gebruiken om alle variabelen te laten tonen die beginnen met “Qc”. Laten we een kijkje nemen: ======

SET GLOBAL query_cache_size = 16777216;
SHOW STATUS LIKE 'Qc%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 16759696 |
| Qcache_hits             | 0        |
| Qcache_inserts          | 0        |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 0        |
| Qcache_queries_in_cache | 0        |
| Qcache_total_blocks     | 1        |
+-------------------------+----------+
8 rows in set (0.01 sec)

Bulk edit price in Magento with SQL query

Select Prices

SELECT 
catalog_product_entity_decimal 
WHERE
catalog_product_entity_decimal.attribute_id=75  

Update Prices with Value

UPDATE 
catalog_product_entity_decimal 
SET catalog_product_entity_decimal.value = 35.9500
WHERE
catalog_product_entity_decimal.attribute_id=75  

(catalog_product_entity_decimal.attribute_id=75 = price)

Empty Value

UPDATE 
catalog_product_entity_decimal 
SET catalog_product_entity_decimal.value = NULL
WHERE
catalog_product_entity_decimal.value_id=120

Update Prices with %

UPDATE
catalog_product_entity_decimal
SET
catalog_product_entity_decimal.value = catalog_product_entity_decimal.value*1.19
WHERE
catalog_product_entity_decimal.attribute_id=75
OR
catalog_product_entity_decimal.attribute_id=76;

1.19 is 19% tax
Id=75 is the price
Id = 76 is the special price (see table eav_attribute table for codes)

Update Prices with Rounded Value

UPDATE
catalog_product_entity_decimal
SET
catalog_product_entity_decimal.value= ROUND(catalog_product_entity_decimal.value,0)
WHERE
catalog_product_entity_decimal.attribute_id=75
OR
catalog_product_entity_decimal.attribute_id=76;

Run this if you want all your prices to be also rounded e..g 25.00 instead of 25.01

Now Refresh Cache in Magento

‘Open With’ Menu in Mac OS X

Clearing Finder’s ‘Open With’ Menu in Mac OS X Mountain lion

Enter the following command :

/System/Library/Frameworks/CoreServices.framework/Versions/A/Frameworks/LaunchServices.framework/Versions/A/Support/lsregister -kill -r -domain local -domain system -domain user