Running XAMPP on Ubuntu using Terminal

To start XAMPP run the following and the command will return a list of running services:

sudo /opt/lampp/lampp start

To stop XAMPP run the command below and it will return a list of the stopped services.

sudo /opt/lampp/lampp stop

You can also stop/start/restart individual services by appending “apache”, “mysql” or “ftp” to the end.

Starting

start – Start XAMPP (Apache, MySQL and eventually others)
startapache – Start only Apache
startmysql – Start only MySQL
startftp – Start only ProFTPD

Stopping

stop – Stop XAMPP (Apache, MySQL and eventually others)
stopapache – Stop only Apache
stopmysql – Stop only MySQL
stopftp – Stop only ProFTPD

Restart

reload – Reload XAMPP (Apache, MySQL and eventually others)
reloadapache – Reload only Apache
reloadmysql – Reload only MySQL
reloadftp – Reload only ProFTPD
restart – Stop and start XAMPP

SSL & Security

security – Check XAMPP’s security
enablessl – Enable SSL support for Apache
disablessl – Disable SSL support for Apache

Config

backup – Make backup file of your XAMPP config, log and data files
oci8 – Enable the oci8 extenssion

GUI Panel

panel – Starts graphical XAMPP control panel

Magento Server Transfer – Tried and Tested Method

Moving an entire Magento site from one server to another or between hosts can be a daunting task for non-server administrators.

I’m going to try and expand this over time.

  • Agree time frames, a day for file system sync, an hour for database update and DNS update.
  • Content Freeze – no new images or content is added by Store owner, customers continue to place orders and create accounts.
    • A copy of the site is downloaded to the new server via FTP
    • A snapshot of the database is taken and used to test the site on the new server
  • Database Freeze – no new orders, holding page on the front end, Admin Panel still accessible if required but no updates allowed.
    • Export/import database
      • Old Server: mysqldump -u YourUser -p YourDatabaseName > wantedsqlfile.sql
      • New Server wget http://www.yourwebsite.com/wantedsqlfile.sql
      • New Server: mysql -u YourUser -p YourDatabaseName < wanted.sql
    • Copy over/configure your SSL for the new site
    • Next-up is the DNS update to repoint the domain
  • Done

 

MySQL TRUNCATE command: Quickly and easilty clear out the Magento database

This is a simple list of SQL commands which can be used to clear out various parts of the Magento database. It’ll be useful when creating a development environment from an existing database. It will clear all real customer, orders and sales data while keeping the catalogue and CMS information.

The basic command should be to disable foreign key checks in MySQL, followed by the required truncate commands, then reenable foreign key checks.

SET FOREIGN_KEY_CHECKS=0; 

Clear the log tables down

This should be managed by Magento but issues with Cron scheduling can mean they grow way beyond a reasonable size. Clearing the log tables will have no impact on the system.

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`; 

Clear out report tables

The report tables will, again, have no negative impact on your store but it will affect performance of some features including recently viewed products, and any reporting you do will need rebuilt before run again.

TRUNCATE `coupon_aggregated`;
TRUNCATE `coupon_aggregated_order`;
TRUNCATE `coupon_aggregated_updated`;
TRUNCATE `report_viewed_product_aggregated_daily`;
TRUNCATE `report_viewed_product_aggregated_monthly`;
TRUNCATE `report_viewed_product_aggregated_yearly`;
TRUNCATE `sales_bestsellers_aggregated_daily`;
TRUNCATE `sales_bestsellers_aggregated_monthly`;
TRUNCATE `sales_bestsellers_aggregated_yearly`;
TRUNCATE `sales_invoiced_aggregated`;
TRUNCATE `sales_invoiced_aggregated_order`;
TRUNCATE `sales_order_aggregated_created`;
TRUNCATE `sales_order_aggregated_updated`;
TRUNCATE `sales_refunded_aggregated`;
TRUNCATE `sales_refunded_aggregated_order`;
TRUNCATE `sales_shipping_aggregated`;
TRUNCATE `sales_shipping_aggregated_order`;
TRUNCATE `tax_order_aggregated_created`;
TRUNCATE `tax_order_aggregated_updated`;

Miscellaneous

TRUNCATE `catalogsearch_query`;
TRUNCATE `index_event`; 
TRUNCATE `index_process_event`;   
TRUNCATE `report_event`;
TRUNCATE `report_viewed_product_index`;
TRUNCATE `sendfriend_log`; 
TRUNCATE `tag`; 
TRUNCATE `tag_relation`; 
TRUNCATE `tag_summary`; 
TRUNCATE `wishlist`;

Remove customer information

-- Customerc
TRUNCATE `customer_entity`;
TRUNCATE `customer_entity_datetime`;
TRUNCATE `customer_entity_decimal`;
TRUNCATE `customer_entity_int`;
TRUNCATE `customer_entity_text`;
TRUNCATE `customer_entity_varchar`;

-- Customer Addresses
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`;

