Mysql 常用操作（随时更新 自用）

每次用完就忘～

• 查看慢sql 相关配置：show variables like ‘slow%’;
• 慢sql 时间设置：set long_query_time=1; # 超过1毫秒就是慢sql
• 打开 optimizer_trace，只对本线程有效：set optimizer_trace=‘enabled=on’;
• 查看 optimizer_trace 输出：select * from information_schema.OPTIMIZER_TRACE\G
• 修改表名：alert table old_name rename as new_name;
• 查询 MDL 锁：select blocking_pid from sys.schema_table_lock_waits; 先设置 UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' where name='wait/lock/metadata/sql/mdl';
• 查询行锁：select * from sys.innodb_lock_waits\G
• 查看binlog配置：show variables like ‘%log_bin%’; 开启binlog：[mysqld] 追加 log-bin=mysql-bin server-id=1
• 查看binlog文件列表：show binary logs;
• 查看正在写的binlog文件：show binlog events;
• 查看指定binlog文件：show binlog events in ‘mysql-bin.000001’;
• 查询每次IO耗时：performance_schema.file_summary_by_event_name; redologwait/io/file/innodb/innodb_log_file，binlogwait/io/file/sql/binlog
• 开启MRR： set optimizer_switch=‘mrr_cost_based=off’;
• 开启BKA：set optimizer_switch=‘mrr=on,mrr_cost_based=off,batched_key_access=on’;
• 双1设置：set global innodb_flush_log_at_trx_commit=1; set global sync_binlog=1;
• 导出sql文件：mysqldump -h -P -u -p --add-locks=0 --no-create-info --single-transaction --set-gtid-purged=OFF db1 t --where=“a>900” --result-file=/home/chensong/t.sql
• 执行sql文件：mysql -u -p db2 -e “source /home/chensong/t.sql”
• 服务端导出csv文件：select * from db1.t where a>900 into outfile ‘/server_tmp/t.csv’;
• 服务端执行csv文件：load data infile ‘/server_tmp/t.csv’ into table db2.t;
• 创建用户：create user ‘ua’@‘%’ identified by ‘pa’; 授权：grant all privileges on db1.* to ‘ua’@‘%’ with grant option; 收回：revoke all privileges on db1.* from ‘ua’@‘%’;
• 查看binlog：mysqlbinlog -vv master.000001
• 设置双1配置：set global innodb_flush_log_at_trx_commit=0; set global sync_binlog=0;