MySQL (9)


Setting up MySQL on Mac OS

A few notes for next time… and this may help someone else you never know! Even after doing this multiple times it’s easy to forget a step and wonder why things aren’t working. After moving all my data to a new mac, i need to install mysql. Install mysql. (Grab a .dmg from mysql.com) Move […]

Read more...



Weighted search results using MySQL LIKE

  • May 7, 2013
  • MySQL

Using LIKE, instead of the usual MATCH AGAINST using FULLTEXT. Here’s the query i’ve made to search events: [code] SELECT id, title, date_from, IF (`title` LIKE ‘%$searchTerm%’, 20, IF (`content` LIKE ‘%$searchTerm%’, 10, 0)) + IF (`content` LIKE ‘%$searchTerm%’, 10, 0) + IF (`venue_name` LIKE ‘%$searchTerm%’, 5, 0) + IF (`venue_address` LIKE ‘%$searchTerm%’, 5, 0) […]

Read more...



Get MySQL Passwords from Plesk (bash/command line)

Quick bash script to grep out MySQL passwords from Plesk’s PSA database. Script: [code language=”bash”]#!/bin/bash clear; echo "Searching MySQL user tables for: $1"; echo mysql psa -uadmin -p`cat /etc/psa/.psa.shadow` -e ‘SELECT domains.name AS domain_name, data_bases.name AS database_name, db_users.login, accounts.password FROM data_bases, db_users, domains, accounts WHERE data_bases.dom_id = domains.id AND db_users.db_id = data_bases.id AND db_users.account_id = […]

Read more...



Get Protected Directory Logins from Plesk

Quick bash script to grep out protected directory logins from Plesk’s PSA database. Script: [code language=”bash”]#!/bin/bash clear; you=`who am i | cut -d’ ‘ -f1`; echo -e "Hello $you, looking in the protected dirs password list for \033[33;1m$1\033[0m eh?"; echo mysql psa -uadmin -p`cat /etc/psa/.psa.shadow` -e ‘select pd.login, a.password, d.name, dirs.path from accounts a, pd_users […]

Read more...



Get FTP Passwords from Plesk

Quick bash script to grep out ftp passwords from Plesk’s PSA database. Script: [code language=”bash”]#!/bin/bash clear; echo "Searching the FTP password list for: $1"; echo mysql psa -uadmin -p`cat /etc/psa/.psa.shadow` -e ‘SELECT REPLACE(sys_users.home,"/home/httpd/vhosts/","") AS domain, sys_users.login,accounts.password FROM sys_users LEFT JOIN accounts on sys_users.account_id=accounts.id ORDER BY sys_users.home ASC’ | grep $1;[/code] Usage: [code gutter=”false”][root@mail bin]# grepftp […]

Read more...



get protected directory user and password

one query to grab the user and pass from the Plesk psa database: SELECT pd.login, a.password, d.name, dirs.path FROM accounts a, pd_users pd, protected_dirs dirs, domains d WHERE dirs.path = ‘admin’ AND a.id = pd.account_id AND pd.pd_id = dirs.id AND dirs.dom_id = d.id AND d.id = DOMAIN_ID;

Read more...



MySQL: Find and Replace

i accidentally imported a load of customers email addresses with quotes and commas in, so to remedy this, a short query: UPDATE customers SET email = REPLACE(REPLACE(email, ‘"’, ”), ‘,’ ,”) the first REPLACE replaces double quotes with nothing, and the second REPLACE replaces commas with nothing.

Read more...



sorting on two price fields in MySQL

Sorting on two price fields in MySQL was a confusing thought at first, and was tempted to query then use php to do it. No! I needed a way to select my products and order them by price. But we have two prices, a “price” and “price_offer”, so we need to compare these values so […]

Read more...



How to reset mysql root password

Ever forgotten your mysql root password? Indeed. Unless you know how to reset the mysql root password, you may find yourself either stuck, or re-installing mysql (as i did a few times many moons ago before i knew any better!) So lets reset mysql root password (seo-tastic I know) First thing to do is to […]

Read more...