Sales Quotes and Order Data

-- Quotes
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`; 

-- Orders
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`;

-- Invoices
TRUNCATE `sales_flat_invoice`;
TRUNCATE `sales_flat_invoice_comment`;
TRUNCATE `sales_flat_invoice_grid`;
TRUNCATE `sales_flat_invoice_item`;

-- Shipments
TRUNCATE `sales_flat_shipment`;
TRUNCATE `sales_flat_shipment_comment`;
TRUNCATE `sales_flat_shipment_grid`;
TRUNCATE `sales_flat_shipment_item`;
TRUNCATE `sales_flat_shipment_track`;

-- Sales Order Tax
TRUNCATE `sales_order_tax`;
TRUNCATE `sales_order_tax_item`;

-- Creditmemos
TRUNCATE `sales_flat_creditmemo`;
TRUNCATE `sales_flat_creditmemo_comment`;
TRUNCATE `sales_flat_creditmemo_grid`;
TRUNCATE `sales_flat_creditmemo_item`;

After each update, re-enable foreign key checks.

SET FOREIGN_KEY_CHECKS=1;

Magento Code: UK and Ireland counties for region drop-down in addresses

Full list of counties for the United Kingdom and Republic of Ireland. Run this SQL against your Magento database to replace the existing region text box in Magento with a drop down containing the values related to either the UK or Ireland.

Northern Irish counties will appear under United Kingdom and depending on website you may want to modify county names before importing.

-- Data for table `directory_country_region`

