MySql query log to table or file

For debugging it’s often useful to see all queries and since you usually don’t want to save all this data, it’s nice to be able to turn it on and off efficiently.

You can log to file but also to a table in the mysql database.

While you can turn this on and off by updating the my.cnf file, you can also do it via mysql commands:

SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';

One nice thing about logging to a table is that you can search for specific things, see examples below.

USE mysql;
SELECT * FROM general_log;
SELECT * FROM general_log WHERE argument LIKE "DELETE%";

You probably want to clear the log periodically and can do so with:

TRUNCATE general_log;

Then simply turn OFF when finished.

SET GLOBAL general_log = 'OFF';

If you would rather log to file (the default), use:

SET GLOBAL log_output = "FILE";
SET GLOBAL general_log_file = "/path/to/your/logfile.log";
SET GLOBAL general_log = 'ON';

Swap column values in MySql (via temp variables)

If you want to swap values in one query in MySql without a temporary table, you can use variables to hold values that otherwise would be changed before you can use them.

E.g., swap start_date and end_date via:

UPDATE mydates SET start_date=@tmp:=start_date, start_date=end_date, end_date=@tmp WHERE start_date > end_date

Install amqp-ext PHP extension to Apache in Docker

I wanted to add RabbitMQ support to my PHP app and prefer to use the PECL amqp package:

This extension can communicate with any AMQP spec 0-9-1 compatible server, such as RabbitMQ, OpenAMQP and Qpid, giving you the ability to create and delete exchanges and queues, as well as publish to any exchange and consume from any queue.

Using pecl install won’t install all the dependencies. There are various options to how to solve this including fetching from Git repos, however this is the one that worked for me and seemed the most straight-forward (ymmv). See below for code used in a Dockerfile with base image FROM php:7.1.14-apache which uses debian:jessie, so the native commands should work in similar Debian/Ubuntu linux distros.

RUN curl http://in.archive.ubuntu.com/ubuntu/ubuntu/pool/universe/libr/librabbitmq/librabbitmq4_0.8.0-1_amd64.deb -o librabbitmq4_0.8.0-1_amd64.deb
RUN curl http://in.archive.ubuntu.com/ubuntu/ubuntu/pool/universe/libr/librabbitmq/librabbitmq-dev_0.8.0-1_amd64.deb -o librabbitmq-dev_0.8.0-1_amd64.deb
RUN dpkg -i librabbitmq4_0.8.0-1_amd64.deb
RUN dpkg -i librabbitmq-dev_0.8.0-1_amd64.deb
RUN pecl install amqp-1.9.3 && docker-php-ext-enable amqp

Note if you do use this in a Dockerfile, you can combine some of the statements to optimize layers. You also may need to update the lib version if it’s updated or if you change the PECL version to install.

Redis database size and key info

You can use INFO to get total memory size and a lot of other useful bits but if you just want to know the size of a single database or other info about database-specific keys, it can be a bit tricky.

see: https://stackoverflow.com/questions/7638542/redis-show-database-size-size-for-keys

tldr; in many cases, all you might need is:

redis-cli --bigkeys

To see what’s going on real-time:

redis-cli MONITOR

Yii2 fix “Malformed UTF-8 characters” error with master/slave db

You need to specify charset in slave config, e.g.,

'slaveConfig' => [
  'charset' => 'utf8',
  ...