20171112 MySQL进阶（二）

2017-11-16 本文已影响14人哈喽别样

日志

备份和恢复

MySQL Replication 复制

一、日志：

日志的分类：
查询日志：general_log
慢查询日志：log_slow_queries
错误日志：log_error， log_warnings
二进制日志：binlog
中继日志：relay_log
事务日志：innodb_log

（一）查询日志：记录查询语句，一般不开启

日志存储位置：
文件：file
表：table (mysql.general_log)
配置的变量
general_log={ON|OFF}
general_log_file=HOSTNAME.log
log_output={FILE|TABLE|NONE}

（二）慢查询日志：运行时间超出指定时长的查询

根据变量long_query_time确定指定时长
日志存储位置：
文件：FILE
表：TABLE(mysql.slog_log)
配置的变量
log_slow_queries={ON|OFF}
slow_query_log={ON|OFF}
slow_query_log_file=
log_output={FILE|TABLE|NONE} log_slow_filter=admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk：允许存储为慢日志的原因
log_slow_rate_limit
log_slow_verbosity

（三）错误日志

记录如下几类信息：
- mysqld启动和关闭过程中输出的信息
- mysqld运行中产生的错误信息
- event scheduler运行时产生的信息
- 主从复制架构中，从服务器复制线程启动时产生的日志
配置的变量
log_error={/var/log/mariadb/mariadb.log|OFF}
log_warnings={ON|OFF}

（四）二进制日志

（1）基本概念

定义：用于记录引起数据改变或存在引起数据改变的潜在可能性的语句(STATEMENT)或改变后的结果(ROW)，也可能是二者混合
功用：“重放”，数据库备份和主从复制时使用
查看二进制日志文件列表：
SHOW MASTER|BINARY LOGS;
查看当前正在使用的二进制日志文件：
SHOW MASTER STATUS;
查看二进制日志文件中的事件：
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

（2）相关变量

定义存放信息格式的变量
binlog_format={STATEMENT|ROW|MIXED}
STATEMENT：语句，默认
ROW：行
MIXED：混编
服务器变量：
- log_bin=/PATH/TO/BIN_LOG_FILE
  只读变量
- session.sql_log_bin={ON|OFF}
  控制某会话中的“写”操作语句是否会被记录于日志文件中
- max_binlog_size=1073741824
  日志最大存储容量，单位B
- sync_binlog={1|0}
  事务COMMIT后是否立即同步至二进制日志，打开后性能有损失，但消除了数据丢失的风险

（3）mysqlbinlog

由于二进制日志以二进制格式存储，使用一般文本查看工具可能对日志产生损坏，并且也无法查询到有效信息，故设计了mysqlbinlog作为查询二进制日志信息的专用工具
语法：mysqlbinlog OPTIONS
- 指定时间范围
  --start-datetime=YYYY-MM-DD hh:mm:ss
  --stop-datetime=
- 指定起止点
  -j, --start-position=#
  --stop-position=#
- 指定用户、主机、密码
  --user, --host, --password

（4）二进制日志事件格式：

二进制日志截取片段如下：

# at 553
#160831 9:56:08 server id 1 end_log_pos 624 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1472608568/*!*/;
BEGIN
/*!*/;

事件的起始位置：# at 553
事件发生的日期时间：#160831 9:56:08
事件发生的服务器id：server id 1
事件的结束位置：end_log_pos 624
事件的类型：Query
事件发生时所在服务器执行此事件的线程的ID： thread_id=2
语句的时间戳与将其写入二进制日志文件中的时间差：exec_time=0
错误代码：error_code=0
设定事件发生时的时间戳：SET TIMESTAMP=1472608568/*!*/;
事件内容：BEGIN

（5）实验1：二进制日志的相关操作

开启二进制功能，设置二进制日志的存放路径

