mysql常见问题-学习笔记

MySQL常见问题

服务器配置类常见问题

分析一个Group By语句异常原因
- SQL_MODE
  - 配置MySQL处理SQL的方式
  - set [session/global/persist] sql_mode="xxx"
  - [mysqld] sql_mode=xxx
- 常用的SQL Mode
  - ONLY_FULL_GROUP_BY 对于group by聚合操作，如果出现在select中的列、having或group by子句的非聚合列，没有在group by出现，那么SQL语法检查报错。
  - ANSI_QUOTES 禁止用双引号来引用字符串
  - REAL_AS_FLOAT Real作为float的同义词
  - PIPES_AS_CONCAT 将||视为字符串的链接操作符而非或运算符
  - STRICT_TRANS_TABLES/STRICT_ALL_TABLES 在事务存储引擎/所有存储引擎启用严格模式出现，那么SQL语法检查报错
  - ERROR_FOR_DIVISION_BY_ZERO 不允许0作为除数
  - NO_AUTO_CREATE_USER 在用户不存在时不允许grant语句自动建立用
  - NO_ZERO_IN_DATA/NO_ZERO_DATE 日期数据内/日期数据不能含0
  - NO_ENGINE_SUBSTITUTION 当指定的存储引擎不可用时报错
如何比较系统运行配置和配置文件中的配置是否一致
- 使用set命令配置动态参数, session只影响当前session的配置;global会对整个mysql的配置产生影响，并不会当前的session进行修改;在mysql8.0版本中persist会对全局变量修改，并在mysql数据目录中生成一个新的数据配置文件，mysqld_auto.cnf文件，同样记录了全局变量值修改，下次mysql重启，会对mysqld_auto.cnf文件内容对配置命令进行配置。
  - set [session | @@session] system_val_name=expr
  - set [global | @@global] system_val_name=expr
  - set [persist | @@persist] system_val_name=expr
- 使用pt-config-diff 工具比较配置文件
  - pt-config-diff u=root,p=123456,h=localhost /etc/my.cnf
MySQL中关键性能参数
- max_connections 设置MySQL允许访问的最大链接数量
- interactive_timeout 设置交互连接的timeout时间
- wait_timeout 设置非交互连接的timeout时间
- max_allowed_packet 控制MySQL可以接受的数据包的大小
- sync_binlog 表示每写多少次缓冲会向磁盘同步一次binlog
- sort_buffer_size 设置每个会话使用的排序缓存区的大小
- join_buffer_size 设置每个会话使用的连接缓冲的大小
- read_buffer_size 指定了一个对MYISAM进行表扫描时所分配的读缓存池的大小
- read_rnd_buffer_size 设置控制索引缓冲区的大小
- 以上参数是对每个线程进行分配的，如果有100个连接，那么可能就会分配100以上分配的内存大小，如果参数设置过大，可能会导致内存浪费或内存溢出
- 基于会话参数配置
- binlog_cache_size 设置每个会话用于缓存未提交事务缓存大小
- 基于存储引擎的配置
- innodb_flush_log_at_trx_commit 0:每秒进行一次重做日志的磁盘刷新操作。1：每次事务提交都会刷新日志到磁盘中。2:每次事务提交写入系统缓存每秒想磁盘刷新一次
- innodb_buffer_pool_size 设置Innodb缓冲池的大小，应为系统可用内存的75%
- innodb_buffer_pool_instances 设置Innodb缓存示例个数，每个实例大小为总缓冲池大小/示例个数。
- innodb_file_per_table 设置每个表独立使用一个表空间文件

日志类常见问题

常用的MySQL日志有哪些？什么情况下使用这些日志

MySQL常用日志类型

错误日志(error_log) 记录mysql在启动、运行、停止时出现的问题
- 使用场景
  - 分析排除MySQL运行错误
  - 记录未经授权的访问
- 配置参数
  - log_error = $mysql/sql_log/mysql-error.log 错误日志的存放路径
  - log_error_verbosity = [1,2,3] 定义错误日志的级别：1表示Error messages 2表示Error and warning messages 3表示Error、warning、爱你的note mesages
  - log_error_services=[日志服务组件；日志服务组件] mysql8.0版本中的参数
    - 常用的mysql自带的日志组建
      - log_filter_internal 默认日志过滤组件，依赖log_error_verbosity
      - log_sink_internal 默认的日志输出组件，依赖log_error
      - log_sink_json 将错误日志输出到json文件
        
        安装组件：install component ‘file://component_log_sink_json';
        
        设置日志组件服务： set persist log_error_services=' log_sink_json';
      - log_sink_syseventlog 将错误日志输出到系统日志文件
    - 默认mysql使用的是UTC时区，怎么让mysql使用系统的使用时间
      - 查看当前mysql使用的时区：select @@log_timestamps
      - 设置mysql使用系统时间：set persist log_timestamps='SYSTEM'
