Shulou(Shulou.com)06/01 Report--

1. Set up the initialization database

2. Determine the location of the log position

3. Backup database T0

4. Analog database changes T1

5. Simulation database changes T2

6. Restore the database to the backup point T0

7. Restore the simulated database to point-in-time T1

8. Restore the simulated database to point-in-time T2

1. Install the database and install xtrabackup

A. The installation database and initialization data are not repeated here. After initialization, the database is as follows:

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)

-other tables also have data, but not much, we do not make any changes when testing, so we will not query it here.

B. To install xtrabackup, be sure to select the xtrabackup version corresponding to your database version.

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 is recommended to use RPM installation, otherwise it is easy to trigger Bug.

Rpm-ivh percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

2. Determine the location of the log 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. Backup database 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-1909-40-22/ibdata1

171219 09:40:22 [01]... done

171219 09:40:22 [01] Copying. / ty/APP_OTAURL.ibd to / app/mysqldump/2017-12-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1909-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-1911 09-40-22

[root@mariadbl01 mysqldump] # du-sh 2017-12-1909-40-22

14m 2017-12-1909-40-22

4. Analog database changes T1

MariaDB [ty] > create table t (tid int,tdept int,tname varchar, ttime datetime)

Query OK, 0 rows affected (0.07 sec)

