MySQL

一、基础

mysql配置文件默认顺序从下列文件中读取：/etc/mysql/my.cnf  --> /etc/my.cnf  --> ~/.my.cnf

日志

　　二进制日志：binlog，主要用于数据还原，在主从模式中可用于数据复制，实现主从同步。重要！建议将二进制日志与数据文件存放于不同设备。

　　中继日志：relay log，用于主从模式中接收主节点的二级制文件，实现主从同步。

　　事务日志：分为重做日志（redo log）和回滚日志（undo log）。redo保证事务持久性，保存事务发生后执行后的数据版本，用来恢复事务提交后未写入数据文件的数据。undo保障原子性，保存事务发生前的数据版本，用于回滚。

　　错误日志：

　　　　MariaDB [(none)]> show global variables like 'log_error' G;
　　　　*************************** 1. row ***************************
　　　　Variable_name: log_error
　　　　Value: /var/log/mariadb/mariadb.log
　　　　1 row in set (0.00 sec)

　　慢查询日志：查询执行时长超过指定时长（long_query_time）的查询，即为慢查询

　　　　

二、备份、恢复

2.1 备份类型

根据备份时，数据库服务器是否在线：
　　冷备：cold backup
　　温备：warm backup
　　热备：hot backup

根据备份的数据集：
　　完全备份：full backup
　　部分备份: partial backup

根据备份时的接口（直接备份数据文件还是通过mysql服务器导出数据）：
　　物理备份：直接复制(归档)数据文件的备份方式；physical backup
　　逻辑备份：把数据从库中提出出来保存为文本文件；logical backup

根据备份时是备份整个数据还是仅备份变化的数据：
　　完全备份：full backup
　　增量备份：incremental backup
　　差异备份：differential backup

2.2 备份恢复演示

2.2.1 mysqldump+binary log

　mysqldump工具前置条件：逻辑备份，需要开启二进制日志，支持InnoDB热备、MyISAM温备、Aria温备　

语法：

mysqldump [options] [db_name [tbl_name ...]]

　　shell> mysqldump [options] db_name [tbl_name ...]　　    # 恢复时需要手动创建数据库
　　shell> mysqldump [options] --databases db_name ...　　   # 恢复时不需要手动创建数据库
　　shell> mysqldump [options] --all-databases　　　　　　    # 恢复时不需要手动创建数据库

options：
--lock-all-tables：请求锁定所有表之后再备份
--lockl-tables：请求锁定单个数据库所有表之后再备份
--single-transaction: 备份时启用一个大的事务完成的备份，能够对InnoDB存储引擎实现热备
-E,--events:备份指定数据库相关的所有事件调度器
-R,--routines:备份指定数据库相关的所有存储过程和存储函数
--triggers:备份表相关的触发器
--master-data:
    0:不记录
    1:记录为CHANGE MASTER TO语句
    2:记录为注释的CHANGE MASTER TO语句
--flush-logs:锁定表完成后，执行日志滚动

备份：

先记录现在的Position，以便恢复时还原

　　MariaDB [(none)]> show master statusG;
　　*************************** 1. row ***************************
　　File: mysql-bin.000003
　　Position: 7791967
　　Binlog_Do_DB:
　　Binlog_Ignore_DB:
　　1 row in set (0.00 sec)

对于InnoDB存储引擎来说，mysqldump支持热备，可以直接使用命令进行备份：

　　mysqldump -uroot --single-transaction --databases hellodb > hellodb.sql

对于MyISAM存储引擎来说，mysqldump不支持热备，需要先锁定要备份的数据库的表，在进行备份：

　　mysqldump -uroot --lock-tables[--lock-all-tables] --databases hellodb > hellodb.sql

恢复：

　　# mysql < hellodb.sql

　　# mysqlbinlog --start-position=106 --stop-position=7791967 mysql-bin.000003 |mysql hellodb

2.2.2 lvm-snapshot

　　原理就是基于lvm快照进行备份，没看懂。略

2.2.3 xtrabackup

　　xtrabackup工具使用前景：物理备份，需要下载（https://www.percona.com/）、开启二进制日志，支持InnoDB热备，增量备份；MyISAM温备，不支持增量

语法：

$ innobackupex --user=DBUSER --password=SECRET /path/to/backup/dir/
$ innobackupex --user=LUKE  --password=US3TH3F0RC3 --stream=tar ./ | bzip2 -
$ xtrabackup --user=DVADER --password=14MY0URF4TH3R --backup --target-dir=/data/bkps/

备份：

全量备份：innobackupex --user=root /backup/

　　使用innobakupex备份时，其会调用xtrabackup备份所有的InnoDB表，复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件，同时还会备份触发器和数据库配置信息相关的文件。这些文件会被保存至一个以时间命令的目录中。

增量备份：innobackupex --incremental /backup --incremental-basedir=BASEDIR