常规日志(general_log) 记录所有发向MySQL的请求
- 参数配置
- general_off=[ON|OFF]
- general_log_file=$mysql/sql_log/general.log
- log_output=[FILE|TABLE|NONE]
慢查询日志(slow_query_log) 记录符合条件的查询，找到需要优化的SQL
- 参数配置
  - slow_query_log=[ON|OFF] 是否开启慢查询日志
  - slow_query_log_file=$mysql/sql_log/slowlog.log 慢查询日志存放路径
  - long_query_time=xxx秒 默认是10秒；设置SQL执行超过此时间的SQL记录下来
  - log_queries_not_using_indexes=[ON|OFF] 若为ON，则记录所有SQL没有使用索引的SQL语句日志
  - log_slow_admin_statements=[ON|OFF] 默认为OFF 把管理命令记录到慢查询日志
  - log_slow_slave_statememts=[ON|OFF] 把主从复制中主的sql在从库执行时，才会记录
二进制日志(binary_log) 记录全部有效的数据修改日志，基于时间点的备份和恢复,主从复制
- 怎么把二进制日志文件变成人能读懂的格式
  - mysqlbinlog --no-defaults -vv --base64-outpus=DECODE_ROWS $mysql/sql_log/mysql-bin.0001
- 参数配置
  - log-bin [=base_name] 只能在配置文件中修改，并重启才会生效，base_name二进制文件的目录和前缀
  - binlog_format=[ROW|STATEMENT|MIXED] 二进制日志格式，MySQL5.7后默认使用ROW格式
  - binlog_row_image=[FULL|MINIMAL|NOBLOB] 默认为FULL，二进制日志ROW格式副格式
  - binlog_rows_query_log_events=[ON|OFF] 默认为OFF，在row格式二进制文件记录实际需要的SQL，需要把这个参数设置为ON
  - log_slave_updates=[ON|OFF] 默认为OFF，在主从架构模式下，slave服务器上的二进制日志并不会记录从主同步过来的二进制内容
  - sync_binlog=[1|0] MySQL5.7后默认为1，二进制日志如何刷新磁盘； 0表示mysql并不会主动刷新日志到磁盘而由操作系统自己控制，1表示每写一次二进制日志就刷新磁盘
  - expire_logs_days=days 设置二进制日志的过期时间
  - 手动清理二进制文件
  - PURGE BINARY LOGS TO 'mysql-bin.010'; 清理mysql-bin.010之前的日志全部删除
  - PURGE BINARY LOGS BEFORE '2008-04-22 22:46:26'; 清理时间2008-04-22 22:46:26之前的二进制日志文件
中继日志(relay_log) 用于主从复制，临时存储在主从库同步的二进制日志
- 参数配置
  - relay_log=filename 中继日志的目录和前缀
  - relay_log_purge=[ON|OFF] 默认为ON，是否开启对relay_log的自动清除

如何通过日志来审计用户活动

存储引擎类常见问题

了解的MySQL存储引擎及适用场景

MyISAM存储引擎

MySQL5.6之前的默认引擎，最常用的非事务性存储引擎；适用场景读操作大于写操作的场景
特点：
- 非事务性存储引擎
- 以堆表方式存储
- 使用表级锁
- 支持Bree索引，空间索引，全文索引
检查MyISAM表的状况
- check table myisam的表名 检查MyISAM表的状况
- repair table myisam引擎的表名 修复myisam引擎的表
- myisampack -b -f 表名 压缩myisam引起的表

CSV存储引擎

以CSV格式存储的非事务性存储引擎；作为数据交换的中间表
特性
- 数据以csv格式存储
- 所有列都不能为null
- 不支持索引

Archive存储引擎

只允许查询和新增数据而不允许修改的非事务性存储引擎；场景：适用于日志或数据采集类引用；数据归档存储
特点
- 表数据使用zlib压缩
- 只支持Insert和Select
- 只允许在自增ID上建立索引

Memory存储引擎

是一种易失性非事务存储引擎，存储在内存中，断电易丢失；场景：用于换成字典映射表；缓存周期性分析数据
特点：
- 数据保存在内存中，读写速度很快，数据易丢失
- 所有字段长度固定
- 支持Btree和Hash索引，默认是Hash索引

InnoDB存储引擎

最常用的事务性存储引擎；适用场景：大多数OLTP场景
特点：
- 数据按主键聚集存储
- 支持行级锁以及MVCC(多版本并发控制)
- 支持Btree和自适应Hash索引
- MySQL5.6后支持全文索引和空间索引

NDB存储引擎

MySQL集群所使用的内存性事务存储引擎；场景：需要数据完全同步的高可用场景
特点：
- 数据存储在内存中
- 支持行级锁
- 支持高可用集群
- 支持Ttree索引

什么情况下InnoDB无法在线修改表结构

加全文索引 CREATE FULLTEXT INDEX name ON table(column)
加空间索引 ALTER TABLE geom ADD SPATIAL INDEX(g)
删除主键 ALTER TABLE tbl_name GROP PRIMARY KEY
增加自增列 ALTER TABLE add COLUMN id int AUTO_INCREMENT NOT NULL PRIMARY key
修改列类型 ALTER TABLE tbl_name CHANGE c1 c1 NEW_TYPE
修改字符集 ALTER TABLE tbl_name CHANGE SET = charset_name

在线DDL存在的问题

有部分语句不支持在线DDL
长时间对DDL操作会引起严重的主从延迟
无法对DDL操作进行资源限制

在无法进行在线修改表结构的情况下，要如何操作

如何更安全的执行DDL

pt-online-schema-change [OPTIONS] DSN
示例
- pt-online-schema-change --alter "add column modified_time timestamp" --excute D=stock,t=stock,u=dba,p=123456

InnoDB是如何实现事务的

事务的定义

事务要保证一组数据库操作，要么全部成功，要么全部失败

事务的特点

A(Atomicity) 原子性 回滚日志（Undo log）用于记录修改前的状态
C(Consistency) 一致性 重做日志（Redo log）：用于记录数据修改后的状态
I(Isolation) 隔离性 锁：用于资源隔离
- 锁的分类：
- 锁的属性：共享锁和排它锁
- 锁的粒度：页锁、表锁，行锁
- 锁的状态：意向共享锁、意向排它锁
D(Durability) 持久性 重做日志（Redo log）+（Undo log）

InnoDB读操作是否会阻塞写操作

查询需要对资源加共享锁(S)
数据修改需要对资源加派它锁(X)

MySQL架构类常见问题

My SQL的主从复制是如何工作的

MySQL主从复制的实现原理
- 主数据库开启二进制日志(binary log)
- 从服务会启动一个IO进程，配置完成，并和主库建立链接，主库启动一个特殊的二进制进程binlog_dump
- 从服务器的IO_Thread线程会从指定位置读取主服务器的binary log并写到中继日志(relay log)
- 从服务器的sql_thread会从relay log读取或重放到从库中
MySQL主从复制的配置步骤

##  在master服务器上的操作
# 0、查看主从服务器的MySQL数据库版本是否一致
mysql> select @@version;
# 1、开启binlog和gtid(可选）
# 查看binlog是否开启
mysql> show variables like 'log_bin%';
# 查看是否开启gtid
mysql> show variables like 'gtid_mode';
# 如果配置gtid模式，在/etc/my.cnf文件中需要配置gtid相关的参数
default_authentication_plugine='mysql_native_passwd'  在使用MMM和MHA和MGR需要使用此配置
enforce-gtid-consistency
log-slave-updates = on
master_info_repository=TABLE
relay_log_info_repository=TABLE

# 2、建立同步所用的数据库账号
# 创建复制账号
mysql> create user repl@'ip.%' identified by 'passwd';
# 授权复制账号
mysql> grant replication slave on *.* to repl@'ip.%';

# 3、适用master_data参数备份数据库
～$ mysqldump --single-transaction -uroot -p --routines --triggers --events --master-data=2 --all-databases > master.sql

# 4、把备份危机传输到slave服务器
$ `scp master.sql root@ip:/root` 把master.sql拷贝到slave的root目录下

## 在slave服务器上的操作
# 1、开启binlog(可选）开启gtid(可选)

# 2、恢复master上的备份数据库
$ mysql -uroot -p < /root/master.sql