MariaDB [ty] > insert into t values (1, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 2, 2, 2, 2, 2, 4, 2, 2, 2, 4, 2, 4, 2, 2, 4, 2, 4, 2, 4, 2, 4, 2, 4, 2, 4, 4, 4, 4, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6

Query OK, 1 row affected (0.00 sec)

MariaDB [ty] > insert into t values (2meme 20penny MySQL now ())

Query OK, 1 row affected (0.01sec)

MariaDB [ty] > insert into t values (11pc110pr. Tangyunyunju now ())

Query OK, 1 row affected (0.00 sec)

MariaDB [ty] > insert into t values (12 ~ (100))

Query OK, 1 row affected (0.00 sec)

MariaDB [ty] > insert into t values (13meme 100pr 'Tang Yun', 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. Simulation database changes 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. Analog database changes 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)

-if you source / app/mysqldump/exs_3.sql, although the data has not changed, but the Position has changed.

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 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. Restore the database to the backup point 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-1909-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-1909-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

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 logfile. / ib_logfile101 size to 100 MB

InnoDB: Progress in MB:

one hundred

InnoDB: Setting logfile. / ib_logfile1 size to 100 MB

InnoDB: Progress in MB:

one hundred

InnoDB: Renaming logfile. / 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

InnoDB: Doing recovery: scanned up to log sequence number 1679381

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!

-perform recovery to T0 point in time

[root@mariadbl01 mysql] # innobackupex-- defaults-file=/etc/my.cnf.d/server.cnf / app/mysqldump/2017-12-1909-40-22 /-- copy-back

171221 15:27:10 innobackupex: Starting the copy-back operation

IMPORTANT: Please check that the copy-back run completes successfully.

At the end of a successful copy-back run innobackupex

Prints "completed OK!".

Innobackupex version 2.4.4 based on MySQL server 5.7.13 Linux (x86 / 64) (revision id: df58cf2)

171221 15:27:10 [01] Copying ib_logfile0 to / app/mysql/3306/ib_logfile0

171221 15:27:11 [01]... done

171221 15:27:12 [01] Copying ib_logfile1 to / app/mysql/3306/ib_logfile1

171221 15:27:12 [01]... done

171221 15:27:13 [01] Copying ibdata1 to / app/mysql/3306/ibdata1

171221 15:27:13 [01]... done

171221 15:27:14 [01] Copying. / test/db.opt to / app/mysql/3306/test/db.opt

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / ty/APP_OTAURL.ibd to / app/mysql/3306/ty/APP_OTAURL.ibd

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / ty/APP_GROUPS.ibd to / app/mysql/3306/ty/APP_GROUPS.ibd

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / ty/APP_ROLE.frm to / app/mysql/3306/ty/APP_ROLE.frm

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / ty/APP_GROUPS.frm to / app/mysql/3306/ty/APP_GROUPS.frm

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / ty/APP_MEMBER.frm to / app/mysql/3306/ty/APP_MEMBER.frm

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / ty/APP_OTAURL.frm to / app/mysql/3306/ty/APP_OTAURL.frm

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / ty/APP_ORG.ibd to / app/mysql/3306/ty/APP_ORG.ibd

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / ty/APP_ROLE.ibd to / app/mysql/3306/ty/APP_ROLE.ibd

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / ty/APP_ORG.frm to / app/mysql/3306/ty/APP_ORG.frm

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / ty/APP_MEMBER.ibd to / app/mysql/3306/ty/APP_MEMBER.ibd

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / ty/db.opt to / app/mysql/3306/ty/db.opt

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / xtrabackup_binlog_pos_innodb to / app/mysql/3306/xtrabackup_binlog_pos_innodb

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / xtrabackup_info to / app/mysql/3306/xtrabackup_info

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / performance_schema/events_waits_summary_by_user_by_event_name.frm to / app/mysql/3306/performance_schema/events_waits_summary_by_user_by_event_name.frm

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / performance_schema/objects_summary_global_by_type.frm to / app/mysql/3306/performance_schema/objects_summary_global_by_type.frm

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / performance_schema/events_waits_history_long.frm to / app/mysql/3306/performance_schema/events_waits_history_long.frm

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / performance_schema/events_waits_summary_by_host_by_event_name.frm to / app/mysql/3306/performance_schema/events_waits_summary_by_host_by_event_name.frm

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / performance_schema/socket_summary_by_event_name.frm to / app/mysql/3306/performance_schema/socket_summary_by_event_name.frm

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / performance_schema/table_lock_waits_summary_by_table.frm to / app/mysql/3306/performance_schema/table_lock_waits_summary_by_table.frm

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / performance_schema/events_statements_summary_by_account_by_event_name.frm to / app/mysql/3306/performance_schema/events_statements_summary_by_account_by_event_name.frm

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / performance_schema/events_stages_summary_by_host_by_event_name.frm to / app/mysql/3306/performance_schema/events_stages_summary_by_host_by_event_name.frm

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / performance_schema/socket_instances.frm to / app/mysql/3306/performance_schema/socket_instances.frm

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / performance_schema/host_cache.frm to / app/mysql/3306/performance_schema/host_cache.frm

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / performance_schema/events_stages_summary_by_user_by_event_name.frm to / app/mysql/3306/performance_schema/events_stages_summary_by_user_by_event_name.frm

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / performance_schema/events_stages_summary_by_thread_by_event_name.frm to / app/mysql/3306/performance_schema/events_stages_summary_by_thread_by_event_name.frm

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / performance_schema/events_stages_history_long.frm to / app/mysql/3306/performance_schema/events_stages_history_long.frm

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / performance_schema/file_summary_by_instance.frm to / app/mysql/3306/performance_schema/file_summary_by_instance.frm

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / performance_schema/events_waits_summary_by_thread_by_event_name.frm to / app/mysql/3306/performance_schema/events_waits_summary_by_thread_by_event_name.frm

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / performance_schema/setup_instruments.frm to / app/mysql/3306/performance_schema/setup_instruments.frm

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / performance_schema/events_statements_summary_by_thread_by_event_name.frm to / app/mysql/3306/performance_schema/events_statements_summary_by_thread_by_event_name.frm

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / performance_schema/mutex_instances.frm to / app/mysql/3306/performance_schema/mutex_instances.frm

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / performance_schema/events_statements_current.frm to / app/mysql/3306/performance_schema/events_statements_current.frm

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / performance_schema/setup_objects.frm to / app/mysql/3306/performance_schema/setup_objects.frm

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / performance_schema/events_stages_history.frm to / app/mysql/3306/performance_schema/events_stages_history.frm

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / performance_schema/accounts.frm to / app/mysql/3306/performance_schema/accounts.frm

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / performance_schema/events_waits_history.frm to / app/mysql/3306/performance_schema/events_waits_history.frm

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / performance_schema/events_waits_current.frm to / app/mysql/3306/performance_schema/events_waits_current.frm

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / performance_schema/table_io_waits_summary_by_table.frm to / app/mysql/3306/performance_schema/table_io_waits_summary_by_table.frm

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / performance_schema/setup_consumers.frm to / app/mysql/3306/performance_schema/setup_consumers.frm

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / performance_schema/performance_timers.frm to / app/mysql/3306/performance_schema/performance_timers.frm

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / performance_schema/session_account_connect_attrs.frm to / app/mysql/3306/performance_schema/session_account_connect_attrs.frm

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / performance_schema/session_connect_attrs.frm to / app/mysql/3306/performance_schema/session_connect_attrs.frm

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / performance_schema/events_statements_summary_by_host_by_event_name.frm to / app/mysql/3306/performance_schema/events_statements_summary_by_host_by_event_name.frm

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / performance_schema/file_summary_by_event_name.frm to / app/mysql/3306/performance_schema/file_summary_by_event_name.frm

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / performance_schema/setup_actors.frm to / app/mysql/3306/performance_schema/setup_actors.frm

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / performance_schema/file_instances.frm to / app/mysql/3306/performance_schema/file_instances.frm

171221 15:27:14 [01]... done

171221 15:27:14 [01] Copying. / performance_schema/events_statements_summary_by_digest.frm to / app/mysql/3306/performance_schema/events_statements_summary_by_digest.frm

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / performance_schema/hosts.frm to / app/mysql/3306/performance_schema/hosts.frm

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / performance_schema/events_statements_history_long.frm to / app/mysql/3306/performance_schema/events_statements_history_long.frm

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / performance_schema/events_waits_summary_by_instance.frm to / app/mysql/3306/performance_schema/events_waits_summary_by_instance.frm

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / performance_schema/threads.frm to / app/mysql/3306/performance_schema/threads.frm

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / performance_schema/rwlock_instances.frm to / app/mysql/3306/performance_schema/rwlock_instances.frm

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / performance_schema/events_statements_summary_global_by_event_name.frm to / app/mysql/3306/performance_schema/events_statements_summary_global_by_event_name.frm

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / performance_schema/setup_timers.frm to / app/mysql/3306/performance_schema/setup_timers.frm

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / performance_schema/events_stages_current.frm to / app/mysql/3306/performance_schema/events_stages_current.frm

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / performance_schema/socket_summary_by_instance.frm to / app/mysql/3306/performance_schema/socket_summary_by_instance.frm

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / performance_schema/events_waits_summary_by_account_by_event_name.frm to / app/mysql/3306/performance_schema/events_waits_summary_by_account_by_event_name.frm

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / performance_schema/users.frm to / app/mysql/3306/performance_schema/users.frm

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / performance_schema/cond_instances.frm to / app/mysql/3306/performance_schema/cond_instances.frm

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / performance_schema/events_waits_summary_global_by_event_name.frm to / app/mysql/3306/performance_schema/events_waits_summary_global_by_event_name.frm

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / performance_schema/events_stages_summary_by_account_by_event_name.frm to / app/mysql/3306/performance_schema/events_stages_summary_by_account_by_event_name.frm

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / performance_schema/table_io_waits_summary_by_index_usage.frm to / app/mysql/3306/performance_schema/table_io_waits_summary_by_index_usage.frm

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / performance_schema/events_statements_history.frm to / app/mysql/3306/performance_schema/events_statements_history.frm

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / performance_schema/db.opt to / app/mysql/3306/performance_schema/db.opt

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / performance_schema/events_stages_summary_global_by_event_name.frm to / app/mysql/3306/performance_schema/events_stages_summary_global_by_event_name.frm

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / performance_schema/events_statements_summary_by_user_by_event_name.frm to / app/mysql/3306/performance_schema/events_statements_summary_by_user_by_event_name.frm

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / mysql/slow_log.CSM to / app/mysql/3306/mysql/slow_log.CSM

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / mysql/time_zone_name.frm to / app/mysql/3306/mysql/time_zone_name.frm

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / mysql/time_zone_transition.MYI to / app/mysql/3306/mysql/time_zone_transition.MYI

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / mysql/table_stats.MYD to / app/mysql/3306/mysql/table_stats.MYD

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / mysql/user.MYD to / app/mysql/3306/mysql/user.MYD

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / mysql/time_zone_leap_second.MYI to / app/mysql/3306/mysql/time_zone_leap_second.MYI

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / mysql/proxies_priv.MYI to / app/mysql/3306/mysql/proxies_priv.MYI

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / mysql/column_stats.MYD to / app/mysql/3306/mysql/column_stats.MYD

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / mysql/column_stats.MYI to / app/mysql/3306/mysql/column_stats.MYI

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / mysql/procs_priv.MYD to / app/mysql/3306/mysql/procs_priv.MYD

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / mysql/tables_priv.MYD to / app/mysql/3306/mysql/tables_priv.MYD

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / mysql/host.MYD to / app/mysql/3306/mysql/host.MYD

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / mysql/innodb_index_stats.ibd to / app/mysql/3306/mysql/innodb_index_stats.ibd

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / mysql/help_category.MYD to / app/mysql/3306/mysql/help_category.MYD

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / mysql/time_zone_transition.frm to / app/mysql/3306/mysql/time_zone_transition.frm

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / mysql/help_keyword.MYD to / app/mysql/3306/mysql/help_keyword.MYD

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / mysql/roles_mapping.frm to / app/mysql/3306/mysql/roles_mapping.frm

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / mysql/table_stats.MYI to / app/mysql/3306/mysql/table_stats.MYI

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / mysql/plugin.MYI to / app/mysql/3306/mysql/plugin.MYI

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / mysql/tables_priv.MYI to / app/mysql/3306/mysql/tables_priv.MYI

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / mysql/gtid_slave_pos.ibd to / app/mysql/3306/mysql/gtid_slave_pos.ibd

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / mysql/proc.MYI to / app/mysql/3306/mysql/proc.MYI

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / mysql/func.frm to / app/mysql/3306/mysql/func.frm

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / mysql/tables_priv.frm to / app/mysql/3306/mysql/tables_priv.frm

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / mysql/procs_priv.frm to / app/mysql/3306/mysql/procs_priv.frm

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / mysql/help_keyword.MYI to / app/mysql/3306/mysql/help_keyword.MYI

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / mysql/time_zone_transition_type.frm to / app/mysql/3306/mysql/time_zone_transition_type.frm

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / mysql/index_stats.MYD to / app/mysql/3306/mysql/index_stats.MYD

171221 15:27:15 [01]... done

171221 15:27:15 [01] Copying. / mysql/event.frm to / app/mysql/3306/mysql/event.frm

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/help_topic.MYI to / app/mysql/3306/mysql/help_topic.MYI

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/roles_mapping.MYD to / app/mysql/3306/mysql/roles_mapping.MYD

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/columns_priv.MYD to / app/mysql/3306/mysql/columns_priv.MYD

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/func.MYI to / app/mysql/3306/mysql/func.MYI

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/proxies_priv.MYD to / app/mysql/3306/mysql/proxies_priv.MYD

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/proc.MYD to / app/mysql/3306/mysql/proc.MYD

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/procs_priv.MYI to / app/mysql/3306/mysql/procs_priv.MYI

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/columns_priv.frm to / app/mysql/3306/mysql/columns_priv.frm

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/servers.frm to / app/mysql/3306/mysql/servers.frm

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/time_zone_transition_type.MYI to / app/mysql/3306/mysql/time_zone_transition_type.MYI

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/db.MYI to / app/mysql/3306/mysql/db.MYI

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/gtid_slave_pos.frm to / app/mysql/3306/mysql/gtid_slave_pos.frm

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/plugin.frm to / app/mysql/3306/mysql/plugin.frm

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/time_zone_transition.MYD to / app/mysql/3306/mysql/time_zone_transition.MYD

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/table_stats.frm to / app/mysql/3306/mysql/table_stats.frm

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/time_zone_leap_second.MYD to / app/mysql/3306/mysql/time_zone_leap_second.MYD

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/host.frm to / app/mysql/3306/mysql/host.frm

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/plugin.MYD to / app/mysql/3306/mysql/plugin.MYD

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/roles_mapping.MYI to / app/mysql/3306/mysql/roles_mapping.MYI

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/index_stats.MYI to / app/mysql/3306/mysql/index_stats.MYI

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/help_relation.MYI to / app/mysql/3306/mysql/help_relation.MYI

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/innodb_table_stats.frm to / app/mysql/3306/mysql/innodb_table_stats.frm

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/db.frm to / app/mysql/3306/mysql/db.frm

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/help_relation.frm to / app/mysql/3306/mysql/help_relation.frm

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/innodb_index_stats.frm to / app/mysql/3306/mysql/innodb_index_stats.frm

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/help_category.frm to / app/mysql/3306/mysql/help_category.frm

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/help_relation.MYD to / app/mysql/3306/mysql/help_relation.MYD

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/servers.MYI to / app/mysql/3306/mysql/servers.MYI

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/time_zone_transition_type.MYD to / app/mysql/3306/mysql/time_zone_transition_type.MYD

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/user.MYI to / app/mysql/3306/mysql/user.MYI

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/help_keyword.frm to / app/mysql/3306/mysql/help_keyword.frm

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/time_zone_leap_second.frm to / app/mysql/3306/mysql/time_zone_leap_second.frm

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/general_log.CSV to / app/mysql/3306/mysql/general_log.CSV

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/help_topic.MYD to / app/mysql/3306/mysql/help_topic.MYD

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/host.MYI to / app/mysql/3306/mysql/host.MYI

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/proxies_priv.frm to / app/mysql/3306/mysql/proxies_priv.frm

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/index_stats.frm to / app/mysql/3306/mysql/index_stats.frm

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/proc.frm to / app/mysql/3306/mysql/proc.frm

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/general_log.frm to / app/mysql/3306/mysql/general_log.frm

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/innodb_table_stats.ibd to / app/mysql/3306/mysql/innodb_table_stats.ibd

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/time_zone.MYI to / app/mysql/3306/mysql/time_zone.MYI

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/general_log.CSM to / app/mysql/3306/mysql/general_log.CSM

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/help_topic.frm to / app/mysql/3306/mysql/help_topic.frm

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/slow_log.frm to / app/mysql/3306/mysql/slow_log.frm

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/time_zone.frm to / app/mysql/3306/mysql/time_zone.frm

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/time_zone.MYD to / app/mysql/3306/mysql/time_zone.MYD

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/func.MYD to / app/mysql/3306/mysql/func.MYD

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/columns_priv.MYI to / app/mysql/3306/mysql/columns_priv.MYI

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/column_stats.frm to / app/mysql/3306/mysql/column_stats.frm

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/time_zone_name.MYD to / app/mysql/3306/mysql/time_zone_name.MYD

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/user.frm to / app/mysql/3306/mysql/user.frm

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/slow_log.CSV to / app/mysql/3306/mysql/slow_log.CSV

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/time_zone_name.MYI to / app/mysql/3306/mysql/time_zone_name.MYI

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/db.MYD to / app/mysql/3306/mysql/db.MYD

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/servers.MYD to / app/mysql/3306/mysql/servers.MYD

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/event.MYI to / app/mysql/3306/mysql/event.MYI

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/help_category.MYI to / app/mysql/3306/mysql/help_category.MYI

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / mysql/event.MYD to / app/mysql/3306/mysql/event.MYD

171221 15:27:16 [01]... done

171221 15:27:16 [01] Copying. / ibtmp1 to / app/mysql/3306/ibtmp1

171221 15:27:16 [01]... done

171221 15:27:17 completed OK!

[root@mariadbl01 mysql] # cd 3306

[root@mariadbl01 3306] # ls-lrt

Total 229640

-rw-r- 1 root root 104857600 Dec 21 15:27 ib_logfile0

-rw-r- 1 root root 104857600 Dec 21 15:27 ib_logfile1

-rw-r- 1 root root 12582912 Dec 21 15:27 ibdata1

-rw-r- 1 root root 536 Dec 21 15:27 xtrabackup_info

-rw-r- 1 root root 40 Dec 21 15:27 xtrabackup_binlog_pos_innodb

Drwxr-x--- 2 root root 4096 Dec 21 15:27 ty

Drwxr-x--- 2 root root 4096 Dec 21 15:27 test

Drwxr-x--- 2 root root 4096 Dec 21 15:27 performance_schema

Drwxr-x--- 2 root root 4096 Dec 21 15:27 mysql

-rw-r- 1 root root 12582912 Dec 21 15:27 ibtmp1

[root@mariadbl01 3306] # cd..

[root@mariadbl01 mysql] # chown-R mysql:mysql 3306

[root@mariadbl01 mysql] # service mysql start

Starting MySQL.171221 15:28:40 mysqld_safe Adding'/ usr/lib64/libjemalloc.so.1' to LD_PRELOAD for mysqld

171221 15:28:40 mysqld_safe Logging to'/ app/mysql/mysql.err'.

171221 15:28:40 mysqld_safe Starting mysqld daemon with databases from / app/mysql/3306/

SUCCESS!

MariaDB [(none)] > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | test |

| | ty |

+-+

5 rows in set (0.00 sec)

MariaDB [(none)] > use ty

MariaDB [ty] > show tables

+-+

| | Tables_in_ty |

+-+

| | APP_GROUPS |

| | APP_MEMBER |

| | APP_ORG |

| | APP_OTAURL |

| | APP_ROLE |

+-+

5 rows in set (0.00 sec)

MariaDB [ty] > select count (*) from APP_MEMBER

+-+

| | count (*) |

+-+

| | 3 |

+-+

1 row in set (0.00 sec)

MariaDB [ty] > select count (*) from APP_OTAURL

+-+

| | count (*) |

+-+

| | 47 |

+-+

1 row in set (0.00 sec)

-at this point, you can see that the database has been restored to point in time T0

8. Restore the simulated database to point-in-time T1

-find the position of the binlog when the database is backed up

# cd 2017-12-1909-40-22

# cat xtrabackup_binlog_info

Mysql-bin.000001 312

-apply the log to the T1 point in time

MariaDB [ty] > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000001 | 5106 | |

+-+

1 row in set (0.00 sec)

# / usr/bin/mysqlbinlog-- no-defaults-- start-position=312-- stop-position=5106 / app/mysql/binlog_bk/mysql-bin.000001 | / usr/bin/mysql

MariaDB [ty] > show tables

+-+

| | Tables_in_ty |

+-+

| | APP_GROUPS |

| | APP_MEMBER |

| | APP_ORG |

| | APP_OTAURL |

| | APP_ROLE |

| | t |

+-+

6 rows in set (0.00 sec)

MariaDB [ty] > select * from t

+-+

| | tid | tdept | tname | ttime | |

+-+

| | 1 | 10 | binlog | 2017-12-19 10:01:11 |

| | 2 | 20 | MySQL | 2017-12-19 10:01:17 |

| | 11 | 110 | TangYun | 2017-12-19 10:01:17 |

| | 12 | 100 | tangyun | 2017-12-19 10:01:17 |

| | 13 | 100 | | 2017-12-19 10:01:18 |

+-+

5 rows in set (0.00 sec)

MariaDB [ty] > select count (*) from APP_OTAURL

+-+

| | count (*) |

+-+

| | 0 |

+-+

1 row in set (0.00 sec)

-you can see that by this time the data has been restored to the point in time T1.

9. Restore the simulated database to the point in time T2

# / usr/bin/mysqlbinlog-- no-defaults-- start-position=5106-- stop-position=240852 / app/mysql/binlog_bk/mysql-bin.000001 | / usr/bin/mysql

MariaDB [ty] > show tables

+-+

| | Tables_in_ty |

+-+

| | APP_GROUPS |

| | APP_MEMBER |

| | APP_ORG |

| | APP_OTAURL |

| | APP_ROLE |

| | exs_order |

| | exs_product |

| | exs_report |

| | t |

+-+

9 rows in set (0.00 sec)

MariaDB [ty] > select count (*) from exs_order

+-+

| | count (*) |

+-+

| | 566 |

+-+

1 row in set (0.00 sec)

MariaDB [ty] > select count (*) from exs_product

+-+

| | count (*) |

+-+

| | 13 |

+-+

1 row in set (0.00 sec)

MariaDB [ty] > select count (*) from exs_report

+-+

| | count (*) |

+-+

| | 513 |

+-+

1 row in set (0.00 sec)

-you can see that the database has been restored to the point in time T2

10. Simulated database recovery to point-in-time T3

# / usr/bin/mysqlbinlog-- no-defaults-- start-position=365 / app/mysql/binlog_bk/mysql-bin.000002 | / usr/bin/mysql

MariaDB [ty] > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | test |

| | ty |

| | ty_db |

+-+

6 rows in set (0.00 sec)

MariaDB [ty_db] > show tables

+-+

| | Tables_in_ty_db |

+-+

| | exs_order |

| | exs_product |

| | exs_report |

+-+

3 rows in set (0.00 sec)

MariaDB [ty_db] > select count (*) from exs_order

+-+

| | count (*) |

+-+

| | 0 |

+-+

1 row in set (0.00 sec)

-at this point, the database has been restored to the current point in time.

-- End By TangYun (Tony.Tang) May 12, 2017.12

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.