mysql日志(介绍 路径修改 备份)
mysql日志(介绍 路径修改 备份)
doudou___9958 发表于1年前
mysql日志(介绍 路径修改 备份)
  • 发表于 1年前
  • 阅读 14
  • 收藏 0
  • 点赞 0
  • 评论 0

腾讯云 技术升级10大核心产品年终让利>>>   

环境:senos6

软件:mysql2.6.20

mysql日志:
    错误日志

    一般查询日志

    慢查询日志

    二进制日志
        只记录DDL,DML等引起数据库改变的操作都会记录下来
        复制,即时点恢复

    中继日志

    事务日志
        只是满足acid要求来做的
    innodb才有事务日志

    
    错误日志:
        mysql数据库启动记录也会在错误日志中
        mysql服务器运行中产生的错误信息
        时间调度器运行一个时间产生的信息
        log_error| /mydata/data/openvpn.err
    一般查询日志:
        general_log     | OFF # 建议关闭 因数据量太大,可以查询慢日志。系统默认关闭!
        general_log_file| /mydata/data/openvpn.log
        log_output| [FILE|TABLES|NONE]
    一般文件类的修改必须写在配置文件中,并重启mysql数据库服务器
    二进制日志(建议开启。作用很大!):
        binlog_max_flush_queue_time | 0
        不刷新二进制日志
        log_bin                     | OFF
       
      开启的方法:vim /etc/my.cnf
        [mysqld]下添加一下内容:
        log_bin = mysql-bin
        开启二进制日志
    如果要滚动二进制文件:1.重启mysql
                          2.mysql>flush logs

    慢日志:
        slow_query_log      | OFF
        slow_query_log_file | /mydata/data/openvpn-slow.log

 

    查看mysql中和日志相关选项
    show global variables like '%log%'

    查看二进制日志相关信息
    mysql>show master status
    mysql>show binary logs
    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   二进制日志转换成SQL语句后可以导入mysql里面进行数据恢复,作用很大。

 

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

显示当前使用的二进制文件和文件中的位置
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       375 |
+------------------+-----------+
1 row in set (0.00 sec)


mysql> show binlog events in 'mysql-bin.000001';
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                                |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------------+
| mysql-bin.000001 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.20-log, Binlog ver: 4               |
| mysql-bin.000001 | 120 | Query       |         1 |         199 | BEGIN                                               |
| mysql-bin.000001 | 199 | Intvar      |         1 |         231 | INSERT_ID=1                                         |
| mysql-bin.000001 | 231 | Query       |         1 |         344 | use `up01`; insert into up01(name) values ('aaaaa') |
| mysql-bin.000001 | 344 | Xid         |         1 |         375 | COMMIT /* xid=24 */                                 |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------------+
5 rows in set (0.00 sec)

mysql> show binlog events in 'mysql-bin.000001' from 199;
+------------------+-----+------------+-----------+-------------+-----------------------------------------------------+
| Log_name         | Pos | Event_type | Server_id | End_log_pos | Info                                                |
+------------------+-----+------------+-----------+-------------+-----------------------------------------------------+
| mysql-bin.000001 | 199 | Intvar     |         1 |         231 | INSERT_ID=1                                         |
| mysql-bin.000001 | 231 | Query      |         1 |         344 | use `up01`; insert into up01(name) values ('aaaaa') |
| mysql-bin.000001 | 344 | Xid        |         1 |         375 | COMMIT /* xid=24 */                                 |
+------------------+-----+------------+-----------+-------------+-----------------------------------------------------+
3 rows in set (0.00 sec)

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

    事务日志:
        仅仅满足事务acid要求
        不能单独用来直接进行还原
        innodb_flush_log_at_trx_commit| [0,1,2]
        0:每秒同步,并执行磁盘flush
        1:每事务同步,并执行磁盘flush
        2:每事务同步,但不执行磁盘flush,而是由操作系统绝对何时进行磁盘flush

        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---->从


备份策略:
    完全+增量 完全+差异(差异备份:一般使用

        mysqlbinlog
        --start-datetime
        --stpo-datetime

        --start-position
        --stop-position

    还原时长需要考虑

mysql备份工具:
    mysqldump 逻辑备份工具
    mysqlhotcopy物理备份工具,会锁表,温备

文件系统工具:
    cp
    快照

innodb:
    ibbackup(图形化操作软件)
    xtrabackup(图形化操作软件)

mysqldump:
    进行完全备份+二进制日志
    完全+增量(需要手动进入mysql界面使用:flush logs;)

 

mysqldump db_name [tb1] [tb2]            # 默认不会备份创建数据库的sql语句

备份步骤:
mysql>flush tables with read lock;   #锁表(建议锁住所有表,可以防止表表之间的关联关系)
mysqldump -uroot -p123456 db_name > file.sql  #备份
mysqldump -uroot -p123456 db_name tb_name tb_name > file.sql   #备份

mysql>unlock tables   #解锁表

--master-data=0|1|2
0:不记录二进制日志文件极其事件位置
1:以change master to方式记录位置,可以用于恢复后直接启动从服务器
2:以change master to方式记录位置,默认为注释

--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

备份还原:
    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语句

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

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

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.增量备份二进制日志

复制:
   mysql ab复制
    mysql server1----U,D,T------>mysql server2
   原理:
    当主库有更新操作的时候,主库会把更新操作的sql,写如到二进制日志文件当中,并维护一个二进制日志文件的索引文件,以便日志文件轮询
    在从库启动异步复制的时候,从库会开启两个I/O线程
    其中一个用于连接主库
    另外一个用于传输sql

注意:
    必须打开二进制日志文件
    从库必须知道主库是从那一给位置偏移量开始的
    主库和从库的数据库名称可以不一样,推荐使用一样
    主库和从库的mysql版本必须一致
192.168.1.1:master
192.168.1.2:slave


vim /etc/my.cnf
master:
     log-bin    = mysql-bin
     relay-log  = mysql-relay-bin
     server-id  = 1
     binlog-do-db= up01
     binlog-ignore-db = mysql
     binlog-ignore-db = test
slave:
    log-bin = mysql-bin
    relay-log = mysql-relay-bin
    server-id = 2
    master-host=192.168.1.1
    master-port=3306
    master-user=repl
    master-password=repl
    master-connect-retry=30
    replicate-do-db=up01
    replicate-ingore-db=mysql
    replicate-ingore-db=test

共有 人打赏支持
粉丝 5
博文 44
码字总数 54220
×
doudou___9958
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: