Tunnel MySQL through SSH

Recently I had the challenge, that the MySQL database of one of the Magento shops I maintain wasn’t accessible directly through the Net. The access was restricted to the Webserver only. So I had to tunnel MySQL through SSH. And that worked like that: edit the ssh config (~/.ssh/config) on the remote Webserver Host * […]

Save typing effort for complex MySQL queries with custom variables or procedures

To save time and because it’s not that smart to repeatedly retype the same things multiple times, you can user custom variables in MySQL like that: SET @store := 1, @name_id := 71, @short_desc_id := 72, @desc_id := 73; SELECT CPE.sku AS sku, CPEVN.value AS name, CPEVSD.value AS short_description, CPEVD.value AS description FROM catalog_product_entity AS […]

Drop all tables from MySQL database

From time to time I stumbled over the case, that I had to drop all tables from a MySQL database. In phpMyAdmin or other GUI-based tools – in general – you only have the possibility to drop the whole database. But then you have to recreate it from the scratch.  Or you have to click-drop each table […]

Simulate a MySQL query before running it

It is possible to Simulate a MySQL query before running it with the use of  MySQL-Transactions and the build in ROLLBACK-Feature of MySQL: For example: START TRANSACTION; SELECT * FROM nicetable WHERE somthing=1; UPDATE nicetable SET nicefield=’VALUE’ WHERE somthing=1; SELECT * FROM nicetable WHERE somthing=1; #check COMMIT; # or if you want to reset changes ROLLBACK; […]

Find duplicate entries in a MySQL Table

You can use the following query to get all duplicated records of a MySQL table having equal values for the respective field: SELECT fieldname, COUNT(*) FROM tablename GROUP BY fieldname HAVING COUNT(*) > 1 This query will give you a list of all the entries having the same value for fieldname. It also will show […]

Get table or DB size im Megabyte

Sometimes it is really useful to know how large a certain database table has become. With the following SQL statement you get a list of all tables of the currently connected database with their rounded size in MB: SELECT table_name AS “Table”, round(((data_length + index_length) / 1024 / 1024), 2) “Size in MB” FROM information_schema.TABLES […]

Get current user, mysql version and other status information

To get information about the currently connected user and more MySQL status information, you can call the following command if you are already logged in: This will give you an output like that: ————– mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1 Connection id: 1044395 Current database: Current user: niceguy@localhost SSL: Not in […]