Monday, August 20, 2012

Pre-dump database reduction script

Big databases are always a problem and it is handy to clean up your database before you are going to do a db dump. This is most handy when you have to move the dump from one server to another. The syntax for you file will be something like this
truncate dataflow_batch_export;
truncate dataflow_batch_import;
truncate log_customer;
truncate log_quote;
truncate log_summary;
truncate log_summary_type;
truncate log_url;
truncate log_url_info;
truncate log_visitor;
truncate log_visitor_info;
truncate log_visitor_online; 
truncate report_viewed_product_index;
truncate report_compared_product_index;
truncate report_event;
truncate report_viewed_product_aggregated_daily;
truncate report_viewed_product_aggregated_monthly;
truncate report_viewed_product_aggregated_yearly;
truncate sendfriend_log;
Then you want to save this as a sql file. (cleandb.sql) Then you can run it against your current database (After you make a backup) like this: First I would take a current backup of your database like this
mysqldump -p'password' -u user -h hostaddress databasename | gzip > backup.sql.gz
Then run the clean script
mysql -p'password' -u user -h hostaddress databasename < cleandb.sql

Wednesday, July 11, 2012

Delete all Magento Products

Delete all your products in Magento

If you don't know what you are doing don't do this. It will completely ruin your site. You will have no products left. Always make a backup! This is tested on 1.6 + Vern has pointed out I need to remove products and categories, I have posted his update
SET FOREIGN_KEY_CHECKS = 0;
        TRUNCATE TABLE `core_url_rewrite`;
        TRUNCATE TABLE `catalog_product_relation`;
        TRUNCATE TABLE `catalog_category_entity`;
        TRUNCATE TABLE `catalog_category_entity_datetime`;
        TRUNCATE TABLE `catalog_category_entity_decimal`;
        TRUNCATE TABLE `catalog_category_entity_int`;
        TRUNCATE TABLE `catalog_category_entity_text`;
        TRUNCATE TABLE `catalog_category_entity_varchar`;
        TRUNCATE TABLE `catalog_category_product`;
        TRUNCATE TABLE `catalog_category_product_index`;

        INSERT  INTO `catalog_category_entity`(`entity_id`,`entity_type_id`,`attribute_set_id`,`parent_id`,`created_at`,`updated_at`,`path`,`position`,`level`,`children_count`) VALUES (1,3,0,0,'0000-00-00 00:00:00','2009-02-20 00:25:34','1',1,0,1),(2,3,3,0,'2009-02-20 00:25:34','2009-02-20 00:25:34','1/2',1,1,0);
        INSERT  INTO `catalog_category_entity_int`(`value_id`,`entity_type_id`,`attribute_id`,`store_id`,`entity_id`,`value`) VALUES (1,3,34,0,2,1),(2,3,34,1,2,1);
        INSERT  INTO `catalog_category_entity_varchar`(`value_id`,`entity_type_id`,`attribute_id`,`store_id`,`entity_id`,`value`) VALUES
            (1,3,33,0,1,'Root Catalog'),
            (2,3,35,0,1,'root-catalog'),
            (3,3,33,0,2,'Default Category'),
            (4,3,41,0,2,'PRODUCTS'),
            (5,3,35,0,2,'default-category');
        TRUNCATE TABLE `catalog_product_bundle_option`;
        TRUNCATE TABLE `catalog_product_bundle_option_value`;
        TRUNCATE TABLE `catalog_product_bundle_selection`;
        TRUNCATE TABLE `catalog_product_entity_datetime`;
        TRUNCATE TABLE `catalog_product_entity_decimal`;
        TRUNCATE TABLE `catalog_product_entity_gallery`;
        TRUNCATE TABLE `catalog_product_entity_int`;
        TRUNCATE TABLE `catalog_product_entity_media_gallery`;
        TRUNCATE TABLE `catalog_product_entity_media_gallery_value`;
        TRUNCATE TABLE `catalog_product_entity_text`;
        TRUNCATE TABLE `catalog_product_entity_tier_price`;
        TRUNCATE TABLE `catalog_product_entity_varchar`;
        TRUNCATE TABLE `catalog_product_link`;
        TRUNCATE TABLE `catalog_product_link_attribute`;
        TRUNCATE TABLE `catalog_product_link_attribute_decimal`;
        TRUNCATE TABLE `catalog_product_link_attribute_int`;
        TRUNCATE TABLE `catalog_product_link_attribute_varchar`;
        TRUNCATE TABLE `catalog_product_link_type`;
        TRUNCATE TABLE `catalog_product_option`;
        TRUNCATE TABLE `catalog_product_option_price`;
        TRUNCATE TABLE `catalog_product_option_title`;
        TRUNCATE TABLE `catalog_product_option_type_price`;
        TRUNCATE TABLE `catalog_product_option_type_title`;
        TRUNCATE TABLE `catalog_product_option_type_value`;
        TRUNCATE TABLE `catalog_product_super_attribute_label`;
        TRUNCATE TABLE `catalog_product_super_attribute_pricing`;
        TRUNCATE TABLE `catalog_product_super_attribute`;
        TRUNCATE TABLE `catalog_product_super_link`;
        TRUNCATE TABLE `catalog_product_enabled_index`;
        TRUNCATE TABLE `catalog_product_website`;
        TRUNCATE TABLE `catalog_category_product_index`;
        TRUNCATE TABLE `catalog_category_product`;
        TRUNCATE TABLE `cataloginventory_stock_item`;
        TRUNCATE TABLE `cataloginventory_stock_status`;
        TRUNCATE TABLE `cataloginventory_stock`;
        TRUNCATE TABLE `catalog_compare_item`;
        TRUNCATE TABLE `adminnotification_inbox`;
        TRUNCATE TABLE `index_event`;
        TRUNCATE TABLE `index_process_event`;
        TRUNCATE TABLE `log_url_info`;
        TRUNCATE TABLE `log_url`;
        TRUNCATE TABLE `log_visitor`;
        TRUNCATE TABLE `log_visitor_info`;
        TRUNCATE TABLE `report_viewed_product_index`;
        TRUNCATE TABLE `report_event`;
        DELETE FROM `eav_attribute_option` WHERE attribute_id = 70;
        DELETE `eav_attribute_option_value`.* FROM `eav_attribute_option_value` LEFT JOIN `eav_attribute_option` USING (`option_id`) WHERE `eav_attribute_option`.`option_id` IS NULL;
        INSERT  INTO `catalog_product_link_type`(`link_type_id`,`code`) VALUES (1,'relation'),(2,'bundle'),(3,'super'),(4,'up_sell'),(5,'cross_sell');
        INSERT  INTO `catalog_product_link_attribute`(`product_link_attribute_id`,`link_type_id`,`product_link_attribute_code`,`data_type`) VALUES (1,2,'qty','decimal'),(2,1,'position','int'),(3,4,'position','int'),(4,5,'position','int'),(6,1,'qty','decimal'),(7,3,'position','int'),(8,3,'qty','decimal');
        INSERT  INTO `cataloginventory_stock`(`stock_id`,`stock_name`) VALUES (1,'Default');
        TRUNCATE TABLE `catalog_product_entity`;
        SET FOREIGN_KEY_CHECKS = 1;

