Category: MySQL
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 […]
Dump wildcard selected tables
This little tutorial shows how to dump wildcard selected tables from a MySQL database. That’s useful if you don’t want to dump the whole database. Background Recently I had to export a few selected tables from a very huge database. For that I could have run a query like that to export only the required […]
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 […]
Create UTF-8 Database
To crate a UTF-8 database: CREATE DATABASE sns_typo3 DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;