Mysql_Learning_Notes_mysql系统结构_2

Wesley13
• 阅读 614

Mysql_Learning_Notes_mysql系统结构_2

三层体系结构,启动方式,日志类型及解析方法,mysql 升级

连接层

  • 通信协议处理\线程处理\账号认证(用户名和密码认证)\安全检查等
  • tcp/ip 连接 网络+端口号
    • mysql -h x.x.x.x -uroot -pxxxx -P3306
      • ERROR 1129 (HY000): Host 'mysql02' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
      • 错误原因:同一个ip在短时间内产生太多中断的数据库连接而导致的阻塞(超过mysql数据库max_connection_errors的最大值)
      • 解决办法:mysqladmin flush-hosts
    • 尽可能不在命令里出现,可以使用mysql --loginpath 的方式登录.
      • mysql_config_editor set -G p3507 -S /tmp/mgr_mysql3507.sock -p
      • mysql --login-path=p3507
  • Unix socket
  • Name pipe (win 不常用)
  • share memory(win 不常用)
登录认证注意:

mysql -hlocalhost -uroot 认证的是localhost mysql -h127.1 -uroot 认证的是ip地址

SQL层

  • 权限判断(鉴权)、查询解析、优化器&缓存、查询执行&返回等 SQL解析没有软解析,每次都是硬解析,但硬解析损耗很小.
  • 为什么不用QC ? 单看查询缓存来说,是有积极作用,但从并发来说,如果实例在频繁的更新,会导致等待(锁),性能并不理想,所只适合不频繁更新的实例.5.7开始默认是关闭的,8.0直接禁了.
  • Select sql_no_cache count(*) from users; 通过'sql_no_cache'方式指定某一条SQL不走QC

储存层

数据目录结构:

请求日志\错误日志\二进制日志\中继日志 - 存储引擎(基于磁盘):Innodb、MyISAM、TokuDB 存储引擎API接口 - 存储引擎(基于内存):MEMORY/HEAP - 存储引擎(基于网络):NDB(不推荐) 特别的文件:

  • auto.cnf 常于用存储server-uuid,当复制从库时需要注意,避免server-uuid重复(重复时需要删除此文件,再重新启动会产生新的).
  • relay-bin.000001 relay log(中继日志)主要用于存储主库传过来的binlog
  • mysql-bin.000001 binlog 主要存储实例修改日志
  • ib_logfile0 innodb redo log files
  • error.log 错误日志文件排错非常有用的文件
  • *.ibd 表数据文件,没有备份并drop表后,数据恢复时的关键文件

配置文件

  • 读取位置顺序:

    • /etc/my.cnf
    • /etc/mysql/my.cnf
    • usr/local/mysql/etc/my.cnf
    • ~/.my.cnf
  • my.cnf 配置文件的主要部分

    • client 所有客户端及相关程序(如:mysql mysqladmin mysqldump等)
    • mysql 只管"mysql"客户端
    • mysqldump 只管"mysqldump"客户端
    • mysql_multi 多版本启动的配置
    • mysqld mysql服务的配置
  • 多实例配置在同一个文件: my.cnf [mysqld] 通用参数 [mysqld3306] 3306端口实例的参数 [mysqld3307] 3306端口实例的参数

启动方式

  • mysqld (5.7默认,格式:mysqld --defaults-file=/data/mysql3306/my3306.cnf&)
  • mysql_safe (5.6默认)
  • mysqld_multi (多版本启动)

日志类型及解析方法

日志文件

选项(配置文件中)

文件名/表名

应用程序

错误

log-error=error.log

error.log

n/a

常规

general_log/ general_log_file=general.log

general.log或mysql.general表

pt-query-digest

慢速查询

slow_query_log=slow.log&long-query_time=x

slow.log或mysql.slow_log表

mysqldumpslow或pt-query-digest

二进制

log-bin=/path/mybinlog&expire-logs-days

mybinlog.000001

mysqlbinlog或pt-query-digest

审计

audit_log&audit_log_file

audit.log

N/A

