mysql_随心记

创建一个用户并只能访问指定数据库

CREATE DATABASE dbtest;
CREATE USER 'testUser'@'%' IDENTIFIED BY '123456';
GRANT SELECT, INSERT, UPDATE, REFERENCES, DELETE, CREATE, DROP, ALTER, INDEX, TRIGGER, CREATE VIEW, SHOW VIEW, EXECUTE, ALTER ROUTINE, CREATE ROUTINE, CREATE TEMPORARY TABLES, LOCK TABLES, EVENT ON `dbtest`.* TO 'testUser'@'%';
GRANT GRANT OPTION ON `dbtest`.* TO 'testUser'@'%';

新建用户并开启远程连接

CREATE USER 'root'@'%' IDENTIFIED BY 'cdjs0803';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

修改用户名密码

方法一

set password for username @localhost = password(newpwd);

方法二

mysqladmin -u用户名 -p旧密码 password 新密码

方法三

update mysql.user set authentication_string=password(‘新密码’) where user=‘用户名’ and Host =‘localhost’;

使用navicat连接MySQL，报密码错误

当用户名和密码都正确时，测试连接也成功时，报错

1.更改加密方式：

ALTER USER ‘yypt’@‘%’ IDENTIFIED BY ‘123456’ PASSWORD EXPIRE NEVER;

2.更改密码：

ALTER USER ‘yypt’@‘%’ IDENTIFIED WITH mysql_native_password BY ‘123456’;

3.刷新：

flush privileges;

mysqld配置

连接数

查询当前设置的最大连接数

SHOW VARIABLES LIKE 'max_connections';

查询当前连接数

SHOW STATUS WHERE `variable_name` = 'Threads_connected';
或者：
SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST;

查看详细的连接信息

SHOW PROCESSLIST;

该命令将列出所有当前的 MySQL 连接，包括每个连接的 ID、用户、主机、数据库、状态、执行的命令等信息。如果连接数较多，可以使用：

SHOW FULL PROCESSLIST;

mysqld配置

在 MySQL 的配置文件中（通常是 my.cnf 或 my.ini），max_connections 变量应该放在 [mysqld] 段落下。

[mysqld]
# 设置最大连接数
max_connections = 1000
# key_buffer_size 是 MySQL 用来缓存索引块的内存大小。它主要用于 MyISAM 存储引擎来存储索引块，以加快索引查找速度。这个参数在使用 MyISAM 表时非常重要
key_buffer_size = 16M
# 定义了 MySQL 允许传输的最大数据包的大小。这是客户端和服务器之间通信时可以传输的最大单个数据包的大小，通常用于控制 BLOB 或大文本字段的传输
#通常，默认值是 4M。如果你的应用程序需要传输大块数据（如大文件或长字符串），你可以增加此值。将其设置为 64M 或更高取决于具体需求
max_allowed_packet = 64M
# 配置 MySQL 分配给每个线程的堆栈大小。每当 MySQL 服务器创建一个新线程时，都会分配一定量的内存作为线程的堆栈空间。堆栈空间主要用于保存线程的局部变量和函数调用栈。
# 256K 是一个相对合理的默认值。如果你的应用程序有大量递归操作或者需要更多堆栈空间，你可以考虑增加此值，但大多数情况下不需要更改
thread_stack = 256K
# 是 InnoDB 存储引擎用于缓存数据和索引的内存区域。提高这个值可以减少磁盘 I/O，从而提高数据库性能
# 通常设置为物理内存的 60%-80%。如果服务器上主要运行 MySQL，可以设置得更高
innodb_buffer_pool_size
# 控制 InnoDB 重做日志文件的大小。更大的日志文件可以减少写入频率，但在崩溃恢复时可能会延长恢复时间
# 通常建议设置为 256MB 到 1GB，但需要根据工作负载进行调整。
innodb_log_file_size
# 控制事务提交时重做日志的刷新行为。1 表示每次提交后都刷盘，保证数据一致性。2 表示每秒刷盘，数据可能有短时间延迟，但性能更好。
# 对于性能优先的场景，可以设置为 2。如果数据一致性要求非常高，可以设置为 1。
innodb_flush_log_at_trx_commit
# 控制 MySQL 可以同时打开的表的数量。提高这个值可以减少频繁打开和关闭表的开销。
# 通常建议设置为 2000 或更高，具体取决于数据库的大小和表的数量。
table_open_cache
# 控制每个 InnoDB 表是否存储在单独的表空间文件中，而不是共享表空间。
# 建议开启（ON），以便更容易管理表空间并在需要时进行表优化
innodb_file_per_table
# 控制 InnoDB 刷新数据到磁盘的方法。O_DIRECT 可以避免缓存重复，O_DSYNC 在某些场景下表现更好
# 大多数情况下，推荐使用 O_DIRECT，特别是在有 RAID 控制器和高速磁盘时。
innodb_flush_method
# 启用二进制日志记录所有数据变更，便于数据恢复和复制。
# 对于需要备份和主从复制的系统，建议开启。同时控制日志大小和保留时间。
log_bin
# 可以记录执行时间超过一定阈值的慢查询，便于分析和优化。
# 开启 slow_query_log 并设置合理的 long_query_time（如 2 秒），通过分析慢查询日志来优化 SQL 语句。
slow_query_log
# 提供数据库性能的详细监控信息。
# 建议开启并定期分析结果，识别性能瓶颈。
performance_schema

强制恢复模式

InnoDB 表空间可能已经损坏，这通常是由于意外停机、磁盘故障或硬件问题引起的。可以尝试使用 InnoDB 的强制恢复模式来启动 MySQL
编辑 MySQL 配置文件 /etc/mysql/my.cnf（或 /etc/my.cnf）

[mysqld]
innodb_force_recovery=1

innodb_force_recovery 的值可以从 1 到 6，表示不同程度的恢复模式。尝试从 1 开始，逐步增加值，直到 MySQL 可以启动。

全局解决 Incorrect datetime value: ‘0000-00-00 00:00:00’ 错误，

1. 调整 MySQL 配置文件（my.cnf/my.ini）

你可以修改 MySQL 配置文件，使之在服务器启动时就默认不包含 NO_ZERO_DATE 和 NO_ZERO_IN_DATE 模式，从而允许 ‘0000-00-00 00:00:00’ 作为合法的日期时间值。

在 MySQL 配置文件中找到 [mysqld] 部分，添加或修改 sql_mode 选项：

[mysqld]
sql_mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

2. 通过命令设置全局 SQL 模式

如果你不想修改配置文件，也可以通过 SQL 命令来设置全局 SQL 模式。

SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

3.在当前会话中生效：

SET SESSION sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';