数据库常用指令

查看正在被锁定的的表:
show OPEN TABLES where In_use > 0;

查询 正在执行的事务：
SELECT * FROM information_schema.INNODB_TRX;

查看当前进程状态 
show full processlist;
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST where command <>'sleep' ORDER BY TIME desc;

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST where command <>'sleep' and time>30 ORDER BY TIME desc;
 

查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

 

#any_user的数据库监控
#创建监控用户
CREATE USER 'any_user'@'%' IDENTIFIED BY 'xxxxxx';
GRANT ALL PRIVILEGES ON *.* TO 'any_user'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
#限制监控用户的并发链接数
ALTER USER 'any_user'@'%' WITH MAX_USER_CONNECTIONS 100;
FLUSH PRIVILEGES;

查询所有文件下的包含hello world!的字符
find -type f -name '*.go'|xargs grep 'db_connector.Init'

全局替换
sed -i "s/oldstring/newstring/g" `grep oldstring -rl path`
sed -i "s/IsUionSearch/IsUnionSearch/g" `grep IsUionSearch -rl .`

导出记录
mysqldump -h xxxx.xxxx.xxxx.xxx -u xxxuser -p'xxxxx' --single-transaction db_name table_name>a.sql

导出存储过程,函数,触发器
mysqldump -h xxxx.xxxx.xxxx.xxx -u xxxuser -p'xxxxxx' --routines --skip-triggers  --no-create-info --no-data --no-create-db --skip-opt db_name>s.sql
导入记录
mysql -h xxxx.xxxx.xxxx.xxx -u user -p'xxxxxxxxxx' -Ddb_name<a.sql