MySQL 日志类型/备份与恢复/工具命令

一、日志类型

　　简介：默认情况下，MySQL只会启动错误日志文件，其他日志文件需要手动启动才可以被启动。使用日志有优点也有缺点，启动日志后，虽然可以实现对MySQL服务器进行维护，但是会降低MySQL软件的执行速度。

　　1. 二进制日志(log_bin)

　　　　A. 作用：该日志文件会以二进制形式记录数据库的非查询外各种操作语句，用于增量备份；

　　　　B. my.cnf文件配置

[mysqld]
# 默认名字：默认目录/主机名-bin-munber
log_bin=dir/filename

　　　　C. 查看二进制日志文件命令：mysqlbinlog；

　　可参考：MySQL Binlog介绍

　　2. 中继日志(relay_log)

　　　　A. 作用：传递日志，主要用在主从复制的架构中，只在从库中有中继日志（多级复制除外）在从库中将主库复制过来的二进制日志保存为中继日志，用于从库重构数据；

　　　　B. my.cnf文件配置

[mysqld]
relay_log=dir/filename

　　3. 慢查询日志(slow_query_log)

　　　　A. 作用：记录执行时间超过指定时间的查询语句，通过工具分析慢查询日志可以定位MySQL服务器性能瓶颈所在，用于优化SQL语句；

　　　　B. my.cnf文件配置

[mysqld]
# 默认名字：默认目录/主机名-slow.log
log-slow-queries=dir/filename
log_query_time=n

　　　　C. 命令

　　　　　　慢查询是否开启：show variables like 'slow_query_log'，默认关闭状态，若要开启慢查询，可以临时设置：set global slow_query_log='ON'；

　　　　　　慢查询日志存放位置：show variables like 'slow_query_log_file'，默认位置/var/lib/mysql/mysql-slow.log；

　　　　　　查询超过多久就记录(单位微秒)：show variables like 'long_query_time'，默认10秒；

　　4. 通用查询日志(general_log)

　　　　A. 作用：该日志记录MySQL服务器的启动和关闭信息、客户端的连接信息、更新数据库记录SQL语句和查询数据库记录SQL语句；

　　　　B. my.cnf文件配置

[mysqld]
# 默认名字：默认目录/主机名.log
general_log=dir/filename

　　5. 错误日志(log_error)

　　　　A. 作用：该日志文件会记录MySQL服务器启动、关闭和运行时出错等信息；

　　　　B. my.cnf文件配置

[mysqld]
# 默认名字：默认目录/主机名.error
log_error=dir/filename

　　6. 事务日志(innodb_log)

　　　　A. 作用：缓存事务提交的数据，实现将随机IO转换成顺序IO；

　　　　B. my.cnf文件配置

[mysqld]
innodb_log_file_size=128M

 

二、备份/恢复

　　1. 分类

　　　　从物理和逻辑角度分

　　　　　　A. 物理备份(Xtrabackup)：指对数据库操作系统的物理文件（如数据文件、日志文件等）的备份。物理备份又可以分为脱机备份(冷备份)和联机各份(热备份)；

　　　　　　　　冷备份：在关闭数据库时进行的备份操作，能够较好地保证数据库的完整性；

　　　　　　　　热备份：在数据库运行状态中进行操作，这种备份方法依赖于数据库的日志文件。

　　　　　　B. 逻辑备份(mysqldump)：指对数据库逻辑组件(如表等数据库对象)的备份；

　　　　从备份策略角度分

　　　　　　A. 完全备份：每次对数据进行完整的备份。可以备份整个数据库，包含用户表、系统表、索引、视图和存储过程等所有数据库对象。但它需要花费更多的时间和空间，所以，做一次完全备份的周期要长些；

　　　　　　B. 差异备份：备份那些自从上次完全备份之后被修改过的文件，只备份数据库部分的内容。它比最初的完全备份小，因为只包含自上次完全备份以来所改变的数据库。它的优点是存储和恢复速度快；

　　　　　　C. 增量备份：只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。

　　2. 全量备份

　　　　A. 备份脚本：

#!/bin/bash
# mysqldump的路径
DUMP=/usr/bin/mysqldump
# 数据库登录名
DB_USER=root
# 数据库登录密码
DB_PASSWORD=123456
# 备份文件目录
BAK_DIR=/mysql
# 当前时间
DATE=`date +%Y%m%d%H%M`

# 目录不存在就创建
if [ ! -d "$BAK_DIR" ]; then
    mkdir -p $BAK_DIR
fi

$DUMP -u$DB_USER -p$DB_PASSWORD -x --databases user | gzip >$BAK_DIR/${DATE}.sql.gz

# 只保留一周的备份数据
find $BAK_DIR -type d -mtime +7 -name "*.sql.gz" -exec rm -rf {} \;

echo "${DATE}数据库全量备份成功！"

　　　　B. 全量备份执行时日志发出警告：Using a password on the command line interface can be insecure

 　　　　　解决措施：vim etc/my.cnf，增加如下配置

[mysqldump]
# 用户名
user=root
# 密码
password=password