INSERT INTO `directory_country_region` (`region_id`, `country_id`, `code`, `default_name`) VALUES
(1000, 'GB', 'AB', 'Aberdeenshire'),
(1001, 'GB', 'AN', 'Anglesey'),
(1002, 'GB', 'AG', 'Angus'),
(1003, 'GB', 'AR', 'Argyll'),
(1004, 'GB', 'AY', 'Ayrshire'),
(1005, 'GB', 'BN', 'Banffshire'),
(1006, 'GB', 'BD', 'Bedfordshire'),
(1007, 'GB', 'BI', 'Berwickshire'),
(1008, 'GB', 'BR', 'Breconshire'),
(1009, 'GB', 'BK', 'Buckinghamshire'),
(1010, 'GB', 'BU', 'Bute'),
(1011, 'GB', 'CN', 'Caernarvonshire'),
(1012, 'GB', 'CT', 'Caithness'),
(1013, 'GB', 'CM', 'Cambridgeshire'),
(1014, 'GB', 'CG', 'Cardiganshire'),
(1015, 'GB', 'CR', 'Carmarthenshire'),
(1016, 'GB', 'CH', 'Cheshire'),
(1017, 'GB', 'CL', 'Clackmannanshire'),
(1018, 'GB', 'CN', 'Cornwall and Isles of Scilly'),
(1019, 'GB', 'ANT', 'County Antrim'),
(1020, 'GB', 'ARM', 'County Armagh'),
(1021, 'GB', 'DOW', 'County Down'),
(1022, 'GB', 'FER', 'County Fermanagh'),
(1023, 'GB', 'DER', 'County Londonderry'),
(1024, 'GB', 'TYR', 'County Tyrone'),
(1025, 'GB', 'CU', 'Cumbria'),
(1026, 'GB', 'DI', 'Denbighshire'),
(1027, 'GB', 'DB', 'Derbyshire'),
(1028, 'GB', 'DV', 'Devon'),
(1029, 'GB', 'DO', 'Dorset'),
(1030, 'GB', 'DA', 'Dumbartonshire'),
(1031, 'GB', 'DU', 'Dumfriesshire'),
(1032, 'GB', 'DR', 'Durham'),
(1033, 'GB', 'EL', 'East Lothian'),
(1034, 'GB', 'ES', 'East Sussex'),
(1035, 'GB', 'EX', 'Essex'),
(1036, 'GB', 'FI', 'Fife'),
(1037, 'GB', 'FL', 'Flintshire'),
(1038, 'GB', 'GM', 'Glamorgan'),
(1039, 'GB', 'GL', 'Gloucestershire'),
(1040, 'GB', 'GR', 'Greater London'),
(1041, 'GB', 'GM', 'Greater Manchester'),
(1042, 'GB', 'HM', 'Hampshire'),
(1043, 'GB', 'HT', 'Hertfordshire'),
(1044, 'GB', 'IV', 'Inverness'),
(1045, 'GB', 'KN', 'Kent'),
(1046, 'GB', 'KE', 'Kincardineshire'),
(1047, 'GB', 'KP', 'Kinross-shire'),
(1048, 'GB', 'KC', 'Kirkcudbrightshire'),
(1049, 'GB', 'LA', 'Lanarkshire'),
(1050, 'GB', 'LC', 'Lancashire'),
(1051, 'GB', 'LE', 'Leicestershire'),
(1052, 'GB', 'LN', 'Lincolnshire'),
(1053, 'GB', 'LD', 'London'),
(1054, 'GB', 'ME', 'Merionethshire'),
(1055, 'GB', 'MR', 'Merseyside'),
(1056, 'GB', 'MO', 'Midlothian'),
(1057, 'GB', 'MO', 'Monmouthshire'),
(1058, 'GB', 'MG', 'Montgomeryshire'),
(1059, 'GB', 'MY', 'Moray'),
(1060, 'GB', 'NN', 'Nairnshire'),
(1061, 'GB', 'NR', 'Norfolk'),
(1062, 'GB', 'NH', 'Northamptonshire'),
(1063, 'GB', 'NU', 'Northumberland'),
(1064, 'GB', 'NY', 'North Yorkshire'),
(1065, 'GB', 'NO', 'Nottinghamshire'),
(1066, 'GB', 'OR', 'Orkney'),
(1067, 'GB', 'OX', 'Oxfordshire'),
(1068, 'GB', 'PB', 'Peebleshire'),
(1069, 'GB', 'PM', 'Pembrokeshire'),
(1070, 'GB', 'PT', 'Perthshire'),
(1071, 'GB', 'RD', 'Radnorshire'),
(1072, 'GB', 'RN', 'Renfrewshire'),
(1073, 'GB', 'RS', 'Ross & Cromarty'),
(1074, 'GB', 'RX', 'Roxburghshire'),
(1075, 'GB', 'SL', 'Selkirkshire'),
(1076, 'GB', 'SE', 'Shetland'),
(1077, 'GB', 'SH', 'Shropshire'),
(1078, 'GB', 'SO', 'Somerset'),
(1079, 'GB', 'SY', 'South Yorkshire'),
(1080, 'GB', 'ST', 'Staffordshire'),
(1081, 'GB', 'SN', 'Stirlingshire'),
(1082, 'GB', 'SU', 'Suffolk'),
(1083, 'GB', 'SU', 'Surrey'),
(1084, 'GB', 'SR', 'Sutherland'),
(1085, 'GB', 'TW', 'Tyne and Wear'),
(1086, 'GB', 'WR', 'Warwickshire'),
(1087, 'GB', 'WE', 'West Lothian'),
(1088, 'GB', 'WM', 'West Midlands'),
(1089, 'GB', 'WS', 'West Sussex'),
(1090, 'GB', 'WY', 'West Yorkshire'),
(1091, 'GB', 'WG', 'Wigtownshire'),
(1092, 'GB', 'WL', 'Wiltshire'),
(1093, 'GB', 'WO', 'Worcestershire'),
(1094, 'IE', 'CAR', 'Carlow'),
(1095, 'IE', 'CAV', 'Cavan'),
(1096, 'IE', 'CLA', 'Clare'),
(1097, 'IE', 'COR', 'Cork'),
(1098, 'IE', 'DON', 'Donegal'),
(1099, 'IE', 'DUB', 'Dublin'),
(1100, 'IE', 'GAL', 'Galway'),
(1101, 'IE', 'KER', 'Kerry'),
(1102, 'IE', 'KID', 'Kildare'),
(1103, 'IE', 'KIK', 'Kilkenny'),
(1104, 'IE', 'LET', 'Leitrim'),
(1105, 'IE', 'LEX', 'Leix'),
(1106, 'IE', 'LIM', 'Limerick'),
(1107, 'IE', 'LOG', 'Longford'),
(1108, 'IE', 'LOU', 'Louth'),
(1109, 'IE', 'MAY', 'Mayo'),
(1110, 'IE', 'MEA', 'Meath'),
(1111, 'IE', 'MOG', 'Monaghan'),
(1112, 'IE', 'OFF', 'Offaly'),
(1113, 'IE', 'ROS', 'Roscommon'),
(1114, 'IE', 'SLI', 'Sligo'),
(1115, 'IE', 'TIP', 'Tipperary'),
(1116, 'IE', 'WAT', 'Waterford'),
(1117, 'IE', 'WEM', 'Westmeath'),
(1118, 'IE', 'WEX', 'Wexford'),
(1119, 'IE', 'WIC', 'Wicklow');