mkdir -p /mydata/{data,logs}
chown -R mysql.mysql /mydata/*
vim /etc/my.cnf.d/server.cnf
log_bin = /mydata/logs/master-log    // 在[server]下添加一行
systemctl restart mariadb.service

此时在日志存放目录下，出现了日志文件

MySQL下查询二进制日志文件状态信息：SHOW BINARY LOGS;
二进制文件滚动记录，通过命令FLUSH LOGS;强行滚动日志，再查询二进制日志文件，发现了新的滚动文件
查询当前使用的二进制日志文件：SHOW MASTER STATUS;
查询指定二进制日志记录的事件：SHOW BINLOG EVENTS IN 'master-log.000001';
临时关闭二进制日志：SET @@session.sql_log_bin=OFF;

可以看到当关闭二进制日志后，执行添加一条新记录的操作后，二进制日志没有新事件的记录
二进制日志在数据库的备份恢复和主从复制操作中发挥了很大的作用，一般情况下不会关闭，只有在执行“重放”操作时才有临时关闭的需求

（五）中继日志：

从服务器上记录下来从主服务器的二进制日志文件同步过来的事件

（六）事务日志：

事务型存储引擎innodb用于保证事务特性的日志文件

二、备份和恢复：

备份：存储的数据副本，需要注意主机备份过程中原始数据可能持续发生改变
恢复：把副本应用到线上系统，需要注意仅能恢复至备份操作时刻的数据状态
时间点恢复：通过二进制日志binary logs恢复备份过程中发生改变的数据

（一）为什么备份？

灾难恢复：人为错误、硬件故障（冗余）、软件故障（bug）、自然灾害、黑客攻击等原因造成
测试

（二）备份时应该注意事项：

能容忍最多丢失多少数据
恢复数据需要在多长时间内完成
需要恢复哪些数据
做恢复演练：
测试备份的可用性
增强恢复操作效率

（三）备份类型：

备份的数据集的范围：完全备份和部分备份
完全备份：整个数据集
部分备份：数据集的一部分，比如部分表
全量备份、增量备份、差异备份：
全量备份：即完全备份
增量备份：仅备份自上一次完全备份或增量备份以来变化的那部分数据
差异备份：仅备份自上一次完全备份以来变化的那部分数据
物理备份、逻辑备份：
物理备份：复制数据文件进行的备份，效率较高
逻辑备份：从数据库导出数据另存在一个或多个文件中，效率较低
根据数据服务是否在线：
热备：读写操作均可进行的状态下所做的备份，实际生产环境只有热备是可接受的
温备：可读但不可写状态下进行的备份
冷备：读写操作均不可进行的状态下所做的备份

（四）备份策略：

全量+差异 + binlogs
全量+增量 + binlogs

（五）备份内容：

数据
二进制日志、InnoDB的事务日志；
代码（存储过程、存储函数、触发器、事件调度器）
服务器的配置文件

（六）备份工具：

（1）mysqldump

mysql服务自带的备份工具；逻辑备份工具
完全、部分备份
- InnoDB：热备
- MyISAM：温备
备份策略：全量+binlog

（2）cp/tar

lvm2：快照（请求一个全局锁），之后立即释放锁，达到几乎热备的效果；物理备份
注意：不能仅备份数据文件；要同时备份事务日志
前提：要求数据文件和事务日志位于同一个逻辑卷

（3）Innobackup/xtrabackup

由Percona提供，开源工具，支持对InnoDB做热备，物理备份工具
支持以下备份方式
完全备份、部分备份
完全备份、增量备份
完全备份、差异备份
备份策略
全量+差异+binlog
全量+增量+binlog

（七）mysqldump备份工具的使用：

（1）备份机制：逻辑备份，即备份内容为SQL脚本

库：CREATE DATABASE
表：CREATE TABLE
数据：INSERT INTO

（2）具体使用语法：

备份：
- mysqldump [OPTIONS] database [tables]：
  备份单库，可以只备份其中的一部分表（部分备份），备份内容中不存在创建库的SQL语句
- mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]：
  备份多库，备份单库也推荐使用
- mysqldump [OPTIONS] --all-databases [OPTIONS]：
  备份所有库
MyISAM存储引擎：支持温备，备份时要锁定表
-x, --lock-all-tables：锁定所有库的所有表，读锁
-l, --lock-tables：锁定指定库所有表
InnoDB存储引擎：支持温备和热备
--single-transaction：创建一个事务，基于此快照执行备份
其它选项：
-R, --routines：备份指定库的存储过程和存储函数；
--triggers：备份指定库的触发器；
-E, --events：备份指定度的时间调度器
--master-data[=#]
1：记录为CHANGE MASTER TO语句，此语句不被注释
2：记录为CHANGE MASTER TO语句，此语句被注释
--flush-logs：锁定表完成后，即进行日志刷新操作

（3）实验：使用mysqldump实现“完全+binlog”方式的备份与还原操作

步骤1：远程备份数据库
mysqldump -uroot -pcentos -h 192.168.136.230 --single-transaction -R --triggers -E --master-data=2 --flush-logs --databases hellodb > /root/hellodb-$(date +%F-%H:%M:%S).sql
步骤2：查看备份文件，其中找到"CHANGE MASTER TO"语句，可以看到在全量备份时正在工作的二进制日志和所处位置

步骤3：这是备份服务器上原始数据库，可以看到没有备份的hellodb数据库

步骤4：此时在原数据库中进行增、删、改等操作，本例中添加了两条记录

步骤5：在备份服务器上导入全量备份的SQL脚本：
mysql -uroot -pcentos < /root/hellodb-2017-11-14-10\:45\:45.sql
可以看到成功恢复了hellodb数据库，但是全量备份后添加的两条记录丢失

步骤6：在原数据库中将二进制文件导出，并传输至备份服务器

mysqlbinlog master-log.000007 > /tmp/binlog.sql
scp /tmp/binlog.sql 192.168.136.130:/root

步骤7：将二进制日志备份文件导入，可以看到丢失的记录也恢复了
mysql -uroot -pcentos < /root/binlog.sql

注意：
由于步骤1中的远程备份操作使用了选项"flush-logs" 强制刷新日志，故导入的二进制日志备份文件全部为全量备份后的操作；
若步骤1中没有添加此选项，则需要在导出二进制日志备份时使用''mysqlbinlog -j ##"指定从第##行开始，保证二进制日志备份记录与全量备份没有重复，##可以从全量备份文件的"CHANGE MASTER TO"语句中看到，如步骤2所示

（八）Xtrabackup备份工具的使用

（1）Xtrabackup备份采用物理备份：

物理备份，速率快、可靠
备份完成后自动校验备份结果集是否可用
还原速度快

（2）具体使用语法：

innobackupex：对用C语言编写的xtrabackup程序的Perl脚本封装工具
完全+binlog
备份：innobackupex --user --password= --host= /PATH/TO/BACKUP_DIR
准备：innobackupex --apply-log /PATH/TO/BACKUP_DIR
还原：--copy-back，注意还原需要在mysqld主机本地进行，mysqld服务不能启动

（3）实验：使用xtrabackup实现“完全+增量+binlog ”方式的备份与还原操作

基本命令：
- 备份：
  全量备份：
  innobackupex --user=DBUSER --password=DBUSERPASS /path/to/BACKUP-DIR/
  增量备份：
  innobackupex --user=DBUSER --password=DBUSERPASS --incremental /backup --incremental-basedir=BASEDIR
- 准备：
  只有全量备份时：
  innobackupex --apply-log --redo-only BASEDIR
  有增量备份时，先进行增量备份合并入全量备份的操作：
  innobackupex --apply-log --redo-only BASEDIR --incremental-dir=INCREMENTAL-DIR
- 恢复：
  innobackupex --copy-back BASEDIR
步骤1.1：执行对数据库的全量备份

mkdir -pv /mydata/backups
innobackupex --user=root --password=centos --host=localhost /mydata/backups/
ls -l /mydata/backups      // 查看目录下以时间命名的备份目录

步骤1.2：对数据库的表增加一条记录模拟对数据库的修改，执行对数据库的增量备份

MariaDB [(none)]> INSERT INTO students (NAME,AGE,GENDER) VALUES ('Philip',17,'M')
innobackupex --user=root --password=centos --host=localhost --incremental /mydata/backups/ --incremental-basedir=/mydata/backups/2017-11-14_14-22-51/

步骤1.3：再对数据库的表增加一条记录，执行对数据库的第2次增量备份。

MariaDB [(none)]> INSERT INTO students (NAME,AGE,GENDER) VALUES ,('Nancy',19,'F');
innobackupex --user=root --password=centos --host=localhost --incremental /mydata/backups/ --incremental-basedir=/mydata/backups/2017-11-14_14-30-19/

步骤1.4：对数据库的表记录进行修改后，数据库服务器服务不可用。输出第2次增量备份后的二进制日志，至此备份操作全部完成

MariaDB [(none)]> UPDATE students SET DESP='physical' WHERE AGE>22;
// 查询最后一次增量备份后二进制日志事件位置
cat /mydata/backups/2017-11-14_14-37-45/xtrabackup_binlog_info
// 将之后的日志导出
mysqlbinlog -j 1026 /mydata/logs/master-log.000007 > /root/mybinlog.sql

步骤2.1：备份后的准备工作

// 依次将增量备份合并至全量备份上
innobackupex --apply-log --redo-only /mydata/backups/2017-11-14_14-22-51/
innobackupex --apply-log --redo-only /mydata/backups/2017-11-14_14-22-51/ --incremental-dir=/mydata/backups/2017-11-14_14-30-19/
innobackupex --apply-log --redo-only /mydata/backups/2017-11-14_14-22-51/ --incremental-dir=/mydata/backups/2017-11-14_14-37-45
// 最后对合并后的全量备份做整理准备
innobackupex --apply-log /mydata/backups/2017-11-14_14-22-51/

步骤2.2：将备份文件复制到全新的数据库服务器上，至此还原前的准备工作完成

scp -r /mydata/backups/2017-11-14_14-22-51/ 192.168.136.130:/root
scp /root/mybinlog.sql 192.168.136.130:/root

步骤3.1：在新服务器上还原数据库，注意不能有任何数据库内容，服务更不能启动

innobackupex --copy-back /root/2017-11-14_14-22-51/
ls -l /var/lib/mysql/     // 查看备份还原情况
chown -R mysql.mysql /var/lib/mysql/*
systemctl start mariadb

此时恢复至最后一次增量备份时的状态

步骤3.2：在新服务器上还原二进制日志

方法1：一次导入SQL脚本

SET @@session.sql_log_bin=OFF;
mysql -uroot -pcentos < /root/mybinlog.sql
SET @@session.sql_log_bin=ON;

方法2：在mysql环境下执行SQL脚本

SET @@session.sql_log_bin=OFF;
mv /root/mybinlog.sql /tmp/
mysql -uroot -pcentos
MariaDB [(none)]> \. /tmp/mybinlog.sql
SET @@session.sql_log_bin=ON;

此时全部恢复完成

步骤3.3：还原成功后，建议立即全量备份一次

mkdir -pv /mydata/backups
innobackupex --user=root --password=centos --host=localhost /mydata/backups/

三、MySQL Replication 复制：

（一）主从(MASTER/SLAVE)架构

由于架构和原理的限制，数据库服务器无法通过共享存储设备实现高可用
数据库系统采用主从架构、异步复制的方式实现高可用
主从架构：
- Master：主节点主要负责写操作，一般只有一个
- Slave：从节点主要负责读，个数不限
异步复制：
- 从节点I/O线程：监视主节点的二进制日志，将更新复制到自身的中继日志中
- 从节点SQL线程：将中继日志中的更新重放
- 异步复制的特性，决定了主从结构中从节点的数据总是晚于主节点更新，数据的准确性不高
- 当从节点落后主节点更新过多时，只能通过暂时挂起从节点的服务，恢复主节点的备份实现

（二）主从架构的分类

一主多从：主负责写操作，多从负责读操作
一从一主：一主多从的简单版
级联复制：
一台服务器作为上层主节点的从节点，同时作为下层多个从节点的主节点；
自身不执行数据的写操作，只需生成二进制日志即可；
使用BLACKHOLE引擎实现
循环复制
双主复制
两台服务器都是主节点，在执行写操作外，还监控对方的二进制日志更新中继日志用于重放同步；
由于两条写操作指令的执行先后顺序不同会引发不同的结果，故双主复制可能导致数据的混乱
半同步复制

（三）MySQL主从复制的实现

步骤一：主服务器的设置

ntpdate 172.18.0.1                // 时间同步
vim /etc/my.cnf.d/server.cnf      //[server]段落下添加如下两行
log_bin = master-log
server_id = 1
systemctl start mariadb
// mysql登录
MariaDB [(none)]> GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.136.%'' IDENTIFIED BY 'centos'    // 给从服务器的账号授权允许复制
MariaDB [(none)]> FLUSH PRIVILEGES;
SHOW MASTER STATUS;              // 查看当前二进制日志所在位置

步骤二：从服务器的设置

ntpdate 172.18.0.1                // 时间同步
vim /etc/my.cnf.d/server.cnf 
relay_log = relay-log
server_id = 2                     // 独一无二，不能与主服务器和其他从服务器相同
systemctl start mariadb
// mysql登录
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.136.230',MASTER_USER='repluser',MASTER_PASSWORD='centos',MASTER_LOG_FILE='master-log.000003',MASTER_LOG_POS=497;
MariaDB [(none)]> START SLAVE IO_THREAD,SQL_THREAD;     // 开启复制
MariaDB [(none)]> SHOW SLAVE STATUS\G                   // 查看当前从服务器状态

红框1：主服务器的地址，用户；
红框2：从服务器获取的主服务器二进制日志和所在位置；
红框3：从服务器IO和SQL线程的开启状态

步骤3：测试MySQL的主从复制功能
主服务器上添加数据库、表，增加表记录

MariaDB [(none)]> CREATE DATABASE mydb;
MariaDB [(none)]> USE mydb;
CREATE TABLE students (ID INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,NAME CHAR(20) NOT NULL,AGE TINYINT NOT NULL, GENDER ENUM('M','F'),DESP VARCHAR(50));
INSERT INTO students (NAME,AGE,GENDER) VALUE ('Alice',20,'F'),('Bob',22,'M');
SELECT * FROM students;

可以看到当前主服务器的二进制日志位置已经前进至1054

登录从服务器，检查数据库同步成功

MariaDB [(none)]> SHOW DATABASES;
USE mydb;
MariaDB [mydb]> SELECT * FROM students;

查看从服务器的状态信息

MariaDB [mydb]> SHOW SLAVE STATUS\G

可以看到从服务器上显示的主服务器二进制文件和位置信息跟随主服务器进行了同步

（四）MySQL主主复制的实现

配置要求：
1. 两个节点各自都要开启binlog和relay log
2. 为保证id自动增长且不冲突，需要自定义增长方式：
  - 定义一个节点使用奇数id
    auto_increment_offset=1
    auto_increment_increment=2
  - 另一个节点使用偶数id
    auto_increment_offset=2
    auto_increment_increment=2
3. 服务启动后，都授权有复制权限的用户账号并各把对方指定为主节点
步骤1：节点1，配置并授权

vim /etc/my.cnf.d/server.cnf
server_id = 1
log_bin = master-log
relay_log = relay-log
auto_increment_offset = 1
auto_increment_increment = 2
systemctl start mariadb
MariaDB [(none)]> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.136.%' IDENTIFIED BY 'centos';
MariaDB [(none)]> SHOW MASTER STATUS;

记录此时节点1二进制日志文件和位置

步骤2：节点2，配置并授权

vim /etc/my.cnf.d/server.cnf 
server_id = 2
log_bin = master-log
relay_log = relay-log
auto_increment_offset = 2
auto_increment_increment = 2
systemctl start mariadb
MariaDB [(none)]> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.136.%' IDENTIFIED BY 'centos';
MariaDB [(none)]> SHOW MASTER STATUS;

记录此时节点1二进制日志文件和位置

步骤3：节点1，开启从节点，此时的主节点信息从步骤2获得

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.136.130',MASTER_USER='repluser',MASTER_PASSWORD='centos',MASTER_LOG_FILE='master-log.000001',MASTER_LOG_POS=428;
MariaDB [(none)]> START SLAVE IO_THREAD,SQL_THREAD;
MariaDB [(none)]> SHOW SLAVE STATUS\G

步骤4：节点2，开启从节点，此时的主节点信息从步骤1获得

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.136.230',MASTER_USER='repluser',MASTER_PASSWORD='centos',MASTER_LOG_FILE='master-log.000004',MASTER_LOG_POS=428;
MariaDB [(none)]> START SLAVE IO_THREAD,SQL_THREAD;
MariaDB [(none)]> SHOW SLAVE STATUS\G

步骤5：节点1，数据库初始数据如下

MariaDB [(none)]> USE mydb;
MariaDB [mydb]> SELECT * FROM students;

步骤6：节点1，对数据增加记录，在节点2中查询已经同步成功

MariaDB [mydb]> INSERT INTO students (NAME,AGE,GENDER) VALUES ('John',19,'M'),('Penny',23,'F');
MariaDB [mydb]> SELECT * FROM students;

步骤7：节点2，对数据增加记录，在节点1中查询已经同步成功

MariaDB [(none)]> USE mydb;
MariaDB [mydb]> INSERT INTO students (NAME,AGE,GENDER) VALUES ('James',25,'M'),('Marry',24,'F');
MariaDB [mydb]> SELECT * FROM students;

可以发现：不同节点的ID并非自身递增，如节点2增加的记录并不是从"2"后的"4"继续编码，而是以当前表中最大的编号"5"后最近的偶数"6"作为编码，故主主复制的数据编码可能出现跳过ID的情况

20171112 MySQL进阶（二）

一、日志：

（一）查询日志：记录查询语句，一般不开启

（二）慢查询日志：运行时间超出指定时长的查询

（三）错误日志

（四）二进制日志

（1）基本概念

（2）相关变量

（3）mysqlbinlog

（4）二进制日志事件格式：

（5）实验1：二进制日志的相关操作

（五）中继日志：

（六）事务日志：

二、备份和恢复：

（一）为什么备份？

（二）备份时应该注意事项：

（三）备份类型：

（四）备份策略：

（五）备份内容：

（六）备份工具：

（1）mysqldump

（2）cp/tar

（3）Innobackup/xtrabackup

（七）mysqldump备份工具的使用：

（1）备份机制：逻辑备份，即备份内容为SQL脚本

（2）具体使用语法：

（3）实验：使用mysqldump实现“完全+binlog”方式的备份与还原操作

（八）Xtrabackup备份工具的使用

（1）Xtrabackup备份采用物理备份：

（2）具体使用语法：

（3）实验：使用xtrabackup实现“完全+增量+binlog ”方式的备份与还原操作

三、MySQL Replication 复制：

（一）主从(MASTER/SLAVE)架构

（二）主从架构的分类

（三）MySQL主从复制的实现

（四）MySQL主主复制的实现

猜你喜欢

热点阅读