MySQL企业备份4.1发布笔记
MySQL实例从备份恢复数据库服务器
关闭数据库服务器
删除服务器数据目录内所有文件并删除目录内所有文件
--innodb_data_home_dir,--innodb_log_group_home_dir并--innodb_undo_directory选项恢复,如果目录与数据目录不同举例使用
copy-back-and-apply-log命令将原备份转换成准备备份并更新为一致性状态,然后将表、索引、元数据和任何其他所需文件复制到目标服务器上各种选项可指定此操作,见第15.3节,恢复操作.
下例中单文件备份第4.2.2节“整理全 MySQL实例使用恢复copy-back-and-apply-log命令除常用连接参数外,还使用下列选项:
默认文件提供配置恢复数据必须是首选显示mysql备份命令,如果使用过在大多数情况下,您可以向mysql备份使用此选项配置文件目标服务器恢复数据后InnoDB设置与目标服务器不同时,必须提供备份值mysql备份恢复期间mysqld启动恢复服务器时(反恢复可能失效,并可能启动恢复服务器有问题):如果您不确定这些设置备份,则存储在
备份my.cnf文件备份期间-您可以在临时目录中查找文件备份dir单图像备份创建时或备份目录创建时使用备份图像拆包提取式命令如果这些选项的值与目标服务器上值不同, 添加到配置文件中mysql备份并存配置文件后启动服务器或可提供命令行选项mysql备份并mysqld.上表某些选项
innodb_data_file_path,innodb_log_file_size,innodb_log_files_in_group并innodb_undo_tablespaces)mysql备份校验提供它们的值以确保你事后能启动目标服务器并使用这些值:如果其中任何一个值与备份实际值不匹配,它会报错警告发布时,这些值不指定mysql备份或配置文件或命令行内(下文示例中即为此例)。数据目录提供数据目录位置恢复数据您必须为恢复操作指定此选项后援图像提供单文件备份路径备份dir提供空文件夹位置存储恢复进程期间创建的一些临时文件
/mysql备份-defatsfile=/etc/mysql/my.cnf-datadir=/var/lib/mysql/-fackupimage=/home/admin/backups/my.mbi-form-dir=/home/admin/restive-tm全权保留170214 22:11:38MINFO:线程编译/mysql备份-defatsfile=/etc/mysql/my.cnf-datadir=/var/lib/mysql-sackup-image=/home/admin/backup/my.mbi-bru成功回溯应用日志尾运行 mysqlback170214 22:11:38MINFO:备份图像MEB版本字符串4.1.0[2017/02/13]170214 22:11:38MINFO: MySQL服务器版本为'5.717'170214'22:11:38MINWARINING:如果你恢复不同版本服务器,innodb_data_file#path参数可能有不同的默认值indb_data_file_path=ibdata1:12M:utomextend170214 22:11:38 Main警告:如果恢复不同版本服务器,innodb_log_files_in_group参数可能有不同的默认值indb_logfiles_in_group=2170214 22:11:38 Main警告:如果恢复不同版本服务器,innodb_log_file参数可能有不同的默认值indb_log_file=50331648170214 22:11:38MININFO:键ring服务未初始化MEB无法验证加密密钥170214 22:11:38MAININFO:有效密钥指针将在服务器启动访问加密表170214 22:11:38 MAIN INFO: MEB logfile created at /home/admin/restore-tmp/meta/MEB_2017-02-14.22-11-38_copy_back_img_to_datadir.log -------------------------------------------------------------------- Server Repository Options: -------------------------------------------------------------------- datadir = /var/lib/mysql innodb_data_home_dir = /var/lib/mysql innodb_data_file_path = ibdata1:12M:autoextend innodb_log_group_home_dir = /var/lib/mysql innodb_log_files_in_group = 2 innodb_log_file_size = 50331648 innodb_buffer_pool_filename = ib_buffer_pool innodb_page_size = Null innodb_checksum_algorithm = crc32 -------------------------------------------------------------------- Backup Config Options: -------------------------------------------------------------------- datadir = /home/admin/restore-tmp/datadir innodb_data_home_dir = /home/admin/restore-tmp/datadir innodb_data_file_path = ibdata1:12M:autoextend innodb_log_group_home_dir = /home/admin/restore-tmp/datadir innodb_log_files_in_group = 2 innodb_log_file_size = 50331648 innodb_buffer_pool_filename = ib_buffer_pool innodb_page_size = 16384 innodb_checksum_algorithm = crc32 170214 22:11:38 MAIN INFO: Creating 14 buffers each of size 16777216.170214 22:11:38 MAIN INFO: Copy-back-and-apply-log operation starts with following threads 1 read-threads 6 process-threads 1 write-threads 170214 22:11:38 RDR1 INFO: A thread created with Id '140026981721856' 170214 22:11:38 PCR1 INFO: A thread created with Id '140026973329152' 170214 22:11:38 PCR1 INFO: Copying database directory: meta 170214 22:11:38 PCR2 INFO: A thread created with Id '140026964936448' 170214 22:11:38 PCR3 INFO: A thread created with Id '140026956543744' 170214 22:11:38 PCR4 INFO: A thread created with Id '140026948151040' 170214 22:11:38 PCR5 INFO: A thread created with Id '140026939758336' 170214 22:11:38 PCR6 INFO: A thread created with Id '140026931365632' 170214 22:11:38 WTR1 INFO: A thread created with Id '140026922972928' 170214 22:11:38 RDR1 INFO: Copying ibdata1.170214 22:11:38RDR1INFO复制 mysql/engine_cost.ib170214 22:11:38RDR1INFO拷贝 mysql/gtid_e170214 22:11:38RDR1INFO复制 mysql/help170214 22:11:38RDR1INFO复制 mysql/help_keeword.ib170214 22:11:38RDR1INFO复制 mysql/help_relation.ibd170214 22:11:38RDR1INFO复制 mysql/help_topic.ibd170214 22:11:38PCR2INFO复制数据库目录: mysql170214 22:11:39RDRIINFO复制 mysql/innodb_index_stats.ib170214 22:11:39RDR1INFO复制 mysql/innodb_stats.ib170214 22:11:39RDR1INFO复制 mysql/插件ibd170214 22:11:39RDR1INFO复制 mysql/server_cost.ib170214 22:11:39RDR1INFO复制 mysql/servers.ibd170214 22:11:39RDR1INFO复制 mysql/slave_master_info.ib170214 22:11:39RDR1INFO复制 mysql/slavere170214 22:11:39RDR1INFO复制 mysql/slave_worker_info.ibd170214 22:11:39RDR1INFO复制 mysql/time_zone.ib170214 22:11:39RDR1INFO复制 mysql/time_zone_leap_second.ibd170214 22:11:39RDR1INFO复制 mysql/time_zone_name.ib170214 22:11:39RDR1INFO复制 mysql/time_zone170214 22:11:39RDR1INFO拷贝 mysql/time_zone170214 22:11:40RDRIINFO拷贝数据库目录:性能_Schema170214 22:11:40PCR5INFO复制数据库目录:宠物170214 22:11:40RDR1INFO复制宠物/cats.ibd170214 22:11:40PCR5INFO复制数据库目录:sys 170214 22:11:40RDRIINFO复制sys/sys_config.ibd170214 22:11:40 PCR5INFO复制数据库目录: mysql 170214 22:11:40 PCR5INFO复制数据库目录:性能170214 22:11:40 PCR5INFO复制数据库目录:pets 170214 22:11:40 PCR5INFO复制数据库目录:sys 170214 22:11:40MINININFO复制文件170214 22:11:40MINFO:创建服务器配置服务器-my.cnf和server-all.cnf170214 22:11:40MINFO:源图像路径=/home/admin/backup/my.mbi 170214 22:11:40MINFO: MySQL服务器版本为'5.717'170214 22:11:40MINFO:应用日志操作启动线程1读线程1进程线程6应用线程170214 22:11:40MINFO:使用最多100MB内存170214 22:11:40MINFO:ibbbackup_logfile创建参数:启动Isn 2543104,端端Isn 2543132,启动检查站2543123170214 22:11:40 ALW1 INFO: A thread created with Id '140026922972928' 170214 22:11:40 ALW6 INFO: A thread created with Id '140027204466432' 170214 22:11:40 ALW5 INFO: A thread created with Id '140027212859136' 170214 22:11:40 ALW4 INFO: A thread created with Id '140026948151040' 170214 22:11:40 ALW3 INFO: A thread created with Id '140026939758336' 170214 22:11:40 ALW2 INFO: A thread created with Id '140026931365632' 170214 22:11:40 RDR1 INFO: A thread created with Id '140027196073728' 170214 22:11:40 PCR1 INFO: A thread created with Id '140027187681024' 170214 22:11:40 PCR1 INFO: InnoDB: Doing recovery: scanned up to log sequence number 2543132.170214 22:11:40PCR1INFO:InnoDB:启动数据库应用数批日志记录.InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 170214 22:11:40 PCR1 INFO: InnoDB: Setting log file size to 50331648.170214 22:11:45PCR1INFO:InnoDB:设置日志文件尺寸为50331648170214 22:11:50 PCR1INFO:我们能够解析ibbackup_logfile170214 22:11:50 PCR1INFO:第一个数据文件为 /var/lib/mysql/ibdata1170214 22:11:50MINFO:完全备份已成功恢复mysql备份完成带3警告数据库原目录从备份恢复视你启动恢复式服务器的方式而定,你可能需要调整恢复式数据目录的所有权举例说,如果服务器由用户启动mysql下命令修改数据目录和文件所有者属性mysql用户群属性mysql分组.
$ Chown-R mysql:mysql/path/to/datadir数据库服务器恢复启动更多关于如何实现不同恢复的讨论见第5.1节,“实现恢复操作.