lefred’s gists

lefred / mysql_gtid_functions.txt

Last active January 17, 2024 08:07

	SET GLOBAL log_bin_trust_function_creators = 1;
	USE sys;

	DROP FUNCTION IF EXISTS GTID_IS_EQUAL;
	DROP FUNCTION IF EXISTS GTID_IS_DISJOINT;
	DROP FUNCTION IF EXISTS GTID_IS_DISJOINT_UNION;
	DROP FUNCTION IF EXISTS GTID_NORMALIZE;
	DROP FUNCTION IF EXISTS GTID_UNION;
	DROP FUNCTION IF EXISTS GTID_INTERSECTION;
	DROP FUNCTION IF EXISTS GTID_SYMMETRIC_DIFFERENCE;

lefred / sys_replication_status.sql

Created October 24, 2020 08:02

	use sys;
	DROP VIEW IF EXISTS replication_status;
	CREATE
	ALGORITHM = MERGE
	SQL SECURITY INVOKER
	VIEW replication_status
	AS
	SELECT
	concat(conn_status.channel_name, ' (', worker_id,')') AS channel,
	conn_status.service_state AS io_state,

lefred / addition_to_sys_GR.sql

Last active November 26, 2022 00:25

	USE sys;

	DELIMITER $$

	CREATE FUNCTION my_id() RETURNS TEXT(36) DETERMINISTIC NO SQL RETURN (SELECT @@global.server_uuid as my_id);$$

	-- new function, contribution from Bruce DeFrang
	CREATE FUNCTION gr_member_in_primary_partition()
	RETURNS VARCHAR(3)
	DETERMINISTIC

lefred / check_async_mysql.sh

Last active February 19, 2023 11:24

Consul Asynchronous Slave Check

	# script used in consul to check if mysql is primary master and asynchronous slave
	# v.0.1 - lefred 2018-02-16

	SLAVEOFDC="dc2"
	SLAVEUSER="async_repl"
	SLAVEPWD="asyncpwd"

	# check if we are the primary one
	ROLE=$(mysql -h 127.0.0.1 -BNe "select MEMBER_ROLE from performance_schema.replication_group_members where MEMBER_HOST=@@hostname")

lefred / check_mysql.sh

Created February 15, 2018 21:07

MySQL InnoDB Cluster Consul check

	# script used in consul
	# the following addition to sys is required:
	# https://gist.github.com/lefred/153448f7ea0341d6d0daa2738db6fcd8
	# v.0.1 - lefred 2018-02-14


	read -r mysql_primary mysql_readonly mysql_tx mysql_cert <<<$(mysql -h 127.0.0.1 -P 6446 -BNe "select * from sys.gr_member_routing_candidate_status")

	if [[ "${mysql_primary}" == "YES" ]] && [[ "${mysql_readonly}" == "NO" ]]
	then

lefred / addtion_to_sys_8.0.2.sql

Last active March 7, 2019 15:26

Addition to MySQL SYS table 8.0.2 for routing GR

	USE sys;

	DELIMITER $$

	CREATE FUNCTION my_id() RETURNS TEXT(36) DETERMINISTIC NO SQL RETURN (SELECT @@global.server_uuid as my_id);$$

	-- previous obsolete function
	-- CREATE FUNCTION gr_member_in_primary_partition()
	-- RETURNS VARCHAR(3)
	-- DETERMINISTIC

lefred / addition_to_sys.sql

Last active August 26, 2024 00:43

MySQL Group Replication extra functions and views to sys schema

	USE sys;

	DELIMITER $$

	CREATE FUNCTION IFZERO(a INT, b INT)
	RETURNS INT
	DETERMINISTIC
	RETURN IF(a = 0, b, a)$$

	CREATE FUNCTION LOCATE2(needle TEXT(10000), haystack TEXT(10000), offset INT)

lefred / maskit.sh

Last active February 19, 2021 07:59

maskit

lefred / gist:1343bc190ba3de58c4e484fac4ff34b3

Created August 10, 2016 07:43

STR_TO_DATE MySQL 5.7

	mysql2 mysql> select str_to_date('9','%m');
	+-----------------------+
	\| str_to_date('9','%m') \|
	+-----------------------+
	\| NULL \|
	+-----------------------+
	1 row in set, 1 warning (0.00 sec)

	mysql2 mysql> show variables like 'sql_mode'\G
	************************* 1. row *************************

lefred / max repl throughput galera

Created March 1, 2016 13:29

	SET GLOBAL wsrep_desync=on;
	FLUSH TABLES WITH READ LOCK;
	...wait until the queue rises to be quite high, about 20.000
	UNLOCK TABLES; use test;
	SELECT sum(trx) as transactions, sum(duration) as time,
	IF(sum(duration) < 5, 'DID NOT TAKE LONG ENOUGH TO BE ACCURATE',
	ROUND(SUM(trx)/SUM(duration)))
	AS transactions_per_second
	FROM
	(SELECT VARIABLE_VALUE * -1 AS trx, null as duration

Frédéric Descamps lefred