MySQL基础

认识MySQL日志

• 错误日志
  • --log-error
  • host_name.err
  • error.log
  • 默认在datadir下
• 慢查询日志
  • --slow_query_log
  • --long_query_time
  • host_name-slow.log
  • slow_log
  • mysqldumpslow
  • pt-query-digest
• 常规日志
  • --general_log
  • host_name.log
  • general_log
• 二进制日志
  • --log_bin
  • --expire-log-days
  • host_name-bin.000001
  • mysqlbinlog
  • binlog2sql
  • binary log什么时间刷新
    • 达到max_binlog_size
    • 执行flush logs;命令
    • 服务器重启，会扫描binlog.seq，并生成一个最后的binlog.seq+1的binlog文件
  • 二进制日志记录的格式
    • 建议binlog_format=row
  • 查询二进制日志文件
    • show binary logs 列出当前的日志文件及大小
    • show master status；显示MySQL当前的日志及状态（需要super，replication，client权限）
    • show binlog events in 'mysql-bin.000010';
      • mysql的二进制日志是以’事件‘（event）为单位存储到日志中，一个insert，update,....有多个事件组成
      • 专业名词：日志文件：mysql-bin.000010,字节偏移量(位置)
    • 一个dml语句基本事物包括哪些events
      • GTID
      • query
      • table_map
      • write_rows
      • xid
    • 一个ddl语句基本事物包括哪些events
      • GTID
      • query
  • 查看二进制日志
    • 二进制格式,无法用文本查看
    • 日志以紧凑二进制格式存储,以事件组合
    • 使用mysqlbinlog查看
      • mysqlbinlog -v --base64-out-put=decode-rows mysql-bin.000010|more
      • mysqlbinlog -v --base64-out-put=decode-rows mysql-bin.000010 mysql-bin.000011
      • --start-positon 在哪个位置开始
      • --stop-position 在那个位置结束
      • 可以将这个命令的参数好好看一下
  • 二进制日志维护
    • 删除二进制日志
    • 基于时间删除日志
      • set global expire_logs_days=7;
      • purge binary logs before now() -interval 3 days;
    • 根据文件名删除
      • purge binary logs to 'mysql-bin.000010';
    • 注意:主库purge时要确保日志已经传到从库上了,否则复制会出现问题,还有这个命令不会写到binlog中,所以从库是不会删除任何东西的
• 中继日志
  • hostname-relay.log
  • 这里如果使用默认配置，主机名修改后主从复制会出问题，因为sql线程找不到relay.log了，需要注意
  • relay_log_purge relay-log消费完了就清理掉
  • relay_log_space_limit reloy-log超过多少就不去master拉取日志了，可能会导致日志没同步过来的情况，特殊有需求才开
• 审计日志
  • audit_log
  • audit_log_file
  • audit.log
  • 官方收费组件,需要购买企业版
  • 基于策略的日志记录
  • 通过audit_log_policy选项设置
  • 提供日志记录选项ALL,NONE,LOGINS或QUERIES
  • 默认为ALL
  • 在日志文件中生成一个服务器活动审计记录
  • 内容取决于策略,可能包括:
    • 在系统上发生的错误的记录
    • 客户机连接和断开连接的时间
    • 客户机在连接期间执行的操作
    • 客户机访问的数据库和表

MySQL DBA日常管理命令

• 总览
  • 5.7新增加的sys schema,可以去官方手册上面学习,叶老师博客上有翻译
  • 认识information_schema数据库,这个数据库是memory引擎的,在ibdata1中
  • 学习利用information_schena的字典信息生成语句
    • ps:information_schema相当于MySQL的中央信息库模式和模式对象,服务器的统计信息(状态变量,设置,连接),该库不持久化,'虚拟数据库'可以通过select访问
• information_schema
  • 常见用法
    • select table_name , engine from information_schema.tables where table_schema = 'xxx';
    • select character_set_name,collation_name from information_schema.collation where id_default='Yes';
    • select table_schema,count(*) from information_schema.tables group by table_schema;
    • ps:information_schema是只读库

show语句及相关字典查询

• 核心语句
  • show databases;
  • show tables;
  • show processlist;
  • show create table table_name;
  • show index from table_name;
  • show open tables; #查看打开了哪些表
  • show table status;
  • show grants for user;
  • help show; #忘了看帮助

MySQL目录结构

• 数据目录
  • data目录
    • auto.cnf #server-uuid
    • error.log #错误日志
    • ib_buffer_pool #insert_buffer
    • ibdata1 #系统表空间
    • ib_logfile0 #redolog
    • ibtmp1 #临时表文件
  • logs目录 #binlog目录
  • tmp目录
• MySQL server目录
  • bin #管理命令目录
    • mysqld
    • mysql
    • mysqldump
    • mysqlbinlog
    • mysqladmin
    • mysql_config_edior
    • perror
    • mysqlslap #做性能测试
    • resolveip #ip解析,这个解析不对可能会连接不上数据库,可能发生的场景是将数据库目录拷贝到别的机器上启动后连接不上,可以用这个命令看一下解析是否出问题了
  • docs #文档文件
  • include #c语言头文件目录
    • /usr/local/mysql/bin/mysql_config文件内有c语言include目录地址,有些软件会用到这个文件去找到include目录和lib目录以及plugin目录等
  • lib #引用的库文件
    • 在lib目录中有个plugin目录,里面有一些MySQL的插件在里边,比如innodb引擎,半同步插件等
  • man #帮助手册目录,如果man命令找不到,可以执行以下命令:cp man/man* /usr/local/share/man/ -r
  • share #字符集和初始化sql
  • support_files #脚本目录
    • mysql.server #守护进程启动前启动
    • mysql-log-rotate #log切换脚本
    • mysql_multi.server #多实例脚本