# 常用MySQL命令 LLMS 索引： [llms.txt](/llms.txt) --- - **将某个字段/binary类型转换为utf8字符集的方法** ```sql CONVERT(job_meta USING utf8) COLLATE utf8_unicode_ci ``` - **统计BINLOG中EVENT分布** ```shell # 按照时间段解析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 ``` - **删除非系统库表** ```sql 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') ``` - **查看索引是否被使用(需要到主从多台机器上都查一下)** ```mysql 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 状态** ```sql 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）** ```mysql SET GTID_NEXT = '127a3424-847c-11ea-90a6-00163e104a66:2743448'; BEGIN; COMMIT; SET SESSION GTID_NEXT = 'AUTOMATIC'; START SLAVE; ``` - **统计单IP连接使用量** ```mysql 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常用过滤库表** ```shell /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工具常用参数** ```shell 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 ``` - **统计库表内存用量** ```sql 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** ```shell 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** ```mysql 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 命令 ```shell mysqldump --single-transaction --complete-insert --no-create-info --extended-insert=false -h ``` - **获取长时间没有释放的事务** ```sql 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); ``` ‍