Mysql备份与恢复工具mysqldump

一、简介

mysqldump 是 MySQL 自带的逻辑备份工具。

二、基础

• log_bin：二进制日志

  记录所有的事务操作，包括空数据库查询。

  • 功能

    1.数据恢复

    2.从同步主

  • 查看日志是否开启

    mysql> show global variables like '%log%';
    # log_bin : ON
    

  • 开启日志（默认关闭）

    vim  /etc/my.cnf
    # 添加
    ========================================================================================================
    server-id=1
    binlog_format=row
    expire_logs_days=30
    # for UBUNTU
    # log_bin=/var/log/mysql/mysql-bin.log
    # for CentOS
    # log_bin=/var/log/mariadb/mariadb-bin.log
    # for Mac
    log_bin=/usr/local/mysql/data/mysql-bin
    ========================================================================================================
    # 授权
    sudo chmod 664 my.cnf
    # 重启
    service mysql restart
    # 查看
    mysql> show variables like 'log_bin%';
    

• sql_log_bin：动态变量

  可以只对当前会话生效（Session），也可以是全局的（Global），当全局修改这个变量时，只会对新的会话生效 （这意味当对当前会话也不会生效），因此一般全局修改了这个变量后，都要把原来的所有连接 kill 掉。

  • 开启日志场景（sql_log_bin=1）：

    搭建一套全新的主从数据库，必须打开二进制日志，否则从库无法同步主库。

  • 关闭日志场景（sql_log_bin=0）：

    还原的时候关闭二进制日志，否则还原过程也会记录在二进制日志里，浪费资源。

  • 操作

    mysql> set @@session.sql_log_bin = 1;
    mysql> select @@session.sql_log_bin; 
    

• GTID

  GTID(GlobalTransaction ID)是对于一个已提交事务的编号，并且是一个全局唯一的编号。GTID实际上是由UUID+TID组成的。其中UUID是一个MySQL实例的唯一标识。TID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增。

  eg : SET @@GLOBAL.GTID_PURGED=‘02c6b686-a77c-11e8-8d59-7a8ac0d4c5s3:1-761606’;

• 主从同步

  mysql> show slave status;
  

三、导出

• 命名

  # 日期
  echo `date +%Y%m%d`
  # 日期时间(date后面必须有一个空格，+号后面必须不能有空格)
  echo `date +%Y%m%d%H%M%S`
  

• 整库数据及结构压缩导出

  mysqldump -u 你的数据库用户名 -p你的源数据库密码 -P 端口 -h 数据库IP --set-gtid-purged=off 数据库名称｜gzip >数据库名称_bak_`date +%Y%m%d%H%M%S`.sql.gz
  

  参数说明：
  -d：等价于-–no-data，只导出数据库表结构；

  -t：等价于—no-create-info，只导出数据；

  –skip-lock-tables：可以在不锁表的情况导出数据，速度更快；

  –default-character-set=utf8：设置编码；

  –all-databases：导出该实例的所有数据库；

  –set-gtid-purged=off：不废除GTID（全局事务ID），不增加 GLOBAL.GTID_PURGED变量。

  Value	Meaning
  OFF	不添加SET语句。
  ON	添加SET语句。 如果未在服务器上启用GTID，则会发生错误。(SET @@SESSION.SQL_LOG_BIN=0;# 不记录日志)
  AUTO	如果服务器上启用了GTID，则添加SET语句。

四、导入

• 0.建库

  mysqldump导出无建库脚本

  • 创建create_db_demo.sql文件

    CREATE DATABASE demo DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
    

  • 执行

    mysql>source create_db_demo.sql
    

• 1、导入表

  mysql -uroot -p demo < demo_bak_20200707.sql
  

• 2、导入库

  mysql -uroot -p < demo_bak_20200707.sql