mysql相关命令总结

#mysql备份命令总结

mysqldump -uroot -p123456 --opt -R -E -B nacos_mysql > nacos_mysql20240319.sql -- 备份导出创建数据库的sql，恢复的时候直接恢复

mysqldump -uopenser -pZH_qwsx123 -P 2297 --default-character-set=utf8 --opt -R -E openser >20191106.sql --导出表结构、视图、数据及存储函数及触发器、事件
mysqldump -u root -pkaifarootpass --default-character-set=utf8 --opt -d -R -E test > test.sql --导出表结构、视图、存储函数及触发器、事件
mysqldump -u openser -pjianxin#$ --default-character-set=utf8 --opt --skip-triggers zk_user_basic_info > zk_user_basic_info.sql --导出表结构、数据
mysqldump -u root -pYtx_123 --default-character-set=utf8 --opt -d --skip-triggers openser >openser.sql --导出表结构，不包含数据
mysqldump -u root -pkaifarootpass --default-character-set=utf8 --opt -t --skip-triggers test > test.sql --导出数据，不包含表结构
mysqldump -u root -pkaifarootpass --default-character-set=utf8 --opt -R -t -d test > test.sql --导出存储函数(包含触发器)
mysqldump -u root -pkaifarootpass --default-character-set=utf8 --opt -R -t -d --skip-triggers test > test.sql --只导出存储函数（不包含触发器）
mysqldump -u root -pkaifarootpass --default-character-set=utf8 --opt -t -d test > test.sql --只导出触发器
mysqldump -u root -pkaifarootpass --default-character-set=utf8 --opt --skip-triggers -d openser --tables ccp_ivr_bill>ccp_ivr_bill.sql --导出指定表

mysqldump -u openser -pa6r4t9w3m7n52c -u rds9scb81ltd6m5z3o27.mysql.rds.aliyuncs.com --default-character-set=utf8 --opt --skip-triggers openser --tables gdt_user_info>gdt_user_info.sql --导出指定表及数据，多个用空格隔开

mysqldump -u root -pkaifarootpass --default-character-set=utf8 --opt -t --skip-triggers openser --tables cp_ivr_bill --where="id=1 and name='aaa'">ccp_ivr_bill.sql --导出表的指定条件的数据

mysqldump -u root -pkaifarootpass --default-character-set=utf8 --set-gtid-purged=OFF --opt -R -d --skip-triggers openser > openser.sql -- 导出除数据以外的所有对象

 

#mysql查询命令

-------------------MySQL中show语法---------------
1. show tables或show tables from database_name; -- 显示当前数据库中所有表的名称。
2. show databases; -- 显示mysql中所有数据库的名称。
3. show columns from table_name from database_name; 或show columns from database_name.table_name; -- 显示表中列名称。
4. show grants for user_name; -- 显示一个用户的权限，显示结果类似于grant 命令。
5. show index from table_name; -- 显示表的索引。
6. show status; -- 显示一些系统特定资源的信息，例如，正在运行的线程数量。
7. show variables; -- 显示系统变量的名称和值。
8. show processlist; -- 显示系统中正在运行的所有进程，也就是当前正在执行的查询。大多数用户可以查看他们自己的进程，但是如果他们拥有process权限，就可以查看所有人的进程，包括密码。
9. show table status; -- 显示当前使用或者指定的database中的每个表的信息。信息包括表类型和表的最新更新时间。
10. show privileges; -- 显示服务器所支持的不同权限。
11. show create database database_name; -- 显示create database 语句是否能够创建指定的数据库。
12. show create table table_name; -- 显示create database 语句是否能够创建指定的数据库。
13. show engines; -- 显示安装以后可用的存储引擎和默认引擎。
14. show innodb status; -- 显示innoDB存储引擎的状态。
15. show logs; -- 显示BDB存储引擎的日志。
16. show warnings; -- 显示最后一个执行的语句所产生的错误、警告和通知。
17. show errors; -- 只显示最后一个执行语句所产生的错误。
18. show [storage] engines; --显示安装后的可用存储引擎和默认引擎。

---------------常用的查询语句--------------
查看锁相关状态
show status like '%lock%';

#查看超时时间信息
show variables like '%timeout%';

#线程运行情况，正在执行的sql等
show processlist

#查看mysql的socket文件在什么路径
show variables like 'socket';

#查看最大连接
show variables like 'max_connections';

#响应连接数，不能超过最大连接数的85%
show global status like 'max_used_connections';

--查看查询时间超过long_query_time秒的查询的个数。
show status like 'slow_queries';

--查看创建时间超过slow_launch_time秒的线程数。
show status like 'slow_launch_threads';

--查看不能立即获得的表的锁的次数。如果该值较高，并且有性能问题，你应首先优化查询，然后拆分表或使用复制。
show status like 'table_locks_waited';

--查看立即获得的表的锁的次数。
show status like 'table_locks_immediate';

--查看激活的(非睡眠状态)线程数。
show status like 'threads_running';

--查看创建用来处理连接的线程数。如果Threads_created较大，你可能要增加thread_cache_size值。
show status like 'threads_created';

--查看当前打开的连接的数量。
show status like 'threads_connected';

--查看线程缓存内的线程的数量。
show status like 'threads_cached';

