Mysql FAQ手册

中文乱码


[client] 
default-character-set = utf8mb4 
[mysql] 
default-character-set = utf8mb4 
[mysqld] 
character-set-client-handshake = FALSE 
character-set-server = utf8mb4 
collation-server = utf8mb4_unicode_ci 
init_connect='SET NAMES utf8mb4'

mysql的配置文件权限

 mysql的配置文件my.cnf权限不能为777，会导致启动mysql失败

mysql的配置文件名字

 如果自定义的配置项单独创建文件，不要命名为my.cnf，会导致自定义的配置项的文件不生效

查看mysql的字符集

 SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';

查看mysql使用的cnf文件路径

 # mysqld --verbose --help |grep -A 1 'Default options'

设置密码策略

 查看密码策略相关配置项
 show variables like 'validate_password%'

 设置如下两项
 set global validate_password_policy='LOW'
 set global validate_password_length=4

如果出现is marked as crashed and should be repaired 错误，怎么办？

 备份数据库
 mysqldump -u root -p xxx> xxx.sql

 修复数据库
 mysqlcheck -h10.0.132.132 -uroot -p123456 --tables sendmail --auto-repair

 备注：
     sendmail其实是数据库的名称，也可以用-B 代替tables

启动mysql，报：Can't find messagefile '/usr/share/mysql/errmsg.sys错误怎么办？

 在mysqld段增加如下配置：
   languate={路径}/errmsg.sys

 备注：此处的路径为errmsg.sys的实际路径，可通过find 查找

如何查看当前mysql服务加载哪个路径下的配置文件（my.cnf等）？

 mysqld --verbose --help |grep -A 1 'Default options'

 备注：从结果可以看到会按照顺序加载如下文件：
   /etc/my.cnf
   /etc/mysql/my.cnf
   /usr/etc/my.cnf
   /mysql/my.cnf
   ~/.my.cnf

Specified key was too long; max key length is 767 bytes

 系统变量innodb_large_prefix开启了，则对于使用DYNAMIC或COMPRESSED行格式的InnoDB表，索引键前缀限制为3072字节。如果禁用innodb_large_prefix，不管是什么表，索引键前缀限制为767字节。

 上述的bug很明显是索引超出了限制的长度767（我司生产上innodb_large_prefix禁用了）：

        我发现报错的那张表建立了一个varchar类型的索引，varchar(255)，觉得没什么问题，其实不然，上述的767是字节，而varchar类型是字符，同时我发现我使用的字符集为（utf8mb4），这个指每个字符最大的字节数为4，所以很 明显 4*255 > 767

 所以就报上述错了（Specified key was too long; max key length is 767 bytes）

 解决方法：
 改变varchar的字符数，我改成了64就可以了。varchar(64)
 或者启用innodb_large_prefix，那么限制值会增加到3072

utf8与utf8bmp4的区别？

mysql Space ID in fsp header is ...错误？

 在配置文件中设置： innodb_force_recovery=1
 启动一次后，再修改为：0

 此方法可以在测试环境使用，不建议生产

连接mysql报错：Authentication plugin 'caching_sha2_password' cannot be loaded

 由于目前已有的客户端连接软件还不支持Mysql8新增加的加密方式caching_sha2_password，所以我们需要修改用户的加密方式，将其改为老的加密验证方式

 use mysql;
 select user,plugin from user where user='root';
 alter user 'root'@'%' identified with mysql_native_password by 'Root@123';
 flush privileges

mysql 1153 got a packet bigger than max_allowed_packet？

 # show VARIABLES like '%max_allowed_packet%’;
 # set global max_allowed_packet=524288000; #修改 512M

ERROR 1030 (HY000): Got error 28 from storage engine

出现此问题的原因：临时空间不够，无法执行此SQL语句 
将tmpdir指向一个硬盘空间很大的目录即可

如何分析慢查询日志？

 mysqldumpslow
 pt-query-digest

如何恢复数据？

1. 设置普通用户数据库只读
   set global read_only=1; 

2. 查询binlog的最新pos（标记为A)（DROP DATABASE 是最后执行的语句的关键字）
   mysql -uroot -p123456 -e "show binlog events in 'mysql-bin.000001'"|grep -i 'DROP DATABASE'

3. 查询备份数据的最新pos(标记为B)
   cat all_database.sql |grep -i 'CHANGE MASTER TO MASTER_LOG_FILE'|head -n1

4. 用备份数据恢复
   mysql -uroot -p123456 < all_database.sql

5. 用binlog恢复从B到A的数据；
   mysqlbinlog  --start-position=13871   --stop-position=15035  -d dbname mysql-bin.000001|mysql -uroot -p123456 dbname

6. 把数据库锁解开
   set global read_only=0

如何设置mysql的binlog保留时长？

修改配置文件

  例如：保留7天，每个文件大小500M
  expire_logs_days=7
  max_binlog_size=500M

  表示所有binlog日志永久都不会失效，不会自动删除
  expire_logs_days=0

  修改配置文件，执行如下命令，立即生效。
  mysql>flush logs;

  备注：如果binlog很多，建议用purge，因为此操作会导致大量的io

 也可以通过命令行删除binlog日志

 将bin.000055之前的binlog清掉:
 mysql>purge binary logs to 'bin.000055';

 将指定时间之前的binlog清掉:
 mysql>purge binary logs before '2019-09-13 23:59:59';

查看binlog日志模式
mysql> show variables like "%binlog_format%";

修改立即生效
mysql> set global binlog_format='MIXED';