mysql 日志和备份

2019独角兽企业重金招聘Python工程师标准>>>

1    mysql日志

    1.1 错误日志

        mysql数据库启动记录也会在错误日志中
        mysql服务器运行中产生的错误信息
        时间调度器运行一个时间产生的信息
        log_error| /mydata/data/openvpn.err

    1.2 一般查询日志

        general_log     | OFF
        general_log_file| /mydata/data/openvpn.log
        log_output| [FILE|TABLES|NONE]

        一般文件类的修改必须写在配置文件中，并重启mysql数据库服务器

    1.3 二进制日志

        binlog_max_flush_queue_time | 0
        不刷新二进制日志
        log_bin                     | OFF
       
       vim /etc/my.cnf
        [mysqld]
        log_bin = mysql-bin
        开启二进制日志

[root@myvm program]# vim /etc/my.cnf 
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log_bin=mysql-bin ## 添加这一行可以开机二进制日志文件
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

开启日志二进制日志之后服务器要重启，重启之后：

mysql> show variables like  '%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | ON    |
| log_bin_trust_function_creators | OFF   |
| log_bin_trust_routine_creators  | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+
4 rows in set (0.00 sec)

## 上面的log_bin 的值是ON 表示已经开启

        如果要滚动二进制文件：1.重启mysql
                                                2.mysql>flush logs

    1.4 慢日志

        slow_query_log      | OFF
        slow_query_log_file | /mydata/data/openvpn-slow.log

    1.5    查看mysql中和日志相关选项

        mysql>show master status

