• ADADADADAD

    Mysql数据库二进制日志的管理[ mysql数据库 ]

    mysql数据库 时间:2024-12-03 12:14:40

    作者:文/会员上传

    简介:

    如果配置了log_bin参数,则需要配置expire_logs_days参数指定二进制日志保留时间,不然磁盘空间早晚会被日志占满的。如果之前没有配置,则配置步骤如下: 1、查看当前配置
    (root@lo

    以下为本文的正文内容,内容仅供参考!本站为公益性网站,复制本文以及下载DOC文档全部免费。

    如果配置了log_bin参数,则需要配置expire_logs_days参数指定二进制日志保留时间,不然磁盘空间早晚会被日志占满的。如果之前没有配置,则配置步骤如下:

    1、查看当前配置
    (root@localhost)[(none)] show global variables like 'log_bin';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | log_bin | ON |
    +---------------+-------+
    1 row in set (0.02 sec)

    (root@localhost)[(none)] show global variables like 'expire_logs_days';
    +------------------+-------+
    | Variable_name | Value |
    +------------------+-------+
    | expire_logs_days | 0 |
    +------------------+-------+
    1 row in set (0.00 sec)

    (root@localhost)[(none)] show global variables like 'max_binlog_size';
    +-----------------+------------+
    | Variable_name | Value |
    +-----------------+------------+
    | max_binlog_size | 1073741824 |
    +-----------------+------------+
    1 row in set (0.00 sec)

    (root@localhost)[(none)] show binary logs;
    +------------------+-----------+
    | Log_name | File_size |
    +------------------+-----------+
    | mysql-bin.000001 | 614 |
    | mysql-bin.000002 | 1595 |
    | mysql-bin.000003 | 445747850 |
    +------------------+-----------+
    3 rows in set (0.02 sec)

    [root@dbrac16 ~]# ll /usr/local/mysql/data/mysql-bin*
    -rw-r----- 1 mysql mysql 614 May 11 10:06 /usr/local/mysql/data/mysql-bin.000001
    -rw-r----- 1 mysql mysql 1595 May 17 09:45 /usr/local/mysql/data/mysql-bin.000002
    -rw-r----- 1 mysql mysql 445747850 May 19 14:28 /usr/local/mysql/data/mysql-bin.000003
    -rw-r----- 1 mysql mysql 117 May 17 11:16 /usr/local/mysql/data/mysql-bin.index


    2、查看slave状态
    [root@dbrac17 ~]# mysql -uroot -p123456 -e "show slave status\G" | grep Master_Log_File
    mysql: [Warning] Using a password on the command line interface can be insecure.
    Master_Log_File: mysql-bin.000003
    Relay_Master_Log_File: mysql-bin.000003


    3、手动清除过期的日志
    如果堆积的日志太多,可以先执行这一步,清理掉一部分日志。
    (root@localhost)[(none)] purge master logs before '2017-05-17 00:00:00';
    Query OK, 0 rows affected (0.15 sec)

    (root@localhost)[(none)] show binary logs;
    +------------------+-----------+
    | Log_name | File_size |
    +------------------+-----------+
    | mysql-bin.000002 | 1595 |
    | mysql-bin.000003 | 445747850 |
    +------------------+-----------+
    2 rows in set (0.00 sec)


    4、配置参数
    [root@dbrac16 ~]# cat /etc/my.cnf | grep expire_logs_days
    expire_logs_days = 3

    修改参数文件,设置日志保留3天,需要重启才能生效。
    直接设置,立即生效。
    (root@localhost)[(none)] set global expire_logs_days=3;
    Query OK, 0 rows affected (0.00 sec)

    触发过期日志清除操作。
    (root@localhost)[(none)] flush logs;
    Query OK, 0 rows affected (0.04 sec)

    (root@localhost)[(none)] show binary logs;
    +------------------+-----------+
    | Log_name | File_size |
    +------------------+-----------+
    | mysql-bin.000003 | 445747897 |
    | mysql-bin.000004 | 201 |
    +------------------+-----------+
    3 rows in set (0.00 sec)

    Mysql数据库二进制日志的管理.docx

    将本文的Word文档下载到电脑

    推荐度:

    下载
    热门标签: mysql数据库管理