mysql 升级

  • mysql_upgrade -f (小版本)
  • 大版本:
    • 全备并查阅官方升级文档
    • innodb脏页比例innodb_max_dirty_pages_pct设置为0
    • innodb_fast_shutdown=0
    • shutdown
    • mysql_upgrade -f

[root@db211_07:07:13 /data/57mysql/mysql3507]
''#tree . ├── data │ ├── 3506binlog │ ├── auto.cnf │ ├── backup-my.cnf │ ├── error.log │ ├── ib_buffer_pool │ ├── ib_logfile0 │ ├── ib_logfile1 │ ├── ib_logfile2 │ ├── innodb_status.12888 │ ├── master.info │ ├── mysql │ │ ├── columns_priv.frm │ │ ├── columns_priv.MYD │ │ ├── columns_priv.MYI │ │ ├── db.frm │ │ ├── db.MYD │ │ ├── db.MYI │ │ ├── db.opt │ │ ├── engine_cost.frm │ │ ├── engine_cost.ibd │ │ ├── event.frm │ │ ├── event.MYD │ │ ├── event.MYI │ │ ├── func.frm │ │ ├── func.MYD │ │ ├── func.MYI │ │ ├── general_log.CSM │ │ ├── general_log.CSV │ │ ├── general_log.frm │ │ ├── gtid_executed.frm │ │ ├── gtid_executed.ibd │ │ ├── help_category.frm │ │ ├── help_category.ibd │ │ ├── help_keyword.frm │ │ ├── help_keyword.ibd │ │ ├── help_relation.frm │ │ ├── help_relation.ibd │ │ ├── help_topic.frm │ │ ├── help_topic.ibd │ │ ├── innodb_index_stats.frm │ │ ├── innodb_index_stats.ibd │ │ ├── innodb_table_stats.frm │ │ ├── innodb_table_stats.ibd │ │ ├── ndb_binlog_index.frm │ │ ├── ndb_binlog_index.MYD │ │ ├── ndb_binlog_index.MYI │ │ ├── plugin.frm │ │ ├── plugin.ibd │ │ ├── proc.frm │ │ ├── proc.MYD │ │ ├── proc.MYI │ │ ├── procs_priv.frm │ │ ├── procs_priv.MYD │ │ ├── procs_priv.MYI │ │ ├── proxies_priv.frm │ │ ├── proxies_priv.MYD │ │ ├── proxies_priv.MYI │ │ ├── server_cost.frm │ │ ├── server_cost.ibd │ │ ├── servers.frm │ │ ├── servers.ibd │ │ ├── slave_master_info.frm │ │ ├── slave_master_info.ibd │ │ ├── slave_relay_log_info.frm │ │ ├── slave_relay_log_info.ibd │ │ ├── slave_worker_info.frm │ │ ├── slave_worker_info.ibd │ │ ├── slow_log.CSM │ │ ├── slow_log.CSV │ │ ├── slow_log.frm │ │ ├── tables_priv.frm │ │ ├── tables_priv.MYD │ │ ├── tables_priv.MYI │ │ ├── time_zone.frm │ │ ├── time_zone.ibd │ │ ├── time_zone_leap_second.frm │ │ ├── time_zone_leap_second.ibd │ │ ├── time_zone_name.frm │ │ ├── time_zone_name.ibd │ │ ├── time_zone_transition.frm │ │ ├── time_zone_transition.ibd │ │ ├── time_zone_transition_type.frm │ │ ├── time_zone_transition_type.ibd │ │ ├── user.frm │ │ ├── user.MYD │ │ └── user.MYI │ ├── performance_schema │ │ ├── accounts.frm │ │ ├── cond_instances.frm │ │ ├── db.opt │ │ ├── events_stages_current.frm │ │ ├── events_stages_history.frm │ │ ├── events_stages_history_long.frm │ │ ├── events_stages_summary_by_account_by_event_name.frm │ │ ├── events_stages_summary_by_host_by_event_name.frm │ │ ├── events_stages_summary_by_thread_by_event_name.frm │ │ ├── events_stages_summary_by_user_by_event_name.frm │ │ ├── events_stages_summary_global_by_event_name.frm │ │ ├── events_statements_current.frm │ │ ├── events_statements_history.frm │ │ ├── events_statements_history_long.frm │ │ ├── events_statements_summary_by_account_by_event_name.frm │ │ ├── events_statements_summary_by_digest.frm │ │ ├── events_statements_summary_by_host_by_event_name.frm │ │ ├── events_statements_summary_by_program.frm │ │ ├── events_statements_summary_by_thread_by_event_name.frm │ │ ├── events_statements_summary_by_user_by_event_name.frm │ │ ├── events_statements_summary_global_by_event_name.frm │ │ ├── events_transactions_current.frm │ │ ├── events_transactions_history.frm │ │ ├── events_transactions_history_long.frm │ │ ├── events_transactions_summary_by_account_by_event_name.frm │ │ ├── events_transactions_summary_by_host_by_event_name.frm │ │ ├── events_transactions_summary_by_thread_by_event_name.frm │ │ ├── events_transactions_summary_by_user_by_event_name.frm │ │ ├── events_transactions_summary_global_by_event_name.frm │ │ ├── events_waits_current.frm │ │ ├── events_waits_history.frm │ │ ├── events_waits_history_long.frm │ │ ├── events_waits_summary_by_account_by_event_name.frm │ │ ├── events_waits_summary_by_host_by_event_name.frm │ │ ├── events_waits_summary_by_instance.frm │ │ ├── events_waits_summary_by_thread_by_event_name.frm │ │ ├── events_waits_summary_by_user_by_event_name.frm │ │ ├── events_waits_summary_global_by_event_name.frm │ │ ├── file_instances.frm │ │ ├── file_summary_by_event_name.frm │ │ ├── file_summary_by_instance.frm │ │ ├── global_status.frm │ │ ├── global_variables.frm │ │ ├── host_cache.frm │ │ ├── hosts.frm │ │ ├── memory_summary_by_account_by_event_name.frm │ │ ├── memory_summary_by_host_by_event_name.frm │ │ ├── memory_summary_by_thread_by_event_name.frm │ │ ├── memory_summary_by_user_by_event_name.frm │ │ ├── memory_summary_global_by_event_name.frm │ │ ├── metadata_locks.frm │ │ ├── mutex_instances.frm │ │ ├── objects_summary_global_by_type.frm │ │ ├── performance_timers.frm │ │ ├── prepared_statements_instances.frm │ │ ├── replication_applier_configuration.frm │ │ ├── replication_applier_status_by_coordinator.frm │ │ ├── replication_applier_status_by_worker.frm │ │ ├── replication_applier_status.frm │ │ ├── replication_connection_configuration.frm │ │ ├── replication_connection_status.frm │ │ ├── replication_group_members.frm │ │ ├── replication_group_member_stats.frm │ │ ├── rwlock_instances.frm │ │ ├── session_account_connect_attrs.frm │ │ ├── session_connect_attrs.frm │ │ ├── session_status.frm │ │ ├── session_variables.frm │ │ ├── setup_actors.frm │ │ ├── setup_consumers.frm │ │ ├── setup_instruments.frm │ │ ├── setup_objects.frm │ │ ├── setup_timers.frm │ │ ├── socket_instances.frm │ │ ├── socket_summary_by_event_name.frm │ │ ├── socket_summary_by_instance.frm │ │ ├── status_by_account.frm │ │ ├── status_by_host.frm │ │ ├── status_by_thread.frm │ │ ├── status_by_user.frm │ │ ├── table_handles.frm │ │ ├── table_io_waits_summary_by_index_usage.frm │ │ ├── table_io_waits_summary_by_table.frm │ │ ├── table_lock_waits_summary_by_table.frm │ │ ├── threads.frm │ │ ├── users.frm │ │ ├── user_variables_by_thread.frm │ │ └── variables_by_thread.frm │ ├── relay-bin.000020 │ ├── relay-bin.000021 │ ├── relay-bin.000022 │ ├── relay-bin.000023 │ ├── relay-bin.index │ ├── relay-log.info │ ├── slow.log │ ├── sys │ │ ├── db.opt │ │ ├── host_summary_by_file_io.frm │ │ ├── host_summary_by_file_io_type.frm │ │ ├── host_summary_by_stages.frm │ │ ├── host_summary_by_statement_latency.frm │ │ ├── host_summary_by_statement_type.frm │ │ ├── host_summary.frm │ │ ├── innodb_buffer_stats_by_schema.frm │ │ ├── innodb_buffer_stats_by_table.frm │ │ ├── innodb_lock_waits.frm │ │ ├── io_by_thread_by_latency.frm │ │ ├── io_global_by_file_by_bytes.frm │ │ ├── io_global_by_file_by_latency.frm │ │ ├── io_global_by_wait_by_bytes.frm │ │ ├── io_global_by_wait_by_latency.frm │ │ ├── latest_file_io.frm │ │ ├── memory_by_host_by_current_bytes.frm │ │ ├── memory_by_thread_by_current_bytes.frm │ │ ├── memory_by_user_by_current_bytes.frm │ │ ├── memory_global_by_current_bytes.frm │ │ ├── memory_global_total.frm │ │ ├── metrics.frm │ │ ├── processlist.frm │ │ ├── ps_check_lost_instrumentation.frm │ │ ├── schema_auto_increment_columns.frm │ │ ├── schema_index_statistics.frm │ │ ├── schema_object_overview.frm │ │ ├── schema_redundant_indexes.frm │ │ ├── schema_table_lock_waits.frm │ │ ├── schema_table_statistics.frm │ │ ├── schema_table_statistics_with_buffer.frm │ │ ├── schema_tables_with_full_table_scans.frm │ │ ├── schema_unused_indexes.frm │ │ ├── session.frm │ │ ├── session_ssl_status.frm │ │ ├── statement_analysis.frm │ │ ├── statements_with_errors_or_warnings.frm │ │ ├── statements_with_full_table_scans.frm │ │ ├── statements_with_runtimes_in_95th_percentile.frm │ │ ├── statements_with_sorting.frm │ │ ├── statements_with_temp_tables.frm │ │ ├── sys_config.frm │ │ ├── sys_config.ibd │ │ ├── sys_config_insert_set_user.TRN │ │ ├── sys_config.TRG │ │ ├── sys_config_update_set_user.TRN │ │ ├── user_summary_by_file_io.frm │ │ ├── user_summary_by_file_io_type.frm │ │ ├── user_summary_by_stages.frm │ │ ├── user_summary_by_statement_latency.frm │ │ ├── user_summary_by_statement_type.frm │ │ ├── user_summary.frm │ │ ├── version.frm │ │ ├── wait_classes_global_by_avg_latency.frm │ │ ├── wait_classes_global_by_latency.frm │ │ ├── waits_by_host_by_latency.frm │ │ ├── waits_by_user_by_latency.frm │ │ ├── waits_global_by_latency.frm │ │ ├── x@0024host_summary_by_file_io.frm │ │ ├── x@0024host_summary_by_file_io_type.frm │ │ ├── x@0024host_summary_by_stages.frm │ │ ├── x@0024host_summary_by_statement_latency.frm │ │ ├── x@0024host_summary_by_statement_type.frm │ │ ├── x@0024host_summary.frm │ │ ├── x@0024innodb_buffer_stats_by_schema.frm │ │ ├── x@0024innodb_buffer_stats_by_table.frm │ │ ├── x@0024innodb_lock_waits.frm │ │ ├── x@0024io_by_thread_by_latency.frm │ │ ├── x@0024io_global_by_file_by_bytes.frm │ │ ├── x@0024io_global_by_file_by_latency.frm │ │ ├── x@0024io_global_by_wait_by_bytes.frm │ │ ├── x@0024io_global_by_wait_by_latency.frm │ │ ├── x@0024latest_file_io.frm │ │ ├── x@0024memory_by_host_by_current_bytes.frm │ │ ├── x@0024memory_by_thread_by_current_bytes.frm │ │ ├── x@0024memory_by_user_by_current_bytes.frm │ │ ├── x@0024memory_global_by_current_bytes.frm │ │ ├── x@0024memory_global_total.frm │ │ ├── x@0024processlist.frm │ │ ├── x@0024ps_digest_95th_percentile_by_avg_us.frm │ │ ├── x@0024ps_digest_avg_latency_distribution.frm │ │ ├── x@0024ps_schema_table_statistics_io.frm │ │ ├── x@0024schema_flattened_keys.frm │ │ ├── x@0024schema_index_statistics.frm │ │ ├── x@0024schema_table_lock_waits.frm │ │ ├── x@0024schema_table_statistics.frm │ │ ├── x@0024schema_table_statistics_with_buffer.frm │ │ ├── x@0024schema_tables_with_full_table_scans.frm │ │ ├── x@0024session.frm │ │ ├── x@0024statement_analysis.frm │ │ ├── x@0024statements_with_errors_or_warnings.frm │ │ ├── x@0024statements_with_full_table_scans.frm │ │ ├── x@0024statements_with_runtimes_in_95th_percentile.frm │ │ ├── x@0024statements_with_sorting.frm │ │ ├── x@0024statements_with_temp_tables.frm │ │ ├── x@0024user_summary_by_file_io.frm │ │ ├── x@0024user_summary_by_file_io_type.frm │ │ ├── x@0024user_summary_by_stages.frm │ │ ├── x@0024user_summary_by_statement_latency.frm │ │ ├── x@0024user_summary_by_statement_type.frm │ │ ├── x@0024user_summary.frm │ │ ├── x@0024wait_classes_global_by_avg_latency.frm │ │ ├── x@0024wait_classes_global_by_latency.frm │ │ ├── x@0024waits_by_host_by_latency.frm │ │ ├── x@0024waits_by_user_by_latency.frm │ │ └── x@0024waits_global_by_latency.frm │ ├── sysbench_testdata │ │ ├── db.opt │ │ ├── sbtest10.frm │ │ ├── sbtest10.ibd │ │ ├── sbtest1.frm │ │ ├── sbtest1.ibd │ │ ├── sbtest2.frm │ │ ├── sbtest2.ibd │ │ ├── sbtest3.frm │ │ ├── sbtest3.ibd │ │ ├── sbtest4.frm │ │ ├── sbtest4.ibd │ │ ├── sbtest5.frm │ │ ├── sbtest5.ibd │ │ ├── sbtest6.frm │ │ ├── sbtest6.ibd │ │ ├── sbtest7.frm │ │ ├── sbtest7.ibd │ │ ├── sbtest8.frm │ │ ├── sbtest8.ibd │ │ ├── sbtest9.frm │ │ └── sbtest9.ibd │ ├── wenyz │ │ ├── db.opt │ │ ├── t2.cfg │ │ ├── t2.frm │ │ └── t2.ibd │ ├── xtrabackup_binlog_info │ ├── xtrabackup_binlog_pos_innodb │ ├── xtrabackup_checkpoints │ ├── xtrabackup_info │ └── xtrabackup_logfile ├── logs │ ├── mysql-bin.000001 │ ├── mysql-bin.000002 │ ├── mysql-bin.000003 │ ├── mysql-bin.000004 │ ├── mysql-bin.000005 │ ├── mysql-bin.000006 │ ├── mysql-bin.000007 │ ├── mysql-bin.000008 │ └── mysql-bin.index ├── my3506.cnf └── tmp

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
待兔 待兔
4个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Stella981 Stella981
3年前
KVM调整cpu和内存
一.修改kvm虚拟机的配置1、virsheditcentos7找到“memory”和“vcpu”标签,将<namecentos7</name<uuid2220a6d1a36a4fbb8523e078b3dfe795</uuid
Easter79 Easter79
3年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中,有一些需要使用全局唯一ID的场景,这种时候为了防止ID冲突可以使用36位的UUID,但是UUID有一些缺点,首先他相对比较长,另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID,并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求,最初Twitter把存储系统从MySQL迁移
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Stella981 Stella981
3年前
Android蓝牙连接汽车OBD设备
//设备连接public class BluetoothConnect implements Runnable {    private static final UUID CONNECT_UUID  UUID.fromString("0000110100001000800000805F9B34FB");
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
10个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这