Skip to content

Instantly share code, notes, and snippets.

View datacharmer's full-sized avatar

Giuseppe Maxia datacharmer

598 followers · 8 following
View GitHub Profile
@datacharmer
datacharmer / schema_comparison
Last active December 19, 2015 10:59
Poor man's schema comparison Given two database definitions, it displays the difference in their schemas
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
my @dsn;
$dsn[0] = shift or help('no dns1');
$dsn[1] = shift or help('no dns1');
my @dbh;
@datacharmer
datacharmer / partition_helper
Last active May 30, 2018 18:53
This script creates an ALTER TABLE statement to add or reorganize partitions for MySQL 5.1 or later.For usage: partition_helper --help
#!/usr/bin/perl
# The MySQL Partitions helper
# Copyright (C) 2008-2015 Giuseppe Maxia
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
@datacharmer
datacharmer / purge process list from slow queries or idle connections
Last active March 2, 2020 15:49
These SQL procedures examine the process list and kill the queries that take more than N seconds to execute, or the connections idles for more than N seconds. Requires MySQL 5.1 or later. The companion events use the event scheduler to clean the process list every 30 seconds
-- SQL
drop procedure if exists purge_slow_queries;
drop procedure if exists purge_idle_connections;
drop event if exists auto_purge_slow_queries;
drop event if exists auto_purge_idle_connections;
delimiter //
create procedure purge_idle_connections()
@datacharmer
datacharmer / sample.txt
Last active June 8, 2019 20:41
sample master-master setup
# Configuration node 1
[mysqld]
user = gmax
port = 25617
socket = /tmp/mysql_sandbox25617.sock
basedir = /home/gmax/opt/mysql/8.0.16
datadir = /home/gmax/sandboxes/multi_msb_8_0_16/node1/data
tmpdir = /home/gmax/sandboxes/multi_msb_8_0_16/node1/tmp
pid-file = /home/gmax/sandboxes/multi_msb_8_0_16/node1/data/mysql_sandbox25617.pid
bind-address = 127.0.0.1
@datacharmer
datacharmer / clone.sh
Last active August 13, 2019 10:34
test MySQL 8.0.17 CLONE
#!/bin/bash
set -x
dbdeployer deploy single 5.7.26 --master --sandbox-directory=master_5_7_26 --port=18000 --db-password=different --db-user=different
dbdeployer deploy single 8.0.17 --master --sandbox-directory=master_8_0_17 --port=18001 --db-password=different --db-user=different
dbdeployer deploy single 8.0.17 --master --sandbox-directory=slave_8_0_17 --port=18002
~/sandboxes/master_5_7_26/use -t < fill.sql
du -sh ~/sandboxes/*/data
@datacharmer
datacharmer / tutorial Percona Live EU 2019
Last active September 28, 2019 14:23
tutorial Percona Live EU 2019
Use a Linux or Mac computer
Download at least the following 2 files:
1) https://github.com/datacharmer/dbdeployer/releases/tag/v1.39.0 . (~ 5MB)
2a) https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.17-linux-x86_64-minimal.tar.xz (Linux 42 MB)
or
2b) https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.17-macos10.14-x86_64.tar.gz (Mac 148 MB)
@datacharmer
datacharmer / dbdeployer demo
Last active June 16, 2020 09:26
dbdeployer demo
### Dockerfile
FROM ubuntu:18.04
LABEL MAINTAINER Giuseppe Maxia <[email protected]>
RUN groupadd -r msandbox \
&& useradd -m -s /bin/bash -g msandbox msandbox