SSH Upgrade for Magento

Just a quick way to upgrade you site
find . -type f -exec chmod 777 {} \;
find . -type d -exec chmod 777 {} \;
rm -rf var/cache/* var/session/*
chmod 550 ./mage
./mage mage-setup .
./mage config-set preferred_state stable
./mage list-installed
./mage list-upgrades
./mage install http://connect20.magentocommerce.com/community Mage_All_Latest --force
php shell/indexer.php reindexall
find . -type f -exec chmod 644 {} \;
find . -type d -exec chmod 755 {} \;

Wednesday, October 26, 2011

Installing Magento Via SSH

Installing Magento With The Full Download

Installing Into The Website Document Root

I took this content from the Magento WIKI page, since they have removed the Search feature for the Magento WIKI's I figured it would be easier to find this way for myself and others! Since this Post will soon become outdated, I must tell you to change the version number for your download. i.e. if you want version 1.5.1 you will put magento-1.5.1.0.tar.gz When version 1.7 comes out you will need magento-1.7.0.0
wget http://www.magentocommerce.com/downloads/assets/1.6.1.0/magento-1.6.1.0.tar.gz
tar -zxvf magento-1.6.1.0.tar.gz
mv magento/* magento/.htaccess .
chmod -R o+w media var
chmod o+w app/etc
rm -rf magento/ magento-1.6.1.0.tar.gz

Install Magento using the downloader

wget http://www.magentocommerce.com/downloads/assets/1.5.0.0/magento-downloader-1.5.0.0.tar.gz
tar -zxvf magento-downloader-1.5.0.0.tar.gz
rm -rf magento-downloader-1.5.0.0.tar.gz

Install into a Subdirectory

For this install you only need to replace SUBDIRECTORY below (two times) with the name of the subdirectory that you want Magento installed in.
mkdir SUBDIRECTORY
cd SUBDIRECTORY
wget http://www.magentocommerce.com/downloads/assets/1.5.0.0/magento-downloader-1.5.0.0.tar.gz
tar -zxvf magento-downloader-1.5.0.0.tar.gz
rm -rf magento-downloader-1.5.0.0.tar.gz

Installing Magento With The Downloader & Sample Data

Installing into the Root Web Directory

wget http://www.magentocommerce.com/downloads/assets/1.5.0.0/magento-downloader-1.5.0.0.tar.gz
wget http://www.magentocommerce.com/downloads/assets/1.2.0/magento-sample-data-1.2.0.tar.gz
tar -zxvf magento-downloader-1.5.0.0.tar.gz
tar -zxvf magento-sample-data-1.2.0.tar.gz
mv magento-sample-data-1.2.0/media/* magento/media/
mv magento-sample-data-1.2.0/magento_sample_data_for_1.2.0.sql magento/data.sql
mv magento/* magento/.htaccess .
chmod -R o+w media
mysql -h DBHOST -u DBUSER -p'DBPASS' DBNAME < data.sql
./mage mage-setup .
./mage install magento-core/Mage_All_Latest-stable
chmod o+w var app/etc
rm -rf magento/ magento-sample-data-1.2.0/
rm -rf magento-downloader-1.5.0.0.tar.gz magento-sample-data-1.2.0.tar.gz data.sql
Please visit the Magento site and post to the forum if you have any questions or problem. Click to visit Magento Forums

Monday, October 3, 2011

How to find the Magento version - Quick and Easy

Many Magento store leave the release notes in the root of their website. This is the fastest way to find out what version the site is running

http://www.domain.com/RELEASE_NOTES.txt

Of course if you remove the file or change the permissions you will not find it.

Wednesday, September 14, 2011

Super Easy Magento Compatible Centos Install

The Basic Magento Web Server Install Guide

This install guide assumes that you already have the basic install of Centos 5.6 complete. We will now install Apache, MySQL and PHP 5.2.17 First let's install some basic tools
yum groupinstall 'Development Tools'
yum groupinstall 'Development Libraries'
Ok let's install Apache, NTP and MySQL
yum install ntp httpd mysql-server httpd-devel
Look to see what PHP packages are installed and remove any if needed.
rpm -qa | grep php
remove all php 5.3 packages We are going to need some re-requisites for our PHP so lets install them now!!
yum install libc-client libmcrypt libmhash net-snmp libtidy  unixODBC-devel libpng-devel libmcrypt-devel
Now we are ready to install PHP5.2.17
cd ~/mkdir php5.2.17
cd php5.2.17
wget 'http://www.atoomnet.net/./php/php-5.2.17/php-5.2.17-1.x86_64.rpm'
wget 'http://www.atoomnet.net/./php/php-5.2.17/php-bcmath-5.2.17-1.x86_64.rpm'
wget 'http://www.atoomnet.net/./php/php-5.2.17/php-cli-5.2.17-1.x86_64.rpm'
wget 'http://www.atoomnet.net/./php/php-5.2.17/php-common-5.2.17-1.x86_64.rpm'
wget 'http://www.atoomnet.net/./php/php-5.2.17/php-dba-5.2.17-1.x86_64.rpm'
wget 'http://www.atoomnet.net/./php/php-5.2.17/php-devel-5.2.17-1.x86_64.rpm'
wget 'http://www.atoomnet.net/./php/php-5.2.17/php-eaccelerator-5.2.17_0.9.6.1-1.x86_64.rpm'
wget 'http://www.atoomnet.net/./php/php-5.2.17/php-gd-5.2.17-1.x86_64.rpm'
wget 'http://www.atoomnet.net/./php/php-5.2.17/php-imap-5.2.17-1.x86_64.rpm'
wget 'http://www.atoomnet.net/./php/php-5.2.17/php-ldap-5.2.17-1.x86_64.rpm'
wget 'http://www.atoomnet.net/./php/php-5.2.17/php-mbstring-5.2.17-1.x86_64.rpm'
wget 'http://www.atoomnet.net/./php/php-5.2.17/php-mcrypt-5.2.17-1.x86_64.rpm'
wget 'http://www.atoomnet.net/./php/php-5.2.17/php-mhash-5.2.17-1.x86_64.rpm'
wget 'http://www.atoomnet.net/./php/php-5.2.17/php-mysql-5.2.17-1.x86_64.rpm'
wget 'http://www.atoomnet.net/./php/php-5.2.17/php-ncurses-5.2.17-1.x86_64.rpm'
wget 'http://www.atoomnet.net/./php/php-5.2.17/php-odbc-5.2.17-1.x86_64.rpm'
wget 'http://www.atoomnet.net/./php/php-5.2.17/php-pdo-5.2.17-1.x86_64.rpm'
wget 'http://www.atoomnet.net/./php/php-5.2.17/php-pgsql-5.2.17-1.x86_64.rpm'
wget 'http://www.atoomnet.net/./php/php-5.2.17/php-readline-5.2.17-1.x86_64.rpm'
wget 'http://www.atoomnet.net/./php/php-5.2.17/php-snmp-5.2.17-1.x86_64.rpm'
wget 'http://www.atoomnet.net/./php/php-5.2.17/php-soap-5.2.17-1.x86_64.rpm'
wget 'http://www.atoomnet.net/./php/php-5.2.17/php-tidy-5.2.17-1.x86_64.rpm'
wget 'http://www.atoomnet.net/./php/php-5.2.17/php-xml-5.2.17-1.x86_64.rpm'
wget 'http://www.atoomnet.net/./php/php-5.2.17/php-xmlrpc-5.2.17-1.x86_64.rpm'
rpm -ivh *.rpm
(I removed wget 'http://www.atoomnet.net/./php/php-5.2.17/php-mssql-5.2.17-1.x86_64.rpm') Make sure MySQL is on
chkconfig --levels 235 mysqld on
/etc/init.d/mysqld start
And let's make sure we have MySQL locked down (follow instructions)
mysql_secure_installation
Make sure Apache is on
chkconfig --levels 235 httpd on
/etc/init.d/httpd start
Finally, add another repo
cd /etc/yum.repos.d/
wget http://centos.karan.org/kbsingh-CentOS-Extras.repo
then make the following changes:
vi /etc/yum.repos.d/kbsingh-CentOS-Extras.repo
gpgcheck to 0 and enabled to 1 in the [kbs-CentOS-Testing] You will need to configure your Apache and MySQL

The Best Dedicated Server for Magento

This posting is more for question answers than anything else.

Background:

I do a lot of Magento upgrades, the bottom line, your upgrading server matters. What I would like to do is collaborate (knowledge) on the best machine (and the cheapest) and the best configuration on a single dedicated server.

My best case example is a 4gb Magento 1.3 database run on a HPDL160 G6 Dual Quad Core 1.6ghz processors, 16gb RAM, and SATA 7200rpm drives.

I installed and configured CentOS 5.5 with MySQL 5.1. I tuned MySQL (I will post a script later)

I will use my desktop machine (at the time) as my benchmark.
Intel i7 Quad core
16gb Memory
7200 RPM SATA Drive
CentOS 5.5, MySQL 5.1
(Purchased all from Microcenter)

In addition, I also tested a Dual Intel i7 Quad Core with 16GB memory and a 7200RPM SATA drive running Ubuntu 10.04

My best completion time on my Desktop Machine was 12 hours
My best completion time for the Ubuntu Desktop was 10 hours
My best completion time for the HP G6 was just under 5 hours.

My latest endevor involves a HP DL385 AMD Quad -Dual Core server (4-dual core CPUs) with 32GB memory and RAID 5 SCSI Ultra 320.

A 2gb Magento database is taking more than 12 hours to upgrade.

I am not a hardware GURU, so I am looking for some explanations on why the G6 ran so much faster? In addition, I would be happy to post anyone configuration that allows for the fastest Magento upgrades. Ideally I would like to first find the optimum server configuration, then find the optimum server.