--查看试图连接到MySQL(不管是否连接成功)的连接数
show status like 'connections';

--查看delete语句的执行数
show [global] status like 'com_delete';

--查看update语句的执行数
show [global] status like 'com_update';

--查看insert语句的执行数
show [global] status like 'com_insert';

--查看select语句的执行数
show [global] status like 'com_select';

--查看MySQL本次启动后的运行时间(单位：秒)
show status like 'uptime';

--查看表缓存的数目
show variables like '%table_open_cache%';
--临时设置表缓存的数目
set global table_open_cache=1024;

--Open_tables表示打开表的数量，Opened_tables表示打开过的表数量
show global status like 'Open%tables';

 

----------------慢查询-----------------------

#查询当前慢查询日志的状态
show VARIABLES like '%slow_query_log%'

#查看慢查询日志记录路劲
show VARIABLES like '%slow_query_log_file%'
找到这个路径，可以看到超过默认10秒打印的日志

#查看慢查询默认时间
show VARIABLES like '%long_query_time%'

#查看记录没有使用索引查询是否开启
show VARIABLES like '%log_queries_not_using_indexes%'
#开启 记录没有使用索引查询语句
log-queries-not-using-indexes = on

#手动开启慢日志查询
set global slow_query_log=on;
set GLOBAL slow_query_log = 1;

#默认10秒，这里为了演示方便设置为0
set global long_query_time=0;

#查看日志记录的位置
show VARIABLES like 'log_output'
#项目开发中日志只能记录在日志文件中，不能记表中
set global log_output='FILE,TABLE'

 

------------------kill掉正在执行的sql-----------------

mysql> show processlist;
将Id号拿到

mysql> kill 89;
kill掉正在执行的id为89的sql
或者：
/app/mysql/bin/mysqladmin -uroot -p'Ytx_1!23' kill 86

#查询正在运行中的sql，以时间排序
SELECT t.ID,t.USER,t.HOST,t.DB,t.COMMAND,t.TIME,t.STATE,t.INFO FROM information_schema.PROCESSLIST t WHERE t.COMMAND<>'Sleep' order by t.time desc limit 10;

#查询正在执行的mysql的sql信息，可以看到全部的sql，而show processlist 只能看到前100个字符
show full processlist

 

-----------------------------其他-----------------------------

#查看存储
show create procedure PS_CLEAR_HISTORY_MSG;

#查看数据库中有哪些事件
use openser;
show events\G;

#查看索引
show index from ytx_msg_route\G

#查看日期最新的数据
select * from ytx_history_notify_message order by update_time desc limit 10;

#修改表名
rename table ytx_history_message202006 to ytx_history_message_2020_06;

#删除存储
drop procedure procedure_transfer_history_msg;

#删除事件
drop event event_transfer_history_msg;

1，切换数据库

use information_schema;

2，查看数据库使用大小

select concat(round(sum(data_length/1024/1024),2),’MB’) as data from tables where table_schema=’DB_Name’ ;

3，查看表使用大小

select concat(round(sum(data_length/1024/1024),2),’MB’) as data from tables where table_schema=’DB_Name’ and table_name=’Table_Name’;

 

#查询指定时间的值
select count(1) from ytx_history_message where date_created BETWEEN '2020-07-08 00:00:00' and '2020-07-08 23:59:59';

#连表查询，使用where 字段 in方式
SELECT * from ytx_history_message where msg_sender in (SELECT account from zk_userinfo where userid in (select userid from zk_user_basic_info where user_name='吴志坤')) and date_created BETWEEN '2020-07-01 00:00:00' and '2020-07-01 23:59:59';

#mysql查询命令：
查询全部的记录： select * from test_limit ;

查第一条记录： select * from test_limit limit 1;

查前面两条记录： select * from test_limit limit 0,2;

查第二和第三条记录: select * from test_limit limit 1,2;

查最后一条记录： select * from test_limit order by id DESC limit 1;

mysql通过key模糊匹配某些值
select * from rx_compinfo_config where cfg_val like '%ldap%' ;

 

#distinct 某个字段去重，count(distinct msg_receiver)去重并且统计这个字段的数量
select count(distinct msg_receiver) from ytx_history_message202007 where update_time > '2020-07-24 00:00:00' and update_time < '2020-07-31 23:59:00';

 

-------------------------mysql查看所有触发器以及存储过程等操作集合------------------------------
1. mysql查询所有表:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '数据库名' AND TABLE_TYPE ='BASE TABLE'

mysql查询建表语句:

show create table `表名`

2.mysql查询所有视图:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '数据库名' AND TABLE_TYPE ='VIEW'

mysql查询视图创建语句:

show create view `视图名`

3.mysql查询所有函数:

SELECT name from mysql.proc where db= 'ifms' and type='function'

mysql查询函数定义语句:

SHOW CREATE FUNCTION `函数名`

4.mysql查询所有存储过程:

SELECT name from mysql.proc where db= 'ifms' and type='procedure'

mysql查询procedure定义语句:

SHOW CREATE procedure `存储过程名`

5.mysql查询所有触发器:

SELECT * FROM information_schema.`TRIGGERS`

mysql查询触发器定义语句:

select * from information_schema.triggers where TRIGGER_NAME='触发器名';