MySQL8备份恢复（五）

　　MySQL8备份恢复（一）
　　MySQL8备份恢复（二）
　　MySQL8备份恢复（三）
　　MySQL8备份恢复（四）
　　MySQL8备份恢复（五）锁定实例备份恢复
　　MySQL8。x中新增了一个轻量级的备份锁，它允许在online备份的时候进行DML操作，同时可防止快照不一致。这个锁禁止的操作很少，它禁止的操作包括：
　　1。文件的创建，删除，改名2。账号的管理3。REPAIRTABLE4。TRUNCATETABLE5。OPTIMIZETABLE
　　长查询对flushtableswithreadlock是有影响的，整个系统会hung住，这时其它的数据库查询也不能用，包括usedatabase
　　而lockinstanceforbackup没有这个问题。Oracle自己的备份产品（MySQLEnterpriseBackup8）就用这个锁，Percona的Xtrabackup在8的版本也是用这个锁
　　percona自己的MySQL也有一种轻量级的备份锁locktableforbackup也是一种比flushtableswithreadlock要轻量级的锁，它不会刷新表，即存储引擎不会强制关闭表，并且表不会从表缓存中排出。因此，LOCKTABLESFORBACKUP仅等待冲突的语句完成，它不会等待SELECT或更新InnoDB表来完成开始备份之前，请锁定需要备份的实例：mysqlLOCKINSTANCEFORBACKUP；执行备份rsyncavvarlibmysqlbackups完成后解锁实例：mysq1UNLOCKINSTANCE；
　　测试结果：锁定后可以DML，但是会阻塞EVENT事件二进制日志备份
　　1。在服务器上创建一个复制用户CREATEUSERreplIDENTIFIEDWITHmysqlnativepasswordBY；GRANTREPLICATIONSLAVEON。TOrepl；flushprivileges；
　　2。检查服务器上的二进制日志：SHOWBINARYLOGS；
　　在服务器上找到第一个可用的二进制日志，可以从这里开始备份。例如binlog。000005
　　3。登录到备份服务器并执行以下命令，会将二进制日志从MySQL服务器复制到备份服务器。你可以使用nohup或disownmysqlbinlogh192。168。55。44ureplpreadfromremoteserverstopnevertolastlograwbinlog。000005shelldisownadisown效果相当于忘记输入nohup，同样的还有setsid
　　4。验证是否正在备份二进制日志lsltrbinlog。MySQLShell备份恢复一说明MySQLShell介绍
　　MySQLShell是MySQL的一个高级客户端和代码编辑器，是第二代MySQL客户端。第一代MySQL客户端即我们常用的mysqlMySQLShell关键特性
　　支持Python和JavaScript两种语言模式。基于此，我们可以很容易地进行一些脚本开发工作
　　支持AdminAPI。AdminAPI可用来管理InnoDBCluster、InnoDBClusterSet和InnoDBReplicaSet
　　支持XDevAPI。XDevAPI可对文档（Document）和表（Table）进行CRUD（Create，Read，Update，Delete）操作MySQLShell核心优势
　　1。并行Parallelization默认4个线程进行导出或者导入数据，可根据情况自己定义
　　2。分块Chunking默认开启，将每个表数据分成大小相等的数据文件，默认64兆一个文件，由参数bytesPerChunk控制，最小128k（针对那些小表，小于64兆的表）
　　3。压缩Compression
　　默认开启压缩，压缩类型为时下最新的zstd压缩，另外还支持gzip压缩，zstd在压缩和解压之间获得了一个折中的平衡，相对比gzip压缩率高的情况下还要比gzip解压时间更短。compression：zstd默认开启，不用设置
　　4。输出格式Outputformat
　　MySQLShellUtilities工具默认使用loaddata语句预期的默认格式进行数据导出和导入，这会让输出文件更小，同时导入速度也更加快速。
　　mysqldump，msqlpump，mydumper这三类导出工具都是以insert语句的形式导出数据，这在速度上明显要落后于loaddata语句，官方文档中描述loaddata方式要比insert方式快20倍MySQLShell使用时注意
　　通过dumpInstance，dumpSchemas，dumpTables生成的备份只能通过loadDump来恢复
　　通过exportTable生成的备份只能通过importTable来恢复
　　使用限制：MySQLShell工具目前在数据导出导入时，目标库的版本只适用于MySQL5。7和8。0版本，不适用低于5。7的版本
　　MySQLShell的安装yuminstallmysqlshellcommercial8。0。311。1。el8。x8664。rpm登录mysqlshuriroot127。0。0。1：3308mysql二例子
　　1。备份例子util。dumpInstance（backupfull，｛compression：none｝）AcquiringglobalreadlockGlobalreadlockacquiredInitializingdone1outof5schemaswillbedumpedandwithinthem8tables，0views。3outof6userswillbedumped。GatheringinformationdoneAlltransactionshavebeenstartedLockinginstanceforbackupGlobalreadlockhasbeenreleasedWritingglobalDDLfilesWritingusersDDLRunningdatadumpusing4threads。NOTE：Progressinformationusesestimatedvaluesandmaynotbeaccurate。WritingschemametadatadoneWritingDDLdoneWritingtablemetadatadoneStartingdatadump124（198。22Krows158。99Krows），133。81Krowss，0。00BsDumpduration：00：00：00sTotalduration：00：00：01sSchemasdumped：1Tablesdumped：8Datasize：598。90MBRowswritten：198221Byteswritten：598。90MBAveragethroughput：598。90MBs
　　2。备份产生的内容
　　。done。json：会记录备份的结束时间，备份集的大小。备份结束时生成
　　。json：会记录备份的一些元数据信息，包括备份时的一致性位置点信息：binlogFile，binlogPosition和gtidExecuted，这些信息可用来建立复制
　　。sql，。post。sql：这两个文件只有一些注释信息。在通过util。loadDump导入数据时，我们可以通过这两个文件自定义一些SQL。其中，。sql是数据导入前执行，。post。sql是数据导入后执行
　　。tsv：数据文件。我们看看数据文件的内容
　　TESTanews。json：记录了表相关的一些元数据信息，如列名，字段之间的分隔符（fieldsTerminatedBy）等
　　TESTanews。sql：建表语句
　　TEST。json：记录数据库中已经备份的表、视图、定时器、函数和存储过程
　　TEST。sql：建库语句。如果这个库中存在存储过程、函数、定时器，也是写到这个文件中
　　。users。sql：创建账号及授权语句。默认不会备份mysql。session，mysql。session，mysql。sys这三个内部账号
　　3。恢复例子util。loadDump（backupfull）LoadingDDLandDatafrombackupfullusing4threads。Openingdump。。。TargetisMySQL8。0。31。DumpwasproducedfromMySQL8。0。31ScanningmetadatadoneCheckingforpreexistingobjects。。。ExecutingcommonpreambleSQLExecutingDDLdoneExecutingviewDDLdoneStartingdataload1thdsloading100（598。90MB598。90MB），20。59MBs，78tablesdoneExecutingcommonpostambleSQLRecreatingindexesdone23chunks（198。22Krows，598。90MB）for8tablesin1schemaswereloadedin50sec（avgthroughput12。13MBs）0warningswerereportedduringtheload。
　　4。恢复产生的内容
　　loadprogress。。progress：在导入的过程中，会在备份目录生成一个progressFile，用于记录加载过程中的进度信息，可用来实现断点续传功能三命令说明
　　1。备份命令
　　1）备份实例其中，outputUrl是备份目录，其必须为空。options是可指定的选项
　　util。dumpInstance（outputUrl〔，options〕）
　　2）备份指定库的数据。第一个参数必须为数组，如util。dumpSchemas（〔TEST〕，backupschema）从MySQLShell8。0。28开始，可直接使用util。dumpInstance中的includeSchemas选项进行指定库的备份
　　util。dumpSchemas（schemas，outputUrl〔，options〕）
　　3）备份指定表的数据。第二个参数必须为数组，如util。dumpTables（TEST，〔t〕，backuptable）从MySQLShell8。0。28开始，可直接使用util。dumpInstance中的includeTables选项进行指定表的备份
　　util。dumpTables（schema，tables，outputUrl〔，options〕）
　　2。恢复命令
　　util。loadDump（url〔，options〕）
　　3。不进入mysqlsh，操作系统命令行执行备份
　　mysqlshuriroot127。0。0。1：3308mysqleutil。dumpInstance（backupfull1）四特性说明
　　1。util。dumpInstance的关键特性
　　多线程备份。并发线程数由threads决定，默认是4
　　支持单表chunk级别的并行备份，前提是表上存在主键或唯一索引
　　默认是压缩备份
　　支持备份限速。可通过maxRate限制单个线程的数据读取速率
　　util。loadDump的关键特性
　　2。util。loadDump的关键特性如下：
　　多线程恢复。并发线程数由threads决定，默认是4
　　支持断点续传功能
　　在导入的过程中，会在备份目录生成一个进度文件，用于记录导入过程中的进度信息
　　文件名由progressFile指定，默认是loadprogress。。progress
　　导入时，如果备份目录中存在progressFile，默认会从上次完成的地方继续执行。如果要从头开始执行，需将resetProgress设置为true
　　支持延迟创建二级索引
　　支持边备份，边导入
　　通过LOADDATALOCALINFILE命令来导入数据
　　如果单个文件过大，util。loadDump在导入时会自动进行切割，以避免产生大事务
　　3。util。dumpInstance与mysqldump不同的地方
　　util。dumpInstance会加备份锁。备份锁可用来阻塞备份过程中的DDL
　　util。dumpInstance是并行备份，相对于mysqldump的单线程备份，备份效率更高五参数说明
　　1。util。dumpInstance的参数解析
　　过滤相关的选项：
　　excludeSchemas：忽略某些库的备份，多个库之间用逗号隔开，如，excludeSchemas：〔db1，db2〕
　　includeSchemas：指定某些库的备份
　　excludeTables：忽略某些表的备份，表必须是schema。table的格式，多个表之间用逗号隔开，如，excludeTables：〔sbtest。sbtest1，sbtest。sbtest2〕
　　includeTables：指定某些表的备份
　　events：是否备份定时器，默认为true
　　excludeEvents：忽略某些定时器的备份
　　includeEvents：指定某些定时器的备份
　　routines：是否备份函数和存储过程，默认为true
　　excludeRoutines：忽略某些函数和存储过程的备份
　　includeRoutines：指定某些函数和存储过程的备份
　　users：是否备份账号信息，默认为true
　　excludeUsers：忽略某些账号的备份，可指定多个账号
　　includeUsers：指定某些账号的备份，可指定多个账号
　　triggers：是否备份触发器，默认为true
　　excludeTriggers：忽略某些触发器的备份
　　includeTriggers：指定某些触发器的备份
　　ddlOnly：是否只备份表结构，默认为false
　　dataOnly：是否只备份数据，默认为false
　　并行备份相关的选项
　　chunking：是否开启chunk级别的并行备份功能，默认为true
　　bytesPerChunk：每个chunk文件的大小，默认64M
　　threads：并发线程数，默认为4
　　OCI（甲骨文云）相关：
　　ocimds：是否检查备份集与甲骨文云的MySQL云服务MySQLDatabaseService（简称MDS）的兼容性，默认为false，不检查。如果设置为true，会输出所有的不兼容项及解决方法。不兼容项可通过下面的compatibility来解决。
　　compatibility：如果要将备份数据导入到MDS中，为了保证与后者的兼容性，可在导出的过程中进行相应地调整。具体来说：
　　1）createinvisiblepks：对于没有主键的表，会创建一个隐藏主键：myrowidBIGINTUNSIGNEDAUTOINCREMENTINVISIBLEPRIMARYKEY。隐藏列是MySQL8。0。23引入的
　　2）forceinnodb：将表的引擎强制设置为InnoDB
　　3）ignoremissingpks：忽略主键缺失导致的错误，与createinvisiblepks互斥，不能同时指定
　　4）skipinvalidaccounts：忽略没有密码，或者使用了MDS不支持的认证插件的账号
　　5）stripdefiners：去掉视图、存储过程、函数、定时器、触发器中的DEFINERaccount子句
　　6）striprestrictedgrants：去掉MDS中不允许GRANT的权限
　　7）striptablespaces：去掉建表语句中的TABLESPACExxx子句
　　osBucketName，osNamespace，ociConfigFile，ociProfile，ociParManifest，ociParExpireTime：OCI对象存储相关
　　其它选项：
　　tzUtc：是否设置TIMEZONE00：00，默认为true
　　consistent：是否开启一致性备份，默认为true。若设置为false，则不会加全局读锁，也不会开启事务的一致性快照
　　dryRun：试运行。此时只会打印备份信息，不会执行备份操作
　　maxRate：限制单个线程的数据读取速率，单位byte，默认为0，不限制
　　showProgress：是否打印进度信息，如果是TTY设备（命令行终端），则为true，反之，则为false
　　defaultCharacterSet：字符集，默认为utf8mb4
　　compression：备份文件的压缩算法，默认为zstd。也可设置为gzip或none（不压缩）
　　2。util。loadDump的参数解析
　　过滤相关：
　　excludeEvents：忽略某些定时器的导入
　　excludeRoutines：忽略某些函数和存储过程的导入
　　excludeSchemas：忽略某些库的导入
　　excludeTables：忽略某些表的导入
　　excludeTriggers：忽略某些触发器的导入
　　excludeUsers：忽略某些账号的导入
　　includeEvents：导入指定定时器
　　includeRoutines：导入指定函数和存储过程
　　includeSchemas：导入指定库
　　includeTables：导入指定表
　　includeTriggers：导入指定触发器
　　includeUsers：导入指定账号
　　loadData：是否导入数据，默认为true
　　loadDdl：是否导入DDL语句，默认为true
　　loadUsers：是否导入账号，默认为false。注意，即使将loadUsers设置为true，也不会导入当前正在执行导入操作的用户
　　ignoreExistingObjects：是否忽略已经存在的对象，默认为off
　　并行导入相关：
　　backgroundThreads：获取元数据和DDL文件内容的线程数。备份集如果存储在本地，backgroundThreads默认和threads一致
　　threads：并发线程数，默认为4
　　maxBytesPerTransaction：指定单个LOADDATA操作可加载的最大字节数。默认与bytesPerChunk一致。这个参数可用来规避大事务
　　断点续传相关：
　　progressFile：在导入的过程中，会在备份目录生成一个progressFile，用于记录加载过程中的进度信息，这个进度信息可用来实现断点续传功能。默认为loadprogress。。progress
　　resetProgress：如果备份目录中存在progressFile，默认会从上次完成的地方继续执行。如果要从头开始执行，需将resetProgress设置为true。该参数默认为off
　　OCI相关：
　　osBucketName，osNamespace，ociConfigFile，ociProfile
　　二级索引相关：
　　deferTableIndexes：是否延迟（数据加载完毕后）创建二级索引。可设置：off（不延迟），fulltext（只延迟创建全文索引，默认值），all（延迟创建所有索引）
　　loadIndexes：与deferTableIndexes一起使用，用来决定数据加载完毕后，最后的二级索引是否创建，默认为true
　　其它选项：
　　analyzeTables：表加载完毕后，是否执行ANALYZETABLE操作。默认是off（不执行），也可设置为on或histogram（只对有直方图信息的表执行）
　　characterSet：字符集，无需显式设置，默认会从备份集中获取
　　createInvisiblePKs：是否创建隐式主键，默认从备份集中获取。这个与备份时是否指定了createinvisiblepks有关，若指定了则为true，反之为false
　　dryRun：试运行
　　ignoreVersion：忽略MySQL的版本检测。默认情况下，要求备份实例和导入实例的大版本一致
　　schema：将表导入到指定schema中，适用于通过util。dumpTables创建的备份
　　showMetadata：导入时是否打印一致性备份时的位置点信息
　　showProgress：是否打印进度信息
　　skipBinlog：是否设置sqllogbin0，默认false。这一点与mysqldump（开启GTID的情况下）、mydumper不同，后面这两个工具默认会禁用Binlog
　　updateGtidSet：更新GTIDPURGED。可设置：off（不更新，默认值），replace（替代目标实例的GTIDPURGED），append（追加）
　　waitDumpTimeout：util。loadDump可导入当前正在备份的备份集。处理完所有文件后，如果备份还没有结束（具体来说，是备份集中没有生成。done。json），util。loadDump会报错退出，可指定waitDumpTimeout等待一段时间，单位秒六注意事项
　　1。表上存在主键或唯一索引才能进行chunk级别的并行备份。字段的数据类型不限。不像mydumper，分片键只能是整数类型
　　2。对于不能进行并行备份的表，目前会备份到一个文件中。如果该文件过大，不用担心大事务的问题，util。loadDump在导入时会自动进行切割
　　3。util。dumpInstance只能保证InnoDB表的备份一致性
　　4。默认不会备份informationschema，mysql，ndbinfo，performanceschema，sys
　　5。备份实例支持MySQL5。6及以上版本，导入实例支持MySQL5。7及以上版本
　　6。备份的过程中，会将BLOB等非文本安全的列转换为Base64，由此会导致转换后的数据大小超过原数据。导入时，注意maxallowedpacket的限制
　　7。导入之前，需将目标实例的localinfile设置为ON速度比较
　　数据库73G：
　　mysqldump：34分钟输出10G，
　　myshelldump线程4（默认）：30分钟输出10G，
　　myshelldump线程8：5分钟输出10G，
　　myshelldump线程12：5分钟输出10G，
　　xtrabackup：34分钟输出73G