前言

MySQL是一个功能强大且广泛使用的开源关系型数据库管理系统。随着互联网和数据应用的不断发展,熟练掌握MySQL的使用对于开发人员和数据库管理员来说变得越来越重要。MySQL提供了丰富的命令和功能,能够满足各种数据库操作需求。本文将总结一些常用的MySQL命令,帮助大家更好地掌握和使用MySQL。

1、查看表状态

show table status like '%tablename%';

2、改密码

-- 方法一:

./mysqladmin -uroot -hlocalhost --socket=/data/mysql_3306/tmp/mysql.sock -p password
-- 方法二:

ALTER USER 'root'@'localhost' IDENTIFIED with mysql_native_password BY 'password';

3、杀特定用户链接

select concat('KILL ',id,';')from information_schema.processlist where user='21xmt_user';

4、binlog解析

mysqlbinlog --no-defaults -vv --base64-output=decode-rows mysql-bin.000201

5、删除用户

drop user 'xxx'; 只删除 'xxx'@'%' 账户

6、修改主键(小表,大表用pt-osc等工具)

alter table xxx drop primary key,add primary key(task_id, aaa);

7、查看表大小

select table_name , table_rows from inforation_schema.tables where table_name='xxx';

8、权限查询

-- 1. 查询用户的权限:

SHOW GRANTS FOR '用户名'@'主机名';
-- 其中,'用户名'是你想查询权限的用户,'主机名'是连接该用户的主机名。

-- 2. 查询所有用户的权限:

SELECT user, host, authentication_string, plugin, password_expired FROM mysql.user;
-- 该查询语句将返回所有用户的用户名、主机名、认证字符串、插件和密码过期信息等。

-- 3. 查询用户拥有的权限:

SELECT * FROM mysql.user WHERE User = '用户名' AND Host = '主机名';
-- 更改上述查询语句中的'用户名'和'主机名'为你想查询权限的用户和主机名。

4. 查询用户在特定数据库上的权限:

SHOW GRANTS FOR '用户名'@'主机名' ON '数据库名';
-- 将'用户名'替换为要查询权限的用户,'主机名'设置为连接该用户的主机名,'数据库名'为目标数据库。

注意,执行上述查询需要具有足够的权限,一般只有具有SUPER权限或拥有grant权限的用户才能查询其他用户的权限信息。

9、trace

SET SESSION OPTIMIZER_TRACE="enabled=on";   # enable tracing
<statement to trace>;   # like SELECT, EXPLAIN SELECT, UPDATE, DELETE...
SELECT * FROM information_schema.OPTIMIZER_TRACE;
[ repeat last two steps at will ]
SET SESSION OPTIMIZER_TRACE="enabled=off"; # disable tracing
  • SET SESSION OPTIMIZER_TRACE="enabled=on";此指令用于启用查询优化器跟踪。将其设置为"enabled=on"表示启用跟踪功能。此配置仅对当前会话有效。

  • <statement to trace>;在该指令中,你可以执行要进行跟踪的SQL语句,例如SELECT、EXPLAIN SELECT、UPDATE、DELETE等。查询优化器将在执行该语句的同时记录相关信息以进行跟踪。

  • SELECT * FROM information_schema.OPTIMIZER_TRACE;这个查询语句用于检索跟踪结果。执行后,将返回包含查询优化器跟踪信息的结果集。你可以通过分析这些信息来了解优化器是如何处理和优化查询的。

  • [ repeat last two steps at will ]这两个步骤可以重复多次,以跟踪和分析不同的查询语句。

  • SET SESSION OPTIMIZER_TRACE="enabled=off";此指令用于禁用查询优化器跟踪。将其设置为"enabled=off"表示禁用跟踪功能。同样,此配置仅对当前会话有效。

10、innodb状态查看

SHOW ENGINE INNODB STATUS;

执行上述语句后,会返回一个结果集,其中包含了InnoDB引擎的详细状态信息。可以查看到InnoDB的版本、事务和锁定的信息、缓冲池的统计信息等。

11、修改root密码

方式一:使用ALTER USER语句

-- 将 'new_password' 替换为你要设置的新密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

-- 执行完成后刷新权限,以使修改后的密码生效
flush privileges;

方式二:使用SET PASSWORD语句

-- 将 'new_password' 替换为你要设置的新密码
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');

