mysql数据 日志 配置 备份_MySQL 日志类型/备份与恢复/工具命令

一、日志类型

简介：默认情况下，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：指定位置间隔内的所有日志；