MySQL运用bin-log异库恢复到指定时间点
发布时间：2022-03-29 17:18:31 所属栏目：MySql教程 来源：互联网
导读：MySQL使用bin-log异库恢复到指定时间点： 1、搭建初始化数据库 2、确定日志的位置position 3、备份数据库T0 4、模拟数据库发生变化T1 5、模拟数据库发生变化T2 6、恢复数据库到备份时间点T0 7、模拟数据库恢复到时间点T1 8、模拟数据库恢复到时间点T2 1、安
|
MySQL使用bin-log异库恢复到指定时间点： 1、搭建初始化数据库 2、确定日志的位置position 3、备份数据库T0 4、模拟数据库发生变化T1 5、模拟数据库发生变化T2 6、恢复数据库到备份时间点T0 7、模拟数据库恢复到时间点T1 8、模拟数据库恢复到时间点T2 1、安装数据库，安装xtrabackup a、安装数据库及初始化数据这里就不在重复了，初始化后数据库如下： MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | ty | +--------------------+ 5 rows in set (0.00 sec) MariaDB [(none)]> use ty Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [ty]> show tables; +--------------+ | Tables_in_ty | +--------------+ | APP_GROUPS | | APP_MEMBER | | APP_ORG | | APP_OTAURL | | APP_ROLE | +--------------+ 5 rows in set (0.00 sec) MariaDB [ty]> select count(*) from APP_MEMBER; +----------+ | count(*) | +----------+ | 3 | +----------+ 1 row in set (0.00 sec) MariaDB [ty]> select count(*) from APP_OTAURL; +----------+ | count(*) | +----------+ | 47 | +----------+ 1 row in set (0.00 sec) ---其他表也有数据，但是不多，我们测试时不做任何改变，所以这里就不查询出来了。 b、安装xtrabackup，这里一定要选择和自己数据库版本对应的xtrabackup版本 yum install libaio curl numactl zlib zlib-devel openssl openssl-devel perl cpio expat expat-devel gettext gettext-devel perl-ExtUtils-MakeMaker perl-DBD-MySQL package -y rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm ----libev建议使用RPM安装，否则容易触发Bug。 rpm -ivh percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm 2、确定日志的位置position MariaDB [ty]> show variables like 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ 1 row in set (0.00 sec) MariaDB [ty]> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 312 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) 3、备份数据库T0 [root@mariadbl01 mysqldump]# innobackupex --defaults-file=/etc/my.cnf.d/server.cnf /app/mysqldump --no-lock 171219 09:40:22 innobackupex: Starting the backup operation IMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex prints "completed OK!". 171219 09:40:22 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/app/mysql/3306/mysql.sock' (using password: NO). 171219 09:40:22 version_check Connected to MySQL server 171219 09:40:22 version_check Executing a version check against the server... 171219 09:40:22 version_check Done. 171219 09:40:22 Connecting to MySQL server host: localhost, user: (null), password: not set, port: 0, socket: /app/mysql/3306/mysql.sock Using server version 10.0.30-MariaDB innobackupex version 2.4.4 based on MySQL server 5.7.13 Linux (x86_64) (revision id: df58cf2) xtrabackup: uses posix_fadvise(). xtrabackup: cd to /app/mysql/3306/ xtrabackup: open files limit requested 0, set to 1024 xtrabackup: using the following InnoDB configuration: xtrabackup: innodb_data_home_dir = . xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend xtrabackup: innodb_log_group_home_dir = ./ xtrabackup: innodb_log_files_in_group = 2 xtrabackup: innodb_log_file_size = 104857600 InnoDB: Number of pools: 1 171219 09:40:22 >> log scanned up to (1679012) xtrabackup: Generating a list of tablespaces InnoDB: Allocated tablespace ID 7 for ty/APP_OTAURL, old maximum was 0 171219 09:40:22 [01] Copying ./ibdata1 to /app/mysqldump/2017-12-19_09-40-22/ibdata1 171219 09:40:22 [01] ...done 171219 09:40:22 [01] Copying ./ty/APP_OTAURL.ibd to /app/mysqldump/2017-12-19_09-40-22/ty/APP_OTAURL.ibd 171219 09:40:22 [01] ...done 171219 09:40:22 [01] Copying ./ty/APP_GROUPS.ibd to /app/mysqldump/2017-12-19_09-40-22/ty/APP_GROUPS.ibd 171219 09:40:22 [01] ...done 171219 09:40:22 [01] Copying ./ty/APP_ORG.ibd to /app/mysqldump/2017-12-19_09-40-22/ty/APP_ORG.ibd 171219 09:40:22 [01] ...done 171219 09:40:22 [01] Copying ./ty/APP_ROLE.ibd to /app/mysqldump/2017-12-19_09-40-22/ty/APP_ROLE.ibd 171219 09:40:22 [01] ...done 171219 09:40:22 [01] Copying ./ty/APP_MEMBER.ibd to /app/mysqldump/2017-12-19_09-40-22/ty/APP_MEMBER.ibd 171219 09:40:22 [01] ...done 171219 09:40:22 [01] Copying ./mysql/innodb_index_stats.ibd to /app/mysqldump/2017-12-19_09-40-22/mysql/innodb_index_stats.ibd 171219 09:40:22 [01] ...done 171219 09:40:23 [01] Copying ./mysql/gtid_slave_pos.ibd to /app/mysqldump/2017-12-19_09-40-22/mysql/gtid_slave_pos.ibd 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./mysql/innodb_table_stats.ibd to /app/mysqldump/2017-12-19_09-40-22/mysql/innodb_table_stats.ibd 171219 09:40:23 [01] ...done 171219 09:40:23 >> log scanned up to (1679012) 171219 09:40:23 Starting to backup non-InnoDB tables and files 171219 09:40:23 [01] Copying ./test/db.opt to /app/mysqldump/2017-12-19_09-40-22/test/db.opt 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./ty/APP_ROLE.frm to /app/mysqldump/2017-12-19_09-40-22/ty/APP_ROLE.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./ty/APP_GROUPS.frm to /app/mysqldump/2017-12-19_09-40-22/ty/APP_GROUPS.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./ty/APP_MEMBER.frm to /app/mysqldump/2017-12-19_09-40-22/ty/APP_MEMBER.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./ty/APP_OTAURL.frm to /app/mysqldump/2017-12-19_09-40-22/ty/APP_OTAURL.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./ty/APP_ORG.frm to /app/mysqldump/2017-12-19_09-40-22/ty/APP_ORG.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./ty/db.opt to /app/mysqldump/2017-12-19_09-40-22/ty/db.opt 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/events_waits_summary_by_user_by_event_name.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/events_waits_summary_by_user_by_event_name.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/objects_summary_global_by_type.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/objects_summary_global_by_type.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/events_waits_history_long.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/events_waits_history_long.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/events_waits_summary_by_host_by_event_name.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/events_waits_summary_by_host_by_event_name.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/socket_summary_by_event_name.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/socket_summary_by_event_name.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/table_lock_waits_summary_by_table.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/table_lock_waits_summary_by_table.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/events_statements_summary_by_account_by_event_name.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/events_statements_summary_by_account_by_event_name.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/events_stages_summary_by_host_by_event_name.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/events_stages_summary_by_host_by_event_name.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/socket_instances.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/socket_instances.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/host_cache.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/host_cache.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/events_stages_summary_by_user_by_event_name.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/events_stages_summary_by_user_by_event_name.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/events_stages_summary_by_thread_by_event_name.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/events_stages_summary_by_thread_by_event_name.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/events_stages_history_long.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/events_stages_history_long.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/file_summary_by_instance.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/file_summary_by_instance.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/events_waits_summary_by_thread_by_event_name.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/events_waits_summary_by_thread_by_event_name.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/setup_instruments.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/setup_instruments.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/events_statements_summary_by_thread_by_event_name.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/events_statements_summary_by_thread_by_event_name.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/mutex_instances.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/mutex_instances.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/events_statements_current.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/events_statements_current.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/setup_objects.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/setup_objects.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/events_stages_history.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/events_stages_history.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/accounts.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/accounts.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/events_waits_history.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/events_waits_history.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/events_waits_current.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/events_waits_current.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/table_io_waits_summary_by_table.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/table_io_waits_summary_by_table.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/setup_consumers.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/setup_consumers.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/performance_timers.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/performance_timers.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/session_account_connect_attrs.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/session_account_connect_attrs.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/session_connect_attrs.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/session_connect_attrs.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/events_statements_summary_by_host_by_event_name.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/events_statements_summary_by_host_by_event_name.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/file_summary_by_event_name.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/file_summary_by_event_name.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/setup_actors.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/setup_actors.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/file_instances.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/file_instances.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/events_statements_summary_by_digest.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/events_statements_summary_by_digest.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/hosts.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/hosts.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/events_statements_history_long.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/events_statements_history_long.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/events_waits_summary_by_instance.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/events_waits_summary_by_instance.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/threads.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/threads.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/rwlock_instances.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/rwlock_instances.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/events_statements_summary_global_by_event_name.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/events_statements_summary_global_by_event_name.frm 171219 09:40:23 [01] ...done 171219 09:40:23 [01] Copying ./performance_schema/setup_timers.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/setup_timers.frm 171219 09:40:23 [01] ...done 171219 09:40:24 [01] Copying ./performance_schema/events_stages_current.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/events_stages_current.frm 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./performance_schema/socket_summary_by_instance.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/socket_summary_by_instance.frm 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./performance_schema/events_waits_summary_by_account_by_event_name.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/events_waits_summary_by_account_by_event_name.frm 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./performance_schema/users.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/users.frm 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./performance_schema/cond_instances.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/cond_instances.frm 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./performance_schema/events_waits_summary_global_by_event_name.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/events_waits_summary_global_by_event_name.frm 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./performance_schema/events_stages_summary_by_account_by_event_name.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/events_stages_summary_by_account_by_event_name.frm 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./performance_schema/table_io_waits_summary_by_index_usage.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/table_io_waits_summary_by_index_usage.frm 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./performance_schema/events_statements_history.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/events_statements_history.frm 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./performance_schema/db.opt to /app/mysqldump/2017-12-19_09-40-22/performance_schema/db.opt 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./performance_schema/events_stages_summary_global_by_event_name.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/events_stages_summary_global_by_event_name.frm 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./performance_schema/events_statements_summary_by_user_by_event_name.frm to /app/mysqldump/2017-12-19_09-40-22/performance_schema/events_statements_summary_by_user_by_event_name.frm 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/slow_log.CSM to /app/mysqldump/2017-12-19_09-40-22/mysql/slow_log.CSM 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/time_zone_name.frm to /app/mysqldump/2017-12-19_09-40-22/mysql/time_zone_name.frm 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/time_zone_transition.MYI to /app/mysqldump/2017-12-19_09-40-22/mysql/time_zone_transition.MYI 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/table_stats.MYD to /app/mysqldump/2017-12-19_09-40-22/mysql/table_stats.MYD 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/user.MYD to /app/mysqldump/2017-12-19_09-40-22/mysql/user.MYD 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/time_zone_leap_second.MYI to /app/mysqldump/2017-12-19_09-40-22/mysql/time_zone_leap_second.MYI 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/proxies_priv.MYI to /app/mysqldump/2017-12-19_09-40-22/mysql/proxies_priv.MYI 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/column_stats.MYD to /app/mysqldump/2017-12-19_09-40-22/mysql/column_stats.MYD 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/column_stats.MYI to /app/mysqldump/2017-12-19_09-40-22/mysql/column_stats.MYI 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/procs_priv.MYD to /app/mysqldump/2017-12-19_09-40-22/mysql/procs_priv.MYD 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/tables_priv.MYD to /app/mysqldump/2017-12-19_09-40-22/mysql/tables_priv.MYD 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/host.MYD to /app/mysqldump/2017-12-19_09-40-22/mysql/host.MYD 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/help_category.MYD to /app/mysqldump/2017-12-19_09-40-22/mysql/help_category.MYD 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/time_zone_transition.frm to /app/mysqldump/2017-12-19_09-40-22/mysql/time_zone_transition.frm 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/help_keyword.MYD to /app/mysqldump/2017-12-19_09-40-22/mysql/help_keyword.MYD 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/roles_mapping.frm to /app/mysqldump/2017-12-19_09-40-22/mysql/roles_mapping.frm 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/table_stats.MYI to /app/mysqldump/2017-12-19_09-40-22/mysql/table_stats.MYI 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/plugin.MYI to /app/mysqldump/2017-12-19_09-40-22/mysql/plugin.MYI 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/tables_priv.MYI to /app/mysqldump/2017-12-19_09-40-22/mysql/tables_priv.MYI 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/proc.MYI to /app/mysqldump/2017-12-19_09-40-22/mysql/proc.MYI 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/func.frm to /app/mysqldump/2017-12-19_09-40-22/mysql/func.frm 171219 09:40:24 [01] ...done 171219 09:40:24 >> log scanned up to (1679012) 171219 09:40:24 [01] Copying ./mysql/tables_priv.frm to /app/mysqldump/2017-12-19_09-40-22/mysql/tables_priv.frm 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/procs_priv.frm to /app/mysqldump/2017-12-19_09-40-22/mysql/procs_priv.frm 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/help_keyword.MYI to /app/mysqldump/2017-12-19_09-40-22/mysql/help_keyword.MYI 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/time_zone_transition_type.frm to /app/mysqldump/2017-12-19_09-40-22/mysql/time_zone_transition_type.frm 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/index_stats.MYD to /app/mysqldump/2017-12-19_09-40-22/mysql/index_stats.MYD 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/event.frm to /app/mysqldump/2017-12-19_09-40-22/mysql/event.frm 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/help_topic.MYI to /app/mysqldump/2017-12-19_09-40-22/mysql/help_topic.MYI 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/roles_mapping.MYD to /app/mysqldump/2017-12-19_09-40-22/mysql/roles_mapping.MYD 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/columns_priv.MYD to /app/mysqldump/2017-12-19_09-40-22/mysql/columns_priv.MYD 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/func.MYI to /app/mysqldump/2017-12-19_09-40-22/mysql/func.MYI 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/proxies_priv.MYD to /app/mysqldump/2017-12-19_09-40-22/mysql/proxies_priv.MYD 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/proc.MYD to /app/mysqldump/2017-12-19_09-40-22/mysql/proc.MYD 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/procs_priv.MYI to /app/mysqldump/2017-12-19_09-40-22/mysql/procs_priv.MYI 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/columns_priv.frm to /app/mysqldump/2017-12-19_09-40-22/mysql/columns_priv.frm 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/servers.frm to /app/mysqldump/2017-12-19_09-40-22/mysql/servers.frm 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/time_zone_transition_type.MYI to /app/mysqldump/2017-12-19_09-40-22/mysql/time_zone_transition_type.MYI 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/db.MYI to /app/mysqldump/2017-12-19_09-40-22/mysql/db.MYI 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/gtid_slave_pos.frm to /app/mysqldump/2017-12-19_09-40-22/mysql/gtid_slave_pos.frm 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/plugin.frm to /app/mysqldump/2017-12-19_09-40-22/mysql/plugin.frm 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/time_zone_transition.MYD to /app/mysqldump/2017-12-19_09-40-22/mysql/time_zone_transition.MYD 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/table_stats.frm to /app/mysqldump/2017-12-19_09-40-22/mysql/table_stats.frm 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/time_zone_leap_second.MYD to /app/mysqldump/2017-12-19_09-40-22/mysql/time_zone_leap_second.MYD 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/host.frm to /app/mysqldump/2017-12-19_09-40-22/mysql/host.frm 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/plugin.MYD to /app/mysqldump/2017-12-19_09-40-22/mysql/plugin.MYD 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/roles_mapping.MYI to /app/mysqldump/2017-12-19_09-40-22/mysql/roles_mapping.MYI 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/index_stats.MYI to /app/mysqldump/2017-12-19_09-40-22/mysql/index_stats.MYI 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/help_relation.MYI to /app/mysqldump/2017-12-19_09-40-22/mysql/help_relation.MYI 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/innodb_table_stats.frm to /app/mysqldump/2017-12-19_09-40-22/mysql/innodb_table_stats.frm 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/db.frm to /app/mysqldump/2017-12-19_09-40-22/mysql/db.frm 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/help_relation.frm to /app/mysqldump/2017-12-19_09-40-22/mysql/help_relation.frm 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/innodb_index_stats.frm to /app/mysqldump/2017-12-19_09-40-22/mysql/innodb_index_stats.frm 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/help_category.frm to /app/mysqldump/2017-12-19_09-40-22/mysql/help_category.frm 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/help_relation.MYD to /app/mysqldump/2017-12-19_09-40-22/mysql/help_relation.MYD 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/servers.MYI to /app/mysqldump/2017-12-19_09-40-22/mysql/servers.MYI 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/time_zone_transition_type.MYD to /app/mysqldump/2017-12-19_09-40-22/mysql/time_zone_transition_type.MYD 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/user.MYI to /app/mysqldump/2017-12-19_09-40-22/mysql/user.MYI 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/help_keyword.frm to /app/mysqldump/2017-12-19_09-40-22/mysql/help_keyword.frm 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/time_zone_leap_second.frm to /app/mysqldump/2017-12-19_09-40-22/mysql/time_zone_leap_second.frm 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/general_log.CSV to /app/mysqldump/2017-12-19_09-40-22/mysql/general_log.CSV 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/help_topic.MYD to /app/mysqldump/2017-12-19_09-40-22/mysql/help_topic.MYD 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/host.MYI to /app/mysqldump/2017-12-19_09-40-22/mysql/host.MYI 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/proxies_priv.frm to /app/mysqldump/2017-12-19_09-40-22/mysql/proxies_priv.frm 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/index_stats.frm to /app/mysqldump/2017-12-19_09-40-22/mysql/index_stats.frm 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/proc.frm to /app/mysqldump/2017-12-19_09-40-22/mysql/proc.frm 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/general_log.frm to /app/mysqldump/2017-12-19_09-40-22/mysql/general_log.frm 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/time_zone.MYI to /app/mysqldump/2017-12-19_09-40-22/mysql/time_zone.MYI 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/general_log.CSM to /app/mysqldump/2017-12-19_09-40-22/mysql/general_log.CSM 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/help_topic.frm to /app/mysqldump/2017-12-19_09-40-22/mysql/help_topic.frm 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/slow_log.frm to /app/mysqldump/2017-12-19_09-40-22/mysql/slow_log.frm 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/time_zone.frm to /app/mysqldump/2017-12-19_09-40-22/mysql/time_zone.frm 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/time_zone.MYD to /app/mysqldump/2017-12-19_09-40-22/mysql/time_zone.MYD 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/func.MYD to /app/mysqldump/2017-12-19_09-40-22/mysql/func.MYD 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/columns_priv.MYI to /app/mysqldump/2017-12-19_09-40-22/mysql/columns_priv.MYI 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/column_stats.frm to /app/mysqldump/2017-12-19_09-40-22/mysql/column_stats.frm 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/time_zone_name.MYD to /app/mysqldump/2017-12-19_09-40-22/mysql/time_zone_name.MYD 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/user.frm to /app/mysqldump/2017-12-19_09-40-22/mysql/user.frm 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/slow_log.CSV to /app/mysqldump/2017-12-19_09-40-22/mysql/slow_log.CSV 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/time_zone_name.MYI to /app/mysqldump/2017-12-19_09-40-22/mysql/time_zone_name.MYI 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/db.MYD to /app/mysqldump/2017-12-19_09-40-22/mysql/db.MYD 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/servers.MYD to /app/mysqldump/2017-12-19_09-40-22/mysql/servers.MYD 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/event.MYI to /app/mysqldump/2017-12-19_09-40-22/mysql/event.MYI 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/help_category.MYI to /app/mysqldump/2017-12-19_09-40-22/mysql/help_category.MYI 171219 09:40:24 [01] ...done 171219 09:40:24 [01] Copying ./mysql/event.MYD to /app/mysqldump/2017-12-19_09-40-22/mysql/event.MYD 171219 09:40:24 [01] ...done 171219 09:40:24 Finished backing up non-InnoDB tables and files 171219 09:40:24 [00] Writing xtrabackup_binlog_info 171219 09:40:24 [00] ...done 171219 09:40:24 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS... xtrabackup: The latest check point (for incremental): '1679012' xtrabackup: Stopping log copying thread. .171219 09:40:24 >> log scanned up to (1679012) 171219 09:40:24 Backup created in directory '/app/mysqldump/2017-12-19_09-40-22' MySQL binlog position: filename 'mysql-bin.000001', position '312', GTID of the last change '' 171219 09:40:24 [00] Writing backup-my.cnf 171219 09:40:24 [00] ...done 171219 09:40:24 [00] Writing xtrabackup_info 171219 09:40:24 [00] ...done xtrabackup: Transaction log of lsn (1679012) to (1679012) was copied. 171219 09:40:25 completed OK! [root@mariadbl01 mysqldump]# ls -lrt drwxr-x--- 6 root root 4096 Dec 19 09:40 2017-12-19_09-40-22 [root@mariadbl01 mysqldump]# du -sh 2017-12-19_09-40-22 14M 2017-12-19_09-40-22 4、模拟数据库发生变化T1 MariaDB [ty]> create table t(tid int,tdept int,tname varchar(100),ttime datetime); Query OK, 0 rows affected (0.07 sec) MariaDB [ty]> insert into t values(1,10,'binlog',now()); Query OK, 1 row affected (0.00 sec) MariaDB [ty]> insert into t values(2,20,'MySQL',now()); Query OK, 1 row affected (0.01 sec) MariaDB [ty]> insert into t values(11,110,'TangYun',now()); Query OK, 1 row affected (0.00 sec) MariaDB [ty]> insert into t values(12,100,'tangyun',now()); Query OK, 1 row affected (0.00 sec) MariaDB [ty]> insert into t values(13,100,'汤云',now()); Query OK, 1 row affected, 1 warning (0.00 sec) MariaDB [ty]> commit; Query OK, 0 rows affected (0.00 sec) MariaDB [ty]> show tables; +--------------+ | Tables_in_ty | +--------------+ | APP_GROUPS | | APP_MEMBER | | APP_ORG | | APP_OTAURL | | APP_ROLE | | t | +--------------+ 6 rows in set (0.00 sec) MariaDB [ty]> select count(*) from APP_OTAURL; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) MariaDB [ty]> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 5106 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) 5、模拟数据库发生变化T2 MariaDB [ty]> source /app/mysqldump/exs_3.sql MariaDB [ty]> show tables; +--------------+ | Tables_in_ty | +--------------+ | APP_GROUPS | | APP_MEMBER | | APP_ORG | | APP_OTAURL | | APP_ROLE | | exs_order | | exs_product | | exs_report | | t | +--------------+ 9 rows in set (0.00 sec) MariaDB [ty]> select count(*) from exs_order; +----------+ | count(*) | +----------+ | 566 | +----------+ 1 row in set (0.00 sec) MariaDB [ty]> select count(*) from exs_product; +----------+ | count(*) | +----------+ | 13 | +----------+ 1 row in set (0.00 sec) MariaDB [ty]> select count(*) from exs_report; +----------+ | count(*) | +----------+ | 513 | +----------+ 1 row in set (0.00 sec) MariaDB [ty]> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 240852 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) 6、模拟数据库发生变化T3 MariaDB [ty]> flush logs; Query OK, 0 rows affected (0.03 sec) MariaDB [ty]> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000002 | 365 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) create database if not exists ty_db default charset utf8 collate utf8_general_ci; MariaDB [ty]> use ty_db Database changed MariaDB [ty_db]> source /app/mysqldump/exs_3.sql MariaDB [ty_db]> show tables; +-----------------+ | Tables_in_ty_db | +-----------------+ | exs_order | | exs_product | | exs_report | +-----------------+ 3 rows in set (0.00 sec) MariaDB [ty_db]> select count(*) from exs_order; +----------+ | count(*) | +----------+ | 566 | +----------+ 1 row in set (0.00 sec) MariaDB [ty_db]> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000002 | 472084 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) ---如果再source /app/mysqldump/exs_3.sql，虽然数据没有发生变化，但是Position已经发生了变化。 MariaDB [ty_db]> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000002 | 707875 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) MariaDB [ty_db]> select count(*) from exs_order; +----------+ | count(*) | +----------+ | 566 | +----------+ 1 row in set (0.00 sec) MariaDB [ty_db]> delete from exs_order where id<=30; Query OK, 30 rows affected (0.01 sec) MariaDB [ty_db]> commit; Query OK, 0 rows affected (0.00 sec) MariaDB [ty_db]> select count(*) from exs_order; +----------+ | count(*) | +----------+ | 536 | +----------+ 1 row in set (0.00 sec) MariaDB [ty_db]> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000002 | 714281 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) MariaDB [ty_db]> truncate table exs_order; Query OK, 0 rows affected (0.41 sec) MariaDB [ty_db]> select count(*) from exs_order; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) MariaDB [ty_db]> source /app/mysqldump/exs_3.sql MariaDB [ty_db]> select count(*) from exs_order; +----------+ | count(*) | +----------+ | 566 | +----------+ 1 row in set (0.00 sec) MariaDB [ty_db]> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000002 | 950198 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) MariaDB [ty_db]> truncate table exs_order; Query OK, 0 rows affected (0.07 sec) MariaDB [ty_db]> select count(*) from exs_order; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) MariaDB [ty_db]> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000002 | 950324 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) 7、恢复数据库到备份时间点T0 [root@mariadbl01 mysqldump]# service mysql stop Shutting down MySQL... SUCCESS! [root@mariadbl01 mysqldump]# cd /app/mysql [root@mariadbl01 mysql]# mv 3306/ 3306_bk [root@mariadbl01 mysql]# mkdir 3306 [root@mariadbl01 mysql]# chown -R mysql.mysql 3306 [root@mariadbl01 mysql]# mv binlog/ binlog_bk [root@mariadbl01 mysql]# mkdir binlog [root@mariadbl01 mysql]# chown -R mysql:mysql binlog [root@mariadbl01 mysql]# vi /etc/my.cnf.d/server.cnf [mysqld] datadir=/app/mysql/3306/ # this is only for embedded server [root@mariadbl01 mysql]# innobackupex --defaults-file=/etc/my.cnf.d/server.cnf /app/mysqldump/2017-12-19_09-40-22/ --apply-log 171221 15:26:13 innobackupex: Starting the apply-log operation IMPORTANT: Please check that the apply-log run completes successfully. At the end of a successful apply-log run innobackupex prints "completed OK!". innobackupex version 2.4.4 based on MySQL server 5.7.13 Linux (x86_64) (revision id: df58cf2) xtrabackup: cd to /app/mysqldump/2017-12-19_09-40-22 xtrabackup: This target seems to be not prepared yet. InnoDB: Number of pools: 1 xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(1679012) xtrabackup: using the following InnoDB configuration for recovery: xtrabackup: innodb_data_home_dir = . xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend xtrabackup: innodb_log_group_home_dir = . xtrabackup: innodb_log_files_in_group = 1 xtrabackup: innodb_log_file_size = 8388608 xtrabackup: using the following InnoDB configuration for recovery: xtrabackup: innodb_data_home_dir = . xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend xtrabackup: innodb_log_group_home_dir = . xtrabackup: innodb_log_files_in_group = 1 xtrabackup: innodb_log_file_size = 8388608 xtrabackup: Starting InnoDB instance for recovery. xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter) InnoDB: PUNCH HOLE support available InnoDB: Mutexes and rw_locks use GCC atomic builtins InnoDB: Uses event mutexes InnoDB: GCC builtin __sync_synchronize() is used for memory barrier InnoDB: Compressed tables use zlib 1.2.3 InnoDB: Number of pools: 1 InnoDB: Using CPU crc32 instructions InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M InnoDB: Completed initialization of buffer pool InnoDB: page_cleaner coordinator priority: -20 InnoDB: Highest supported file format is Barracuda. InnoDB: The log sequence number 1679002 in the system tablespace does not match the log sequence number 1679012 in the ib_logfiles! InnoDB: Database was not shutdown normally! InnoDB: Starting crash recovery. InnoDB: Doing recovery: scanned up to log sequence number 1679012 (0%) InnoDB: xtrabackup: Last MySQL binlog file position 8850, file name /app/mysql/binlog/mysql-bin.000002 InnoDB: Creating shared tablespace for temporary tables InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... InnoDB: File './ibtmp1' size is now 12 MB. InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active. InnoDB: 32 non-redo rollback segment(s) are active. InnoDB: Waiting for purge to start InnoDB: 5.7.13 started; log sequence number 1679012 InnoDB: xtrabackup: Last MySQL binlog file position 8850, file name /app/mysql/binlog/mysql-bin.000002 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 1679031 InnoDB: Number of pools: 1 xtrabackup: using the following InnoDB configuration for recovery: xtrabackup: innodb_data_home_dir = . xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend xtrabackup: innodb_log_group_home_dir = . xtrabackup: innodb_log_files_in_group = 2 xtrabackup: innodb_log_file_size = 104857600 InnoDB: PUNCH HOLE support available InnoDB: Mutexes and rw_locks use GCC atomic builtins InnoDB: Uses event mutexes InnoDB: GCC builtin __sync_synchronize() is used for memory barrier InnoDB: Compressed tables use zlib 1.2.3 InnoDB: Number of pools: 1 InnoDB: Using CPU crc32 instructions InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M InnoDB: Completed initialization of buffer pool InnoDB: page_cleaner coordinator priority: -20 InnoDB: Setting log file ./ib_logfile101 size to 100 MB InnoDB: Progress in MB: 100 InnoDB: Setting log file ./ib_logfile1 size to 100 MB InnoDB: Progress in MB: 100 InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0 InnoDB: New log files created, LSN=1679031 InnoDB: Highest supported file format is Barracuda. InnoDB: Log scan progressed past the checkpoint lsn 1679372 InnoDB: Doing recovery: scanned up to log sequence number 1679381 (0%) InnoDB: Doing recovery: scanned up to log sequence number 1679381 (0%) InnoDB: Database was not shutdown normally! InnoDB: Starting crash recovery. InnoDB: xtrabackup: Last MySQL binlog file position 8850, file name /app/mysql/binlog/mysql-bin.000002 InnoDB: Removed temporary tablespace data file: "ibtmp1" InnoDB: Creating shared tablespace for temporary tables InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... InnoDB: File './ibtmp1' size is now 12 MB. InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active. InnoDB: 32 non-redo rollback segment(s) are active. InnoDB: Waiting for purge to start InnoDB: 5.7.13 started; log sequence number 1679381 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 1679400 171221 15:26:18 completed OK! ---执行恢复到T0时间点 [root@mariadbl01 mysql]# innobackupex --defaults-file=/etc/my.cnf.d/server.cnf /app/mysqldump/2017-12-19_09-40-22/ --copy-back 171221 15:27:10 innobackupex: Starting the copy-back operation IMPORTANT: Please check that the copy-back run completes successfully. At the end of a successful copy-back run innobackupex prints "completed OK!". innobackupex version 2.4.4 based on MySQL server 5.7.13 Linux (x86_64) (revision id: df58cf2) 171221 15:27:10 [01] Copying ib_logfile0 to /app/mysql/3306/ib_logfile0 171221 15:27:11 [01] ...done 171221 15:27:12 [01] Copying ib_logfile1 to /app/mysql/3306/ib_logfile1 171221 15:27:12 [01] ...done 171221 15:27:13 [01] Copying ibdata1 to /app/mysql/3306/ibdata1 171221 15:27:13 [01] ...done 171221 15:27:14 [01] Copying ./test/db.opt to /app/mysql/3306/test/db.opt 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./ty/APP_OTAURL.ibd to /app/mysql/3306/ty/APP_OTAURL.ibd 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./ty/APP_GROUPS.ibd to /app/mysql/3306/ty/APP_GROUPS.ibd 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./ty/APP_ROLE.frm to /app/mysql/3306/ty/APP_ROLE.frm 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./ty/APP_GROUPS.frm to /app/mysql/3306/ty/APP_GROUPS.frm 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./ty/APP_MEMBER.frm to /app/mysql/3306/ty/APP_MEMBER.frm 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./ty/APP_OTAURL.frm to /app/mysql/3306/ty/APP_OTAURL.frm 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./ty/APP_ORG.ibd to /app/mysql/3306/ty/APP_ORG.ibd 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./ty/APP_ROLE.ibd to /app/mysql/3306/ty/APP_ROLE.ibd 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./ty/APP_ORG.frm to /app/mysql/3306/ty/APP_ORG.frm 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./ty/APP_MEMBER.ibd to /app/mysql/3306/ty/APP_MEMBER.ibd 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./ty/db.opt to /app/mysql/3306/ty/db.opt 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./xtrabackup_binlog_pos_innodb to /app/mysql/3306/xtrabackup_binlog_pos_innodb 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./xtrabackup_info to /app/mysql/3306/xtrabackup_info 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./performance_schema/events_waits_summary_by_user_by_event_name.frm to /app/mysql/3306/performance_schema/events_waits_summary_by_user_by_event_name.frm 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./performance_schema/objects_summary_global_by_type.frm to /app/mysql/3306/performance_schema/objects_summary_global_by_type.frm 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./performance_schema/events_waits_history_long.frm to /app/mysql/3306/performance_schema/events_waits_history_long.frm 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./performance_schema/events_waits_summary_by_host_by_event_name.frm to /app/mysql/3306/performance_schema/events_waits_summary_by_host_by_event_name.frm 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./performance_schema/socket_summary_by_event_name.frm to /app/mysql/3306/performance_schema/socket_summary_by_event_name.frm 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./performance_schema/table_lock_waits_summary_by_table.frm to /app/mysql/3306/performance_schema/table_lock_waits_summary_by_table.frm 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./performance_schema/events_statements_summary_by_account_by_event_name.frm to /app/mysql/3306/performance_schema/events_statements_summary_by_account_by_event_name.frm 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./performance_schema/events_stages_summary_by_host_by_event_name.frm to /app/mysql/3306/performance_schema/events_stages_summary_by_host_by_event_name.frm 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./performance_schema/socket_instances.frm to /app/mysql/3306/performance_schema/socket_instances.frm 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./performance_schema/host_cache.frm to /app/mysql/3306/performance_schema/host_cache.frm 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./performance_schema/events_stages_summary_by_user_by_event_name.frm to /app/mysql/3306/performance_schema/events_stages_summary_by_user_by_event_name.frm 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./performance_schema/events_stages_summary_by_thread_by_event_name.frm to /app/mysql/3306/performance_schema/events_stages_summary_by_thread_by_event_name.frm 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./performance_schema/events_stages_history_long.frm to /app/mysql/3306/performance_schema/events_stages_history_long.frm 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./performance_schema/file_summary_by_instance.frm to /app/mysql/3306/performance_schema/file_summary_by_instance.frm 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./performance_schema/events_waits_summary_by_thread_by_event_name.frm to /app/mysql/3306/performance_schema/events_waits_summary_by_thread_by_event_name.frm 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./performance_schema/setup_instruments.frm to /app/mysql/3306/performance_schema/setup_instruments.frm 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./performance_schema/events_statements_summary_by_thread_by_event_name.frm to /app/mysql/3306/performance_schema/events_statements_summary_by_thread_by_event_name.frm 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./performance_schema/mutex_instances.frm to /app/mysql/3306/performance_schema/mutex_instances.frm 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./performance_schema/events_statements_current.frm to /app/mysql/3306/performance_schema/events_statements_current.frm 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./performance_schema/setup_objects.frm to /app/mysql/3306/performance_schema/setup_objects.frm 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./performance_schema/events_stages_history.frm to /app/mysql/3306/performance_schema/events_stages_history.frm 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./performance_schema/accounts.frm to /app/mysql/3306/performance_schema/accounts.frm 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./performance_schema/events_waits_history.frm to /app/mysql/3306/performance_schema/events_waits_history.frm 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./performance_schema/events_waits_current.frm to /app/mysql/3306/performance_schema/events_waits_current.frm 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./performance_schema/table_io_waits_summary_by_table.frm to /app/mysql/3306/performance_schema/table_io_waits_summary_by_table.frm 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./performance_schema/setup_consumers.frm to /app/mysql/3306/performance_schema/setup_consumers.frm 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./performance_schema/performance_timers.frm to /app/mysql/3306/performance_schema/performance_timers.frm 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./performance_schema/session_account_connect_attrs.frm to /app/mysql/3306/performance_schema/session_account_connect_attrs.frm 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./performance_schema/session_connect_attrs.frm to /app/mysql/3306/performance_schema/session_connect_attrs.frm 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./performance_schema/events_statements_summary_by_host_by_event_name.frm to /app/mysql/3306/performance_schema/events_statements_summary_by_host_by_event_name.frm 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./performance_schema/file_summary_by_event_name.frm to /app/mysql/3306/performance_schema/file_summary_by_event_name.frm 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./performance_schema/setup_actors.frm to /app/mysql/3306/performance_schema/setup_actors.frm 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./performance_schema/file_instances.frm to /app/mysql/3306/performance_schema/file_instances.frm 171221 15:27:14 [01] ...done 171221 15:27:14 [01] Copying ./performance_schema/events_statements_summary_by_digest.frm to /app/mysql/3306/performance_schema/events_statements_summary_by_digest.frm 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./performance_schema/hosts.frm to /app/mysql/3306/performance_schema/hosts.frm 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./performance_schema/events_statements_history_long.frm to /app/mysql/3306/performance_schema/events_statements_history_long.frm 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./performance_schema/events_waits_summary_by_instance.frm to /app/mysql/3306/performance_schema/events_waits_summary_by_instance.frm 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./performance_schema/threads.frm to /app/mysql/3306/performance_schema/threads.frm 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./performance_schema/rwlock_instances.frm to /app/mysql/3306/performance_schema/rwlock_instances.frm 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./performance_schema/events_statements_summary_global_by_event_name.frm to /app/mysql/3306/performance_schema/events_statements_summary_global_by_event_name.frm 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./performance_schema/setup_timers.frm to /app/mysql/3306/performance_schema/setup_timers.frm 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./performance_schema/events_stages_current.frm to /app/mysql/3306/performance_schema/events_stages_current.frm 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./performance_schema/socket_summary_by_instance.frm to /app/mysql/3306/performance_schema/socket_summary_by_instance.frm 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./performance_schema/events_waits_summary_by_account_by_event_name.frm to /app/mysql/3306/performance_schema/events_waits_summary_by_account_by_event_name.frm 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./performance_schema/users.frm to /app/mysql/3306/performance_schema/users.frm 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./performance_schema/cond_instances.frm to /app/mysql/3306/performance_schema/cond_instances.frm 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./performance_schema/events_waits_summary_global_by_event_name.frm to /app/mysql/3306/performance_schema/events_waits_summary_global_by_event_name.frm 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./performance_schema/events_stages_summary_by_account_by_event_name.frm to /app/mysql/3306/performance_schema/events_stages_summary_by_account_by_event_name.frm 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./performance_schema/table_io_waits_summary_by_index_usage.frm to /app/mysql/3306/performance_schema/table_io_waits_summary_by_index_usage.frm 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./performance_schema/events_statements_history.frm to /app/mysql/3306/performance_schema/events_statements_history.frm 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./performance_schema/db.opt to /app/mysql/3306/performance_schema/db.opt 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./performance_schema/events_stages_summary_global_by_event_name.frm to /app/mysql/3306/performance_schema/events_stages_summary_global_by_event_name.frm 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./performance_schema/events_statements_summary_by_user_by_event_name.frm to /app/mysql/3306/performance_schema/events_statements_summary_by_user_by_event_name.frm 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./mysql/slow_log.CSM to /app/mysql/3306/mysql/slow_log.CSM 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./mysql/time_zone_name.frm to /app/mysql/3306/mysql/time_zone_name.frm 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./mysql/time_zone_transition.MYI to /app/mysql/3306/mysql/time_zone_transition.MYI 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./mysql/table_stats.MYD to /app/mysql/3306/mysql/table_stats.MYD 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./mysql/user.MYD to /app/mysql/3306/mysql/user.MYD 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./mysql/time_zone_leap_second.MYI to /app/mysql/3306/mysql/time_zone_leap_second.MYI 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./mysql/proxies_priv.MYI to /app/mysql/3306/mysql/proxies_priv.MYI 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./mysql/column_stats.MYD to /app/mysql/3306/mysql/column_stats.MYD 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./mysql/column_stats.MYI to /app/mysql/3306/mysql/column_stats.MYI 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./mysql/procs_priv.MYD to /app/mysql/3306/mysql/procs_priv.MYD 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./mysql/tables_priv.MYD to /app/mysql/3306/mysql/tables_priv.MYD 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./mysql/host.MYD to /app/mysql/3306/mysql/host.MYD 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./mysql/innodb_index_stats.ibd to /app/mysql/3306/mysql/innodb_index_stats.ibd 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./mysql/help_category.MYD to /app/mysql/3306/mysql/help_category.MYD 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./mysql/time_zone_transition.frm to /app/mysql/3306/mysql/time_zone_transition.frm 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./mysql/help_keyword.MYD to /app/mysql/3306/mysql/help_keyword.MYD 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./mysql/roles_mapping.frm to /app/mysql/3306/mysql/roles_mapping.frm 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./mysql/table_stats.MYI to /app/mysql/3306/mysql/table_stats.MYI 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./mysql/plugin.MYI to /app/mysql/3306/mysql/plugin.MYI 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./mysql/tables_priv.MYI to /app/mysql/3306/mysql/tables_priv.MYI 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./mysql/gtid_slave_pos.ibd to /app/mysql/3306/mysql/gtid_slave_pos.ibd 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./mysql/proc.MYI to /app/mysql/3306/mysql/proc.MYI 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./mysql/func.frm to /app/mysql/3306/mysql/func.frm 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./mysql/tables_priv.frm to /app/mysql/3306/mysql/tables_priv.frm 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./mysql/procs_priv.frm to /app/mysql/3306/mysql/procs_priv.frm 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./mysql/help_keyword.MYI to /app/mysql/3306/mysql/help_keyword.MYI 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./mysql/time_zone_transition_type.frm to /app/mysql/3306/mysql/time_zone_transition_type.frm 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./mysql/index_stats.MYD to /app/mysql/3306/mysql/index_stats.MYD 171221 15:27:15 [01] ...done 171221 15:27:15 [01] Copying ./mysql/event.frm to /app/mysql/3306/mysql/event.frm 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/help_topic.MYI to /app/mysql/3306/mysql/help_topic.MYI 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/roles_mapping.MYD to /app/mysql/3306/mysql/roles_mapping.MYD 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/columns_priv.MYD to /app/mysql/3306/mysql/columns_priv.MYD 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/func.MYI to /app/mysql/3306/mysql/func.MYI 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/proxies_priv.MYD to /app/mysql/3306/mysql/proxies_priv.MYD 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/proc.MYD to /app/mysql/3306/mysql/proc.MYD 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/procs_priv.MYI to /app/mysql/3306/mysql/procs_priv.MYI 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/columns_priv.frm to /app/mysql/3306/mysql/columns_priv.frm 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/servers.frm to /app/mysql/3306/mysql/servers.frm 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/time_zone_transition_type.MYI to /app/mysql/3306/mysql/time_zone_transition_type.MYI 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/db.MYI to /app/mysql/3306/mysql/db.MYI 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/gtid_slave_pos.frm to /app/mysql/3306/mysql/gtid_slave_pos.frm 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/plugin.frm to /app/mysql/3306/mysql/plugin.frm 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/time_zone_transition.MYD to /app/mysql/3306/mysql/time_zone_transition.MYD 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/table_stats.frm to /app/mysql/3306/mysql/table_stats.frm 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/time_zone_leap_second.MYD to /app/mysql/3306/mysql/time_zone_leap_second.MYD 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/host.frm to /app/mysql/3306/mysql/host.frm 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/plugin.MYD to /app/mysql/3306/mysql/plugin.MYD 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/roles_mapping.MYI to /app/mysql/3306/mysql/roles_mapping.MYI 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/index_stats.MYI to /app/mysql/3306/mysql/index_stats.MYI 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/help_relation.MYI to /app/mysql/3306/mysql/help_relation.MYI 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/innodb_table_stats.frm to /app/mysql/3306/mysql/innodb_table_stats.frm 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/db.frm to /app/mysql/3306/mysql/db.frm 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/help_relation.frm to /app/mysql/3306/mysql/help_relation.frm 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/innodb_index_stats.frm to /app/mysql/3306/mysql/innodb_index_stats.frm 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/help_category.frm to /app/mysql/3306/mysql/help_category.frm 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/help_relation.MYD to /app/mysql/3306/mysql/help_relation.MYD 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/servers.MYI to /app/mysql/3306/mysql/servers.MYI 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/time_zone_transition_type.MYD to /app/mysql/3306/mysql/time_zone_transition_type.MYD 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/user.MYI to /app/mysql/3306/mysql/user.MYI 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/help_keyword.frm to /app/mysql/3306/mysql/help_keyword.frm 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/time_zone_leap_second.frm to /app/mysql/3306/mysql/time_zone_leap_second.frm 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/general_log.CSV to /app/mysql/3306/mysql/general_log.CSV 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/help_topic.MYD to /app/mysql/3306/mysql/help_topic.MYD 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/host.MYI to /app/mysql/3306/mysql/host.MYI 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/proxies_priv.frm to /app/mysql/3306/mysql/proxies_priv.frm 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/index_stats.frm to /app/mysql/3306/mysql/index_stats.frm 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/proc.frm to /app/mysql/3306/mysql/proc.frm 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/general_log.frm to /app/mysql/3306/mysql/general_log.frm 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/innodb_table_stats.ibd to /app/mysql/3306/mysql/innodb_table_stats.ibd 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/time_zone.MYI to /app/mysql/3306/mysql/time_zone.MYI 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/general_log.CSM to /app/mysql/3306/mysql/general_log.CSM 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/help_topic.frm to /app/mysql/3306/mysql/help_topic.frm 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/slow_log.frm to /app/mysql/3306/mysql/slow_log.frm 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/time_zone.frm to /app/mysql/3306/mysql/time_zone.frm 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/time_zone.MYD to /app/mysql/3306/mysql/time_zone.MYD 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/func.MYD to /app/mysql/3306/mysql/func.MYD 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/columns_priv.MYI to /app/mysql/3306/mysql/columns_priv.MYI 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/column_stats.frm to /app/mysql/3306/mysql/column_stats.frm 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/time_zone_name.MYD to /app/mysql/3306/mysql/time_zone_name.MYD 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/user.frm to /app/mysql/3306/mysql/user.frm 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/slow_log.CSV to /app/mysql/3306/mysql/slow_log.CSV 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/time_zone_name.MYI to /app/mysql/3306/mysql/time_zone_name.MYI 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/db.MYD to /app/mysql/3306/mysql/db.MYD 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/servers.MYD to /app/mysql/3306/mysql/servers.MYD 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/event.MYI to /app/mysql/3306/mysql/event.MYI 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/help_category.MYI to /app/mysql/3306/mysql/help_category.MYI 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./mysql/event.MYD to /app/mysql/3306/mysql/event.MYD 171221 15:27:16 [01] ...done 171221 15:27:16 [01] Copying ./ibtmp1 to /app/mysql/3306/ibtmp1 171221 15:27:16 [01] ...done 171221 15:27:17 completed OK! [root@mariadbl01 mysql]# cd 3306 [root@mariadbl01 3306]# ls -lrt total 229640 -rw-r----- 1 root root 104857600 Dec 21 15:27 ib_logfile0 -rw-r----- 1 root root 104857600 Dec 21 15:27 ib_logfile1 -rw-r----- 1 root root 12582912 Dec 21 15:27 ibdata1 -rw-r----- 1 root root 536 Dec 21 15:27 xtrabackup_info -rw-r----- 1 root root 40 Dec 21 15:27 xtrabackup_binlog_pos_innodb drwxr-x--- 2 root root 4096 Dec 21 15:27 ty drwxr-x--- 2 root root 4096 Dec 21 15:27 test drwxr-x--- 2 root root 4096 Dec 21 15:27 performance_schema drwxr-x--- 2 root root 4096 Dec 21 15:27 mysql -rw-r----- 1 root root 12582912 Dec 21 15:27 ibtmp1 [root@mariadbl01 3306]# cd .. [root@mariadbl01 mysql]# chown -R mysql:mysql 3306 [root@mariadbl01 mysql]# service mysql start Starting MySQL.171221 15:28:40 mysqld_safe Adding '/usr/lib64/libjemalloc.so.1' to LD_PRELOAD for mysqld 171221 15:28:40 mysqld_safe Logging to '/app/mysql/mysql.err'. 171221 15:28:40 mysqld_safe Starting mysqld daemon with databases from /app/mysql/3306/ SUCCESS! MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | ty | +--------------------+ 5 rows in set (0.00 sec) MariaDB [(none)]> use ty MariaDB [ty]> show tables; +--------------+ | Tables_in_ty | +--------------+ | APP_GROUPS | | APP_MEMBER | | APP_ORG | | APP_OTAURL | | APP_ROLE | +--------------+ 5 rows in set (0.00 sec) MariaDB [ty]> select count(*) from APP_MEMBER; +----------+ | count(*) | +----------+ | 3 | +----------+ 1 row in set (0.00 sec) MariaDB [ty]> select count(*) from APP_OTAURL; +----------+ | count(*) | +----------+ | 47 | +----------+ 1 row in set (0.00 sec) ------到这里，可以看到数据库已经恢复到时间点T0 8、模拟数据库恢复到时间点T1 ---找到数据库备份时的binlog的position # cd 2017-12-19_09-40-22 # cat xtrabackup_binlog_info mysql-bin.000001 312 ----将日志应用到T1时间点的位置 MariaDB [ty]> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 5106 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) # /usr/bin/mysqlbinlog --no-defaults --start-position=312 --stop-position=5106 /app/mysql/binlog_bk/mysql-bin.000001|/usr/bin/mysql MariaDB [ty]> show tables; +--------------+ | Tables_in_ty | +--------------+ | APP_GROUPS | | APP_MEMBER | | APP_ORG | | APP_OTAURL | | APP_ROLE | | t | +--------------+ 6 rows in set (0.00 sec) MariaDB [ty]> select * from t; +------+-------+---------+---------------------+ | tid | tdept | tname | ttime | +------+-------+---------+---------------------+ | 1 | 10 | binlog | 2017-12-19 10:01:11 | | 2 | 20 | MySQL | 2017-12-19 10:01:17 | | 11 | 110 | TangYun | 2017-12-19 10:01:17 | | 12 | 100 | tangyun | 2017-12-19 10:01:17 | | 13 | 100 | | 2017-12-19 10:01:18 | +------+-------+---------+---------------------+ 5 rows in set (0.00 sec) MariaDB [ty]> select count(*) from APP_OTAURL; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) ----可以看到，这个时候数据已经恢复到时间点T1。 9、模拟数据库恢复到时间点T2 # /usr/bin/mysqlbinlog --no-defaults --start-position=5106 --stop-position=240852 /app/mysql/binlog_bk/mysql-bin.000001|/usr/bin/mysql MariaDB [ty]> show tables; +--------------+ | Tables_in_ty | +--------------+ | APP_GROUPS | | APP_MEMBER | | APP_ORG | | APP_OTAURL | | APP_ROLE | | exs_order | | exs_product | | exs_report | | t | +--------------+ 9 rows in set (0.00 sec) MariaDB [ty]> select count(*) from exs_order; +----------+ | count(*) | +----------+ | 566 | +----------+ 1 row in set (0.00 sec) MariaDB [ty]> select count(*) from exs_product; +----------+ | count(*) | +----------+ | 13 | +----------+ 1 row in set (0.00 sec) MariaDB [ty]> select count(*) from exs_report; +----------+ | count(*) | +----------+ | 513 | +----------+ 1 row in set (0.00 sec) ----可以看到数据库已经恢复到时间点T2 10、模拟数据库恢复到时间点T3 # /usr/bin/mysqlbinlog --no-defaults --start-position=365 /app/mysql/binlog_bk/mysql-bin.000002|/usr/bin/mysql MariaDB [ty]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | ty | | ty_db | +--------------------+ 6 rows in set (0.00 sec) MariaDB [ty_db]> show tables; +-----------------+ | Tables_in_ty_db | +-----------------+ | exs_order | | exs_product | | exs_report | +-----------------+ 3 rows in set (0.00 sec) MariaDB [ty_db]> select count(*) from exs_order; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) -----到此，数据库已经恢复到当前时间点。 （编辑：威海站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
