#mysql运维日记（二）：备份与恢复

mysql运维笔记（二）：备份与恢复

• 主从

备份

• mysql的备份常用的是binlog和sql转储，如果是企业级应用还可以使用MySQL Enterprise Backup。

• 建议

  1. 如果是企业级应用则直接使用MySQL Enterprise Backup，有丰富的功能与工具。
  2. sql整点备份，然后在恢复时使用sql+binlog来恢复到任意时刻。许多场景下整点恢复已经足够了。

• MySQL Enterprise Backup

  MySQL Enterprise Backup 8.0.15是MySQL 8.0.15的备份实用程序。它是一个多平台，高性能的工具，提供丰富的功能，如热备份，增量和差异备份，选择性备份和恢复，支持直接云存储备份，备份加密和压缩，以及许多其他有价值的功能特征。
  虽然针对InnoDB表进行了优化，但MySQL Enterprise Backup能够备份和恢复由MySQL支持的任何类型的存储引擎创建的各种表。其读取和写入过程的并行性（在独立的多线程中执行）及其块级并行性（不同的线程可以在单个文件中读取，处理或写入不同的块）允许以极快的速度完成备份和恢复过程，与logical backup使用mysqldump等工具 相比，通常可以获得显着的性能提升。
  它是MySQL企业版的一部分，可以通过商业许可向订户提供。

• bin-log

  • binlog只需要在my.cnf中配置即可启动。因为它是增量备份，恢复时会比较慢。此外在设置binlog_expire_logs_seconds/day来定时删除binlog时，这个值应该不低于从属服务器可能滞后于主服务器的最长时间。

  
  log-bin= /export/mysql/mysqllog/binlog/mysql-bin.bin
  binlog_format= MIXED
  max-binlog-size  = 512M
  expire_logs_days = 3
  binlog_cache_size = 32M
  log_bin_trust_function_creators =1
  
  

• mysqldump

  • mysqldump --single-transaction --flush-logs --skip-add-locks --quick --master-data=2 db_name > db_name.sql
  • mysql -e "source /path-to-backup/backup-file.sql" db_name 或者mysql db_name < backup-file.sql
  • 把库从一个地方复制到另一个地方可以这样写mysqldump --opt db_name | mysql --host=remote_host -C db_name
  • 关于此操作对表的锁定

  此备份操作在转储开始时（使用FLUSH TABLES WITH READ LOCK）获取所有表的全局读锁定。获取此锁定后，将读取二进制日志坐标并释放锁定。如果在FLUSH发出语句时正在运行长更新语句 ，则备份操作可能会停止，直到这些语句完成。之后，转储变得无锁，并且不会干扰表上的读取和写入。

  • 如果在备份期间能保证表的结构不会变化可以指定–skip-add-locks
  • 假如你想要在10点备份，备份过程长达半个小时，在这期间有些表的数据还会发生变化，如果你想要所有的表的备份都是10点时刻，则应该指定–single-transaction。如果不指定此选项，假如有十个表，在10:02时mysqldump开始备份第七个表，则这个表的备份的内容是10：02时刻而不是10:00。

  此选项将事务隔离模式设置为， REPEATABLE READ并START TRANSACTION在转储数据之前将SQL语句发送到 服务器。它仅对事务表有用，例如InnoDB，因为它在START TRANSACTION发出时不转储任何应用程序时转储数据 库的一致状态 。
  使用此选项时，应记住只有 InnoDB表以一致状态转储。例如，使用此选项时转储的任何表MyISAM或 MEMORY表仍可能更改状态。
  虽然 --single-transaction转储过程，以确保有效的转储文件（正确的表的内容和二进制日志坐标），没有其他的连接应使用以下语句： ALTER TABLE， CREATE TABLE， DROP TABLE， RENAME TABLE， TRUNCATE TABLE。一致读取不会与这些语句隔离，因此在要转储的表上使用它们会导致 mysqldumpSELECT执行该操作 以检索表内容以获取不正确的内容或失败。该–single-transaction选项与 --lock-tables选项是相互排斥的，因为LOCK TABLES会导致任何挂起的事务隐含提交。
  要转储大表，请将该–single-transaction选项与–quick选项组合 使用 。

  • 为了能够跟 binlog配合使用，还应该指定–flush-logs，在开始备份前会刷新binlog日志。
  • –quick 此选项对于转储大型表非常有用。它强制 mysqldump一次一行地从服务器检索表的行，而不是检索整个行集并在写出之前在内存中缓冲它。
  • –master-data 如果指定要备份主库则应该指定此选项
  • 在shell脚本执行备份时时常需要知道是否备份成功，可以这样写

  mysqldump $MYSQL_OPTS $database $tables |gzip -1 >$path
  if [ ${PIPESTATUS[0]} != 0 ]; then
  	echo " ## Backup of $db tables $tables FAILED! ##"
  else
  	echo "Backup of $db $tables successful!"
  fi