　　4. 增量备份

　　　　A. 先要确保打开了二进制日志文件：show variables like 'log_bin';

　　　　　其次确保/etc/my.cnf中配置了一下参数，若重新配置后需要重启mysql使其生效；

server-id=1
log-bin=/usr/local/mysql/binlog/mysql-bin

　　　　B. 配置/etc/my.cnf后，重启mysql报错，查看mysql错误日志发现是创建log-bin文件目录的权限问题

　　　　　解决措施：修改二进制文件目录的用户和用户组权限 —— chown -R mysql:mysql /usr/local/mysql/binlog。

　　　　C. 增量脚本

#!/bin/bash
# MySQL工具命令的路径
TOOL_DIR=/usr/bin
# 数据库登录名
DB_USER=root
# 数据库登录密码
DB_PASSWORD=123456
# 获取当天最新的二进制文件
BIN_DIR=/var/lib/mysql/binlog
# 最新日志文件
LOG=`tail -1 ${BIN_DIR}/mysql-bin.index`
# 备份文件目录
BAK_DIR=/mysql
# 当天时间
DATE=`date +%Y%m%d`

# 文件不存在就创建
if [ -f "${BIN_DIR}/mysql-bin.index" ]; then
    cat /dev/null > ${BIN_DIR}/mysql-bin.index
else
    touch ${BIN_DIR}/mysql-bin.index
fi

# 更新二进制文件
${TOOL_DIR}/mysqladmin -u$DB_USER -p$DB_PASSWORD flush-logs
# 备份新生成的日志
${TOOL_DIR}/mysqlbinlog $LOG --database=user >> $BAK_DIR/${DATE}.incr.sql

# 只保留三天的备份数据
find $BIN_DIR -type d -mtime +3 -name "mysql-bin.*" -exec rm -rf {} \;
find $BAK_DIR -type d -mtime +3 -name "*.incr.sql" -exec rm -rf {} \;

echo "数据库${LOG}增量备份成功！"

　　5. crontab定时任务，具体添加、查看及常见问题可前往Linux crontab命令。

　　6. 恢复

　　　　A. 

　　　　B. 增量恢复：/usr/bin/mysqlbinlog mysql-bin.000001 --database=test --skip-gtids=true | usr/bin/mysql -uroot -p123456 test；

 

三、工具命令

　　1. mysql：客户端工具，用于连接服务端，如：mysql -h127.0.0.1 -P3306 -uroot -p123456 -e "show databases;"；

　　　　-e：执行选项，建立连接后执行SQL语句返回结果后退出客户端,可以执行多条SQL语句，常用于Shell批处理脚本 mysql -uroot -p123456 -e "USE rhxy;ALTER TABLE ip_geo DISCARD TABLESPACE"；

　　　　-N：不显示列信息；

　　　　-s：一行一行输出，中间有TAB分割。

　　2. mysqldump：用来全量备份数据库，可以控制表结构或数据的输出；

　　　　A. -u, --user=name ：指定用户名；

　　　　B. -p, --password[=name] ：指定密码；

　　　　C. --database=name ：指定数据库；

　　　　D. --lock-all-tables，-x：在开始导出之前，提交请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭 --single-transaction 和 --lock-tables 选项；

　　　　例如：备份指定数据库——mysqldump  -h127.0.0.1 -P3306 -uroot -p123456 testdb  > /root/testdb.sql；

　　　　　　　只备份表结构，添加-d参数——mysqldump  -h127.0.0.1 -P3306 -uroot -p123456 -d testdb rhxytable > /root/rhxytable.sql；

　　　　　　　备份表结构和数据——mysqldump  -h127.0.0.1 -P3306 -uroot -p123456 testdb rhxytable > /root/rhxytable.sql；

　　　　　　　只备份表数据，添加-t参数——mysqldump -h127.0.0.1 -P3306 -uroot -p123456 -t testdb rhxytable > /root/rhxytable.sql；　　

　　3. mysqladmin：执行管理操作客户端，可以刷新日志和创建数据库等；

　　4. mysqlbinlog：服务器工具，用来查看binlog二进制日志文件及实现增量数据恢复；

　　　　A. --start-datatime=date1 --stop-datetime=date2 ：指定日期间隔内的所有日志；

　　　　B. --base64-output=decode-rows：解码二进制内容，配合-v, --verbose选项一起解码行事件到带注释的伪SQL语句；

　　　　C. --start-position=pos1 --stop-position=pos2：指定位置间隔内的所有日志；

　　5. mysqlimport：客户端导入数据工具，主要用于导入使用mysqldump工具加上-T参数后导出的表数据文本文件(.txt)；

　　6. source：mysql客户端的命令，主要是用于在mysql客户端命令行上批量执行sql语句使用的，如初始化项目数据库 source /data/mysql/vpn.sql；

　　7. mysqlshow：是客户端工具，主要用于显示数据库中的各种对象的信息。

　　可参考：MySQL之常用的命令行工具

 

可参考：https://blog.csdn.net/xiaoweite1/article/details/80299754