mysql命令

海上二手挖掘机

已于 2023-01-16 18:16:36 修改

阅读量164

点赞数

文章标签： mysql 数据库

于 2022-08-15 11:35:06 首次发布

本文链接：https://blog.csdn.net/qq_27342265/article/details/126343793

版权

mysqlbinlog

mysqlbinlog -vv --start-datetime="2022-04-31 21:33:00" --stop-datetime="2022-03-31 21:34:00" binlog.028407
mysqlbinlog binlog.009671 -vv --base64-output='decode-rows' --start-position=4

用户及权限相关

show grants for echo@172.28.5.3
revoke select on *.* from read@'%'
create user read@'%' identified by xxxx;
drop user read@'%';
grant select on test.* to 'test123'@'%' identified by '123';

查询表大小

SELECT CONCAT(TRUNCATE(SUM(data_length)/1024/1024,2),'MB') AS data_size, 
CONCAT(TRUNCATE(SUM(max_data_length)/1024/1024,2),'MB') AS max_data_size, 
CONCAT(TRUNCATE(SUM(data_free)/1024/1024,2),'MB') AS data_free, 
CONCAT(TRUNCATE(SUM(index_length)/1024/1024,2),'MB') AS index_size 
FROM information_schema.tables WHERE TABLE_NAME ='ClassesAnalysis ';

查询当前正在执行的事务

select * from information_schema.innodb_trx\G

查询是否存在锁等待

show status like 'innodb_row_lock%';
等MDL锁 select * from information_schema.schema_table_lock_waits\G
等行锁  select * from information_schema.innodb_lock_waits\G

mysqldump

# mysqldump导出表结构
mysqldump -uXXXX -pXXXX -A  -E -R  --single-transaction --master-data=2  --no-data 
# mysqldump导出全量数据
mysqldump -uXXXX -pXXXX -A -F -E -R --triggers --single-transaction --master-data=1 --flush-privileges