mysql笔记

mysql日志分析

错误日志

• 日志默认开启，查询show variables like “%error_log%”，日志存在于/var/log/mysqld.log

二进制日志

• 日志默认开启，记录所有的DDL(Create等)和DML(insert等)，但不包括数据查询（SELECT、SHOW)语句
• 作用：
  • 灾难时的数据恢复
  • mysql的主从复制
• 查询show variables like “%log_bin%”，日志存在于/var/log/mysql/binlog，binlog_index是日志文件索引文件
• 查询show variables like “%binlog_format%”，查看记录的日志格式
  • STATEMENT ：记录SQL语句
  • ROW ：记录每一行的数据变更（默认）
  • MIXED ：混合前两种，默认第一种，特殊情况切换第二种
• 查看日志：mysqlbinlog [参数] logfile
  • -d : 指定数据库名称
  • -o ：忽略前n行命令
  • -v : 将行事件重构为SQL语句
  • -w ：将行事件重构为SQL语句，并输出注释信息
• 日志删除（在mysql内执行）：
  • rest master ：删除全部日志
  • purge master logs to ‘binlog.000005’：删除编号5之前日志
  • purge master logs before ‘2024-06-08’：删除6月8号之前日志
  • show variables like “%logbin_expire_logs_seconds%”; ：设置日志过期时长，过期自动删除（默认30天）

查询日志

• 日志默认未开启，记录客户端所有操作语言，包括SQL，可在/etc/my.cnf开启
• 查询show variables like “%general_log%”，日志存在于/var/lib/mysql/localhost.log

慢查询日志

• 记录所有执行时间超过参数long_query_time并且扫描记录值数不小于min_examined_row_limit的所有的SQL语句的日志，日志默认未开启，long_query_time默认为10s
• 在my.cnf种慢查询日志开启：slow_query_log = 1 long_query_time = 5
• 日志文件存放路径：/var/log/mysql/localhost-slow.log
• 默认情况不会记录管理语句和不使用索引进行查找的语句，开启如下：
  • log_slow_admin_statements=1
  • log_queies_not_using_indexs=1

mysql的主从复制

定义：

• 将主数据库的DDL和DML操作通过二进制日志传到从服务器中，然后在库中对这些日志重新执行（重做），从而使得主从数据同步。（主库：master，从库：slave）

作用：

• 热冗余，主从快速切换
• 实现读写分离，降低主库访问压力
• 可以在从库中执行备份，以避免备份期间影响主库使用

实现原理：

• master事务提交时，数据变更记录在二进制binlog文件中
• slav读取master的binlog文件，写入slave的中继日志relay log
• slave重做relay log中的事件，改变反映自己的数据

实际操作：

主库配置：

• 修改配置文件my.cnf：
  • server-id=1 # mysql服务id，保证整个集群一致
  • read-only=0 # 是否只读，1只读，0读写
• 重启服务：systemctl restart mysql
• 创建mysql远程链接账户：
  • CREATE USER ‘itcast’@‘%’ IDENTIFIED WITH mysql_native_password BY ‘123456’
• 为用户分配主从复制权限：
  • GRANT REPLICATION SLAVE ON * . * TO ‘itcast’@‘%’
• 通过指令查看二进制日志坐标：show master status;
  • file : 从哪个日志文件开始推送
  • position : 从哪个位置开始推送
  • binlog_ignore_db : 指定不需要的同步的数据库

从库配置：

• 修改配置文件my.cnf：
  • server-id=2 # mysql服务id，保证整个集群环境中唯一，与主库不一样
  • read-only=1 # 是否只读，1只读，0读写
  • super-read-only=1 # root也只能读
• 重启服务：systemctl restart mysql
• 登录从库，执行语句：
  
• 开启同步操作：
  • start replica; # 8.0.22之后
  • start slave; # 8.0.22之前
• 查看主从同步状态：
  • show replica status\G; # 8.0.22之后。列数据转换行
  • show slave status; # 8.0.22之前

数据库的备份与恢复

手动备份

• 查看数据存放文件：show variables like ‘datadir’;
• 将该地址下面所有文件复制一份出来

使用mysqldump命令备份

• 在terminal执行，备份所有数据库，生成sql文件：
  • mysqldump -uroot -p123456 --all-databases > /opt/all_db.sql
• 在terminal执行，备份一部分数据库，生成sql文件：
  • mysqldump -uroot -p123456 --databases dbname1 dbname2 > /opt/mydb1.sql
• 在terminal执行，备份某个数据库中的某些表，生成sql文件：
  • mysqldump -uroot -p123456 dbname tablename1 tablename2 > /opt/db3.sql
• 恢复数据库,登录mysql，执行
  • source /opt/all_db.sql

使用第三方工具备份

• navicate-备份-对象选择-开始