　　其中，BASEDIR指的是完全备份所在的目录，此命令执行结束后，innobackupex命令会在/backup目录中创建一个新的以时间命名的目录以存放所有的增量备份数据。另外，在执行过增量备份之后再一次进行增量备份时，其--incremental-basedir应该指向上一次的增量备份所在的目录。增量备份仅能应用于InnoDB或XtraDB表，对于MyISAM表而言，执行增量备份时其实进行的是完全备份。

准备：

一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。

对于完全备份，直接执行： innobackupex --apply-log  /backup/2020-12-22_16-20-33/

对于增量备份，只能同步已提交事务，不能同步位提交事务（--redo-only），并需要先“准备”全量备份的数据，在依次按时间顺讯准备各个增量的数据

于是，操作就变成了：
# innobackupex --apply-log --redo-only BASE-DIR

接着执行：
# innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1

而后是第二个增量：
# innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-2

恢复：

innobackupex --copy-back  /path/to/BACKUP-DIR

 需要修改文件权限

chown -R mysql.mysql /var/lib/mysql/*

三、主从复制

3.1 原理

主服务器工作流程：

       主服务器的dump线程负责接收从服务器的二进制日志文件请求并发送给对方

从服务器工作流程

        从服务器的I/O线程负责向主服务器请求二进制文件，并记录与中继日志文件中

        从服务器的SQL线程负责读取中继文件中的sql语句，进行重放，以此达到数据同步的效果（上面备份的时候已经提到过了二进制文件记录的是所有sql语句，所以从服务器拿来过后进行重新运行一遍就OK了~）

另外主从服务器的server id 不可重复，二进制文件中会标明serverid mysql的工作机制遇到同样的serverid 是不会进行重放的，以此避免循环复制

3.2 配置

一、简单的主从复制

1）主节点
1、启用二进制日志
log-bin=/mydata/binlogs/master-bin
log-bin-index=

2、为master选择一个在当前复制架构中惟一的server-id
server-id=1

3、为保障主从复制时的事务安全
sync_binlog=1

4、创建一个具有复制权限的用户帐号
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repluser'@'hostname' IDENTIFIED BY 'repluser_pass';
mysql> FLUSH PRIVILEGES;

2）从节点

1、启用中继日志（并关闭二进制日志）
relay-log=/mydata/relaylogs/relay-bin
relay-log-index=

2、为slave选择一个在当前复制架构中惟一的server-id
server-id=11

3、连接至主服务器
mysql> CHANGE MASTER TO MASTER_HOST='', MASTER_USER='', MASTER_PASSWORD='', MASTER_LOG_FILE='', MASTER_LOG_POS=#;
mysql> START SLAVE;

二、读写分离

 禁止从节点具有“写操作”权限

　　read-only=ON

注意：仅能限制那不具有SUPER权限用户无法执行写操作；

想限制所有用户：
　　mysql> FLUSH TABLES WITH READ LOCK;

三、双主模式

同理简单的主从复制，两个节点互为主从，在两个或多个数据库节点均开启二进制日志、中继日志、设计不同的server_id值、建立带有复制权限的账号、分别设置change master to，开启log-slave-updates参数，保证复制事件写入二进制日志。另外注意AUTO_INCREMENT属性列。

四、复制过滤器

　　master:
binlog_do_db=
binlog_ignore_db=

　　slave:
replicate_do_db=
replicate_ignore_db=

replicate_do_table= db_name.table_name
replicate_ignore_table=

replicate_wild_do_table=
replicate_wild_ignore_table=

五、半同步复制

需要新增插件，半同步复制原理为，在存储数据时，主节点存储完成后需要等待至少一个从节点同步完成后在反馈用户存储完成。
　　主服务器：
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
mysql> SHOW GLOBAL VARIABLES LIKE '%semi%';
mysql> SET GLOBAL rpl_semi_sync_master_enabled=ON;
mysql> SET GLOBAL rpl_semi_sync_master_timeout=1000;

　　从服务器：
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
mysql> SET GLOBAL rpl_semi_sync_slave_enabled=ON;
mysql> STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;

在主服务器验正半同步复制是否生效：
mysql> SHOW GLOBAL STATUS LIKE '%semi%';

一旦某次等待超时，会自动降级为异步

四、重要参数

innodb_file_per_table=ON　　每个InnoDB引擎的表拥有独立的表空间。默认所有InnoDB表会存储在一个共享空间ibdata1中

skip_name_resolve=ON　　　忽略mysql对连接的客户端进行DNS反向解析

log-slave-updates=ON　　　　从节点复制事件也会写入二进制日志，默认不写入

sync_master_info=1　　　　　每间隔多少事务刷新master.info

sync_relay_log=1　　　　　　控制relay_log 刷新到磁盘。 当等于=0，由操作系统Cache刷新， 大于0，随着事务的次数刷新。每次刷新调用fdatasync() 刷新数据和元数据

sync_relay_log_info=1　　　　每间隔多少事务刷新relay-log.info