# 3、适用change master配置链路(配置io线程链接master实例需要的参数)
mysql> change master to master_host='主库ip', master_log_file='mysql-bin.000012', master_log_pos=710; 这个master_log_file和master_log_pos偏移量可以在master.sql中看到

# 4、适用start slave启动复制
# 启动从库
mysql> start slave user='repl' passwd='passwd';
# 查看slave的状态
mysql> show slave status;

### 半同步复制
# 在master服务器上
# 查看是否安装同步插件
mysql> show  plugins;
# 安装半同步插件
mysql> install plugin rpl_semi_sync_master;
# 查看半同步相关的变量
mysql>show variables like 'rpl%';
# 设置半同步的超时时间
mysql> set persist rpl_semi_sync_master_timeout=500;
# 启动半同步复制
mysql>set persist rpl_semi_sync_master_enabled=on;

# 在slave服务器上
# 需要查看从服务器上是否安装版同步插件
# slave安装半同步服务插件
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
# 查看slave的半同步参数
mysql> show variables like 'rpl%';
# 设置slave的半同步参数
mysql> set persist rpl_semi_sync_slave_enabled=on;
# 启动slave的io线程
mysql> start slave io_thread user='repl' passwd='passwd';
# 查看slave状态
mysql> show slave status \G;
# 查看master半同步的状态
mysql> show global status like 'rpl%';
# 查看slave半同步的状态
mysql> show global status like 'rpl%';

比较一下基于GTID方式的复制和基于日志点的复制

什么是基于日志点的复制
- 传统的主从复制方式
- slave请求master的增量日志依赖日志偏移量
- 配置链路是需要指定master_log_file和master_logs_pos参数
什么是基于GTID的复制
- GTID=source_id:transaction_id 是全局事务id的缩写；source_id是主机的uuid，transcation_id是事务的id
- slave增量同步master的数据依赖于未同步的事务ID
- 复制链路时，slave可以根据已经同步的事务ID继续进行自动同步。
这两种复制方式的各自特点
- 基于日志复制，兼容性好；基于GTID同老版本的MySQL和MariaDB不兼容
- 基于日志复制，支持MMM和MHA架构；基于GTID仅支持MHA架构
- 基于日志复制，主备切换后很难找到新的同步点；基于GTID复制，可以很方便的找到未完成同步的事务ID
- 基于日志复制，可以方便跳过复制错误；基于GTID复制，只能通过置入空事务的方式跳过错误

比较一下MMM和MHA两种高可用架构的优缺点

MMM和MHA两种架构的作用
- 对主从复制中的master实例进行健康监控
- 当master宕机后把写VIP(虚拟ip)迁移到新的master
- 重新配置集群中的其他slave对新的master同步
MMM适用的主从复制架构
MMM架构的故障转移步骤
- slave服务器上操作
  - 完成原主上已复制日志的恢复
  - 使用change master命令配置新主
- 主备服务器上的操作
  - 设置read_only=off
  - 迁移写vip到新主服务器
- MMM架构需要的资源
  - 主DB， 2个，用于主备模式的主主复制配置
  - 从DB，0-N个，用于配制0台或多台从服务器
  - IP地址，2n+1个，N为MySQL服务器的数量
  - 监控用户，1个，用于监控数据库状态的MySQL用户(replication client)
  - 代理用户，1个，用于MMM的agent端用于改变read_only状态(super,replication,client,process)
  - 复制用户，1个，用于配制MySQL复制的MySQL用户(replication slave)
MMM架构的配置步骤

# 配置主主复制的集群架构


# 按照centos的yum扩展包

# 安装所需的perl包

# 安装MMM工具包

# 配置并启用MMM服务

MMM架构的优点

提供了读写vip的配置，使读写请求都可以达到高可用
工具包相对完善，不需要额外的开发脚本
故障转移后，可以持续对MySQL集群进行高可用监控

MMM架构的缺点

故障切换简单粗暴容易丢事务，解决方法：主备用半同步复制
不支持GTID的复制方式
自行修改perl脚本实现
社区不活跃，很久不更新版本，维护难

MHA适用的主从复制架构

MHA架构的故障转移步骤

选举最新更新的slave
尝试从宕机的master保存二进制日志
应用差异的中继日志到其他slave
应用从master保存的二进制日志
选举新的slave为新的master
配置其他slave向新的master同步

MHA架构的资源

主DB， 1个，用于初始主从服务的Master服务器
从DB，2到N个，可以配置2台或多台从服务器
IP地址，n+2个，N为MySQL服务器的数量
监控用户，1个，用于监控数据看状态的MySQL用户(all privileges)
复制用户, 1个，用于配制MySQL复制的MySQL用户(replication slave)