--执行完成后刷新权限,以使修改后的密码生效
FLUSH PRIVILEGES;

12、添加自增属性

alter table xxx modify id bigint auto_increment;

13、rename库名 (所有表操作即可完成库名重命名)

RENAME DATABASE old_database_name TO new_database_name;

将上述语句中的"old_database_name"替换为要重命名的现有数据库的名称,将"new_database_name"替换为所需的新数据库名称。

请注意以下事项:

  • 仅适用于空数据库:RENAME DATABASE语句只能重命名空数据库。如果数据库中有表或其他对象,则无法使用该语句。
  • 权限要求:执行RENAME DATABASE需要具有足够的权限(如CREATE、DROP和ALTER权限)。
  • 慎重操作:重命名数据库是一个潜在的高风险操作,应该在确认备份和可能的影响后谨慎执行。

14、表碎片整理

OPTIMIZE TABLE table_name;
-- 将上述语句中的"table_name"替换为要进行碎片整理的表名。可以一次指定多个表,用逗号分隔。

碎片整理会重新组织表的物理存储结构,删除不再使用的空间和优化表的性能。它可以回收被删除数据所占用的空间并重组数据行,提高查询性能。

请注意以下事项:

  • 权限要求:执行OPTIMIZE TABLE需要具有足够的权限(如ALTER和CREATE权限)。
  • 表锁定:执行OPTIMIZE TABLE时,表将被锁定,可能会影响其他对表的操作。在生产环境中,应该在合适的时间段执行该操作,避免对用户的影响。
  • 自动碎片整理:MySQL InnoDB存储引擎默认支持自动碎片整理,并且在某些情况下会自动执行碎片整理。
    –碎片整理操作并不是在每个MySQL版本或存储引擎中都是可用的。在执行碎片整理之前,建议先进行数据备份,并测试对性能的影响。

15、查看表的最近更新时间

-- 使用INFORMATION_SCHEMA表
SELECT UPDATE_TIME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_database_name'
AND TABLE_NAME = 'your_table_name';

16、创建索引时间查询

SELECT trx_id, trx_started, (NOW() - trx_started) trx_duration_seconds, id processlist_id, user, IF(LEFT(HOST, (LOCATE(':', host) - 1)) = '', host,  LEFT(HOST, (LOCATE(':', host) - 1))) host, command, time, REPLACE(SUBSTRING(info,1,25),'\n','') info_25 FROM information_schema.innodb_trx JOIN information_schema.processlist  ON innodb_trx.trx_mysql_thread_id = processlist.id WHERE (NOW() - trx_started) > 60 ORDER BY trx_started;

17、mysql 终端中操作启用事务

begin-- 用于启动一个新的事务
要执行的sqlcommit/rollback-- commit用于提交事务,将之前的修改永久保存到数据库中
-- rollback命令用于回滚事务,撤销之前的修改,将数据库恢复到事务开始前的状态

启用事务后,在提交或回滚前,对数据库做的任何修改都只是在事务的上下文中进行的,对外部用户是不可见的。只有在显式执行 COMMIT; 或 ROLLBACK; 命令后,其他用户才能看到你的修改结果。

18、修改表的字符集

使用 ALTER TABLE 语句加上 CONVERT TO 子句,指定新的字符集来修改表的字符集。例如,如果要将表的字符集改为UTF-8,可以这样操作:

ALTER TABLE 表名 CONVERT TO CHARACTER SET utf8;

如果你想同时修改表的字符集和校对规则(collation),可以使用下面的命令:

ALTER TABLE 表名 CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

这里的 utf8_general_ci 是UTF-8字符集的一个常见的校对规则。
修改表字符集后,你可以使用 SHOW CREATE TABLE 命令再次查看表的详细信息,确认字符集的修改是否生效。

19、查看用户自定义视图

存储过程、triggers查询方法类似,自己找相关表查询即可。

SELECT TABLE_SCHEMA, TABLE_NAME
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');

总结

本文对MySQL常用命令进行了总结,涵盖了数据库的创建、连接、操作、查询、优化和备份等方面。通过学习和掌握这些命令,就可以完成基本的数据库操作,提高数据库性能,保证数据安全性。

学习MySQL是一个长期的过程,不断实践和探索才能更好地掌握。祝愿大家在使用MySQL时能够取得更好的效果,为自己的项目和工作创造更大的价值。

03-19 03:21