备份和恢复

mysqldump + 复制binlog

mysqldump: 完全备份

复制binlog中指定时间范围内的event: 增量备份

lvm2快照+ 复制binlog

lvm2快照: 使用cp或tar等做物理被封: 完全备份

复制binlog中指定的event: 增量备份

xtarbackup:

由percona提供支持对innodb做(物理备份)的工具

完全备份, 曾量备份

---

binlog日志查看命令

mysql> show master|binary logs;
mysql> show master status;
mysql> show binlog events in 'binlog.000009';  | show binlog events in 'binlog.000009' from pos;

客户端
mysqlbinlog
–start-position
–stop-position
–start-datetime
–stop-datetime
选项: -u -h -p

备份名词

完全备份: 完整的数据库备份
增量备份: 以上一次备份为对象 以来 更改的备份数据
差异被封: 备份上次以完整备份以后发生改变的备份数据
物理备份: 复制文件
逻辑备份: 抽取数据,把每个表的数据抽取出来转换成一个巨大的insert语句,存储到文件里面,不适用于完整的大数据备份
热备: 在线被封  可数据库可以进行读写操作
冷备: 停机备份   数据库不可读写
温被: 锁表  数据库只能读 不能写

备份工具

mysqldump
cp , tar
lvm2
xtarbackup

mysqldump (不建议备份GB级别.速度慢)

客户端程序,通过mysql协议链接至mysqld服务,发起一个全量查询操作,把所有数据读取到本地,保存到本地文件里面,实现备份
常用命令

# mysqldump [option] db_name [table_name]                : 直接这样写 sql语句里面不会 自动create创建库, 需要手动创建
# mysqldump [option] --databases db_name  [table_name]   : 指明 --databases sql文件里面会自动创建 create database 不用手动创建数据库
# mysqldump [option] --all-databases 

# 线上数据库不建议直接备份, 先进行 锁 表 然后在备份
mysqldump -root -pxxxxx --databases xxx_db  xxx_name > xxxx.sql

# innodb: 锁定
# -single-transaction 

# 备份指定数据库相关的所有event scheduler 事件调度器
# -E , --events

# 备份指定数据库相关的所有存储过程和存储函数
# -R , --routines

# 备份当前 binlog 日志位置
# --master-data=[0|1|2]
# 
mysqldump -root -pxxxxx --databases xxx_db  xxx_name > xxxx.sql

# --flush-logs
# 锁定表完成后, 执行 flush logs命令

# 锁定数据库myisam(温备)
# -lock-tables     :锁定单个库的所有表
# -lock-all-tables :锁定所有的库的所有表


##二进制恢复

#把二进制日志导出成sql文件 
mysqlbinlog --start-position xxx(记录的日志位置数字) /var/lib/mysql/binlog.00001 > back.sql

#把二进制恢复的sql文件导入数据库
mysql -uroot -p < back.sql

主从配置

主节点

• 启用二进制日志
  • 修改 /etc/my.cnf , 添加: log-bin=binlog
• 为当前节点设置一个全局唯一的ID 号
  • 修改 /etc/my.cnf , 添加 server-id=xx(全局唯一)
• 创建有复制权限的用户账号
  • REPLICATION SLAVE, REPLICATION CLIENT
  • GRANT REPLICATION SLAVE, REPLICATION CLIENT CLIENT ON . TO ‘USERNMAE’@’HOST’ IDENTIFIED BY ‘USERPASSWD’

从节点

• 启用中继日志

  • 修改 /etc/my.cnf , 添加 reploy-log=reploy-log(日志名)

• 为当前节点设置一个全局唯一的ID号

  • 修改 /etc/my.cnf , 添加 server-id=xx(全局唯一)

• 启用二进制日志(根据需要开启)

  • 修改 /etc/my.cnf , 添加: log-bin=binlog\

• 使用有复制权限的用户账号链接至主服务器,并启动复制线程

  #查看"change master to" 命令帮助
  help change master to;
  #链接至主服务器, 需要注意两点,MASTER_LOG_FILE 同步的二进制文件 , MASTER_LOG_POS 节点
  change master to MASTER_HOST='192.168.196.129',MASTER_USER='root',MASTER_PASSWORD='9Tong@123',MASTER_LOG_FILE='binlog.000002',MASTER_LOG_POS=154;
  #查看从节点状态
  show slave status\G
  #启动复制线程
  start slave;

  复制架构中应该注意的问题

• 限制从服务器为读

  • 查看读状态 mysql> show global variables like ‘%read_only%’;
  • 修改 /etc/my.cnf , 添加: read_only=ON , 此限制对拥有 SUPER 权限的用户均无效
  • 组织所有用户
    • mysql> flush tables with read lock; 适用于当前启动环境

• 如何保证主从复制的事务安全

  • 在master节点上面启动参数:
    • sync_binlog=ON : 当遇到事务提交时,立即将binlog缓冲区中记录下来的事件刷写到磁盘中的二进制日志文件中, 从而通知从节点
    • innodb引擎开启
      • innodb_flush_logs_at_trx_commit=ON : 当遇到事务提交时, 立即将事务日志内存缓冲区的与事务相关的数据 刷写到磁盘中的事务日志中
      • innodb_support_xa=ON : 让innodb 支持分布式提交 基于这个实现两段式提交 默认开启
  • 在从节点(slave)
    • skip_slave_start=ON :开机是否启动同步服务 建议不开启
  • 中继日志参数
    • master节点
      • sync_master_info
    • slave节点
      • sync_relay_log
      • sync_relay_log_info
  • 一些重要文件
    • /var/lib/mysql/{master.info,relay_log,info}
    • master.info 主服务链接参数和同步参数, relay_log,info 中继日志同步参数

主主复制

• 互为主从

  • 数据不一致 不建议用

  复制过滤器

  让从节点仅指定的数据库或指定数据库的指定表