MHA架构的配置步骤

* 配置一主多从的复制架构
* 按照centos的yamu扩展源及依赖包
# 到https://dl.fedoraproject.org/pub/epel/下载对应系统的rpm包
$  wget https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm 
# 安装rpm包,安装的目录存放在/etc/yum.repos.d目录下
$ rpm -ivh epel-release-latest-7.noarch.rpm
# 把epel.repo的包校验关闭，把gpgcheck改为1
gpgcheck=1 

* 配置集群内各主机的ssh免认证
# 配置ssh
$ ssh-keygen
# 把服务器的ssh的公钥拷贝到其他服务器
$ ssh-copy-id -i /root/.ssh/id_rsa root@ip

* 在各节点安装mha_node软件
# 安装MHA依赖包
$ yum install -y perl-DBD-MySQL ncftp perl-DBI.x86
# 安装MHA软件包
$ rpm -ivh mha4mysql-node-0.57-0.el7.noarch.rpm

* 在管理节点安装mha_manager
# 安装MAH管理节点依赖包
$ yum install -y perl-Config-Tiny.noarch perl-Time-HiRes.x86_64 perl-Paraller-ForkManager perl-Log-Dispatch-Perl.noarch
# 安装MHA管理节点包
$ rpm -ivh mha4mysql-manager-0.57-0.el7.noarch.rpm

* 配置并启动MHA管理进程
# 配制master服务器的mha账户并授权
mysql> create user dba_mha@'192.168.1.%' identified by '123456';
mysql> grant all privileges on *.* to dba_mha@'192.168.1.%';
# 创建mha的目录和文件
$ mkdir /etc/mha && touch mysql-mha.conf
# 配置参数如下：
[server default]
user=dba-mha
password=123456
manager_workdir=/home/mha
manager_log=/home/mha/manager.log
remote_workdir=/home/mha
ssh_user=root
repl_user=repl
repl_password=123456
ping_interval=1
master_binlog_dir=/home/mysql/sql_log
ssh_port=22
master_ip_failover_script=/usr/bin/master_ip_failover
secondary_check_script=/usr/bin/masterha_secondary_check -s 192.168.1.91 -s 192.168.192 -s 192.168.1.93
[server1]
hostname=192.168.1.91
candidate_master=1
[server2]
hostname=192.168.1.92
candidate_master=1
[server3]
hostname=192.168.1.93
no_master=1

#启动mha服务
$ nohup masterha_manager --conf=/etc/mha/mysql-mha.conf &

MHA架构的优点

支持GTID的复制方式和机遇日志点的复制方式
可以多个slave中选举最合适的新master
会尝试从旧master中尽可能多的保存为同步日志

MHA架构的缺点

未能获取到旧主未同步的日志，解决方法：使用半同步复制
需要自行开发写vip转移脚本
只监控master而没有对slave实现高可用

如何减小主从复制的延迟

主从复制延迟产生的原因
- 主数据库的大事务操作
- 主从服务器之间的网络延迟
- io线程顺序写入relay log 过程，影响比较小
- sql thread读写relay log过程中
减小主从延迟的处理方法
- 化大事务为小事务，分批更新数据
- 使用pt-online-schema-change工具对DDL操作
- 网络延迟
  - 减少单词事务处理的数据量已减少产生的日志文件大小
  - 减少主上所同步的slave的数量
- 由于主上多线程的写入，从上单线程恢复所引起的延迟
  - 适用MySQL5.7后的多线程复制
  - 适用MGR复制架构

说下对MGR的认识

什么是MGR复制
- MGR（MySQL group Replication）
- 是官方推出的一种基于Paxos协议的复制
- 是一种不同于异步复制的多master复制集群
MGR复制架构
MGR两种模式
- group_replication_single_primary_mod=[ON｜OFF] on是单主模式，off上多主模式
- 单主模式默认的工作模式
- 多主模式

MGR复制架构的配置步骤

