mysql日志和备份

 

一．日志

mysql日志是mysql的重要组成部分，用来记录再使用mysql的过程中的各种信息，其中包括事务日志，错误日志，通用日志，满查询日志和二进制日志等

 

1.事务日志

mysql事务在写入硬盘之前会先写入内存，再以顺序io写入事务日志中，最后再将事务日志写入硬盘（随机io），以此来防止数据的不一致性，同时提高存储的速度。事务日志一般为两组，且同数据文件分开存储。使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把改修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。

 

MariaDB [(none)]> show global variables like 'innodb_%log%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    9
Current database: *** NONE ***

+-------------------------------------------+---------+
| Variable_name                             | Value   |
+-------------------------------------------+---------+
| innodb_flush_log_at_trx_commit            | 1       |
| innodb_locks_unsafe_for_binlog            | OFF     |
| innodb_log_block_size                     | 512     |
| innodb_log_buffer_size                    | 8388608 |
| innodb_log_file_size                      | 5242880 |//一个事务日志组的大小，默认为5M
| innodb_log_files_in_group                 | 2       |//事务日志组数
| innodb_log_group_home_dir                 | ./      |//事务日志的存放位置（./在此处表示数据文件的存放位置，一般不建议将事务日志同数据文件一起存放）
| innodb_mirrored_log_groups                | 1       |//1表示对事务日志组做镜像
+-------------------------------------------+---------+

 

 

2.错误日志

记录数据库服务的运行信息，一般用于数据库服务排错，默认开启

MariaDB [(none)]> show global variables like '%log%_error';

+---------------+------------------------------+

| Variable_name | Value                        |

+---------------+------------------------------+

| log_error     | /var/log/mariadb/mariadb.log |//错误日志的存放位置

+---------------+------------------------------+

 

 

3.通用日志

通用日志会记录数据库的所有操作，其中包括错误的mysql语句，会大量增加数据库负担一般不开启，默认关闭

MariaDB [(none)]> show global variables like 'general_log%';

+------------------+-------------+

| Variable_name    | Value       |

+------------------+-------------+

| general_log      | OFF         |//off为通用日志关闭，on为开启

| general_log_file | centos7.log |//通用日志的存储位置（同数据库的数据文件位置）

+------------------+-------------+

 

 

4.慢查询日志

用来记录执行查询超时或者多次索引的操作，记录这些操作的时间，常用来优化数据库，默认开启

MariaDB [(none)]> show global variables like 'slow_query_log%';

+---------------------+------------------+

| Variable_name       | Value            |

+---------------------+------------------+

| slow_query_log      | ON               |//on为慢查询开启，off为关闭

| slow_query_log_file | centos7-slow.log |//慢查询日志存放位置（同数据库的数据文件位置）

+---------------------+------------------+

 

 

5.二进制日志

二进制日志用于记录所有已提交的更改数据的操作，其记录了语句发生时间、执行时长、操作的数据等等，常用于数据库备份，一般建议二进制日志和数据文件分开存储

二进制日志有两种记录格式，记录语句和记录行，默认为记录语句，记录行占用空间大但是更加安全，默认一个二进制日志文件的大小为1G，二进制文件的前245字节均用来记录当前数据库的版本号。

 

MariaDB [(none)]> show global variables like '%log_bin%';

+---------------------------------+-------+

| Variable_name                   | Value |

+---------------------------------+-------+

| log_bin                         | OFF   |//二进制日志文件存储位置，off为关闭

| log_bin_trust_function_creators | OFF   |
| sql_log_bin                     | ON    |//on为二进制日志开启，off为关闭

+---------------------------------+-------+

 

 开启二进制日志

二进制日志是默认开启的，但是其存储位置默认没有，所以相当于不开启这个功能，但我们可以自定义其存储位置

vim /etc/my.cnf

log_bin=目录/文件名

然后给予这个目录即对应文件mysql的读写权限即可

 二进制日志相关操作

flush log ；  开启新的日志

show binary logs；  查询所有二进制日志列表

show master logs；  查询所有二进制日志列表

show master status；  查询当前二进制日志状态

show binlog events in‘二进制日志文件’； 查看某一二进制文件

show binlog events in ‘二进制日志文件’from n  ；查看某一二进制日志文件n位置后的部分

purge binary logs to ‘二进制日志文件’； 清空某二进制日志文件之前的二进制日志文件

reset master ；重置（清空） 二进制日志文件

mysqlbinlog --start-position=n --stop-position=m 二进制日志文件；只查看n到m的二进制日志文件

show binlog events in ‘二进制日志文件’from n limit a，b ；查看部分二进制日志文件

 

二．备份和恢复

备份可以从不同的角度可以分类为逻辑备份，物理备份和冷备份，温备份，热备份,在mysql中我们主要使用mysqldump和xtrabackup这两个工具

