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