# 安装group_replication插件
mysql> install plugin group_replication soname 'group_replication.so';
# 在第一个实例上建立复制用户
mysql> create user repl@'192.168.1.%' identified by '123456';
mysql> grant replication slave on *.* to repl@'192.168.1.%';
# 配置第一组实例
# 查看主主复制的变量
mysql> show variables like 'group_replication%';
mysql>show varialbes like 'transcation_write_set_extraction';
# 修改事务的加密算法为XXHASH64
mysql>set perisist transcation_write_set_extraction='XXHASH64';
#
mysql> show variables like 'binlog_checksum';
mysql> set persist binlog_checksum=none;
# 对主复制节点设置一个名字
mysql> show variables like 'group_replication_group_name';
mysql>select uuid();
mysql> set persist group_replication_group_name='aaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa';
mysql>show variables like 'group_replication_local_address';
mysql> set persist group_replication_local_address=33061;
mysql> set global group_replcation_bootstrap_group=on; 在主服务器启动后设置为off
# 启动主服务器
mysql> start group_replication;
# 把其他实例加入组
修改/etc/my.conf配置文件的参数，如下
# group replication specific options
plugin-load=group_replication.so
group_replication=FORCE_PLUS_PERMANENT
transaction-write-set-extraction=XXHASH64
loose-group_replication_start_on_boot=OFF
loose-group_replication_bootstarp_group=OFF
loose-group_replication_group_name='aaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa'
group_replication_local_address='本机ip:33061'
group_replication_group_seeds='主节点ip:33061'
binlog_checksum=NONE
# 重启mysql实例

# 启动从服务器节点
mysql> change master to master_user='repl', master_password='123456' for channel 'group_replication_recovery';
mysql> start group_replication;
# 查看复制的成员表
mysql> use performance_schema;
mysql>show tables;
mysql>selelct * from replication_group_members\G;

MGR复制架构的优点

Group replication组内成员基本无延迟
可以支持多谢、读写服务高可用
数据强一致性，可以保证不丢失事务

MGR复制架构的缺点

只支持InnoDB存储引擎的表，并且每个表上必须要有一个主键
单主模式下很难确认下一个Primary
只能用在gtid模式下的复制形式，且日志格式必须为row。

MGR复制架构的适用场景

对主从延迟十分敏感的应用场景
希望对读写提供高可用场景
希望可以保证数据强一致的场景

如何解决数据库读/写负载大的问题？

如何解决读负载大的问题

增加slave服务器，读写分离、通过客户端实现或者数据库中间件(MyCAT/ProxySQL/Maxscale)

如何解决写负载大的问题

数据库中间件(MyCAT/ProxySQL/Maxscale) 进行分库分表

MySQL备份恢复类常见问题

如何对数据库进行备份

备份方式
- 逻辑备份和物理备份
- 全量备份和增量备份
常用的备份工具
- mysqldump 最常用的逻辑备份工具，支持全量备份和条件备份
  - mysqldump的优点
    - 备份结果为可读SQL文件，用于跨版本跨平台恢复数据
    - 备份文件的尺寸小于物理备份，便于长时间存储
    - MySQL发行版自带工具，无需安装第三方软件
  - mysqldump的缺点
    - 只能进行单线程执行备份恢复任务，备份恢复速度较慢
    - 为完成一致性备份对备份表加锁，容易造成阻塞
    - 会对Innodb Buffer Pool造成污染
  - mysqldump实例
    - mysqldump -uroot -p --database stock > stock.sql 备份stock数据库
    - mysqldump -uroot -p stock stock > stock.sql 对stock数据库的stock表备份
    - mysqldump -uroot -p --master-data=2 --all-databases > all.sql 备份所有数据库的二进制日志信息
    - mysqldump -uroot -p --where "count>20" stock stock > stock.sql 备份stock库stock表 count 大于20的数据
- mysql恢复
  - mysql -uroot -p stock <stock.sql 恢复stock数据库
- mysqlpump 多线程逻辑备份工具，mysqldump的增强版本，最早在mysql5.7版本引入
  - mysqlpump的优点
    - 语法同mysqldump高度兼容，学习成本低
    - 支持基于库和表的并行备份，可以提高逻辑备份的性能
    - 适用ZLIB和Lz4算法对备份进行压缩
  - mysqlpump的缺点
    - 基于表进行并行备份，对于大表来说性能较差
    - 5.7.11之前版本不支持一致性并行备份
  - mysqlpump实例
    - mysqlpump --compress-output=zlib --set-gtid-purged=off --databases stock > stock.zlib 对所有数据库进行zlib压缩备份
    - zlib_devompress stock.zlib stock.sql 解压缩
    - mysql -uroot -p stock <stock.sql 恢复stock数据库
    - mysqlpump --users --execlude-databases=sys,mysql,percona,stock --set-gtid-purged=off -uroot -p 只备份数据库的账号