-- Data for `directory_country_region_name`

INSERT INTO `directory_country_region_name` (`locale`, `region_id`, `name`) VALUES
('en_US', 1000, 'Aberdeenshire'),
('en_US', 1001, 'Anglesey'),
('en_US', 1002, 'Angus'),
('en_US', 1003, 'Argyll'),
('en_US', 1004, 'Ayrshire'),
('en_US', 1005, 'Banffshire'),
('en_US', 1006, 'Bedfordshire'),
('en_US', 1007, 'Berwickshire'),
('en_US', 1008, 'Breconshire'),
('en_US', 1009, 'Buckinghamshire'),
('en_US', 1010, 'Bute'),
('en_US', 1011, 'Caernarvonshire'),
('en_US', 1012, 'Caithness'),
('en_US', 1013, 'Cambridgeshire'),
('en_US', 1014, 'Cardiganshire'),
('en_US', 1015, 'Carmarthenshire'),
('en_US', 1016, 'Cheshire'),
('en_US', 1017, 'Clackmannanshire'),
('en_US', 1018, 'Cornwall and Isles of Scilly'),
('en_US', 1019, 'County Antrim'),
('en_US', 1020, 'County Armagh'),
('en_US', 1021, 'County Down'),
('en_US', 1022, 'County Fermanagh'),
('en_US', 1023, 'County Londonderry'),
('en_US', 1024, 'County Tyrone'),
('en_US', 1025, 'Cumbria'),
('en_US', 1026, 'Denbighshire'),
('en_US', 1027, 'Derbyshire'),
('en_US', 1028, 'Devon'),
('en_US', 1029, 'Dorset'),
('en_US', 1030, 'Dumbartonshire'),
('en_US', 1031, 'Dumfriesshire'),
('en_US', 1032, 'Durham'),
('en_US', 1033, 'East Lothian'),
('en_US', 1034, 'East Sussex'),
('en_US', 1035, 'Essex'),
('en_US', 1036, 'Fife'),
('en_US', 1037, 'Flintshire'),
('en_US', 1038, 'Glamorgan'),
('en_US', 1039, 'Gloucestershire'),
('en_US', 1040, 'Greater London'),
('en_US', 1041, 'Greater Manchester'),
('en_US', 1042, 'Hampshire'),
('en_US', 1043, 'Hertfordshire'),
('en_US', 1044, 'Inverness'),
('en_US', 1045, 'Kent'),
('en_US', 1046, 'Kincardineshire'),
('en_US', 1047, 'Kinross-shire'),
('en_US', 1048, 'Kirkcudbrightshire'),
('en_US', 1049, 'Lanarkshire'),
('en_US', 1050, 'Lancashire'),
('en_US', 1051, 'Leicestershire'),
('en_US', 1052, 'Lincolnshire'),
('en_US', 1053, 'London'),
('en_US', 1054, 'Merionethshire'),
('en_US', 1055, 'Merseyside'),
('en_US', 1056, 'Midlothian'),
('en_US', 1057, 'Monmouthshire'),
('en_US', 1058, 'Montgomeryshire'),
('en_US', 1059, 'Moray'),
('en_US', 1060, 'Nairnshire'),
('en_US', 1061, 'Norfolk'),
('en_US', 1062, 'Northamptonshire'),
('en_US', 1063, 'Northumberland'),
('en_US', 1064, 'North Yorkshire'),
('en_US', 1065, 'Nottinghamshire'),
('en_US', 1066, 'Orkney'),
('en_US', 1067, 'Oxfordshire'),
('en_US', 1068, 'Peebleshire'),
('en_US', 1069, 'Pembrokeshire'),
('en_US', 1070, 'Perthshire'),
('en_US', 1071, 'Radnorshire'),
('en_US', 1072, 'Renfrewshire'),
('en_US', 1073, 'Ross & Cromarty'),
('en_US', 1074, 'Roxburghshire'),
('en_US', 1075, 'Selkirkshire'),
('en_US', 1076, 'Shetland'),
('en_US', 1077, 'Shropshire'),
('en_US', 1078, 'Somerset'),
('en_US', 1079, 'South Yorkshire'),
('en_US', 1080, 'Staffordshire'),
('en_US', 1081, 'Stirlingshire'),
('en_US', 1082, 'Suffolk'),
('en_US', 1083, 'Surrey'),
('en_US', 1084, 'Sutherland'),
('en_US', 1085, 'Tyne and Wear'),
('en_US', 1086, 'Warwickshire'),
('en_US', 1087, 'West Lothian'),
('en_US', 1088, 'West Midlands'),
('en_US', 1089, 'West Sussex'),
('en_US', 1090, 'West Yorkshire'),
('en_US', 1091, 'Wigtownshire'),
('en_US', 1092, 'Wiltshire'),
('en_US', 1093, 'Worcestershire'),
('en_US', 1094, 'Carlow'),
('en_US', 1095, 'Cavan'),
('en_US', 1096, 'Clare'),
('en_US', 1097, 'Cork'),
('en_US', 1098, 'Donegal'),
('en_US', 1099, 'Dublin'),
('en_US', 1100, 'Galway'),
('en_US', 1101, 'Kerry'),
('en_US', 1102, 'Kildare'),
('en_US', 1103, 'Kilkenny'),
('en_US', 1104, 'Leitrim'),
('en_US', 1105, 'Leix'),
('en_US', 1106, 'Limerick'),
('en_US', 1107, 'Longford'),
('en_US', 1108, 'Louth'),
('en_US', 1109, 'Mayo'),
('en_US', 1110, 'Meath'),
('en_US', 1111, 'Monaghan'),
('en_US', 1112, 'Offaly'),
('en_US', 1113, 'Roscommon'),
('en_US', 1114, 'Sligo'),
('en_US', 1115, 'Tipperary'),
('en_US', 1116, 'Waterford'),
('en_US', 1117, 'Westmeath'),
('en_US', 1118, 'Wexford'),
('en_US', 1119, 'Wicklow');

MySQL Database Backup and Restore via Command Line (Copy-and-pasteable)

Putting this together because I always end up checking online for the correct bash commands for Linux. These commands rely on your knowing for sure which database is which and you should always double check to be sure.

Export

First step is to go to use cd to change directory to the one in which you want the SQL Dump to appear. The dump will consist of a text file and all the database structure and content included.

cd var/www/example.com

If you want to double check the contents of the directory you are in then simply enter ls and a list of directories and files will be returned.

ls

When you’re happy you’re in the right directory, we can export the file. In this instance I have a user name (user123) after the -uand the database is database_live and the initial -p means bash will ask me for the password.

mysqldump -p -uuser123 database_live > mydumpfile.sql

Hit enter, enter the password and after some serious thinking (depending on the size of the database) you’ll be able to enter the next command. Again, entering ls will allow you to verify there is a file called mydumpfile.sql in the directory.

Import

The next step is then to import the database into your other database. It should be noted that importing the previous dump will with default configuration wipe any existing data during the import into the second database.

In the instance below I have a user name (uuser456 ) after the -uand the database is database_live and the initial -p means bash will ask me for the password.

mysql -p -uuser456 database_backup < mydumpfile.sql

Hit enter, enter the password for the user and again it will hang while the details are imported.