逻辑备份和物理备份

逻辑备份中是对数据库的操作，恢复时只需执行这些语句即可

物理备份则是直接记录数据库的数据，占用空间较多，但速度较快

 

冷备份，温备份和热备份

冷备份在备份时不可读写

温备份在备份时可读但不可写

热备份在备份时可读可写

 

1.mysqldump

mysql有其专门的备份工具mysqldump，属于逻辑备份，可以通过事务功能轻易的实现热备份。虽然在备份时我们需要让数据库暂时保持静止，即数据不被修改，但是mysql拥有事务功能可以让备份时产生幻读。

mysqldump

-A 备份所有数据库

-B  db_name 备份指定数据库

-R 备份所有存储过程和自定义函数

--trigger 备份触发器（默认开启）

--skip-trigger 不备份触发器

--master-data[=n] 此选项须启用二进制日志 n=1：所备份的数据之前加一条记录,为CHANGE MASTER TO语句用于获取二进制日志文件的位置和名称，默认为1;n=2：记录为注释的CHANGE MASTER TO语句,此选项会自动关闭--lock-tables功能（除非开启--single-transaction）

-F 锁定表并且前滚动日志

--single-transaction 利用事务功能让备份时产生幻读，即在一个事务中读到的数据为暂时静止的状态（在开始的时候把该session的事务隔离级别设置成repeatable read；然后启动一个事务，备份结束的时候结束该事务有了这两个操作，在备份过程中，该session读到的数据都是启动备份时的数据。可以理解为对于innodb引擎来说加了该参数，备份开始时就已经把要备份的数据定下来了，备份过程中的提交的事务时是看不到的，也不会备份进去。）

 

备份

一般我们用mysqldump -A -F -R --single-transaction --master-data >bank.sql 即可完整热备份数据库到bank.sql文件中，bank.sql是我们导入的文件名，可以自定义

恢复

直接将我们的备份文件导入mysql即可

 

2.xtrabackup

xtrabackup是mysql的一个备份工具，属于物理备份，它读取innodb数据来进行数据备份的，支持热备，增量备份和差量备份。xtrabackup 是用来备份 innodb 表的，不能备份非 innodb 表，和 mysql Server 没有交互。

备份

xtrabackup的备份分为完全备份和增量备份，完全备份是拷贝所有文件，而增量备份则是基于之前的备份只备份修改的数据

完全备份

innobackupex /目录

命令加目录即可将数据库完全备份到我们指定的目录下，文件名为当前日期

增量备份

innobackupex --incremental 路径 --incremental-basedir=路径

--incremental指定这是增量备份

第一个目录是我们的存储目录

第二个目录为我们基于这个目录所做的增量目录

还原

完全备份还原

1.将备份拷贝至要进行还原操作的主机上

2.同步日志 innobackupex --apply-log 备份的文件

  --apply-log 在数据库备份好后，这些备份的数据并不能立即用于恢复，因为这些刚备份的数据里包含了未提交的数据，需要回滚，也包括的已完成的事务在重做日志文件中并没有写入数据文件中，这些数据需要重做，这个参数正是用于做这些事情，以保证数据      文件的一致性，在数据库恢复之前，须先对备份的数据文件应用此参数（innobackup会重现重做日志文件中的事务条目，重做已经提交的事务和回滚未提交的事务）

3.保证数据目录为空，mysql服务停止

4.恢复数据 innobackupex --copy-back 备份文件

5.将恢复的文件修改所属人，所属组并修改其权限，使mysql对其可读写，重启mysql

增量备份还原

1.将所有备份拷贝到要进行还原操作的主机上

2.同步日志，因为是增量，所以我们每一次增量都需要进行一次同步,将增量合并到全量备份中

   对完全备份和最后一个增量备份之前的增量备份进行如下操作

   innobackupex --apply-log --redo-only 备份文件

   --redo-only  在做增量恢复时，全备和增量备份的数据文件在恢复前必须先将在重做日志文件中的已提交的事务重做，此参数将会合并全备和增量备份的数据文件，让xtrabackup跳过 rollback 阶段，只进行redo 阶段，如果增量备份中含有rollback可能导致

  增量备份无法衔接

  对最后一个备份进行如下操作

  innobackupex --apply-log 备份文件

3.保证数据目录为空，mysql服务停止

4.恢复数据 innobackupex --copy-back 整合后的全量备份文件

5.将恢复的文件修改所属人，所属组并修改其权限，使mysql对其可读写

 

XtraBackup优点 ：

1、无需停止数据库进行InnoDB热备
2、增量备份MySQL
3、流压缩到传输到其它服务器
4、能比较容易地创建主从同步
5、备份MySQL时不会增大服务器负载

转载于:https://www.cnblogs.com/bailandecheng/p/9783045.html