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

喜欢ヅ旅行 2022-03-12 02:50 198阅读 0赞

# 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