重庆分公司
重庆分公司，新征程启航
为企业提供网站建设、域名注册、服务器等服务
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 /ap
当前名称：MySQL使用bin-log异库恢复到指定时间点
文章来源：http://cqcxhl.com/article/jeggps.html
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 /ap
当前名称：MySQL使用bin-log异库恢复到指定时间点
文章来源：http://cqcxhl.com/article/jeggps.html