8月31日上课内容（MySQL第四天）
（一）
课前回顾（30分钟）
前言：
（二）
MySQL 日志功能详解
前言：
MySQL日志分类
1：查询日志 ：query log
2：慢查询日志：slow_query_log 查询执行时长超过指定时长的查询操作所记录日志
3：错误日志：error log
4：二进制日志：binary log
5：中继日志：relay log (主从复制会讲解)
6：事务日志：transaction log
查询日志 （query log一般不启用）
general_log = {ON|OFF}: 是否启用查询日志
general_log_file = /logs/mysql/general_log：当log_output为FILE类型时，日志信息的记录位置；
log_output = {TABLE|FILE|NONE}
log_output = TABLE,FILE
慢查询日志 （slow_query_log 必须启用）
慢查询日志产生的来源
SQL语句返回的结果集大，或者SQL语句没有被优化器优化，或SQL语句没有使用索引
慢查询日志的作用
慢查询日志用于对执行速率较慢的SQL语句就像过滤，有利于SQL代码的优化
1：执行时长超出指定时长的操作
show global variables like 'long_query_time'; 查看指定的时长
set global long_query_time = 自定义时长
2：slow_query_log = {ON|OFF}：是否启用慢查询日志
set global slow_query_log = ON
3：slow_query_log_file = mariadb1-slow.log
4：log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk 过滤条件
5：log_slow_rate_limit = 1 指定记录速率
6：log_slow_verbosity = 指定内容级别
中继日志（relay log）
在主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取到的时间
事物日志（transaction log）
事物日志由事物型存储引擎自行管理和使用，无需手动管理
redo log：将一组SQL语句安装先后顺序再执行一次
undo log：回滚到之前数据集没有发生改变的状态
错误日志 （error log 必须启用）
1：错误日志信息产生的来源
mysqld启动和关闭过程中输出的信息；
mysqld运行中产生的错误信息；
event scheduler运行一个event时产生的日志信息
在主从复制架构中的从服务器上启动从服务器线程时产生的日志信息；
2：错误日志的作用
二进制日志可以反应MySQL数据库的错误信息，用于调试
3：如何开启错误日志
log_error = /path/to/somefile
log_warnings = {ON|OFF}：是否记录警告信息于错误日志中；
二进制日志（binary log）
1：二级制日志信息产生的来源
记录导致数据改变，或者潜在改变数据的SQL语句
2：二级制日志的作用
用于通过'重新执行'日志文件中的记录的事件（SQL语句）来生成数据副本，也就是用于主从复制
3：服务器变量
（1）sql_log_bin = [on|off] 是否记录二进制日志, 通常为on
（2）log_bin= on ：记录位置，通常为on，如果为off，那么需要在my.cnf配置文件中添加一项：log_bin=mysql-bin，用来指明二进制日志保存的路径名，对于yum安装的MySQL，保存于/var/lib/mysql/mysql-bin.000001这样的二进制日志中
（3）binlog_format = MIXED ：二进制记录的格式
（4）max_binlog_size = 1073741824 ：单个二进制文件的最大值，默认为1G
到达最大值会自动滚动
文件达到上限的大小未必是精确值
（5）max_binlog_cache_size = 18446744073709547520
（6）sync_binlog = 0：设定多久同步一次二进制日志文件；0表示不同步；任何正值都表示记录多少个语句后同步一次；
4：查看二进制文件的信息
show master status： 显示当前正在使用的二进制文件名
show {binary | master} logs：查看当前二进制文件记录end_log_pos（结束位置）的值，其实当前正在记录的二进制文件的大小。
5：日志记录的格式分类
基于“SQL语句”记录: statement
基于“行”记录：row
“混合模式” ：mixed，系统自行判断
6：二进制日志文件的构成
日志文件：mysql-bin.文件序号
例如： mysql-bin.000001
索引文件：mysql-bin.index
例如：mysql-bin.index
7：使用 mysqlbinlog 客户端命令工具查看二进制文件
mysqlbinlog /var/lib/mysql/mysql-bin.000001
8： 二进制格式解析
# at 19364
#140829 15:50:07 server id 1 end_log_pos 19486 Query thread_id=13 exec_time=0 error_code=0
SET TIMESTAMP=1409298607/*!*/;
GRANT SELECT ON tdb.* TO tuser@localhost
/*!*/;
# at 19486
事件发生的日期和时间；(140829 15:50:07)
事件发生在服务器的标识（server id）
事件的结束位置：（end_log_pos 19486）
事件的类型：(Query)
事件发生时所在的服务器执行此事件的线程的ID：（thread_id=13）
语句的时间戳与将其写入二进制文件中的时间差：（exec_time=0）
错误代码：（error_code=0)
事件内容：（SET TIMESTAMP=1409298607/*!*/;
GRANT SELECT ON tdb.* TO tuser@localhost）
GTID事件专属：
事件所属的全局事务的GTID：（GTID 0-1-2）
（三）
MySQL 备份和恢复
前言：
为什么要备份
数据无价
制定备份策略的注意点
1：可容忍丢失多少数据
2：恢复需要在多长时间内完成
3：备份的对象
数据、二进制日志和InnoDB的事务日志、SQL代码（存储过程和存储函数、触发器、事件调度器等）、服务器配置文件
备份类型
（1）站在数据集是否完整的角度上完全备份，部分备份
（2）站在完全备份的基础上
增量备份，差异备份
（3）站在是否影响数据集读写的角度上
热备份：在线备份，读写操作不受影响；
温备份：在线备份，读操作可继续进行，但写操作不允许
冷备份：离线备份，数据库服务器离线，备份期间不能为业务提供读写服务
MyISAM存储引擎: 能够实现温备
InnoDB存储引擎: 能够实现热备
（4）站在数据存储角度上
物理备份：直接复制数据文件进行的备份
逻辑备份：从数据库中“导出”操作数据的SQL语句，再执行，实现备份
备份策略需要考虑的因素
持锁的时长备份过程时长
备份负载
恢复过程时长
数据库备份具体解决方案
数据：完全备份 + 增量备份备份：物理 + 逻辑
备份工具介绍
mysqldump: 逻辑备份工具，适用于所有存储引擎，温备；但是对InnoDB存储引擎支持热备；
scp, tar 等文件系统工具：物理备份工具，适用于所有存储引擎；冷备；完全备份，部分备份；
lvm2的快照：几乎热备；借助于文件系统工具实现物理备份；
mysqlhotcopy: 几乎冷备；仅适用于MyISAM存储引擎
实施备份期间，备份工具的常用组合
（一）mysqldump+binlog:
mysqldump：完全备份，通过备份二进制日志实现增量备份
（二）lvm2快照+binlog：
lvm2快照使用scp、tar进行物理完全备份
通过备份二进制日志实现增量备份
（三）xtrabackup（最佳）
由percana提供的热备，在物理层，实现完全备份和增量备份
（四）
MySQL 基于mysqldump备份工具实战演练（非常重要）
前言： 细节提示：先执行 show global variables like 'log_bin';看看log_bin的值，如果服务器变量log_bin的值为OFF，需要修改my.cnf配置文件，将log_bin=mysql-bin，再重启MariaDB
mysqldump: 客户端SQL导出工具，通过mysql协议连接至mysqld服务器
（1）使用格式：
mysqldump [选项] > backup.sql
（2）选项说明
-A: 备份所有的数据库
-B 数据库1, 数据库2, 数据库3：指定需要备份的数据库
MyISAM, InnoDB: 温备
-x 或 --lock-all-tables：锁定所有表
-l 或 --lock-tables：锁定备份数据库中的表
InnoDB：热备
--single-transaction：启动一个大的单一事务实现备份
-B 或 --databases 数据库1，数据库2：备份指定的数据库
-C 或 --compress：压缩传输
注意：二进制文件不应该与数据文件放在同一个磁盘上
# 前言：由于mysqldump只能实现数据库中指定数据库或数据表的完全备份，无法实现对表的单行或多行的增量备份，那么对应增量备份，将使用二进制文件进行备份
第一步：
使用mysqldump做完全备份，其中使用 --master-data=2 选项会在hellodb.sql中增加一条注释说明完全备份的结束位置,并且会显示完全备份结束后，滚到到了那个日志文件
mysqldump -uroot -h192.168.23.11 -p123456 -B uplooking --lock-tables --flush-logs > uplooking.sql
第二步：
对完全备份之后的时间点到当前时间点做增量备份，在需要备份的服务器上操作，其中8631是完全备份后的时间点，因此，将从这个点开始到最后的日志文件的SQL语句都备份出来
mysqlbinlog /data/mysql/mysql-bin.000005 > increment.sql
（对于之后的每次增量做备份，可以无需指定开始时间，我们可以在增量备份之前将，二进制日志滚动，那么每次增量的时候，登入mysql使用 flush logs 命令，滚动日志，那么如何再有日志产生，将会写在下一个日志文件里，那么对于上一次增量备份到此时，可以直接使用客户端命令 mysqlbinlog /data/mysql/mysql-bin.000005 > increment.sql , 再将备份文件拷贝到备份的数据库中就可以了）
第三步：
set sql_log_bin = OFF; # 关闭二进制文件
show variables like 'sql_log_bin'; # 查看状态
第四步：
将hellodb.sql和increment.sql文件都拷贝到对应的数据恢复的主机上，将备份的SQL文件进行导入（关闭备份数据库服务器的二进制日志功能）
mysql -p123456 < hellodb.sql
mysql -p123456 < increment.sql
第五步：
# 当关闭二进制文件之后再导入文件系统上保存的SQL文件，但是SQL文件不要放在/root目录下面，因为/root目录对其它用户的权限是0，而mysqld进程的属主和属组都是mysql，因此无法进入/root目录里面，去读取SQL文件，切记
# 导入了SQL文件之后，在开启二进制文件
set sql_log_bin = ON; # 开启二进制文件
show variables like 'sql_log_bin'; # 查看状态
注意：SQL文件进行导入的时候不能一个同样的SQL进行重复导入，如果是插入语句，就会一直插入值
（五）
MySQL 基于lvm2的备份实战演练（非常重要）
前言： lvm2实现热备的原理是基于lvm2的快照功能，lvm2可以实现数据集不大的情况下的热备。
实战过程如下：这里的演示是在一台Mariadb服务器上进行创建快照，将快照中的文件scp到备份服务器上的Mariadb的数据目录中，这样就实现了基于lvm2的备份
（1）新添加一块磁盘，将磁盘进行分区之后，先在分区上创建pv物理卷，然后在pv物理卷上创建vg卷组，之后在vg卷组上创建lv逻辑卷，将lv逻辑卷创建文件系统（使用ext4文件系统），将创建了文件系统的lv逻辑卷挂载至数据库目录，这里最好是创建两个lv逻辑卷，两个lv逻辑卷可以实现将数据库目录与二进制日志文件分开，分别放在不同的lv逻辑卷上，那么当使用lvcreate命令进行快照的时候，只对数据库目录进行快照，不对二进制文件进行快照，可以将
0: 添加一块硬盘，sdb给个40G就ok
1：fdisk /dev/sdb # 创建分区/dev/sdb1、/dev/sdb2，设置分区类型为linux lvm
2：partx -a /dev/sdb # 让内核重读
3：pvcreate /dev/sdb1 /dev/sdb2 # 创建两个pv
4：vgcreate myvg /dev/sdb1 /dev/sdb2 # 在两个pv上创建一个vg
5：lvcreate -L 20G -n mysql_data myvg # 创建一个名为mysql_data的逻辑卷
6：lvcreate -L 19.9G -n mysql_binlog myvg # 创建一个名为mysql_binlog的逻辑卷
7：mkfs -t ext4 /dev/myvg/mysql_data # 在mysql_data的逻辑卷逻辑卷上创建文件系统
8：mkfs -t ext4 /dev/myvg/mysql_binlog # 在mysql_binlog的逻辑卷上创建文件系统
9：mkdir -pv /data/{mysql,binlog} # 创建数据库目录和二进制文件目录
10：mount /dev/myvg/mysql_data /data/mysql # 挂载数据库目录
11：mount /dev/myvg/mysql_binlog /data/binlog # 挂载数据库目录
12：chown -R mysql.mysql /data # 让mysql用户可以读写
13：修改/etc/my.cnf配置文件：log-bin=/data/binlog/mysql-bin 和 datadir = /data/mysql
14：cd /usr/local/mysql/;
./scripts/mysql_install_db --user=mysql --datadir=/data/mysql # 如果是二级制或者编译安装的Mariadb这样初始化数据库，如果是yum安装的Mariadb，那么在数据库重启的时候自动hui
15：systemctl start mariadb.service # 启动Mariadb
（2）在数据库依然提供服务的情况下，首先登入mysql数据库，请求锁定所有的表，添加读锁，这个时候所有的数据表只能够读数据，不能够写数据
1：flush tables with read lock;
（3）让二进制日志文件滚动跟新，并且记录新的二进制文件的开始位置，那么数据库新的数据更改操作将记录到新的二进制文件中，做增量备份的时候需要再次滚动二进制日志，并且导出当前的日志为SQL文件
# 这里的日志滚动其实可以不要
flush logs;
（4）使用lvcreate 命令创建快照
查看现在数据目录有多大了，如果是40M，那么创建快照的时候给个50M就ok了
du -sh /data/mysql
创建名为lvm_snap的快照 ，-s 表示创建快照，-p r 表示快照的权限是只读的，-n 表示快照的名，此时就会在生成一个/dev/myvg/lvm_snap的快照
lvcreate -L 50M -s -p r -n lvm_snap /dev/myvg/mysql_data
（5）释放读锁，MySQL服务器可以读写正常工作，此时立马让日志滚动一下，让之后的所有操作都记录在新的二进制文件中
unlock tables;
flush tables
（6）将快照挂载，将数据库目录通过文件系统命令cp -a等不修改属性和时间戳的情况下拷贝
mount /dev/myvg/lvm_snap /mnt
scp -rp /mnt/* 192.168.23.32:/data/mysql
# 上课的时候试一试这里是否需要拷贝二进制文件，如果不拷贝二进制文件，那么备份服务器使用的二进制文件应该是从第一个开始的。但是，现在只需要备份数据，所以拷贝二进制文件也是可以的。
scp -rp /data/binlog/* 192.168.23.32:/data/binlog/
此时在192.168.23.32这台Mariadb备份服务器中，已经存在Mariadb的数据目录/data/mysql和二进制目录/data/binlog/，且配置文件/etc/my.cnf都已经配置完毕。在这个时候，还需要修改/data目录的属主和属组，在192.168.23.32这台Mariadb备份服务器中操作如下：
chown -R mysql.mysql /data
（7）使用客户端命令mysqlbinlog 导出SQL文件
mysqlbinlog /data/binlog/`mysql -p123456 -e 'show master status;' | grep mysql-bin | awk '{print $1}'` > increment.sql
（8）使用scp命令将数据库目录、导出的SQL文件拷贝到备份的MySQL服务器上，在备份的MySQL服务器上将数据库目录放在对应的数据库目录，并且导入增量备份的SQL文件
scp -rp increment1.sql 192.168.23.11:/root/
mysql -p123456 < increment.sql
（9）之后的备份，均使用二进制文件做增量备份即可，备份前切记先滚动新的日志文件，再将当前二进制日志文件导出为SQL文件即可
mysql -p123456 -e 'flush logs;'
mysqlbinlog /data/binlog/`mysql -p123456 -e 'show master status;' | grep mysql-bin | awk '{print $1}'` > increment2.sql
scp increment2.sql 192.168.23.32:/root/
# 在备份的服务器上执行导入增量的sql备份文件
mysql -p123456 increment2.sql
（六）
MySQL基于xtrabackup备份—热备工具
- xtrabackup（仅对InnoDB存储引擎支持热备）
- percona公司开发
- 改进的MySQL分支：percona-server
- 存储引擎改进：InnoDB —> XtraDB
- 使用本地的RPM包安装
- 1：下载xtrabackup的RPM包(https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.8/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.8-1.el7.x86_64.rpm)
- 2：yum install percona-xtrabackup-2.3.2-1.el7.x86_64.rpm
- 开启：innodb_file_per_table = ON
使用rpm -ql percona-xtrabackup 查看提供的程序
/usr/bin/innobackupex # 备份innodb存储引擎的数据
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup # 也可以直接使用xtrabackup进行备份
- 开始完全备份
innobackupex --user=root --password=123456 /backup
- 其中/backup为新建的备份文件保存的目录
- 在另一台主机上开始导入
- 在/etc/my.cnf中加入innodb-file-per-table = ON
- 由于备份文件需要进行重新整合，因此，在备份主机上也要安装percona-xtrabackup
- 1：下载xtrabackup的RPM包
- 2：yum install -y percona-xtrabackup
- 将备份文件拷贝到备份服务器上
- scp -pr /backup/2017-08-30_21-55-01 192.168.23.32:/root/
- 在备份主机上将备份文件重新整理, 将已经提交的事务进行执行，未提交的事务进行回滚，并且
systemctl stop mariadb.serviceinnobackupex --apply-log /root/2017-08-30_21-55-01首先整理事务innobackupex --copy-back /root/2017-08-30_21-55-01chown -R mysql.mysql /data/- 如果无法启动，需要将/data/mysql/lb_logfile* 删除，因为这个文件变大了
- 开始增量备份
- 在主服务器上先创建新表
innobackupex --user=root --password=123456 --incremental /backup/ --incremental-basedir=/backup/2017-08-30_21-55-01innobackupex --apply-log --redo-only /backup/2017-08-24_23-16-04/ ：整理完全备份innobackupex --apply-log --redo-only /backup/2017-08-24_23-16-04/ --incremental-dir=/backup/2017-08-24_23-16-04/ ：将增量备份合并到完全备份上innobackupex --copy-back /root/2017-08-24_23-16-04- 同样也要修改属组和属主
完全备份过程演示
1：在配置文件里面，设置主服务器和备份服务器的数据目录为/data/mysql, 二进制目录为/data/binlog
datadir=/data/mysql
log_bin=/data/binlog/mysql-bin
innodb_file_per_table=on
2：在主服务器里面创建数据库，创建表，插入数据，使用innobackupex进行完全备份
innobackupex --user=root --password=123456 /backup
3：将完全备份拷贝到备份服务器
scp -pr /backup/2017-08-30_21-55-01 192.168.23.32:/root/
4：在备份服务器停止Mariadb，先整理完全备份的事务
innobackupex --apply-log /root/2017-08-30_21-55-01
5：再导入数据，此时会在备份服务器上创建数据目录为/data/mysql, 二进制目录为/data/binlog
innobackupex --copy-back /root/2017-08-30_21-55-01
6：最后将备份服务器的数据目录的属主和属组修改为mysql
chown -R mysql.mysql /data/
7：在备份服务器启动Mariadb，检查是否备份完毕
systemctl start mariadb.service
增量备份过程演示
1：完全备份之后，我们再来增量备份，现在主服务器上创建其他的数据库，和表，已经插入数据
2：基于刚刚创建的完全备份，再创建增量备份
innobackupex --user=root --password=123456 --incremental /backup/ --incremental-basedir=/backup/2017-08-30_21-55-01
3：再整理完全备份
innobackupex --apply-log --redo-only /backup/2017-08-30_21-55-01
4：将增量备份合并到完全备份
innobackupex --apply-log --redo-only /backup/2017-08-30_21-55-01 --incremental-dir=/backup/2017-08-30_23-16-04
5：将完全备份拷贝到备份服务器，这会覆盖之前备份服务器上的完全备份
scp -pr /backup/2017-08-30_21-55-01 192.168.23.32:/root/
6：将备份服务器停止，在备份服务器上还原数据
innobackupex --copy-back /root/2017-08-30_21-55-01
7：修改数据目录的属主和属组为mysql
chown -R mysql.mysql /data/
1：备份脚本
2：指定备份策略
温馨提示：
（1）拿到当前的使用的二进制文件的文件名
mysqladmin -uroot -h192.168.23.11 -p123456 extended-status | grep Binlog_snapshot_file | awk '{print $4}'
（2）-e 选项使得mysql可以在shell下执行SQL语句，并将结果打印到标准输出
mysql -p123456 -e 'show master status;' | grep mysql-bin | awk '{print $1}'
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