mysql> flush logs;
Query OK, 0 rows affected (0.04 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      106 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

## 二进制日志滚动之后日志文件重新命名为mysql-bin.000002 

    mysql>show binary logs

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       149 |
| mysql-bin.000002 |       106 |
+------------------+-----------+
2 rows in set (0.00 sec)

    mysql>show binlog events in '二进制日志文件'
    mysql>purge binary logs to '二进制日志文件'
    mysql>flush logs 滚动二进制日志文件 
    mysqlbinlog用于查看二进制日志信息

   --start-datetime
        --stpo-datetime

        --start-position
        --stop-position
    mysqlbinlog mysql-bin.000001 > 1.sql 把二进制日志文件转为sql

[root@myvm mysql]# mysqlbinlog mysql-bin.000002 > 1.sql

二进制文件中刚刚建库，建表语句都保存了，可以转换成sql，如下：

mysql> show binlog events in 'mysql-bin.000002';
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                                                  |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------------------------------+
| mysql-bin.000002 |   4 | Format_desc |         1 |         106 | Server ver: 5.1.73-log, Binlog ver: 4                                 |
| mysql-bin.000002 | 106 | Query       |         1 |         191 | create database base1                                                 |
| mysql-bin.000002 | 191 | Query       |         1 |         311 | use `base1`; create table tmp_wzl_20161110_1(id int,name varchar(30)) |
| mysql-bin.000002 | 311 | Query       |         1 |         429 | use `base1`; insert into tmp_wzl_20161110_1(id,name)values(1,'wzl')   |
| mysql-bin.000002 | 429 | Rotate      |         1 |         472 | mysql-bin.000003;pos=4                                                |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------------------------------+
5 rows in set (0.00 sec)

mysql> show binlog events in 'mysql-bin.000002' from 191;
+------------------+-----+------------+-----------+-------------+-----------------------------------------------------------------------+
| Log_name         | Pos | Event_type | Server_id | End_log_pos | Info                                                                  |
+------------------+-----+------------+-----------+-------------+-----------------------------------------------------------------------+
| mysql-bin.000002 | 191 | Query      |         1 |         311 | use `base1`; create table tmp_wzl_20161110_1(id int,name varchar(30)) |
| mysql-bin.000002 | 311 | Query      |         1 |         429 | use `base1`; insert into tmp_wzl_20161110_1(id,name)values(1,'wzl')   |
| mysql-bin.000002 | 429 | Rotate     |         1 |         472 | mysql-bin.000003;pos=4                                                |
+------------------+-----+------------+-----------+-------------+-----------------------------------------------------------------------+
3 rows in set (0.00 sec)

    1.6 中继日志：
        从服务器上，从主服务器的二进制日志文件中复制而来的事件

    1.7 事务日志：
        仅仅满足事务acid要求
        不能单独用来直接进行还原

        innodb_flush_log_at_trx_commit| [0,1,2]
        0:每秒同步，并执行磁盘flush
        1:每事务同步，并执行磁盘flush
        2:每事务同步，但不执行磁盘flush，而是由操作系统绝对何时进行磁盘flush

mysql> show variables like '%innodb_flush%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
| innodb_flush_method            |       |
+--------------------------------+-------+
2 rows in set (0.01 sec)

innodb_log_buffer_size 缓存大小
        innodb_log_file_size 日志大小
        innodb_log_files_in_group 事务日志中每组里面有多少个文件
        innodb_mirrored_log_groups有多少个事务日志的镜像

        expire_logs_days 设定二进制日志文件过期天数
        general_log 是否启用一般查询日志
        general_log_file 一般查询日志存储路径

        binlog_format 设定二进制日志的类型
        stateamment|row|mixed

        log-bin是否启用二进制日志文件（名称）
        sql_log_bin是否启用二进制日志文件

        
        slow_query_log是否开启慢日志查询
        slow_query_log_file慢日志文件存储路径

        log_warnings把告警信息也放在错误日志当中
        long_query_time=# 设定慢日志超时时间默认10s
        sync_binlog 同步二进制日志文件

myisam：
    不支持事务
    表锁
    支持表压缩
    表格式：frm，myd，myi
    对于读比较多的数据库可以选择myisam存储引擎
innodb：
    支持事务
    行锁
    表空间
    表格式：frm,idb
    对于读写操作都相当数据库可以选择innodb存储引擎
CSV:
    把数据存为一定格式的文本文件

ARCHIVE：
    归档存储引擎
        实际使用较少，主要用于数据挖掘
MEMORY：
    内存型数据存储引擎

BLACKHOLE：
    黑洞存储引擎

mysql备份和还原：
    备份：
        raid0,raid1:这个方式只能保证硬件故障之后不会中断业务
        drop table ....逻辑上的删除他不能恢复

    50G
        copy 

    服务器是否在线：
        热备：在线备份，读写操作都可执行
        温备：能读不能写
        冷备：离线备份，读写都不可操作

    物理备份：拷贝数据库实际在系统中产生数据文件，速度快
    逻辑备份：将数据导出至文本文件中，速度慢，移植性好

    完全备份：备份所有数据
    增量备份：仅仅备份上次完全备份或增量备份以后变化的数据
    差异备份：仅仅备份上次完全备份以来变化的数据

备份什么：
    数据，配置文件，备份二进制日志，事务日志

还原：
    备份最好拿来还原测试以下，保证备份可用，并有还原方案

热备：
    myisam表进行热备几乎不可能，接近热备，使用快照，不然只能使用温备
    innodb表可以使用xtrabackup，mysqldump
    mysql---->从

备份策略：
    完全+增量 完全+差异

    还原时长需要考虑

mysql备份工具：
    mysqldump 逻辑备份工具
    mysqlhotcopy物理备份工具，会锁表，温备

文件系统工具：
    cp
    快照

innodb：
    ibbackup
    xtrabackup

mysqldump：
    进行完全备份+二进制日志
    完全+增量

 

2    备份

    2.1    整库备份

            备份前锁表：

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

            全库备份：

[root@myvm mysql]# mysqldump -uroot -p123456 base1 > all_base1.sql

如上图，这中全库备份的方式sql中不会指定数据库的名称，只会记录表的创建语句。所以如果要回复的时候要自己手动先创建好数据库在进行恢复的。一般情况下不建议采用。看下面的全库备份会连数据库一起备份：

[root@myvm mysql]# mysqldump -uroot -p123456 --database base1 > all_base2.sql
 

备份完成之后记得解锁：mysql>unlock tables

模拟场景数据库base1 被删除，进行恢复：

## 删除数据库base1
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| base1              |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> drop database base1;
Query OK, 1 row affected (0.03 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

进行恢复：

[root@myvm mysql]# mysql -uroot -p123456 < all_base2.sql 

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| base1              |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> use base1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+--------------------+
| Tables_in_base1    |
+--------------------+
| tmp_wzl_20161110_1 |
+--------------------+
1 row in set (0.00 sec)
 

如上所述，数据库base1 被删除了，可以通过备份文件进行恢复。

--lock-all-tables
所定所有表
--lock-tables
锁表
--flush-logs
二进制日志文件滚动
--single-transaction如果你确定你所有的表都所innodb存储引擎的就可以使用此选项完成innodb的热备

--all-databases备份所有数据库
--databases备份单库
备份的时候同时备份创建数据库sql

--routines
备份存储过程和函数的

--triggers
备份触发器的

myisam：
mysqldump -uroot -p456789 --lock-all-tables --flush-logs --all-databases --master-data=2 >file.sql
innodb热备:
mysqldump -uroot -p456789 --single-transaction --master-data=2 --all-databases > file.sql

3    恢复数据库

    数据库损毁最极端的比如说服务器损坏，磁盘损毁。如果有数据库全库备份文件和二进制备份文件，也是可以恢复的。

    例如，服务器设置的每一个星期日00:00:00全库备份一次，每星期日00:00:05 分二进制日志滚动一次，结果在周三mysql服务器磁盘坏了。

    3.1 找出最近一周日00:00:00的全库备份文件（前提是备份文件要保存在其他地方），先恢复周日之前的数据，

mysql> show tables;
+--------------------+
| Tables_in_base1    |
+--------------------+
| tmp_wzl_20161110_1 |
| tmp_wzl_20161110_2 |
+--------------------+
2 rows in set (0.00 sec)

tmp_wzl_20161110_1  是上周日之前建的，在周日的全库备份中已经备份，而tmp_wzl_20161110_2 是周二新建的。

先恢复之前库备份的：

[root@myvm mysql]# mysql -uroot -p123456 < all_base2.sql 
 

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| base1              |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

上面数据库base1 已经恢复

Database changed
mysql> show tables;
+--------------------+
| Tables_in_base1    |
+--------------------+
| tmp_wzl_20161110_1 |
+--------------------+
1 row in set (0.00 sec)

但是全库备份之后的表tmp_wzl_20161110_2表没有恢复，我们接着恢复最后的二进制尾巴

mysql> show tables;
+--------------------+
| Tables_in_base1    |
+--------------------+
| tmp_wzl_20161110_1 |
| tmp_wzl_20161110_2 |
+--------------------+
2 rows in set (0.00 sec)

mysql> select * from tmp_wzl_20161110_1;
+------+------+
| id   | name |
+------+------+
|    1 | wzl  |
+------+------+
1 row in set (0.00 sec)

mysql> select * from tmp_wzl_20161110_2;
+------+------+
| id   | name |
+------+------+
|    2 | up19 |
+------+------+
1 row in set (0.00 sec)

最后的尾巴已经恢复成功。

4    myisam 数据库引擎 下的备份
mysqldump -uroot -p456789 --lock-all-tables --flush-logs --all-databases --master-data=2 >file.sql

5    innodb 数据库引擎下的热备份
innodb热备:
mysqldump -uroot -p456789 --single-transaction --master-data=2 --all-databases > file.sql

6    还原

还原：
    mysql>source /path/file.sql
    mysql -uroot -p < /path/file.sql
    password:
    以上备份文件是包含了创建数据库的sql语句

    mysql>create database db_name
    mysql>use db_name;
    mysql>source /path/file.sql
    
    mysql -uroot -p -D db_name < /path/file.sql
    以上备份文件是没有包含创建数据库的sql语句

7 快速备份和还原

快速备份单表：
    mysql>select * into outfile '/path/file' from tb_name

快速还原单表:
    mysql>load data infile '/path/file' into table tb_name

8 LVM 快照

lvm:使用快照的方式进行备份主要是用于myisam的几乎热备
    前提：
        数据文件在逻辑卷上
        此逻辑卷必须要有足够的空间来存储快照
        数据文件和事务日志必须在同一个逻辑卷上
    步凑：
        1.打开会话，添加所有表的锁操作，读锁
          mysql>flush tables with read lock;
          mysql>flush logs
        2.通过另一给终端，保存二进制日志文件及其位置信息
          mysql -uroot -p123456 -e "show master status\G" >> /path/file
        3.创建快照卷
          lvcreate -L # -s -p -n LV_NAME /path/to/source_lv
        4.释放锁
          mysql>unlock tables

        5.挂载快照卷，备份
          mount 
          cp

        6.删除快照卷

        7.增量备份二进制日志

 

转载于:https://my.oschina.net/wangzilong/blog/785872