joostvanveen

-- Get all stores where orders, invoices, etc use the wrong prefix
-- E.g. the '2' in order number '200002345'

SELECT store_id, prefix, entity_type, sequence_table FROM sales_sequence_meta
JOIN sales_sequence_profile ON sales_sequence_profile.meta_id = sales_sequence_meta.meta_id
WHERE prefix <> store_id
ORDER BY store_id;

-- Get all stores that use the wrong prefix autoincrement table for orders, invoices, etc.
-- E.g. the '2345' in order number '200002345'

joostvanveen

-- Inspect the default charset and collation for your database
SELECT default_character_set_name FROM information_schema.schemata WHERE schema_name = 'MYDATABASE_NAME';

-- Alter the default charset and collation for your database
-- CAREFUL: when you change the charset for your database, make sure the data in your tables matches that character set. 
-- If it is not, follow these steps: http://www.alphadevx.com/a/420-Converting-a-MySQL-database-from-latin1-to-utf8
ALTER DATABASE MYDATABASE_NAME CHARACTER SET utf8 COLLATE utf8_general_ci;

-- Alter the default charset and collation for your database
ALTER TABLE MYTABLE_NAME CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

joostvanveen

# Loop though all rotated Directadmin access logs and get the top 20 list of User Agents
# This can be used to identify bots
zcat /home/USER/domains/DOMAIN/logs/LOGNAME.tar.gz* | awk -F\" '{print $6}' | sort | uniq -c | sort -nr | head -20

# Get top 20 IP addresses
zcat /home/USER/domains/DOMAIN/logs/Feb-2019.tar.gz | awk -F\" '{print $1}' | sort | uniq -c | sort -nr | head -20

# Get top 20 most visited URLs
zcat /home/USER/domains/DOMAIN/logs/Feb-2019.tar.gz | awk -F\" '{print $4}' | sort | uniq -c | sort -nr | head -20

joostvanveen

# Delete log files containing a certain string. 
# Handy to clean up Magento reports after committing a fix.
find var/report/* -exec grep -q 'A non-numeric value encountered' '{}' \; -delete

# If you ant to chcek first:
find var/report/* -exec grep -q 'A non-numeric value encountered' '{}' \; -find

joostvanveen

# Delete all cached images older than 365 days
find ~/public/media/catalog/product/cache/* -type f -atime +365 -exec rm {} \;

# Remove product images that are not present in the database
magerun media:images:removeorphans

joostvanveen

# Display the top 20 visitors by IP address from a single log file
awk '{print $2}' /var/log/nginx/access.log | sort | uniq -c | sort -nr | head -20

# Display the top 40 visitors by IP address from all rotated gzipped log files
zcat /var/log/nginx/access.log* | awk '{print $2}' | sort | uniq -c | sort -nr | head -40

# Find IP adrdresses for all users that created a custoter account for Magento
zcat /var/log/nginx/access.log* | awk -v OFS='\t' '/\/customer\/account\/createpost?/ {print $2, $21}' | sort | uniq -c | sort -nr | head -40

joostvanveen

## Split large SQL files into separate files for each table if every tabel starts with a 'DROP TABLE IF EXISTS' statement
csplit -k $PWD/filename.sql  '/^DROP TABLE IF EXISTS .*/' '{900}'

## Split large SQL files into separate files for each table if every tabel starts with a 'CREATE TABLE' statement
csplit -k $PWD/filename.sql  '/^CREATE TABLE .*/' '{900}'

joostvanveen

-- STEP 1: Set @database, @old_prefix and @new_prefix
-- STEP 2: Execute the query
-- STEP 3: Copy the string that was genereated by the query
-- STEP 4: Run the generated query to rename all tables
-- STEP 5: Use you Party Gun to make it rain!

SET SESSION group_concat_max_len = 999999999;
SET @database   = "databasename";
SET @old_prefix = "ma2_"; 
SET @new_prefix = "";

joostvanveen

## NOTE: when finding file by time, use one of the following flags:
# ctime : the time a file was changed, in hours. On most systems this timestamp cannot be altered by any user, so it is fairly reliable
# atime : the time a file was last accessed, in hours. This is also set by the system.
# mtime : the modified time of a file, in hours. This can be set by any user. So if you are looking for mailcious files, never use this flag.
# cmin  : same as mtime, but in minutes.
# amin  : same as atime, but in minutes.
# mmin  : same as mtime, but in minutes.

# Find all files in current directory changed more than 8 hours ago (480 minutes)
find $PWD -mindepth 1 -type f -cmin +480

joostvanveen

#!/usr/bin/env bash

# Magento 2 Deployment Script
# Author: Joost van Veen

usage="$(basename "$0") [-h] source-directory destination-directory folders-to-sync files-to-sync
Rsync folders and files from one folder to another

where:
    -h  show this help text