DanBrother的部落格

Extracts, orders, and then prints grants for MySQL user accounts (pt-show-grants)
# Extracts, orders, and then prints grants for MySQL user accounts
pt-show-grants --host 10.0.1.120 -u admin --ask-pass --port 3306 > testdb-grants.sql
 

MySQL Stored Procedure - Nested Loop Example
DELIMITER $$
CREATE PROCEDURE sp_fix_dup_nickname()
BEGIN   
    DECLARE outer_done, inner_done BOOLEAN DEFAULT FALSE;    
    DECLARE current_id varchar(30);    
    DECLARE current_nickname varchar(60);
    DECLARE current_update_time timestamp(6);
    DECLARE current_ranking int;
    DECLARE new_nickname varchar(60);
    DECLARE outer_cur CURSOR FOR select nickname from tmp_dup_nickname;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET outer_done = TRUE;

Metabase H2 DB Migration to MySQL 5.7
#### On Aurora New MySQL ####################
create database metabase charset utf8mb4;
create user `metabase_app`@`192.168.%` identified by 'xxxxxx';
grant all on metabase.* to `metabase_app`@`192.168.%`;

MySQL 5.7 :  Steps for Upgrading Percona-Toolkit to the Latest Version
pt-online-schema-change --version
>>
pt-online-schema-change 2.2.20

MySQL Slave - Skip Replication for A Particular Table
mysql> stop slave sql_thread;
mysql> change replication filter Replicate_Wild_Ignore_Table=('mydb.my_unused_table1');
mysql> start slave sql_thread;

check_table_fragmentation.sql
>>
MySQL > source check_table_fragmentation.sql
SELECT table_schema, table_name, CONCAT(ROUND((data_free / 1024 / 1024),2),'MB') as data_free
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema','sys')
AND ENGINE LIKE 'InnoDB' AND data_free > 100 * 1024 * 1024;
+------------------+-----------------------------------+------------+
| table_schema     | table_name                        | data_free  |
+------------------+-----------------------------------+------------+
| test_usr        | test_frag_table                        | 15002.00MB |
# Performance Table Defragmentation Using Percona's toolket - pt-online-schema-change
nohup  pt-online-schema-change -u$dbuser  --socket=$socket_file --critical-load="Threads_running=100" \
--set-vars innodb_lock_wait_timeout=60 \
--execute --alter "ENGINE=InnoDB;" --charset=utf8 --nocheck-replication-filters --recursion-method=processlist D=test_usr,t=test_frag_table 2>&1 &

cat log_file_viewer.sh
>>
#################################
# Multiple Log File Viewer (Linux Bash)
# Programmed By DanBrother
#################################
#!/bin/bash
function pause(){
   read -p "$*"
}

There're MySQL global variable settings that will help for improving the speed of SQL apply in slave db.
That would solve the issue of slave lagging.
slave_rows_search_algorithms comes to the rescue.
slave_rows_search_algorithms
default: TABLE_SCAN,INDEX_SCAN
improvement: INDEX_SCAN,TABLE_SCAN,HASH_SCAN
select @@global.slave_rows_search_algorithms;