Skip to content

Tag: MySQL

Writing a Magento 2 module

[This post is a work in progress.] Between the more […]

Magento 2 MVC

C = Controller: URL routing V = Block/Template/Layout M = Model Read More »

G.B.D. Calamari Joomla and phpBB upgrade

As the chairman of G.B.D. Calamari, a Dutch student diving association, it falls to me to upgrade an aging Joomla configuration that is a relict of the last time that the board was chaired by somebody with web-development expertise (Joris van Alphen). This happened to be during a time when Joomla 1.5.20 was cutting-edge. It no longer is, so, although the website still looks nifty enough for this day and age (in my ageing eyes, at least), a software upgrade is long overdue. Read More »

Decreasing shutdown time of MySQL with large transaction log file

If you need/have a large transaction log file, a shut down can take a while. This post lists how you can force a flush before you do so, decreasing shut down time. Read More »

Dumping MySQL users and grants

MySQL stores its users and grants in a very annoying, non-clonable format. I found this post, describing how you can extra them. Read More »

Trying to reduce MySQL InnoDB disk usage after major reduction of data

So, two days ago, I tried to shrink my MediaWiki database and it almost worked, except the MySQL process wouldn't shrink along with it. Read More »

Shrinking/compressing a MediaWiki database

As of late, I haven't had a lot of time to chase after spammers, so – despite of anti-spam captchas and everything – a couple of my wikis have been overgrowing with spam. One after the other I've been closing them down to anonymous edits, even closing down user registration alltogether, but some a little too late. Read More »

Converting all tables in MySQL DB to InnoDB

#!/bin/bash exit 1 dbname="eorder" echo 'SHOW TABLES;' | mysql $dbname | awk '!/^Tables_in_/ {print "ALTER TABLE `"$0"` ENGINE = InnoDB;"}' | column -t echo 'SHOW TABLES;' | mysql $dbname | awk '!/^Tables_in_/ {print "ALTER TABLE `"$0"` ENGINE = InnoDB;"}' | column -t | mysql $dbname Read More »

Listing MySQL table sizes

This query lists the sizes of all tables in MySQL: SELECT TABLE_SCHEMA, TABLE_NAME, CONCAT(ROUND(data_length / ( 1024 * 1024 ), 2), 'MB') DATA, CONCAT(ROUND(data_free / ( 1024 * 1024 ), 2), 'MB') FREE from information_schema.TABLES where TABLE_SCHEMA NOT IN ('information_schema','mysql', 'performance_schema'); Read More »

Converting a MySQL database from latin1 to utf8

mysqldump dbname > dbname_bak_before_messing_with_it.sql mysqldump --default-character-set=latin1 --skip-set-charset dbname > dump.sql sed -r 's/latin1/utf8/g' dump.sql > dump_utf.sql mysql --execute="DROP DATABASE dbname; CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;" mysql --default-character-set=utf8 dbname < dump_utf.sql Read More »

Getting a better MySQL prompt

When you want to see which user you are and which database you're working with, put this in .my.cnf: prompt=(\\u@\\h) [\\d]>\\_ Read More »