- xtrabackup Innodb在线物理备份工具，支持多线程和增量备份
  - xtrabackup的优点
    - 支持Innodb存储引擎的在线热备份，对Innodb缓冲没有影响。
    - 支持并行对数据库的全备和增量备份
    - 备份和恢复效率比逻辑备份高
  - xtrabackup的缺点
    - 做单表恢复时比较复杂
    - 完整的数据拷贝，备份文件比逻辑备份大
    - 对跨平台和数据库版本的备份恢复支持度不如逻辑备份
  - xtrabackup示例
    - yum install -y rsync numactl libaio libev
    - yum install -y perl-DBD-MySQL.x86_x64 perl-DBI.x86 perl-Time-HiRes.x86_64 perl-IO-Socket-SSL.noarch perl-TermReadKey.x86_64
    - rpm -ivh percona-xtrabackup-24-12-1.el7.x86_64.rpm
    - innobackupex --user=root --password=123456 /home/db_backup
    - innobackupex --user=root --password=123456 --parallel=2 /home/db_backup 20221127 --no-timestamp 多线程自定义备份文件名称
    - innobackupex --apply-log /home/db/_backup/20221127 备份恢复

如何对MySQL进行增量备份和恢复

mysql -uroot -p stock < stock.sql
mysqlbinlog --start-position=337 --database=stock mysql-bin.000002 > stock_diff.sql
mysql -uroot -p stock <stock_diff.sql
innobackupex --user=root --password=pwd 全备目录 xtrabackup全量备份
innobackupex --user=root --password=pwd --incremental 增量备份目录 --incremental-basedir=上一次全量备份的目录 xtrabackup增量备份
innobackupex --apply-log --redo-only 全备目录
innobackupex --apply-log --redo-only 全备目录 --incremental-dir=第1...N次增量目录
innobackupex --apply-log 全备目录 全量数据恢复
关闭mysql实例，把undo和data的旧目录替换成新的
rm -rf data_old/ undo_old/
mv data data_old
mv undo_log undo_old
mkdir data undo_log
innobackupex --move-back 全备目录

如何对binlog进行备份

备份方式
- 利用cp命令进行离线备份
- 适用mysqlbinlog命令在线实时在线备份
- mysqlbinlog --raw --read-from-remote-server --stop-never --host 备份机ip --port=3306 -u repl --p xxxxx 启动二进制日志文件名

MySQL管理及监控类问题

对MySQL进行过哪些监控

性能指标

QPS 数据库每秒处理的请求数量
- 方法一
  - show global status like 'Com%';
  - sum(com_xxx) 对上面com所有的数相加
- 方法二
  - show global status where like 'Queries'
  - QPS=(queries2-queries1)/时间间隔
  - 示例
  - show global status where variable_name in ('Queries', 'uptime')
TPS 数据库美妙处理的事务数量
- show global status where variable_name in ('com_start','com_delete', 'com_update');
- Tc=com_insert+com_delete+com_update
- TPS = (Tc2-Tc1)/(time2-time1)
并发数 数据库当前并发处理的会话数量
- show global status like 'threads_running'
连接数 连接到数据库会话的数量
- show global status like 'threads_connected'
缓存命中率 Innodb的缓存命中率
- (innodb_buffer_pool_read_requests - innodb_buffer_pool_reads) / innodb_buffer_pool_read_requests * 100%
- innodb_buffer_pool_read_requests 表示从缓冲池读取的次数
- innodb_buffer_pool_pool_reads 从物理磁盘读取的次数
- innodb_buffer_pool_read_requests 读取的总次数

功能指标

可用性 数据库是否正常对外提供服务
- 周期性链接数据库服务器并执行select @@version;
- mysqladmin -uxxx -pxxx -hxxxx ping
阻塞 当前是否有阻塞的回话
死锁 当前事务是否会产生死锁
- pt-deadlock-logger u=dba,p=xxxx,h=127.0.0.1 --create-dest-table -dest u=dba,p=xxx,h=127.0.0.1,D=crn,t=deadlock
- seet global innodb_print_all_deadlocks=on
慢查询 实时慢查询监控
- 通过慢查询日志监控
- 通过information_schema.'PROCESSLIST' 表实时监控
主从延迟 数据库主从复制链路是否正常
- show slave status 查看seconds_behind_master这个值
- 启动两个线程
- pt-heartbeat --user=xx --password=xxx -h master --create-table --database xxx --update --daemonize --interval=1 周期性更新监控表的值
- pt-heartbeat --user=xx --password=xxx -h slave database crn --monitor --daemonize --log /tmp/slave_lag.log 查询监控表的值

