MySQL实用技巧

1、使用 set persist语法实现持久化my.cnf

该语法会先在data目录下创建一个json格式的mysqld-auto.cnf文件

在数据库启动时，会首先读取其它配置文件(手工指定的)，最后才读取mysqld-auto.cnf文件

这样就避免了我们改了数据库的参数，但是没有修改my.cnf文件，造成参数没有正确加载。

2、 自增id持久化，重启之后，不会缩减为max(PK)+1

在8.0之前的版本，自增值是保存在内存中,自增主键AUTO_INCREMENT的值如果大于max(primary key)+1，在MySQL重启后，会重置AUTO_INCREMENT=max(primary key)+1。这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题。

自增主键重启重置的问题很早就被发现(https://bugs.mysql.com/bug.php?id=199)，一直到8.0才被解决。8.0版本将会对AUTO_INCREMENT值进行持久化，MySQL重启后，该值将不会改变。

MySQL server重启后不再取消AUTO_INCREMENT = N表选项的效果。如果将自增计数器初始化为特定值，或者将自动递增计数器值更改为更大的值，新的值被持久化，即使服务器重启。

3、 强制每个表都要有主键

sql_require_primary_key参数控制

[email protected]:(none) 15:34:12>>show variables like '%requi%';

+--------------------------+-------+

| Variable_name | Value |

+--------------------------+-------+

| password_require_current | OFF |

| require_row_format | OFF |

| require_secure_transport | OFF |

| sql_require_primary_key | ON |

+--------------------------+-------+

4 rows in set (0.00 sec)

如果该参数设为on，创建表的时候没有主键，MySQL会直接报错，并给出提示。如下：

[email protected]:test 15:44:44>>create table testc (id int);

ERROR 3750 (HY000): Unable to create 
or change a table without a primary key,
 when the system variable 'sql_require_primary_key' is set.
 Add a primary key to the table 
or unset this variable to avoid this message.
 Note that tables without a primary key 
can cause performance problems in row-based
 replication, so please consult your DBA
 before changing this setting.

4 生成随机密码

[email protected]:test 15:59:27>>create user dba_yeyz@'%' identified by random password;
+----------+------+----------------------+
| user     | host | generated password   |
+----------+------+----------------------+
| dba_yeyz | %    | C.S}DfhQw:X+*DHYZ;7! |
+----------+------+----------------------+
1 row in set (0.00 sec)

[email protected]:test 16:01:42>>create user dba_yeyz2@'%' identified with 'mysql_native_password' by random password; 
+-----------+------+----------------------+
| user      | host | generated password   |
+-----------+------+----------------------+
| dba_yeyz2 | %    | pmdnVsK.SU]p]irSdPDz |
+-----------+------+----------------------+
1 row in set (0.00 sec)

5 mysqldump和mysqlpump备份

5.1、之前版本中，使用-all-databases参数导出数据的时候，不加--routines、--triggers和--events参数，也可以导出触发器和存储过程等信息，因为这些信息都在proc和event表中，现在不行了，需要加上--event和--routines选项才能导出存储过程和触发器

5.2、之前版本中--routines选项导出时候，备份账户需要有proc表的select权限，现在8.0需要有所有表的select权限

5.3、之前版本中导出触发器、处处过程可以同时导出其创建和修改的时间戳，8.0版本以后不可以了

6、DDL操作会耗时加长

旧版本中是直接对.frm文件进行更改操作，只写一个文件，新版本中需要更新数据字典表，需要将数据写到存储引擎、redo log 、undo log中

7、 撤销远程状态下的skip-grant-tables

--skip-grant-tables参数使用后，不能通过远程连接MySQL，而只能通过本地进行连接。

8、 自适应参数(实用)

innodb_dedicated_server=on

启用该参数时，innodb将自动配置以下变量：

innodb_buffer_pool_size

innodb_log_file_size

innodb_flush_method

单机多实例场景、或者MySQL和Redis等应用混布的情况下不建议使用，单机单实例场景下建议使用。

buffer pool配置规则：

当检测到的机器内存<1GB时，buffer_pool_size设置为128MB

当检测到的机器内存在1GB~4GB之间时，buffer_pool_size设置为机器内存*0.5

当检测到的机器内存大于4GB时，buffer_pool_size设置为机器内存*0.75

8.0.14之后，log file配置规则：

buffer pool小于8G，则log file配置为512MB

buffer pool在8G~128G之间，则log file配置为1024MB

buffer pool大于128G，则logfile配置为2048MB

8.0.14之前，log file配置规则

系统内存<1G，log file配置为48MB

系统内存<4G，log file配置为128MB

系统内存<8G，log file配置为512MB

系统内存<16G，log file配置为1024MB

系统内存>16G，log file配置为2048MB

redo 日志组内的日志个数 innodb_log_files_in_group

当buffer pool<8GB，log file个数为round(buffer pool size)，如果结果小于2，则设置为2

当buffer pool在8G~128G，log file个数为round(0.75* buffer pool size)

当buffer pool大于128G，则log file个数为64个

9、密码及安全方面

支持密码过期策略，需要周期性修改密码

[email protected]:test 16:17:17>>show variables like '%default_password_lifetime%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| default_password_lifetime | 0     |
+---------------------------+-------+
1 row in set (0.00 sec)

设置密码永不过期：

alter user yeyz password expire never;

增加历史密码检测机制，防止总是几个密码反复使用,检测最近的几次密码。

[email protected]:test 16:17:38>>show variables like '%password_history%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| password_history | 0     |
+------------------+-------+
1 row in set (0.00 sec)

需要提供旧密码才能修改新密码，防止被篡改

支持双密码机制，可以选择采用主密码还是第二密码连接，安全性更高

增加密码强度约束，避免使用弱密码