MySQL常用命令

最新推荐文章于 2022-07-21 11:13:33 发布

laosu8

最新推荐文章于 2022-07-21 11:13:33 发布

阅读量192

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/ppp00/article/details/103816973

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

--查看每个线程占用多少内存，然后乘以正在运行的线程（也就是排查sleep的）。
SELECT ( ( @@read_buffer_size
+ @@read_rnd_buffer_size
+ @@sort_buffer_size
+ @@join_buffer_size
+ @@binlog_cache_size
+ @@thread_stack
+ @@max_allowed_packet
+ @@net_buffer_length )
) / (1024*1024) AS MEMORY_MB;

--查看MySQL全局占用多少内存
select (@@innodb_buffer_pool_size
+@@innodb_log_buffer_size
+@@key_buffer_size) / 1024 /1024 AS MEMORY_MB;

--查看performance_schema占用多少内存
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;

--查看 memory 存储引擎占用多少内存
select sum(max_data_length)/1024/1024 as MEMORY_MB from information_schema.tables where engine='memory';

--全表扫描sbtest1
select count(*) from (select * from sbtest1) a;

--查看innodb_buffer_pool情况
select sum(POOL_SIZE),sum(free_buffers),sum(DATABASE_PAGES) from information_schema.INNODB_BUFFER_POOL_STATS;

打开PS内存监控
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'memory/%';
10:06
查看内存占用：
select event_name, current_alloc, high_alloc from sys.memory_global_by_current_bytes where current_count > 0;
临时表使用：
show global status like '%tmp%';

select event_name, SUM_NUMBER_OF_BYTES_ALLOC from performance_schema.memory_summary_by_thread_by_event_name order by SUM_NUMBER_OF_BYTES_ALLOC desc limit 20;

select event_name,SUM_NUMBER_OF_BYTES_ALLOC from performance_schema.memory_summary_global_by_event_name order by SUM_NUMBER_OF_BYTES_ALLOC desc LIMIT 10;

--复制相关
change master to master_host='xxx',master_user='xxx',master_password='xxx',master_port=3306,master_log_file='mysql-bin.000004',master_log_pos=631 for channel'ch01';
change replication filter replicate_do_db=(db1);
change replication filter REPLICATE_DO_TABLE = (master1.t1),REPLICATE_REWRITE_DB = ((master1,slave1));

--备份恢复
mysqldump --defaults-file=/etc/my.cnf  --master-data=2 --single-transaction --set-gtid-purged=on --log-error=all.log -A >all.sql
/usr/local/mysql/bin/mysqldump --defaults-file=/data/mysql_3306/my.cnf  --master-data=2 --single-transaction --set-gtid-purged=on --log-error=all.log -A >all.sql

--kill杀正在sending data的语句
select group_concat(concat('kill ',id) separator ';') from information_schema.processlist where state = 'Sending data';

--binlog 
基于开始/结束时间
mysqlbinlog --start-datetime='2013-09-10 00:00:00' --stop-datetime='2013-09-10 01:01:01' -d 库名 二进制文件 
基于pos值
mysqlbinlog --start-postion=107 --stop-position=1000 -d 库名 二进制文件 
指定开始/结束时间,并把结果重定向到本地t.binlog文件中.
mysqlbinlog -u username -p password -hl-db1.dba.beta.cn6.qunar.com -P3306 \ 
--read-from-remote-server --start-datetime='2013-09-10 23:00:00' --stop-datetime='2013-09-10 23'

--查询没有主键的表
select table_schema,table_name from information_schema.tables where (table_schema,table_name) not in( select distinct table_schema,table_name from information_schema.columns where COLUMN_KEY='PRI' ) and table_schema not in ('sys','mysql','information_schema','performance_schema');

--mysql 线程和操作系统对应关系
select a.thd_id,b.THREAD_OS_ID,a.user ,a.conn_id,b.TYPE,a.source,a.program_name from sys.processlist a,performance_schema.threads b where b.thread_id=a.thd_id;