MySQL优化及异常处理

数据库服务器负载过大的问题

服务器负载过大的原因
- 服务器磁盘IO超负荷
  - iostat-dmx 1
    - 慢查询造成的磁盘IO爆表
      - MySQL输出大量日志
      - MySQL正在进行大批量写
      - 慢查询产生了大量的磁盘临时表
        
        show global status like '%tmp%' 查看磁盘临时表的数量
    - 解决慢查询造成的磁盘IO爆表问题
      - 优化慢查询，减少使用磁盘临时表
      - 增加temp_table_size和max_heap_table_size参数的大小
  - Isof
- 存在大量阻塞线程
  - show processlist
  - 阻塞监控
- 存在大量并发慢查询
  - show processlist
  - 慢查询日志
- 存在其他占用cpu的服务
  - ps
  - top
- 服务器硬件资源原因
  - 硬件监控

主从数据库数据不一致

主从数据库延迟为0
IO_THREAD和SQL_THREAD状态为yes
相同查询在主从服务器中的查询结果不同

主从数据不一致的原因

对从服务器进行了写操作
使用了sql_slave_skip_counter或注入了空事务的方式修复错误
使用了statement格式的复制

针对主从数据不一致问题的，处理方法

在slave服务器设置read_only=ON的权限设置
设置super_read_only=on设置super用户
使用row格式的复制
使用pt-table-sync修复数据
- pt-table-sync --exec --charset=utf8 -- database=stock --table=stock --sync-to-master h=从库ip,u=dba,p=password 在主库执行

主服务器连不上问题

主从服务器网络是否通畅
是否存在防火墙，过滤了数据端口
复制链路配置的用户名和密码是否正确，是否有相应的权限

主键冲突问题

跳过故障数据
- 使用gtid复制，只能使用空事务的方法
检查主从数据一致性
或直接删除从库主键冲突数据

数据库不存在

跳过故障数据
使用pt-table-sync修复数据

relay_log损坏

找到已经正确同步的日志点
- 怎么找正确同步的日志点
  - show slave status \G中两个参数Relay_Master_Log_File 和Exec_Master_Log_Pos
使用reset slave删除relay_log
在正确同步日志点后重新同步日志

posted @ 2022-11-29 22:23 phper-liunian 阅读(54) 评论(0) 编辑收藏举报

刷新页面返回顶部

似水流年

mysql常见问题-学习笔记

MySQL常见问题

服务器配置类常见问题

日志类常见问题

常用的MySQL日志有哪些？什么情况下使用这些日志

MySQL常用日志类型

如何通过日志来审计用户活动

存储引擎类常见问题

了解的MySQL存储引擎及适用场景

MyISAM存储引擎

CSV存储引擎

Archive存储引擎

Memory存储引擎

InnoDB存储引擎

NDB存储引擎

什么情况下InnoDB无法在线修改表结构

在线DDL存在的问题

在无法进行在线修改表结构的情况下，要如何操作

如何更安全的执行DDL

InnoDB是如何实现事务的

事务的定义

事务的特点

InnoDB读操作是否会阻塞写操作

MySQL架构类常见问题

My SQL的主从复制是如何工作的

比较一下基于GTID方式的复制和基于日志点的复制

比较一下MMM和MHA两种高可用架构的优缺点

MMM架构的优点

MMM架构的缺点

MHA适用的主从复制架构

MHA架构的故障转移步骤

MHA架构的资源

MHA架构的配置步骤

MHA架构的优点

MHA架构的缺点

如何减小主从复制的延迟

说下对MGR的认识

MGR复制架构的配置步骤

MGR复制架构的优点

MGR复制架构的缺点

MGR复制架构的适用场景

如何解决数据库读/写负载大的问题？

如何解决读负载大的问题

如何解决写负载大的问题

MySQL备份恢复类常见问题

如何对数据库进行备份

如何对MySQL进行增量备份和恢复

如何对binlog进行备份

MySQL管理及监控类问题

对MySQL进行过哪些监控

性能指标

功能指标

MySQL优化及异常处理

数据库服务器负载过大的问题

主从数据库数据不一致

主从数据不一致的原因

针对主从数据不一致问题的，处理方法

主服务器连不上问题

主键冲突问题

数据库不存在

relay_log损坏

公告