常用MySQL命令

• 将某个字段/binary类型转换为utf8字符集的方法

  CONVERT(job_meta USING utf8) COLLATE utf8_unicode_ci

• 统计BINLOG中EVENT分布

  # 按照时间段解析BINLOG
  mysqlbinlog -vv --start-datetime "2022-04-01 03:02:00" --stop-datetime "2022-04-01 03:08:00" mysql-bin.002572 >2572.txt
  
  # 将BINLOG过滤指定EVENT名到文件中
  cat 2572.txt | grep -E "^SET TIMESTAMP|### (INSERT|UPDATE|DELETE)" >table.txt
  # 查看单个事务的更改行数
  uniq -c table.txt
  # 统计某个时间段EVENT分布情况
  sort table.txt | uniq -c | sort -n -k1

• 删除非系统库表

  select 
  	concat('drop database ',schema_name,';') 
  from information_schema.schemata 
  where 
      schema_name not in ('dba','mysql','performance_schema','sys','tmpdir','mysql','information_schema','binlog')

• 查看索引是否被使用(需要到主从多台机器上都查一下)

  SELECT  
  	object_type,  object_schema,  object_name,  index_name,  count_star,  count_read,  COUNT_FETCH  
  FROM  PERFORMANCE_SCHEMA.table_io_waits_summary_by_index_usage 
  where 
  	object_schema='ying99_pomodel' and object_name='portfolio_composition';

• 获取所有connection的某个 variables 状态

  select
      p.id, p.user, v.VARIABLE_NAME, v.VARIABLE_VALUE, p.time, p.command
  from
      information_schema.processlist p
      left join performance_schema.threads t on p.id = t.PROCESSLIST_ID
      left join  `performance_schema`.`variables_by_thread` v on t.thread_id = v.thread_id
  where
      v.VARIABLE_NAME like 'auto_increment_increment' and v.VARIABLE_VALUE = '1' ;

• 跳过复制错误（GTID）

  SET GTID_NEXT = '127a3424-847c-11ea-90a6-00163e104a66:2743448';
  BEGIN; COMMIT;
  SET SESSION GTID_NEXT = 'AUTOMATIC';
  START SLAVE;

• 统计单IP连接使用量

  select 
      User,substring_index(Host,':',1) as ip,count(1) as conn_num 
  from information_schema.processlist 
  group by ip having conn_num>=50 
  order by conn_num desc;

• 统计大表

  select 
      table_schema,table_name, 
      (data_length + index_length) /(1024 * 1024 * 1024) as size_G 
  from information_schema.tables 
  order by size_G desc limit 30;

• mydumper常用过滤库表

  /usr/local/bin/mydumper -h 10.242.149.120 -u zft_db_dump -p zft_db_dump -P 3306 --insert-ignore --load --lhost 127.0.0.1 --luser fmha_admin --lpassword ******* --lport 3306 -t 5 -v 1 --less-locking -c -o /data1/fmha_dump_data/2019053110073872 --enable-binlog --progressfile /data1/fmha_dump_data/2019053110073872/progress.log --logfile /data1/fmha_dump_data/2019053110073872/mydumper.log -x '^(?!(mysql.user$|mysql.db$|mysql.hosts$|mysql.tables_priv$|mysql.columns_priv$|mysql.procs_priv$|mysql.proxies_priv$|mysql.gtid_executed$|mysql.slave_master_info$|mysql.slave_relay_log_info$|mysql.slave_worker_info$|sys.*|information_schema.*|performance_schema.*))'

• pt-online-schema-change工具常用参数

  pt-online-schema-change -h 127.0.0.1 -P 30002 -u ying99-testing -p ying99-testing --charset=utf8 --max-load="Threads_running:25" --critical-load="Threads_running:100" --progress="time,1" --nocheck-replication-filters --retries=10 --chunk-time=${CHUNKTIME} --chunk-size=${CHUNKSIZE} --chunk-size-limit=${CHUNKSIZELIMIT} --alter="${ALTERSQL}" D=${curr_alter_db},t=${curr_alter_table} --recursion-method=none --exec --print

• 统计库表内存用量

  SELECT SUBSTRING_INDEX(event_name,'/',2) AS code_area, sys.format_bytes(SUM(current_alloc)) AS current_alloc FROM sys.x$memory_global_by_current_bytes GROUP BY SUBSTRING_INDEX(event_name,'/',2) ORDER BY SUM(current_alloc) DESC;

• pt-kill

  pt-kill --match-user '^(?:eossvc-user)|(?:core-debezium-user)$' --victims all --interval 5 -v -u root --ask-pass -h 127.0.0.1 --print --kill

• 找到MetaData LOCK

  SELECT
      locked_schema,
      locked_table,
      locked_type,
      waiting_processlist_id,
      waiting_age,
      waiting_query,
      waiting_state,
      blocking_processlist_id,
      blocking_age,
      substring_index(sql_text,"transaction_begin;" ,-1) AS blocking_query,
      sql_kill_blocking_connection
  FROM
      (
          SELECT
              b.OWNER_THREAD_ID AS granted_thread_id,
              a.OBJECT_SCHEMA AS locked_schema,
              a.OBJECT_NAME AS locked_table,
              "Metadata Lock" AS locked_type,
              c.PROCESSLIST_ID AS waiting_processlist_id,
              c.PROCESSLIST_TIME AS waiting_age,
              c.PROCESSLIST_INFO AS waiting_query,
              c.PROCESSLIST_STATE AS waiting_state,
              d.PROCESSLIST_ID AS blocking_processlist_id,
              d.PROCESSLIST_USER as blocking_processlist_user, 
              d.PROCESSLIST_TIME AS blocking_age,
              d.PROCESSLIST_INFO AS blocking_query,
              concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
          FROM
              performance_schema.metadata_locks a
          JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA
          AND a.OBJECT_NAME = b.OBJECT_NAME
          AND a.lock_status = 'PENDING'
          AND b.lock_status = 'GRANTED'
          AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
          AND a.lock_type = 'EXCLUSIVE'
          JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID
          JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID
      ) t1,
      (
          SELECT
              thread_id,
              group_concat(   CASE WHEN EVENT_NAME = 'statement/sql/begin' THEN "transaction_begin" ELSE sql_text END ORDER BY event_id SEPARATOR ";" ) AS sql_text
          FROM
              performance_schema.events_statements_history
          GROUP BY thread_id
      ) t2
  WHERE
      t1.granted_thread_id = t2.thread_id \G

• mysqldump 命令

  mysqldump --single-transaction --complete-insert --no-create-info --extended-insert=false -h 

• 获取长时间没有释放的事务

  select 
      p.id, p.user, p.host, IF(p.DB IS NULL, '', p.DB) as db, timestampdiff(SECOND, t.trx_started, now()) as time
  from 
  	information_schema.PROCESSLIST p JOIN information_schema.INNODB_TRX t ON p.id = t.trx_mysql_thread_id
  where 
  	 t.trx_started<= date_sub(now(), interval 60 second);

‍