Skip to content

Tag: MySQL

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 »

Taking control of the wpautop filter

Wordpress does automatic paragraph formatting using the wpautop filter, some PHP code originally developed by Matt Mullenweg. For most of the time that this blog has existed, I've disabled the wpautop filter using the following two lines in my theme's functions.php file: Read More »