一、日志类型
简介:默认情况下,MySQL只会启动错误日志文件,其他日志文件需要手动启动才可以被启动。使用日志有优点也有缺点,启动日志后,虽然可以实现对MySQL服务器进行维护,但是会降低MySQL软件的执行速度。
1. 二进制日志(log_bin)
A. 作用:该日志文件会以二进制形式记录数据库的非查询外各种操作语句,用于增量备份;
B. my.cnf文件配置
[mysqld]
# 默认名字:默认目录/主机名-bin-munber
log_bin=dir/filename
C. 查看二进制日志文件命令:mysqlbinlog;
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%';
最大查询时间:show variables like '%query_time%';
4. 通用查询日志(general_log)
A. 作用:该日志记录MySQL服务器的启动和关闭信息、客户端的连接信息、更新数据库记录SQL语句和查询数据库记录SQL语句;
B. my.cnf文件配置
[mysqld]
# 默认名字:默认目录/主机名.loggeneral_log=dir/filename
5. 错误日志(log_error)
A. 作用:该日志文件会记录MySQL服务器启动、关闭和运行时出错等信息;
B. my.cnf文件配置
[mysqld]
# 默认名字:默认目录/主机名.errorlog_error=dir/filename
6. 事务日志(innodb_log)
A. 作用:缓存事务提交的数据,实现将随机IO转换成顺序IO;
B. my.cnf文件配置
[mysqld]
innodb_log_file_size=128M
二、备份/恢复
1. 分类
从物理和逻辑角度分
A. 物理备份:指对数据库操作系统的物理文件(如数据文件、日志文件等)的备份。物理备份又可以分为脱机备份(冷备份)和联机各份(热备份);
冷备份:在关闭数据库时进行的备份操作,能够较好地保证数据库的完整性;
热备份:在数据库运行状态中进行操作,这种备份方法依赖于数据库的日志文件。
B. 逻辑备份:指对数据库逻辑组件(如表等数据库对象)的备份;
从备份策略角度分
A. 完全备份:每次对数据进行完整的备份。可以备份整个数据库,包含用户表、系统表、索引、视图和存储过程等所有数据库对象。但它需要花费更多的时间和空间,所以,做一次完全备份的周期要长些;
B. 差异备份:备份那些自从上次完全备份之后被修改过的文件,只备份数据库部分的内容。它比最初的完全备份小,因为只包含自上次完全备份以来所改变的数据库。它的优点是存储和恢复速度快;
C. 增量备份:只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。
2. 全量备份
A. 备份脚本:
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=1log-bin=/usr/local/mysql/binlog/mysql-bin
B. 配置/etc/my.cnf后,重启mysql报错,查看mysql错误日志发现是创建log-bin文件目录的权限问题
解决措施:修改二进制文件目录的用户和用户组权限 —— chown -R mysql:mysql /usr/local/mysql/binlog。
C. 增量脚本
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;
2. mysqldump:备份数据库
A. -u, --user=name :指定用户名;
B. -p, --password[=name] :指定密码;
C. -d, --database=name :指定数据库;
D. --lock-all-tables,-x:在开始导出之前,提交请求锁定所有数据库中的所有表,以保证数据的一致性。这是一个全局读锁,并且自动关闭 --single-transaction 和 --lock-tables 选项;
3. mysqladmin:执行管理操作客户端
4. mysqlbinlog:查看binlog二进制日志文件
A. -d, --database=name :指定数据库;
B. --start-datatime=date1 --stop-datetime=date2 :指定日期间隔内的所有日志;
C. --base64-output=decode-rows:解码二进制内容,配合-v, --verbose选项一起解码行事件到带注释的伪SQL语句;
D. --start-position=pos1 --stop-position=pos2:指定位置间隔内的所有日志;