跳到主要内容

8 篇博文 含有标签「Mysql」

查看所有标签

MySQL 8.x日期格式0000-00-00报错问题

· 阅读需 2 分钟

MySQL 8.x日期格式0000-00-00报错问题​

5.7 数据迁移至 8.x 报错

由于MySQL的严格模式（STRICT MODE）导致的。MySQL的严格模式不允许日期为'0000-00-00'。你可以通过以下方法来解决这个问题：

临时方法：​

  1. 修改原始数据 如果可以修改源数据文件，将其中所有的'0000-00-00'日期改为一个有效的日期值（例如'1000-01-01'）或NULL。

  2. 在导入数据前禁用NO_ZERO_DATE模式： 在MySQL 8.x中执行以下命令以当前会话禁用NO_ZERO_DATE SQL模式：

    SET @@session.sql_mode=REPLACE(@@session.sql_mode, 'NO_ZERO_DATE', '');

  3. 导入数据 现在，您可以开始导入数据。使用SOURCE命令或mysql客户端工具来导入数据。例如：

    mysql -u <username> -p<password> <database_name> < /path/to/your/data.sql

  4. 恢复NO_ZERO_DATE模式（可选） 如果您想要重新启用NO_ZERO_DATE模式，可以使用以下命令：

    SET @@session.sql_mode=CONCAT_WS(',', @@session.sql_mode, 'NO_ZERO_DATE');

请注意，这种方法仅适用于当前会话。如果您在其他会话中导入数据，需要再次禁用NO_ZERO_DATE模式。

永久方法：​

修改MySQL的配置文件 : 如果你想要永久地关闭严格模式，你可以修改MySQL的配置文件。配置文件通常位于'/etc/my.cnf'或者'/etc/mysql/my.cnf'。在配置文件中，找到'[mysqld]'部分，添加或修改以下行：

sql_mode=ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

然后，你需要重启MySQL服务器以使新的设置生效。

Mysql 调优

· 阅读需 3 分钟
  • Mysql 硬件及内部的参数调优
  • 开发规范制定
  • 数据库索引

Mysql 内部参数调优​

表示缓冲池字节大小。
推荐值为物理内存的50%~80%。
innodb_buffer_pool_size

用来控制redo log刷新到磁盘的策略。
innodb_flush_log_at_trx_commit=1

每提交1次事务同步写到磁盘中，可以设置为n。
sync_binlog=1

脏页占innodb_buffer_pool_size的比例时，触发刷脏页到磁盘。 推荐值为25%~50%。
innodb_max_dirty_pages_pct=30

后台进程最大IO性能指标。
默认200，如果SSD，调整为5000~20000
innodb_io_capacity=200

指定innodb共享表空间文件的大小。
innodb_data_file_path

慢查询日志的阈值设置，单位秒。
long_qurey_time=0.3

mysql复制的形式，row为MySQL8.0的默认形式。
binlog_format=row

调高该参数则应降低interactive_timeout、wait_timeout的值。
max_connections=200

过大，实例恢复时间长；过小，造成日志切换频繁。
innodb_log_file_size

全量日志建议关闭。
默认关闭。
general_log=0

开发规范制定​

创建数据库表，走工单系统完成，需要审核。 如果在创建表时检测到没有创建索引，那就会提示 warning
规范上来说，只要有查询需求，都应该建立索引

1、利用自查询优化超多分页的场景。比如 limit offset，n 在 Mysql 是获取 offset + n 的记录，在返回 n 条。而利用自查询则是查出 n 条，通过 ID 检索对应的记录出来，提高查询效率
2、通过explain命令来查看SQL的执行计划，看看自己写的SQL是否走了索引，走了什么索引。通过show profile 来查看SQL对系统资源的损耗情况（不过一般还是比较少用到的）
3.在开启事务后，在事务内尽可能只操作数据库，并有意识地减少锁的持有时间（比如在事务内需要插入&&修改数据，那可以先插入后修改。因为修改是更新操作，会加行锁。如果先更新，那并发下可能会导致多个事务的请求等待行锁释放）

数据库索引​

1、是否能使用 [覆盖索引] ，减少 [回表] 所消耗的时间，这意味着我们在 select 的时候不能使用 * ，一定要指明对应的列
2、考虑是否组建 [联合索引] ，如果组建 [联合索引] ，尽量将区分度最高的放在最左边，并且考虑 [最左匹配原则]
3、对索引进行函数操作或者表达式计算会导致索引失效

隔离级别​

总结性概述​

可以优化的点有很多，比如说：
1、mysql硬件层面的使用多核性能优的cpu，足够的memory，disk使用ssd
2、还有针对mysql的参数调优：设置max_connections mysql最大连接数，每个用户的最大连接数，设置连接超时时间，设置innodb引擎，InnoDB引擎数据数据和索引尽量都放在内存中配置值为物理内存的70%,innodb_log_buffer_size 事务提交的缓存区,可以提高该参数值.
3、增加慢sql查询，对慢sql优化，大表添加索引或采取同步es 用es查大表数据，如果读写量级都很大，可以做读写分离，在分库分表这块也可以做优化，等等...

Mysql 主从复制原理

· 阅读需 2 分钟

Mysql 主从复制原理​

画一个图 ![在这里插入图片描述](/img/Mysql 主从复制原理/1.jpg)

用语言表述一下吧

  1. change master to 时，ip pot user password binlog position写入到master.info进行记录
  1. start slave 时，从库会启动IO线程和SQL线程
  2. IO_T，读取master.info信息，获取主库信息连接主库
  3. 主库会生成一个准备binlog DUMP线程，来响应从库
  4. IO_T根据master.info记录的binlog文件名和position号，请求主库DUMP最新日志
  5. DUMP线程检查主库的binlog日志，如果有新的，TP(传送)给从从库的IO_T
  6. IO_T将收到的日志存储到了TCP/IP 缓存，立即返回ACK给主库 ，主库工作完成
  7. IO_T将缓存中的数据，存储到relay-log日志文件,更新master.info文件binlog 文件名和postion，IO_T工作完成
  8. SQL_T读取relay-log.info文件，获取到上次执行到的relay-log的位置，作为起点，回放relay-log
  9. SQL_T回放完成之后，会更新relay-log.info文件。
  10. relay-log会有自动清理的功能。

Tips：

  1. 主库一旦有新的日志生成，会发送“信号”给binlog dump ，IO线程再请求